Splitting columns based on comma

Hey everyone, I have a table (raw) with several columns, one of them is 'Position' (see below) which contains different coordinates. Some rows are blank (''). I want to split the coordinates based on the comma between them and put them seperately back to the 'raw' table.

raw.Position = 
''
''
''
'(-6.9, 4.8, 68.4)'
'(-8.5, 3.1, 18.0)'
'(13.5, 2.5, 70.1)'
''
''

I tried the following

P = raw.Position
new = regexp(P, ',','split');
raw.a = P(:,1);                             Error: Index in position 2 exceeds array bounds. Index must not exceed 1.
raw.b = P (:,2);
raw.c = P (:,3); 

but the 'new' table saved the splitted coordinates in 1x3 cells, so they could not be transfered back to the 'raw' table (I think that's the problem). Also, I wanted to remove the brackets as well from the coordinates which did not work either.. Any help appreciated!

new = 
''
''
''
1x3 cell
1x3 cell
1x3 cell
''
''

[Image Analyst]

Make it easy for people to help you. Attach one of the data files or the "raw" variable in a .mat file with the paperclip icon.

TUTORIAL: How to ask a question (on Answers) and get a fast answer

[Stephen23]

Context: https://www.mathworks.com/matlabcentral/answers/1727700-splitting-table-text-column-with-multiple-delimiters-based-on-parantheses

Answer 1

[Stephen23]

raw.Position = {'';'';'';'(-6.9, 4.8, 68.4)';'(-8.5, 3.1, 18.0)';'(13.5, 2.5, 70.1)';'';''};
tmp = sscanf(sprintf('%s',raw.Position{:}),'(%f,%f,%f)',[3,Inf]).';
raw.a = tmp(:,1);
raw.b = tmp(:,2);
raw.c = tmp(:,3)

raw = struct with fields:
    Position: {8×1 cell}
           a: [3×1 double]
           b: [3×1 double]
           c: [3×1 double]

[greenyellow22]

Thank you very much. The command splits the coordinates into three columns. Nevertheless, I would like to have also the cells which do not contain any coordinates. Otherwise I cannot move these three new columns back to the original table which also contains other columns/ data. Because the newly created columns has less rows than the original table. Is there any way you could help me with that? I highly appreciate it!!

[Stephen23]

aw.Position = {'';'';'';'(-6.9, 4.8, 68.4)';'(-8.5, 3.1, 18.0)';'(13.5, 2.5, 70.1)';'';''};
fnh = @(t) sscanf(t,'(%f,%f,%f)',[1,3]);
V = cellfun(fnh,aw.Position,'uni',0)

V = 8×1 cell array
    {0×0 double              }
    {0×0 double              }
    {0×0 double              }
    {[-6.9000 4.8000 68.4000]}
    {[     -8.5000 3.1000 18]}
    {[13.5000 2.5000 70.1000]}
    {0×0 double              }
    {0×0 double              }

[Stephen23]

aw.Position = {'';'';'';'(-6.9, 4.8, 68.4)';'(-8.5, 3.1, 18.0)';'(13.5, 2.5, 70.1)';'';''};
fnh = @(t) sscanf(t,'(%f,%f,%f)',[1,3]);
V = cellfun(fnh,aw.Position,'uni',0);
V(cellfun(@isempty,V)) = {nan(1,3)};
M = vertcat(V{:})

M = 8×3

NaN NaN NaN NaN NaN NaN NaN NaN NaN -6.9000 4.8000 68.4000 -8.5000 3.1000 18.0000 13.5000 2.5000 70.1000 NaN NaN NaN NaN NaN NaN

Answer 2

[greenyellow22]

But with that I would end up having the coordinates in one column all together again, and not have them splitted into three columns... :/

Answer 3

[greenyellow22]

Thanks!

Instead of aw.Position = {'';'';'';'(-6.9, 4.8, 68.4)';'(-8.5, 3.1, 18.0)';'(13.5, 2.5, 70.1)';'';''}, I wrote

XY = aw.Position;

fnh = @(t) sscanf(t,'(%f,%f,%f)',[1,3]);

V = cellfun(fnh,XY,'uni',0);

V(cellfun(@isempty,V)) = {nan(1,3)};

M = vertcat(V{:})

(aw is a table already existing, where Position is just one column with more rows and coordinates than the example I gave).

but then I get the error message:

Error using vertcat

Dimensions of arrays being concatenated are not consistent.

I'm sorry, I just don't have enough transfer knowledge to adapt your commands accordingly.

[Stephen23]

This is your example:

aw.Position =  {'';'';'';'(-6.9, 4.8, 68.4)';'(-8.5, 3.1, 18.0)';'(13.5, 2.5, 70.1)';'';''};
XY = aw.Position;
fnh = @(t) sscanf(t,'(%f,%f,%f)',[1,3]);
V = cellfun(fnh,XY,'uni',0);
V(cellfun(@isempty,V)) = {nan(1,3)};
M = vertcat(V{:})

M = 8×3

NaN NaN NaN NaN NaN NaN NaN NaN NaN -6.9000 4.8000 68.4000 -8.5000 3.1000 18.0000 13.5000 2.5000 70.1000 NaN NaN NaN NaN NaN NaN

I suspect that your data might be somewhat different to what you explained.

[greenyellow22]

Yes, but aw.Position is actually a column of over 10000 cells and the coordinates I posted here are only a tiny portion of it. So by entering the whole column as array, and not just {'';'';'';'(-6.9, 4.8, 68.4)';'(-8.5, 3.1, 18.0)';'(13.5, 2.5, 70.1)';'';''} results in the error message:

Error using vertcat

Dimensions of arrays being concatenated are not consistent.

(Not sure if that makes sense :D)

[Stephen23]

"Not sure if that makes sense"

Yes, it makes sense. As I already wrote in my earlier comment, the cause is probably that your data differ in some way to the example data that you gave. Because you did not upload your actual data in a mat file by clicking the paperclip button, I cannot investigate this for you. If you do upload your data, then I can investigate it.

[greenyellow22]

I understand but unfortunately, I cannot upload the data. But I have a table of 5 columns and one column contains the coordinates (23420x1 cells), whereby some cells/ rows are empty and some contain the coordinates (e.g. '(-6.9, 4.8, 68.4)'). So for all cells containing coordinates I want to split the coordinates into three and enter these seperately into three different new columns added to the table (with the empty cells staying empty). If writing the commands is impossible without the data, then I understand and thank you anyway for your prior help :-)