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!
