0

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?

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

  • could you please explain the steps? It works perfectly. – fusion.slope May 28 at 22:37
  • 1
    @fusion.slope I've added a brief explanation – steeldriver May 28 at 22:47
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.