Visit Our UNIX and Linux User Community


Awk: Summing values with group criteria


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk: Summing values with group criteria
# 1  
Old 11-05-2009
Awk: Summing values with group criteria

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
# 2  
Old 11-05-2009
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 SmilieSmilieSmilieSmilieSmilie
SmilieSmilieSmilieSmilie
# 3  
Old 11-05-2009
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

# 4  
Old 11-05-2009
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 ' ' ';'


Last edited by Scrutinizer; 11-05-2009 at 03:06 PM..
# 5  
Old 11-06-2009
Work but...

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
# 6  
Old 11-06-2009
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 ' ' ';'

# 7  
Old 11-06-2009
Debian

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


Previous Thread | Next Thread
Test Your Knowledge in Computers #632
Difficulty: Easy
Apple development of what would become the iPhone began in 2004.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Replace Stub Values In One Group Of Files With Actual Values From Another Group Of Files

I have two directories of files (new-config-files and old-config-files): new-config-files/this-db/config.inc.php new-config-files/that-db/config.inc.php new-config-files/old-db/config.inc.php new-config-files/new-db/config.inc.php new-config-files/random-database/config.inc.php etc. ... (4 Replies)
Discussion started by: spacegoose
4 Replies

2. Shell Programming and Scripting

Only print specific xml values that meet two criteria in python

I have a large XML file that I want to parse, and only print one specific value if two values are met. This is the code so far: #!/usr/local/bin/python import xml.etree.ElementTree as ET tree = ET.parse('onedb-dhcp.xml') root = tree.getroot() # This successfully gets all... (1 Reply)
Discussion started by: brianjb
1 Replies

3. Shell Programming and Scripting

Summing per group in a loop

I want to sum and average all other columns by first column GR1 1 4 7 GR1 2 5 8 GR1 3 6 9 GR2 11 14 17 GR2 13 16 19 GR3 1 3 5 GR3 2 4 6 For a limited number of columns I can do... (2 Replies)
Discussion started by: jianp83
2 Replies

4. Shell Programming and Scripting

Summing up values of rows of numbers

data file contains failed=24 error=23 error=163 failed=36 error=903 i need to get a total count of each value above. i'm looking for the most efficient method to do this as the datafile i provided is just a sample. the actual data can be several hundred thousands of lines. so from... (3 Replies)
Discussion started by: SkySmart
3 Replies

5. Shell Programming and Scripting

Summing columns over group of lines

I have an input file that looks like: ID1 V1 ID2 V2 P1 P2 P3 P4 ..... n no. of columns 1 1 1 1 1.0000 1.0000 1.0000 1.0000 1 1 1 2 0.9999 0.8888 0.7777 0.6666 1 2 1 1 0.8888 0.7777 0.6666 0.5555 1 2 1 2 0.7777 0.6666 0.5555 0.4444 2 1 1 1 0.6666 0.5555 0.4444 0.3333 2 1 1 2 0.5555 0.4444... (4 Replies)
Discussion started by: sdp
4 Replies

6. Shell Programming and Scripting

reading a script and summing some values

I need help reading and summing some values in a file that looks like the following. This is an Oracle trace file. Oracle has as utility to do this,but it doesn't work properly unless my sql statement is done. I want to read the file and sum up some values to let me know how the query/job is... (0 Replies)
Discussion started by: guessingo
0 Replies

7. Shell Programming and Scripting

Cat Values from Several files if it meets criteria for column values

I have results from some statistical analyses. The format of the results are as given below: I want to select lines that have a p-value (last column) less than 0.05 from all the results files (*.results) and cat to a new results file. It would be very nice if a new column is added that tells... (2 Replies)
Discussion started by: genehunter
2 Replies

8. Shell Programming and Scripting

Summing values in columns

Basically I have to process a text file which has been sorted this way: John 12 John 13 John 10 John 900 Peter 20 Peter 30 Peter 32 The first column is a name, and the second an arbitrary value, both delimited by a space. How can I sum them up such that it would become: John 935... (2 Replies)
Discussion started by: Dwee
2 Replies

9. Shell Programming and Scripting

summing up iostat values in AIX

Friends, I have to run iostat -d on my AIX machine and print the sum of the output in tps column per iteration. can any one pls guide me how to do this using awk. here is the sample output iostat -d 2 2 | awk '!/System/ && !/Disks/ && !/cd/ && !/^$/ {print $4}' 2.0 3.0 1.0 3.0... (1 Reply)
Discussion started by: achak01
1 Replies

10. Shell Programming and Scripting

summing values of a column

I have a file which contains data as below: ----------------------------------------------------------------------------------------------- GSPWeb Statistics for the period of last 20 days... (3 Replies)
Discussion started by: mohsin.quazi
3 Replies

Featured Tech Videos