[SOLVED] summing by a particular column

arn2025 · 05-17-2012, 10:20 AM

hi there i have a huge file

Quote:

SUBSCRIPTION QUOTA Balance Used
WIMAXPOSTMONTHLY3GB 3,145,728 48,293,568 -
WIMAXPOSTMONTHLY10GB 10,485,760 15,914,810 36,514,022
WIMAXPOSTMONTHLY10GB 10,485,760 22,993,475 25,536,410
WIMAXPOSTMONTHLY3GB 3,145,728 26,881,436 204,296
WIMAXPOSTMONTHLY10GB 10,000,000 2,892,547 27,798,041
WIMAXPOSTMONTHLY10GB 10,485,760 8,540,054 17,633,395
WIMAXPOSTMONTHLY3GB 3,145,728 4,639,558 12,137,728
WIMAXPOSTMONTHLY3GB 3,000,000 12,787,712 3,000,000
WIMAXPOSTMONTHLY10GB 10,000,000 14,515,783 5,689,017

i want to sum column 2 grouped by column one

so that my out put file has

Quote:

WIMAXPOSTMONTHLY10GB 60,748,736
WIMAXPOSTMONTHLY3GB 53894464

colucix · 05-17-2012, 10:47 AM

From your example I get different results:

Code:

$ TK_USE_CURRENT_LOCALE=1 awk 'NR > 1{gsub(/,/,"",$2); _[$1]+=$2}END{for (i in _) printf "%s %'\''d\n", i, _[i]}' file            
WIMAXPOSTMONTHLY3GB 12,437,184
WIMAXPOSTMONTHLY10GB 51,457,280

The problem for me is to read numbers with thousands separator (hence the gsub statement), whereas the TK_USE_CURRENT_LOCALE=1 and the (escaped) %'d format takes care of the output. Anyway, it should give you an idea, hopefully.

arn2025 · 05-18-2012, 06:57 AM

how do i also have the sum of the third and fouth column being displayaed too

colucix · 05-18-2012, 09:20 AM

If the suggested solution work on your system and if you understand the code, it should be easy to change it accordingly. Where are you stuck with the awk code?

grail · 05-18-2012, 10:37 AM

As the commas aren't doing you any favours, just gsub the whole line to remove commas

colucix · 05-18-2012, 12:10 PM

Quote:

Originally Posted by grail

As the commas aren't doing you any favours, just gsub the whole line to remove commas

Indeed the requirement is ambiguous, since the desired output shown in post #1 is both with and without thousands separators. Anyway, grail are you aware of a method to read numbers in UK notation with GNU awk? Maybe something involving the language settings?

grail · 05-18-2012, 12:51 PM

Quote:

Originally Posted by colucix

Anyway, grail are you aware of a method to read numbers in UK notation with GNU awk? Maybe something involving the language settings?

I did have a quick look but was not able to find any setting that would influence awk as it perceives it strictly as a string and if you force a number it truncates after the first comma.

colucix · 05-18-2012, 02:12 PM

Quote:

Originally Posted by grail

I did have a quick look but was not able to find any setting that would influence awk as it perceives it strictly as a string and if you force a number it truncates after the first comma.

Exactly what I've experienced. I will deepen my search and if I get some result I will post somewhere for the sake of curiosity. Thanks!