1

I need to write a shell script to merge csv files. Both the files have same columns with fixed number of columns designated as "Keys". If the Keys are same, then I want to merge them by adding the non key columns.

For example:

A.csv

k1,k2,k3,v1,v2      => This is the header row
A,B,C,4,5
A,B,D,3,4

B.csv

k1,k2,k3,v1,v2      => This is the header row
A,B,A,2,3
A,B,C,1,4
A,B,E,4,5

Merged.csv:

k1,k2,k3,v1,v2      => This is the header row
A,B,A,2,3
A,B,C,5,9           => A,B,C is present in both the csv, so value cols are added 
A,B,D,3,4
A,B,E,4,5
  • did you find the right answer for you? if yes, please mark it below, it is useful for stack exchange – aborruso Jun 9 at 7:13
1

Using Miller (http://johnkerl.org/miller/doc/index.html) with

mlr --csv stats1 -a sum -g k1,k2,k3 -f v1,v2 input_a.csv input_b.csv

you will have

k1,k2,k3,v1_sum,v2_sum
A,B,C,5,9
A,B,D,3,4
A,B,A,2,3
A,B,E,4,5
0

awk:

BEGIN{FS=OFS=","}

# print head row
NR==1;

# skip blank, sum column 4, 5 into a, b
FNR!=1 && NF!=0 {k=($1","$2","$3); a[k]+=$4; b[k]+=$5 }

# print result
END { for(k in a) print k, a[k], b[k] }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.