sort and uniq: the data toolkit

Counting things is 80% of data work

Two more small tools complete the pipeline kit:

The classic gotcha is that uniq only sees duplicates that are next to each other. That is why nearly every real use of it is written as sort file | uniq, with sort gathering the duplicates into neighboring lines so uniq has something to collapse.

The legendary combination sort | uniq -c | sort -rn ranks lines by how often they occur, and it shows up everywhere: top visitors in a web log, the most frequent error, the most-used command in a shell history.

Grouping duplicates, then collapsing them

votes.txt holds six votes across three fruits. sort gathers the repeats into neighboring lines, and sort | uniq then reduces them to a deduplicated ballot.

echo "banana" > votes.txt
echo "apple" >> votes.txt
echo "cherry" >> votes.txt
echo "apple" >> votes.txt
echo "banana" >> votes.txt
echo "apple" >> votes.txt
sort votes.txt
sort votes.txt | uniq

Output

apple
apple
apple
banana
banana
cherry
apple
banana
cherry

The output is two results stacked together. The first six lines are plain sort, still holding every vote but with identical names adjacent, and the last three lines are the same data after uniq collapsed each run of repeats into a single entry.

Ranking by frequency to find the winner

Four stages turn a list of votes into a winner. uniq -c counts each fruit, sort -rn puts the biggest count first, and head -n 1 keeps the top row. The trailing xargs merely trims the decorative spacing uniq adds, so the output is clean.

echo "banana" > votes.txt
echo "apple" >> votes.txt
echo "cherry" >> votes.txt
echo "apple" >> votes.txt
echo "banana" >> votes.txt
echo "apple" >> votes.txt
sort votes.txt | uniq -c | sort -rn | head -n 1 | xargs

Output

3 apple

Reading the pipeline left to right gives its plain-English description: group the identical lines, count each group, rank the counts from largest down, then take the top one. That same four-step shape answers most frequency questions you will meet.

cut: picking columns

Much real data is line-based and column-based: CSV exports, server logs with fields, /etc/passwd. cut extracts columns from each line. The -d flag sets the delimiter, the character that separates fields, and -f picks which field to keep. So cut -d, -f1 people.csv prints just the first column of a comma-separated file.

It slots straight into pipelines alongside everything above. cut -d, -f3 log.csv | sort | uniq -c | sort -rn ranks the values in column 3 by frequency in a single line, answering in one command a question that would otherwise mean opening a spreadsheet.

Pulling one column out of a CSV

The first cut pulls the name column out of a three-line CSV. The second takes the city column and feeds it through sort, so head -n 1 yields the alphabetically first city.

printf 'ada,42,london\ngrace,36,new york\nalan,41,cambridge\n' > people.csv
cut -d, -f1 people.csv
cut -d, -f3 people.csv | sort | head -n 1

Output

ada
grace
alan
cambridge

The field number is the only thing that changes between the two commands. -f1 selects the names and -f3 the cities, and -f2 would give the ages column instead, since the fields are numbered left to right starting at 1.

uniq usually needs sort in front of it because it only collapses duplicates that sit on adjacent lines. Each line is compared solely against the line directly before it, and anything further away is invisible to it.

A file reading apple, banana, apple demonstrates the problem. It contains an obvious duplicate, but the two apples are not neighbors, so uniq on its own changes nothing at all. Sorting first rearranges the lines so identical values become adjacent, and only then can uniq do its job.

The same pipeline answers two related questions about a list of five color votes containing repeats. sort colors.txt | uniq lists each color once, and appending wc -l to that exact pipeline counts how many distinct colors there were.

echo "red" > colors.txt
echo "blue" >> colors.txt
echo "red" >> colors.txt
echo "green" >> colors.txt
echo "blue" >> colors.txt
sort colors.txt | uniq
sort colors.txt | uniq | wc -l | tr -d ' '

Output

blue
green
red
3

The 3 agrees with the three lines printed above it, which is the useful property of building the count from the same pipeline that produced the list. Counting the deduplicated stream is what makes the number a count of distinct colors rather than a count of votes.