How to split a table into multiple tables based on value in a column?

Hello,

I am reading in an excel file that has 2 columns of data. One of them has a lot number and the other has row/col numbers.

I've made a table that holds this information, however, I would like to make multiple tables for every set of lot numbers.

I've attached an example excel sheet. In this example there are 2 different lot numbers, so I would need it to make 2 different tables for each lot number with each corresponding row, col number! I also need it to be able to adjust if there are more than 2 different lot numbers because the sheet is always updated.

Thanks!

[Steven Lord]

What are you hoping / planning to do with these "multiple tables"? There is likely a better way to achieve what you're trying to do that creating an arbitrary number of variables (with names set only at runtime?) such as perhaps using the grouping functions like groupsummary or grouptransform.

[Stephen23]

Often splitting up data makes it harder to process. Consider instead:

https://www.mathworks.com/help/matlab/matlab_prog/perform-calculations-by-group-in-table.html

https://www.mathworks.com/help/matlab/matlab_prog/grouped-calculations-in-tables-and-timetables.html

[KD]

Im splitting them into different tables because I want to make heatmaps out of each separate data set. Would this still change the approach you would use?

Answer 1

[Cris LaPierre]

There are several possible approaches based on what you need. Here's one that creates a cell array of tables, one for each unique lot number.

data = readtable ('ExampleExcel.xlsx');

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

% Convert RC to numeric arrays
tmp = cellfun(@str2num,data.RC,'UniformOutput',false);
data.RC = cell2mat(tmp);

% find unique LotNumbers
G = findgroups(data.LotNumber);
% Split data into table for each lot number
tblLN = splitapply(@(x1,x2){table(x1,x2)},data,G)

tblLN = 2x1 cell array
    {9x2 table}
    {3x2 table}

% view first table
tblLN{1}

ans = 9x2 table
        x1           x2   
    __________    ________

    {'A12345'}     2     3
    {'A12345'}     4     5
    {'A12345'}     5     6
    {'A12345'}     6     7
    {'A12345'}     7     8
    {'A12345'}     8     9
    {'A12345'}     9    10
    {'A12345'}    10    11
    {'A12345'}    11    12

[Cris LaPierre]

I'll reiterate the comments above. I would probably look to groupsummary, but we'd need to know more about what your end goal is to say for certain.

data = readtable ('ExampleExcel.xlsx');

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

% Convert RC to numeric arrays
tmp = cellfun(@str2num,data.RC,'UniformOutput',false);
data.RC = cell2mat(tmp);

% find max ID for each unique LotNumber
mxID = groupsummary(data,'LotNumber','max')

mxID = 2x3 table
    LotNumber     GroupCount     max_RC 
    __________    __________    ________

    {'A12345'}        9         11    12
    {'A54321'}        3          6     7

[KD]

Thank you! The first response was exactly what I needed!