Filter and summarize a CSV with Miller

Use Miller to handle a quoted comma, select rows, and sum numeric fields by group in a CSV file.

how-to3 steps

Updated

in short

Miller operates on CSV field names instead of splitting lines by commas. Chain filter, cut, and stats1 to produce small reports.

Use the small fixture below to check the workflow before applying it to your own project.

Tools used

Before you start

  • A Bash or Zsh terminal on Linux or macOS
  • The listed tools installed and available on PATH

Steps

  1. Step 1: Create a CSV with a quoted comma

    The first name contains a comma inside a quoted field.

    sh
    trmn_guide_dir="$(mktemp -d)"
cd "$trmn_guide_dir"
cat > sales.csv <<'EOF'
team,name,amount
blue,"Ada, A.",12
blue,Lin,8
red,Sam,5
EOF

  2. Step 2: Filter and choose columns

    The quoted comma stays within the name field.

    sh
    mlr --csv filter '$amount >= 8' then cut -f name,amount sales.csv
    output
    name,amount
"Ada, A.",12
Lin,8

  3. Step 3: Sum amounts per team

    stats1 names the generated column amount_sum.

    sh
    mlr --csv stats1 -a sum -f amount -g team sales.csv
    output
    team,amount_sum
blue,20
red,5

Further reading

[Miller tutorial](https://miller.readthedocs.io/en/latest/10min/)

FAQ

Why not split each line on commas?
A comma can be part of a quoted CSV field. Use a CSV-aware parser so that values stay in their intended columns.
Does this modify sales.csv?
No. Results go to standard output. Write to a different filename if you want to save a report.

related

Miller details