I asked a similar question yesterday (Merging two tables including multiple ocurrence of column identifiers) but ran into a problem with unique lines.
I would like to merge two tables based on column 1:
File 1:
1 today
1 green
2 tomorrow
3 red
File 2:
1 a lot
1 sometimes
2 at work
2 at home
2 sometimes
3 new
4 a lot
5 sometimes
6 at work
Desired output (file 3):
1 today a lot
1 today sometimes
1 green a lot
1 green sometimes
2 tomorrow at work
2 tomorrow at home
2 tomorrow sometimes
3 red new
I came up with the following:
awk -F '[\t]' -v OFS='\t' '{i=$1;$1=x} NR==FNR{A[i]=$0;next} A[i]{print i,$0A[i]}' file2 file1 > file3
However, it gives me only:
1 today sometimes
2 tomorrow sometimes
3 red new
Please note that the solutions in the previous thread (join and awk) would give me a combination of the 2 files including all lines. I would like to have only the lines of file 1 (column 1 as the identifier) but report all matching occurrences in file 2.
Edit:
columns are tab separated
Real File 1: fig|395961.4.peg.2627 Bacteria Cyanobacteria unknown unknown 1795
(Column1: fig... Column2: Bacteria... Column3 1795)
Real File 2: fig|1000561.3.peg.1838 Cysteine desulfurase (EC 2.8.1.7) Test - Thiamin Cofactors, Vitamins, Prosthetic Groups, Pigments
(Column1: fig... Column2: Cysteine... Column3 Test...)