如何使用awk比较两个文件中的列 [英] how to compare the columns in two files using awk

问题描述

我想使用awk命令比较两个文件，文件1和2包含以下信息.文件1是核苷酸位置，如文件2的第2列所示.

I want to compare two files using awk command, with the File 1 and 2 containing the following information. The File 1 is the nucleotide positions as can be seen in the column 2 of File 2.

现在，我需要awk命令将文件1中的列(仅存在一个块)与文件2的列2进行比较，如果找到匹配项，则将文件2的整行打印到文件3

Now I need an awk command to compare the column (only one cloumn is present) in File 1 to the column 2 of File 2 and if a match is found, print the whole line in File 2 to File 3

文件1

7113528
8680847

文件2

chromosome01    6765006 .   C   T   22.8    .   DP=3;RPB=-8.745357e-01;AF1=1;AC1=2;DP4=1,1,1,0;MQ=35;FQ=-27;PV4=1,1,1,1;EFF=DOWNSTREAM(MODIFIER||1505||492|OS01G0223600|protein_coding|CODING|OS01T0223600-01||1|WARNING_TRANSCRIPT_NO_START_CODON),DOWNSTREAM(MODIFIER||262||174|OS01G0223500|protein_coding|CODING|OS01T0223500-00||1),INTERGENIC(MODIFIER||||||||||1)    PL  51,0,0
chromosome01    6765043 .   C   T   22.8    .   DP=3;RPB=-8.745357e-01;AF1=1;AC1=2;DP4=1,1,1,0;MQ=35;FQ=-27;PV4=1,1,1,1;EFF=DOWNSTREAM(MODIFIER||1468||492|OS01G0223600|protein_coding|CODING|OS01T0223600-01||1|WARNING_TRANSCRIPT_NO_START_CODON),DOWNSTREAM(MODIFIER||225||174|OS01G0223500|protein_coding|CODING|OS01T0223500-00||1),INTERGENIC(MODIFIER||||||||||1)    PL  51,0,0
chromosome01    7113528 .   GACAC   GAC 7.98    .   INDEL;IS=1,0.333333;DP=3;VDB=6.186179e-02;AF1=1;AC1=2;DP4=1,1,0,1;MQ=35;FQ=-34.5;PV4=1,1,1,1;EFF=DOWNSTREAM(MODIFIER||2254||107|OS01G0228901|protein_coding|CODING|OS01T0228901-01||1),DOWNSTREAM(MODIFIER||3930|||NCRNA_20319|ncRNA|NON_CODING|NCRNA_20319||1),INTERGENIC(MODIFIER||||||||||1),UPSTREAM(MODIFIER||3930|||NCRNA_21253|ncRNA|NON_CODING|NCRNA_21253||1),UPSTREAM(MODIFIER||4436||687|OS01G0228800|protein_coding|CODING|OS01T0228800-01||1)  PL  43,0,0
chromosome01    7113583 .   C   T   22.8    .   DP=3;RPB=-8.745357e-01;AF1=1;AC1=2;DP4=1,1,0,1;MQ=35;FQ=-27;PV4=1,1,1,1;EFF=DOWNSTREAM(MODIFIER||2202||107|OS01G0228901|protein_coding|CODING|OS01T0228901-01||1),DOWNSTREAM(MODIFIER||3982|||NCRNA_20319|ncRNA|NON_CODING|NCRNA_20319||1),INTERGENIC(MODIFIER||||||||||1),UPSTREAM(MODIFIER||3982|||NCRNA_21253|ncRNA|NON_CODING|NCRNA_21253||1),UPSTREAM(MODIFIER||4488||687|OS01G0228800|protein_coding|CODING|OS01T0228800-01||1)   PL  51,0,0
chromosome01    7427540 .   C   T   22.8    .   DP=3;RPB=8.745357e-01;AF1=1;AC1=2;DP4=0,2,0,1;MQ=35;FQ=-27;PV4=1,1,1,1;EFF=DOWNSTREAM(MODIFIER||1091|||NCRNA_19787|ncRNA|NON_CODING|NCRNA_19787||1),DOWNSTREAM(MODIFIER||1113|||NCRNA_7056|ncRNA|NON_CODING|NCRNA_7056||1),DOWNSTREAM(MODIFIER||2841||256|OS01G0234433|protein_coding|CODING|OS01T0234433-00||1|WARNING_TRANSCRIPT_NO_START_CODON),DOWNSTREAM(MODIFIER||4859|||NCRNA_25306|ncRNA|NON_CODING|NCRNA_25306||1),NON_SYNONYMOUS_CODING(MODERATE|MISSENSE|Cca/Aca|P35T|421|OS01G0234200|protein_coding|CODING|OS01T0234200-00|1|1|WARNING_REF_DOES_NOT_MATCH_GENOME),UPSTREAM(MODIFIER||1091|||NCRNA_19719|ncRNA|NON_CODING|NCRNA_19719||1),UPSTREAM(MODIFIER||1113|||NCRNA_7253|ncRNA|NON_CODING|NCRNA_7253||1),UPSTREAM(MODIFIER||1844||386|OS01G0234300|protein_coding|CODING|OS01T0234300-00||1),UPSTREAM(MODIFIER||2862|||NCRNA_9648|ncRNA|NON_CODING|NCRNA_9648||1),UPSTREAM(MODIFIER||3028||255|OS01G0234499|protein_coding|CODING|OS01T0234499-00||1),UPSTREAM(MODIFIER||4863|||NCRNA_27966|ncRNA|NON_CODING|NCRNA_27966||1),UPSTREAM(MODIFIER||4872|||NCRNA_33984|ncRNA|NON_CODING|NCRNA_33984||1)   PL  51,0,0
chromosome01    7427583 .   T   C   26.1    .   DP=3;RPB=-9.668049e-01;AF1=1;AC1=2;DP4=0,1,0,1;MQ=42;FQ=-28;PV4=1,1,1,1;EFF=DOWNSTREAM(MODIFIER||1134|||NCRNA_19787|ncRNA|NON_CODING|NCRNA_19787||1),DOWNSTREAM(MODIFIER||1156|||NCRNA_7056|ncRNA|NON_CODING|NCRNA_7056||1),DOWNSTREAM(MODIFIER||2798||256|OS01G0234433|protein_coding|CODING|OS01T0234433-00||1|WARNING_TRANSCRIPT_NO_START_CODON),DOWNSTREAM(MODIFIER||4902|||NCRNA_25306|ncRNA|NON_CODING|NCRNA_25306||1),SYNONYMOUS_CODING(LOW|SILENT|ggC/ggG|G20|421|OS01G0234200|protein_coding|CODING|OS01T0234200-00|1|1|WARNING_REF_DOES_NOT_MATCH_GENOME),UPSTREAM(MODIFIER||1134|||NCRNA_19719|ncRNA|NON_CODING|NCRNA_19719||1),UPSTREAM(MODIFIER||1156|||NCRNA_7253|ncRNA|NON_CODING|NCRNA_7253||1),UPSTREAM(MODIFIER||1801||386|OS01G0234300|protein_coding|CODING|OS01T0234300-00||1),UPSTREAM(MODIFIER||2905|||NCRNA_9648|ncRNA|NON_CODING|NCRNA_9648||1),UPSTREAM(MODIFIER||2985||255|OS01G0234499|protein_coding|CODING|OS01T0234499-00||1),UPSTREAM(MODIFIER||4906|||NCRNA_27966|ncRNA|NON_CODING|NCRNA_27966||1),UPSTREAM(MODIFIER||4915|||NCRNA_33984|ncRNA|NON_CODING|NCRNA_33984||1)  PL  55,1,0

推荐答案

您可以使用以下awk:

You can use this awk:

awk 'FNR==NR {a[$1]; next} $2 in a' f1 f2

要重定向到另一个文件:

To redirect to another file:

awk 'FNR==NR {a[$1]; next} $2 in a' f1 f2 > f3

说明

• FNR==NR {a[$1]; next}遍历第一个将值存储在数组a[]中的文件.
• $2 in a如果数组a[]中存在第二个文件的第二列，则为true，并打印整行.

Explanation

• FNR==NR {a[$1]; next} loop through the first file storing the values in the array a[].
• $2 in a if 2nd column of 2nd file is present in the array a[], then this is true and the full line is printed.

$ awk 'FNR==NR {a[$1]; next} $2 in a' f1 f2
chromosome01    7113528 .   GACAC   GAC 7.98    .   INDEL;IS=1,0.333333;DP=3;VDB=6.186179e-02;AF1=1;AC1=2;DP4=1,1,0,1;MQ=35;FQ=-34.5;PV4=1,1,1,1;EFF=DOWNSTREAM(MODIFIER||2254||107|OS01G0228901|protein_coding|CODING|OS01T0228901-01||1),DOWNSTREAM(MODIFIER||3930|||NCRNA_20319|ncRNA|NON_CODING|NCRNA_20319||1),INTERGENIC(MODIFIER||||||||||1),UPSTREAM(MODIFIER||3930|||NCRNA_21253|ncRNA|NON_CODING|NCRNA_21253||1),UPSTREAM(MODIFIER||4436||687|OS01G0228800|protein_coding|CODING|OS01T0228800-01||1)  PL  43,0,0

这篇关于如何使用awk比较两个文件中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！