Matching 2 main columns between files; and paste other columns into the output file when those main columns match. Keep row size of 1st file intact

Question

This is a follow-up question to my previous question asked about 24 hours ago: Matching two main columns at the same time between files, and paste supplementary columns into the output file when those main columns match

G-Man solved that problem with a useful code, but I have a follow-up question. I already accepted the answer, hence this second post...

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 tables 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.

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

As mentioned before, G-Man provided a useful code that exactly did what I asked for (please see previous post). I will probably ask G-Man (or someone else who has time) some specific questions about some of the individual lines of the code that I don't quite understand yet, but until then, I have that follow-up question.

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

The follow-up question:

One thing I forgot to mention, that the code wasn't able to do (as far as I have seen), is that it will remove rows from file1 if there is no match with columns MAIN1 and MAIN2 between the files. This is my fault, since I did not specify that. My goal is to have an OUTPUT file where no lines from file1 are removed.

Basically file1 is my priority file. Whatever amount of rows this file has (close to a million), that's the amount of rows the OUTPUT file should have too. Columns "minor8" and "minor9" can be empty for some rows if there is no column MAIN1,MAIN2 match. But I would like to keep those rows of file1 when there is a "missing/empty" value for either "minor8" or "minor9" (or both).

I will try to illustrate this using a slightly different version of files 2 and 3 mentioned above (so file1 stays the same).

adjusted file2 (does not have MAIN1,MAIN2 combination: 2,d):

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

adjusted file3 (does not have MAIN1,MAIN2 combination: 5,a):

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

adjusted, desired OUTPUT (i.e., empty value in column minor8 for MAIN1,MAIN2 combination 2-d; and empty value in column minor9 for MAIN1,MAIN2 combination 5-a):

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     sure7
3   bla6    e   blabla6 yes2    sure8
4   bla7    f   blabla7 yes3    sure9
5   bla8    a   blabla8 yes4    
5   bla9    g   blabla9 yes5    sure2

I hope my way of explaining this is clear enough. I see that the tabs of the tables are a bit messed up. Do you guys prefer it like this, or for me to straighten out the tables visually? (only issue that can result from that, I can imagine, is that when you copy-paste my example data, that you would have additional tabs that shouldn't be there...)

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...

Do you have any suggestions how G-Man's code should be edited in order to make this possible? Or if you have a totally different suggestion how a useful code could be written that takes this additional requirement into account, please let me know.

Answer 1

Create the following files:

merge21:

BEGIN {
        FS = "\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]
        else              print $1, $2, $3, $4, "-"
}

merge312:

BEGIN {
        FS = "\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]
        else              print $1, $2, $3, $4, $5, "-"
}

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 key fields include comma(s) and none of your data include hyphens, but it really depends only on there being some strings that don’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 a “left outer join” of the data in your files; see Difference between INNER and OUTER joins on Stack Overflow for some definitions.  (“Left outer join” is defined in the accepted answer to that question as (paraphrased) «all rows in the first table, plus any common rows in the other table(s)».)

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 yes8    sure5
2       bla4    a       blabla4 yes9    sure6
2       bla5    d       blabla5 -       sure7
3       bla6    e       blabla6 yes2    sure8
4       bla7    f       blabla7 yes3    sure9
5       bla8    a       blabla8 yes4    -
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.)

---

Notes

• FS and OFS are the Input Field Separator and the Output Field Separator, respectively.  (Apparently IFS is meaningless in awk; that was an error on my part.)  You probably don’t really need the FS="\t" — awk recognizes tabs as field separators on input by default.  (It lets you have fields that contain spaces, but you don’t seem to be interested in that.)  OFS="\t" is important; because of it, I can say print $1, $2, $3, $4 and get the input fields to be output with tabs between them.  If I didn’t say OFS="\t", they would be separated by spaces, unless I said print $1 "\t" $2 "\t" $3 "\t" $4, which is tedious and impairs readability.

• If you had given additional constraints on MAIN1 and MAIN2 — for example, they are always just one character each, or MAIN1 is always a number and MAIN2 always begins with a letter — I wouldn’t have needed the comma (,) in key.  But the original version of your first question shows no such constraint.  Consider the following data:

  MAIN1 ($2)         MAIN2 ($3)         badkey = $2 $3         goodkey = $2 "," $3
      2              34151                  234151                   2,34151
     23               4151                  234151                   23,4151
  

  If we don’t include some separator character in the key that doesn’t otherwise appear in the key fields (MAIN1 and MAIN2), we can get the same key value for different rows.

• At the risk of splitting hairs, I’m not “telling Linux” anything; I’m telling awk what to do.
• Regarding the code

  NR==FNR {               # file3
          key = $1 "," $2
          present[key] = 1
          minor9[key] = $3
          next
  }

  Consider the seventh-from-the-last line of file3, which contains 1 a sure3.  Obviously we have $1=1, $2=a, and $3=sure3, so key=1,a.  present[key] = 1 means I am setting present["1,a"] to 1 as a flag to indicate that file3 has a 1,a line; i.e., that there is a minor9 value for key=1,a.  Since there is no 5,a line in file3, present["5,a"] doesn’t get set, and so the "file1 + file2" part of the code knows that there is no minor9 for key=5,a, and it should print - instead.  The name present is just an arbitrary choice on my part; it indicates that the 1,a row is present in file3 (and the 5,a row is not).  It's conventional to use 1 to represent “TRUE”.

• You can replace print $1, $2, $3, $4 with for (n=1; n<=4; n++) printf "%s\t", $n.  You should end the line either by using plain print (as opposed to printf) for the last field, or by doing printf "\n".  You can simplify even further by doing something like

          for (n=1; n<=4; n++) printf "%s\t", $n
          if (present[key]) print minor8[key]
          else              print "-"

Please read awk(1), the POSIX specification for awk, The GNU Awk User’s Guide, and see Awk.info for more information.