How to write/read a table to/from a CSV that has multiple values per column?

If I create a table with multiple values per column like so:

x = [1,2,3];
y = 4;
T = table(x,y)

outputs

T = 
  1x2 table

           x       y
      ___________  _
      1    2    3  4

such that T.x stores three values, 1 2 and 3. If I use writetable and readtable to save/load this table into a CSV file, like so:

writetable(T,"table.csv");
T2 = readtable("table.csv")

outputs

T2 = 
  1x4 table

      x_1  x_2  x_3  y
      ___  ___  ___  _
      1    2    3    4

How can I read in this table such that x_1, x_2, and x_3 are grouped under one header, like in T?

Answer 1

[Stephan]

Hi,

the first part is known from your question, just added a row to see if it works with more than one column:

x = [1,2,3;6 7 8];
y = [4; 9];
T = table(x,y);

writetable(T,"table.csv");
T2 = readtable("table.csv");

To import the table that style you wish without mergevars you could do so:

% Define new Table
T3 = table();

% Loop for collect values from T2 and split them...
% at the right position
for k = 1: height(T2)
    % Get a double-array from every line in T2
    t2 = T2{k,1:end};
    % Build first column of the table with values 1:3
    T3{k,1} = t2(1:3);
    % Build second column with y-Value
    T3{k,2} = t2(4);
end

% Set Names for the variables
T3.Properties.VariableNames = {'x','y'};

After proceeding this code you get:

>> T3

T3 =

    2×2 table

           x         y
      ___________    _

      1    2    3    4
      6    7    8    9

I think this should work like you wanted.

Best regards

Stephan

Answer 2

[Stephan]

Hi,

after importing the data in the format you dont want, you could use the mergevars function to change the table like you expect it:

T=mergevars(T,[1 2 3], 'NewVariableName','x')

Best regards

Stephan

[Jay Franklin]

This is the kind of thing I am looking for, but unfortunately mergevars doesn't exist in r2017b (it was added r2018a). That being said, I'm sure its possible to string together some older functions to accomplish this, although I haven't been able to get it working yet.