Puzzleshot #002 - Groupings with Unix Tools
After verifying the requested components in #001, you look a bit closer at the datafile and want to count how many rows for each region from column 4.
2023-12-01,Deere,X9100,North,847500,dealer,new,12
2024-07-01,Caterpillar,CH95,West,923000,private,used,8
2025-03-11,Deere,X9100,Northwest,745000,dealer,new,15How would you do this with cut / sort / uniq? How about with awk? Now consider what would happen if you had 10x the number of rows. What about 100x? Focus on what happens to memory usage in either case.
Reveal solution
To count the groupings of column 4:
zcat datafile.csv.gz | cut -d',' -f 4 | sort | uniq -c | sort -rnBasically, send the uncompressed CSV to cut, get the fourth column (note the delimiter flag is needed with CSV data), sort it, get a count of each unique entry, and sort from highest to lowest.
The problem comes when you have significant numbers of rows (the meaning of significant depends on your system resources) as sort has to keep all the entries in memory before continuing on.
This is where an awk associative array (ie, hashtable) approach can help dramatically:
zcat datafile.csv.gz | awk -F',' '{counts[$4]++} END {for (region in counts) print region, counts[region]}'This iterates through the data in a single pass and calculates the counts on the fly. Once finished it prints out each region and the count without storing all entries in memory first. The awk version uses memory proportionally to the number of distinct entries in the column.
Note you can change the formatting to fit your needs - for example if you need the format to be exactly the same as uniq -c it’d be:
zcat datafile.csv.gz | awk -F',' '{counts[$4]++} END {for (region in counts) print counts[region], region}'I use this one extremely often, especially for off-the-cuff validations or calculations. You can easily adapt this for different formats / etc and get quick results even on really large files. Next round, we’ll talk about speeding this one up further on modern systems.