Help with data mapping

조회 수: 7 (최근 30일)
AHMED FAKHRI
AHMED FAKHRI 2021년 6월 14일
답변: Asvin Kumar 2021년 6월 23일
Hi All
I hope you are well.
I have the following piece of code to process the data in the excel file attached ('ex1'), I want to map the output of the code E25-50, H25-50, and CCS25-50 into their respective columns in the new excel file ('Mapping') attached. Any idea please how to do it?
Note that for example CCS25 only contain 3 values according to their respective sectors. Therefore, when mapping, the values should correpsond to the correct sector in the file 'Mapping'.
Thanks,
Please see the attached Excel files after running the code.
[~, ~, numberandtext] = xlsread('ex1.xlsx');
str=numberandtext;
[ii1,jj1,kk1]=unique(str(:,2));
[ii2,jj2,kk2]=unique(str(:,4));
[ii3,jj3,kk3]=unique(str(:,6));
[ii4,jj4,kk4]=unique(str(:,8));
[ii5,jj5,kk5]=unique(str(:,10));
[ii6,jj6,kk6]=unique(str(:,12));
[ii7,jj7,kk7]=unique(str(:,14));
[ii8,jj8,kk8]=unique(str(:,16));
[ii9,jj9,kk9]=unique(str(:,18));
[ii10,jj10,kk10]=unique(str(:,20));
[ii11,jj11,kk11]=unique(str(:,22));
[ii12,jj12,kk12]=unique(str(:,24));
E25=[ii1 num2cell(accumarray(kk1,[str{:,1}]'))];
E25(cellfun(@(x) ~x(1),E25(:,2)),:) = [];
H25=[ii2 num2cell(accumarray(kk2,[str{:,3}]'))];
H25(cellfun(@(x) ~x(1),H25(:,2)),:) = [];
CCS25=[ii3 num2cell(accumarray(kk3,[str{:,5}]'))];
CCS25(cellfun(@(x) ~x(1),CCS25(:,2)),:) = [];
E30=[ii4 num2cell(accumarray(kk4,[str{:,7}]'))];
E30(cellfun(@(x) ~x(1),E30(:,2)),:) = [];
H30=[ii5 num2cell(accumarray(kk5,[str{:,9}]'))];
H30(cellfun(@(x) ~x(1),H30(:,2)),:) = [];
CCS30=[ii6 num2cell(accumarray(kk6,[str{:,11}]'))];
CCS30(cellfun(@(x) ~x(1),CCS30(:,2)),:) = [];
E40=[ii7 num2cell(accumarray(kk7,[str{:,13}]'))];
E40(cellfun(@(x) ~x(1),E40(:,2)),:) = [];
H40=[ii8 num2cell(accumarray(kk8,[str{:,15}]'))];
H40(cellfun(@(x) ~x(1),H40(:,2)),:) = [];
CCS40=[ii9 num2cell(accumarray(kk9,[str{:,17}]'))];
CCS40(cellfun(@(x) ~x(1),CCS40(:,2)),:) = [];
E50=[ii10 num2cell(accumarray(kk7,[str{:,19}]'))];
E50(cellfun(@(x) ~x(1),E50(:,2)),:) = [];
H50=[ii11 num2cell(accumarray(kk8,[str{:,21}]'))];
H50(cellfun(@(x) ~x(1),H50(:,2)),:) = [];
CCS50=[ii12 num2cell(accumarray(kk9,[str{:,23}]'))];
CCS50(cellfun(@(x) ~x(1),CCS50(:,2)),:) = [];

채택된 답변

Asvin Kumar
Asvin Kumar 2021년 6월 23일
  1. Read the empty table in Mapping.xlsx using readtable or the Import Tool. If you are using readtable, create a SpreadsheetImportOptions object and pass it to the File Import Options input argument.
  2. Set the 'RowNamesRange' and the 'VariablesNameRange' properties in the SpreadsheetImportOptions object to appropriate values.
  3. Now, you can assign the values in E25-E50, H25-50, and CCS25-50 using for loops. Since the row names are sectors, you can index into a row of a table using the first column of the E, H and CC matrices. Example here.
  4. Write the populated table back into an excel sheet using writetable.

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

제품


릴리스

R2020b

Community Treasure Hunt

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

Start Hunting!

Translated by