Profile numeric CSV/JSONL columns while streaming in Node.js, including count, missing/invalid values, min, max, mean, and standard deviation—with no dependencies.

node-profile-numeric-columns.js

#!/usr/bin/env node
/**
 * Profile numeric CSV/JSONL columns while streaming.
 *
 * Calculates:
 * - Valid numeric count
 * - Missing, empty, and invalid counts
 * - Minimum and maximum
 * - Sum and mean
 * - Population and sample standard deviation
 *
 * Uses Welford's online algorithm for numerically stable variance without
 * storing every value in memory.
 *
 * Usage:
 *   node node-profile-numeric-columns.js data.csv \
 *     --format=csv \
 *     --columns=age,score,revenue
 *
 *   cat data.jsonl | node node-profile-numeric-columns.js \
 *     --format=jsonl \
 *     --columns=age,metrics.score,metrics.revenue
 *
 * Options:
 *   --format=csv|jsonl   Input format (default: jsonl)
 *   --columns=a,b,c      Numeric columns to profile
 *   --delimiter=,        CSV delimiter (default: comma)
 *   --allow-infinity     Accept Infinity and -Infinity as numbers
 *
 * Notes:
 * - JSONL mode supports nested paths such as metrics.score.
 * - Empty strings, null, and undefined are not treated as zero.
 * - CSV input must contain a header row.
 * - No dependencies.
 */

const fs = require("fs");
const readline = require("readline");

function usage() {
  console.error(`Usage:
  node node-profile-numeric-columns.js <input.csv> \\
    --format=csv \\
    --columns=age,score,revenue

  cat input.jsonl | node node-profile-numeric-columns.js \\
    --format=jsonl \\
    --columns=age,metrics.score

Options:
  --format=csv|jsonl   Input format (default: jsonl)
  --columns=a,b,c      Numeric columns to profile
  --delimiter=,        CSV delimiter (default: ,)
  --allow-infinity     Accept Infinity and -Infinity
`);
}

function parseArgs(argv) {
  const args = {
    file: null,
    format: "jsonl",
    columns: null,
    delimiter: ",",
    allowInfinity: false,
    help: false,
  };

  for (const arg of argv.slice(2)) {
    if (!args.file && !arg.startsWith("--")) {
      args.file = arg;
      continue;
    }

    if (arg.startsWith("--format=")) {
      args.format = arg.slice("--format=".length).toLowerCase();
      continue;
    }

    if (arg.startsWith("--columns=")) {
      args.columns = arg
        .slice("--columns=".length)
        .split(",")
        .map((column) => column.trim())
        .filter(Boolean);
      continue;
    }

    if (arg.startsWith("--delimiter=")) {
      args.delimiter = decodeDelimiter(
        arg.slice("--delimiter=".length)
      );
      continue;
    }

    if (arg === "--allow-infinity") {
      args.allowInfinity = true;
      continue;
    }

    if (arg === "-h" || arg === "--help") {
      args.help = true;
      continue;
    }

    throw new Error(`Unknown argument: ${arg}`);
  }

  return args;
}

function decodeDelimiter(value) {
  const aliases = {
    "\\t": "\t",
    tab: "\t",
    comma: ",",
    pipe: "|",
    semicolon: ";",
  };

  return aliases[value.toLowerCase()] ?? value;
}

/**
 * Parse one CSV-like record.
 *
 * Supports:
 * - Quoted fields
 * - Delimiters inside quoted fields
 * - Escaped double quotes: ""
 *
 * This line-oriented parser does not support embedded newlines inside
 * quoted CSV fields.
 */
function parseDelimitedLine(line, delimiter = ",") {
  const fields = [];
  let current = "";
  let inQuotes = false;

  for (let i = 0; i < line.length; i += 1) {
    const char = line[i];

    if (inQuotes) {
      if (char === '"') {
        if (line[i + 1] === '"') {
          current += '"';
          i += 1;
        } else {
          inQuotes = false;
        }
      } else {
        current += char;
      }

      continue;
    }

    if (char === '"') {
      inQuotes = true;
      continue;
    }

    if (line.startsWith(delimiter, i)) {
      fields.push(current);
      current = "";
      i += delimiter.length - 1;
      continue;
    }

    current += char;
  }

  if (inQuotes) {
    throw new Error("Unclosed quoted field");
  }

  fields.push(current);
  return fields;
}

function getByPath(object, keyPath) {
  let current = object;

  for (const part of keyPath.split(".")) {
    if (
      current === null ||
      typeof current !== "object" ||
      !Object.prototype.hasOwnProperty.call(current, part)
    ) {
      return {
        exists: false,
        value: undefined,
      };
    }

    current = current[part];
  }

  return {
    exists: true,
    value: current,
  };
}

function createColumnStats() {
  return {
    total: 0,
    valid: 0,
    missing: 0,
    empty: 0,
    invalid: 0,
    min: null,
    max: null,
    sum: 0,

    // Welford's online variance state.
    mean: 0,
    m2: 0,
  };
}

function createStats(columns) {
  return Object.fromEntries(
    columns.map((column) => [column, createColumnStats()])
  );
}

function classifyNumber(rawValue, { exists, allowInfinity }) {
  if (!exists) {
    return { type: "missing" };
  }

  if (
    rawValue === null ||
    rawValue === undefined ||
    (typeof rawValue === "string" && rawValue.trim() === "")
  ) {
    return { type: "empty" };
  }

  if (typeof rawValue === "boolean") {
    return { type: "invalid" };
  }

  const number =
    typeof rawValue === "number"
      ? rawValue
      : Number(String(rawValue).trim());

  if (Number.isNaN(number)) {
    return { type: "invalid" };
  }

  if (!allowInfinity && !Number.isFinite(number)) {
    return { type: "invalid" };
  }

  return {
    type: "valid",
    value: number,
  };
}

function updateStats(columnStats, rawValue, options) {
  columnStats.total += 1;

  const result = classifyNumber(rawValue, options);

  if (result.type !== "valid") {
    columnStats[result.type] += 1;
    return;
  }

  const value = result.value;

  columnStats.valid += 1;
  columnStats.sum += value;

  if (columnStats.min === null || value < columnStats.min) {
    columnStats.min = value;
  }

  if (columnStats.max === null || value > columnStats.max) {
    columnStats.max = value;
  }

  // Welford's online algorithm.
  const delta = value - columnStats.mean;
  columnStats.mean += delta / columnStats.valid;
  const delta2 = value - columnStats.mean;
  columnStats.m2 += delta * delta2;
}

function roundNumber(value, decimalPlaces = 6) {
  if (value === null || !Number.isFinite(value)) {
    return value;
  }

  return Number(value.toFixed(decimalPlaces));
}

function buildReport(stats) {
  return Object.entries(stats).map(([column, values]) => {
    const populationVariance =
      values.valid > 0 ? values.m2 / values.valid : null;

    const sampleVariance =
      values.valid > 1 ? values.m2 / (values.valid - 1) : null;

    return {
      column,
      total_rows: values.total,
      valid_numbers: values.valid,
      missing: values.missing,
      empty: values.empty,
      invalid: values.invalid,
      numeric_pct:
        values.total === 0
          ? 0
          : roundNumber((values.valid / values.total) * 100, 2),
      min: values.min,
      max: values.max,
      sum: roundNumber(values.sum),
      mean:
        values.valid > 0
          ? roundNumber(values.mean)
          : null,
      population_stddev:
        populationVariance === null
          ? null
          : roundNumber(Math.sqrt(populationVariance)),
      sample_stddev:
        sampleVariance === null
          ? null
          : roundNumber(Math.sqrt(sampleVariance)),
    };
  });
}

async function runCSV(input, args) {
  const reader = readline.createInterface({
    input,
    crlfDelay: Infinity,
  });

  const stats = createStats(args.columns);

  let headers = null;
  let columnIndexes = null;
  let physicalLineNumber = 0;

  for await (const line of reader) {
    physicalLineNumber += 1;

    if (!line.trim()) {
      continue;
    }

    let fields;

    try {
      fields = parseDelimitedLine(line, args.delimiter);
    } catch (error) {
      throw new Error(
        `Invalid delimited record on line ${physicalLineNumber}: ${error.message}`
      );
    }

    if (headers === null) {
      headers = fields.map((header) => header.trim());

      const duplicateHeaders = headers.filter(
        (header, index) => headers.indexOf(header) !== index
      );

      if (duplicateHeaders.length > 0) {
        throw new Error(
          `Duplicate CSV header(s): ${[...new Set(duplicateHeaders)].join(", ")}`
        );
      }

      columnIndexes = Object.fromEntries(
        args.columns.map((column) => [
          column,
          headers.indexOf(column),
        ])
      );

      continue;
    }

    for (const column of args.columns) {
      const index = columnIndexes[column];
      const exists = index !== -1 && index < fields.length;
      const value = exists ? fields[index] : undefined;

      updateStats(stats[column], value, {
        exists,
        allowInfinity: args.allowInfinity,
      });
    }
  }

  if (headers === null) {
    throw new Error("No CSV header row found");
  }

  return stats;
}

async function runJSONL(input, args) {
  const reader = readline.createInterface({
    input,
    crlfDelay: Infinity,
  });

  const stats = createStats(args.columns);
  let lineNumber = 0;

  for await (const line of reader) {
    lineNumber += 1;

    if (!line.trim()) {
      continue;
    }

    let record;

    try {
      record = JSON.parse(line);
    } catch (error) {
      throw new Error(
        `Invalid JSON on line ${lineNumber}: ${error.message}`
      );
    }

    if (
      record === null ||
      typeof record !== "object" ||
      Array.isArray(record)
    ) {
      throw new Error(
        `Invalid JSONL record on line ${lineNumber}: expected an object`
      );
    }

    for (const column of args.columns) {
      const result = getByPath(record, column);

      updateStats(stats[column], result.value, {
        exists: result.exists,
        allowInfinity: args.allowInfinity,
      });
    }
  }

  return stats;
}

async function main() {
  const args = parseArgs(process.argv);

  if (args.help) {
    usage();
    process.exit(0);
  }

  if (!["csv", "jsonl"].includes(args.format)) {
    throw new Error("--format must be csv or jsonl");
  }

  if (!args.columns || args.columns.length === 0) {
    throw new Error("--columns must contain at least one column");
  }

  if (!args.delimiter) {
    throw new Error("--delimiter cannot be empty");
  }

  const input = args.file
    ? fs.createReadStream(args.file, {
        encoding: "utf8",
      })
    : process.stdin;

  const stats =
    args.format === "csv"
      ? await runCSV(input, args)
      : await runJSONL(input, args);

  console.log(JSON.stringify(buildReport(stats), null, 2));
}

main().catch((error) => {
  console.error(
    "ERROR:",
    error && error.stack ? error.stack : error
  );
  process.exit(1);
});