Filtering CSV Rows From the Command Line With Miller

Miller (mlr) is the tool I reach for when a CSV question has more than one step: keep these rows, compute a field, sort, take the top five. It reads and writes CSV with a real parser, so quoted commas and line breaks are safe, and its verbs compose with then in a way that reads like the question you are answering. It is also opinionated about types in ways that can surprise you, and this article covers both: how to filter and chain, and three defaults that I have seen change a result. It is one of the tools in the tour of working with CSV in the shell.

The shape of a command

mlr --icsv --opprint filter '$status == "refunded"' file.csv

--icsv says the input is CSV, --opprint says to print it as an aligned table for reading. --csv is shorthand for CSV in and CSV out, and --c2p for CSV in and pretty-print out. After the format flags come verbs, and field values are $name. Run it on the small file:

mlr --icsv --opprint filter '$status == "refunded"' orders-small.csv

id customer    city   qty price status
2  Smith, Jane Boston 10  2.25  refunded
8  Gus         Riga   5   20.00 refunded

The two refunded orders, with Smith, Jane as one value. For output you will pass on to another tool, switch to --csv:

mlr --csv filter '$status == "refunded"' orders-small.csv

id,customer,city,qty,price,status
2,"Smith, Jane",Boston,10,2.25,refunded
8,Gus,Riga,5,20.00,refunded

The name is quoted again on the way out, "Smith, Jane", and the output is a valid CSV that any other tool can read. I use --icsv --opprint while exploring and --csv when the result goes anywhere else.

Conditions, and what happens to empty cells

filter takes a boolean expression. It supports comparison, arithmetic and the usual &&, || and !:

mlr --icsv --opprint filter '$price * $qty > 20 && $status != "failed"' orders-small.csv

id customer      city   qty price  status
1  Ana Silva     Lisbon 3   9.50   paid
2  Smith, Jane   Boston 10  2.25   refunded
3  Bo Chen       Oslo   2   100.00 paid
5  Doe "JD" John Rome   7   3.10   paid
8  Gus           Riga   5   20.00  refunded

Five rows pass the test $price * $qty > 20 && $status != "failed". Row 4, Cy Dubois, has no quantity, and is not among them. Be careful here, because the empty cell is not treated the same way everywhere. A comparison on an empty value did what I wanted:

mlr --icsv --opprint filter '$qty > 5' orders-small.csv

id customer      city   qty price status
2  Smith, Jane   Boston 10  2.25  refunded
5  Doe "JD" John Rome   7   3.10  paid
7  Flo           Kyiv   12  1.05  paid

filter '$qty > 5' returned three rows, 2, 5 and 7, with row 4 absent. Arithmetic is different:

mlr --icsv --opprint put '$value = $qty * $price' then cut -f id,qty,price,value then head -n 4 orders-small.csv

id qty price  value
1  3   9.50   28.5
2  10  2.25   22.5
3  2   100.00 200
4  -   15.75  15.75

put '$value = $qty * $price' produced value 28.5, 22.5 and 200 for rows 1 to 3, and 15.75 for row 4, whose qty is empty. The empty quantity was not a zero (that would give 0) and not an error: Miller treats an empty value as absent in arithmetic and returns the other operand. The result looks plausible, which is the problem. A report of order values that includes row 4 at 15.75 has assumed a quantity of one. If empty means "unknown" in your data, handle it explicitly:

mlr --icsv --opprint put 'is_empty($qty) { $value = "unknown" } is_not_empty($qty) { $value = $qty * $price }' then cut -f id,qty,price,value then head -n 4 orders-small.csv

id qty price  value
1  3   9.50   28.5
2  10  2.25   22.5
3  2   100.00 200
4  -   15.75  unknown

With is_empty($qty) as a condition, row 4 got unknown and the others kept their computed values. Or filter those rows out first.

Chaining verbs

The then keyword passes the output of one verb to the next, which is how I build up an answer:

mlr --icsv --opprint sort -nr price then head -n 3 then cut -f id,customer,price orders-small.csv

id customer  price
3  Bo Chen   100.00
8  Gus       20.00
4  Cy Dubois 15.75

sort -nr price then head -n 3 then cut -f id,customer,price gave the three most expensive orders. The order of the verbs matters and costs nothing to change: sort first, then take the top, then keep the columns you want. An aggregate follows the same pattern:

mlr --icsv --opprint filter '$status == "paid"' then put '$total = $qty * $price' then stats1 -a sum,count -f total orders-small.csv

total_sum total_count
262.8     4

The paid orders, a computed total per row and a summary: total_sum 262.8 over total_count 4. And a frequency table:

mlr --icsv --opprint count-distinct -f status then sort -nr count orders-small.csv

status   count
paid     4
refunded 2
pending  1
failed   1

count-distinct -f status then sort -nr count gave paid 4, refunded 2, pending 1, failed 1. The same questions can be asked of csvkit and qsv; Miller's strength is that the expression language lets you ask the ones they have no verb for.

Type inference: three things to know

Miller infers a type for each value from its text. Most of the time you do not notice. Here are the three times I did.

Leading zeros. A file with ZIP codes and a count:

mlr --icsv --ojson cat zips.csv

[
{
  "zip": "02134",
  "n": 1
},
{
  "zip": 90210,
  "n": 2
},
{
  "zip": "00501",
  "n": 3
},
{
  "zip": 31,
  "n": 4
}
]

With JSON output, 02134 and 00501 came out as strings: Miller refuses to treat a number with a leading zero as a number by default. But 90210 is a number, and 0x1F, a ZIP code that is not, became 31: Miller read it as hexadecimal. Inference is per value, so a column ends up with a mixture. If you want every value taken as written, -S turns inference off:

mlr --icsv --ojson -S cat zips.csv

[
{
  "zip": "02134",
  "n": "1"
},
{
  "zip": "90210",
  "n": "2"
},
{
  "zip": "00501",
  "n": "3"
},
{
  "zip": "0x1F",
  "n": "4"
}
]

Every value is now a string, including n, and 0x1F is preserved. With CSV output the original text is kept either way:

mlr --icsv --ocsv put '$n2 = $n + 1' zips.csv

zip,n,n2
02134,1,2
90210,2,3
00501,3,4
0x1F,4,5

The ZIP column is unchanged, 02134, 90210, 00501, 0x1F, and the computed n2 shows n + 1 working on the number. So for pure CSV in and CSV out, inference rarely hurts; it matters when you convert to another format, or compute on a field you assumed was text.

Float noise. Sums are double precision floats:

cd /batches/csv-0001-0200/work
time mlr --icsv --opprint stats1 -a count,sum,mean -f amount -g country then sort -nr sum orders-2m.csv 2>&1 | head -n 4

country amount_count amount_sum         amount_mean
GB      285284       22823884.280000005 80.00408112617603
BR      285957       22933345.88999974  80.19858191965834
JP      285052       22786705.729999844 79.93876811950045

real	0m1.232s
user	0m2.307s
sys	0m0.209s

The per-country table printed 22823884.280000005 for GB and 22933345.88999974 for BR. The true totals are 22823884.28 and 22933345.89; the long tails are binary floating point error, which accumulates over 285,000 additions. Format the result when the figure goes into a report:

cd /batches/csv-0001-0200/work
mlr --icsv --opprint stats1 -a sum -f amount -g country then put '$amount_sum = fmtnum($amount_sum, "%.2f")' then sort -nr amount_sum then head -n 2 orders-2m.csv

country amount_sum
FR      22969870.61
IN      22934328.00

fmtnum($amount_sum, "%.2f") printed France as 22969870.61 and India as 22934328.00. That fixes the display, and not the accumulated error, so for exact money use a tool that sums decimals.

Absent and empty. As above, empty can behave like absent. If a column has gaps, count them first: mlr --icsv --opprint count-distinct -f qty then ... or filter 'is_empty($qty)'.

Streaming, and speed

Miller reads records one at a time, and verbs like head stop early. On the 2,000,000 row file:

cd /batches/csv-0001-0200/work
time mlr --csv filter '$status == "refunded" && $amount > 500' then head -n 3 orders-2m.csv 2>&1

order_id,customer_id,zip,amount,created_at,status,country,note
11896,166857,67158,607.8,2025-03-02 18:51:41,refunded,JP,leave at door
50153,91965,98558,555.36,2025-11-07 06:07:28,refunded,FR,rush order
52450,166735,38706,531.58,2025-08-19 15:11:39,refunded,US,call first

real	0m0.085s
user	0m0.121s
sys	0m0.021s

filter '$status == "refunded" && $amount > 500' then head -n 3 printed three rows and took 0.085 s, because the third match turned up within the first 52,500 lines and Miller stopped. The grouped stats1 above, which needs every row, took 1.23 s. To check the filter itself, I counted the matches and compared:

cd /batches/csv-0001-0200/work
mlr --icsv --ojson filter '$status == "refunded" && $amount > 500' then stats1 -a count -f amount orders-2m.csv
duckdb -list -noheader -c "SELECT count(*) FROM 'orders-2m.csv' WHERE status = 'refunded' AND amount > 500"

[
{
  "amount_count": 25
}
]
25

25 refunded orders over 500 in Miller, 25 in DuckDB. When two tools agree on a count, I trust both more.

Headers and awkward names

For a file with no header, --implicit-csv-header numbers the fields 1, 2, 3, and --headerless-csv-output omits the header on the way out:

printf '1,Ana,3\n2,Bo,5\n' | mlr --csv --implicit-csv-header --headerless-csv-output put '$3 = $3 * 2'

1,Ana,6
2,Bo,10

$3 * 2 doubled the third field of both rows. For a column name with a space, wrap it in braces:

printf 'Order ID,Unit Price\n1,2.5\n' | mlr --icsv --ojson put '${Total} = ${Unit Price} * 4'

[
{
  "Order ID": 1,
  "Unit Price": 2.5,
  "Total": 10
}
]

${Unit Price} worked, and the new field ${Total} was 10 (2.5 times 4). The JSON output shows the numbers as numbers, because Miller inferred them from 1 and 2.5.

How to tell it worked

Count the rows before and after, and read three of the rows the filter dropped. Cross-check one aggregate with a second tool; DuckDB is quick for that. Check any field that might contain leading zeros or hexadecimal-looking text with -S once. And run the verbs separately before chaining them, so you see where an unexpected value enters.

Where Miller's conveniences mislead

When an empty cell means missing, its arithmetic quietly ignores the operand. When a column is an identifier, inference can reformat it in JSON or compute with it. When sums go into a report, float noise has to be formatted away. None of it is a bug; each is a documented default, and the cost of not knowing is a plausible wrong number. The fix is the same in all three: make the assumption explicit in the command, with is_empty, -S or fmtnum, so the next reader of the script can see it.