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