LinuxQuestions.org
Help answer threads with 0 replies.
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 10-16-2012, 08:57 AM   #1
umix
LQ Newbie
 
Registered: Oct 2011
Posts: 13

Rep: Reputation: Disabled
sum a value of column based on the value of other columns


Hey Guys!
I am new to unix and use it rarely...need yor help..

i have csv file:

col1,col2,col3,col4,col5,col6
1,"solve,prob",Y,N,20.8,z/3
2,No Prob,N,Y,179.2,z/3
3,"solve,prob",Y,Y,30,z/3

need to sum the values of col5 based on values in col3 and col4 as Y.....commas in double quotes are not considered as delimiter.

output: 260
explanation----> 20.8(one time Y in row 1 col3)+179.2(one time Y in row 2 col4)+30*2(two times Y in row 3 in both col 3 n 4)=260

Last edited by umix; 10-16-2012 at 09:03 AM.
 
Old 10-16-2012, 11:04 AM   #2
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,424

Rep: Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823
And what have you tried? I would dissuade you from using bash as it does not natively process floating point numbers.
 
Old 10-17-2012, 06:18 AM   #3
umix
LQ Newbie
 
Registered: Oct 2011
Posts: 13

Original Poster
Rep: Reputation: Disabled
Hi Grail!!

I tried below code but it is not perfect:

awk -F"Y" '{print NF-1}' tst.txt > file1
awk '{FS=",";} {print $(NF-1);}' tst.txt > file2
paste file1 file2 >file3
awk '{print $1 * $2}' file3>file4
cat file4 | awk '{ sum+=$1} END {print sum}'
rm file1
rm file2
rm file3
rm file4

output:260

prob:it does not ignore the commas in double quotes
: count Y in whole row rather than only col3 and col4
 
Old 10-17-2012, 12:41 PM   #4
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,424

Rep: Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823Reputation: 2823
What version of awk are you using?
 
Old 10-19-2012, 01:08 AM   #5
umix
LQ Newbie
 
Registered: Oct 2011
Posts: 13

Original Poster
Rep: Reputation: Disabled
Is there any command to check it?
 
Old 04-11-2013, 04:15 PM   #6
pgalba
LQ Newbie
 
Registered: Apr 2013
Posts: 6

Rep: Reputation: Disabled
Quote:
Originally Posted by umix View Post
Is there any command to check it?
Code:
awk --version
 
  


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
Sum a column micyew Programming 10 06-26-2012 03:30 PM
sum up values from each columns (awk) lcvs Linux - Newbie 10 06-20-2012 05:16 AM
Sum up value of columns in 4 files using shell script reach.sree@gmail.com Programming 6 06-07-2012 08:29 PM
[SOLVED] Selecting lowest and highest values in columns 1 and 2, based on subsets in column 3 hubleo Linux - Newbie 9 04-25-2012 08:30 AM
mysql sum from one of two columns secretlydead Programming 1 12-16-2009 02:11 AM


All times are GMT -5. The time now is 12: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