Miller by example¶
Obtaining Miller¶
You can install Miller for various platforms as follows:
Linux:
yum install millerorapt-get install millerdepending on your flavor of LinuxMacOS:
brew install millerorport install millerdepending on your preference of Homebrew or MacPorts .Windows:
choco install millerusing Chocolatey .See also Building from source if you prefer – in particular, if your platform’s package manager doesn’t have the latest release.
Regardless, as a first check, at your system’s command prompt, you should be able to run mlr --version and see something like the following:
$ mlr --version
Miller v6.0.0-dev
As a second check, given (example.csv) you should be able to do
$ mlr --csv cat example.csv
color,shape,flag,index,quantity,rate
yellow,triangle,true,11,43.6498,9.8870
red,square,true,15,79.2778,0.0130
red,circle,true,16,13.8103,2.9010
red,square,false,48,77.5542,7.4670
purple,triangle,false,51,81.2290,8.5910
red,square,false,64,77.1991,9.5310
purple,triangle,false,65,80.1405,5.8240
yellow,circle,true,73,63.9785,4.2370
yellow,circle,true,87,63.5058,8.3350
purple,square,false,91,72.3735,8.2430
$ mlr --icsv --opprint cat example.csv
color shape flag index quantity rate
yellow triangle true 11 43.6498 9.8870
red square true 15 79.2778 0.0130
red circle true 16 13.8103 2.9010
red square false 48 77.5542 7.4670
purple triangle false 51 81.2290 8.5910
red square false 64 77.1991 9.5310
purple triangle false 65 80.1405 5.8240
yellow circle true 73 63.9785 4.2370
yellow circle true 87 63.5058 8.3350
purple square false 91 72.3735 8.2430
If you run into issues on these checks, please check out the resources on the Community page for help.
Miller verbs¶
Let’s take a quick look at some of the most useful Miller verbs – file-format-aware, name-index-empowered equivalents of standard system comments.
mlr cat is like cat – it passes the data through unmodified:
$ mlr --csv cat example.csv
color,shape,flag,index,quantity,rate
yellow,triangle,true,11,43.6498,9.8870
red,square,true,15,79.2778,0.0130
red,circle,true,16,13.8103,2.9010
red,square,false,48,77.5542,7.4670
purple,triangle,false,51,81.2290,8.5910
red,square,false,64,77.1991,9.5310
purple,triangle,false,65,80.1405,5.8240
yellow,circle,true,73,63.9785,4.2370
yellow,circle,true,87,63.5058,8.3350
purple,square,false,91,72.3735,8.2430
but it can also do format conversion (here, you can pretty-print in tabular format):
$ mlr --icsv --opprint cat example.csv
color shape flag index quantity rate
yellow triangle true 11 43.6498 9.8870
red square true 15 79.2778 0.0130
red circle true 16 13.8103 2.9010
red square false 48 77.5542 7.4670
purple triangle false 51 81.2290 8.5910
red square false 64 77.1991 9.5310
purple triangle false 65 80.1405 5.8240
yellow circle true 73 63.9785 4.2370
yellow circle true 87 63.5058 8.3350
purple square false 91 72.3735 8.2430
$ mlr --icsv --ojson cat example.csv
{
"color": "yellow",
"shape": "triangle",
"flag": true,
"index": 11,
"quantity": 43.6498,
"rate": 9.8870
}
{
"color": "red",
"shape": "square",
"flag": true,
"index": 15,
"quantity": 79.2778,
"rate": 0.0130
}
{
"color": "red",
"shape": "circle",
"flag": true,
"index": 16,
"quantity": 13.8103,
"rate": 2.9010
}
{
"color": "red",
"shape": "square",
"flag": false,
"index": 48,
"quantity": 77.5542,
"rate": 7.4670
}
{
"color": "purple",
"shape": "triangle",
"flag": false,
"index": 51,
"quantity": 81.2290,
"rate": 8.5910
}
{
"color": "red",
"shape": "square",
"flag": false,
"index": 64,
"quantity": 77.1991,
"rate": 9.5310
}
{
"color": "purple",
"shape": "triangle",
"flag": false,
"index": 65,
"quantity": 80.1405,
"rate": 5.8240
}
{
"color": "yellow",
"shape": "circle",
"flag": true,
"index": 73,
"quantity": 63.9785,
"rate": 4.2370
}
{
"color": "yellow",
"shape": "circle",
"flag": true,
"index": 87,
"quantity": 63.5058,
"rate": 8.3350
}
{
"color": "purple",
"shape": "square",
"flag": false,
"index": 91,
"quantity": 72.3735,
"rate": 8.2430
}
mlr head and mlr tail count records rather than lines. Whether you’re getting the first few records or the last few, the CSV header is included either way:
$ mlr --csv head -n 4 example.csv
color,shape,flag,index,quantity,rate
yellow,triangle,true,11,43.6498,9.8870
red,square,true,15,79.2778,0.0130
red,circle,true,16,13.8103,2.9010
red,square,false,48,77.5542,7.4670
$ mlr --csv tail -n 4 example.csv
color,shape,flag,index,quantity,rate
purple,triangle,false,65,80.1405,5.8240
yellow,circle,true,73,63.9785,4.2370
yellow,circle,true,87,63.5058,8.3350
purple,square,false,91,72.3735,8.2430
You can sort on a single field:
$ mlr --icsv --opprint sort -f shape example.csv
color shape flag index quantity rate
red circle true 16 13.8103 2.9010
yellow circle true 73 63.9785 4.2370
yellow circle true 87 63.5058 8.3350
red square true 15 79.2778 0.0130
red square false 48 77.5542 7.4670
red square false 64 77.1991 9.5310
purple square false 91 72.3735 8.2430
yellow triangle true 11 43.6498 9.8870
purple triangle false 51 81.2290 8.5910
purple triangle false 65 80.1405 5.8240
You can sort primarily alphabetically on one field, then secondarily numerically descending on another field:
$ mlr --icsv --opprint sort -f shape -nr index example.csv
color shape flag index quantity rate
yellow circle true 87 63.5058 8.3350
yellow circle true 73 63.9785 4.2370
red circle true 16 13.8103 2.9010
purple square false 91 72.3735 8.2430
red square false 64 77.1991 9.5310
red square false 48 77.5542 7.4670
red square true 15 79.2778 0.0130
purple triangle false 65 80.1405 5.8240
purple triangle false 51 81.2290 8.5910
yellow triangle true 11 43.6498 9.8870
If there are fields you don’t want to see in your data, you can use cut to keep only the ones you want, in the same order they appeared in the input data:
$ mlr --icsv --opprint cut -f flag,shape example.csv
shape flag
triangle true
square true
circle true
square false
triangle false
square false
triangle false
circle true
circle true
square false
You can also use cut -o to keep specified fields, but in your preferred order:
$ mlr --icsv --opprint cut -o -f flag,shape example.csv
flag shape
true triangle
true square
true circle
false square
false triangle
false square
false triangle
true circle
true circle
false square
You can use cut -x to omit fields you don’t care about:
$ mlr --icsv --opprint cut -x -f flag,shape example.csv
color index quantity rate
yellow 11 43.6498 9.8870
red 15 79.2778 0.0130
red 16 13.8103 2.9010
red 48 77.5542 7.4670
purple 51 81.2290 8.5910
red 64 77.1991 9.5310
purple 65 80.1405 5.8240
yellow 73 63.9785 4.2370
yellow 87 63.5058 8.3350
purple 91 72.3735 8.2430
You can use filter to keep only records you care about:
$ mlr --icsv --opprint filter '$color == "red"' example.csv
color shape flag index quantity rate
red square true 15 79.2778 0.0130
red circle true 16 13.8103 2.9010
red square false 48 77.5542 7.4670
red square false 64 77.1991 9.5310
$ mlr --icsv --opprint filter '$color == "red" && $flag == 1' example.csv
You can use put to create new fields which are computed from other fields:
$ mlr --icsv --opprint put '$ratio = $quantity / $rate; $color_shape = $color . "_" . $shape' example.csv
color shape flag index quantity rate ratio color_shape
yellow triangle true 11 43.6498 9.8870 4.414868008496004 yellow_triangle
red square true 15 79.2778 0.0130 6098.292307692308 red_square
red circle true 16 13.8103 2.9010 4.760530851430541 red_circle
red square false 48 77.5542 7.4670 10.386259541984733 red_square
purple triangle false 51 81.2290 8.5910 9.455127458968688 purple_triangle
red square false 64 77.1991 9.5310 8.099790158430384 red_square
purple triangle false 65 80.1405 5.8240 13.760388049450551 purple_triangle
yellow circle true 73 63.9785 4.2370 15.09995279679018 yellow_circle
yellow circle true 87 63.5058 8.3350 7.619172165566886 yellow_circle
purple square false 91 72.3735 8.2430 8.779995147397793 purple_square
Even though Miller’s main selling point is name-indexing, sometimes you really want to refer to a field name by its positional index. Use $[[3]] to access the name of field 3 or $[[[3]]] to access the value of field 3:
$ mlr --icsv --opprint put '$[[3]] = "NEW"' example.csv
color shape NEW index quantity rate
yellow triangle true 11 43.6498 9.8870
red square true 15 79.2778 0.0130
red circle true 16 13.8103 2.9010
red square false 48 77.5542 7.4670
purple triangle false 51 81.2290 8.5910
red square false 64 77.1991 9.5310
purple triangle false 65 80.1405 5.8240
yellow circle true 73 63.9785 4.2370
yellow circle true 87 63.5058 8.3350
purple square false 91 72.3735 8.2430
$ mlr --icsv --opprint put '$[[[3]]] = "NEW"' example.csv
color shape flag index quantity rate
yellow triangle NEW 11 43.6498 9.8870
red square NEW 15 79.2778 0.0130
red circle NEW 16 13.8103 2.9010
red square NEW 48 77.5542 7.4670
purple triangle NEW 51 81.2290 8.5910
red square NEW 64 77.1991 9.5310
purple triangle NEW 65 80.1405 5.8240
yellow circle NEW 73 63.9785 4.2370
yellow circle NEW 87 63.5058 8.3350
purple square NEW 91 72.3735 8.2430
You can find the full list of verbs at the Verbs reference page.
Choices for printing to files¶
Often we want to print output to the screen. Miller does this by default, as we’ve seen in the previous examples.
Sometimes we want to print output to another file: just use > outputfilenamegoeshere at the end of your command:
% mlr --icsv --opprint cat example.csv > newfile.csv
# Output goes to the new file;
# nothing is printed to the screen.
% cat newfile.csv
color shape flag index quantity rate
yellow triangle 1 11 43.6498 9.8870
red square 1 15 79.2778 0.0130
red circle 1 16 13.8103 2.9010
red square 0 48 77.5542 7.4670
purple triangle 0 51 81.2290 8.5910
red square 0 64 77.1991 9.5310
purple triangle 0 65 80.1405 5.8240
yellow circle 1 73 63.9785 4.2370
yellow circle 1 87 63.5058 8.3350
purple square 0 91 72.3735 8.2430
Other times we just want our files to be changed in-place: just use mlr -I:
% cp example.csv newfile.txt
% cat newfile.txt
color,shape,flag,index,quantity,rate
yellow,triangle,1,11,43.6498,9.8870
red,square,1,15,79.2778,0.0130
red,circle,1,16,13.8103,2.9010
red,square,0,48,77.5542,7.4670
purple,triangle,0,51,81.2290,8.5910
red,square,0,64,77.1991,9.5310
purple,triangle,0,65,80.1405,5.8240
yellow,circle,1,73,63.9785,4.2370
yellow,circle,1,87,63.5058,8.3350
purple,square,0,91,72.3735,8.2430
% mlr -I --icsv --opprint cat newfile.txt
% cat newfile.txt
color shape flag index quantity rate
yellow triangle 1 11 43.6498 9.8870
red square 1 15 79.2778 0.0130
red circle 1 16 13.8103 2.9010
red square 0 48 77.5542 7.4670
purple triangle 0 51 81.2290 8.5910
red square 0 64 77.1991 9.5310
purple triangle 0 65 80.1405 5.8240
yellow circle 1 73 63.9785 4.2370
yellow circle 1 87 63.5058 8.3350
purple square 0 91 72.3735 8.2430
Also using mlr -I you can bulk-operate on lots of files: e.g.:
mlr -I --csv cut -x -f unwanted_column_name *.csv
If you like, you can first copy off your original data somewhere else, before doing in-place operations.
Lastly, using tee within put, you can split your input data into separate files per one or more field names:
$ mlr --csv --from example.csv put -q 'tee > $shape.".csv", $*'
$ cat circle.csv
color,shape,flag,index,quantity,rate
red,circle,true,16,13.8103,2.9010
yellow,circle,true,73,63.9785,4.2370
yellow,circle,true,87,63.5058,8.3350
$ cat square.csv
color,shape,flag,index,quantity,rate
red,square,true,15,79.2778,0.0130
red,square,false,48,77.5542,7.4670
red,square,false,64,77.1991,9.5310
purple,square,false,91,72.3735,8.2430
$ cat triangle.csv
color,shape,flag,index,quantity,rate
yellow,triangle,true,11,43.6498,9.8870
purple,triangle,false,51,81.2290,8.5910
purple,triangle,false,65,80.1405,5.8240
TBF¶
What’s a CSV file, really? It’s an array of rows, or records, each being a list of key-value pairs, or fields: for CSV it so happens that all the keys are shared in the header line and the values vary data line by data line.
For example, if you have:
shape,flag,index
circle,1,24
square,0,36
then that’s a way of saying:
shape=circle,flag=1,index=24
shape=square,flag=0,index=36
Data written this way are called DKVP, for delimited key-value pairs.
We’ve also already seen other ways to write the same data:
CSV PPRINT JSON
shape,flag,index shape flag index [
circle,1,24 circle 1 24 {
square,0,36 square 0 36 "shape": "circle",
"flag": 1,
"index": 24
},
DKVP XTAB {
shape=circle,flag=1,index=24 shape circle "shape": "square",
shape=square,flag=0,index=36 flag 1 "flag": 0,
index 24 "index": 36
}
shape square ]
flag 0
index 36
Anything we can do with CSV input data, we can do with any other format input data. And you can read from one format, do any record-processing, and output to the same format as the input, or to a different output format.