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 at 17:59
Latest activity Edited by dpb
about 18 hours ago
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 at 12:25
Edited by dpb
about 18 hours ago
 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.