I want to use the first two columns of each file to add additional columns from fil2, file3, and file4 to file1 at the appropriate rows (where the first two columns match). File2 has three columns to add to file1, but all other files have just one column to add, the last one.
The entries NW_456 44
and NW_987 75
are not annotated in file3 and thus missing. I would like to keep this empty in the output file for that particular column (without it actually saying 'empty').
Example:
file1
NW_1234 23
NW_1234 29
NW_1234 778
NW_456 44
NW_987 75
NW_987 98
NW_5000 105
NW_5500 37
NW_5500 900
file2
NW_1234 23 C 0:0:32:0:0:0 42:0:0:0:0:0
NW_1234 29 C 0:0:28:0:0:0 0:28:0:0:0:0
NW_1234 778 C 0:54:0:0:0:0 0:0:53:0:0:0
NW_456 44 G 0:0:0:45:0:0 59:0:0:0:0:0
NW_987 75 G 0:0:0:60:0:0 55:0:0:0:0:0
NW_987 98 C 0:0:63:0:0:0 0:42:0:0:0:0
NW_5000 105 G 0:0:71:0:0:0 0:50:0:0:0:0
NW_5500 37 G 0:0:0:54:0:0 55:0:0:0:0:0
NW_5500 900 A 43:0:0:0:0:0 0:0:0:37:0:0
file3
NW_1234 23 DOCK
NW_1234 29 DOCK
NW_1234 778 DOCK
NW_987 98 TFEC
NW_5000 105 MIN
NW_5500 37 LIPG
NW_5500 900 MYC
FILE4
NW_1234 23 intron_region
NW_1234 29 intron_region
NW_1234 778 intron_region
NW_456 44 intergenic
NW_987 75 intergenic
NW_987 98 intron_region
NW_5000 105 intron_region
NW_5500 37 intron_region
NW_5500 900 intron_region
outputfile
NW_1234 23 C 0:0:32:0:0:0 42:0:0:0:0:0 DOCK intron_region
NW_1234 29 C 0:0:28:0:0:0 0:28:0:0:0:0 DOCK intron_region
NW_1234 778 C 0:54:0:0:0:0 0:0:53:0:0:0 DOCK intron_region
NW_456 44 G 0:0:0:45:0:0 59:0:0:0:0:0 (empty) intergenic
NW_987 75 G 0:0:0:60:0:0 55:0:0:0:0:0 (empty) intergenic
NW_987 98 C 0:0:63:0:0:0 0:42:0:0:0:0 TFEC intron_region
NW_5000 105 G 0:0:71:0:0:0 0:50:0:0:0:0 MIN intron_region
NW_5500 37 G 0:0:0:54:0:0 55:0:0:0:0:0 LIPG intron_region
NW_5500 900 A 43:0:0:0:0:0 0:0:0:37:0:0 MYC intron_region
Similar to this question: Adding column based on matching of second column
Any help is appreciated!
perl
available?