Append columns from a known variable into cell

Hi!

I can't seem to find the solution to this problem.

In my program I'm extracting data from multiple e.g. 6 excel files (through readcell), all with the same structure: column 1 with the quantity, column 2 with the value and column 3 with the unit of measurement, obtaining a {28,3} cell.

I then extract in a for loop the second column, and create a matrix of all the numeric values adding column after column for the different examines files.

What i'd like to do now is to create a cell with:

• column 1 from the first cell with the quantity names;
• column 2:(6+1) with the different values of the quantity across the 6 files
• column 8 with the units of measurement;

Here is the script that I tried:

clear all

dataset=uigetfile('*.xlsx','Multiselect','on');

for i=1:length(dataset)
    tri=readcell((dataset{1,i}));
    estrai(:,i)=cell2mat(tri(:,2));
end

output=[tri(:,1),estrai];

xlswrite('Analysis',output)

If I run the script i get the following error:

Dimensions of arrays being concatenated are not consistent. Consider converting input arrays to the same type before concatenating.

I also tried writing output as output={tri(:,1),estrai} but instead of a 28x7 cell it returns a 1x2 cell, in which element {1,1} is a 28x1 cell and {1,2} is a 28x6 double. xlswrite('Analysis',output) in this case returns an empty excel file.

Any help?

Answer 1

[Simon Silge]

Have you tested this? You are trying to join a cell array and a numerical matrix from what it seems.

output = [cell2mat(tri(:,1)),estrai] 

[Andrea Moro]

Yes I tried but it doesn't work: since the dimensions of the element of tri(:,1) isn't constant, it can't execute the conversion cell2mat.

>> cell2mat(tri(:,1))

Error using cat

Dimensions of arrays being concatenated are not consistent.

Error in cell2mat

m{n} = cat(1,c{:,n});

[Simon Silge]

What do you mean by "the dimension is not constant"?

Does you cell array tri(:,1) contain actual numbers (type double) or text in character/string format or a mix maybe? That could be a problem here.

Apart from that, in the end you want a cell array anyway right? So you could just leave out the conversion to a matrix :

clear all
dataset=uigetfile('*.xlsx','Multiselect','on');
estrai = cell(28,6) % preallocate memory if you know the size of your output

for i=1:length(dataset)
    tri=readcell((dataset{1,i}));
    estrai(:,i)=tri(:,2);
end
output=[tri(:,1),estrai];
writecell(output,'Analysis.xls') %changed to writecell

If you want to do calculations afterwards you still have to make sure that your numbers in the cell array are actually numbers and not in a text format.

Edit: Also you should try to use newer writecell instead of xlswrite now (fixed it in the code above also)

Info to writecell: https://de.mathworks.com/help/matlab/ref/writecell.html

[Andrea Moro]

Solution verified!

Yes. tri(:,1) contained text.

My script didn't work because I was trying to append a double array to a cell array, executed by Matlab creating a new cell with the two individual elements instead of appending the content of the two elements. Your script instead executes the operation between two cell arrays.

Did I understand correctly?

[Simon Silge]

Thanks for marking the answer as correct.

Correct, In the code you posted you tried to concatenate/ join a cell array and a numerical matrix, which does not work by using [ ] . Instead you should first make sure both variables have the same format and then use [ ]

When you use { } what you do is create a cell array that incorporates the variables (you pass) into new cells. So creating two cells (one for each of your variables) and putting your variables inside, which is not what you want here.

If you have to work with data that contains both text and numbers you have to stay with an cell array (or a table could also work maybe). Just keep in mind that if you want to do calculations you have to extract the numerical values with cell2mat. And you can put them back in:

E.g.

A = {1, 'A'; 2,'B'}
% extract numerical matrix
col1 = cell2mat(A(:,1))
% do calculation
col1 = col1 * 2 

% put back into cell if needed
A(:,1) = num2cell(col1)