基于三列联接两个文件 [英] join two files based on three columns

问题描述

我有以下两个文件，我需要根据三列的值将它们加入，如果不匹配，则在不匹配的列中打印NA.

I have following two files, I need to join them based on the value of three columns, and if not matched print NA in the unmatched column.

cat f1
AAA  0  node4  Activated  Unreachable  down
AAA  1  node3  Activated  Pingable     cool

cat f2 
AAA  0  node3  XYZ  Active

当前，我使用以下命令得到不正确的输出

Currently I am getting incorrect output using:

 awk 'NR==FNR{a[$1]=$1;b[$2]=$2;c[$3]=$3;next} $1 in a && $2 in b &&  $3 in c{print $0}' f1 f2
AAA 0 node3 XYZ Active

所需的输出:

AAA 0 node4 Activated Unreachable down NA
AAA 1 node3 Activated Pingable    cool Active

推荐答案

awk 方法:

awk approach:

awk 'NR==FNR{a[$1,$3]=$5; next}{$7="NA";if(($1,$3) in a){$7=a[$1,$3]} print}' f2 f1

输出:

AAA 0 node4 Activated Unreachable down NA
AAA 1 node3 Activated Pingable cool Active

---

a[$1,$3]=$5-使用第一个$1和第三个$3字段的组合作为数组键，将第五个字段$5的值存储在第二个文件f2中

---

a[$1,$3]=$5 - stores the value of the fifth field $5 in the second file f2 using combination of first $1 and third $3 fields as array key

$7="NA";-使用默认值"NA"启动另一个第七字段$7

$7="NA"; - initiates an additional seventh field $7 with default value "NA"

这篇关于基于三列联接两个文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！