Sort Through Cell And Put Corresponding Values In Their Respective Rows

Using the following code sample I desire the following:

1. I would like to first have the subroutine look through the first column of the "sortCell" cell array. If the text in the first column of the "raw" cell array has not been assigned prior to the "sortCell" cell array, assign it in the first row, ith column of the "sortCell" cell array.

2. If the name in the ith row, first column of the "raw" array has been assigned previously to first row, ith column, copy the corresponding value from the "raw" array and assign it under its name in the "sortCell" array (in the next empty row under the previously assigned "Test 1:6").

Note: The names ("Test 1:6") in the "raw" will differ by name and number depending on the excel sheet read in.

I therefore require the elements in first column of the "raw" array be assessed as variables and not hard-coded text.

If the "Test 1:6" has been placed in the first row, ith column, just get the corresponding value from the "raw" cell array and place it in the corresponding column in the next empty element.

Sample results for the "sortCell" cell array for "Test 1" & "Test 3" are outputted as an example of what I am trying to describe

%% Requirements

% 1. Have the sub-routine look at the ith "name" in the first column of the
% "raw" array.

% 2. Have the subroutine determine if that name exists in the ith row of the sortCell:

% If there does not exist that value in the sortCell, get the ith value from the "raw" Cell array (column 1) 
% place it in the sortCell row sequentially. 
% Also get the corresponding value of that name the ith row, column 2 and place it in the sortCell array 
% under its respective name

% If the name has already been placed from the first column, ith row of the "raw" Cellarray, get the corresponding
% value and place it under its respective name in the next empty row element

% If the "raw" Cell array has no value for that name, skip the value. Do not place any value in the next sequential element.

%% Constraints

% Some values will be empty, hence the requirement to assess if the there is no value and if true, skip placing value 
% in the next empty element of the sortCell Cell array.

%%

clear
clc


%% Input
% Read in spreadsheet
[num,text,raw] = xlsread('ML_Q_1.xlsx');


%% Subroutine

% Populates the cell type array from cell type to elements with strings
mask = cellfun(@ischar, raw);                               % Boolian logic determining if character type for each element of "raw" array.
raw(mask) = num2cell(string(raw(mask)));                    % Changes character type to string type in each element of the "raw" array.

% Initial dimensions of num array

[rowDim, colDim] =size(text)

%% Output

% Names in sperate columns with their corresponding values below them

% Initalize Cell Array
sortCell{rowDim,rowDim} = []

%%
% End Result Should Be Sorted As Follows (for Test 1 values):

sortCell{1,1} = "Test 1"
sortCell{1,2} = "Test 2"
sortCell{1,3} = "Test 3"
sortCell{1,4} = "Test 4"
sortCell{1,5} = "Test 5"
sortCell{1,6} = "Test 6"

% Column 1 of the sortCell
sortCell{2,1} = 100
sortCell{3,1} = 101
sortCell{4,1} = 100.6
sortCell{5,1} = 100.8
sortCell{6,1} = 100.4
sortCell{7,1} = 100.5

% Column 3 of the sortCell
sortCell{2,3} = 130
sortCell{3,3} = 130.7
sortCell{4,3} = 130.9

sortCell{6,3} = 130.2
sortCell{7,3} = 130.5

[Bob Thompson]

What exactly seems to be the problem?

[Jay]

I do not see how I can:

1. Code an algorithm to look if the text from the first column of "raw" array is in the first row, all columns of the "sortCell"

-> If the text does not exist in the "sortCell", assign it to next empty column element of the first row. Next, the value in the same row but second column of the "raw" array is to be assigned under the same name in the "sortCell" array.

Or,

-> If the text exists in the "sortCell" of the first row, ith column, assign the value of the same row, second column being perused ("raw" array) to the next column element under the same text in the "sortCell" array.

Answer 1

[Guillaume]

This is how I'd do it:

data = readtable('ML_Q_1.xlsx', 'ReadVariableNames', false);
%optionally, give better name to the variables of the table
%data.Properties.VariableNames = {'???', '????'}
data = rmmissing(data);  %get rid of empty rows
sorted = rowfun(@(v) {v}, data, 'GroupingVariables', 1);  %group column 2 by column 1

You can keep the result as a table, or if you really want a cell array:

csorted = sorted{:, [1, 3]}'  %a 2xG cell array. csorted{1, :} is the header, csorted2, :} is the grouped data

[Jay]

That does everything required with 4 command lines.

I will now try and wrap my head around the Name-Value Pair Arguments and parameters to work with tables more efficiently.

Thank you Guillaume.

[Jay]

Guillaume,

Can you please tell me what you are calling (@) in the rowfun with the "v" specifiers (using the Name-Value Pair Argument parameter relating the array and cell "v" to the tidied-up "data" table?

sorted = rowfun(@(v) {v}, data, 'GroupingVariables', 1);  %group column 2 by column 1

[Guillaume]

The function that rowfun invokes for each group of data receives a vector of the grouped rows (for each data variable). In return, it expects a scalar (for each data variable). So, all the anonymous function does is pack the input it gets (I called it v) into a scalar cell array.

Another option would have been to tell rowfun that the 'OutputFormat' is 'cell', in which case:

sorted = rowfun(@(v) v, data, 'GroupingVariables', 1, 'OutputFormat', 'cell')

but you don't get a table in return. Tables are typically more powerful than cell array.

[Jay]

Thank you for the explanation Guillaume, but why when I use the code in other programs, does the order of the grouping (in the first column) change to an alphabetical sequence and not remain the same initial order and, more importantly,how do I specify to keep the order the same?