2

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?

2 Answers 2

2

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 
2
  • Thanks a lot for the response! Can this be done by awk, I tried this but not giving me the same same result. Thanks! Commented Aug 2, 2022 at 5:48
  • Thanks Again! But this command is giving me error "for reading (File name too long)". Commented Aug 2, 2022 at 9:40
0

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.

2
  • Hi Ed, I notied that this command is doing merging job file but where the value is long it is chagning the format. For one key the value was 1280946.909 but it got changed to 1.28095e+06. Commented Aug 4, 2022 at 10:09
  • I changed line like this " printf "%s%.3f", OFS, vals[key][i]" This worked. Commented Aug 4, 2022 at 10:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .