Merge two lists with a join in a column

Question

I am trying to combine two lists by combining them with a common field such as ENST00000371026. I tried the following but no luck. What is the actual way to do this?

cat> gar1.txt <<EOF
ENST00000371026 ENSG00000152763
ENST00000371023 ENSG00000152763
ENST00000395250 ENSG00000152763
ENST00000309502 ENSG00000163485
ENST00000377464 ENSG00000142599
ENST00000400908 ENSG00000142599
ENST00000337907 ENSG00000142599
ENST00000400907 ENSG00000142599
ENST00000401087 ENSG00000179571
EOF

cat> gar2.txt <<EOF
DDX11L1 ENST00000371026
DDX11L9 ENST00000309502
DDX11L1 ENST00000371026
OR4F5   ENST00000377464
DQ597235        n/a
DQ599768        n/a
LOC388312       ENST00000401087
LOC100132287    ENST00000425496
LOC100132287    ENST00000425496
EOF

join -t"\t" -2 2 -1 1 gar1.txt gar2.txt

Output Example:

DDX11L1 ENSG00000152763
DDX11L9 ENSG00000163485
OR4F5   ENSG00000142599
LOC388312       ENSG00000179571
LOC100132287    NONE

Answer 1

Try:

join -a 2 -t $'\t' -2 2 -1 1 -o 2.1 1.2 <(sort gar1.txt) <(sort -k 2,2 gar2.txt) | sed '/\t$/ s/$/NONE/' | uniq

It uses Bash process replacement ( <()), but you can just interrupt your files if you don't want to use this. Your version joinmust have a parameter -o, or you can use awk to further process the output. For the lines "n / a" it will print "NONE", but you can do this to eliminate them:

<(sort -k 2,2 gar2.txt | grep -v 'n/a$')

Edit:

, sed uniq:

DDX11L9 ENSG00000163485
DDX11L1 ENSG00000152763
DDX11L1 ENSG00000152763
OR4F5   ENSG00000142599
LOC388312       ENSG00000179571
LOC100132287
LOC100132287
DQ597235
DQ599768

:

DDX11L9 ENSG00000163485
DDX11L1 ENSG00000152763
OR4F5   ENSG00000142599
LOC388312       ENSG00000179571
LOC100132287    NONE
DQ597235        NONE
DQ599768        NONE

grep:

DDX11L9 ENSG00000163485
DDX11L1 ENSG00000152763
OR4F5   ENSG00000142599
LOC388312       ENSG00000179571
LOC100132287    NONE

, . , .