Sum of 2nd column based on 1st column

Question

Sum of 2nd column based on 1st column(Hour value), i have a file 1st coumn is hour and 2nd coulmn is count. I'm calculating total based on the hour coulmn,

Input
01:01,15
01:02,16
01:03,6
02:01,44
02:02,33
02:05,22
14:01,55
14:02,06

Output:
01,37
02,99
14,61

I was able to create the required output by below steps.

create the unique hour file, below is sample code, while IFS=":" read f1 f2 do

if [ $f1 -eq 01 ]
then
echo $f1":",$f2 >> convertedFile01
fi

then from the converted file, I'm suming the column value. But this process generates 24 converted files, is there a way to generate the expected output in a simple way?

Kent · Accepted Answer · 2014-10-23 12:38:50Z

4

this one liner should do:

awk -F'[,:]' '{a[$1]+=$3}END{for(x in a)print x","a[x]}' file

answered Oct 23, 2014 at 12:38

Kent

196k36 gold badges248 silver badges316 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marjer Over a year ago

Can you please explain

user3442743 Over a year ago

:( beat me to it. They are literally identical ha

score 2 · Accepted Answer · 2014-10-23 12:46:26Z

2

save in array, print at end

awk -F'[:,]' '{a[$1]+=$3}END{for(i in a) print i","a[i]}' file

Explanation

 -F'[:,]' - Sets field separator to either a : or ,

  {a[$1]+=$3} - For each line store the value of the third field in an associative array 
                with the value in the first field as a key

  END{for(i in a) print i","a[i]} - At the end of the file, for each item in 
                         the array, print the key and the value for that key

edited Oct 23, 2014 at 12:46

answered Oct 23, 2014 at 12:41

user3442743

1 Comment

user3442743 Over a year ago

@EdMorton I know, he hadn't posted as i was writing :(

Collectives™ on Stack Overflow

Sum of 2nd column based on 1st column

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related