# Group all rows based on the first column then calculate the total of the second and third columns

I need to group rows based on the first column then to calculate the summation of the values of all second row and the summation of the values of all third row.

The second column should be calculated as following: 10:56 = 10*60 + 56 = 656 seconds.

Input file:

``````     testing 00:34 123487
archive 00:45 3973
testing 09:16 800500
archive 10:10 100000
``````

Output:

``````     archive 655 103973
testing 590 923987
``````

## 3 Answers

Golfed it down to a one-liner. Works fine on GNU awk 3.1.7. Other awk implementations might need the `\$2*60` replaced with `substr(\$2,0,2)*60`. (Expecting the likes of '09:16' to get interpreted as an integer value of 9 is stretching the rules a little.)

``````awk '{a[\$1]+=\$2*60+substr(\$2,4);b[\$1]+=\$3}END{for(c in a){print c,a[c],b[c]}}'
``````

Giving output:

``````archive 655 103973
testing 590 923987
``````

Alternatively, a perl approach:

``````perl -e 'while(<>){/(\S+) +(\d+):(\d+) (\d+)/;\$a{\$1}+=\$2*60+\$3;\$b{\$1}+=\$4;}for(keys %a){print "\$_ \$a{\$_} \$b{\$_}\n"}'
``````
• I'm getting wrong values: testing 50 923987 archive 55 103973 – ekassis Jan 26 '17 at 22:42
• indeed by replacing `\$2*60` by `substr(\$2,0,2)*60` the command line is working perfectly. `awk '{a[\$1]+=substr(\$2,0,2)*60+substr(\$2,4);b[\$1]+=\$3}END{for(c in a){print c,a[c],b[c]}}'` – ekassis Jan 26 '17 at 22:53
• I did not understand your following last comment (Expecting the likes of '09:16' to get interpreted as an integer value of 9 is stretching the rules a little.) – ekassis Jan 26 '17 at 23:01
• ekassis - your data "testing 09:16 800500". So my awk is interpreting the \$2 value "09:16" as 09. Your awk interprets it as 16. Kinda bad practice to pass a non-numeric field into a math calculation, hence seeing different behaviours in awk versions. My later suggestion of using substr(\$2,0,2)*60 rather than \$2*60 clears this up and offers better practice, consistent results etc. – steve Jan 27 '17 at 10:11
• how can I add a 4th column in the output containing the number of occurrences of each pattern. `archive 655 103973 2 testing 590 923987 2` ? – ekassis Jan 29 '17 at 11:17

Using this `awk` script with `gawk`:

``````{
split(\$2,time,":");
seconds=time*60;
seconds+=time;
types[\$1]["time"]+=seconds;
types[\$1]["othersum"]+=\$3
}

END {
for (record in types)
print record, types[record]["time"], types[record]["othersum"]
}
``````

`gawk -f script.awk /path/to/input` seems to do the trick.

If you need it as a one-liner, you can do this:

``````gawk '{split(\$2,time,":");seconds=time*60;seconds+=time;types[\$1]["time"]+=seconds;types[\$1]["othersum"]+=\$3} END {for (record in types) print record, types[record]["time"], types[record]["othersum"] }' /path/to/input
``````
• @DopeGhotti can I have it please in one command line? – ekassis Jan 26 '17 at 22:38
• Yes, you can. Done. – DopeGhoti Jan 26 '17 at 22:44
• I'm getting a syntax error. gawk: {split(\$2,time,":");seconds=time*60;seconds+=time;types[\$1]["time"]+=seconds;types[\$1]["othersum"]+=\$3} END {for (record in types) print record, types[record]["time"], types[record]["othersum"] } gawk: ^ syntax error – ekassis Jan 26 '17 at 22:46

Just for variety

``````perl -pe 's/(\d+):(\d+)/60*\$1+\$2/e' file | datamash -Ws groupby 1 sum 2,3
archive 655     103973
testing 590     923987
``````