Sort Through Cell And Put Corresponding Values In Their Respective Rows
12 views (last 30 days)
Show older comments
Using the following code sample I desire the following:
- 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
2 Comments
Accepted Answer
Guillaume
on 15 Jul 2019
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
4 Comments
Guillaume
on 16 Jul 2019
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.
More Answers (0)
See Also
Categories
Find more on Characters and Strings in Help Center and File Exchange
Products
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!