Filter a timetable (balance a panel data)

Hello,

I have a timetable and I want to keep those days that coincide between each value from my variable "Id". In the example I put here, I start with my timetable T where there are three different "Id". My goal would be to end up with a timetable T_1 where only the observations where the dates coincide between each "Id" are kept.

T= timetable(datetime({'13/04/2018';'25/04/2018';'28/04/2018';'13/04/2018';'25/04/2018';'13/04/2018'}), [1;1;1;2;2;3],[30;29;45;21;24;8] );
T.Properties.VariableNames = {'Id' 'Price'}
%I want to get:
T_1=timetable(datetime({'13/04/2018';'13/04/2018';'13/04/2018'}), [1;2;3],[30;21;8] );

I woudl be really thankful if you can help me!

Answer 1

[Lei Hou]

Hi Angelavtc,

You can use unstack to first unstack the "Id" variable into multiple variables (In your case, into 3 variables). The value of each variable is the value of "Price". If there is no "Price" value for that row time and Id combination, a NaN will be filled in. Then call rmmissing to remove rows containing missing value (such row is the row time that doesn't have "Price" value for all Id values). At last, call stack to stack the multiple Id variables into one variable of "Price".

>> tt1 = unstack(T,'Price','Id',"VariableNamingRule","preserve")

tt1 =

  3×3 timetable

       Time        1      2      3 
    ___________    __    ___    ___

    13-Apr-2018    30     21      8
    25-Apr-2018    29     24    NaN
    28-Apr-2018    45    NaN    NaN
>> tt2 = rmmissing(tt1)

tt2 =

  1×3 timetable

       Time        1     2     3
    ___________    __    __    _

    13-Apr-2018    30    21    8
>> T_1 = stack(tt2,[1 2 3],"IndexVariableName",'Id',"NewDataVariableName",'Price')

T_1 =

  3×2 timetable

       Time        Id    Price
    ___________    __    _____

    13-Apr-2018    1      30  
    13-Apr-2018    2      21  
    13-Apr-2018    3       8  

[Angelavtc]

Thank you very much @Lei Hou!