How can I get the averages of each day of multiple years?

Hello,

I have a table with 3 collums: the first collum are dates over 4 years. The second and third collum are vallues of temperature and flowrate of a river for te corresponding date. The Temperature has some NaN's in the data. Nan's have to be ignored

I want to calculate the average temperature and flowrate on each day over this years. So I get a new table (365x3) with each date of the year with these avarage values.

Can someone help me to achive this please?

Thank you!

[Mathieu NOE]

hello

ok can your share your data file ?

[Hendrik Van de Peer]

Thanks for responding! Some words are in dutch but normally its all clear: If your run the code you wil see the table to work with :)

[Sindar]

Check out groupsummary, specifically this example and this input argument

[Sindar]

Also, this thread has some solutions related to leap years

[Mathieu NOE]

tx Sindar for your inputs

[Mathieu NOE]

hello Hendrick

I was figuring out how join is actually working in your code, have to get more familiar with this function

so your 2 excel files do not cover the same period; what is the intention for the not common dates ? do we fill the missing data with Nans or zeros or do we peform your data analysis only on the common date segement ?

I am not sure that join is doing what we wanted ...

[Hendrik Van de Peer]

The join function joins two tables with eachother. So in this case it joins te flowrate data with the temperature data that is available.

[Mathieu NOE]

yes I understand the that reason but have you looked how the final time table looks ??

[Hendrik Van de Peer]

Whats wrong with it? It a table with all data thats available for the temperature and flow rate. In the temperature there are still at some dates NaN's. But these can be ignored in the calculation of the mean per day.

[Mathieu NOE]

ok ok just worrying about several sections of the final data that looked strange to me

• we can have a valid date and some data (or one data is NaN or both are NaN)
• we have sometimes both data with an invalid date ; NaT (not a time value)

so what do we do in this case ?

extract :

NaT 13.76 3

NaT 14.23 3

NaT 13.63 2.5

NaT 13.22 2.5

NaT 12.08 2.5

NaT 11.18 3

1/05/2006 10.42 3.5

2/05/2006 11.43 3

3/05/2006 13.66 3

4/05/2006 15.49 2.5

5/05/2006 15.77 2.5

6/05/2006 16.17 4

7/05/2006 15.44 2.5

8/05/2006 14.79 2.5

9/05/2006 14.69 3.5

10/05/2006 15.23 3.5

11/05/2006 15.43 3

12/05/2006 16.17 2.5

13/05/2006 16.14 3.5

14/05/2006 15.38 3.5

15/05/2006 14.91 3

16/05/2006 15.57 3

17/05/2006 15.97 3

18/05/2006 NaN 4

19/05/2006 15.5 3.5

20/05/2006 14.1 4.5

21/05/2006 13.84 4.5

22/05/2006 14.8 6

23/05/2006 14.5 4

24/05/2006 13.56 3.5

25/05/2006 13.1 5

26/05/2006 13.17 5.5

27/05/2006 14.01 7

28/05/2006 14.47 7.5

29/05/2006 13.95 5.5

30/05/2006 12.86 5.5

31/05/2006 12.08 4.5

NaT 11.76 4

NaT 11.96 4.5

NaT 13.27 3.5

NaT 14.1 3.5

NaT 14.68 3.5

Answer 1

[Sindar]

Check out groupsummary, specifically this example and this input argument

Also, this thread has some solutions related to leap years and NaN data