Awk: Summing values with group criteria

gianluca2 · #1 (**permalink**) 2 Weeks Ago

Hi Guys,

I have a text file with ";" like separator

F1;F2;F3;F4;F5
444;100041;IT;GLOB;1800000000
444;100041;TM;GLOB;1000000000
444;10300264;IT;GLOB;2000000000
444;10300264;IT;GLOB;2500000000

I have to sum the cullums F5 for same F2 and F3 collums
The result must be:

444;100041;IT;GLOB;1800000000
444;100041;TM;GLOB;1000000000
444;10300264;IT;GLOB;4500000000

Thanks for your support.
Regards
Gianluca

ahmad.diab · #2 (**permalink**) 2 Weeks Ago

code:-

Code:

nawk -F";"  -v OFS=";" '
NF{a[$2";"$3]+=$5 ; b[$2";"$3]=$1";"$4}
END{ for (i in a) {print i,b[i],a[i] } }
'  input_file | sort | nawk -F";" -v OFS=";" '{print $3,$1,$2,$4,$5}'

BR

---------- Post updated at 11:45 AM ---------- Previous update was at 11:33 AM ----------

or better one and more elegant:-

Code:

nawk  -F";"  -v OFS=";" '
NF{a[$2";"$3]+=$5 ; b[$2";"$3]=$1";"$2";"$3";"$4}
END{ for (i in a) {print b[i],a[i] } }
'  input_file | sort > out_file

BR

danmero · #3 (**permalink**) 2 Weeks Ago

Code:

awk 'BEGIN{FS=OFS=";"}NR>1{a[$2FS$3]+=$NF;$NF="";b[$2";"$3]=$0}END{for(i in a)print b[i]a[i]}' file

Scrutinizer · #4 (**permalink**) 2 Weeks Ago

Code:

awk -F';' '{A[$2$3]=$1FS$2FS$3FS$4;B[$2$3]+=$5} END{for (i in A) printf "%s%.0f\n",A[i]FS,B[i]}' infile |sort -nt';'

Code:

444;100041;IT;GLOB;1800000000
444;100041;TM;GLOB;1000000000
444;10300264;IT;GLOB;4500000000

-or-

Code:

awk -F';' '{B[$2$3]+=$5;$5="";A[$2$3]=$0} END{for (i in A) printf "%s%.0f\n",A[i],B[i]}' infile |sort -n|tr ' ' ';'

gianluca2 · #5 (**permalink**) 2 Weeks Ago

Hi guys,

Many thanks for your answers & solution.
I have a little issue. If the source trace change as is

444;100041;IT;GLOB;1800000000;;;;;I
444;100041;TM;GLOB;1000000000;;;;;I
444;10300264;IT;GLOB;4500000000;;;;;I

the awk drop the chars after the number ";;;;;I"

What i have to change?
Thanks and regards
Gianluca

Scrutinizer · #6 (**permalink**) 2 Weeks Ago

Try this:

Code:

awk 'BEGIN{OFS=FS=";"}{B[$2$3]+=$5;$5="@";A[$2$3]=$0} END{ for (i in A) {sub(/@/,sprintf("%.0f",B[i]),A[i]); print A[i]}}' infile | sort -nt';'

-or-

Code:

awk -F';' '{B[$2$3]+=$5;$5="@";A[$2$3]=$0} END{ for (i in A) {sub(/@/,sprintf("%.0f",B[i]),A[i]); print A[i]}}' infile |sort -n|tr ' ' ';'

radoulov · #7 (**permalink**) 2 Weeks Ago

Use gawk, nawk or /usr/xpg4/bin/awk on Solaris.

Code:

awk -F\; 'END { for (_ in _0) {
  l = split(_0[_], t); t[5] = _5[_]
  for (i=1; i<=l; i++) 
    printf "%s", t[i] (i == l ? RS : FS)  
    }      
  }  
{ _5[$2,$3] += $5; _0[$2,$3] = $0 }   
' infile

---------- Post updated at 01:03 PM ---------- Previous update was at 12:54 PM ----------

If the file is already ordered:

Code:

awk -F\; 'END { print r }
!_[$2,$3]++ && NR > 1 { 
  print r; x = 0 
  }
{ $5 = x += $5; r = $0 }
' OFS=\; infile

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
summing up iostat values in AIX	achak01	Shell Programming and Scripting	1	3 Weeks Ago 11:20 AM
summing values of a column	mohsin.quazi	Shell Programming and Scripting	3	08-24-2009 10:45 PM
Combine multiple string into 1 string group by certain criteria	whchee	Shell Programming and Scripting	4	05-11-2009 03:28 AM
Summing amounts	mohan705	Shell Programming and Scripting	4	12-24-2008 07:13 AM
Summing on column	sbasetty	Shell Programming and Scripting	2	02-07-2007 08:15 PM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode	Rate This Thread: