convert multidimensional array to a table with multi indexing

I have a multidimensional matrix say M such that

M=rand(5,4,3,2)

where the 5 rows stand for say last names of customers and the 4 columns stand for their {age, weight, activities, happiness level}, while the other 3 multi-matrix stands for years {1999,2003,2009}, while 2 stand for countries {A, and B}

How could i transfer this matrix to be a table with multi-index?

Thank you very much

Answer 1

[Sebastian Castro]

The array2table function wants your matrix to be 2-dimensional (or m-by-n, as they say in the documentation).

One thing you could try is use reshape as follows:

M = rand(5,4,3,2);
M2 = reshape(M,[5, 4*3*2])

This will give you a 5-by-24 matrix, but you lose all the multi-indexing capabilities. Each column would have to be given a name that indicates which year and country you want.

Another approach is to keep some of the nesting. See below. The squeeze function is used such that each table element is 3-D instead of 4-D:

T = table;
T.Age = squeeze(M(:,1,:,:));
T.Weight = squeeze(M(:,2,:,:));
T.Activities = squeeze(M(:,3,:,:));
T.Happiness = squeeze(M(:,4,:,:));

So in this case if you wanted customer 4's age in 2003, country A, you'd say:

thisGuysAge = T.Age(4,2,1);

You could get real fancy with this, e.g.:

years = [1993 2003 2009];
countries = {'A','B'};
thisGuysAge = T.Age(4 , years==2003 , strcmp(countries,'A'))

... and so on. Ultimately it's a trade-off between having a 2-D table where you have to figure out how to distinguish between years and ages vs. keeping some of the multidimensionality and having the ability to index in.

Sebastian

[Peter Perkins]

It's also possible to take this one step further and make each variable itself a table.

>> years = {'y1999' 'y2003' 'y2009'};
>> T.Age = table(squeeze(T.Age(:,1,:)),squeeze(T.Age(:,2,:)),squeeze(T.Age(:,3,:)), ...
    'VariableNames',years);
>> T.Weight = table(squeeze(T.Weight(:,1,:)),squeeze(T.Weight(:,2,:)),squeeze(T.Weight(:,3,:)), ...
    'VariableNames',years);
>> T.Activities = table(squeeze(T.Activities(:,1,:)),squeeze(T.Activities(:,2,:)),squeeze(T.Activities(:,3,:)), ...
    'VariableNames',years);
>> T.Happiness = table(squeeze(T.Happiness(:,1,:)),squeeze(T.Happiness(:,2,:)),squeeze(T.Happiness(:,3,:)), ...
    'VariableNames',years);
>> T
T =
  5×4 table
        Age          Weight       Activities      Happiness 
    ___________    ___________    ___________    ___________
    [1x3 table]    [1x3 table]    [1x3 table]    [1x3 table]
    [1x3 table]    [1x3 table]    [1x3 table]    [1x3 table]
    [1x3 table]    [1x3 table]    [1x3 table]    [1x3 table]
    [1x3 table]    [1x3 table]    [1x3 table]    [1x3 table]
    [1x3 table]    [1x3 table]    [1x3 table]    [1x3 table]

The display for that is not great, but the subscripting works as you might hope to give you named indexing one level deeper:

>> T.Age
ans =
  5×3 table
          y1999                 y2003                  y2009       
    __________________    __________________    ___________________
    0.02193    0.86234     0.7663    0.74618    0.47524     0.85337
    0.80828     0.8964     0.7513    0.11749    0.80532     0.39812
    0.17921    0.18901    0.13886    0.50902    0.53078     0.11549
    0.16539    0.66072    0.34932    0.16883    0.22731    0.080281
     0.1816    0.94123    0.15134    0.83111    0.70948     0.36047
>> T.Age.y1999
ans =
      0.02193      0.86234
      0.80828       0.8964
      0.17921      0.18901
      0.16539      0.66072
       0.1816      0.94123
>> T.Age.y1999(1,1)
ans =
      0.02193

And then of course you can make T.Age.y1999, etc., themselves each a table. You'd have three levels of tables, and at each you'd subscript by measurement, year, and country, respectively.

The code above gets pretty tedious, but I've typed it out like that only to make it more understandable. You could easily write loops to do it all.

Answer 2

[Peter Perkins]

Another possibility is to flatten out the data. You may have the N-D organization on purpose. but here's one way to flatten the data out.

>> M = reshape(1:120,[5 4 3 2]);
>> T = array2table(M(:,:));
>> T = stack(T,{1:4:24 2:4:24 3:4:24 4:4:24}, ...
'IndexVariableName','Subject', ...
'NewDataVariableName',{'Age' 'Weight' 'Activities' 'Happiness'});
>> T.Subject = categorical(repelem({'Subject1'; 'Subject2'; 'Subject3'; 'Subject4'; 'Subject5'},6));
>> T.Year = categorical(repmat(repmat({'y1999'; 'y2003'; 'y2009'},2,1),5,1));
>> T.Country = categorical(repmat(repelem({'CountryA'; 'CountryB'},3),5,1));
>> T = T(:,[1 6 7 2:5])
T =
  30×7 table
    Subject     Year     Country     Age    Weight    Activities    Happiness
    ________    _____    ________    ___    ______    __________    _________
    Subject1    y1999    CountryA      1      6        11            16      
    Subject1    y2003    CountryA     21     26        31            36      
    Subject1    y2009    CountryA     41     46        51            56      
    Subject1    y1999    CountryB     61     66        71            76      
    Subject1    y2003    CountryB     81     86        91            96      
    Subject1    y2009    CountryB    101    106       111           116      
    Subject2    y1999    CountryA      2      7        12            17      
    Subject2    y2003    CountryA     22     27        32            37      
    Subject2    y2009    CountryA     42     47        52            57      
    Subject2    y1999    CountryB     62     67        72            77      
    Subject2    y2003    CountryB     82     87        92            97      
    Subject2    y2009    CountryB    102    107       112           117      
    Subject3    y1999    CountryA      3      8        13            18      
    Subject3    y2003    CountryA     23     28        33            38      
    Subject3    y2009    CountryA     43     48        53            58      
    Subject3    y1999    CountryB     63     68        73            78      
    Subject3    y2003    CountryB     83     88        93            98      
    Subject3    y2009    CountryB    103    108       113           118      
    Subject4    y1999    CountryA      4      9        14            19      
    Subject4    y2003    CountryA     24     29        34            39      
    Subject4    y2009    CountryA     44     49        54            59      
    Subject4    y1999    CountryB     64     69        74            79      
    Subject4    y2003    CountryB     84     89        94            99      
    Subject4    y2009    CountryB    104    109       114           119      
    Subject5    y1999    CountryA      5     10        15            20      
    Subject5    y2003    CountryA     25     30        35            40      
    Subject5    y2009    CountryA     45     50        55            60      
    Subject5    y1999    CountryB     65     70        75            80      
    Subject5    y2003    CountryB     85     90        95           100      
    Subject5    y2009    CountryB    105    110       115           120