MATLAB Answers

NS
0

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

Asked by NS
on 12 Oct 2018
Latest activity Edited by dpb
on 14 Oct 2018
Accepted Answer by dpb
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.

  0 Comments

Sign in to comment.

1 Answer

Answer by dpb
on 13 Oct 2018
Edited by dpb
on 14 Oct 2018
 Accepted Answer

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.

  17 Comments

R2016a and I dont have access to R2016b
Well, bummer! But, all is not lost... :)
See ADDENDA 2
Thank you so much sir . It worked :)

Sign in to comment.