Sorting a CSV File by Column in the Terminal Correctly

Sorting a CSV by a column sounds like one command and is three decisions: how to keep the header out of the sort, whether the column is text or a number, and what order text sorts in. Get any of them wrong and the output looks like a sorted file anyway. This article goes through the shell tools, sort, qsv, Miller, csvkit and DuckDB, shows the mistakes on small files, measures them on 2,000,000 rows, and says when sort -t, is safe. It is part of the tour of working with CSV in the shell; the related counting job is in counting rows and unique values, and the CSV-aware sorter in the qsv article. The CSV rules these tools follow are in RFC 4180.

The header is a row, too

sort has no idea that the first line is a header. It sorts every line. The command most people write first:

sort -t, -k5 -n orders-small.csv

id,customer,city,qty,price,status
7,Flo,Kyiv,12,1.05,paid
5,"Doe ""JD"" John",Rome,7,3.10,paid
6,Ed Zürich,Bern,1,8.00,failed
1,Ana Silva,Lisbon,3,9.50,paid
2,"Smith, Jane",Boston,10,2.25,refunded
4,Cy Dubois,Lyon,,15.75,pending
8,Gus,Riga,5,20.00,refunded
3,Bo Chen,Oslo,2,100.00,paid

This sorted the small orders file by field 5, the price, numerically, and the header ended up first. That was luck: the text price is not a number, so -n reads it as 0, which is lower than every price. Reverse the sort, -r, and the header lands last. With a text sort it ends up wherever its letters put it. The habit that works in every case is to keep the header out of the sort and put it back on top:

(head -n 1 orders-small.csv; tail -n +2 orders-small.csv | sort -t, -k5 -n -r) | head -n 4

id,customer,city,qty,price,status
3,Bo Chen,Oslo,2,100.00,paid
8,Gus,Riga,5,20.00,refunded
4,Cy Dubois,Lyon,,15.75,pending

head -n 1 prints the header and tail -n +2 hands everything else to sort, here -k5 -n -r for price, descending. The subshell parentheses join the two outputs: header first, then the sorted rows, 100.00, 20.00 and 15.75 at the top.

Text order is not number order

Sorting treats a value as text unless you say otherwise, and text order puts 100 before 2. On a six row file of names and scores:

cut -d, -f2 mixed-sort.csv | tail -n +2 | sort -n
echo ---
tail -n +2 mixed-sort.csv | sort -t, -k2
echo --- numeric
tail -n +2 mixed-sort.csv | sort -t, -k2 -n

2
7
9
10
50
100
---
banana,10
cherry,100
apple,2
Élan,50
Zed,7
Apple,9
--- numeric
apple,2
Zed,7
Apple,9
banana,10
Élan,50
cherry,100

The first block is cut -d, -f2 | sort -n on the scores: 2, 7, 9, 10, 50, 100. The second sorts the whole rows by the second field as text: banana,10, cherry,100, apple,2, Élan,50, Zed,7, Apple,9, which is the order 10, 100, 2, 50, 7, 9. The third adds -n and gives 2 to 100 in numeric order. A report that is "sorted by score" and starts with 10, 100, 2 has had no -n. Add -n for integers and decimals; -g handles scientific notation like 1e3; and numbers formatted with a thousands separator (1,000) need cleaning first, since the comma is the delimiter anyway.

Text order depends on the locale

For names, the order depends on the collation of the locale. The same names under the two locales available here, and with case folding:

tail -n +2 mixed-sort.csv | cut -d, -f1 | LC_ALL=C sort | tr '\n' ' '; echo
tail -n +2 mixed-sort.csv | cut -d, -f1 | LC_ALL=C.UTF-8 sort | tr '\n' ' '; echo
tail -n +2 mixed-sort.csv | cut -d, -f1 | LC_ALL=C sort -f | tr '\n' ' '; echo

Apple Zed apple banana cherry Élan 
Apple Zed apple banana cherry Élan 
Apple apple banana cherry Zed Élan 

Under LC_ALL=C and LC_ALL=C.UTF-8 the order is Apple Zed apple banana cherry Élan: all capital letters first, then lowercase, then the accented É, because both compare by code point. sort -f folds case and gives Apple apple banana cherry Zed Élan, with É still last. A machine with en_US.UTF-8 would order these differently, and I could not test that here. This is the reason a sorted export made on your laptop can differ from one made on a server. For a reproducible order, set the locale in the script, usually LC_ALL=C, and say so.

Quotes shift the key

sort -t, -k2 splits every line on commas like cut does, and the key field is the wrong one on any line with a quoted comma:

sort -t, -k2 orders-small.csv | head -n 4

5,"Doe ""JD"" John",Rome,7,3.10,paid
2,"Smith, Jane",Boston,10,2.25,refunded
1,Ana Silva,Lisbon,3,9.50,paid
3,Bo Chen,Oslo,2,100.00,paid

Sorting by the name field gave Doe, Smith, Ana Silva, Bo Chen. The quote character sorts before letters, so the two quoted names lead, and "Smith, Jane" is keyed on "Smith. A stable sort on the status makes the shift obvious:

sort -t, -k6,6 -s orders-small.csv | head -n 4

2,"Smith, Jane",Boston,10,2.25,refunded
6,Ed Zürich,Bern,1,8.00,failed
1,Ana Silva,Lisbon,3,9.50,paid
3,Bo Chen,Oslo,2,100.00,paid

The sort by field 6 (-s keeps equal keys in input order) should have produced failed first. It started with the "Smith, Jane" row, whose field 6 is 2.25, because the name's comma moved every later field one position over. The other rows are in the right order for the wrong reason. The same limit applies as for cut: sort -t, is safe only when grep -c '"' file.csv is 0.

CSV-aware sorting

qsv, Miller and csvkit sort by column name and parse quoting:

qsv sort -s price -N -R orders-small.csv | qsv select id,customer,price | head -n 4
echo ---
qsv sort -s name mixed-sort.csv | qsv select name | tr '\n' ' '; echo
qsv sort -s name -i mixed-sort.csv | qsv select name | tr '\n' ' '; echo

id,customer,price
3,Bo Chen,100.00
8,Gus,20.00
4,Cy Dubois,15.75
---
name Apple Zed apple banana cherry Élan 
name Apple apple banana cherry Zed Élan 

qsv sort -s price -N -R sorts the price numerically, reversed, and returns the same top three as the pipeline. For text, qsv sort -s name returned Apple Zed apple banana cherry Élan (code point order) and -i folded case to Apple apple banana cherry Zed Élan. Miller:

mlr --icsv --opprint sort -nr price then cut -f id,customer,price then head -n 3 orders-small.csv
echo ---
mlr --icsv --opprint sort -f status -nr price then cut -f id,status,price orders-small.csv

id customer  price
3  Bo Chen   100.00
8  Gus       20.00
4  Cy Dubois 15.75
---
id price  status
6  8.00   failed
3  100.00 paid
1  9.50   paid
5  3.10   paid
7  1.05   paid
4  15.75  pending
8  20.00  refunded
2  2.25   refunded

mlr sort -nr price gave 100.00, 20.00, 15.75. Its second command shows the multi-key form, sort -f status -nr price: status ascending as text, then price descending within each status. The paid rows came out 100.00, 9.50, 3.10, 1.05, as they should. csvkit does it too:

csvsort -c status,price orders-small.csv | csvcut -c id,status,price | head -n 4

id,status,price
6,failed,8.00
7,paid,1.05
5,paid,3.10

csvsort -c status,price returned failed 8.00, then the paid rows by ascending price, 1.05 and 3.10. Remember that csvkit infers types and may turn an identifier column into a number; sort a column you want as text with --no-inference.

Speed on 2,000,000 rows

Sorting by the amount column of the orders file, with the file output cut to three lines:

cd /batches/csv-0001-0200/work
for c in "(head -n 1 orders-2m.csv; tail -n +2 orders-2m.csv | sort -t, -k4,4 -n -r) | head -n 3" "(head -n 1 orders-2m.csv; tail -n +2 orders-2m.csv | LC_ALL=C sort -t, -k4,4 -n -r) | head -n 3" "qsv sort -s amount -N -R orders-2m.csv | head -n 3" "mlr --csv sort -nr amount orders-2m.csv | head -n 3" "duckdb -c \"COPY (SELECT * FROM 'orders-2m.csv' ORDER BY amount DESC) TO '/dev/stdout' (FORMAT csv)\" | head -n 3"; do
  /usr/bin/time -f "%es" bash -c "$c > /dev/null" 2>&1 | tail -1 | tr '\n' ' '; echo "$c" | cut -c1-100
done

1.25s (head -n 1 orders-2m.csv; tail -n +2 orders-2m.csv | sort -t, -k4,4 -n -r) | head -n 3
1.17s (head -n 1 orders-2m.csv; tail -n +2 orders-2m.csv | LC_ALL=C sort -t, -k4,4 -n -r) | head -n 3
1.52s qsv sort -s amount -N -R orders-2m.csv | head -n 3
6.54s mlr --csv sort -nr amount orders-2m.csv | head -n 3
0.76s duckdb -c "COPY (SELECT * FROM 'orders-2m.csv' ORDER BY amount DESC) TO '/dev/stdout' (FORMAT csv)" 

The shell pipeline took 1.25 s, with LC_ALL=C 1.17 s, qsv 1.52 s, Miller 6.54 s, and DuckDB reading, ordering and writing a full CSV 0.76 s. In an earlier run qsv took 2.76 s and 3.4 s for the same sort, so treat these as ranges: this machine was busy with other work at times. The ordering is what I would take from them: sort and DuckDB are fast, qsv is close, and Miller's sort is several times slower on a file this size. All of them agree on the answer:

cd /batches/csv-0001-0200/work
tail -n +2 orders-2m.csv | sort -t, -k4,4 -n -r | head -n 3 | cut -d, -f1,4
qsv sort -s amount -N -R orders-2m.csv | qsv slice --len 3 | qsv select order_id,amount

1012726,654.24
158192,651.04
1112548,649.03
order_id,amount
1012726,654.24
158192,651.04
1112548,649.03

The top three amounts are 654.24 (order 1,012,726), 651.04 (order 158,192) and 649.03 (order 1,112,548) from the pipeline and from qsv. For a file larger than memory, GNU sort spills to temporary files and keeps going; the -T and -S options control where and how much, and --parallel the threads. The cost is temporary disk space of about the size of the file.

How to tell it worked

Check the first and last data rows against the sort order you wanted. Confirm the header is on line 1 and only once. Count the rows before and after. Compare the top few values with a second tool, as above. And for a text sort, look at how capital letters, accents and digits fall, since they show the collation you got.

When sort -t, is not safe

When the file can contain quoted fields, use a CSV-aware tool. When the sort key is a column with embedded newlines, line-oriented sort will separate the pieces of a record, and only a parser can keep them together. When the order must be the same on every machine, fix the locale. And when you only need the largest few values, a full sort is more work than the job needs; ask the question in SQL with ORDER BY ... LIMIT 3:

cd /batches/csv-0001-0200/work
time duckdb -box -c "SELECT order_id, amount FROM 'orders-2m.csv' ORDER BY amount DESC LIMIT 3"

┌──────────┬────────┐
│ order_id │ amount │
├──────────┼────────┤
│ 1012726  │ 654.24 │
│ 158192   │ 651.04 │
│ 1112548  │ 649.03 │
└──────────┴────────┘

real	0m0.324s
user	0m0.402s
sys	0m0.272s

The same three amounts came back in 0.32 s.