The command line for data work: a cheat sheet that was run

The command line is a quick way to look at a file before you load it anywhere. These commands work on any Linux system and in Git Bash on Windows; macOS ships BSD versions of some tools, where a few options differ (check man). All the examples use this small file, orders.csv:

order_id,country,product,quantity,amount
1,DE,pen,2,2.40
2,FR,lamp,1,24.00
3,DE,lamp,1,24.00
4,ES,pen,5,6.00
5,DE,notebook,3,13.50
6,FR,pen,1,1.20

Look at it

cut -d, -f2,5 splits each line at commas (-d,) and keeps fields 2 and 5. It does not understand quoted commas inside a field, so for messy CSV use a proper parser such as Python's csv module.

Count and group

tail -n +2 orders.csv | cut -d, -f2 | sort | uniq -c | sort -nr

      3 DE
      2 FR
      1 ES

Read it left to right as a pipeline. tail -n +2 skips the header, cut takes the country, sort brings equal values together (uniq only collapses neighbours), uniq -c counts them, and sort -nr puts the biggest first. This pattern answers "how many of each?" for any column.

For the distinct values of a column:

tail -n +2 orders.csv | cut -d, -f3 | sort -u

lamp
notebook
pen

Search

grep -c ",lamp," orders.csv       # how many lines match
grep -n "DE" orders.csv            # show matching lines with line numbers

2
2:1,DE,pen,2,2.40
4:3,DE,lamp,1,24.00
6:5,DE,notebook,3,13.50

Useful options: -i ignores case, -v inverts (lines that do not match), -r searches folders recursively, -E enables extended regular expressions.

Calculate with awk

awk treats each line as fields ($1, $2, ...), and -F, sets the separator. NR is the line number.

Total amount by country:

awk -F, 'NR>1 {sum[$2]+=$5} END {for (c in sum) printf "%s %.2f\n", c, sum[c]}' orders.csv | sort

DE 39.90
ES 6.00
FR 25.20

Keep the header and the rows where the amount is above 10:

awk -F, 'NR==1 || $5 > 10' orders.csv

order_id,country,product,quantity,amount
2,FR,lamp,1,24.00
3,DE,lamp,1,24.00
5,DE,notebook,3,13.50

Sort

tail -n +2 orders.csv | sort -t, -k5,5nr | head -2

2,FR,lamp,1,24.00
3,DE,lamp,1,24.00

-t, sets the separator, -k5,5 sorts by field 5 only, n means numeric and r reversed. Without n, 13.50 would sort before 2.40, because text is compared character by character.

Transform text

echo "Hello World" | tr 'A-Z' 'a-z'        # hello world
echo "a,b;c" | sed 's/[,;]/ /g'            # a b c

tr translates characters one for one. sed 's/old/new/g' replaces text matching a pattern.

Files and differences

find logs -name "*.log"         # logs/a.log
diff f1 f2                       # show what differs
comm -12 s1 s2                   # lines common to two SORTED files

diff prints nothing and exits with 0 when the files match, and exits with 1 when they differ, which makes it usable in scripts. comm needs both inputs sorted.

Make it readable

column -s, -t orders.csv

order_id  country  product   quantity  amount
1         DE       pen       2         2.40
2         FR       lamp      1         24.00

Habits that prevent accidents

• Look before you change. Run the pipeline without the final step first, and check the output.
• Never edit the only copy. Write results to a new file (> result.csv), not over the input.
• Mind the header. Many mistakes are the header line being counted, sorted or summed. tail -n +2 and NR>1 skip it.
• Mind the separator and quotes. These tools split on a character. A comma inside a quoted field breaks them, so switch to a real CSV tool when the data is irregular.
• Check line endings. Files from Windows end lines with \r\n. A stray carriage return can make a value fail to match. tr -d '\r' < in.csv > out.csv removes it.