MATLAB Answers

Reshape table by rows, then merge horizontally and new var names

60 views (last 30 days)
QuanCCC
QuanCCC on 17 Sep 2018
Edited: QuanCCC on 24 Sep 2018
Hi
I have a long table dataset with 6 columns (DAY YEAR serial var1 var2 var3). Column DAY repeated values [1, 2, 3, ...,365, 1, ...,365,....]. Column YEAR has the same value for every 365 rows [2010 repeat365 times, 2011 repeat 365 times,....].
I want it to be divided into every 365 rows (have the same value in column B), merge the new subsets horizontally. Another problem is the reshaped new wide-table will have repeatedly variables names, how can I reshape the table and rename variables to (DAY var1_2010 var2_2010 var4_2010 var1_2011 var2_2011 var3_2011 var1_2012...)? Thank you.

  2 Comments

Guillaume
Guillaume on 17 Sep 2018
Why do you want to do that? That doesn't sound like a good idea (and is not going to be simple). Certainly once you've done that, calculating statistics on your table will be a nightmare.
QuanCCC
QuanCCC on 24 Sep 2018
Because I need to do an 'outerjoin' with another table. The issue I am facing is the dataset with too much dimensions. I guess I can do an unstack first.

Sign in to comment.

Accepted Answer

Peter Perkins
Peter Perkins on 19 Sep 2018
Unstack:
>> t = table([1;1;1;2;2;2;3;3;3;4;4;4],[1;2;3;1;2;3;1;2;3;1;2;3],[1;2;3;4;5;6;7;8;9;10;11;12])
t =
12×3 table
Var1 Var2 Var3
____ ____ ____
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
3 3 9
4 1 10
4 2 11
4 3 12
>> unstack(t,'Var3','Var2','GroupingVariable','Var1')
Warning: Table variable names were modified to make them valid MATLAB identifiers.
ans =
4×4 table
Var1 x1 x2 x3
____ __ __ __
1 1 2 3
2 4 5 6
3 7 8 9
4 10 11 12

  1 Comment

QuanCCC
QuanCCC on 24 Sep 2018
Yeah, I should do an unstack for each var, then do the algebraic operation for matrices. For people who has similar questions: 1) make a new table contains Days, Year, Var1; 2) do unstack for Var1. Var1 become a 2-dimension table; 3) repeat 1-2 for other vars. 4) algebraic operation for matrices as you want. e.g. TableVar1+TableVar2-TableVar3

Sign in to comment.

More Answers (0)

Sign in to answer this question.