Transforming data table from wide to long format

79 views (last 30 days)
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

Accepted Answer

Peter Perkins
Peter Perkins on 28 Jul 2021
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]

More Answers (1)

Mikel  Jimenez
Mikel Jimenez on 28 Jul 2021
Dear Peter,
Thank you so much, the code works perfectly!!
Best,
Mikel

Community Treasure Hunt

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

Start Hunting!