I want to merge mulpiple files based on common column and want to add 0 when any file does not have that common column. For example see below:
a1.txt
111,222,444,5.5
121,321,555,1.2
a2.txt
111,222,444,7.8
333,321,555,4.5
311,555,222,1.1
a3.txt
333,321,555,9.1
311,555,222,8.8
444,666,777,2.5
match should be with the combination of 1st 3 columns.
output should be as below:
111,222,444,5.5,7.8,0
121,321,555,1.2,0,0
333,321,555,0,4.5,9.1
311,555,222,0,1.1,8,8
444,666,777,0,0,2.5
The value of 4th column in 3 input files is different which I want to put in sequence. Like a1.txt value should be 4th column in output file. a2.txt value should be in 5th col in output file and value of a3.txt should be in 6th column of output file. I tried below but did not give me expected result.
awk '{ a[$1 FS $2 FS $3 FS] = a[$1 FS $2 FS $3 FS] ( a[$1 FS $2 FS $3 FS] == "" ? "" : FS) $4 } END{ for (i in a){print i,a[i,0],a[i]} }' FS="," a1.txt a2.txt a3.txt
This way I want to do the same for 4 or 5 or 6 input files. Can someone help me on this?