Read and sort data in different spreadsheets according to specific columns

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 :)

Answer 1

[Brahmadev]

Hi @Tanaya Chatterjee,

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!