Matching two main columns at the same time between files, and paste supplementary columns into the output file when those main columns match

Question

I have 3 files, each with a unique number of columns, all tab-separated, but some columns are shared between the 3 files. It's the shared columns between the 3 files that I want to use to create some sort of "aggregate" file.

The images below show examples of what the files could look like. Basically I want to match columns MAIN1 and MAIN2 between the files. Both columns between the three files have to match.

I want to add column "minor8" from file2 to the right side of the table in file1 for those lines when MAIN1 and MAIN2 between the two files match. Subsequently, I want to add "minor9" from file3 on the right side of the file1 table for those cases when MAIN1 and MAIN2 between the two files match. Because "minor8" should go immediately next to the rightmost column of file1 (column name: "minor3"), I would like "minor9" to go next to "minor8" into the new OUTPUT file. The OUTPUT file gives an idea what my ideal final file should look like (although this example OUTPUT has some inconsistencies).

I have tried to do one file at a time using some awk command, but I have only been learning about awk a bit since today.

I tried things like:

awk 'NR==FNR{a[$1$3]++;next} $1$3 in a {$1$3==$2$3 FS a[$1$3]; print}' file1 file2 > OUTPUT

but obviously no success because multiple things are probably lacking or blatantly wrong.

And that line would not take file3 into account yet.

If someone has suggestions how to get an OUTPUT file using file1, file2 and file3, that would be great. I hope to learn from your suggestions. If it is too hard to combine 3 files into 1 OUTPUT file in 1 single step (I surely couldn't think of a way how to do this), please let me know how to do this for 1 file at a time. Detailed explanations are very welcome.

Thanks a lot!

Based on the first couple of comments, I realize the posted images were not that helpful, although I did get one very useful solution from one user.

Basically these are examples of 3 files (the "tabs" are a bit messed up)

file1:

MAIN1   minor1  MAIN2   minor3
1  bla1    a    blabla1
1  bla2    b    blabla2
1  bla3    c    blabla3
2  bla4    a    blabla4
2  bla5    d    blabla5
3  bla6    e    blabla6
4  bla7    f    blabla7
5  bla8    a    blabla8
5  bla9    g    blabla9

file2:

minor8  MAIN1   MAIN2
yes1    2   d
yes2    3   e
yes3    4   f
yes4    5   a
yes5    5   g
yes6    1   a
yes7    1   b
yes8    1   c
yes9    2   a

file3:

MAIN1   MAIN2   minor9
5   a   sure1
5   g   sure2
1   a   sure3
1   b   sure4
1   c   sure5
2   a   sure6
2   d   sure7
3   e   sure8
4   f   sure9

desired OUTPUT file:

MAIN1   minor1  MAIN2   minor3  minor8  minor9
1   bla1    a   blabla1 yes6    sure3
1   bla2    b   blabla2 yes7    sure4
1   bla3    c   blabla3 yes8    sure5
2   bla4    a   blabla4 yes9    sure6
2   bla5    d   blabla5 yes1    sure7
3   bla6    e   blabla6 yes2    sure8
4   bla7    f   blabla7 yes3    sure9
5   bla8    a   blabla8 yes4    sure1
5   bla9    g   blabla9 yes5    sure2

G-Man's code was able to recreate this OUTPUT file, so thank you G-Man!

One thing I forgot to mention, that your code isn't able to do (as far as I have seen), is that it will remove rows from file1 if there is no match.

I basically need to keep those rows of file 1 even if files 2 and 3 don't have an exact match between columns MAIN1 and MAIN2. If there is no match, I would just would like to see an empty column entry for that particular MAIN1,MAIN2 combination...

I hope my way of explaining this is clear enough.

Anyways, I very much appreciate you guys' help. Hopefully at some point in the near future I will be able to contribute to this forum, apart from simply asking for help...

So, for instance (if files 2 and 3 would have 1 missing MAIN1,MAIN2 combination, the OUTPUT could look like this):

MAIN1   minor1  MAIN2   minor3  minor8  minor9
1   bla1    a   blabla1 yes6    sure3
1   bla2    b   blabla2 yes7    sure4
1   bla3    c   blabla3         sure5
2   bla4    a   blabla4 yes9    sure6
2   bla5    d   blabla5 yes1    sure7
3   bla6    e   blabla6 yes2    sure8
4   bla7    f   blabla7 yes3    
5   bla8    a   blabla8 yes4    sure1
5   bla9    g   blabla9 yes5    sure2

Do you have any suggestions how G-Man's code should be edited in order to make this possible?

Answer 1

Create the following files:

merge21:

BEGIN {
        IFS = "\t"
        OFS = "\t"
}
NR==FNR {               # file2
        key = $2 "," $3
        present[key] = 1
        minor8[key] = $1
        next
}
{                       # file1
        key = $1 "," $3
        if (present[key]) print $1, $2, $3, $4, minor8[key]
}

merge312:

BEGIN {
        IFS = "\t"
        OFS = "\t"
}
NR==FNR {               # file3
        key = $1 "," $2
        present[key] = 1
        minor9[key] = $3
        next
}
{                       # file1 + file2
        key = $1 "," $3
        if (present[key]) print $1, $2, $3, $4, $5, minor9[key]
}

They are nearly identical; I have bolded the differences.  Now type the command

awk -f merge21 file2 file1 | awk -f merge312 file3 -

This assumes that none of your data include comma(s), but it really depends only on there being some string that doesn't appear in the data.  It would be trivial to extend this to support more columns; I hope that is obvious.  This could be enhanced to do everything in a single awk run, but that would be a bit more complex, and (IMNSHO) not worth the effort.

This produces what is called an “inner join” of the data in your files; see Difference between INNER and OUTER joins on Stack Overflow for some definitions.  If you want to change this to a “left outer join”, which is defined in the accepted ^{_{(and obscenely upvoted)}} answer to that question as (paraphrased) «all rows in the first table, plus any common rows in the other table(s)», replace the last statement in merge21 with

        if (present[key]) print $1, $2, $3, $4, minor8[key]
        else              print $1, $2, $3, $4, "-"

and likewise in merge312.  Assuming that you have deleted the “1 c” line from file2 and the “4 f” line from file4, your output will be

MAIN1   minor1  MAIN2   minor3  minor8  minor9
1       bla1    a       blabla1 yes6    sure3
1       bla2    b       blabla2 yes7    sure4
1       bla3    c       blabla3 -       sure5
2       bla4    a       blabla4 yes9    sure6
2       bla5    d       blabla5 yes1    sure7
3       bla6    e       blabla6 yes2    sure8
4       bla7    f       blabla7 yes3    -
5       bla8    a       blabla8 yes4    sure1
5       bla9    g       blabla9 yes5    sure2

and, obviously, you can remove the - characters with sed.  (And, of course, if your real data actually include hyphens, choose some unused character or string as the placeholder for absent data.)