sum of all matching rows using awk


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers sum of all matching rows using awk
# 1  
Old 08-05-2008
sum of all matching rows using awk

I have file
"1","x1897"," 89.10"
"1","x2232"," -12.12"
"1","x1897"," 389.10"
"1","x2232"," 212.12"
"1","x1897"," 19.10"
"1","x2232"," 2.12"


i want to add all 3 rd column rows (they have spaces also)for x1
and sum of 3rd column rows for x2 separately.
I am very bad at awk. can some one help me ?
# 2  
Old 08-05-2008
What have you tried so far?

This is not homework for a class is it??? Smilie
# 3  
Old 08-05-2008
Code:
awk -F'"' '/x1/{a+=$(NF-1)}/x2/{b+=$(NF-1)}END{print "x1-> " a, " x2-> " b}' file

x1-> 497.3  x2-> 202.12

or with a new line in between

Code:
awk -F'"' '/x1/{a+=$(NF-1)}/x2/{b+=$(NF-1)}END{print "x1-> " a, "\nx2-> " b}' file

x1-> 497.3
x2-> 202.12

If the file has more than three fields, use ...

Code:
awk -F'","' '/x1/{a+=$3+0}/x2/{b+=$3+0}END{print "x1-> " a, "\nx2-> " b}' file


Last edited by rubin; 08-06-2008 at 07:27 PM.. Reason: added final code
# 4  
Old 08-06-2008
this is not homework problem..

this is what i did

while read file
do
var=`echo $file | cut -f 3 -d "," | sed
....
....
done < file

but i donot wantt to use while loops


-------------------
thanks for the replies. Will this work if the sum amount are in 13 th column or somewhere else ? I could not find them working...

will this work if we need to find the sum on 13th field. File may contain around 30 fields
sum2.awk

{
gsub(/\"| +/,"",$13)
sum += $13

}
END {
print "Sum: ", sum;
}


$ awk -f sum2.awk file

Last edited by i.scientist; 08-06-2008 at 12:57 AM..
# 5  
Old 08-06-2008
No, it wouldn't work, because awk does not know what character to use as a field separator, so it does not know what you mean when you refer to $13.

You can specify a regexp as a separator:

Code:
awk -F '[",]+ *' '{ sum+=$14 } END { print "Sum: ",sum }' file

I had to increase the field number by 1 because with this solution awk would treat the first " as a field delimiter and presume that the first field was empty.
# 6  
Old 08-06-2008
Thanks,

this works fine if all the columns are included in " "
In my case they are not. Sorry for not telling that.

"1",,,,,"X1211","1",

there may be empty fields seperated by commas.
If so how to change the field seperator and how to obtain the sum of 13th column ?
# 7  
Old 08-06-2008
awk -F '[,]' '{ gsub(/\"| +/,"",$13);sum+=$13 } END { printf "Sum: 0.2%d",sum }' file

this worked. Now can i get sum of only those rows matching X1 and X2 ?

thanks for all your replies

Last edited by i.scientist; 08-06-2008 at 11:55 AM..
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to update file with sum of matching fields in another file

In the awk below I am trying to add a penalty to a score to each matching $1 in file2 based on the sum of $3+$4 (variable TL) from file1. Then the $4 value in file1 is divided by TL and multiplied by 100 (this valvue is variable S). Finally, $2 in file2 - S gives the updated $2 result in file2.... (2 Replies)
Discussion started by: cmccabe
2 Replies

2. Shell Programming and Scripting

(hard one) awk, copy matching 2 col & 10-12 rows, is it possible?

Hey guys, my first post here. I'm trying to use awk to copy all matching paragraphs from one file The file looks like this : Test Case Number 990990003099 Card Type CCCC Transaction Type Sale Entry Mode Keyed Account Number 4099562299219923 Transaction Amount 78.88 Description lorem... (2 Replies)
Discussion started by: awkawed
2 Replies

3. Shell Programming and Scripting

awk split columns after matching on rows and summing the last column

input: chr1 1 2 3 chr1 1 2 4 chr1 2 4 5 chr2 3 6 9 chr2 3 6 10 Code: awk '{a+=$4}END{for (i in a) print i,a}' input Output: chr112 7 chr236 19 chr124 5 Desired output: chr1 1 2 7 chr2 3 6 19 chr1 2 4 5 (1 Reply)
Discussion started by: jacobs.smith
1 Replies

4. Shell Programming and Scripting

Sum of range of rows and columns in matrix

Hi all, I have a large matrix of 720 x 25. I want to get sum of range of rows and columns. Like, I need sum of all columns and row number 2 to 21, then leaving 22nd row, again sum of all columns and row number 23 to 42 again leaving 43rd row and then sum of 44th to 63. Means I want to add all... (4 Replies)
Discussion started by: CAch
4 Replies

5. UNIX for Dummies Questions & Answers

List all files with sum of matching word

grep -c 'avihai' 1.log will give me count of 'avihai' in log I want to have a list of files in the folder that show file name with count side by side. Please advice (2 Replies)
Discussion started by: avihaimar
2 Replies

6. Shell Programming and Scripting

Sum of column matching pattern/string

Hi All, I have the following output file created as a result of one script called pattern_daily.log $ cat pattern_daily.log Approved|2|Wed, Oct 24, 2012 11:21:09 AM Declined|1|Wed, Oct 24, 2012 11:21:15 AM Approved|2|Wed, Oct 24, 2012 11:24:08 AM Declined|1|Wed, Oct 24, 2012 11:24:18 AM... (4 Replies)
Discussion started by: Gem_In_I
4 Replies

7. UNIX for Dummies Questions & Answers

Sum the rows number based on first field string value

Hi, I have a file like this one h1 4.70650E-04 4.70650E-04 4.70650E-04 h2 1.92912E-04 1.92912E-04 1.92912E-04 h3A 3.10160E-11 2.94562E-11 2.78458E-11 h4 0.00000E+00 0.00000E+00 0.00000E+00 h1 1.18164E-12 2.74150E-12 4.35187E-12 h1 7.60813E-01 7.60813E-01 7.60813E-01... (5 Replies)
Discussion started by: f_o_555
5 Replies

8. UNIX for Dummies Questions & Answers

how to count number of rows and sum of column using awk

Hi All, I have the following input which i want to process using AWK. Rows,NC,amount 1,1202,0.192387 2,1201,0.111111 3,1201,0.123456 i want the following output count of rows = 3 ,sum of amount = 0.426954 Many thanks (2 Replies)
Discussion started by: pistachio
2 Replies

9. Shell Programming and Scripting

Sum all rows with an Awk one-liner

I have a file with 1000+ columns of data. I need to sum each row (not column). How can I do this with an awk one-liner? Thank you Example file: 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 The output should be: 10 20 30 (13 Replies)
Discussion started by: jm4smtddd
13 Replies

10. Shell Programming and Scripting

bash script, pattern matching + sum

Hi guys, i have been struggling to achieve the following and therefor looking for some help. I am trying to write something that will summerize the following output from my switches for daily totals. Basicly if $1 $2 $3 doesnt change, we can keep adding up $4. Probably would use a awk print end... (3 Replies)
Discussion started by: Wratholix
3 Replies
Login or Register to Ask a Question