awk匹配两个文件中的三列，并将匹配的行追加到一个新文件中

Question

有很多类似的帖子。我花了几个小时来解决这个问题，我绝望了，因为它看起来应该很简单。

我有一个像这样的文件:

tig00000005 15310   16162   XP_012153921.1  NW_003797090.1  LOC105664333    PREDICTED: elastin-like
tig00000005 23339   23974   XP_012152584.1  NW_003797083.1  LOC100878991    PREDICTED: LOW QUALITY PROTEIN
tig00000005 24600   25138   XP_012143166.1  NW_003797196.1  LOC100881279    PREDICTED: ankyrin-2 isoform X2
tig00000005 2685    4511    XP_012144644.1  NW_003797249.1  LOC105662970    PREDICTED: fibrinogen alpha chain-like isoform X2
tig00000005 28923   29432   XP_012148395.1  NW_003797444.1  LOC100881617    PREDICTED: eukaryotic translation initiation factor 4 gamma 3-like isoform X12
tig00000005 32415   34324   XP_012153921.1  NW_003797090.1  LOC105664333    PREDICTED: elastin-like

第二个文件看起来像这样:

tig00000005 maker   gene    15310   16162   .   +   .   ID=snap_masked-tig00000005-processed-gene-0.2;Name=snap_masked-tig00000005-processed-gene-0.2
tig00000005 maker   gene    16764   17237   .   +   .   ID=snap_masked-tig00000005-processed-gene-0.3;Name=snap_masked-tig00000005-processed-gene-0.3
tig00000005 maker   gene    23339   23974   .   +   .   ID=snap_masked-tig00000005-processed-gene-0.4;Name=snap_masked-tig00000005-processed-gene-0.4
tig00000005 maker   gene    24600   25138   .   -   .   ID=snap_masked-tig00000005-processed-gene-0.10;Name=snap_masked-tig00000005-processed-gene-0.10
tig00000005 maker   gene    25472   26900   .   +   .   ID=snap_masked-tig00000005-processed-gene-0.5;Name=snap_masked-tig00000005-processed-gene-0.5

我想将第一个文件中的1、2和3列与第二个文件中的1、4和5列匹配，如果它们匹配，将第二个文件的数据附加到第一个文件中，如下所示:

tig00000005 15310   16162   XP_012153921.1  NW_003797090.1  LOC105664333    PREDICTED: elastin-like tig00000005 maker   gene    15310   16162   .   +   .   ID=snap_masked-tig00000005-processed-gene-0.2;Name=snap_masked-tig00000005-processed-gene-0.2

一些不能工作的示例代码:

awk 'OFS="t"; FS="t"; NR==FNR{a[$1,$2,$3]=$0; next} (($1,$4,$5) in a){print $0,a[$0]}'  file 1 file 2
awk 'OFS="t"; FS="t"; NR==FNR{a[$1,$2,$3]=($1,$4,$5)} {print $0,a[$0]}' file 1 file 2

首先输出一个文件，其中每一行都来自文件1，后面跟着文件2(没有追加)，第二段代码抛出与=函数相关的错误。我试过了所有我能想到的排列。谢谢你提供的任何帮助

Answer 1

对OP的第一个awk脚本进行了一些小更改:

# old:
awk 'OFS="t"; FS="t"; NR==FNR{a[$1,$2,$3]=$0; next} (($1,$4,$5) in a){print $0,a[$0]}' file1 file2
# new - add BEGIN block, modify print statement:
awk 'BEGIN {FS=OFS="t"} NR==FNR{a[$1,$2,$3]=$0; next} (($1,$4,$5) in a){print a[$1,$4,$5],$0}' file1 file2

修改后的awk脚本生成:

tig00000005 15310   16162   XP_012153921.1  NW_003797090.1  LOC105664333    PREDICTED: elastin-like tig00000005 maker   gene    15310   16162   .   +   .   ID=snap_masked-tig00000005-processed-gene-0.2;Name=snap_masked-tig00000005-processed-gene-0.2
tig00000005 23339   23974   XP_012152584.1  NW_003797083.1  LOC100878991    PREDICTED: LOW QUALITY PROTEIN tig00000005 maker   gene    23339   23974   .   +   .   ID=snap_masked-tig00000005-processed-gene-0.4;Name=snap_masked-tig00000005-processed-gene-0.4
tig00000005 24600   25138   XP_012143166.1  NW_003797196.1  LOC100881279    PREDICTED: ankyrin-2 isoform X2 tig00000005 maker   gene    24600   25138   .   -   .   ID=snap_masked-tig00000005-processed-gene-0.10;Name=snap_masked-tig00000005-processed-gene-0.10

Answer 2

像这样?

awk 'NR==FNR{a[$1" "$2" "$3]=$0; next}; {if($1" "$4" "$5 in a){print a[$1" "$4" "$5],$0}}' file1 file2
tig00000005 15310   16162   XP_012153921.1  NW_003797090.1  LOC105664333    PREDICTED: elastin-like tig00000005 maker   gene    15310   16162   .   +   .   ID=snap_masked-tig00000005-processed-gene-0.2;Name=snap_masked-tig00000005-processed-gene-0.2
tig00000005 23339   23974   XP_012152584.1  NW_003797083.1  LOC100878991    PREDICTED: LOW QUALITY PROTEIN tig00000005 maker   gene    23339   23974   .   +   .   ID=snap_masked-tig00000005-processed-gene-0.4;Name=snap_masked-tig00000005-processed-gene-0.4
tig00000005 24600   25138   XP_012143166.1  NW_003797196.1  LOC100881279    PREDICTED: ankyrin-2 isoform X2 tig00000005 maker   gene    24600   25138   .   -   .   ID=snap_masked-tig00000005-processed-gene-0.10;Name=snap_masked-tig00000005-processed-gene-0.10

写入新文件只需执行awk 'NR==FNR{a[$1" "$2" "$3]=$0; next}; {if($1" "$4" "$5 in a){print a[$1" "$4" "$5],$0}}' file1 file2 > file3