MATLAB Answers

Filter a timetable (balance a panel data)

7 views (last 30 days)
Angelavtc
Angelavtc on 22 Feb 2021
Commented: Angelavtc on 25 Feb 2021
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!

Accepted Answer

Lei Hou
Lei Hou on 24 Feb 2021
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

More Answers (0)

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by