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

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.

[Cris LaPierre]

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]

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.

Answer 1

[Ameer Hamza]

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)];

[Thomas Webber]

Thanks Ameer!

This works just how I need! Nice and simple!

[Ameer Hamza]

I am glad to be of help!

Answer 2

[Cris LaPierre]

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.