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
a b c,def,g h i
123,4567,890
2468,1357,3579
9987,3312,4543

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
a_b_c,def,g_h_i
123,4567,890
2468,1357,3579
9987,3312,4543
mlr --csv --opprint rename -g -r ' ,_'  data/spaces.csv
a_b_c def  g_h_i
123   4567 890
2468  1357 3579
9987  3312 4543

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
a_b_c def  g_h_i
123   4567 890
2468  1357 3579
9987  3312 4543

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
Back to top