# how to calculate the average value of the column that have the row ID?

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
``````
• 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

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
``````
• 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}'
``````
• 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

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
``````