Add column with values matching Identifiers of another column

Question

I have two files:

File 1

hc          Value
cluster0    0.1  
cluster0    0.2  
cluster0    0.3  
cluster1    0.3  
cluster1    0.5  
cluster0    0.8  
cluster2    0.9  
cluster2    0.9  
cluster0    0.0  

File 2

hc       mean
cluster0 0.35
cluster1 0.4   
cluster2 0.9

And I would like to create a new table like:

File 3

hc          Value  mean
cluster0    0.1   0.35 
cluster0    0.2   0.35 
cluster0    0.3   0.35 
cluster1    0.3   0.40 
cluster1    0.5   0.40
cluster0    0.8   0.35 
cluster2    0.9   0.90
cluster2    0.9   0.90
cluster0    0.0   0.35

How can I do this in bash? Is there any command that can easily do this?

Answer 1

It can be done easily in Awk:

$ awk 'NR==FNR {a[$1] = FNR==1 ? $2 : sprintf("%.2f",$2); next} {print $0,a[$1]}' File2 File1
hc          Value mean
cluster0    0.1   0.35
cluster0    0.2   0.35
cluster0    0.3   0.35
cluster1    0.3   0.40
cluster1    0.5   0.40
cluster0    0.8   0.35
cluster2    0.9   0.90
cluster2    0.9   0.90
cluster0    0.0   0.35

The steps are:

• for lines in the first named file (NR==FNR), create an associative array (or hash) indexed by the first column values and containing the second column (mean) values. If you don't need the mean values to be displayed in floating-point format, you can simplify this step to NR==FNR {a[$1] = $2} which treats all the values (including the header) as strings.

• otherwise, print the line followed by the value in the array whose index matches the first column

Answer 2

Tried with below script and it worked fine too

command

for i in `cat file2| awk '{print $1}'`; do p=`grep "$i" file2| awk '{print $2}'`; awk -v i="$i" -v p="$p" '$1 == i {$3=p;print $0}' file1; done|sed '1i hc      Value  mean'

output

hc          Value  mean
cluster0    0.1   0.35 
cluster0    0.2   0.35 
cluster0    0.3   0.35 
cluster1    0.3   0.40 
cluster1    0.5   0.40
cluster0    0.8   0.35 
cluster2    0.9   0.90
cluster2    0.9   0.90
cluster0    0.0   0.35