A quick tutorial on Miller

The input file has fields protected by double quotes, numbers with commas as decimal separator (Italian locale), and “-” in place of zeroes. A sample is available here and following table shows its content, verbatim

cat ./csv-cli-tools-input.csv

"name","surname","major","total","d1","d2"
"john","smith","arts","20","-","-16,8"
"carl","doe","engineering","30","-","12"
"adam","white","engineering","30","-","14,3"
"john","brown","arts","24","-","17,8"
"janet","smith","engineering","22","-","16,4"
"jane","doe","arts","20","14,8","26,8"
"anne","carol","engineering","10","12,2","26,3"

These are the tasks I had to perform on the file:

1. Pre-process the file to simplify analyses: convert separator in decimal numbers and replace “-” with zeroes
2. Compute descriptive statistics of “total”
3. Compute average total by major (pivot)
4. Transpose row and columns of the pivot table computed at the previous step
5. Select surname and total
6. Select all records with d1 < 13.0
7. Sort record by total

One of the issues with the file is the use of “,” both as field delimiter and separator of decimal places, which causes datamash to incorrectly parse the file.

We fix the format by:

• using sed to replace “-” with “0.00”
• using miller to convert the file to tsv, so that commas will appear only when used as decimal separators
• using sed to replace “,” with “.”, so that decimal numbers can be correctly interpreted as such

sed 's/"-"/"0.00"/g' csv-cli-tools-input.csv | mlr --icsv --otsv cat | \
    sed 's/,/./g' | mlr --itsv --ocsv  cat > csv-cli-tools-input-cleaned.csv

Now we are ready to perform the analyses.

mlr --from csv-cli-tools-input-cleaned.csv --icsv --ocsv \
    stats1 -a p0,p25,p50,mean,p75,p100,stddev,var -f total 

Notice that we have sued two “tricks” to simplify command composition and readability:

1. the --from options allows to put the input file as first argument. This makes the command more readable and simplifies composition of the command from the command line
2. the then keyword allows to compose different operations, reducing the number of pipes you need. For instance mlr cmd | mlr cmd2 can usually be replaced by mlr cmd1 then cmd2

mlr --from csv-cli-tools-input-cleaned.csv --icsv --ocsv \
    stats1 -a mean -f total -g major then \
    format-values -f "%.2f"

Note. Org Mode cleans data a bit by removing one zero from the second number.