Filter a timetable (balance a panel data)

6 ビュー (過去 30 日間)
Angelavtc
Angelavtc 2021 年 2 月 22 日
コメント済み: Angelavtc 2021 年 2 月 25 日
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!

採用された回答

Lei Hou
Lei Hou 2021 年 2 月 24 日
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
  1 件のコメント
Angelavtc
Angelavtc 2021 年 2 月 25 日
Thank you very much @Lei Hou!

サインインしてコメントする。

その他の回答 (0 件)

カテゴリ

Help Center および File ExchangeData Preprocessing についてさらに検索

Community Treasure Hunt

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

Start Hunting!

Translated by