how to merge 2 files using awk.... 1

[jehst]

Hi
I know that this subject has been dealt a couple of times. But i still couldn t find any satisfying answer to my problem.
As a poor biologist, not used to all those awk tools i hope some of you will be able to save me...
so here is the problem.
i have plenty of experience data files i have to merge together to make one big file. But let just consider two of them ,i ll manage to adapt it for the rest of them
input files would be

file1

experience 1 2 3 4 5 6 7 8
species1 32 41 51 47 52 25 10 63
species2 14 25 51 41 25 63 85 95
species3 24 15 85 74 20 36 95 41
species5 21 45 12 01 25 35 21 74
..
.

file2
experience 9 10 11 12 13 14
species1 21 41 58 63 21 47
species2 54 12 02 13 36 24
species4 41 21 58 96 87 74

and i would like to have an output file which look like that:

file3

experience 1 2 3 4 5 6 7 8 9 10 11 12 13 14
species1 32 41 51 47 52 25 10 63 21 41 58 63 21 47
species2 14 25 51 41 25 63 85 95 54 12 02 13 36 24
species3 24 15 85 74 20 36 95 41 na na na na na na
species4 na na na na na na na na 41 21 58 96 87 74
.
.
.
....

so if a helpful person feel as saving me , i would be really grateful...

best

jehst

 

[PHV]

You may consider the join command:
man join

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jehst]

hi
thanks for answering so quikly
but anyway i might be too dumb but it seems that join doesnt give me the result i am expecting...
how would you use it in my case?

thanks

 

[PHV]

Sorry I thought that -e na -a 1 -a 2 was OK for the join command, but unfortunately not.
So, one awk way:
awk '
NR==1{for(i=1;i<NF;++i)n1=" na"n1}
NR==FNR{a1[$1]=substr($0,length($1)+1);t[$1];next}
FNR==1{for(i=1;i<NF;++i)n2=" na"n2}
{a2[$1]=substr($0,length($1)+1);t[$1];next}
END{for(i in t)print i (i in a1 ? a1 : n1) (i in a2 ? a2 : n2) | "sort"}
' file1 file2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jehst]

thank you so much for that!! it does what i want
Have a great day

best
jehst

 

[comtec17]

Great work PHV, have a star!

 

[Annihilannic]

join seems to do the trick too with the right output format options.

join -a 1 -a 2 -e na file1 file2 -o 0,1.2,1.3,1.4,1.5,1.6,1.7,1.8,1.9,2.2,2.3,2.4,2.5,2.6,2.7

Annihilannic.