0

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?

  • First off I see a single line per day in your sample input file. what do you consider the daily average ? I mean average of which values ? Also please note that this site doesn't exist for helping with homework questions or doing your work for you, contrary to common belief. If you have tried a solution and get stuck somewhere, please post it in your question for further help. Otherwise don't expect someone to provide a solution from the scratch. – MelBurslan Aug 19 '16 at 13:42
  • @MelBurslan-Thank you for your comments. I don´t know how to do neither from where to start that´s why I dare to ask something here. By daily average, regarding the RES1 for example, I mean the average value of everyday from the different values from 1971 until 2100 (for example the average value of day 01.01, regarding all the associated values to day 01.01 from 1971 until 2100) – steve Aug 19 '16 at 13:53
  • So there is more than one line starting with 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 ? – MelBurslan Aug 19 '16 at 14:02
  • @MelBurslan- No there is just one line starting with 1971;1;1; but I am searching to calculate for day 01.01 for example the average values of this day regarding the fact that my data span from 1971 until 2100 (therefore in my data there is 128 day 01.01 for RES1 and also for RES2 and I am trying to calculate the average of those 128 values for each days). This is not a one time deal therefore I think I will solve my problem while understanding the different solutions the users can provide me. I am expecting a bash script, whereas I personally try to solve this problem using R . – steve Aug 19 '16 at 14:10
  • Please edit your question and clarify what you need. Don't put the details in the comments, they're hard to read and easy to miss. I think you want the average for each month across all years, but that isn't clear from your question. – terdon Aug 19 '16 at 14:23
4

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.

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.