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

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.

[Guillaume]

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]

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.

Answer 1

[Peter Perkins]

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

[QuanCCC]

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

Answer 2

[Pamudu Ranasinghe]

The easiest way is to first convert the table into a matrix form and then reshape it by using the "reshape" function in Matlab.Then convert back to table and and the variable names

t = table([1,2,3],[4,4,5])

t = 1×2 table
       Var1           Var2    
    ___________    ___________

    1    2    3    4    4    5

matrix = t{:,:};% t-- your table variable
reshape_matrix = reshape(matrix ,[2,3]) % [2,3]--> the size of the matrix you desire

reshape_matrix = 2×3

1 3 4 2 4 5

These two steps can be done by one line of code

reshape_matrix = reshape(t{:,:},[2,3]);