CSV Statistical Analyzer (javascript, written by Codex)

Codex wrote this javascript project from the task below. It installed and ran on a clean Ubuntu 22.04 machine as written.

Task given to the agent:

TASK: CSV Statistical Analyzer

Write a program that reads a CSV file and performs comprehensive statistical analysis on every numeric column. It should handle real-world messy data — missing values, mixed types, malformed rows — and produce both a human-readable console report and a machine-readable JSON output.

FUNCTIONAL REQUIREMENTS:
- Accept a CSV file path as a command-line argument
- Auto-detect which columns are numeric vs categorical
- For each numeric column compute: mean, median, standard deviation, variance, min, max, 25th/50th/75th percentiles, and non-missing value count
- Detect outliers using the IQR method (values below Q1 - 1.5*IQR or above Q3 + 1.5*IQR) and list them per column
- For each categorical column compute: unique count, most frequent value, and top 10 value frequencies
- Print a formatted summary table to the console with aligned columns
- Save the complete analysis to report.json including all stats, outlier details, and column type classifications
- If no input file is given, generate a sample CSV with at least 200 rows across 5 numeric and 2 categorical columns, then analyze it
- Handle gracefully: empty files, header-only files, columns with all missing values, single-row files, quoted fields containing commas

Create a complete JavaScript project for a clean Ubuntu 22.04 machine with only Node.js 20+ (LTS) installed. Include:
- Source code
- package.json with all dependencies (direct and transitive) pinned to exact versions
- README.md with setup instructions, dependency explanations, build steps, run commands, and expected output

[]

{
  "name": "csv-statistical-analyzer",
  "version": "1.0.0",
  "lockfileVersion": 3,
  "requires": true,
  "packages": {
    "": {
      "name": "csv-statistical-analyzer",
      "version": "1.0.0"
    }
  }
}

{
  "name": "csv-statistical-analyzer",
  "version": "1.0.0",
  "description": "CLI tool that analyzes CSV files with numeric and categorical statistics.",
  "type": "module",
  "main": "src/index.js",
  "scripts": {
    "start": "node src/index.js",
    "analyze": "node src/index.js"
  },
  "engines": {
    "node": ">=20.0.0"
  },
  "dependencies": {},
  "devDependencies": {}
}

# CSV Statistical Analyzer (Node.js)

A robust CSV analysis CLI that detects numeric vs categorical columns, computes summary statistics, detects IQR outliers, prints aligned console tables, and writes a machine-readable `report.json`.

## Features

- Accepts a CSV file path as a command-line argument.
- If no input path is given, generates a sample CSV (`sample.csv`) with 250 rows, 5 numeric columns, and 2 categorical columns, then analyzes it.
- Auto-detects column types (`numeric` or `categorical`) from real data.
- Numeric stats per column:
  - `mean`, `median`, `standardDeviation`, `variance`, `min`, `max`
  - percentiles `p25`, `p50`, `p75`
  - non-missing numeric value count
  - outlier detection with IQR method (`Q1 - 1.5*IQR`, `Q3 + 1.5*IQR`)
- Categorical stats per column:
  - unique value count
  - most frequent value
  - top 10 frequencies
- Handles messy CSV input:
  - missing values
  - mixed types in columns
  - malformed row lengths (padded/truncated, recorded in report)
  - empty files and header-only files
  - quoted fields containing commas
- Writes full JSON output to `report.json`.

## Requirements

- Ubuntu 22.04
- Node.js 20+ (LTS)

## Dependencies (Pinned)

This project intentionally uses only Node.js built-in modules (`node:fs/promises`, `node:path`).

- Direct dependencies: none
- Transitive dependencies: none

Pinning status:
- `package.json` contains exact project metadata and no floating dependency versions.
- `package-lock.json` is included and lockfile version is pinned.

## Setup

```bash
cd TMLR/code_generation/codex_generated/p_01/javascript
npm install
```

`npm install` will complete quickly because there are no external packages.

## Run

Analyze an existing CSV file:

```bash
npm run analyze -- ./your_file.csv
```

or

```bash
node src/index.js ./your_file.csv
```

Generate sample data and analyze it:

```bash
npm start
```

## Output Files

- Console output: aligned summary tables for numeric and categorical columns, plus outlier details.
- JSON output: `report.json` in the current working directory.
- When no input is provided: generated sample file `sample.csv` in the current working directory.

## Expected Console Output (Example Shape)

```text
CSV Statistical Analyzer
========================
Input File: /path/to/sample.csv
Generated : 2026-03-02T10:00:00.000Z
Columns   : 7
Data Rows : 250
Malformed : 0

Numeric Columns
Column        | Count | Mean  | Median | StdDev | Variance | Min   | P25   | P50   | P75   | Max    | Outliers
--------------+-------+-------+--------+--------+----------+-------+-------+-------+-------+--------+---------
revenue_usd   | 250   | ...   | ...    | ...    | ...      | ...   | ...   | ...   | ...   | ...    | 5
...

Categorical Columns
Column   | NonMissing | Unique | Most Frequent | Top Values (up to 3 shown)
---------+------------+--------+---------------+------------------------------
region   | 250        | 5      | East          | East (50), North (50), ...
...

Outlier Details (IQR Method)
- revenue_usd: row 58 -> 52000.7, row 115 -> 49010.2
- latency_ms: none

Saved JSON report to /path/to/report.json
```

## Notes on Statistical Choices

- Variance and standard deviation are computed as sample statistics (`n - 1` denominator) when `n > 1`.
- Percentiles use linear interpolation on sorted values.
- For numeric detection, a column is treated as numeric when at least 80% of its non-missing values parse as valid numbers.

#!/usr/bin/env node
import fs from 'node:fs/promises';
import path from 'node:path';

const DEFAULT_SAMPLE_ROWS = 250;
const MISSING_TOKENS = new Set(['', 'na', 'n/a', 'null', 'none', 'nan', 'undefined', '-']);

function isMissing(value) {
  if (value === null || value === undefined) {
    return true;
  }

  const normalized = String(value).trim().toLowerCase();
  return MISSING_TOKENS.has(normalized);
}

function parsePotentialNumber(value) {
  const text = String(value).trim();
  if (text.length === 0) {
    return { ok: false, value: null };
  }

  if (/^[-+]?(?:\d+(?:\.\d+)?|\.\d+)(?:[eE][-+]?\d+)?$/.test(text)) {
    const parsed = Number(text);
    return Number.isFinite(parsed) ? { ok: true, value: parsed } : { ok: false, value: null };
  }

  if (/^[-+]?\d{1,3}(?:,\d{3})+(?:\.\d+)?$/.test(text)) {
    const parsed = Number(text.replace(/,/g, ''));
    return Number.isFinite(parsed) ? { ok: true, value: parsed } : { ok: false, value: null };
  }

  return { ok: false, value: null };
}

function parseCsv(content) {
  const rows = [];
  let row = [];
  let field = '';
  let inQuotes = false;

  const pushField = () => {
    row.push(field);
    field = '';
  };

  const pushRow = () => {
    rows.push(row);
    row = [];
  };

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

    if (inQuotes) {
      if (char === '"') {
        if (content[i + 1] === '"') {
          field += '"';
          i += 1;
        } else {
          inQuotes = false;
        }
      } else {
        field += char;
      }
      continue;
    }

    if (char === '"') {
      if (field.length === 0) {
        inQuotes = true;
      } else {
        field += char;
      }
      continue;
    }

    if (char === ',') {
      pushField();
      continue;
    }

    if (char === '\n') {
      pushField();
      pushRow();
      continue;
    }

    if (char === '\r') {
      pushField();
      pushRow();
      if (content[i + 1] === '\n') {
        i += 1;
      }
      continue;
    }

    field += char;
  }

  if (field.length > 0 || row.length > 0 || content.endsWith(',')) {
    pushField();
    pushRow();
  }

  return rows;
}

function uniqueHeaders(rawHeaders) {
  const seen = new Map();
  return rawHeaders.map((header, index) => {
    const cleaned = String(header ?? '').replace(/^\uFEFF/, '').trim() || `column_${index + 1}`;
    const current = seen.get(cleaned) ?? 0;
    seen.set(cleaned, current + 1);
    return current === 0 ? cleaned : `${cleaned}_${current + 1}`;
  });
}

function normalizeRows(rows) {
  if (rows.length === 0) {
    return {
      headers: [],
      dataRows: [],
      malformedRows: [],
      skippedBlankRows: 0,
      sourceRowCount: 0
    };
  }

  const headers = uniqueHeaders(rows[0]);
  const expectedColumns = headers.length;
  const malformedRows = [];
  const dataRows = [];
  let skippedBlankRows = 0;

  for (let i = 1; i < rows.length; i += 1) {
    const current = rows[i];
    const allBlank = current.length === 0 || current.every((value) => isMissing(value));

    if (allBlank) {
      skippedBlankRows += 1;
      continue;
    }

    if (current.length !== expectedColumns) {
      malformedRows.push({
        rowNumber: i + 1,
        expectedColumns,
        actualColumns: current.length,
        action: current.length < expectedColumns ? 'padded_with_missing' : 'truncated_extra_columns'
      });
    }

    const normalized = current.slice(0, expectedColumns);
    while (normalized.length < expectedColumns) {
      normalized.push('');
    }

    dataRows.push({
      rowNumber: i + 1,
      values: normalized
    });
  }

  return {
    headers,
    dataRows,
    malformedRows,
    skippedBlankRows,
    sourceRowCount: rows.length
  };
}

function percentile(sortedValues, p) {
  if (sortedValues.length === 0) {
    return null;
  }

  if (sortedValues.length === 1) {
    return sortedValues[0];
  }

  const rank = (p / 100) * (sortedValues.length - 1);
  const lower = Math.floor(rank);
  const upper = Math.ceil(rank);

  if (lower === upper) {
    return sortedValues[lower];
  }

  const weight = rank - lower;
  return sortedValues[lower] * (1 - weight) + sortedValues[upper] * weight;
}

function detectColumnType(values) {
  const nonMissing = [];
  let numericCount = 0;

  for (const value of values) {
    if (isMissing(value)) {
      continue;
    }

    nonMissing.push(value);
    if (parsePotentialNumber(value).ok) {
      numericCount += 1;
    }
  }

  const nonMissingCount = nonMissing.length;
  const numericRatio = nonMissingCount === 0 ? 0 : numericCount / nonMissingCount;
  const type = nonMissingCount > 0 && numericRatio >= 0.8 ? 'numeric' : 'categorical';

  return {
    type,
    nonMissingCount,
    numericCount,
    numericRatio,
    allValuesMissing: nonMissingCount === 0
  };
}

function analyzeNumericColumn(name, columnEntries, typeInfo) {
  let missingCount = 0;
  let invalidCount = 0;
  const numericEntries = [];

  for (const entry of columnEntries) {
    if (isMissing(entry.value)) {
      missingCount += 1;
      continue;
    }

    const parsed = parsePotentialNumber(entry.value);
    if (parsed.ok) {
      numericEntries.push({ rowNumber: entry.rowNumber, value: parsed.value, raw: entry.value });
    } else {
      invalidCount += 1;
    }
  }

  const numericValues = numericEntries.map((entry) => entry.value).sort((a, b) => a - b);
  const count = numericValues.length;

  if (count === 0) {
    return {
      column: name,
      type: 'numeric',
      typeConfidence: Number(typeInfo.numericRatio.toFixed(4)),
      nonMissingValueCount: 0,
      missingValueCount: missingCount,
      invalidNumericValueCount: invalidCount,
      mean: null,
      median: null,
      standardDeviation: null,
      variance: null,
      min: null,
      max: null,
      percentiles: { p25: null, p50: null, p75: null },
      outliers: {
        lowerBound: null,
        upperBound: null,
        iqr: null,
        count: 0,
        values: []
      }
    };
  }

  const sum = numericValues.reduce((acc, value) => acc + value, 0);
  const mean = sum / count;
  const q1 = percentile(numericValues, 25);
  const q2 = percentile(numericValues, 50);
  const q3 = percentile(numericValues, 75);
  const variance = count > 1
    ? numericValues.reduce((acc, value) => acc + (value - mean) ** 2, 0) / (count - 1)
    : 0;
  const standardDeviation = Math.sqrt(variance);
  const iqr = q3 - q1;
  const lowerBound = q1 - 1.5 * iqr;
  const upperBound = q3 + 1.5 * iqr;
  const outliers = numericEntries
    .filter((entry) => entry.value < lowerBound || entry.value > upperBound)
    .map((entry) => ({ rowNumber: entry.rowNumber, value: entry.value }));

  return {
    column: name,
    type: 'numeric',
    typeConfidence: Number(typeInfo.numericRatio.toFixed(4)),
    nonMissingValueCount: count,
    missingValueCount: missingCount,
    invalidNumericValueCount: invalidCount,
    mean,
    median: q2,
    standardDeviation,
    variance,
    min: numericValues[0],
    max: numericValues[numericValues.length - 1],
    percentiles: {
      p25: q1,
      p50: q2,
      p75: q3
    },
    outliers: {
      lowerBound,
      upperBound,
      iqr,
      count: outliers.length,
      values: outliers
    }
  };
}

function analyzeCategoricalColumn(name, columnEntries, typeInfo) {
  let missingCount = 0;
  const frequencies = new Map();

  for (const entry of columnEntries) {
    if (isMissing(entry.value)) {
      missingCount += 1;
      continue;
    }

    const normalized = String(entry.value).trim();
    frequencies.set(normalized, (frequencies.get(normalized) ?? 0) + 1);
  }

  const sorted = [...frequencies.entries()]
    .map(([value, count]) => ({ value, count }))
    .sort((a, b) => {
      if (b.count !== a.count) {
        return b.count - a.count;
      }
      return a.value.localeCompare(b.value);
    });

  return {
    column: name,
    type: 'categorical',
    typeConfidence: Number((1 - typeInfo.numericRatio).toFixed(4)),
    nonMissingValueCount: columnEntries.length - missingCount,
    missingValueCount: missingCount,
    uniqueCount: frequencies.size,
    mostFrequentValue: sorted.length > 0 ? sorted[0].value : null,
    topFrequencies: sorted.slice(0, 10)
  };
}

function analyzeData(headers, dataRows) {
  const columnEntries = new Map();
  for (const header of headers) {
    columnEntries.set(header, []);
  }

  for (const row of dataRows) {
    headers.forEach((header, index) => {
      columnEntries.get(header).push({
        rowNumber: row.rowNumber,
        value: row.values[index]
      });
    });
  }

  const columnClassifications = {};
  const numericColumns = {};
  const categoricalColumns = {};

  for (const [header, entries] of columnEntries.entries()) {
    const values = entries.map((entry) => entry.value);
    const typeInfo = detectColumnType(values);

    columnClassifications[header] = {
      type: typeInfo.type,
      nonMissingCount: typeInfo.nonMissingCount,
      numericValueCount: typeInfo.numericCount,
      numericRatio: Number(typeInfo.numericRatio.toFixed(4)),
      allValuesMissing: typeInfo.allValuesMissing
    };

    if (typeInfo.type === 'numeric') {
      numericColumns[header] = analyzeNumericColumn(header, entries, typeInfo);
    } else {
      categoricalColumns[header] = analyzeCategoricalColumn(header, entries, typeInfo);
    }
  }

  return {
    columnClassifications,
    numericColumns,
    categoricalColumns
  };
}

function formatNumber(value) {
  if (value === null || value === undefined || Number.isNaN(value)) {
    return 'N/A';
  }

  if (!Number.isFinite(value)) {
    return String(value);
  }

  if (value === 0) {
    return '0';
  }

  const abs = Math.abs(value);
  if (abs >= 1_000_000 || abs < 0.0001) {
    return value.toExponential(4);
  }

  const fixed = abs >= 1000 ? value.toFixed(2) : value.toFixed(4);
  return fixed.replace(/\.0+$/, '').replace(/(\.\d*?)0+$/, '$1');
}

function printTable(title, columns, rows) {
  console.log(`\n${title}`);

  if (rows.length === 0) {
    console.log('  (none)');
    return;
  }

  const widths = columns.map((column) => column.length);
  for (const row of rows) {
    row.forEach((cell, index) => {
      widths[index] = Math.max(widths[index], String(cell).length);
    });
  }

  const render = (cells) => cells.map((cell, index) => String(cell).padEnd(widths[index], ' ')).join(' | ');
  console.log(render(columns));
  console.log(widths.map((width) => '-'.repeat(width)).join('-+-'));
  rows.forEach((row) => console.log(render(row)));
}

function summarizeForConsole(report) {
  console.log('CSV Statistical Analyzer');
  console.log('========================');
  console.log(`Input File: ${report.metadata.inputFile}`);
  console.log(`Generated : ${report.metadata.generatedAt}`);
  console.log(`Columns   : ${report.metadata.totalColumns}`);
  console.log(`Data Rows : ${report.metadata.dataRowCount}`);
  console.log(`Malformed : ${report.metadata.malformedRowCount}`);

  if (report.warnings.length > 0) {
    console.log('\nWarnings:');
    report.warnings.forEach((warning) => console.log(`- ${warning}`));
  }

  const numericRows = Object.values(report.numericColumns).map((column) => [
    column.column,
    String(column.nonMissingValueCount),
    formatNumber(column.mean),
    formatNumber(column.median),
    formatNumber(column.standardDeviation),
    formatNumber(column.variance),
    formatNumber(column.min),
    formatNumber(column.percentiles.p25),
    formatNumber(column.percentiles.p50),
    formatNumber(column.percentiles.p75),
    formatNumber(column.max),
    String(column.outliers.count)
  ]);

  printTable(
    'Numeric Columns',
    ['Column', 'Count', 'Mean', 'Median', 'StdDev', 'Variance', 'Min', 'P25', 'P50', 'P75', 'Max', 'Outliers'],
    numericRows
  );

  const categoricalRows = Object.values(report.categoricalColumns).map((column) => {
    const topPreview = column.topFrequencies
      .slice(0, 3)
      .map((entry) => `${entry.value} (${entry.count})`)
      .join(', ');

    return [
      column.column,
      String(column.nonMissingValueCount),
      String(column.uniqueCount),
      column.mostFrequentValue ?? 'N/A',
      topPreview || 'N/A'
    ];
  });

  printTable(
    'Categorical Columns',
    ['Column', 'NonMissing', 'Unique', 'Most Frequent', 'Top Values (up to 3 shown)'],
    categoricalRows
  );

  console.log('\nOutlier Details (IQR Method)');
  const numericColumns = Object.values(report.numericColumns);
  if (numericColumns.length === 0) {
    console.log('  No numeric columns were detected.');
    return;
  }

  for (const column of numericColumns) {
    if (column.outliers.count === 0) {
      console.log(`- ${column.column}: none`);
      continue;
    }

    const items = column.outliers.values
      .map((entry) => `row ${entry.rowNumber} -> ${formatNumber(entry.value)}`)
      .join(', ');
    console.log(`- ${column.column}: ${items}`);
  }
}

function csvEscape(value) {
  const text = String(value ?? '');
  if (/[",\n\r]/.test(text)) {
    return `"${text.replace(/"/g, '""')}"`;
  }
  return text;
}

function randomNormal(mean, stdDev) {
  const u1 = Math.max(Math.random(), Number.EPSILON);
  const u2 = Math.max(Math.random(), Number.EPSILON);
  const z = Math.sqrt(-2 * Math.log(u1)) * Math.cos(2 * Math.PI * u2);
  return mean + z * stdDev;
}

async function generateSampleCsv(filePath, rowCount = DEFAULT_SAMPLE_ROWS) {
  const headers = [
    'temperature_c',
    'revenue_usd',
    'latency_ms',
    'quality_score',
    'units_sold',
    'region',
    'segment'
  ];

  const regions = ['North', 'South', 'East', 'West', 'New York, NY'];
  const segments = ['Consumer', 'Enterprise', 'SMB', 'Public Sector'];
  const lines = [headers.join(',')];

  for (let i = 0; i < rowCount; i += 1) {
    let temperature = randomNormal(20, 7);
    let revenue = randomNormal(15000, 3500);
    let latency = randomNormal(120, 35);
    let quality = Math.min(100, Math.max(0, randomNormal(82, 9)));
    let units = Math.max(0, Math.round(randomNormal(240, 80)));

    if (i % 57 === 0) {
      revenue *= 3.5;
    }

    if (i % 41 === 0) {
      latency = 'timeout';
    }

    if (i % 23 === 0) {
      quality = '';
    }

    if (i % 37 === 0) {
      units = '1,250';
    }

    const region = regions[i % regions.length];
    const segment = segments[(i * 3) % segments.length];

    const values = [
      Number.isFinite(temperature) ? temperature.toFixed(3) : temperature,
      Number.isFinite(revenue) ? revenue.toFixed(2) : revenue,
      Number.isFinite(latency) ? latency.toFixed(3) : latency,
      Number.isFinite(quality) ? quality.toFixed(2) : quality,
      units,
      region,
      segment
    ];

    lines.push(values.map(csvEscape).join(','));
  }

  await fs.writeFile(filePath, `${lines.join('\n')}\n`, 'utf8');
}

async function run() {
  const inputPathArg = process.argv[2];
  let inputPath = inputPathArg ? path.resolve(process.cwd(), inputPathArg) : null;

  if (!inputPath) {
    inputPath = path.resolve(process.cwd(), 'sample.csv');
    await generateSampleCsv(inputPath, DEFAULT_SAMPLE_ROWS);
    console.log(`No input file provided. Generated sample dataset at ${inputPath}`);
  }

  let content = '';
  try {
    content = await fs.readFile(inputPath, 'utf8');
  } catch (error) {
    console.error(`Failed to read input file: ${inputPath}`);
    console.error(error.message);
    process.exitCode = 1;
    return;
  }

  const report = {
    metadata: {
      generatedAt: new Date().toISOString(),
      inputFile: inputPath,
      totalColumns: 0,
      sourceRowCount: 0,
      dataRowCount: 0,
      malformedRowCount: 0,
      skippedBlankRowCount: 0
    },
    columnClassifications: {},
    numericColumns: {},
    categoricalColumns: {},
    malformedRows: [],
    warnings: []
  };

  if (content.trim().length === 0) {
    report.warnings.push('The input file is empty. No rows were available for analysis.');
  } else {
    const parsedRows = parseCsv(content);
    const normalized = normalizeRows(parsedRows);

    report.metadata.totalColumns = normalized.headers.length;
    report.metadata.sourceRowCount = normalized.sourceRowCount;
    report.metadata.dataRowCount = normalized.dataRows.length;
    report.metadata.malformedRowCount = normalized.malformedRows.length;
    report.metadata.skippedBlankRowCount = normalized.skippedBlankRows;
    report.malformedRows = normalized.malformedRows;

    if (normalized.headers.length === 0) {
      report.warnings.push('No header row was detected.');
    } else if (normalized.dataRows.length === 0) {
      report.warnings.push('Header detected, but no data rows were found.');
    }

    const analyzed = analyzeData(normalized.headers, normalized.dataRows);
    report.columnClassifications = analyzed.columnClassifications;
    report.numericColumns = analyzed.numericColumns;
    report.categoricalColumns = analyzed.categoricalColumns;
  }

  summarizeForConsole(report);

  const outputPath = path.resolve(process.cwd(), 'report.json');
  await fs.writeFile(outputPath, `${JSON.stringify(report, null, 2)}\n`, 'utf8');
  console.log(`\nSaved JSON report to ${outputPath}`);
}

run().catch((error) => {
  console.error('Unexpected failure while analyzing CSV data.');
  console.error(error);
  process.exitCode = 1;
});