Matlab's writetable() function only exporting partial data of a table

The writetable() function at the end of my code only exports the first row (namely FR_1w, FR_2w and FR_3w),

whereas I want the entire table to be exported and written as .xls or .xlsx.

V=[{A B C};...
    {A1 B1 C1};...
    {A2 B2 C2}];
X=cell2table(V);
X.Properties.VariableNames= {'FR_1w' 'FR_2w' 'FR_3w'};
X.Properties.RowNames= {'4Weeks' '12Weeks' '24Weeks'};
writetable(X, 'X.xlsx')

n.b. Variables in table V are 3x1 cells.

[Walter Roberson]

My understand is that xls and xlsx format do not support multiple values per cell.

You might suggest that multiple consecutive cells should be used to store the values, but it is not clear what should be done for the non-cell portions, and not clear what should be done if the cells are different sizes between the variables in one row, or different sizes between the different rows. Even if all of the variables in a row are the same size and all of the rows use that same size, then should the row names be replicated, or should the row name be put only on the first of them?

[John]

ideally the row names should only be put on the first row! I managed to get a workaround solution that, instead of exporting a 3x3 table, exports a 9x3 table where I had to rename 9 instead of 3 rows. Surely there must be a more elegant way of doing this.

Answer 1

[Walter Roberson]

https://www.mathworks.com/help/matlab/ref/writetable.html#bty9jzg-5

"Tips:

For variables with a cell data type, writetable outputs the contents of each cell as a single row, in multiple fields. If the contents are other than numeric, logical, character, or categorical, then writetable outputs a single empty field."

For text file output, that agrees with my experiments:

V = [{[10;11] [20;21] [30;31]};{[40;41] [50;51] [60;61]};{[70;71] [80;81] [90;91]}];
X=cell2table(V);
X.Properties.VariableNames= {'FR_1w' 'FR_2w' 'FR_3w'};
X.Properties.RowNames= {'4Weeks' '12Weeks' '24Weeks'};
writetable(X,'/tmp/testwt.txt')

>> !cat /tmp/testwt.txt
FR_1w_1,FR_1w_2,FR_2w_1,FR_2w_2,FR_3w_1,FR_3w_2
10,11,20,21,30,31
40,41,50,51,60,61
70,71,80,81,90,91

Notice the new columns.

And when I write to .xlsx and readtable() it back in,

>> writetable(X,'/tmp/testwt.xlsx')
>> readtable('/tmp/testwt.xlsx')
ans =
  3×6 table array
    FR_1w_1    FR_1w_2    FR_2w_1    FR_2w_2    FR_3w_1    FR_3w_2
    _______    _______    _______    _______    _______    _______
    10         11         20         21         30         31     
    40         41         50         51         60         61     
    70         71         80         81         90         91

Which MATLAB version are you using?

[John]

thanks a lot! I am using the 2016b version

[Walter Roberson]

That was the version I tested with, so the answer is that the multiple rows inside the cell get turned into extra columns.

Answer 2

[Peter Perkins]

John, if Walter's example is indeed what you have, then you are trying to export something that is hierarchical to a file format that is not hierarchical. As Walter describes, writetable does its best to do that. It sounds like you're expecting those cells that contain matrices with multiple rows to be written out as multiple rows in the spreadsheet file. In the long run, I'm skeptical that will be helpful, because it will break the simple correspondence between rows in the table and rows in the file.