MATLAB: Extract values from a cell based on dates

MATLAB

Hi all,

I have data in 6 separate cells (A, B, C, D, E, F), all which are 365×1. One of these cells (A) contains dates as 'datenum' for a whole 12 months. My question is how can I call/pull/extract data from these cells based on date requirements and get an average. i.e. all of the values on the 10th for each month.

Would it be best to use stack and place them all as a table? I don't have any code for this yet as I don't know where to begin.

Best Answer

Here's another approach

%%Dummy dataset
Date = datetime('2000-01-01')+days(0:364);
Date.Format = 'dd/MMM/yyyy';
Data = rand(numel(Date),5);
%%Data to timetable
TT = timetable(Date',day(Date)',Data)
TT = splitvars(TT)
TT.Properties.VariableNames = {'DayOfMonth','C1','C2','C3','C4','C5'}

TT =

366×6 timetable
       Time        DayOfMonth       C1            C2            C3           C4           C5    
    ___________    __________    _________    __________    __________    _________    _________
    01/Jan/2000         1          0.83516       0.64556       0.79434      0.73071     0.068773
    02/Jan/2000         2          0.11948       0.47084       0.92745      0.75217      0.19456
    03/Jan/2000         3          0.76848       0.28703       0.54273      0.59603      0.94978
    ...

Continuing...

%%Mean, std and max with DayOfMonth as grouping variable
[s1,s2,s3] = grpstats(TT.Variables,TT.DayOfMonth,{'mean','std','max'})
%%Monthly average using retime
TT2 = retime(TT,'monthly','mean')

s1, s2 and s3 are matrices with 31 rows representing the selected statistics for each day of the month.

Related Solutions

MATLAB: Reformatting a table with (yyyy-mm-dd HH:mm:ss) in one column to a new table with dates in the rows and time in the columns.

James, you're right about retime, what you're looking for is a "seasonal average" (I forget the right term) where all 10:00's across all days are grouped together. There are several ways to do this, here's two of them:

Create some simple data:

>> X = randn(10,1);
>> day = [1 1 2 2 3 3 4 4 5 5]';
>> hour = [0 12 0 12 0 12 0 12 0 12]';
>> tt = timetable(X,'RowTimes',datetime(2017,1,day,hour,0,0))
tt =
  10×1 timetable
            Time                X    
    ____________________    _________
    01-Jan-2017 00:00:00     0.083462
    01-Jan-2017 12:00:00      0.42727
    02-Jan-2017 00:00:00       1.3205
    02-Jan-2017 12:00:00      0.72672
    03-Jan-2017 00:00:00       1.0172
    03-Jan-2017 12:00:00    0.0081844
    04-Jan-2017 00:00:00        1.711
    04-Jan-2017 12:00:00      0.17656
    05-Jan-2017 00:00:00       1.5919
    05-Jan-2017 12:00:00      0.47648

Add hour of day as a new variable, and compute grouped means by Hour:

>> tt.Hour = tt.Time.Hour;
>> varfun(@mean,tt,'GroupingVariable','Hour','OutputFormat','table')
ans =
2×3 table
  Hour    GroupCount     mean_X 
  ____    __________    ________
    0         5         -0.20561
   12         5          -0.6783

Or, add day as a new variable and unstack the data by hour within day:

>> tt.Day = tt.Time.Day;
>> ttu = unstack(tt,'X','Hour','GroupingVariable','Day');
>> ttu.Time.Format = 'defaultDate'
Warning: Variable names were modified to make them valid MATLAB identifiers. 
ttu =
  5×3 timetable
        Time       Day       x0          x12   
    ___________    ___    ________    _________
    01-Jan-2017     1      0.66052    -0.071304
    02-Jan-2017     2     -0.37509      0.44703
    03-Jan-2017     3        1.085      -1.8861
    04-Jan-2017     4      -1.7021      -2.0648
    05-Jan-2017     5     -0.69644       0.1836

Then you can compute means by hour. I would think the first version is more useful unless you want the unstacked version for something else.

MATLAB: Pulling interval ranges of data from a timetable

https://www.mathworks.com/help/matlab/ref/timerange.html

However I am not sure if you can concatenate several of these together to form a single subscript. My memory from last year is that you cannot do that and so would have to handle the ranges separately.

Another approach would be to extract the day number from each and compare it to 28 to see if it was in range, making a logical mask that you could apply to the row indices.

Best Answer

Related Solutions

MATLAB: Reformatting a table with (yyyy-mm-dd HH:mm:ss) in one column to a new table with dates in the rows and time in the columns.

MATLAB: Pulling interval ranges of data from a timetable

Related Question