Compare .csv files with BASH while merging one column

Question

I am having issue with matching the files together, $GROUP_MEMBERS_FILE hold the info about the members of a group in csv format:

group,mail
eg.
GROUP1,[email protected]
GROUP2,[email protected]

The second file $GROUP_CHANGES_FILE holds the info about the adding to group membership, in .csv format:

date,group,mail
eg.
2021-04-01T14:25:37+00:00,GROUP2,[email protected]
2020-02-01T12:23:47+00:00,GROUP1,[email protected]
2020-02-01T13:23:47+00:00,GROUP1,[email protected]
2021-02-01T12:23:47+00:00,GROUP3,[email protected]

And I am trying to accomplish to filter that matches of $GROUP_MEMBERS_FILE to $GROUP_CHANGES_FILE, although with parsing also the date column to $FILTERING_FILE. The furthest I got, is finding the match, however I am not able to parse the date value to $FILTERING_FILE, to have the same format as this result example:

date,group,mail
eg.
2021-04-01T14:25:37+00:00,GROUP2,[email protected]
2020-02-01T12:23:47+00:00,GROUP1,[email protected]

The code which is not able to give me the date merged, however is matching the files(all files are not sorted):

awk 'BEGIN{FS=","};FNR==NR{a[$1];next};!($1 in a)' $GROUP_MEMBERS_FILE $GROUP_CHANGES_FILE > $FILTERING_FILE

Does someone has an idea how to do it the easiest way ? Thanks, Appreciate that!

Answer 1

I didn't really understand the explanation in your question, sorry, but this seems to be what you're trying to do:

$ awk -F',' 'NR==FNR{a[$1,$2]; next} ($2,$3) in a' members changes
2021-04-01T14:25:37+00:00,GROUP2,[email protected]
2020-02-01T12:23:47+00:00,GROUP1,[email protected]