Keeping a CSV Sorted So Diffs Stay Small in Version Control

A CSV tracked in version control is only as reviewable as it is stable. The same data, written twice, should produce the same bytes. If a script writes rows in database order, hash order or whatever the last query felt like, every commit shows rows moving, and the actual change is lost in the shuffle. Sorting by a key fixes this at almost no cost. This article shows the effect on a diff, the right way to sort a CSV from the shell, from SQL and from pandas, and the traps in each.

What an unsorted file does to a diff

Commit this file:

id,name,status,amount
1,Ana,active,10.00
2,Bo,active,20.00
3,Cy,pending,30.00
4,Di,active,40.00

Rewrite it with the same data and rows 2 and 3 in the other order:

id,name,status,amount
1,Ana,active,10.00
3,Cy,pending,30.00
2,Bo,active,20.00
4,Di,active,40.00

Nothing changed in the data. But:

3d2
< 2,Bo,active,20.00
4a4
> 2,Bo,active,20.00

diff reports that line 3 of the old file (Bo) was deleted and a line for Bo was added after line 4. The reader has to notice that it is the same row. In a file with thousands of rows the shuffle looks like thousands of changes, and the one real change (a price) is somewhere inside. Version control shows churn, git blame credits everything to the last reorder, and merges conflict wherever two branches touched the same stretch.

Sort the body, not the header

A plain sort sorts the header into the data. Keep it in place:

(head -n 1 data.csv; tail -n +2 data.csv | LC_ALL=C sort -t, -k1,1n) > sorted.csv && mv sorted.csv data.csv

head -n 1 writes the header; tail -n +2 takes everything after; sort -t, -k1,1n sorts on the first comma-separated field, numerically. The subshell groups the two outputs into one stream. Three details decide whether the result is stable.

Numeric versus text. Sorted as text, ids order as 1, 10, 11, 2, 3. A file that sorts correctly at 9 rows reorders itself at 10 rows. Use -n for numeric keys. For keys that are text (codes, names), the default comparison applies.

The locale. sort uses the locale's collation, and two machines with different locales order Zoë, zoe and Zoe differently. Set LC_ALL=C so the order is by bytes on every machine and for every user. It is not alphabetical in a human sense (uppercase before lowercase), and it is the same everywhere, which is what a diff needs.

The key field. -k1,1 means "field 1 only". Writing -k1 means "from field 1 to the end of the line", which makes the rest of the line part of the key and gives surprising orders for ties. And -t, splits on every comma, so a quoted field with a comma before the key column shifts the fields; sort files like that with a CSV-aware tool.

Sort in the writer

The shell fix repairs a file after the fact. A better fix is for the program that writes the file to sort. In SQL:

COPY (SELECT * FROM source ORDER BY id) TO 'data.csv' (FORMAT csv, HEADER);

In pandas:

df.sort_values("id", kind="stable").to_csv("data.csv", index=False)

kind="stable" keeps the order of rows with equal keys. In Python's csv module, sort the list of rows before writing. A writer that sorts guarantees the property every time, and a rule "sort after" depends on someone remembering. Add a check to the job: the output must be sorted, and fail if it is not.

tail -n +2 data.csv | cut -d, -f1 | sort -c -n && echo sorted

sort -c checks without sorting and reports the first out-of-order line.

Unique keys

The sort key must identify a row. If two rows share a key (status as the key, with many active), their relative order is up to the sort implementation, and a different run may swap them. Add a second key to break ties: sort -t, -k3,3 -k1,1n sorts by status and then by id. Best, use a column that is unique, or the whole line as a last-resort tiebreaker (sort without -k compares whole lines when keys are equal, unless -s is given for stable order). Finding that the key is not unique is a signal in itself: duplicates in a file you meant to key by id (duplicate keys).

Other things that stabilize a diff

Sorting handles order. Three other sources of noise deserve the same discipline.

Number format. Write 10.00 every time, never 10 one run and 10.0 the next. Fix the number of decimals in the query (round(amount, 2) or CAST(amount AS DECIMAL(10,2))).

Quoting. Use the writer's default minimal quoting consistently. A tool that quotes every field one day and none the next changes every line. Pick one writer for the file.

Line endings and encoding. LF, UTF-8, no byte order mark, a final newline. Configure .gitattributes (*.csv text eol=lf) so Git normalizes line endings for everyone.

The reasons are in the Git diff article, which also shows how to read a diff once the file is stable.

Sorted files help more than diffs

A sorted CSV is easy to merge (two sorted files merge in one pass), easy to search (look, binary search with sort/join) and easy to compare with other sorted files (comm and join both require sorted input). join -t, -1 1 -2 1 a.csv b.csv after sorting both on the key joins on the first column without a database. Sorted data also compresses a little better, because similar neighbors repeat. And sorted by the key, an incremental load or a comparison can stop early once the keys pass each other, which is why sorted exports are the format of choice for large nightly feeds.

How to tell it worked

Regenerate the file from the same data twice and compare bytes: cmp should say they are identical. Shuffle the input rows and regenerate: the output should be unchanged. Change one cell in the source, regenerate and look at the diff: one line (or one cell). Add a row in the middle of the key range: one inserted line at the right place. Run the sorted check on the file.

When the order carries meaning

Some files are ordered on purpose: a time series that is read top to bottom, a ranked list, a log in the order events happened. Sorting by key destroys that. In those cases keep the order column (a timestamp or a sequence number) and sort by that, or leave the order alone and accept noisier diffs, or store the order as data (a rank column) so that a key sort loses nothing. If the file is a log that only ever grows, appending is already the stable operation, and the diff is the new lines. For comparing files that are not sorted, use the key-based methods in the comparison article and the diff and merge guide.

Further reading: GNU sort manual; pandas sort_values.