# How to extract values from second file on the basis common first column?

File1:

ENST00000000233 ARF5
ENST00000000412 M6PR
ENST00000001008 FKBP4
ENST00000001146 CYP26B1
ENST00000002501 DBNDD1
ENST00000002596 HS3ST1


File 2:

ENST00000000412 0.779142
ENST00000001008 0.738143
ENST00000002501 0.715315
ENST00000002596 0.713664


Desired Output

ENST00000000412 0.779142    M6PR
ENST00000001008 0.738143    FKBP4
ENST00000002501 0.715315    DBNDD1
ENST00000002596 0.713664    HS3ST1


I like to use the join command for this. For your example above, you can simply use the following:

\$ join file2 file1
ENST00000000412 0.779142 M6PR
ENST00000001008 0.738143 FKBP4
ENST00000002501 0.715315 DBNDD1
ENST00000002596 0.713664 HS3ST1


This assumes your files are sorted. If they aren't sorted, you can use input redirection if your shell supports it (bash and other modern shells do):

join <(sort -k 1b,1 file2) <(sort -k 1b,1 file1)


If your shell doesn't support <(), sort the files first:

sort file1 > file1.sorted; sort file2 > file2.sorted
join <(sort -k 1b,1 file2.sorted) <(sort -k 1b,1 file1.sorted)

• But the number of lines in file 2 and file 1 are not same. In an output file, I need a 1st and 2nd column of file 2 and 3rd column values to be extracted from file1 Jun 26 '19 at 23:21
• The result matches your desired output - can you clarify what you want?
– Scot
Jun 26 '19 at 23:31
• Note that the join command does not simply paste files together, it matches two files based on a common column (by default column 1), and then tacks on the additional columns from the files in the specified order. I think it is exactly what you want, given what you've written. One caveat is that the files must be sorted by the matching column, though in your example above, they already are.
– Scot
Jun 26 '19 at 23:58
• If they're not sorted, this might work: join <(sort -k 1b,1 file1.txt) <(sort -k 1b,1 file2.txt)
– gringer
Jun 27 '19 at 5:05
• @Scot Thank you for the answer, I figured out my file (completed one) was not sorted that's why it didn't work initially Jun 27 '19 at 9:01

Using awk, this can be done in the following way:

awk 'NR==FNR{a[$$1]=$$2; next}$$1 in a{print 1, 2, a[$$1]}' file1.txt file2.txt > same.txt


Explanation:

NR==FNR (along with the next command)


Will go through the first file only and create an associative array of of the geneIDs corresponding with the geneNames.

Then if the 1st column in the 2nd file is present in the array, a, it will print both columns from the file plus the geneName.