Counting Rows and Unique Values in a CSV From the Shell

How many rows, and how many different values? They are the first two questions about any file, and the shell answers both. They also hide two common mistakes: counting lines when you meant records, and counting spellings when you meant values. This article gives the commands for each, checks them against qsv, Miller, csvkit and DuckDB, and shows what a messy column does to the numbers. It belongs to the tour in working with CSV in the shell.

Lines are not records

wc -l counts newline characters. On a clean CSV that equals the number of records plus one for the header:

cd /batches/csv-0001-0200/work
wc -l orders-2m.csv
printf 'records (qsv count): '; qsv count orders-2m.csv

2000001 orders-2m.csv
records (qsv count): 2000000

wc -l said 2,000,001, and qsv count said 2,000,000: one header line. That is all most files need, and wc -l is the quickest thing to type. The assumption underneath is that no field contains a line break, and a CSV is allowed to have those, quoted. On a four-record file in which one note has a line break inside it:

printf 'wc -l people.csv: '; wc -l < people.csv
printf 'qsv count:        '; qsv count people.csv
printf 'mlr count:        '; mlr --icsv --ojson count people.csv | tr -d '\n '; echo
printf 'csvstat --count:  '; csvstat --count people.csv

wc -l people.csv: 6
qsv count:        4
mlr count:        [{"count":4}]
csvstat --count:  4

wc -l printed 6, and qsv count, Miller's count and csvstat --count all printed 4. Six lines minus the header is five, and there are four records, because one record spans two lines. So if you check "rows in equals rows out" with wc -l, it works on the files you wrote yourself and fails on a file from outside. Use a CSV-aware counter for the number that matters, and wc -l as a fast sanity check. The mechanics of embedded newlines are in the escaping article.

Counting values

The classic pipeline counts how often each value appears in a column (sort and uniq are documented in the GNU manual). Pick the column with cut, drop the header, sort so equal values are adjacent, and let uniq -c count the runs:

cd /batches/csv-0001-0200/work
cut -d, -f6 orders-2m.csv | tail -n +2 | sort | uniq -c | sort -nr

 400329 pending
 400135 refunded
 400113 paid
 399890 shipped
 399533 failed

The final sort -nr puts the most common first. The five statuses of the orders file came out as pending 400,329, refunded 400,135, paid 400,113, shipped 399,890 and failed 399,533. This is cut, so the earlier warning applies, and the file has no quoted fields, which I confirmed with grep. If a column can contain quoted commas, cut will take the wrong piece; the CSV-aware alternatives are below.

The tail -n +2 matters. Leave it out and the header is counted as a value:

cut -d, -f1 messy-status.csv | sort | uniq -c | sort -nr | head -n 3

      2 paid
      1 status
      1 refunded

On a small file this printed 2 paid, 1 status and 1 refunded: the word status is in the results as if it were a status. Easy to spot when there are five values, invisible when the column has 50,000.

Other tools, and how fast they are

The same count with a CSV parser behind it:

cd /batches/csv-0001-0200/work
for c in "cut -d, -f6 orders-2m.csv | tail -n +2 | sort | uniq -c | sort -nr" "cut -d, -f6 orders-2m.csv | tail -n +2 | LC_ALL=C sort | uniq -c | sort -nr" "awk -F, 'NR>1{n[\$6]++} END{for(k in n) print n[k], k}' orders-2m.csv" "qsv frequency -s status -l 0 orders-2m.csv" "mlr --icsv --opprint count-distinct -f status orders-2m.csv" "duckdb -c \"select status, count(*) from 'orders-2m.csv' group by 1\""; do
  /usr/bin/time -f "%es" bash -c "$c > /dev/null" 2>&1 | tail -1 | tr '\n' ' '; echo "$c" | cut -c1-80
done

0.71s cut -d, -f6 orders-2m.csv | tail -n +2 | sort | uniq -c | sort -nr
0.88s cut -d, -f6 orders-2m.csv | tail -n +2 | LC_ALL=C sort | uniq -c | sort -nr
0.92s awk -F, 'NR>1{n[$6]++} END{for(k in n) print n[k], k}' orders-2m.csv
0.45s qsv frequency -s status -l 0 orders-2m.csv
1.17s mlr --icsv --opprint count-distinct -f status orders-2m.csv
0.27s duckdb -c "select status, count(*) from 'orders-2m.csv' group by 1"

The six commands, each on 2,000,000 rows, took: the plain pipeline 0.71 s, the same with LC_ALL=C 0.88 s, awk with an associative array 0.92 s, qsv frequency 0.45 s, Miller's count-distinct 1.17 s and DuckDB's GROUP BY 0.27 s. Two things stand out. The pipeline is competitive for what it is; sorting 2,000,000 short strings is quick, and the LC_ALL=C that is supposed to help did not here, so do not add it by reflex without measuring. And the tool that does the least text processing, DuckDB, is also the fastest. The qsv version is worth knowing because it is a one-word command:

qsv frequency -s status -l 0 file.csv

and Miller's is mlr --icsv --opprint count-distinct -f country then sort -nr count, which produces a table of the seven countries from FR 286,777 down to US 284,938 (shown in the output two sections down). The two-column version, counts per country and status, only changes the field list:

cd /batches/csv-0001-0200/work
mlr --icsv --opprint count-distinct -f country,status then head -n 4 orders-2m.csv

country status  count
GB      shipped 56892
BR      failed  56905
JP      pending 57321
DE      failed  56943

The first four of the 35 combinations were GB shipped 56892, BR failed 56905, JP pending 57321 and DE failed 56943. The same question needs a different verb, not a different pipeline.

How many distinct values

The count of different values, not their frequencies. With the shell, sort and drop duplicates, then count:

cd /batches/csv-0001-0200/work
cut -d, -f2 orders-2m.csv | tail -n +2 | sort -u | wc -l
duckdb -list -noheader -c "SELECT count(DISTINCT customer_id) FROM 'orders-2m.csv'"
qsv stats --cardinality --select customer_id orders-2m.csv | qsv select field,cardinality | tail -n 1

249916
249916
customer_id,249916

Three tools gave 249,916 distinct customers: sort -u | wc -l, DuckDB's count(DISTINCT customer_id), and qsv stats --cardinality. The qsv figure comes with the rest of the column's profile, and Miller has a -n flag on count-distinct that returned the same 249,916:

cd /batches/csv-0001-0200/work
mlr --icsv --opprint count-distinct -f country then sort -nr count orders-2m.csv
mlr --icsv --opprint count-distinct -n -f customer_id orders-2m.csv

country count
FR      286777
IN      286243
BR      285957
DE      285749
GB      285284
JP      285052
US      284938
count
249916

That output also contains the seven-row country table. When the number of distinct values is large, as here, qsv and DuckDB use far less memory than a hash table in awk, and sort -u spills to disk. For a column with millions of distinct values, the shell pipeline is the one that keeps working, slowly.

Messy values split the count

Counting is only as good as the values. A column where the same thing is written four ways has four counts. Here is a status column with paid, Paid, paid (a trailing space), refunded, a blank and paid:

printf 'status\npaid\nPaid\npaid \nrefunded\n\npaid\n' > messy-status.csv
cut -d, -f1 messy-status.csv | tail -n +2 | sort | uniq -c

      1 
      1 Paid
      2 paid
      1 paid 
      1 refunded

uniq -c reported five groups: paid twice, Paid once, paid with the space once, refunded once, and the blank once. Of the six values, four are the same word. A case-insensitive sort -f | uniq -ic merged paid and Paid:

cut -d, -f1 messy-status.csv | tail -n +2 | sort -f | uniq -ic

      1 
      3 Paid
      1 paid 
      1 refunded

That gave 3 Paid, but paid with the trailing space stayed separate. The reliable fix is to normalize before counting: lowercase, trim leading and trailing spaces, then count:

cut -d, -f1 messy-status.csv | tail -n +2 | tr 'A-Z' 'a-z' | sed 's/^ *//; s/ *$//' | sort | uniq -c | sort -nr

      4 paid
      1 refunded
      1 

4 paid, 1 refunded and one blank. The blank is a value too, and whether it is "missing" or "unknown" is a question for the data's owner; grep -c '^$' counted it. Doing the cleaning in the counting step is acceptable for a look. For anything that will be repeated, clean the file once, and keep the cleaning as a script, so every later count agrees.

How to tell it worked

Check the counts add up: the frequencies of a column should sum to the record count (qsv frequency shows an Other row for exactly that reason). Cross-check one number with a second tool. Look at the least common values, not only the most common: the rare ones are where paid and Paid and the stray header hide. And count blanks separately, so you know how many of the rows the column describes.

Where the shell pipeline is not enough

When the column is quoted or may contain commas, cut is wrong and the CSV-aware tools are right, as the cut article shows. When you need several columns at once, counts per group of two or three, a SQL GROUP BY says it in one line, as in the DuckDB guide. And when the values need judgment (is N/A missing, is St. the same as Street), no counting command helps, and the work is in the cleaning. The numbers themselves are the easy part.