Split table column into multiple columns within the same table based on a specific delimiter

15 views (last 30 days)
Hi all,
I have been hunting for a solution for a few days now and currently nothing is quite working out. I have a table pulled from an sql database with around 16,000 rows. One column is a series of values from 0 to 1 (1024 total) seperated by a ',' . I want to be able to split this single column into 1024 seperate columns,each contianing one value for each row and keep them within the same table, idealy just like the text to columns function in excel.
Any help is much appreciated!
Thomas
Edit: Detections.mat is a head of the table in question. I would like to idealy split column 8 (avrg_spectrum) into a column for each value.
  2 Comments
Cris LaPierre
Cris LaPierre on 26 Jun 2020
It is helpful if you can share an actual example of an entry you want to split. How is it stored in your table? Is it a cell, string, char?
Thomas Webber
Thomas Webber on 26 Jun 2020
I have attached a head of the table in question. Column 8, 'avrg_spectrum' is the only column I would like to split open based on numbers seperated by a ','. The column itself is a cell array of charcater vectors, which would ideally be numeric values in the finished table.

Sign in to comment.

Accepted Answer

Ameer Hamza
Ameer Hamza on 26 Jun 2020
Try this
load('Detections.mat');
val = cell2mat(cellfun(@(str) {sscanf(str, '%f,').'}, Click.avrg_spectrum));
new_table = [Click(:,1:7) array2table(val) Click(:,9:end)];

More Answers (1)

Cris LaPierre
Cris LaPierre on 26 Jun 2020
Another approach
Click.avrg_spectrum = str2double(split(string(Click.avrg_spectrum),","));
newClick = splitvars(Click,"avrg_spectrum");
This does add an extra empty column since the field being split ends in a comma. You could remove that as part of the process if you want.
Click.avrg_spectrum = str2double(split(string(Click.avrg_spectrum),","));
Click.avrg_spectrum(:,end)=[];
newClick = splitvars(Click,"avrg_spectrum");
This is a little slower, so if you have lots of data, Ameer's approach is better.

Categories

Find more on Tables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!