How to separate and order timetable data by year

data = readtable('USA_SITE_004_final.txt'); %import text file and present as table

data_nan = standardizeMissing(data, -999); %return '-999' values as 'NaN'

hourly_temp_rain_sm = data_nan(:, [1 5 11 26]); %extract key variables

TT = table2timetable(hourly_temp_rain_sm); %form timetable

daily_temp_rain_sm = retime(TT, 'daily', 'mean'); %retime data to obtain daily averages

daily_sm = daily_temp_rain_sm(:,3);

Hi, i have created a timetable and retimed soil moisture data here to obtain daily averages for a period of several years. I now want to be able to obtain a metric for each year and to do this i will need to separate the data into years, keeping the daily averages each year. I hope that makes sense and thank you in advance for any help.

Answer 1

[Adam Danz]

> I now want to be able to obtain a metric for each year and to do this i will need to separate the data into years, keeping the daily averages each year.

You can use retime with yearly intervals. By storing this in a different variable, you can also keep the daily values.

dt = datetime(1999,1,1) + days(0:1:2000)';
rainfall = rand(numel(dt),1);
TT = timetable(dt, rainfall)  % Daily rainfall

TT = 2001×1 timetable
        dt         rainfall
    ___________    ________

    01-Jan-1999     0.09073
    02-Jan-1999     0.82922
    03-Jan-1999     0.68411
    04-Jan-1999     0.15243
    05-Jan-1999     0.47396
    06-Jan-1999    0.084376
    07-Jan-1999     0.50433
    08-Jan-1999     0.65501
    09-Jan-1999     0.47188
    10-Jan-1999     0.29966
    11-Jan-1999     0.90719
    12-Jan-1999     0.51547
    13-Jan-1999     0.47611
    14-Jan-1999      0.6801
    15-Jan-1999    0.015343
    16-Jan-1999     0.56246

yearlyTT = retime(TT,'yearly','mean')  % yearly avg

yearlyTT = 6×1 timetable
        dt         rainfall
    ___________    ________

    01-Jan-1999    0.52045 
    01-Jan-2000    0.51869 
    01-Jan-2001    0.49597 
    01-Jan-2002    0.50299 
    01-Jan-2003    0.50966 
    01-Jan-2004    0.47196 

Note that the aggregate method can be a function handle if your metric is not a built-in option. See doc retime for more info.

[Star Strider]

The problem with that approach (that I experimented with, as well as others) is:

‘ i will need to separate the data into years, keeping the daily averages each year.’

That is simply a matter of creating separate table or timetable arrays for each year, a relatively straightforward operation.

.

[Adam Danz]

I would highly recommend to use indexing or to create a simple anonymous function to output a subsection of the timetable by year instead of breaking up timetables. Breaking up the timetable by year would necessitate storing the sub-tables in a cell array which introduces complicated indexing. Surely users should not assign the subtables to different variables since that would either need to be done manually or by using dynamic variable naming which is very poor practice.

For example, if the user wants to temporarily isolate all data from 2001 using an anonymous function,

% Create demo timetable
dt = datetime(1999,1,1) + days(0:1:2000)';
rainfall = rand(numel(dt),1);
TT = timetable(dt, rainfall)  % Daily rainfall

TT = 2001×1 timetable
        dt         rainfall
    ___________    ________

    01-Jan-1999     0.55943
    02-Jan-1999      0.2185
    03-Jan-1999     0.34786
    04-Jan-1999     0.91244
    05-Jan-1999    0.041274
    06-Jan-1999     0.44867
    07-Jan-1999     0.25255
    08-Jan-1999     0.73741
    09-Jan-1999     0.23256
    10-Jan-1999    0.053528
    11-Jan-1999    0.019076
    12-Jan-1999     0.66806
    13-Jan-1999     0.32396
    14-Jan-1999     0.87044
    15-Jan-1999      0.8415
    16-Jan-1999     0.68612

% anonymous fcn: enter year as integer
TTbyYearFcn = @(yr)TT(year(TT.dt)==yr,:); 

% Get values from 2001
TTbyYearFcn(2001)

ans = 365×1 timetable
        dt         rainfall
    ___________    ________

    01-Jan-2001     0.70419
    02-Jan-2001     0.62215
    03-Jan-2001     0.93004
    04-Jan-2001     0.57443
    05-Jan-2001    0.055897
    06-Jan-2001     0.95668
    07-Jan-2001     0.50563
    08-Jan-2001     0.62463
    09-Jan-2001     0.27101
    10-Jan-2001     0.44119
    11-Jan-2001     0.75864
    12-Jan-2001     0.53423
    13-Jan-2001     0.13889
    14-Jan-2001     0.34593
    15-Jan-2001     0.08533
    16-Jan-2001      0.1614

% Example of extracting rainfall from 2001
TTbyYearFcn(2001).rainfall

ans = 365×1

0.7042 0.6221 0.9300 0.5744 0.0559 0.9567 0.5056 0.6246 0.2710 0.4412

[Adam Danz]

Another approach would be to create a 3D array of the data where one dimention defines dates by year but that get a little tickly with leapdays, but still achievable.

[Joe Wheeler]

Thanks adam! I will try and apply this to my problem and see where it gets me - apologies for not properly explaining my problem but you were correct in assuming that i am looking to create separate timetables/tables with the data for each year i.e. rainfall data from 2001 etc. thanks again!

[Joe Wheeler]

Unfortunately i got this error:

Unable to use a value of type datetime as an index.

Error in Research_Project>@(yr)daily_sm(year(daily_sm.DT)==yr,:) (line 31)
daily_smbyYearFcn = @(yr)daily_sm(year(daily_sm.DT)==yr,:);

Error in Research_Project (line 33)
A = daily_smbyYearFcn(2001);

[Adam Danz]

What Matlab release are you using?

I'm assuming daily_sm.DT contains datetime values. Does year(daily_sm.DT) throw an error?

Or maybe you have a variable named "year" which is preventing use of the function of the same name.