The most common charting mistake is not choosing the wrong chart type. It is charting the wrong data: raw, ungrouped rows fed straight into a chart, producing a forest of points or bars that shows everything and explains nothing. A thousand sales rows plotted individually do not reveal a trend; they reveal that a thousand sales happened, which was never in doubt. The pattern lives one level up, in the totals, averages, and counts per group — and reaching that level is a step called aggregation, which must happen before charting, not after.
Chart Builder includes aggregation for exactly this reason: it groups CSV rows and totals (or averages, or counts) them before drawing, so the chart shows the pattern rather than the noise. This post explains what aggregation is, the groupings that answer common questions, and the mistakes that happen when it is skipped — using one fictional dataset throughout.
What aggregation actually does
Aggregation takes many rows and replaces them with fewer, meaningful rows. A sales file with one row per transaction becomes one row per month with a total; a survey file with one row per respondent becomes one row per age group with an average score; a website log with one row per visit becomes one row per traffic source with a count. The raw rows are not deleted or falsified — they are summarised, and the summary is what gets charted.
Three aggregations cover most questions. A sum totals a value within each group (total sales per month). A count counts rows within each group (number of orders per region). An average finds the typical value within each group (average order value per category). The choice matters: summing when you meant to average, or counting when you meant to sum, produces a chart that looks authoritative and means something else entirely. Before aggregating, state the question in one sentence — "total sales per month," "average rating per product" — and let the sentence choose the aggregation.
The mess that skipping aggregation creates
Consider a fictional online shop, Example Outdoor Ltd, with 2,400 transaction rows for one year: date, product, category, and amount. Charted raw as a bar chart, with one bar per transaction, the result is 2,400 bars — an unreadable comb that no reader can interpret. Charted raw as a line, with one point per transaction in date order, the result is a jagged scribble, because individual transactions vary wildly while the monthly trend underneath is smooth. Charted raw as a scatter of amount against date, the result is a cloud that hints at a trend but cannot state it.
None of these charts is "wrong" in the sense of plotting incorrect values. Each plots exactly what it was given. They fail because they plot the wrong level of data for the question being asked. A reader who wants the monthly trend needs twelve points, not 2,400. Aggregation is the step that turns the second into the first.
Worked example: from 2,400 rows to twelve bars
Aggregate Example Outdoor's transactions by month, summing the amount within each month. The fictional monthly totals are: January £18,000, February £16,500, March £21,000, April £20,000, May £24,000, June £23,000, July £27,000, August £26,000, September £22,500, October £28,000, November £31,000, December £35,000. Twelve bars now show a clear story: a rising year with a summer plateau and a strong finish — a story invisible in the raw rows.
A second aggregation answers a different question from the same file: group by category and sum, giving fictional annual totals of Tents £142,000, Footwear £98,000, Clothing £87,000, and Accessories £45,000. Sorted largest to smallest, four bars rank the categories instantly. A third aggregation — average order value per category — would answer yet another question (which categories sell expensively versus in volume), and would give a different ranking from the totals. Same file, three aggregations, three answers: the question chooses the grouping, and the grouping chooses the chart.
The workflow matters too. Aggregation should follow cleaning, not precede it: duplicate rows inflate sums, blank amounts distort averages, and inconsistent category spellings split one category into two groups. That is why the reliable sequence is clean first with CSV Cleaning Studio, then aggregate and chart — the order the two tools are designed for. A sum built on uncleaned data is precisely wrong in a way that looks precisely right.
Aggregation mistakes to watch for
Four mistakes recur. First, aggregating by the wrong grain: grouping by day when the question is monthly produces noise; grouping by year when the question is monthly hides the pattern. Match the grain to the question. Second, mixing grains: a chart that plots some months and some quarters together is not comparable, however tidy it looks. Third, averaging averages: the average of monthly averages is not the overall average unless every month had the same number of rows — aggregate from the raw rows, not from already-aggregated figures. Fourth, hiding the group size: an average based on three rows and an average based on three thousand rows look identical as bars, so note the counts alongside averages where the difference matters.
Also, aggregation loses detail by design — and that loss should be acknowledged. A monthly total hides the week that spiked; a category total hides the product that drove it. If a total looks surprising, drill back down to the rows behind it before concluding anything. Aggregation is a lens, not a verdict.
Honest limitations
Chart Builder aggregates what it is given; it cannot know whether the grouping matches your question, whether the raw data was cleaned, or whether a sum, count, or average is the right summary. Aggregated charts also inherit every problem in the source file — duplicates, blanks, inconsistent labels — and can amplify them, because one bad row inside a group shifts the group's total. Always clean first, state the question before grouping, and check a surprising total against its underlying rows. For a structured view of the same data, JSON Studio can format and explore it, and the background guide is how to turn a CSV into charts.
FAQs
How do I group and aggregate data before charting it?
Decide the question first ("total sales per month"), then group rows by the relevant column (month) and apply the matching summary (sum for totals, count for numbers of rows, average for typical values). Chart the grouped result, not the raw rows.
Why does charting raw CSV rows produce a mess?
Because raw rows are at transaction level, while most questions are at group level. Thousands of individual points or bars show noise, not pattern. Aggregation reduces rows to one per group, so the chart shows the trend, ranking, or comparison being asked about.
Should I clean a CSV before aggregating it?
Yes. Duplicates inflate sums, blanks distort averages, and inconsistent spellings split one group into two. Clean first, then aggregate — otherwise the chart will be precisely wrong.
Is the average of monthly averages the overall average?
Not unless every month had the same number of rows. Aggregate from the raw rows for an overall figure, rather than averaging already-averaged numbers, which weights small months and large months equally.
Related reading: Clean the file first.
human review pending client designation