Add headers to matrix using table

***** Full code in the end *****csv.files attached*******

Hello community,

I am using Matlab R2020b.

I've bumped into an issue here, could use some help:

I've got a matrix of test data, and want to create a reuseable code to export it.

Since I don't know how many tests I will do next time, I decide to use a loop to genereate headers.

With this loop, I get the a string array as my "headers". Header is a 1x4 string. I also tried to generate a cell structure - tem (1x4 cell containing string)

% Generate headers
Header = ["Time"]
for filei = 1 : numberoffiles
Header(filei+1) = strcat("Test",num2str(filei))
end
tem = num2cell(Header)

Then I want to use the table function to add the header row, using "VariableNames" argument.

% add headers to the matrix
output  = table (datapool,'VariableNames',Header)

I got rejected. Error message:

The VariableNames property must contain one name for each variable in the table.

In this instance, it is a 1x4 string array against a 200000x4 double matrix, the numbers should match. I don't understand why not.

********************************

I also tried to throw a cell arry to it - doesn't work either.

% add headers to the matrix
output  = table (datapool,'VariableNames',tem)

Error message: The VariableNames property is a cell arry of character vectors. To assgin multiple variable names, specify nonempty names in a string array or a cell array of character vectors.

I know I did not sumbit the right data - the cells contain strings instead of characters. However, I don't know how to change the generator to generate character arrays.

Can you please share some insights?

Full code: files attached.

% Use pop box to select data.
filename = uigetfile(".csv","MultiSelect","on") % note filename is a cell struture, you cannot use dot reference for this structure.
% to retrieve the data in the cell struture, you need to use {} brackets.
% The result is the content of the indexed cell.

% see how many files are in this folder
numberoffiles=length(filename)

% import the files into matrices
datapool = nan(200000,numberoffiles) % initialize a matrice to store data
for filei = 1:numberoffiles 
        t = table2array(readtable (filename{filei},"NumHeaderLines",11))
        
        %Clean data: remove entries less than 0.01.
        validindex = find(t>=0.01);
        t = t(validindex)
        
        T = [t;nan(200000-height(t),1)] % fill emptycells with NaNs to fit datapool.     
        datapool (:,filei) =T  % put data in the target location.
end

%create time stamp for the entries
t = transpose([1:200000])
t = (t-1) * 0.0005
datapool = [t,datapool]

%plot the dataset
plot(datapool(:,1),datapool(:,2),datapool(:,1),datapool(:,3),datapool(:,1),datapool(:,4))

% add headers to the matrix ( this method converts everything to a string
% table.)
% Header = ["Time"]
% for filei = 1 : numberoffiles
% Header(filei+1) = strcat('Test',num2str(filei))
% end
% 
% output = [Header;num2cell(datapool)]
% this makes a file of strings, even the numbers are strings. 

% Generate headers
Header = ["Time"]
for filei = 1 : numberoffiles
Header(filei+1) = strcat("Test",num2str(filei))
end
tem = num2cell(Header)

% add headers to the matrix
output  = table (datapool,'VariableNames',Header)

Answer 1

[dpb]

Use new string class features here --

nFiles=4;
Header=["Time", "Test "+string(1:nFiles)];

returns a string array as

>> Header
Header = 
  1×5 string array
    "Time"    "Test 1"    "Test 2"    "Test 3"    "Test 4"
>> 

Then later on with

% add headers to the matrix
output  = table (datapool,'VariableNames',Header)

The VariableNames property must contain one name for each variable in the table.

"In this instance, it is a 1x4 string array against a 200000x4 double matrix, the numbers should match."

It doesn't match because your use of table tells it to make a table of the array datapool as a single variable which is an Nx4 array, not four separate variables, one for each column.

What you want here is array2table instead --

tDP=array2table(datapool,'VariableNames',Header);

excepting if nFiles=4 as set above to create the header, you'll still get the same error because you've got the Nx4 array but five elements in the Header array. You need the time column as well. If it is one of the columns in the array, then nFiles will have to be 3 in this case, not four.

[Akira Agata]

+1

I would recommend not to use white space in variable names of a table variable.

The following would be one possible solution:

% Select CSV files
filename = uigetfile(".csv","MultiSelect","on");

% Read data from files
data = cell(numel(filename),1);
for kk = 1:numel(filename)
  data{kk} = readmatrix(filename{kk},'NumHeaderLines',11);
end

% Arrange data as table variable
n1 = max(cellfun(@numel, data));
tData = nan(n1,numel(data));
tData = array2table(tData,'VariableNames',compose('Test%d',1:numel(data)));
for kk = 1:numel(filename)
  tData{1:numel(data{kk}),kk} = data{kk};
end

% Add time column and move it to 1st column
dt = 0.0005;
tData.Time = (0:dt:dt*(n1-1))';
tData = movevars(tData,'Time','Before',1);

[Charlie Chen]

Hi @dpbdbp,

Great answer! Thanks for helping me out. The string class feature opened my eyes. I am new to matlab, and I am impressed by how conscise a loop can be here. With this improvement, the codes are much clearer.

As for the trouble shoot, seems like I still have a lot of work to do about the classes. Do you have any reading recommendations about the classes, such as table, cell, arrays? I am a bit lost here.

Thanks again for the help.

@Akira Agata,

Cool code. I am spending a lot of time to wrap my head around it. You helped me to optimize my codes a lot. 3 questions here.

1. for this loop, how did the cell structure work?

for kk = 1:numel(filename)
  tData{1:numel(data{kk}),kk} = data{kk}; % read the content of data to each column.
end

I am seeing the content of the right side transfered to the content of the left side?

also, the content of "data" is a cell contenting an array, and it was transfered to sepeate cells in "tData". I am lost.

2. for this statement,

n1 = max(cellfun(@numel, data));

why should we put an "@" to the function name? I tried to delete the sign and it doesn't work.

3. Generally, I see that you read the file into an array into cells, then transfer the data to cells individually, and work with the table. Can you read the data into the table directly using readtable function?

Best regards,

Charlie

[dpb]

1. Yes; Akira's code first preallocates an array of NaN of the size of the maximum length of the input files in case they're not all the same. This loop just fills the non-empty rows of each column with the array of the specific file--since it's writing into a pre-existing array, must use explicit addressing -- writing just tData(:,kk)=... would require there to be the same number of rows in each file; writing tData=... would not error syntactically, but would overwrite the entire array variable with the content of each in turn, leaving only the last as a one-column variable. It's always good to write general code but if you have a specific case where you know all the files are the same length, then you could shortcut the operation and remove a little of the logic. I didn't look at the files, but if there is only one time column for all, I'd presume then they do all match up; otherwise it would seem you would need to have a separate timestamp for each.

2. The '@' is the syntax to tell MATLAB interpreter it is a function handle, not to try to execute the function. The function to execute on the cell content is what cellfun expects as the first argument.

3. I didn't download the file to look at, but almost certainly can use readtable directly and is also almost certainly what I would have done -- and then use table2timetable instead of array2timetable if you didn't already have the array in the code to pick up on. Unless the file format is really funky readtable perhaps with some help from an import options object can almost always manage to bring it in first shot as is desired. This can include cleaning up variable names, conversion to desired data types, handling missing values, etc., etc, etc., ... all at once. It may not be time effective for only a one-shot operation, but if going to process multiple files of the same type, it is generally well worth some upfront time.

[Charlie Chen]

Thank you dpb, much appreciated!

[dpb]

"I would recommend not to use white space in variable names of a table variable."

That's easily dealt with

Header=["Time", "Test"+string(1:nFiles)];

just remove the space from "Test" string. If one is building the variable names programmatically, it really is of little consequence; if typing by hand it's a little more work to quote them, granted, and I generally don't use embedded spaces in variable names (or file names, either, for that matter) for that reason.

One can sorta' get both the visual effect and avoid needing to quote them by the expedient of the underscore, of course--

Header=["Time", "Test_"+string(1:nFiles)];

[Samuel Katongole]

In studying this problem, I have tried and gone through the code; and also tried out the suggested

tDP=array2table(datapool,'VariableNames',Header);

But it has yielded me the same error...I have observed the the following info should be paid attention to:

"The VariableNames property must be a cell array, with each element containing one nonempty character vector." found in the table VariableNames documentation. So the problem is solved by being able to convert the string array, Header, to a cell array of character vectors. This is achieved using the cellstr function. I have tried the following code and got the result shown below (Hope it is this that the questioner sought after)

for filei=1:nFiles
    %update Header as nFiles changes/increases
    Header=["Time", "Test"+string(1:nFiles)];
    output=array2table(datapool);
    output.Properties.VariableNames=cellstr(Header);
end

For the first ten rows of the output table....

Answer 2

[Samuel Katongole]

In studying this problem, I have tried and gone through the code; and also tried out the suggested

tDP=array2table(datapool,'VariableNames',Header);

But it has yielded me the same error...I have observed the the following info should be paid attention to:

"The VariableNames property must be a cell array, with each element containing one nonempty character vector." found in the table VariableNames documentation. So the problem is solved by being able to convert the string array, Header, to a cell array of character vectors. This is achieved using the cellstr function. I have tried the following code and got the result shown below (Hope it is this that the questioner sought after)

for filei=1:nFiles
    %update Header as nFiles changes/increases
    Header=["Time", "Test"+string(1:nFiles)];
    output=array2table(datapool);
    output.Properties.VariableNames=cellstr(Header);
end

For the first ten rows of the output table....

[Peter Perkins]

Samuel, you must be using a version of MATLAB from around 2016 (or something funny is going on). From sometime around 2018, the variable names need not be a cellstr.

Also a minor suggestion:do the creation and naming all on one line.

output=array2table(datapool,'VariableNames',Header)