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:

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 ''.

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.

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.

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.

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.

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.

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.

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.

// 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.

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.

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

save_elements(datasetId, tabular, options, callback)

Saves an element/dimension collection back to the server.

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).

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.

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

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.

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)

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

JSON result:

{
  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'):

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.

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.

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.

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.

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.