Read and sort data in different spreadsheets according to specific columns
2 次查看(过去 30 天)
显示 更早的评论
SHEET1 (in the attachment, sheet "Raw"):
1 2 3 4 5 6 7
19 969.4 528.3 2 1 0 6.04
21 979.3 534 2 1 0 5.92
21 993.5 294.8 2 1 0 0.73
25 975.6 525.4 1 3 0 6.42
26 983.2 531.6 1 3 0 10.39
28 972.4 522.7 1 3 0 9.32
SHEET2 (in the attachment, sheet "output"):
1 2 3 4 5 6
144 1 80.95268 799.3523 24639.11 -0.13785
158 1 0.076101 299.0596 15.25692 -0.00214
182 1 -131.446 12.7761 -4955.49 -870.151
245 1 7.628916 86.74559 1856.056 46.52387
368 0 -2.67453 89.56531 -588.948 -17.5897
376 1 -3.03928 8.683562 -127.729 -46.3753
DESIRED OUTPUT of sheet2 in sheet 3, namely "labeled_output":
1 2 3 4 5 6 7
144 1 80.95268 799.3523 24639.11 -0.13785 120
158 1 0.076101 299.0596 15.25692 -0.00214 130
182 1 -131.446 12.7761 -4955.49 -870.151 230
245 1 7.628916 86.74559 1856.056 46.52387 210
368 0 -2.67453 89.56531 -588.948 -17.5897 110
In the example, I have two spreadsheets where the data is a numeric matrix. Sheet one contains 7columns and sheet2 contains 6 columns. The first column of both the sheets contain the trial numbers and are the same across sheets. I want matlab to match the row input of column1 of both sheets and then sort the rows in sheet2 according to the columns 4, 5, 6 (4:6) of sheet1. Lastly, I want matlab to create a new column in sheet2 (i.e., column no. 7) according to the sorted data and labels of each row in the column 4:6 (as shown as the desired output). the 7th column of sheet2, should be the input of the rows in the three columns (4,5,6) of sheet1 matching the column1 of both the sheets.
Alternatlively, the output file could comprise of the three columns entries( 4, 5 ,6) of sheet1 in sheet 2 as new columns (i.e., columns 7, 8, 9) after matching and sorting the rows according the column1 in both sheets.
Can anyone help? thank you :)
0 个评论
回答(1 个)
Brahmadev
2024-2-16
编辑:Brahmadev
2024-2-16
As per my understanding, you would like to accomplish 2 tasks, first being, adding a Column 7 in sheet 2 data based on sheet 1 and the second is sorting the different rows in sheet 2 using sheet 1 rows. This can be achieved using MATLAB, see code attached below.
I am assuming that the new column data comes from the first occurance of the data value in the column 1 of sheet 1. If other occurances are needed or an average should be taken, the code can be modified accordingly. Also, if a particular value that is present in sheet 2 is not present in sheet 1, a default value of 'NaN' is used. This can also be changed.
% Read the data from SHEET1 and SHEET2 (replace 'file.xlsx' with your actual file name)
rawData = readmatrix('Output_metrics.xlsx', 'Sheet', 'Raw');
outputData = readmatrix('Output_metrics.xlsx', 'Sheet', 'Output');
% Initialize the new column for SHEET2
newColumn = zeros(size(outputData, 1), 1);
% Loop through each row in SHEET2
for i = 1:size(outputData, 1)
% Find the matching trial numbers in SHEET1
trialNumber = outputData(i, 1);
rowIndex = find(rawData(:, 1) == trialNumber, 1, 'first'); % Only consider the first match
% Check if a matching trial number was found
if ~isempty(rowIndex)
% Take the values from the first matching row in SHEET1 for columns 4, 5, and 6
values = rawData(rowIndex, 5:7);
% Convert the values to a single label (assuming concatenation of the values)
% For example: [2 1 0] becomes '210'
label = str2double(sprintf('%d%d%d', values));
% Assign the label to the new column for SHEET2
newColumn(i) = label;
else
% If no matching trial number is found, you can set a default value or handle it as needed
newColumn(i) = NaN; % or some other default value
end
end
% Append the new column to SHEET2 data
labeledOutput = [outputData, newColumn];
% Rearrange data in SHEET2 using the first column in SHEET1
% Initialize a new matrix to hold the sorted outputData
sortedOutputData = zeros(size(labeledOutput));
% First column of SHEET1 and SHEET2
C1Raw = rawData(:, 1);
C1Output = labeledOutput(:, 1);
for i = 1:length(C1Raw)
% Find the index of the current trial number in the outputData
indexInOutput = find(C1Output == C1Raw(i));
% If the trial number is found in the outputData, copy the row to the sortedOutputData
if ~isempty(indexInOutput)
sortedOutputData(i, :) = labeledOutput(indexInOutput, :);
end
end
% Write the sorted output to a new file (replace 'labeled_output.xlsx' with your desired output file name)
writematrix(sortedOutputData, 'sorted_output.xlsx', 'Sheet', 'SortedOutput');
Hope this helps in resolving your query!
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!