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

3 Answers 3

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"}'
6
  • I'm getting wrong values: testing 50 923987 archive 55 103973
    – ekassis
    Jan 26, 2017 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, 2017 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, 2017 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, 2017 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, 2017 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
3
  • @DopeGhotti can I have it please in one command line?
    – ekassis
    Jan 26, 2017 at 22:38
  • Yes, you can. Done.
    – DopeGhoti
    Jan 26, 2017 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, 2017 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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .