CODEWITHSUNDEEP
shellunixawk
Marjer
Marjer

Reputation: 1403

Sum of 2nd column based on 1st column

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?

Upvotes: 1

Views: 1583

Answers (2)

user3442743
user3442743

Reputation:

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

Upvotes: 2

Kent
Kent

Reputation: 195029

this one liner should do:

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

Upvotes: 4

Related Questions