CODEWITHSUNDEEP
awk
monk
monk

Reputation: 2115

awk,merge two data sets based on column value

I need to combine two data sets stored in variables. This merge needs to be conditional based on the value of 1st column of "$x" and third column of "$y"

 -->echo "$x"
12 hey
23 hello
34 hi


 -->echo "$y"
aa bb 12
bb cc 55
ff gg 34
ss ww 23

By following command, I managed to store the value of first column of $x in a[] and check for third column of $y but not getting what I am expecting, can someone please help here. 

awk 'NR==FNR{a[$1]=$1;next} $3 in a{print $0,a[$1]}' <(echo "$x") <(echo "$y")
aa bb 12
ff gg 34
ss ww 23

Expected result:

aa bb 12 hey
ff gg 34 hi
ss ww 23 hello

Upvotes: 1

Views: 98

Answers (2)

eraenderer
eraenderer

Reputation: 107

join -1 1 -2 3 <(sort -k 1b,1 a.txt) <(sort -k 3b,3 b.txt) |awk '{print $3, $4, $1, $2 }'

Might be a solution for your input in two textfiles a.txt and b.txt using join on your two number columns.

It does not keep the order though. You might have to sort again if it is important.

Upvotes: -1

oliv
oliv

Reputation: 13259

Your answer is almost right:

awk 'NR==FNR{a[$1]=$2;next} ($3 in a){print $0,a[$3]}' <(echo "$x") <(echo "$y")

Note the a[$1]=$2 and the print $0,a[$3].

Upvotes: 2

Related Questions