Using new fields created by put and splitax (Miller 6)

[NikosAlexandris]

Miller 6 features the splita and splitax conversion built-in functions. The following

put '$property = splitax($1, "_vs_")'

magically creates two new fields property.1 and property.2 for an input string like reflectance_1_vs_albedo for example
(see also https://johnkerl.org/miller6/reference-dsl-builtin-functions/#splitax).

Going further, however, like

put '$property = splitax($1, "_vs_")' then put '$new = ${property.1}'

does not create the new field. Should I not expect it to work like that?

[trantor]

Hey @NikosAlexandris . Slightly off-topic, but could you specify a bit more explcitly that you are talking about Miller 6 here? It's clear only by looking at the docs link.

[NikosAlexandris]

You are right. And it's on-topic! Thank you.

ps- Can we add labels? @johnkerl

[johnkerl]

Sorry @NikosAlexandris -- do you mean adding the concept of labels to Miller Discussions -- ?

[johnkerl]

If so I just added the go-port label to this discussion ...

[NikosAlexandris]

Sorry @NikosAlexandris -- do you mean adding the concept of labels to Miller Discussions -- ?

Indeed to Discussions for users (?) (and I guess it's there for issues too (however, not available for simple users to assign one)).

[NikosAlexandris]

Miller 6 features the splita and splitax conversion built-in functions. The following

put '$property = splitax($1, "_vs_")'

magically creates two new fields property.1 and property.2 for an input string like reflectance_1_vs_albedo for example
(see also https://johnkerl.org/miller6/reference-dsl-builtin-functions/#splitax).

Going further, however, like

put '$property = splitax($1, "_vs_")' then put '$new = ${property.1}'

does not create the new field. Should I not expect it to work like that?

First and best, read the developer's comments below! Then, the solution I have found, is to either split the calls via a | or pre-create the new.1 and new.2 fields, since fields will be assigned the last value (for the same key) while streaming. But it's not that clean!

Overall and in the case of using two calls, like

mlr put '$property = splitax($1, "_vs_")' |mlr put '$new = ${property.1}'

if a string does not meet the split condition, only one field new.1 will be created, while strings that will be split will derive two columns new.1 and new.2. This will create some discontinuity in the data too I think -- non-splitable field will derive records with one field less.
Pre-creating the new.1 and new.2 columns, makes the single call

mlr put '$new.1 = "" ; $new.2 = "" ; $property = splitax($1, "_vs_")' then put '$new = ${property.1}'

work, as well as preserving "continuity" in the data, i.e. all records have the same (number of) fields.

[NikosAlexandris]

Then, do some extra work to communicate this as best as possible, in the right places. In the intro of Miller too, I guess.

[johnkerl]

Then, do some extra work to communicate this as best as possible, in the right places. In the intro of Miller too, I guess.

@NikosAlexandris how is https://johnkerl.org/miller6/10min/#file-formats-and-format-conversion ?

[NikosAlexandris]

Looks like a project taken care with love and more. Awesome.

[NikosAlexandris]

@johnkerl So, I am using my "solution" currently which I needed several times after reshaping a wide table to a long table, for example, to make better sense of the attributes of the covariables (after a correlation). So, if I split strings on the '_' character and some strings are giving out 4 fields, others 5 and maybe 6 or more, then I pre-create this 6 or more fields in order to ensure continuity in the new long-table.

Just to be sure if there is not other, easier and better way. I don't know if makes sense to make a general case out of this, but for my use case, it would be indeed quite useful to be able to say in Miller's own laguage: read all the strings/records for a specific field, split them on '_' and create as many new fields as the maximum number of fields that came out of the splita[x].

An example from my work, with an input

➜  mlr.go cat input_widetable.csv |tidy-viewer

      tv dim: 15 x 187
      date       a1_e_29_vs_mga_sur_r_1 a1_e_29_vs_mga_sur_r_2  a1_e_29_vs_mga_sur_r_3 a1_e_29_vs_mga_sur_r_4
1     2019-01-07 -0.512                 -0.4                   -0.497                   -0.503
2     2019-01-23 -0.252                 -0.224                 -0.256                   -0.264
3     2019-02-24 -0.00708               -0.129                 0.10                     0.0246
4     2019-03-12 0.229                  -0.253                 0.152                    0.10
5     2019-03-28 -0.0318                -0.251                 0.04                     -0.01
6     2019-04-13 0.12                   0.136                  0.195                    0.153
7     2019-04-29 0.06                   -0.010                 0.0033                   0.0333
8     2019-05-15 -0.09                  -0.0471                0.0847                   -0.0533
9     2019-05-31 -0.123                 0.0425                 -0.08                    -0.112
10    2019-07-02 -0.03                  -0.214                 0.0792                   -0.006
11    2019-07-18 -0.169                 0.122                  -0.167                   -0.16
12    2019-08-03 -0.202                 0.158                  -0.222                   -0.199
13    2019-09-04 -0.225                 -0.028                 -0.222                   -0.2
14    2019-11-23 0.461                  0.48                   0.462                    0.459
15    2019-12-09 0.0723                 0.006                  -0.01                    0.0468

I convert to long-format and rename/add fields that are later on useful in categorising the data via

mlr.go cat input.csv \
|mlr.go reshape -r '[^date]' -o covariables,correlation \
then put '${variable.1} = "" ;${variable.2} = "" ;$variable = splitax($covariables, "_vs_")' \
|mlr.go put '$band = splitax(${variable.1}, "_")' \
|mlr.go put '${property.1} = "" ; ${property.2} = "" ; ${property.3} = "" ; ${property.4} = "" ; ${property.5} = "Raw" ; $property = splitax(${variable.2}, "_")' \
|mlr.go put 'if(${property.2} == "sur") {${property.2} = ${property.3} ; ${property.3} = ${property.4} ; ${property.4} = ""}' \
|mlr.go put 'if(${property.1} == "savitzkygolay") {${property.1} = ${property.2} ; ${property.2} = ${property.3} ; ${property.3} = ${property.4} ; ${property.4} = ${property.5} ; ${property.5} = "Savitzky-Golay"}'  \
|mlr.go put 'if(${property.2} == "sur") {${property.2} = ${property.3} ; ${property.3} = ${property.4} ; ${property.4} = ""}' \
|mlr.go put 'if(${property.2} == "lst" && ${property.5} == "Savitzky-Golay") {${property.4} = ""}' \
|mlr.go put 'if(${property.4} == "Raw") {${property.4} = ""}' \
|mlr.go cut -x -f covariables,variable.1,band.1,band.2 \
|mlr.go renamecorrelation,Correlation,variable.2,Covariable,band.3,'Emissive band',property.1,Product,property.2,Property,property.3,Set,property.4,Subset,property.5,Filter \
> output_longtable.csv

If an option -a (like add) for put would exist to save all of the work of pre-creating new fields, then the first part of the command

mlr.go cat input.csv \
|mlr.go reshape -r '[^date]' -o covariables,correlation \
then put '${variable.1} = "" ;${variable.2} = "" ;$variable = splitax($covariables, "_vs_")' \
|mlr.go put '$band = splitax(${variable.1}, "_")' \
|mlr.go put '${property.1} = "" ; ${property.2} = "" ; ${property.3} = "" ; ${property.4} = "" ; ${property.5} = "Raw" ; $property = splitax(${variable.2}, "_")'

could be shorter, or say more time-saving, like:

mlr.go cat input.csv \
|mlr.go reshape -r '[^date]' -o covariables,correlation \
then put -a '$variable = splitax($covariables, "_vs_")' \
|mlr.go put '$band = splitax(${variable.1}, "_")' \
|mlr.go put -a '$property = splitax(${variable.2}, "_")' \
..
> output_longtable.csv

Hope this all makes sense.

[johnkerl]

@NikosAlexandris I'll check it out! Meanwhile in Miller 6 there's ??= which may be a little use ...

https://johnkerl.org/miller6/reference-dsl-builtin-functions/#absent-coalesce