Replace one column value by match on another column from a Key-Value 2nd file

[pjfarleyiii]

I have a need to perform a "replace column value" operation for all lines in a primary csv based on a match to a second csv that has only a key and value pair in two columns. I tried using a join verb but I cannot figure out how to preserve only the primary file columns and header and record order and just "leave alone" the lines without a match.

The Key-Value file has only two columns, Name and Category.

The primary file has many columns including Name and Category. In the primary file I want to replace the primary Category value for all lines that have a matching Name in the Key-Value file with the Key-Value file Category value for that Name.

If the Name value of a line does not match any Name in the Key-Value file it should keep its current value. Optionally, unmatched Names could be given a default Category value (like Uncategorized), but that is just desirable and not a requirement.

The output column order when all operations are complete must be identical to the original primary file column order, with no added columns. The final output header line must be exactly the same as the original primary file header line. All output records must be in the same relative order as the input primary file, with no lines added or deleted.

Is this possible using miller command line verbs only, and not a DSL program? I'm not afraid of having to write a program, but if it isn't necessary I'd prefer not to have to do that.

TIA for any help or RTFM you can provide.

[aborruso]

Please add always sample input and output files.

The input files

input.csv

id,Name,Category
1,a,7
2,b,9
3,c,2
4,a,7
5,b,9

value.csv

Name,Category
a,1
b,2

The steps

• add progressive numeric column, to the input to have the original order

mlr --csv cat -n input.csv >tmp.csv

+---+----+------+----------+
| n | id | Name | Category |
+---+----+------+----------+
| 1 | 1  | a    | 7        |
| 2 | 2  | b    | 9        |
| 3 | 3  | c    | 2        |
| 4 | 4  | a    | 7        |
| 5 | 5  | b    | 9        |
+---+----+------+----------+

• run a left join, creating a r_Category field, for the value.csv file
  • then if the right category is not empty $Category=$r_Category
  • then sort all, using the n field you have added;
  • then use the original input file as template for fields selection and order.

mlr --csv join --ul -j Name --rp r_ -f tmp.csv then \
put 'if(is_not_null($r_Category)){$Category=$r_Category}else{$Category=$Category}' then \
unsparsify then \
sort -f n then \
template -t input.csv values.csv >output.csv

+----+------+----------+
| id | Name | Category |
+----+------+----------+
| 1  | a    | 1        |
| 2  | b    | 2        |
| 3  | c    | 2        |
| 4  | a    | 1        |
| 5  | b    | 2        |
+----+------+----------+

[johnkerl]

Nice!

Just a little bonus keystroke-saver:

We can replace

put 'if(is_not_null($r_Category)){$Category=$r_Category}else{$Category=$Category}'

with

put '$Category = $r_Category ?? $Category'

using the absent-coalesce operator

so

mlr --csv \
  join --ul -j Name --rp r_ -f tmp.csv \
  then put '$Category = $r_Category ?? $Category' \
  then unsparsify \
  then sort -f n \
  then template -t input.csv values.csv

[aborruso]

using the absent-coalesce operator

how much I would like to become a more experienced DSL user!!

[johnkerl]

@aborruso no worries, it's brand-new in Miller 6 :)

[pjfarleyiii]

Thank you for that solution, it works just as I need, and adding a simple "elif" to the put phrase I was able to populate a custom default value for other than "known cases" of no-match as well.

Apologies for not including sample data in my original question.