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

Question

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

terdon · Accepted Answer · 2019-06-27 15:48:46Z

6

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)

terdon♦

9,6625 gold badges21 silver badges47 bronze badges

answered Jun 26, 2019 at 22:54

Scot

7824 silver badges8 bronze badges

$\begingroup$ 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 $\endgroup$
– Megha
Jun 26, 2019 at 23:21
$\begingroup$ The result matches your desired output - can you clarify what you want? $\endgroup$
– Scot
Jun 26, 2019 at 23:31
1

$\begingroup$ 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. $\endgroup$
– Scot
Jun 26, 2019 at 23:58
2

$\begingroup$ If they're not sorted, this might work: join <(sort -k 1b,1 file1.txt) <(sort -k 1b,1 file2.txt) $\endgroup$
– gringer ♦
Jun 27, 2019 at 5:05
$\begingroup$ @Scot Thank you for the answer, I figured out my file (completed one) was not sorted that's why it didn't work initially $\endgroup$
– Megha
Jun 27, 2019 at 9:01

Add a comment |

h3ab74 · Accepted Answer · 2019-06-27 18:21:10Z

1

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.

answered Jun 27, 2019 at 18:04

h3ab74

8365 silver badges14 bronze badges

Add a comment |

2 Answers 2