Split a large intraday 1 minute data for each DATE into MATRIX for each DATE?

How can I split an intraday data with DATETIME stamp to each DATE to run a function on each day as matrix?

I did -

1. Separated DATETIME TO get DATE column to be used as grouping variable.
2. TABLE has to be grouped using findgroups using DATE as grouping variable.G=findgroups(X.DATE)
3. Then how to splitapply as I am getting error :Error: Unbalanced or unexpected parenthesis or bracket. Y=splitapply(@M{M},X.DATE,G)
4. Since i dont have any function to apply at moment so what to write in @M{M}?
5. After grouping I want rows and 4 columns for each DATE to become a matrix on which a function has to be run.

P.S.- I am attaching my DATA FILE for testing.

Answer 1

[dpb]

Presuming this is in a timetable from the timeseries in Trading TB, retime is one way to apply a function.

Alternatively, findgroups and splitapply for table object or findgroups and process each group via loop or arrayfun if loose data in array.

ADDENDUM

Try timetable instead...

> T=table2timetable(readtable('MATLABTEST.xlsx'));   % read, convert to timetable
>> mn=retime(T,'hourly',@mean);                    % example calculation
>> ix=all(isnan(mn{:,:}),2);                         % many interpolated values
>> mn=mn(~ix,:)                                      % remove infilled values

The sample data has many hours with no data and retime will infill those; easy as any is to just go ahead and then remove those from end result. That may not be an issue with the full file I suspect...

mn=mn(~ix,:)
mn =
2×4 timetable
           DATETIME               A1        A2        A3        A4  
  __________________________    ______    ______    ______    ______
  12/31/2009 12:00:00.000 AM    1094.2    1093.7    1093.8    107.16
  1/1/2010 12:00:00.000 AM      1095.5    1094.9    1095.1    107.23
>>

COMMENT It's actually 'daily' that was wanted, so the infill problem goes away for it...

ADDENDUM SECOND

Well, some of the new stuff is quite handy but, there are some other ways to skin the cat with earlier stuff that came along with the datetime class...in particular, one I tend to forget with retime is dateshift; it'll do the job quite neatly...

T=readtable('MATLABTEST.xlsx');
Date=dateshift(T.DATETIME,'start','day');
Date.Format='MM/dd/yyyy';
T.Date=Date
[g,ig]=findgroups(T.Date);

Now use splitapply with g as grouping variable.

[NS]

I HAVE ADDED MY DATA SAMPLE AND ELABORATED THE PROBLEM ABOVE .

I am trying to do the same but not getting results. Basically , I have a TIMESTAMPED table of data with 1 minute frequency and i need to convert each day data into a matrix form to run a function on all days.

What should I do?

[dpb]

Show us, don't tell...we can't see your terminal from here so can only guess what really is...attach a (smallish) .mat file with sample data would be one way so somebody can do something specifically w/o trying to create a dataset, just

whos YourVariableNameA YourVariableNameB ...

and post the result would be informative.

[NS]

Sir I have attached my sample data in excel file above.

[dpb]

Y=splitapply(@M{M},X.DATE,G)

has "{}" which is syntax to dereference a cell array instead of "()" for the argument of an anonymous function; hence the "unexpected" error.

You provide the handle to the function you want to run on the data either as an m-file or via an anonymous function.

You do NOT need to aggregate the data manually to apply the function over each group; that's what splitapply does for you.

[NS]

STILL GETTING THE SAME ERROR

 >> P=splitapply(@x(x),G4.DATE,G)
 P=splitapply(@x(x),G4.DATE,G)
                ↑
  Error: Unbalanced or unexpected parenthesis or bracket.

[NS]

How to split minute data for each DATE?

[NS]

The problem is with parenthesis not brackets i guess. I am not able to understand how to split above given dataset for each DATE.

[dpb]

Oh, yeah. splitapply is funny that way, it only accepts a function handle, not the anonymous function. Create a variable to hold the function--

fnX=@(x) x;
P=splitapply(fnX,G4.DATE,G);

Actually, for that retime with a timeseries is probably simpler. With the file (that hadn't noticed before, sorry), see amended Answer.

[NS]

Sir can you please give an example of how to use retime in this case as each day there might not be all minutes due to filtering of outlier. How will retime work in this case ?

[NS]

STILL GOT AN ERROR

>> fnX=@(x) x;
  >> P=splitapply(fnX,G4.DATE,G);
  Error using splitapply (line 133)
  The function '@(x)x' returned a non-scalar value when applied to the 1st group of data.

    To compute nonscalar values for each group, create an anonymous function to return each value in a scalar cell:

      @(x){x}

[NS]

AFTER CORRECTING FOR @x{x} in fnX I still get an error as it gives me a cell array with DATESX1 in first cell and when i change the code to

fnX=@(x){x};
P=splitapply(fnX,G4,G);
Error using splitapply (line 133)
Applying the function '@(x){x}' to the 1st group of data generated the following error:

Too many input arguments.

[NS]

Sir Since each DATE has 700 rows for minute and 4 as columns as a single cell array. How to make it.

[NS]

AFTER RUNNING THIS TOO ERROR

P5 is 1658205x5 table with first column as DATETIME of format 'dd-MM-yyyy HH:mm:ss'. Why is it not converting to timetable?

T=table2timetable(P5)
Undefined function or variable 'table2timetable'.

even after doing this

T=table2timetable(readtable(P5));
Error using readtable (line 143)
Input must be a row vector of characters.

[dpb]

table2timetable and the timetable class were introduced in R2016b. Which release are you using?

[NS]

R2016a and I dont have access to R2016b

[dpb]

Well, bummer! But, all is not lost... :)

See ADDENDA 2

[NS]

Thank you so much sir . It worked :)