CSV for Data Analysis: From Raw File to Insight

Good analysis starts with tidy structure: each row is a single observation, each column is a single variable, and each cell holds one value. Files built for human eyes often break this — a month spread across twelve columns, or two facts crammed into one cell like "Berlin (DE)". Reshaping such a file so that every column means exactly one thing is the unglamorous work that makes every later step, from pivot tables to models, straightforward. Before reshaping, clean the obvious noise: trim whitespace, drop duplicates and remove empty rows so your counts start honest.

The two reshaping moves have names worth knowing. Going from wide to long — twelve month columns collapsing into a month column and a value column — is a melt, or an unpivot; going back the other way is a pivot. Every tool has both: melt and pivot_table in pandas, Unpivot Columns in Power Query, a pivot table in a spreadsheet. Store data long and pivot it only for presentation, never the reverse, because long tables filter, group and join without complaint while wide ones fight you at every step.

CSV stores everything as text, but analysis needs types. "42" must become the number 42 before you can sum it, and "2026-08-01" must be a date before you can sort a timeline. Numbers hiding as text are a classic source of wrong totals and refused sorts. When you move data toward a JSON-based pipeline or an API, converting with type inference turns those strings into real numbers and booleans, so downstream code does not have to guess. Keeping an eye on which columns are numeric and which are labels prevents a whole class of quiet mistakes.

Type inference works column by column, and one bad value ruins the whole column. A single n/a among ten thousand integers makes pandas read the column as text; a column read as a floating-point number turns the identifier 00123 into 123.0. The defence is to be explicit rather than hopeful: read identifiers as strings so leading zeros survive, parse dates instead of leaving them as text, and store them as ISO 8601 — 2026-09-20 — which sorts correctly even while it is still a string. If a timestamp crosses a timezone boundary, keep the offset; a bare 00:30 is not yet a fact.

Five quick checks catch most of what goes wrong. Compare the row count against what you expected. Compare the number of distinct keys against the number of rows, which exposes duplicates immediately. Look at the minimum and maximum of every date and numeric column, where a date in 1970 reveals an epoch zero and a negative quantity reveals a sign error. Measure the share of blanks per column. And reconcile one total against the system the data came from. None of them takes more than a minute.

Run them on the raw file, before any cleaning, and again on the cleaned one. Comparing the two answers is the only way to tell whether a difference came from the data or from your own processing: a total that moves between the two passes means the cleaning removed something real, and you want to know that now rather than after the chart is in a slide deck.

Never clean a file in place. Keep it exactly as it arrived, write the cleaned version beside it, and let the filename carry the date of the export rather than the date you happened to open it. When somebody questions a number three weeks later — and somebody will — the untouched file is the only thing that can settle whether the source changed or your processing did.

The same habit makes every refresh cheap. Keep the previous export and each new one can be measured against it: row counts and totals say that something moved, and a comparison keyed on an identifier says exactly which records moved and how. Writing the cleaning steps down in order, even as a short list sitting next to the files, turns an analysis that people have to trust into one that anyone can re-run.

Much of analysis is really about change: what moved between last month and this one, which records were added or dropped, whose values shifted. Comparing two versions of a dataset keyed on an identifier answers that directly. Instead of scrolling thousands of rows, you get a summary — so many added, so many removed, so many modified — and a coloured table pinpointing each one. This is how you audit a data refresh, verify a migration, or explain why a total changed. Load the old and new files in the Compare tab, pick the key column, and read the diff.

Two things decide whether a diff is readable. The first is the key: when the identifier itself changes — a record re-keyed during a migration — the comparison reports one removal and one addition rather than a modification, which is accurate but easy to misread. The second is noise. Columns that change on every export, such as a last-updated timestamp or a row number, mark every record as modified and bury the handful that genuinely moved. Compare on a stable key, and leave out the columns whose only job is to record when the export ran.