Extract values from a cell based on dates

Hi all,

I have data in 6 separate cells (A, B, C, D, E, F), all which are 365x1. 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.

[jonas]

I dont understand exactly what you mean, but I am certain you will want to use datetime and perhaps place the data in a timetable.

[Walter Roberson]

timetable and retime() perhaps.

[Manuel Flores]

Sorry probably be better if I explain it this way. I have 6 cells that are 365x1 in size. Dates (in datenum), Max_Temp, Min_Temp, pressure, Humidity, speed, and Rainfall, which were all imported from 12 monthly excel files. I would like to create a loop or count I guess (not really sure as I'm still new) to place the first 28 days worth of data in a month as a column and store it in a separate cell/matrix, for Max_Temp only.

I would then like to add a 2nd column to this cell or matrix with the first 28 days of data from the following month and so on until I end up with a 28 x 12 cell. Someone one at my work suggested using a count inside a loop so that it only records from the 1st to 28th of each month and skip the rest of the days. i.e 29th, 30th, 31st.

The reason I want the information like this is so that I can do the average value of all the 1st days of the month (since they will be in a row), the 2nd's 3rd's and so on and end up with a cell that's 28x1 of the averages row values.

Hope this is clearer.

P.S. numeric data that's inside the arrays doesn't not matter for this explanation. The key thing is the dates so it knows when to skip the values or not.

[ANKUR KUMAR]

Please attach the .mat file for more clarification.

[Guillaume]

It sounds to me that you would be much better off changing tack and importing your data in a single table or better a timetable. This probably would be simpler as well than the import code you already have.

Once the data is in a table or timetable, averaging by day/month/year/whatever is just a one line operation (using either rowfun on a table or retime on a timetable).

Therefore, I'd recommend we focus on changing your import code. For that, an example of the excel files would be helpful. Normally, a single call to readtable is all that is needed to import excel files.

[jonas]

As was suggested several times already, what you want is a timetable and retime. If you want to calculate some statistics based on the "day of month", then I would suggest using the day of month as a grouping variable. This is easy if you first convert your data to datetime format, which you should do anyway.

[Manuel Flores]

Thanks everyone I'm taking the suggestions mentioned and importing it as a table.

Answer 1

[jonas]

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.

[Guillaume]

Yes, as stated in the comments once the data in the right format calculating statistics per day/month/whatever is trivial. No need to deal with ismember, different time formats, etc.

Note that there is no need to explicitly store the day of the month. It can be trivially obtained with day(TT.Time).

[Manuel Flores]

So got all my data into and converted into a Timetable and I tried using your code Jonah for the Mean and grouping but I don't have the statistics addon. Can this be done another way?

[jonas]

Yes! Guillaume has just demonstrated another way in another thread ( link , see his answer). I will take this opportunity to learn the new functions and get back to you with an alternative solution :)

In the meantime you can use this code:

G = findgroups(TT.DayOfMonth)
out = splitapply(@mean,TT.Variables,G)

Just change the function you want to apply, e.g. @mean, @max... It does not work for table input though, but when you use dot-notation like in the code above the columns are converted to arrays.

EDIT And here is one that works with tables as input! I've checked that all of the three different methods (grpstats, splitapply, varfun) gives the same result. In some sense varfun is actually better than grpstats because it accepts timetables as input.

out = varfun(@mean,TT,'GroupingVariables','DayOfMonth')

Note that the last input is the grouping variable, and should be set to the name of the column that you want to use as grouping variable.

[Manuel Flores]

THANK YOU SO MUCH GUYS!!! This gets me out of trouble.

[jonas]

Cheers!

[Manuel Flores]

Hey gents I have another question.

I have a code like this where I pull the data based on those 4 dates. That works, so no problem there.

% Pull the Temperature from the design dates.
Tr = {'2017-07-01','2017-07-02','2017-08-01','2017-08-02'};
Tj = TT_all(Tr,2);

Now I'm wondering how to do the same for 28 day for every month over 12 months. Obviously it's not like this.

Tr_all = {'2017-07-01:2017-07-28','2017-08-02:2017-08-28','2017-09-01:2017-09-28'}...
    {'2017-10-01:2017-10-28','2017-11-01:2017-11-28','2017-12-01:2017-12-28'}...
    {'2018-01-01:2011-01-28','2018-02-01:2011-02-28','2018-03-01:2011-03-28'}...
    {'2018-04-01:2011-04-28','2018-05-01:2011-05-28','2018-06-01:2011-06-28'};

can you point me in the right direction please. Thanks

[jonas]

Easier would be

 TT(day(TT.Time)==28,:)