Group and aggregate with the choices stated: what count counts, how group keys are matched, and which values were not numbers
Initializing in your browser…
View, sort and edit a CSV, with an undo stack that goes back exactly one step and a parse report that tells a broken quote from a ragged row
Generate SQL that runs: identifiers quoted rather than stripped, values escaped per dialect, and every change to your data reported
Convert both ways, converting a type only when it reads back the same, and naming every value that cannot
Sales by region and quarter, from a file that has one unreadable amount and one row with no region.
Input
region, quarter, amount (7 rows; one amount is "n/a", one region is blank)
Sum
Q1 Q2 Total North 150 150 300 South 200 250 450 (empty) 40 40 Total 390 400 790 1 value could not be read as a number: "n/a"
South Q1 sums to 200 rather than partly counting the "n/a", and the tool says so above the table instead of leaving it silent. The blank region is its own group and stays distinct from a row whose region is literally the text (empty). Switch to Average and the South total is 225, the mean of 200 and 250, because totals are aggregated over the underlying values rather than over the cells above them.
Pick a row field, a column field, a value field and an aggregation, and get a cross-tabulation with row, column and grand totals. The arithmetic in a pivot table is easy to get right; what is easy to get wrong is everything around it, so this one says what Count counts, how two group keys are decided to be the same, and which values it could not read as numbers.
Measured against the same aggregation written from its definition in Python, the previous version of this tool got every cell and every total right for sum, count, average, median, minimum and maximum on a clean file. Every fault was a decision nobody had made out loud, and each of them changes a number a reader will act on.
Values were read with parseFloat, which reads a PREFIX and stops. parseFloat("12abc") is 12, and parseFloat("1,234") is 1 because it stops at the comma. A group holding 12abc, 1,234 and 100 therefore summed to 113, where only one of the three is a number and the correct sum is 100. Nothing on screen said a value had been partly read. Values now go through a stated decimal grammar, nothing is partly counted, and the table reports how many values it could not read and shows examples.
Count counted empty cells. A group holding 100, a blank and 200 counted 3, and a group whose only cell was blank counted 1. "How many" has two answers, rows and values, and both are now offered under separate names, each saying which it means.
An empty group key became the literal text "(empty)", which is a string a value can also hold, so a row whose key really was (empty) merged into the same group. The empty group is now tracked as a flag rather than as a placeholder, so the two stay apart, and it sorts last.
Group keys sorted lexicographically, so numeric keys came out 1, 10, 2. They now sort numerically when they are numbers and as text when they are not. And whether North, north and "North " are one group or three is now a setting with three options, shown with the results, and any keys that were merged are listed with all their spellings. Under exact matching a key with surrounding whitespace is shown quoted, because otherwise the table has two rows that look identical and are not.
Sums used plain left-to-right addition, which lost all one hundred of a hundred 1s added after a 1e16; they now use compensated summation. And min and max spread the array into an argument list, which threw RangeError: Maximum call stack size exceeded and rendered nothing at all on a 150,000 row group. That group now completes in about 300 milliseconds.
One thing the previous version already did right is preserved and now tested: a row total is the aggregation of the underlying VALUES, not of the cells in that row. The difference is invisible for a sum and decisive for an average, where the mean of a row of means is not the mean of the row unless every cell holds the same number of values.
The classic cross-tabulation, with the totals computed from the underlying rows so an average row total is the average of the data rather than the average of the averages.
The skipped-values report says how many entries in the value column are not numbers and shows examples, which is often the more useful output.
Switch key matching between exact and case-insensitive and see which spellings merge, so you find out whether North, north and "North " are one thing or three.
Use Count rows for how many records fell into a group and Count for how many of them held anything, and see the difference where the blanks are.
No. Count is how many cells hold something. Count rows is how many rows fell into the group, blanks included. Both are offered because both are legitimate questions, and each says which it answers.
Because it is not a number, and the alternative is worse: parseFloat reads a prefix, so 12abc would count as 12 and 1,234 would count as 1. Anything skipped is counted and shown above the table with examples. If your values use grouped thousands, turn on that option and they will be read.
Whichever you choose. The default is exactly as written, which gives three, and a key with surrounding whitespace is shown quoted so you can tell them apart. Switch to ignoring spaces, or spaces and case, and the merged spellings are listed under the table.
From the underlying values, not from the cells. For a sum that makes no difference. For an average it does: the average of a row of averages is not the average of the row unless every cell holds the same number of values.
Whichever you pick. Both are offered: the population form divides by n and describes the group, and the sample form divides by n-1 and estimates the population the group came from. The sample form is reported as undefined for a group with one value, because n-1 is zero.
Everything runs in your browser. A single group of 150,000 rows completes in about 300 milliseconds; the version this replaced crashed on that input.
Rows and columns are parsed and transformed in memory in your browser. No record ever reaches a server.