How to extract sequences from a table?

Hi All,

I am adapting the example by Mathworks, Sequence Classification using Deep Learning,, to work with my own problem.

I have the raw data coming in as a table, and I'm wondering is there a way to split this up into sequences, based on the values in one column, rather than an arbitrary number of rows down?

My table looks like this:

What I would like to do is to convert this to a cellarray, of various double arrays (from this picture above it will be x cells each with 2*y double arrays, however, the real table has a few more columns, so it's just an extension of this).

Many thanks,

James

[Dyuman Joshi]

So you want to convert the table to a cell array where each cell element is data corresponding to a certain date?

[James McBrearty]

That's it exactly. So, each date will have a double array assigned to it.

[the cyclist]

Can you upload the data, or a small representative sample? You can use the paper clip icon in the INSERT section of the toolbar.

[James McBrearty]

So, FCTable is attached.

What I would like is a cell array, where each cell element corresponds to a specific date (I don't need the date), and the contents of each cell would be the double array for each date.

Thanks

Answer 1

[Matt J]

Calling your table T,

out = splitapply(@(x){x} , T{:,[1:10,12:end]},findgroups(T.tradeDate))

[James McBrearty]

Hi Matt,

This seems to be close, however, there is an issue with the grouping of the tradeDate, somehow it thinks that the tradeDates are all unique, even though they have the same value. So it is returning a cell array, but only with 1*12 double arrays, not x*12 double arrays.

The raw table is attached:

Thanks,

James

[Matt J]

load FCTable

G=findgroups( dateshift(FCTable.tradeDate,'start','day')    );

out = splitapply(@(x){x} , FCTable{:,2:end}  ,  G)

out = 51×1 cell array
    {46×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}
    {48×2 double}

[James McBrearty]

That works like a treat! Thank you very much! Was there something wrong with the format of the dates?

[Matt J]

The dates in every row of the raw table are indeed distinct from one another. They need to be rounded down to the nearest day.

[Dyuman Joshi]

"Was there something wrong with the format of the dates?"

The format hid additional information of the datetime values.

The dates might look identical, but the values for hour, minute and seconds were different for the same day. You can check that by using hms.

So, to make the data uniform, all the dates have been shifted to the start of the respective days i.e. 00:00:00

[James McBrearty]

Thanks for this - I did convert the dateformat to 'dd/mm/uuuu', and I was hoping that would change them all to be uniform. At least now I know how to use dateshift in the future. Again, thanks so much.

Answer 2

[Matt J]

Another option, using varfun:

load FCTable

T=FCTable;
T.tradeDate=dateshift(FCTable.tradeDate,'start','day') ;

out = varfun(@(x) x,  T,'Grouping', 'tradeDate','Output','cell')

out = 51×2 cell array
    {46×1 double}    {46×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}
    {48×1 double}    {48×1 double}

[James McBrearty]

That is a great answer too, but the other one is preferred, having a X times 1 cell array.

Thanks