Geek Answers Handbook

Sum a column based on another column

I have a file with 2 columns, 1st column with HH: MM: SS format and second column. I want to summarize the 2nd column based on the 1st dome. in relation to the hour and minute of the 1st column.

Input:

00:00:00,2.00,
00:00:10,1.00,
00:00:20,2.00,
00:00:30,6.00,
00:00:40,1.00,
00:00:50,7.00,
00:01:00,8.00,
00:01:10,7.00,
00:01:20,8.00,
00:01:30,11.00,

Within an hour: awk works below,

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

output:

00,53

For minutes:
it is not possible to sum the column based on the minutes submitted, please suggest a way to get the expected result.

Excluded Conclusion:

00:00,19
00:01,34
+4
source share
2 answers

Using `awk:

awk -F '[:,]' -v OFS=, '{s[$1 ":" $2]+=$(NF-1)} END{for (i in s) print i, s[i]}' file
00:00,19
00:01,34

EDIT: If arranging the original timestamps is important, use:

awk -F '[:,]' -v OFS=, '{k=$1":"$2} !s[k]{b[++n]=k} {s[k]+=$(NF-1)}
     END {for (i=1; i<=n; i++) print b[i], s[b[i]]}' file
00:00,19
00:01,34
+6
source

You need to add minutes to the hour and then count.

awk -F, '{ a[substr($1,0,2)+substr($1,4,2)]+=$2 } END{ for (i in a) print i "," a[i] }'
0,19
1,34

printf, .

+2

More articles:


All Articles