Home > awk, bash > awk – Get the sum of a column based on a second column

awk – Get the sum of a column based on a second column

Problem:
I had a list of totals for various days in the month and I needed to add them up.
From

2012-05-09,2
2012-05-10,2
2012-05-11,3
2012-05-12,3
2012-05-13,2
2012-05-14,2
2012-05-01,3
2012-05-02,4
2012-05-03,2
2012-05-04,2
2012-05-10,2
2012-05-11,2
2012-05-12,1
2012-05-13,2
2012-05-14,2

To

2012-05-01,3
2012-05-02,4
2012-05-03,2
2012-05-04,2
2012-05-09,2
2012-05-10,4
2012-05-11,5
2012-05-12,4
2012-05-13,4
2012-05-14,4

Solution
I was looking for an awk solution and found on at unix.com
Awk + sort

user@computer:~$echo "2012-05-09,2                                                                       
2012-05-10,2
2012-05-11,3
2012-05-12,3
2012-05-13,2
2012-05-14,2
2012-05-01,3
2012-05-02,4
2012-05-03,2
2012-05-04,2
2012-05-10,2
2012-05-11,2
2012-05-12,1
2012-05-13,2
2012-05-14,2" | awk -v FS=, -v OFS=, '{a[$1]+=$2}END{for (i in a) print i,a[i]}' | sort -t, -k1

Awk explained:

The first {…} block creates an array a indexed with parameter $1, adding $2.
When ‘2012-05-13’ on the first column is encountered the first time, you end up with a[‘2012-05-13’]=2, then the second time a[‘2012-05-13’]+=2 (a[‘2012-05-13’] is already 2, plus 2 = 4).

The second {…} block takes each index from the a array, store them in i, then displays i and the corresponding a[‘i’]

Source
http://www.unix.com/shell-programming-scripting/126311-print-unique-values-column-sum-up-corresponding-values-next-column.html

Advertisements
Categories: awk, bash
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s