Merge columns of different files

Shell Programming and Scripting
1

Hi,

I have tab limited file 1

and tab limited file 2

The output should contain common first column vales and corresponding 2nd column values; AND also unique first column value with corresponding 2nd column value of the file that contains it and 0 for the second file.

the output should be:

thanks in advance.:slight_smile:

2 
awk '{if($1 in a){sub("0" FS,"",a[$1]);a[$1]=a[$1] FS $2}else{a[$1]="0" FS $2}}END{for(i in a)print i,a}' file1 file2
1 Like
3

@yinyuemi

thanks a lot. I actually have 4 files to merge...for simplicity sake I used 2 files here...when I applied this code to 4 files, I dont see all columns...How can I modify this code? thanks a lot again. :slight_smile:

4

if you have multiple files,please try this:

awk '{a[ARGIND" "$1]=$2;b[$1];t=ARGIND}END{for(i in b){printf i ;for(j=1;j<=t;j++)printf FS (length(a[j" "i])?a[j" "i]:"0");print ""}}' file1 file2 file1 file2
x 0 678 0 678
a 455 56 455 56
b 443 5 443 5
c 6655 0 6655 0
d 554 0 554 0
e 6 0 6 0
1 Like
5 
cat f1.txt

a 455
b 443
c 6655
d 554
e 6

$ cat f2.txt

a 56
b 5
x 678

awk '$0 !~ /#/{arr[$1]=arr[$1] " " $2}END{for(i in arr)print i,arr}' f1.txt f2.txt

x  678
a  455 56
b  443 5
c  6655
d  554
e  6
1 Like
6

thank you very much. its working perfect. have a great weekend:b:

7

Hi, this code is working for the above mentioned example data, but its not working for the following tables. May I know why and how should I rectify the problem? thanks.

file 1

file 2

desired output is

But after using the code, I am getting only the first column, not the 2nd and third.

I am not sure what the problem is?

thanks in advance:b:

8

I just test the code using your sample data, it works fine, my guess is the format of files make you fail, so please run

 dos2unix file1 file2

first, then re-run the awk code, see if it work.

9

I tried that, but still not working. Your first code (for just 2 files)

awk '{if($1 in a){sub("0" FS,"",a[$1]);a[$1]=a[$1] FS $2}else{a[$1]="0" FS2}}END{for(i in a)print i,a}'

works just fine if I use 2 files. But the 2nd code doesn't work.

10

Tha't weird, how about this?

awk 'FNR==1{t++}{a[t" "$1]=$2;b[$1]}END{for(i in b){printf i ;for(j=1;j<=t;j++)printf FS (length(a[j" "i])?a[j" "i]:"0");print ""}}'

or you can use "cat -A" to see if there're some weird word in your files.

11

Now this code is working just fine.:slight_smile: Thanks a lot. I dont understand why the first one is not working though.