Calculate average values for each day over multiple years

Question

I have some "CSV" data (actually using ; as a delimiter) having a row for every day from 1971-01-01 through 2099-12-31 (a span of 2099−1971=128 years). The data are organized as follows:

YEAR;MONTH;DAY;RES1;RES2
1971;1;1;1206.1;627
1971;1;2;1303.4;654.3
1971;1;3;1248.9;662
1971;1;4;1188.8;666.8
1971;1;5;1055.2;667.8
1971;1;6;987.1;663.3
1971;1;7;939.2;655.1
1971;1;8;883.2;644.4
          ︙
2099;12;29;791.7;664.3
2099;12;30;746.7;646.4
2099;12;31;706.8;629.3

With this data I need to calculate the average value for each calendar day (of the 365 in a year) over all years (so retain month and day and average over the years). For example, since the data span from 1971 until 2100, I have 128 data points for 01-01 (January 1). I would like to calculate the average of those 128 values for January 1 (i.e., the values for 1971-01-01, 1972-01-01, ..., 2099-01-01); and so on for day 01-02 (January 2) until day 12-31 (December 31). Therefore, the desired output should include 365 days and look as follows:

MONTH;DAY;RES1;RES2
1;1;AVERAGE_1.1_RES1;AVERAGE_1.1_RES2
1;2;AVERAGE_1.2_RES1;AVERAGE_1.2_RES2
1;3;AVERAGE_1.3_RES1;AVERAGE_1.3_RES2
1;4;AVERAGE_1.4_RES1;AVERAGE_1.4_RES2
1;5;AVERAGE_1.5_RES1;AVERAGE_1.5_RES2
1;6;AVERAGE_1.6_RES1;AVERAGE_1.6_RES2
1;7;AVERAGE_1.7_RES1;AVERAGE_1.7_RES2
                  ︙
12;29;AVERAGE_12.29_RES1;AVERAGE_12.29_RES2
12;30;AVERAGE_12.30_RES1;AVERAGE_12.30_RES2
12;31;AVERAGE_12.31_RES1;AVERAGE_12.31_RES2

How can I do that?

Answer 1

If you want to average each day over all years, you could do something like

awk -F\; '
  NR>1 {
    sum1[$2";"$3]+=$4; sum2[$2";"$3]+=$5; n[$2";"$3]++;
  } 
  END {
    printf "MONTH;DAY;RES1;RES2\n"; 
    for (i in n) printf "%s;%.1f;%.1f\n", i, sum1[i]/n[i], sum2[i]/n[i]
  }' file.csv

Note that the output order isn't guaranteed unless you sort the arrays - the most convenient way to do that depends somewhat on your flavor of awk. Or you could simply pipe the output through an external sort.