DEV Community

Cover image for Quick data munging: group, sum and bucket a CSV
JSLab
JSLab

Posted on

Quick data munging: group, sum and bucket a CSV

#javascript #typescript #data #beginners

Someone sends you a CSV and a question: "totals by region? and by week?" It's not worth a notebook, a database or a project. It's worth twenty lines.

The data 

const csv = `date,region,product,amount
2026-10-05,EU,Pro,120
2026-10-06,US,Basic,80
2026-10-06,EU,Basic,45.5
2026-10-12,APAC,Pro,210
2026-10-13,US,Pro,120`;
Enter fullscreen mode Exit fullscreen mode

Step 1: rows (the naive way) 

const [header, ...lines] = csv.trim().split("\n");
const keys = header.split(",");

const rows = lines.map((line) => {
  const cells = line.split(",");
  return Object.fromEntries(keys.map((k, i) => [k, cells[i]]));
});
Enter fullscreen mode Exit fullscreen mode

Fine for this file. Hold that thought.

Step 2: totals by region with Object.groupBy

Object.groupBy (ES2024) does the bucketing that used to need a reduce:

const byRegion = Object.groupBy(rows, (r) => r.region);

const totals = Object.fromEntries(
  Object.entries(byRegion).map(([region, rs]) => [
    region,
    rs.reduce((sum, r) => sum + Number(r.amount), 0),
  ]),
);
// { EU: 165.5, US: 200, APAC: 210 }
Enter fullscreen mode Exit fullscreen mode

Note the Number(...): everything from a CSV is a string, and "120" + "45.5" is not a sum.

Step 3: use a real parser

The naive split breaks on the first quoted field: 2026-10-07,EU,"Pro, annual",400 has four commas. Don't fix that with a regex. Use a parser:

import Papa from "papaparse";

const { data, errors } = Papa.parse(csv, {
  header: true,
  dynamicTyping: true, // "120" → 120
  skipEmptyLines: true,
});
Enter fullscreen mode Exit fullscreen mode

data is the same array of objects, with quoted fields handled and amount already a number. Check errors before you trust it.

Step 4: by week, without the timezone bug 

import { parseISO, startOfWeek, format } from "date-fns";

const weekOf = (date) =>
  format(startOfWeek(parseISO(date), { weekStartsOn: 1 }), "yyyy-MM-dd");

const byWeek = Object.groupBy(data, (r) => weekOf(r.date));
Object.entries(byWeek).map(([week, rs]) => ({
  week,
  total: rs.reduce((sum, r) => sum + r.amount, 0),
}));
// [{ week: "2026-10-05", total: 245.5 }, { week: "2026-10-12", total: 330 }]
Enter fullscreen mode Exit fullscreen mode

Why parseISO and not new Date("2026-10-05")? A date-only string passed to Date is parsed as UTC midnight. West of Greenwich, that's still Sunday evening, and your Monday sale lands in the previous week. parseISO reads it as local midnight.

Doing it in a scratchpad

I do this kind of thing in JSLab, a JavaScript & TypeScript scratchpad for macOS:

  • ⌘I opens the package sheet. Add papaparse and date-fns once and every tab can import them. Or just write the import: JSLab underlines it and offers to install it as a quick fix.
  • Auto Log shows each step's value beside its line, so I see totals without logging it.
  • An array of records, like data above, gets a Tree / Table switch in the output. As a table I can sort it, filter it and copy it as CSV, JSON or Markdown, which is usually what the person asking actually wanted.

For the real file, set the tab's working directory to its folder and read it:

import { readFile } from "node:fs/promises";
const csv = await readFile("./sales.csv", "utf8");
Enter fullscreen mode Exit fullscreen mode

JSLab launches Nov 15. Tables and npm packages: https://js-lab.app/docs/output

Top comments (0)