Protobi

← All help posts

View post

# Protobi CSV API

Protobi data processes can use a number of convenience methods we've predefined
The `Protobi` object is available in the browser as `window.Protobi` and in Node.js via `require('./public/javascripts/csv')`. It provides utilities for working with tabular data represented as arrays of row objects as well as utilities to generate SQL.

Examples:

```js
let tables = await Protobi.get_tables(["wave1", "wave2", "wave3", "consumer"])
let stacked = Protobi.stack_row( [data.wave1, data.wave2, data.wave3])
let merged = Protobi.left_join(stacked, consumer, ['respid'], ['segment', 'score'])
```

---

## Value utilities

### `isNA(val)`

Returns `true` if the value is missing: `null`, `undefined`, or empty string `''`.

```js
Protobi.isNA(null)      // true
Protobi.isNA(undefined) // true
Protobi.isNA('')        // true
Protobi.isNA(0)         // false
Protobi.isNA('NA')      // false
```

### `toNumber(val, emptyToNull)`

Converts a numeric string to a number. Leaves non-numeric strings unchanged. If `emptyToNull` is true, converts `''` to `null`.

```js
Protobi.toNumber('42')         // 42
Protobi.toNumber('hello')      // 'hello'
Protobi.toNumber('', true)     // null
```

### `numerify(row, emptyToNull)`

Converts all numeric string values in a row object (or array of rows) to numbers in place.

```js
let row = { id: '1', age: '30', name: 'Alice' }
Protobi.numerify(row)
// => { id: 1, age: 30, name: 'Alice' }
```

### `sum(row, keys)` / `max(row, keys)` / `min(row, keys)`

Aggregate numeric values across specified columns of a single row. Returns `null` if no valid numeric values are found.

```js
let row = { q1: '3', q2: '5', q3: 'x' }
Protobi.sum(row, ['q1', 'q2', 'q3'])  // 8
Protobi.max(row, ['q1', 'q2', 'q3'])  // 5
Protobi.min(row, ['q1', 'q2', 'q3'])  // 3
```

---

## Column utilities

### `get_column_names(rows)`

Returns the union of all keys across all rows. Useful when not every row has every column.

```js
let rows = [{ a: 1, b: 2 }, { b: 3, c: 4 }]
Protobi.get_column_names(rows)  // ['a', 'b', 'c']
```

### `accumulate_keys(rows)`

Same as `get_column_names`. Scans all rows and returns the superset of keys.

---

## Encoding and decoding

### `encode(data, options)`

Converts an array of row objects to a CSV string. Handles quoting, escaping, and leading zeros.

```js
let rows = [{ id: '01', name: 'Alice, PhD' }]
Protobi.encode(rows)
// => 'id,name\n"01","Alice, PhD"'
```

Options:
- `options.header` — array of column names to use (and their order); defaults to keys from first row plus any additional keys found in subsequent rows

### `encode_async(data, options)`

Async version of `encode` that yields to the event loop every 100 rows. Use this in the browser when encoding large datasets to keep the UI responsive.

```js
let csv = await Protobi.encode_async(rows)
```

---

## Fetching and saving data

These methods make AJAX calls to the Protobi server API. They support both callback and Promise styles.

### `get_table(datasetId, key, options, callback)`

Fetches a data table from the server as an array of row objects.

```js
// Promise style
let rows = await Protobi.get_table('643eee3d08618bbb92adf353', 'main')

// Callback style
Protobi.get_table('643eee3d08618bbb92adf353', 'main', function(err, rows) { ... })

// If datasetId is already set in Protobi.options:
let rows = await Protobi.get_table('main')
```

### `get_tables(datasetId, keys, callback)`

Fetches multiple tables in series. Returns an object keyed by table name.

```js
let data = await Protobi.get_tables('643eee3d08618bbb92adf353', ['main', 'lookup'])
data.main    // rows from main table
data.lookup  // rows from lookup table
```

### `get_elements(datasetId, callback)`

Fetches the element/dimension definitions for a dataset as a Tabular collection.

```js
let elements = await Protobi.get_elements('643eee3d08618bbb92adf353')
```

### `save_elements(datasetId, tabular, options, callback)`

Saves an element/dimension collection back to the server.

```js
await Protobi.save_elements('643eee3d08618bbb92adf353', tabular)
```

### `put_table(datasetId, key, csv, filename, callback)`

Uploads a data table to the server. Accepts either a CSV string or an array of row objects (will encode automatically).

```js
await Protobi.put_table('643eee3d08618bbb92adf353', 'main', rows, 'main.csv')
```

### `put_table_async(datasetId, key, csv, filename)`

Async version of `put_table` that encodes CSV with yielding. Always returns a Promise.

```js
await Protobi.put_table_async('643eee3d08618bbb92adf353', 'main', rows, 'main.csv')
```

---

## Joining and merging

### `left_join(left_rows, right_rows, merge_keys, fields)`

Merges columns from `right_rows` into `left_rows` by matching keys. All left rows are preserved; right rows with no match are dropped.

`merge_keys` can be:
- An array of shared key names: `['id']` or `['wave', 'respondentId']`
- A string: `'id'`
- An object mapping left keys to right keys: `{ id: 'respondentId', wave: 'Wave' }`

`fields` can be:
- An array of right-side column names to copy over
- An object mapping right names to left names: `{ score_r: 'score' }`
- Omitted — copies all right-side columns

```js
let merged = Protobi.left_join(
  [{ id: '1', name: 'Alice' }, { id: '2', name: 'Bob' }],
  [{ id: '1', score: 85 },    { id: '3', score: 72 }],
  ['id'],
  ['score']
)
// => [{ id: '1', name: 'Alice', score: 85 }, { id: '2', name: 'Bob' }]
```

### `right_join(left_rows, right_rows, merge_keys, fields)`

Similar to `left_join` but updates values in `left_rows` from matching `right_rows`. Right-only rows are ignored.

### `stack_rows(datasets, fields)`

Concatenates multiple arrays of rows into one. Optionally restricts to a subset of columns.

```js
let all = Protobi.stack_rows([wave1, wave2, wave3])
let slim = Protobi.stack_rows([wave1, wave2], ['id', 'q1', 'q2'])
```

---

## Comparing tables

### `compare_tables(left, right, options)`

Compares two datasets and reports structural and cell-level differences. Useful for validating incoming data against a baseline.

Options:
- `id` — array of column names that uniquely identify a row (default: positional match by index)
- `ignoreCase` — case-insensitive column name and value comparison
- `format` — `'json'` (default) or `'markdown'`
- `limit` — max column names shown in markdown output (default 10)

```js
let result = Protobi.compare_tables(baseline, updated, { id: ['respondentId'] })
```

**JSON result:**
```js
{
  columns: {
    both:  ['id', 'age', 'region'],   // in both datasets
    left:  ['old_field'],             // only in baseline
    right: ['new_field']              // only in updated
  },
  rows: {
    both:  [{ key: '123', left: {...}, right: {...} }],  // matched rows
    left:  [{ key: '456', row: {...} }],                 // deleted rows
    right: [{ key: '789', row: {...} }]                  // added rows
  },
  cells: {
    same:    1402,   // value unchanged (including both blank)
    changed:   18,   // value present on both sides but different
    filled:     5,   // was blank, now has a value
    emptied:    2    // had a value, now blank
  },
  byColumn: {
    age:    { same: 398, changed: 12, filled: 1, emptied: 0 },
    region: { same: 400, changed: 0, filled: 0, emptied: 3 }
  }
}
```

**Markdown result** (`format: 'markdown'`):

```js
let md = Protobi.compare_tables(baseline, updated, {
  id: ['respondentId'],
  format: 'markdown',
  limit: 5
})
console.log(md)
```

```
## Table comparison

| | Left | Right | Both |
|---|---|---|---|
| Columns | 1 | 2 | 12 |
| Rows | 4 | 6 | 394 |

### Columns only in right (2)
`tier`, `region_v2`

### Cell comparison (shared rows x shared columns)
| | Count |
|---|---|
| Same | 4,521 |
| Changed | 18 |
| Filled (was blank) | 5 |
| Emptied (now blank) | 2 |

### Columns with most changes
| Column | Changed | Filled | Emptied |
|---|---|---|---|
| `age` | 12 | 1 | 0 |
| `region` | 0 | 0 | 3 |
```

---

## Analysis

### `calculate_rim_weights(protobi, weightcol, targets)`

Calculates respondent-level rim (raking) weights so that one or more variables match specified marginal distributions. Runs up to 30 iterations.

```js
Protobi.calculate_rim_weights(protobi, 'weight', {
  gender: { '1': 0.52, '2': 0.48 },
  region: { 'East': 0.30, 'West': 0.35, 'Central': 0.35 }
})
```

Each respondent gets a `weight` column. Weights are normalized so their mean is 1.0.

### `temper_shares(row, prefix, temper_factors, distribution_array, redistribution_array, temper_array, scale_array)`

Adjusts market share forecasts by tempering stated intent upward (new products) and redistributing the reduction to existing products proportional to their baseline share. See [Protobi help: tempering market share data](https://help.protobi.com/process-data/tempering-market-share-data).

### `temper(prior, next, epsilon)`

Lower-level helper used by `temper_shares`. Given prior shares, stated next shares, and per-item epsilon tempering factors, returns adjusted shares that still sum to 1.

### `convert_elements(elements_v3, elements_v4, filter, initialize, options)`

Converts a v3 element/dimension collection to v4 format. Used during dataset schema migrations.

---

## EJS + SQL helpers

These functions are used inside EJS templates that generate SQL queries (typically in data pipeline scripts). They are not general JavaScript utilities.

### Background

Protobi datasets backed by PostgreSQL use EJS templates to generate SQL dynamically. A `params` object defines the schema: column names, data types, per-wave substitutions, and net variable definitions. The functions below expand that schema into SQL fragments.

### `expand_fields(options, params)`

Generates the `SELECT` field list for a SQL query. Applies type casts (`::VARCHAR`), substitution rules, and net variable expressions.

```js
let select = Protobi.expand_fields({ key: 'wave1', year: 2024 }, params)
// => "id::VARCHAR, \n age::INTEGER, \n q1::VARCHAR AS brand_awareness"
```

### `define_fields(options, params)`

Generates the field list for a `CREATE TABLE` statement — column name plus SQL type, no expressions.

```js
let cols = Protobi.define_fields(params)
// => "id VARCHAR, \n age INTEGER, \n q1 VARCHAR"
```

### `process_fields(options, params, mode)`

Underlying implementation for both `expand_fields` and `define_fields`. `mode` is `'expand'` (SELECT) or `'define'` (CREATE TABLE).

### `expand_fields_meta(params, options)`

Variant of `expand_fields` that produces metadata-annotated expressions (marks type casts with `::!` instead of `::`). Used by the element metadata system.

### `generate_net_sql(spec, args)`

Generates a SQL CASE expression for a net variable — a derived column that summarizes responses across a set of sub-columns.

Supports two transforms:
- `sum` — counts how many sub-columns match target values
- `any` — 1 if any sub-column matches, 0 if none, NULL if all are NULL

### `transform`

Object containing transform handlers (`any`, `sum`, `expression`, `coalesce`, `window`) called by `process_fields` when a substitution rule specifies a `transform` property.

Publishing

Date

Status

Slug edit

Content

Thumbnail

Categories Manage

Tools

AI Database

Publishing

Content

Tools

Danger zone