Transforming data table from wide to long format

Hello,

I have some data in wide format (see attached wide_format.xls) and I am trying to convert it to long format (see example long_format.xls). Anyone knows a simple way to do it?

Thanks in advance.

-Mikel

Answer 1

[Peter Perkins]

stack to the rescue, twice, because you are stacking two pairs of variables all into one variable:

>> tw = readtable("wide_format.xls")
tw =
  17×4 table
      grb1        grb2        ugb1        ugb2  
    ________    ________    ________    ________
     0.33167    0.046192     0.26909     0.28662
     0.15223     0.19548     0.74902     0.80082
     0.34801     0.72017     0.50389     0.89611
     0.12166     0.72175     0.64681     0.59753
     0.88415      0.8778     0.30775     0.88402
    0.094278     0.58243     0.13872     0.94373
     0.93004    0.070684     0.47557     0.54916
     0.39902     0.92274     0.36246     0.72839
    0.047401     0.80037     0.78811     0.57676
     0.34237     0.28595      0.7803    0.025857
     0.73597     0.54366     0.66851     0.44653
     0.79468     0.98478      0.1335      0.6463
     0.54491     0.71568    0.021556      0.5212
     0.68622     0.83897     0.55984     0.37231
     0.89363     0.43326     0.30082     0.93713
    0.054792     0.47062     0.93941     0.82953
     0.30366     0.56071      0.9809     0.84909

>> tw.ID = (1:height(tw))';
>> t1 = stack(tw,{["ugb1" "ugb2"] ["grb1" "grb2"]}, ...
    "NewDataVariableName",["Ungrouped" "Grouped"], "IndexVariableName","Beta");
>> t1.Beta = categorical(t1.Beta,3:4,["B1" "B2"])
t1 =
  34×4 table
    ID    Beta    Ungrouped    Grouped 
    __    ____    _________    ________
     1     B1      0.26909      0.33167
     1     B2      0.28662     0.046192
     2     B1      0.74902      0.15223
     2     B2      0.80082      0.19548
     3     B1      0.50389      0.34801
     3     B2      0.89611      0.72017
     4     B1      0.64681      0.12166
     4     B2      0.59753      0.72175
[snip]

>> t2 = stack(t1,["Ungrouped" "Grouped"], ...
    "NewDataVariableName","Value", "IndexVariableName","Crowding");
>> t2 = sortrows(t2,["Crowding" "Beta"])
t2 =
  68×4 table
    ID    Beta    Crowding      Value  
    __    ____    _________    ________
     1     B1     Ungrouped     0.26909
     2     B1     Ungrouped     0.74902
     3     B1     Ungrouped     0.50389
     4     B1     Ungrouped     0.64681
     5     B1     Ungrouped     0.30775
     6     B1     Ungrouped     0.13872
     7     B1     Ungrouped     0.47557
     8     B1     Ungrouped     0.36246
[snip]

Answer 2

[Mikel Jimenez]

Dear Peter,

Thank you so much, the code works perfectly!!

Best,

Mikel