Find updated rows between two files (csv)

[benedictdudel]

I have two csv files where I would like to find out if a row were inserted, updated or deleted. My files look like:

a.csv:

id,color
1,red
3,blue
2,green
6,yellow
4,pink

b.csv:

id,color
1,red
3,blue
6,black
4,pink
7,gray

With the following command I can find all rows which are present in a.csv but not in b.csv (deleted rows):

$ mlr --csv join --np --ul -j id -f a.csv b.csv
id,color
2,green

With the following command I can find all rows which are present in b.csv but not in a.csv (inserted rows):

$ mlr --csv join --np --ul -j id -f b.csv a.csv
id,color
7,gray

But how can I find out which rows where updated in b.csv? I am looking for the following ouput:

$ ???
id,color
6,black

And is it possible to generate a new csv with an additional column? Something like:

$ ???
id,color,type
2,green,delete
7,gray,insert
6,black,update

[johnkerl]

@benedictdudel very interesting question!!! :D

One thought:

$ mlr --csv join --lp left_ --rp right_ -j id -f a.csv \
  then filter '$left_color != $right_color' \
  b.csv
id,left_color,right_color
6,yellow,black

Or (longer)

$ mlr --csv join --lp left_ --rp right_ -j id -f a.csv \
  then filter '$left_color != $right_color' \
  then cut -xf left_color \
  then rename right_color,color \
  b.csv
id,color
6,black

[johnkerl]

Then all together:

$ mlr --csv join --np --ul -j id -f a.csv then put '$type="delete"' b.csv | tee 1.csv
id,color,type
2,green,delete

$ mlr --csv join --np --ur -j id -f a.csv then put '$type="insert"' b.csv | tee 2.csv
id,color,type
7,gray,insert

$ mlr --csv join --lp left_ --rp right_ -j id -f a.csv \
>   then filter '$left_color != $right_color' \
>   then cut -xf left_color \
>   then rename right_color,color \
>   then put '$type="update"' \
>   b.csv | tee 3.csv
id,color,type
6,black,update

$ mlr --csv cat 1.csv 2.csv 3.csv
id,color,type
2,green,delete
7,gray,insert
6,black,update

[benedictdudel]

@johnkerl wow 💯 Thank you very much for your fast and informative response!! I have one last question. Is there a special variable or some other method to compare the whole row and not just a single column? Because in my original csv there are like 50 different columns. I tried $* but that didn't seem to work:

$ mlr --csv join --lp left_ --rp right_ -j id -f a.csv \
>   then filter '$* != $*' \
>   then cut -xf left_color \
>   then rename right_color,color \
>   then put '$type="update"' \
>   b.csv | tee 3.csv

[johnkerl]

@benedictdudel alas no, join is by field not by full record :(

[janxkoci]

FWIW the standard unix tool for this job is diff (check also diff -y), which works on whole rows.