Protobi

← All help posts

View post

Protobi can reshape a data file *while it uploads*. You describe the reshaping once, as JSON on the
data card, and every file you upload to that card is transformed on the way in.

This ranges from trivial cleanups — lowercase the column names, drop columns you don't want, keep only
completed interviews — to splitting a wide survey export into a set of stacked tables, one per loop.
It's the same mechanism throughout; only the configuration gets longer.

## The mental model

A survey export usually arrives **flat**: one row per respondent, with repeated question blocks
spread across hundreds of numbered columns. `Q12_1`, `Q12_2`, `Q12_3` are the same question asked
about three different brands.

Analysis usually wants it **stacked**: one row per respondent × brand, with a single `Q12` column.

The transform's job is that rotation. You tell it which columns belong to which repeating block, and
how deeply nested each block is. It produces one table per block.

Everything else — renaming, filtering, selecting columns, adding a row index — is the same machinery
with less configuration.

## Where the configuration lives

On the data card, under **Properties**, in a `savtools` object:

```json
{
  "savtools": {
    "transform": {
      "normalize": {
        "...": "options go here"
      }
    }
  }
}
```

The nesting is deliberate: it mirrors the underlying command. `savtools.transform.normalize` means
"run the *normalize* transform", and the innermost object holds that command's options. If a new
command is added later, it appears as a new path at the same level — nothing else changes.

Two kinds of key live under `savtools`:

- **`transform.normalize`** — what to do to the data
- **`output`, `tables`, `enabled`** — where the results should land

## Level 1 — simple column and row operations

The simplest configurations don't reshape anything. They clean up a file as it loads and write the
result back to the same card.

**Lowercase every column name:**

```json
{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "lowercasekeys": true
      }
    }
  }
}
```

`output: "self"` means "put the result back in this card" — there's only one output, and it replaces
the card's data. Column names are lowercased and any character outside `A-Z a-z 0-9 _` becomes `_`.

**Keep only some columns:**

```json
{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "include": "^(respondent_id|q[0-9]+)"
      }
    }
  }
}
```

**Drop some columns:**

```json
{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "exclude": "^(hid|tmp|debug)"
      }
    }
  }
}
```

`include` and `exclude` are regular expressions matched against column names. Use both together to
take a broad set and then carve out exceptions.

**Keep only some rows:**

```json
{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "filter": { "status": 3 }
      }
    }
  }
}
```

`filter` is a MongoDB-style query, so it handles more than equality:

```json
"filter": { "status": 3, "duration": { "$gt": 120 } }
```

**Add a row-index column:**

```json
{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "rowId": "rownum"
      }
    }
  }
}
```

This adds a sequential column, numbered from 1, as the first column of the output. It's the simplest
way to give every row a stable handle when the source file has no respondent key.

**Take the first N rows** — useful for a quick structural test before committing to a full load:

```json
"limit": 500
```

These options combine freely. A realistic first-pass cleanup:

```json
{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "lowercasekeys": true,
        "exclude": "^(hid|tmp)",
        "filter": { "status": 3 },
        "rowId": "rownum"
      }
    }
  }
}
```

## Level 2 — collapsing repeated columns into arrays

Multi-select questions often arrive as one column per option: `Q11_1`, `Q11_2`, `Q11_3`. Rather than
splitting these into their own table, you can collapse each set into a single array-valued column.

```json
{
  "savtools": {
    "output": "self",
    "transform": {
      "normalize": {
        "lowercasekeys": false,
        "ignoreCase": true,
        "loops": {
          "respondent": {
            "dimension": 0,
            "passthrough": true,
            "patterns": {
              "Q11": "Q11_[0-9]+",
              "Q12": "Q12_[0-9]+"
            },
            "transforms": {
              "Q11": "array",
              "Q12": "array"
            }
          }
        }
      }
    }
  }
}
```

Three new ideas here:

- **`loops`** names the repeating blocks. `dimension: 0` means respondent level — one row out per row
  in, no rotation.
- **`passthrough: true`** carries every other column through untouched, so you keep the rest of the
  interview alongside the collapsed columns.
- **`transforms: {"Q11": "array"}`** turns the matched set into one array column instead of many.

`patterns` maps an output column name to a regular expression matching the source columns that feed
it. Array transforms only apply at `dimension: 0`.

`ignoreCase: true` makes pattern matching case-insensitive — worth setting whenever the source uses
different capitalization than your patterns.

## Level 3 — rotating case data into a loop

Now the real work. A physician reports on several patients; a shopper rates several retailers. The
source has one row per respondent, with each case's answers suffixed by its number.

```json
{
  "savtools": {
    "transform": {
      "normalize": {
        "table": "data_{loop}",
        "ignoreCase": true,
        "loops": {
          "case": {
            "dimension": 1,
            "id": "respondent_id",
            "rename": "renumber",
            "dimensions": ["case_id"],
            "passthrough": true,
            "weak": ["screener_flag"],
            "patterns": {
              "A":   "^A([0-9]+)_([0-9]+)$",
              "B1":  "^B1_([0-9]+)$",
              "B2":  "^B2_([0-9]+)c[0-9]+$"
            }
          }
        }
      }
    }
  }
}
```

What each part does:

- **`dimension: 1`** — one level of nesting below the respondent. Each respondent produces as many
  rows as they have cases.
- **`id`** — the respondent-level identifier carried onto every output row, so you can join back.
- **`dimensions: ["case_id"]`** — names the index column holding which case a row represents.
- **`rename: "renumber"`** — keeps the original column name with the captured index replaced by `1`,
  so `A3_2` becomes `A3_1`. The default, `"key"`, uses the pattern's key as the output name instead.
- **`weak`** — columns copied onto each row but *not* counted as data when deciding whether the row
  exists at all. Use it for hidden or administrative fields that would otherwise keep empty cases
  alive.
- **`table: "data_{loop}"`** — names the output card; `{loop}` becomes the loop name, so this produces
  `data_case`.

Note there's no `output: "self"` here. Once a transform produces named tables, each one becomes its
own card and the upload card stays as the source.

## Level 4 — splitting into several tables

Most real surveys have more than one level of nesting: the respondent, then each brand they rated,
then each attribute they rated it on. Each level becomes its own loop, at its own depth.

```json
{
  "savtools": {
    "transform": {
      "normalize": {
        "table": "data_{loop}",
        "ignoreCase": true,
        "loops": [
          {
            "name": "respondent",
            "dimension": 0,
            "retain": ["respondent_id"],
            "include": [".*"],
            "exclude": ["^q1[0-9]r[0-9]+c[0-9]+$"]
          },
          {
            "name": "brand",
            "dimension": 1,
            "retain": ["respondent_id"],
            "dimensions": ["brand_id"],
            "patterns": {
              "q10": "^q10r([0-9]+)$",
              "q11": "^q11r([0-9]+)$"
            }
          },
          {
            "name": "attribute",
            "dimension": 2,
            "retain": ["respondent_id"],
            "dimensions": ["brand_id", "attribute_id"],
            "patterns": {
              "q12": "^q12_([0-9]+)_([0-9]+)$"
            }
          }
        ]
      }
    }
  }
}
```

This yields three cards: `data_respondent` (one row per respondent), `data_brand` (respondent ×
brand), and `data_attribute` (respondent × brand × attribute).

Points worth noting:

- **`loops` can be a list or an object.** As a list, each entry carries its own `name`. As an object,
  the key *is* the name. Use whichever reads better; lists are easier when order matters to you.
- **`dimension` is the nesting depth**, and **`dimensions` names the index columns** at each level. At
  `dimension: 2` you name two: the outer index and the inner one. Each pattern needs a capture group
  per level, in the same order.
- **`retain`** copies respondent-level columns onto every row of a deeper loop, so each table can
  stand alone. It accepts a single name or a list.
- **The respondent loop usually needs an `exclude`** for the columns the deeper loops consume,
  otherwise every brand column also shows up, unstacked, in the respondent table. `include: [".*"]`
  with a list of exclusions is the usual idiom. At loop level, `include`/`exclude` take **lists** of
  patterns rather than a single expression.
- **`omitted`** is a labelled form of `exclude` — a map of name to pattern, appended to the loop's
  exclusions. Use it when you want the config to record *why* something is dropped.

## Level 5 — files that need a companion metadata file

Some formats can't be read on their own. Fixed-width `hdata` needs its `.spx` spec; some CSV exports
come with a Triple-S `.sss` file describing the variables.

Protobi handles this with a second card. Upload the metadata file to its own **document** card, then
name that card in the transform.

**Step 1.** Create a document card named `def.spx` and upload the spec to it.

**Step 2.** Create the data card and reference it:

```json
{
  "savtools": {
    "transform": {
      "normalize": {
        "table": "data_{loop}",
        "spec": "def.spx",
        "ignoreCase": true,
        "loops": [
          { "name": "respondent", "dimension": 0, "retain": ["respondent_id"],
            "patterns": { "q2": "Q2", "q6": "Q6" } },
          { "name": "brand", "dimension": 1, "retain": ["respondent_id"],
            "dimensions": ["brand_id"],
            "patterns": { "q8": "Q8_([0-9]+)" } }
        ]
      }
    }
  }
}
```

**Step 3.** Upload the data file to the data card.

Three companion options are available, and all three name a *card*, not a file path:

| Option | For |
|---|---|
| `spec` | SPX spec, and other metadata formats, e.g. paired with `hdata` |
| `sss` | Triple-S metadata, e.g. paired with a CSV export |
| `mapping` | Source mapping files |

**Upload the metadata file first.** The transform looks the card up by name and needs a file already
on it.

Once a `spec` is configured, the data file's own extension stops mattering — the spec determines how
the data is read.

## Parameter reference

### Output routing

Directly under `savtools`:

| Option | Default | Effect |
|---|---|---|
| `output` | `"tables"` | `"tables"` creates one card per output. `"self"` writes a single output back into this card. |
| `tables` | — | Per-output overrides, keyed by loop name. |
| `reuseCurrentTable` | `false` | Alias for `output: "self"`. |
| `enabled` | `true` | Set `false` to disable the transform without deleting it. |

An output also lands on the upload card if its name matches that card's key — so naming a loop after
the source card updates it in place. Only one output may target the card.

Per-output overrides in `tables`:

| Field | Effect |
|---|---|
| `key` | Card key to write to, instead of the name from `table` |
| `name` | Display name |
| `mode` | Storage mode, e.g. `csv` or `sql` |
| `type` | Card type; defaults to `data` |
| `loadMode` | Load behaviour, e.g. `replace` |
| `useStreaming` | Whether this output imports via streaming |
| `properties` | Properties to set on the output card |
| `self` | `true` routes this output into the upload card |

### Transform options

Inside `transform.normalize`:

| Option | Default | Effect |
|---|---|---|
| `table` | — | Output card naming pattern; `{loop}` becomes the loop name |
| `loops` | — | The repeating blocks to extract; list or object |
| `patterns` | — | Column patterns, for a single implicit loop |
| `lowercasekeys` | `false` | Lowercase and sanitize all column names |
| `ignoreCase` | `false` | Case-insensitive pattern matching |
| `include` | — | Regex of columns to keep |
| `exclude` | — | Regex of columns to drop |
| `filter` | — | MongoDB-style row filter |
| `limit` | — | Maximum rows to read |
| `rowId` | — | Add a sequential index column with this name |
| `columns` | — | Explicit list of columns |
| `spec` / `sss` / `mapping` | — | Companion metadata card |
| `encoding` | — | Override file encoding, e.g. `windows1252` |
| `format` | `csv` | Output format |
| `verbose` | `false` | Detailed progress in the server log |
| `failOnEmpty` | `true` | Fail if any loop matches no columns |

### Loop options

Inside each entry of `loops`:

| Option | Effect |
|---|---|
| `name` | Loop name; substituted into `table`. Implied when `loops` is an object. |
| `dimension` | Nesting depth: `0` respondent, `1` one level down, `2` two levels down |
| `patterns` | Map of output column name to a regex with one capture group per level |
| `dimensions` | Names for the index columns, outermost first |
| `retain` | Respondent-level columns copied onto every row; name or list |
| `passthrough` | Carry all other columns through |
| `transforms` | Per-column transform, e.g. `{"Q11": "array"}`; arrays require `dimension: 0` |
| `weak` | Columns copied but not counted when deciding whether a row exists |
| `rename` | `"key"` (default) names output by pattern key; `"renumber"` keeps the source name with indexes reset to 1 |
| `id` | Respondent-level identifier carried onto every row |
| `include` / `exclude` | Lists of patterns to keep or drop for this loop |
| `omitted` | Labelled exclusions: a map of name to pattern |

## Troubleshooting

**`savtools transform produced no rows for loop(s): …`**
Those loops' patterns matched nothing. Usually a capitalization mismatch — try `ignoreCase: true` —
or a pattern that doesn't match the real column names. If a loop legitimately has no data this wave,
set `failOnEmpty: false` to skip it and import the rest.

**`card "def.spx" has no uploaded file yet`**
The companion card exists but is empty. Upload the metadata file to it first.

**`references card "def.spx", but no card with that filename or key exists`**
Nothing matches that name. Check the spelling, and that the card is in the same project.

**`output:"self" expects a single transform output; got N`**
`output: "self"` only works with one output. Use the default `output: "tables"` for multiple loops.

**Brand columns appear in the respondent table, unstacked**
The respondent loop needs to exclude the columns the deeper loops consume. Add them to its `exclude`
or `omitted` list.

**A deeper loop produces rows for cases that don't exist**
Some administrative column is keeping empty rows alive. Add it to that loop's `weak` list.

## Notes

- **Outputs are all checked before any is imported**, so a bad configuration can't leave you with a
  half-loaded set of tables.
- **Large files take time.** Several million output rows across a handful of loops can run twenty
  minutes or more. The progress panel names each table as it imports.
- **Set `verbose: true` while developing a configuration.** The server log then shows which columns
  each loop matched and a running record count.
- **Build up in stages.** Start with `limit: 500` and one loop, confirm the shape is right, then add
  loops and remove the limit.

Publishing

Date

Status

Slug edit

Content

Thumbnail

Categories Manage

Tools

AI Database

Publishing

Content

Tools

Danger zone