1

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
2

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
  • 1
    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
2

Using this awk script with gawk:

{
   split($2,time,":");
   seconds=time[1]*60;
   seconds+=time[2];
   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[1]*60;seconds+=time[2];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[1]*60;seconds+=time[2];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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.