Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

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
share|improve this question
    
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! –  Abraham Sep 5 '13 at 9:49

3 Answers 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
share|improve this answer
    
Very elegant. +1 for the -a switch which was new to me –  Joseph R. Sep 5 '13 at 13:16
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}'
share|improve this answer
    
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 –  Abraham 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!! –  Abraham Sep 5 '13 at 10:30

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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