how can i group by same columns by another columns in Bash

Shell Programming and Scripting
1

how can i group by same columns by another columns in Bash

Eq. this is a csv file

Co1 Co2 Co3    Co4
A     A     1,000  1,000
A     B     2,000  1,250
A     A     2,000  3,002
A     C     2,000  3,005

how can i get the result of like this

Co1 Co2 Co3    Co4
A     A     3,000  4,002
A     B     2,000  1,250
A     C     2,000  3,005
2 
 echo "Co1 Co2 Co3    Co4
A     A     1,000  1,000
A     B     2,000  1,250
A     A     2,000  3,002
A     C     2,000  3,005" |awk 'NR==1{print;next}
{a[$1 FS $2]+=gensub(",","",1,$3);b[$1 FS $2]+=gensub(",","",1,$4)}
END{for(i in a) print i,gensub(/(.)(.*)/,"\\1,\\2",1,a),gensub(/(.)(.*)/,"\\1,\\2",1,b)}'
Co1 Co2 Co3    Co4
A A 3,000 4,002
A B 2,000 1,250
A C 2,000 3,005
3

Remove the ',' from your file and apply this command.

awk 'NR==1{print;next} {arr1[$2]=arr1[$2] + $3;arr2[$2]= arr2[$2] + $4;} END{for (x in arr1) print "A  "x"  " arr1[x]"  "arr2[x]}'
4 
# cat file
Co1 Co2 Co3    Co4
A     A     1,000  1,000
A     B     2,000  1,250
A     A     2,000  3,002
A     C     2,000  3,005
# awk '{$1=$1;gsub(/,/,".")}NR==1{print;next}{a1[$1 OFS $2]+=$3;a2[$1 OFS $2]+=$4}END{for(_ in a1){printf "%s\t%.3f\t%.3f\n",_,a1[_],a2[_]}}' OFS='\t' file
Co1     Co2     Co3     Co4
A       A       3.000   4.002
A       B       2.000   1.250
A       C       2.000   3.005
5

Thank u very much!
these is an other problem for me,
I don't know the method of gensub' means

How can I printout the result with format "###,###,###"

6

The gensub function works only with gawk.

1 Like