1

I want to calculate the average value of my data when they have the same ID (in my specific case, the same day but different time in a day). This is meant to calculate the daily average value of my data from the hourly/sub hourly data. My data looks like below.

Date        hour        value 
06/21/1978  14:00:00    1
06/21/1978  15:00:00    2
06/21/1978  16:00:00    3
06/21/1978  17:00:00    4
06/21/1978  18:00:00    5
06/21/1978  19:00:00    6
06/21/1978  20:00:00    7
06/21/1978  21:00:00    7
06/21/1978  22:00:00    9
06/21/1978  23:00:00    10
06/22/1978  00:00:00    5
06/22/1978  01:00:00    5
06/22/1978  02:00:00    5
06/22/1978  03:00:00    7
06/22/1978  04:00:00    8
06/22/1978  05:00:00    9
06/22/1978  06:00:00    22
06/22/1978  07:00:00    56
06/22/1978  08:00:00    9
06/22/1978  09:00:00    12
06/22/1978  10:00:00    3
06/22/1978  11:00:00    5
06/22/1978  12:00:00    7

I want the output to be

Date        value
06/21/1978  5.4
06/22/1978  11.7692307692
1
  • My data a fixture of daily, hourly and sub-hourly data, so I want to calculate the average when there is more than one data in a day. Plus, I want to keep the data order according to the date. Thank you! – AiB Sep 5 '13 at 9:49
3

This is essentially the same as Stephane's awk solution but coded in Perl. It will preserve the order of the dates:

perl -ane 'if($.==1){print "$F[0]\t$F[2]\n"; next}
           $k{$F[0]}+=$F[2]; $l{$F[0]}++; 
           END{print "$_\t",$k{$_}/$l{$_},"\n" for (sort keys(%k))}' data
1
  • Very elegant. +1 for the -a switch which was new to me – Joseph R. Sep 5 '13 at 13:16
3
awk 'NR==1{print $1,$3; next}
    {v[$1]+=$3;n[$1]++}
    END{for (i in n) print i, v[i]/n[i]}'

The order is not guaranteed. If the input itself is sorted by date, you could print as soon as the date changes:

awk 'NR==1{print $1,$3; next}
    {if (n && $1 != l) {print l, v/n; n=0; v=0}
     v+=$3; n++; l=$1}
    END{if (n) print l, v/n}'
2
  • I save and run the script as below, but it didn't work. Can you elaborate a little bit. My file name is flow #!/bin/bash awk 'NR==1{print; next} {v[$1]+=$3;n[$1]++} END{for (i in n) print i, v[i]/n[i]}' flow – AiB Sep 5 '13 at 10:00
  • Thank you, I got the result now but it didn't keep the date order/my data order. Is there anyway the order could be kept as in the input file? Thank you!! – AiB Sep 5 '13 at 10:30
1

Very similar to Stephane's solution except that it takes advantage of GNU awk's PROCINFO["sorted_in"] to enforce array traversal in date order.

awk 'BEGIN{PROCINFO["sorted_in"]="@ind_str_asc"};
NR==1{print $1,$3; next};
{arr[$1]+=$3; ++arr2[$1]};
END{for (k in arr) print k, arr[k]/arr2[k]}' test.1 | column -t

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.