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

5 Ansichten (letzte 30 Tage)
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!
  3 Kommentare
KD
KD am 20 Mär. 2025
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?

Melden Sie sich an, um zu kommentieren.

Akzeptierte Antwort

Cris LaPierre
Cris LaPierre am 19 Mär. 2025
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
  2 Kommentare
Cris LaPierre
Cris LaPierre am 19 Mär. 2025
Bearbeitet: Cris LaPierre am 20 Mär. 2025
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
KD am 20 Mär. 2025
Thank you! The first response was exactly what I needed!

Melden Sie sich an, um zu kommentieren.

Weitere Antworten (0)

Kategorien

Mehr zu Tables finden Sie in Help Center und File Exchange

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by