AWK-如何对文件A中的多个匹配项进行列匹配在文件B中的匹配项 [英] AWK - How to column match multiple matches in file A match one in File B

问题描述

我试图在文件A的第1列与文件B的第2列之间找到匹配的字符串，并为每次匹配打印文件A +文件B的整行.问题是在文件A的第1列中有多个具有相同值的字符串，当我使用awk解决方案时，它只会打印最后一个匹配项，而不是所有匹配项.

I'm trying to find matching strings between column 1 in file A and column 2 in file B and print the entire row of file A + file B for every match. The problem is there are multiple strings with the same value in column 1 of file A and when I use an awk solution it only prints the last match instead of all matches.

我尝试使用以前使用过的awk解决方案来匹配文件A和文件B中的唯一值

I've tried to use an awk solution I've used before for matching unique values in file A and file B

awk -vOFS='\t' 'NR==FNR{a[$1]=$0;next} ($2 in a) {print a[$2],$0}' file A file B

文件A

MLLT3   26.53051423 54.24992354 25.50216856
MLLT3   24.32536694 19.96855016 177.7584507
MLLT3   18.9883621  15.83462512 115.2035222
MLLT3   11.79811105 42.91062427 77.35888553

文件B

ENSG00000171843 MLLT3   3.885477052 3.929504522 3.005321522

预期输出:

MLLT3   26.53051423 54.24992354 25.50216856 ENSG00000171843 MLLT3   3.885477052 3.929504522 3.005321522
MLLT3   24.32536694 19.96855016 177.7584507 ENSG00000171843 MLLT3   3.885477052 3.929504522 3.005321522
MLLT3   18.9883621  15.83462512 115.2035222 ENSG00000171843 MLLT3   3.885477052 3.929504522 3.005321522
MLLT3   11.79811105 42.91062427 77.35888553 ENSG00000171843 MLLT3   3.885477052 3.929504522 3.005321522

实际输出:

MLLT3   11.79811105 42.91062427 77.35888553 ENSG00000171843 MLLT3   3.885477052 3.929504522 3.00532152

我愿意接受任何解决方案，过去我只是将awk用于此类问题.

I'm open to any solution, I've just used awk for this type of problem in the past.

推荐答案

为此我完全不会使用awk.这就是 join 设计的目的:

I wouldn't use awk at all for this. It's what join is designed for:

$ join -t $'\t' -1 1 -2 2 -o 1.1,1.2,1.3,1.4,2.1,2.2,2.3,2.4,2.5 file1.tsv file2.tsv               
MLLT3   26.53051423 54.24992354 25.50216856 ENSG00000171843 MLLT3   3.885477052 3.929504522 3.005321522
MLLT3   24.32536694 19.96855016 177.7584507 ENSG00000171843 MLLT3   3.885477052 3.929504522 3.005321522
MLLT3   18.9883621  15.83462512 115.2035222 ENSG00000171843 MLLT3   3.885477052 3.929504522 3.005321522
MLLT3   11.79811105 42.91062427 77.35888553 ENSG00000171843 MLLT3   3.885477052 3.929504522 3.005321522

这假定文件在相关字段上排序.如果没有:

This assumes that the files are sorted on the relevant fields. If not:

$ join -t $'\t' -1 1 -2 2 -o 1.1,1.2,1.3,1.4,2.1,2.2,2.3,2.4,2.5 <(sort -t $'\t' -k1 file1.tsv) <(sort -t $'\t' -k2 file2.tsv)              

这篇关于AWK-如何对文件A中的多个匹配项进行列匹配在文件B中的匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！