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

234,489
66
447
667

answered Jun 09 '20 at 14:36

Archemar

31,183
18
69
104

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](https://unix.stackexchange.com/questions/126927/have-backticks-i-e-cmd-in-sh-shells-been-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