Skip to content
Quick links:   Flags   Verbs   Functions   Glossary   Release docs

Operating on all fields

Bulk rename of fields

Suppose you want to replace spaces with underscores in your column names:

cat data/spaces.csv
column 1,column 2,column 3
apple,ball,cat
dale egg,fish,gale

The simplest way is to use mlr rename with -g (for global replace, not just first occurrence of space within each field) and -r for pattern-matching (rather than explicit single-column renames):

mlr --csv rename -g -r ' ,_'  data/spaces.csv
column_1,column_2,column_3
apple,ball,cat
dale egg,fish,gale
mlr --csv --opprint rename -g -r ' ,_'  data/spaces.csv
column_1 column_2 column_3
apple    ball     cat
dale egg fish     gale

You can also do this with a for-loop:

cat data/bulk-rename-for-loop.mlr
map newrec = {};
for (oldk, v in $*) {
    newrec[gsub(oldk, " ", "_")] = v;
}
$* = newrec
mlr --icsv --opprint put -f data/bulk-rename-for-loop.mlr data/spaces.csv
column_1 column_2 column_3
apple    ball     cat
dale egg fish     gale

Bulk rename of fields with carriage returns

The previous example isn't sufficient when there are carriage returns in the field names. Here we can use the Miller programming language:

cat data/header-lf.csv
"field 
A",field B
1,2
3,3
6,6
mlr --csv --from data/header-lf.csv put '
  map inrec = $*;
  $* = {};
  for (oldkey, value in inrec) {
    newkey = clean_whitespace(gsub(oldkey, "\n", " "));
    $[newkey] = value;
  }
'
field A,field B
1,2
3,3
6,6

Search-and-replace over all fields

How to do $name = gsub($name, "old", "new") for all fields?

cat data/sar.csv
a,b,c
the quick,brown fox,jumped
over,the,lazy dogs
cat data/sar.mlr
for (k in $*) {
  $[k] = gsub($[k], "e", "X");
}
mlr --csv put -f data/sar.mlr data/sar.csv
a,b,c
thX quick,brown fox,jumpXd
ovXr,thX,lazy dogs

Full field renames and reassigns

Using Miller 5.0.0's map literals and assigning to $*, you can fully generalize rename, reorder, etc.

cat data/small
a=pan,b=pan,i=1,x=0.346791,y=0.726802
a=eks,b=pan,i=2,x=0.758679,y=0.522151
a=wye,b=wye,i=3,x=0.204603,y=0.338318
a=eks,b=wye,i=4,x=0.381399,y=0.134188
a=wye,b=pan,i=5,x=0.573288,y=0.863624
mlr put '
  begin {
    @i_cumu = 0;
  }

  @i_cumu += $i;
  $* = {
    "z": $x + y,
    "KEYFIELD": $a,
    "i": @i_cumu,
    "b": $b,
    "y": $x,
    "x": $y,
  };
' data/small
z=0.346791,KEYFIELD=pan,i=1,b=pan,y=0.346791,x=0.726802
z=0.758679,KEYFIELD=eks,i=3,b=pan,y=0.758679,x=0.522151
z=0.204603,KEYFIELD=wye,i=6,b=wye,y=0.204603,x=0.338318
z=0.381399,KEYFIELD=eks,i=10,b=wye,y=0.381399,x=0.134188
z=0.573288,KEYFIELD=wye,i=15,b=pan,y=0.573288,x=0.863624