CODEWITHSUNDEEP
bashawksed
Daniel S
Daniel S

Reputation: 609

Sum and replace in awk based on duplicate column

I have a file that contains the following:

z,cat,7,9,bar
x,dog,9,9,bar
y,dog,3,4,foo
s,cat,3,4,bar
t,boat,21,1,foo
u,boat,19,3,bar

and i need to reach this result:

x,cat,10,13,x
x,dog,12,13,x
x,boat,40,4,x

i was trying something similar to 

awk '{a[$NF]+=$1}END{for(x in a) printf "%s  %s\n",x,a[x]}'

but what happens with this approach is that when you put more columns, it breaks the hole thing, because rows 1,2 and 5 can contain alpha numeric characters

Upvotes: 0

Views: 113

Answers (2)

choroba
choroba

Reputation: 241928

Perl solution:

perl -aF, -ne '$h{ $F[1] }[$_] += $F[ $_ + 2 ] for 0,1
               }{
               $" = ",";
               print "x,$k,@{ $h{$k} },x\n" while ($k, $v) = each %h'

Upvotes: 1

Jotne
Jotne

Reputation: 41454

This should do;

awk -F, '{arr1[$2]+=$3;arr2[$2]+=$4} END {for (i in arr1) print "x",i,arr1[i],arr2[i],"x"}' OFS=, file
x,cat,10,13,x
x,boat,40,4,x
x,dog,12,13,x

Upvotes: 1

Related Questions