Merge muliple files having N common columns by awk and want to replace column value as 0 if any file does not have common key

Question

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?

Answer 1

With any awk and also to keep the orders of the records in output:

awk 'BEGIN{ SUBSEP=OFS=FS="," }
 FNR==1 && !reProccss{ fileNr++ }
 !reProccss{ keys[$1, $2, $3, fileNr]=$4; next }
  reProccss{ key=($1 OFS $2 OFS $3); recNr++
             for(i=1; i<=fileNr; i++)
                 if(seen[key]++<fileNr){
                     join[key]= join[key] OFS ((key, i) in keys ?keys[key, i]:"0")
                     data[recNr]= key join[key]
                 }
           }
END{ for(rec=1; rec<=recNr; rec++)
         if(data[rec]!="")
             print data[rec]
}' a[1-3].txt reProccss=1 a[1-3].txt

---

Or using with join +shell to convert multiple columns as the key into a single key that then we use the join command with answer similar to Merge multiple files by first column (since join works only with single column as the key) to produce our desired output.

So we convert multiple key columns into one by seprating them with a specific character say - (A character that should not exist in your input files) on the first two files and output to temporary file joined.tmp:

join -t, -a1 -a2 -e 0 -o auto \
    <(<a1.txt sort |awk -F, -v OFS='-' '{ print $1, $2, $3 FS $4 }') \
    <(<a2.txt sort |awk -F, -v OFS='-' '{ print $1, $2, $3 FS $4 }') > joined.tmp

Then we use a shell-loop to process rest of the files against joined.tmp file (which it's updating every run to joining with second next file); we are also skiping those first two files we already processed witin loop.

for file in ./a*.txt; do
    [ "$file" = "./a1.txt" -o "$file" = "./a2.txt" ] && continue
    join -t, -a1 -a2 -e 0 -o auto \
        joined.tmp <(sort "$file" |awk -F, -v OFS='-' '{ print $1, $2, $3 FS $4 }') >joined.tmp.1
    mv joined.tmp.1 joined.tmp
done

At the end change back the added - to thier orignial character ,.

sed 's/-/,/g' joined.tmp > joined-final.csv

order of records in output will be changed since join requires input files to be sorted:

$ cat joined-final.csv
111,222,444,5.5,7.8,0
121,321,555,1.2,0,0
311,555,222,0,1.1,8.8
333,321,555,0,4.5,9.1
444,666,777,0,0,2.5 

Answer 2

Using GNU awk for arrays of arrays and ARGIND:

$ cat tst.awk
BEGIN { FS=OFS=SUBSEP="," }
{ vals[$1,$2,$3][ARGIND] = $NF }
END {
    for ( key in vals ) {
        printf "%s", key
        for ( i=1; i<=ARGIND; i++ ) {
            printf "%s%g", OFS, vals[key][i]
        }
        print ""
    }
}

$ awk -f tst.awk *.txt
111,222,444,5.5,7.8,0
311,555,222,0,1.1,8.8
333,321,555,0,4.5,9.1
444,666,777,0,0,2.5
121,321,555,1.2,0,0

If the order of output lines matters, it's an easy tweak.