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?
1971;1;1;
. Right ? Also, assuming this is not a one-time-only deal, how do you think you will solve your problem and apply it to similar data sets in the future, if you don't know how to start. Are you expecting a script will do this or a C program may be ? Or are you looking for a statistical analysis program ?