Daily average precip for many years

I have a large matrix of two columns containing dates (eg 1/1/1991) and precip values. I want to create the average daily precip for all years from 1991-2016 such that the day of the year is in column 1 and the ave precip value is in column 2. I'm getting bogged down I believe because of leap years (not sure how to eliminate that date, which is fine). Any help would be greatly appreciated.

[the cyclist]

Can you (ideally) upload your data in a MAT file, or (less ideally) give an example -- not just a description -- of how your input data are stored?

[Thomas Burbey]

Here is the precip file you requested showing you the format.

Answer 1

[dpb]

Just taking a stab presuming the file is something recognizable--

precip=table2timetable(readtable('yourfile.ext'));  % read the file; convert to timetable
daily=retime(precip,'daily','mean');

Done!

"Here is the precip file..."

Oh...only daily data and already in table--OK, then create the grouping variable

>> precip.DOM=day(precip.DATE);           % day of month each entry
>>daily=varfun(@mean,precip,'groupingvariables','DOM','inputvariables','Precipitation');
...

ADDENDUM OK, answer the question asked... :)

>> precip.DOY=day(precip.DATE,'dayofyear');
>> daily=varfun(@mean,precip,'groupingvariables','DOY','inputvariables','Precipitation');
>> whos daily
Name         Size            Bytes  Class    Attributes
daily      366x3             10149  table              

>> daily(1:10,:)
ans =
10×3 table
  DOY    GroupCount    mean_Precipitation
  ___    __________    __________________
   1     26            3.27692307692308  
   2     26                         3.8  
   3     26                         2.5  
   4     26            2.84230769230769  
   5     26            3.94615384615385  
   6     26            5.21923076923077  
   7     26            2.13461538461539  
   8     26            2.53461538461538  
   9     26                        3.65  
  10     26            3.33461538461538  
>>

should work; I believe the 'dayofyear' option came about R2104 or so...there's just too much stuff to try to recall introduced too rapidly.

[Thomas Burbey]

This does not work. It leaves me with a table that has 365x(number of years). The file I'm after has 365 rows (one for each day) and two columns. The first is the day of the year (1-365) the other is the mean daily precip for all the years in the original file (from 1991-2016).

[Thomas Burbey]

Here is the starting mat file

[dpb]

Actually, it leaves 31 rows, one for each day of month...I've got meeting at moment so hafta' run but the idea is the same, build the same grouping variable for each day of year as diff between DATE and the 12/31/(year-1) and apply varfun

There may be some specific function for DOY; I don't recall--there's been a lot of new stuff added with the timetable and such that just is hard to keep up with...which release you're using can make big difference, too, as this is a very dynamic area just now.

[Thomas Burbey]

I can't seem to get it to work. I'm running R2016b. I'll try to follow your logic and see what I can do. Thanks.

[Razvan Carbunescu]

The 'dayofyear' option inside day function should give the discretization you need:

precip.DOY=day(precip.DATE,'dayofyear');

[dpb]

I just recalled before heading to town...

[Thomas Burbey]

Thanks again. We're getting closer but still not there. The 'dayofyear' gets us in the ballpark but the varfun is not working properly. For example, I know 1/1/1991 has rainfall, yet the mean for day 1 posts NaN, which is incorrect. How are NaN handled? They should be treated as zeros.

[Thomas Burbey]

I figured it out by converting NaN first to zeros. Thank you

[dpb]

no NaN in evidence in the initial dataset--

>> sum(isnan(precip.Precipitation))
ans =
   0
>>

You have another dataset that does have NaN? I just got the second link to the .mat file and it appears identical to the first; no NaN.

Something's not kosher it would seem...to illustrate works,

>> precip(precip.DOY==1,:)
ans =
26×3 table
    DATE      Precipitation    DOY
  ________    _____________    ___
  1/1/1991     3.4             1  
  1/1/1992       0             1  
  1/1/1993       0             1  
  1/1/1994     2.1             1  
  1/1/1995     0.3             1  
  1/1/1996       0             1  
  1/1/1997       0             1  
  1/1/1998       0             1  
  1/1/1999     3.4             1  
  1/1/2000     0.6             1  
  1/1/2001       0             1  
  1/1/2002       0             1  
  1/1/2003    10.7             1  
  1/1/2004     8.7             1  
  1/1/2005     1.3             1  
  1/1/2006     6.9             1  
  1/1/2007     9.4             1  
  1/1/2008       0             1  
  1/1/2009       0             1  
  1/1/2010       0             1  
  1/1/2011       0             1  
  1/1/2012    10.2             1  
  1/1/2013       0             1  
  1/1/2014      22             1  
  1/1/2015     0.2             1  
  1/1/2016       6             1  
>> mean(ans.Precipitation)
ans =
        3.27692307692308
>>

which matches the varfun solution. If that's not working like that for you, post exact code...

>> sum(isnan(daily.mean_Precipitation))
ans =
   0
>>

[Thomas Burbey]

Everything works fine. I gave you the matrix after I had eliminated NaN, which is what the raw precip file has when no precip for a day is measured. Sorry for the confusion. Thanks again for your help.

[dpb]

Ah, so! That 'splains things, indeed! :)

The time-related stuff has gotten pretty deep as well as facilities to use the various varieties of tables. Seems to me like there's an excess of types that are similar but not quite the same that makes for very difficult learning task; I wish TMW would slow down some and work more on consistency of interfaces and methods.

Answer 2

[Razvan Carbunescu]

An alternative if using R2018a is groupsummary, it has a few different ways of selecting days for each week, month, year from a date:

>> GT = groupsummary(precip,'DATE','dayofyear','mean','Precipitation');
>> head(GT)
ans =

8×3 table

    dayofyear_DATE    GroupCount    mean_Precipitation
    ______________    __________    __________________

          1               26              3.2769      
          2               26                 3.8      
          3               26                 2.5      
          4               26              2.8423      
          5               26              3.9462      
          6               26              5.2192      
          7               26              2.1346      
          8               26              2.5346

[Thomas Burbey]

That is cool! I wish I had that latest version. SO helpful

[dpb]

There's only a one-liner difference to generate the grouping variable, though, so it's only a relatively minor syntactic sugar refinement.

Note same results as above which is another independent confirmation of correct result.