Iterate through a CSV and assign data into new tables, based on unique IDs

Question

I'm attempting to iterate through a CSV file and assign the data within each row into new tables, based on unique IDs. The unique IDs are found within the 'Unit' column - please find the formatting of the CSV file below:

Easting	Northing	Elevation_OD	Chainage	FC	Profile	Reg_ID	Unit
245932.499	200226.982	5.464	148.997	CE	N/A	8c12.11_021	8c12.11
245932.460	200225.448	5.214	150.530	S	N/A	8c12.11_021	8c12.11
245932.485	200224.993	5.111	153.222	S	N/A	8c12.1_001	8c12.1

Essentially, I need to iterate through all of the rows and compile rows which belong to the same 'Unit' and then create subsequent 'Unit' tables.

Here's my code from my answer on my previous question showing how I obtain the unique IDs:

data = 
readtable('Input/Wales_Interim_23/Pembrokeshire_23_Finished.csv');
unit_str = string(data.Reg_ID);
split = cellfun(@(x) strsplit(x, '_'), unit_str, 'UniformOutput', 
false);
split_unit_cell = cell2table(split);
split_unit = splitvars(split_unit_cell)
unit = table2array(split_unit(:,1));

data.Unit = unit;
Ids = unique(data.Unit);
NID = numel(Ids);

Read [the docs on `unique()`](https://ch.mathworks.com/help/matlab/ref/double.unique.html). You can use the second (or third, I always forget which is which) output of `unique()` to index into the array. A loop over `1:max(ia)` then allows you to easily select all relevant rows and build a table from that. Something like `data(ia==3)` should give all rows with the 3rd unique Unit. — Adriaan, Feb 28 '23 at 20:39

Wolfie · Accepted Answer · 2023-03-01T09:17:51.060

Aside: I added a much simpler way to generate the ID as a new answer to your linked question.

You can generate a group number (one per unique ID) using findgroups

gidx = findgroups( data.Unit );

Then you can split the table by the group, and assign each group to a cell

N = max(gidx);         % gidx contains values [1,2,...,N] for N unique IDs
units = cell(N,1);     % pre-allocate an output cell
% Loop over the IDs and assign the subsets of data
for ii = 1:N 
    units{ii} = data( gidx==ii, : );
end

For what it's worth, you might want to think about your workflow. It's usually easier to continue manipulating data in a single table.

Iterate through a CSV and assign data into new tables, based on unique IDs

1 Answers1