TBF

Sorts and stats

Now suppose you want to sort the data on a given column, and then take the top few in that ordering. You can use Miller’s then feature to pipe commands together.

Here are the records with the top three index values:

 $ mlr --icsv --opprint sort -f shape -nr index then head -n 3 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

Lots of Miller commands take a -g option for group-by: here, head -n 1 -g shape outputs the first record for each distinct value of the shape field. This means we’re finding the record with highest index field for each distinct shape field:

 $ mlr --icsv --opprint sort -f shape -nr index then head -n 1 -g shape example.csv
 color  shape    flag  index quantity rate
 yellow circle   true  87    63.5058  8.3350
 purple square   false 91    72.3735  8.2430
 purple triangle false 65    80.1405  5.8240

Statistics can be computed with or without group-by field(s):

 $ mlr --icsv --opprint --from example.csv stats1 -a count,min,mean,max -f quantity -g shape
 shape    quantity_count quantity_min quantity_mean     quantity_max
 triangle 3              43.6498      68.33976666666666 81.229
 square   4              72.3735      76.60114999999999 79.2778
 circle   3              13.8103      47.0982           63.9785

 $ mlr --icsv --opprint --from example.csv stats1 -a count,min,mean,max -f quantity -g shape,color
 shape    color  quantity_count quantity_min quantity_mean      quantity_max
 triangle yellow 1              43.6498      43.6498            43.6498
 square   red    3              77.1991      78.01036666666666  79.2778
 circle   red    1              13.8103      13.8103            13.8103
 triangle purple 2              80.1405      80.68475000000001  81.229
 circle   yellow 2              63.5058      63.742149999999995 63.9785
 square   purple 1              72.3735      72.3735            72.3735

If your output has a lot of columns, you can use XTAB format to line things up vertically for you instead:

 $ mlr --icsv --oxtab --from example.csv stats1 -a p0,p10,p25,p50,p75,p90,p99,p100 -f rate
 rate_p0   0.0130
 rate_p10  2.9010
 rate_p25  4.2370
 rate_p50  8.2430
 rate_p75  8.5910
 rate_p90  9.8870
 rate_p99  9.8870
 rate_p100 9.8870

Other-format examples

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.