LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 05-17-2012, 11:20 AM   #1
arn2025
LQ Newbie
 
Registered: Feb 2012
Posts: 26
Blog Entries: 1

Rep: Reputation: Disabled
summing by a particular column


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
 
Old 05-17-2012, 11:47 AM   #2
colucix
LQ Guru
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.5 OpenSuSE 12.3
Posts: 10,509

Rep: Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976
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.
 
Old 05-18-2012, 07:57 AM   #3
arn2025
LQ Newbie
 
Registered: Feb 2012
Posts: 26
Blog Entries: 1

Original Poster
Rep: Reputation: Disabled
how do i also have the sum of the third and fouth column being displayaed too
 
Old 05-18-2012, 10:20 AM   #4
colucix
LQ Guru
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.5 OpenSuSE 12.3
Posts: 10,509

Rep: Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976
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?
 
Old 05-18-2012, 11:37 AM   #5
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,252

Rep: Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685
As the commas aren't doing you any favours, just gsub the whole line to remove commas
 
Old 05-18-2012, 01:10 PM   #6
colucix
LQ Guru
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.5 OpenSuSE 12.3
Posts: 10,509

Rep: Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976
Quote:
Originally Posted by grail View Post
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?
 
Old 05-18-2012, 01:51 PM   #7
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,252

Rep: Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685Reputation: 2685
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.
 
Old 05-18-2012, 03:12 PM   #8
colucix
LQ Guru
 
Registered: Sep 2003
Location: Bologna
Distribution: CentOS 6.5 OpenSuSE 12.3
Posts: 10,509

Rep: Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976Reputation: 1976
Quote:
Originally Posted by grail View Post
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!
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
How to display 2 different column field values as one column value in mysql VijayaRaghavanLakshman Linux - General 2 04-16-2012 10:56 AM
compare second column of a file then print the first column of it in a ne fil if true java_girl Linux - Newbie 2 03-16-2012 05:50 AM
how to sort the 2nd column on the basis of first column without repeating the value ? zediok Linux - Newbie 15 12-20-2011 12:48 PM
LXer: The summing up LXer Syndicated Linux News 0 04-28-2006 06:21 PM


All times are GMT -5. The time now is 04:32 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration