How to join two file with a key column in awk

Question

I have two tabulate file and I want to join them like this :

file A.out

Well    Sample Name Sample Color    
A1  B1000-1-KF  "RGB(132,193,241)"
A1  B1000-1-KF  "RGB(132,193,241)"      
A2  B500-3-KF   "RGB(168,255,222)"      
A2  B500-3-KF   "RGB(168,255,222)"

File B.out

Well    Cycle   Target Name Rn  ΔRn
A1  1   Ngene   433375.375  -2322.9575
A1  2   Ngene   435992.3438 -1616.2767
A1  3   Ngene   438522  -996.9085
A1  4   Ngene   441353  -76.1964
A2  1   Ngene   541226.1875 13234.6875
A2  2   Ngene   514756.8125 -13234.6875
A2  3   Ngene   517349.2188 -10642.2812
A2  4   Ngene   521414.875  -6576.625

And I want C.out :

Well    Cycle   Target Name Rn  ΔRn Sample Name
A1  1   Ngene   433375.375  -2322.9575  B1000-1-KF
A1  2   Ngene   435992.3438 -1616.2767  B1000-1-KF
A1  3   Ngene   438522  -996.9085   B1000-1-KF
A1  4   Ngene   441353  -76.1964    B1000-1-KF
A2  1   Ngene   541226.1875 13234.6875  B500-3-KF
A2  2   Ngene   514756.8125 -13234.6875 B500-3-KF
A2  3   Ngene   517349.2188 -10642.2812 B500-3-KF
A2  4   Ngene   521414.875  -6576.625   B500-3-KF

So the firt column in each file is the key and I want to put all the B.out and the second column of A.out.

So far I've done :

awk -F'\t' -vOFS="\t" 'NR==FNR{a[$1]=$1;next}{print $0,a[$2]} B.out A.out > C.out

But it doesn't works , any idea ?

Thanks

Welcome to the site. If you say "it doesn't work", could you explain how the result of your approach differs from your expectations? Which flavor and version of awk are you using? — AdminBee, Jun 09 '20 at 14:30
If the first column in each file is the key, is it a typo that you wrote {print $0,a[$2]} instead of {print $0,a[$1]}? — AdminBee, Jun 09 '20 at 14:34

score 3 · Accepted Answer · edited Jun 09 '20 at 14:47

3

try

awk -F'\t' -vOFS="\t" 'NR==FNR{a[$1]=$2;next}{print $0,a[$1]} ' A.out B.out

where

$1 is key and $2 is value in first file
in second file value is fetched from key $1

this gave me

Well    Cycle   Target Name Rn  ΔRn Sample Name Sample Color
A1  1   Ngene   433375.375  -2322.9575  B1000-1-KF
A1  2   Ngene   435992.3438 -1616.2767  B1000-1-KF
A1  3   Ngene   438522  -996.9085   B1000-1-KF
A1  4   Ngene   441353  -76.1964    B1000-1-KF
A2  1   Ngene   541226.1875 13234.6875  B500-3-KF
A2  2   Ngene   514756.8125 -13234.6875 B500-3-KF
A2  3   Ngene   517349.2188 -10642.2812 B500-3-KF
A2  4   Ngene   521414.875  -6576.625   B500-3-KF

edited Jun 09 '20 at 14:47

terdon

242,166

answered Jun 09 '20 at 14:36

Archemar

31,554

Thanks , I have a bit of a problem to fully understand this system of "key,value" in comparison to python – nstatam Jun 09 '20 at 14:51
to set : a[key]=value to read print a[key] – Archemar Jun 09 '20 at 14:54
Yes but why value is $2 and not $1 ? – nstatam Jun 09 '20 at 15:07
The assignment a[$1] = $2 substitutes values from A.out which assign like a["A1"] = "B1000-1-KF". The print a[$1] references matching values from B.out like a["A1"] which collects the corresponding contents value. – Paul_Pedant Jun 09 '20 at 15:41
$1 hold A1 and $2 hold B1000-1-KF in first file. In second file, you have A1 and want the value. – Archemar Jun 09 '20 at 15:41
1

If you change -vOFS to -v OFS then your solution will work in any awk, not just GNU awk. – Ed Morton Jun 09 '20 at 20:27

score 0 · Answer 2 · answered Jun 10 '20 at 08:07

command

for i in A1 A2; do o=`awk -v  i="$i" '$1 == i {if(!seen[$2]++){print $2}}' f1`; awk -v i="$i" -v o="$o"  'BEGIN {print "Well    Cycle   Target Name Rn  ΔRn  Sample Name" } $1 == i {print $0,o}' f2; done

output

Well    Cycle   Target Name Rn  ΔRn  Sample Name
A1  1   Ngene   433375.375  -2322.9575 B1000-1-KF
A1  2   Ngene   435992.3438 -1616.2767 B1000-1-KF
A1  3   Ngene   438522  -996.9085 B1000-1-KF
A1  4   Ngene   441353  -76.1964 B1000-1-KF
Well    Cycle   Target Name Rn  ΔRn  Sample Name
A2  1   Ngene   541226.1875 13234.6875 B500-3-KF
A2  2   Ngene   514756.8125 -13234.6875 B500-3-KF
A2  3   Ngene   517349.2188 -10642.2812 B500-3-KF
A2  4   Ngene   521414.875  -6576.6 B500-3-KF

I would recommend using the modern $( ... ) style instead of the (deprecated) backticks for the command substitution ... — AdminBee, Jun 10 '20 at 08:18
@AdminBee Praveens' been told that many, many times before, idk why it's not sticking. — Ed Morton, Jun 10 '20 at 15:55

How to join two file with a key column in awk

2 Answers2