Using Mlr Miller for Records and Pipelines on the Shell

Most command-line CSV tools come at the problem from the column side: cut this column, filter this one. Miller comes at it from the record side. Each line is a record, a set of named fields, and a verb is something you do to a stream of records, in the same spirit as sort or uniq for text lines but with names and types. Once that clicks, a lot of jobs shrink to one short command, and the format of the input and the output become options you change independently. This article runs Miller 6 on a 200,000-row orders file and shows the verbs I use most, the expression language, format conversion and the one place its numbers need care. It is part of the toolbox in CSV tools worth knowing.

Records, not columns

Miller reads the header and turns every following line into a record whose fields have names. That is why the verbs refer to fields by name (-f order_id,amount) and not by position. A feed whose columns get reordered keeps working, and a feed that renames one fails with an obvious "field not found" and not a silent shift.

The format flags say how to read and write. --icsv reads CSV, --opprint writes an aligned table for the terminal, --ojson writes JSON, and --c2p, --c2j and friends are short forms (CSV to pretty-print, CSV to JSON). Start every exploratory command with --icsv --opprint, since the aligned output is readable:

$ mlr --icsv --opprint head -n 3 then cut -f order_id,amount,status orders-200k.csv
order_id amount status
1        103.1  shipped
2        31.59  failed
3        89.98  pending

Two things are already visible. head -n 3 and cut -f ... are two verbs joined by then, which runs both in one pass over the data without a pipe or a second parse. And cut -f keeps the fields in the order they have in the data, not the order you typed; add -o to take yours.

Group, count, sum

The verbs I reach for first are count-distinct, stats1 and sort. Counting distinct values of a field, then sorting by the count and keeping the top few:

$ mlr --icsv --opprint count-distinct -f country then sort -nr count then head -n 3 orders-200k.csv
country count
IN      28805
FR      28783
BR      28638

stats1 computes statistics, with -a naming the accumulators, -f the fields and -g the group-by fields. It is Miller's GROUP BY:

$ mlr --icsv --opprint stats1 -a count,sum,mean -f amount -g status then sort -f status orders-200k.csv
status   amount_count amount_sum         amount_mean
failed   39926        3196886.2000000123 80.07028502730081
paid     40032        3196797.6999999774 79.85605765387633
pending  39977        3203998.8399999947 80.1460549816143
refunded 39988        3214191.9999999995 80.37891367410222
shipped  40077        3206555.1099999845 80.00985877186378

Look at amount_sum. The totals have float noise, 3196886.2000000123, because Miller adds numbers as 64-bit floating point and a value like 103.1 has no exact binary form. For display, set the output format:

$ mlr --icsv --opprint --ofmt %.2f stats1 -a count,sum,mean -f amount -g status then sort -f status orders-200k.csv
status   amount_count amount_sum amount_mean
failed   39926        3196886.20 80.07
paid     40032        3196797.70 79.86
pending  39977        3203998.84 80.15
refunded 39988        3214192.00 80.38
shipped  40077        3206555.11 80.01

--ofmt %.2f applies to computed floats only, not to values passed through unchanged. If cents matter, say a reconciliation, do not sum money in floats at all; load the data into a database with a decimal type and sum there (summary statistics for a CSV column in SQL).

New fields with put

put runs an expression for each record, with $name for a field. Adding a currency conversion and a tier:

$ mlr --icsv --opprint head -n 3 then put '$amount_eur = fmtnum($amount * 0.92, "%.2f")' then cut -f order_id,amount,amount_eur orders-200k.csv
order_id amount amount_eur
1        103.1  94.85
2        31.59  29.06
3        89.98  82.78

$ mlr --icsv --opprint head -n 4 then put '$tier = $amount >= 100 ? "high" : "standard"' then cut -f order_id,amount,tier orders-200k.csv
order_id amount tier
1        103.1  high
2        31.59  standard
3        89.98  standard
4        24.56  standard

The language has strings, numbers, maps, functions for dates (strptime, sec2gmt), regexes and local variables, and filter takes a boolean expression in the same syntax (mlr filter '$status == "failed" && $amount > 500'). Single-quote the expression in the shell, because $ means something to bash. A mistake in the expression produces a parse error with a position, which is far easier to read than a failed awk one-liner.

Convert formats and split files

Because input and output formats are independent, Miller is a good converter. CSV to JSON:

$ mlr --c2j head -n 2 then cut -f order_id,amount,status orders-200k.csv
[
{
  "order_id": 1,
  "amount": 103.1,
  "status": "shipped"
},
{
  "order_id": 2,
  "amount": 31.59,
  "status": "failed"
}
]

Numbers came out as JSON numbers, and strings as strings, which is Miller inferring types from the text: a field that looks like a number is treated as one. --ojsonl writes one record per line for streaming, and --ixtab or --ojson and others cover most of the other direction. Converting CSV to JSON with Python is the same job in a language, when you need more control.

To split a file by a field, split -g writes one file per value:

$ mlr --icsv --ocsv split -g country orders-200k.csv
$ ls
split_BR.csv  split_DE.csv  split_FR.csv  split_GB.csv  ...   (7 files)
$ head -2 split_BR.csv
order_id,customer_id,zip,amount,created_at,status,country,note
2,193489,34494,31.59,2025-03-03 08:03:33,failed,BR,leave at door

Seven countries, seven files, each with the header. It is the quick answer to "send each region its own file", and splitting a CSV by column value shows other ways.

Type inference and identifiers

Miller infers a type for each field from its text. That is convenient for arithmetic and a hazard for identifiers. In a two-row test, a zip code of 02134 came out of --ojson as the string "02134", and 90210 as the number 90210, so one column held two JSON types. Adding one to each showed the difference: 90210 became 90211, and 02134 gave (error), because Miller does not treat a number with a leading zero as a number by default. That is a kinder failure than silently dropping the zero, and it still leaves a column that behaves differently from row to row.

The fix is to turn inference off for the run. mlr -S (also spelled --infer-none) treats every field as a string:

$ mlr -S --icsv --ojson cat z.csv
[{"code":"A","zip":"02134"},{"code":"B","zip":"90210"}]

Both zips are strings now, which is what an identifier should be. The cost is that arithmetic needs the value converted on purpose, so use -S for conversions and pass-through jobs, and leave inference on when you are summing. Check the output of any conversion for the columns that must stay text, and compare with other tools, since each handles this differently (what each tool does to a zip code).

When Miller is the wrong choice

Miller is not a SQL engine. It has join, but a multi-table query with several aggregates is easier to write in DuckDB, which also reads the file faster: on a 2 million row file, grouping by status took 1.11 seconds in Miller and 0.20 seconds in DuckDB. It is not the best for arithmetic that must be exact, as shown above. And its expression language is its own, which is one more thing for a team to learn, whereas SQL and Python are already known.

Where it shines is the middle: shell pipelines, conversions between CSV, TSV, JSON and fixed formats, and quick record-oriented cleanups where a few verbs replace a script. Choosing between it and csvkit is the subject of choosing between csvkit and Miller. The reference for every verb is the Miller documentation, and the file-format flags are in its file formats page.