Import excel sheet, multiply each column with a variable, and save the results as a new cell array

Hi everyone,

I am new to Matlab coding, and would be great if some one could help me out to solve my issue.

I have an excel sheet with multiple data columns; as an example, I have first set of data as 3 columns of 1, the second set of data as 3 columns of 2, and the third set of data as 3 columns of 3. The number of rows in each data set is not equal.

1 1 1 2 2 2 3 3 3
1 1 1 2 2 2 3 3 3
1 1 1 2 2 2 3 3 3
1 1 1       3 3 3
            3 3 3

I would like to multiply the first column in data set1 with 2, then multiply second column of data set1 by 3, and the third coumn of data set1 by 4. Then I would like to repeat this for dataset2 and dataset3.

The expected output;

2 3 4 4 6 8 6 9 12
2 3 4 4 6 8 6 9 12
2 3 4 4 6 8 6 9 12
2 3 4       6 9 12
            6 9 12

Then I would like to save this as a cell array;

where the first 3 columns (dataset1) is to be saved as cell1, and second 3 sets of columns to be saved as cell2, and last 3 columns to be saved as cell3.

in this format;

tracks = cell(3, 1);

Would be great if someone could help me with this! I am so new to excel imports and cell arrays...

Thanks a lot!

Sara

[SNT]

To add to my question, the columns are not limited to 9, as in my example. I have many more data sets in same excel sheet, and the multiplication must carry on until empty column is found.

Thanks again! Sara

Answer 1

[jonas]

The following solution is inefficient but works for R2016b and later. See Guillaume's answer for better solutions, also including one for earlier release

data=xlsread('filename.xlsx');
data2 = nan(size(data));
for i=1:size(data,2)/3
    data2(:,(i*3)-2:i*3)=data(:,(i*3)-2:i*3).*[2 3 4];
    tracks{i}=data2(:,(i*3)-2:i*3)
end

[SNT]

Hi Jonas,

Thanks for your reply.. But when I try this i get the following error

Error using  .* 
Matrix dimensions must agree.

I have put a screen shot of my excel sheet which I use as input data...

Thanks! Sara

[jonas]

What are the dimensions of your 'data' matrix?

[SNT]

Hi Jonas,

I couldn't attach the excel sheet here, so I have put in a screen shot of my excel sheet with the data. It has 3 sets of data (each containing 3 columns each as explained in my first message), with different number of rows in each set.

1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1  2  2  2  3  3  3
1  1  1        3  3  3
1  1  1        3  3  3
1  1  1            
1  1  1

Thanks a lot!

[SNT]

Also why is there a NaN in this step? It results in a 2D array full of NaN.

data2=data.*NaN;

Thanks!

[jonas]

That's ok, I have made an identical sheet and it works fine for me. What are the dimensions of your data-matrix, after importing?

Creating a set of NaNs before a loop is called preallocation. It makes the code more effecient as the variable data2 does not change size in every iteration.

[SNT]

Hi Jonas,

I have attached screen shots of both data and data2. The empty spaces are filled with NaN in data.

Thanks!

[jonas]

I cannot figure out why it's not working properly... I assume the problem is here:

data(:,(i*3)-2:i*3).*[2 3 4];

Try Andrei's solution, which is more efficient anyway. You can generalize it by altering the 3rd input in repmat to fit with the number of data sets.

[Guillaume]

data2 = nan(size(data));

would be a more efficient (and more obvious as to the intent) way of creating the matrix of nan.

[jonas]

Thanks, updated the answer. Any idea why it's not running properly for OP?

[SNT]

Hi Jonas,

I played around with the code a bit

data=xlsread('matlabTest3.xlsx');
data2=data.*NaN;
A = [2,3,4];
multiply = repmat(A, size(data,1),1)

for i=1:size(data,2)/3
    data2(:,(i*3)-2:i*3)=data(:,(i*3)-2:i*3).* multiply;
    tracks{i}=data2(:,(i*3)-2:i*3)
end

This seems to be okay!

Thanks a lot

[Guillaume]

Oh yes, meant to comment on that and forgot.

The most likely reason is that SNT is using a version of matlab older than R2016b, which doesn't have implicit expansion.

data2(:,(i*3)-2:i*3) = bsxfun(@times, data(:,(i*3)-2:i*3), [2 3 4]);

for pre-2016b.

However, as you said, Andrei's solution is a lot more efficient (and simpler) than the loop. The generic version, for any number of columns:

data = data .* repmat(2:4, 1, size(data, 2) / 3); %R2016b or later
data = bsxfun(@times, data, repmat(2:4, 1, size(data, 2) / 3));  %pre R2016b

I would also add an

assert(~mod(size(data, 2), 3), 'The number of columns in data is not a multiple of 3');

before the above line.

[Guillaume]

This seems to be okay!

It's unfortunately the most complicated and slowest way to achieve what you want. I would advise you to learn the other methods suggested as they will teach you better ways of coding in matlab.

[SNT]

Hi Jonas,

To follow up on this, I now need to remove all Nan from the resulting cell array tracks. The spaces must be empty. How can I do this?

Thanks!

[Guillaume]

I now need to remove all Nan from the resulting cell array tracks

tracks = cellfun(@(m) m(all(isfinite(m), 2), :), tracks, 'UniformOutput', false);

However, if you're still building the cell array with the loop (which again is the least efficient way of doing it, particularly the way you've implemented it), then you can modify the loop to not put these elements in the first place:

tracks{i} = data2(all(isfinite(data2(:, (i*3)-2:i*3)), 2), (i*3)-2:i*3);

[SNT]

Hi Johan,

Undefined function or variable 'isifinite'.

Could this be since the version I am using is R2015a is not supporting this?

[Stephen23]

@SNT: I suspect that Guillaume intended to write isfinite.

[SNT]

Hi Stephen,

Haha thanks for that! I totally didn't see that. It works great now

Sara

[Guillaume]

Yes, typo now fixed.

Answer 2

[Andrei Bobrov]

>> d
d =
     1     1     1     2     2     2     3     3     3
     1     1     1     2     2     2     3     3     3
     1     1     1     2     2     2     3     3     3
     1     1     1     0     0     0     3     3     3
     0     0     0     0     0     0     3     3     3
out = d .* repmat(2:4,1,3);
C = mat2cell(out,size(out,1),[3 3 3]);

[SNT]

Thanks for your reply Andrei, But again I seem to face some error also.. But I managed to make Jonas code work...

Thanks again!

Answer 3

[Guillaume]

Another option that would avoid the repmat is to temporarily reshape the matrix as a 3D array, perform the multiplication with the vector [2 3 4], and split that 3D array by page. As a one-liner:

%R2016b or later:
out = squeeze(num2cell(reshape(data, size(data, 1), 3, []) .* [2 3 4], [1 2]));

%pre R2016b:
out = squeeze(num2cell(bsxfun(@times, reshape(data, size(data, 1), 3, []), [2 3 4])));

In theory this should be faster as reshape is a very fast operation.

[SNT]

Hi Guillaume,

Thanks a lot for your help. I will take a look at this approach as well. Sorry about my very little knowledge in Matlab coding knowledge :)

[Guillaume]

To sum up, including the removal of the nans, this is all that is needed:

tracks = squeeze(num2cell(bsxfun(@times, reshape(data, size(data, 1), 3, []), [2 3 4])));
tracks = cellfun(@(m) m(all(isfinite(m), 2), :), tracks, 'UniformOutput', false);