MATLAB Answers

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

17 views (last 30 days)
SNT
SNT on 23 May 2018
Commented: Guillaume on 24 May 2018
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

  1 Comment

SNT
SNT on 23 May 2018
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

Sign in to comment.

Accepted Answer

jonas
jonas on 23 May 2018
Edited: jonas on 23 May 2018
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

  18 Comments

Sign in to comment.

More Answers (2)

Andrei Bobrov
Andrei Bobrov on 23 May 2018
>> 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]);

  1 Comment

SNT
SNT on 23 May 2018
Thanks for your reply Andrei, But again I seem to face some error also.. But I managed to make Jonas code work...
Thanks again!

Sign in to comment.


Guillaume
Guillaume on 23 May 2018
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.

  2 Comments

SNT
SNT on 24 May 2018
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
Guillaume on 24 May 2018
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);

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!