Add a single column/field via join

[masgo]

I have two CSV files with many columns. The exact number and name of the columns might vary, but there is always a column id in both files and a column data in the second (left) file. I want to add the data column to the left file. Is there a better way than that?

mlr \
then join -f left.csv -j id --lp "XXX" \
then rename XXXdata,data \
then cut --complement -r -f '^XXX.*' \
right.csv

What I am doing here is to join both files on their id, add XXX as prefix to the fields from the left file, then rename the field I am interested in and finally remove all XXX fields since they are no longer needed.

The drawback is that the left file contains ~50 fields, some of which contain really long strings. So, I join this, which creates a really wide table, just to delete most of it afterwards. Also, the code is not that readable.

Another question:
Is there a way to keep the field order of the right file and only add the additional column to the end?

[aborruso]

Please could you add two sample input files (only some example rows and not all the fields) and the output you should want from these?

[masgo]

Here is a simple example
right.csv

aa,id,bb
a1,11,b1
a2,11,b2
a3,22,b1
a4,22,b2
a5,33,b1
a6,33,b2

left.csv

cc,id,data,dd
c1,11,black,d1
c2,22,green,d2
c3,33,white,d3

result.csv

aa,id,bb,data 
a1,11,b1,black
a2,11,b2,black
a3,22,b1,green
a4,22,b2,green
a5,33,b1,white
a6,33,b2,white

This would be the ideal result. Here, the columns in the right.csv remain as they where, and only the data column is added. With my above command, I am actually getting this:

id,data,aa,bb
11,black,a1,b1
11,black,a2,b2
22,green,a3,b1
22,green,a4,b2
33,white,a5,b1
33,white,a6,b2

Where the id column has been moved to the left, then data, and then the other columns. This needs a reorder, which is hard to do in a script if the "right" files have different columns.

Often I actually want to "translate" the id column, i.e., replace the numbers with meaningful values from a lookup table. For that it would be nice to have a "join & delete" or "join & replace" .. or just a join where I could set the position of the new columns (first, last, left/right of join column). Then I could just delete the id column afterwards.

[aborruso]

Thank you.

Also in SQL, you have to declare the fields order and also in SQL it is annoying to manage tables with many fields.

It seems to me that you need a template and mlr has the template verb.
It's not the answer to your question but I'll do it all in this way.

First create the template, cloning right.csv and adding an empty data field:

mlr --csv put '$data=""' then head -n 1 right.csv >template.csv

aa,id,bb,data
a1,11,b1,

Then run the join and use the template verb:

mlr --csv join --ul -j id -f right.csv then unsparsify then template -t template.csv left.csv >output.csv

aa,id,bb,data
a1,11,b1,black
a2,11,b2,black
a3,22,b1,green
a4,22,b2,green
a5,33,b1,white
a6,33,b2,white

Then it's simply

# create the template
mlr --csv put '$data=""' then head -n 1 right.csv >template.csv
# do the join and use the template file
mlr --csv join --ul -j id -f right.csv then unsparsify then template -t template.csv left.csv >output

What do you think about?

When I have CSV files I use always unsparsify for this reason.

[masgo]

I did not know template. For the initial problem which started this question, it's not practical, since the join i am talking about is somewhere in the middle of a longer miller instruction chain.
Most of my output needs to fulfill some specific format, therefore the last two lines are often a bulky cut and a bulky reorder command. Template would make this nicer, especially for files with long/cryptic column names.

[aborruso]

I did not know template. For the initial problem which started this question, it's not practical, since the join i am talking about is somewhere in the middle of a longer miller instruction chain.

Ok I have no idea of your chain. I made a suggestion, on what you have made known, on the unknown I cannot help you.
I hope I have been a little useful in any case

[johnkerl]

@masgo it should be pretty easy to add a --left-keep-fields id,data so that while the left file is being ingested, only those fields will be included.