- Read the 'Mapping' Excel File
Help with data mapping
1 Ansicht (letzte 30 Tage)
Ältere Kommentare anzeigen
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)),:) = [];
0 Kommentare
Antworten (1)
prabhat kumar sharma
am 30 Apr. 2024
Verschoben: Mathieu NOE
am 30 Apr. 2024
Hi Ahmed,
I understand you want to map your data based on sectors.
To map the output of your code (i.e., E25-50, H25-50, and CCS25-50) into their respective columns in a new Excel file ('Mapping'), you'll need to follow a series of steps.
[~, ~, mapping] = xlsread('Mapping.xlsx');
2. Create a Function to Map Values: You can use the below function for refrence.
function updatedMapping = mapValues(mapping, data, columnIndex)
% Assuming mapping is the content of the 'Mapping' Excel file
% data is one of your arrays like E25, H25, etc.
% columnIndex is the column in 'mapping' where the data should go
% Iterate through each row in the data array
for i = 1:size(data, 1)
sector = data{i, 1}; % The sector name
value = data{i, 2}; % The value to map
% Find the matching row in the mapping array
rowIndex = find(strcmp(mapping(:, 1), sector));
if ~isempty(rowIndex)
% If a matching row is found, update the mapping array
mapping{rowIndex, columnIndex} = value;
end
end
updatedMapping = mapping;
end
3. Apply the Mapping Function for Each Dataset
% Example usage for E25, assuming E25 values go in column 2
mapping = mapValues(mapping, E25, 2);
% Repeat for H25, CCS25, etc., with the correct column indices
mapping = mapValues(mapping, H25, 3);
mapping = mapValues(mapping, CCS25, 4);
4. Write the Updated Mapping Back to an Excel File
xlswrite('UpdatedMapping.xlsx', mapping);
I hope it helps!
0 Kommentare
Siehe auch
Kategorien
Mehr zu Large Files and Big Data finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!