Separate multi-word comma separated cell into rows

4 views (last 30 days)
Maddie Long
Maddie Long on 15 Mar 2022
Edited: Peter Perkins on 17 Mar 2022
I would like to seperate the name of a business and the number attached to the name into 2 columns and then at the comma start a new row in the table:
My input:
{'Business Name One[5],Business Name Two[3],Business Name Three[2]'}
{'Business Name One[5]'}
{'Business Name Two[3],Business Name Three[2]'}
and I need it to output:
'Business Name One' | 5
'Business Name Two' | 3
...
I have tried
unqValue=unique(a.data);
cell_dat_split = cellfun(@(x) sscanf(x, '%s %s %s, '), unqValue, 'Uniform', false);
But its just not doing quite what I want, any help would be great!

Accepted Answer

Stephen23
Stephen23 on 15 Mar 2022
More efficient than using CELLFUN:
C = {'Business Name One[5],Business Name Two[3],Business Name Three[2]'; ...
'Business Name One[5]'; ...
'Business Name Two[3],Business Name Three[2]'};
D = regexp(sprintf('%s,',C{:}),'([^,]+)\[(\d+)\]','tokens');
D = vertcat(D{:})
D = 6×2 cell array
{'Business Name One' } {'5'} {'Business Name Two' } {'3'} {'Business Name Three'} {'2'} {'Business Name One' } {'5'} {'Business Name Two' } {'3'} {'Business Name Three'} {'2'}

More Answers (2)

Peter Perkins
Peter Perkins on 17 Mar 2022
Edited: Peter Perkins on 17 Mar 2022
[Edited to be even modern by using split, not strsplit, join, not strjoin, and double, not str2double.]
Here's a more modern version, using string and patterns, which are really the best way to go these days. There's a bit of trickiness at the beginning, going from ragged cellstr to string, but after that it's smooth sailing.
C = {'Business Name One[5],Business Name Two[3],Business Name Three[2]';
'Business Name One[5]';
'Business Name Two[3],Business Name Three[2]'}
C = 3×1 cell array
{'Business Name One[5],Business Name Two[3],Business Name Three[2]'} {'Business Name One[5]' } {'Business Name Two[3],Business Name Three[2]' }
Turn that "ragged" cell array into one long string, then split at the commas.
S = join(string(C),',')
S = "Business Name One[5],Business Name Two[3],Business Name Three[2],Business Name One[5],Business Name Two[3],Business Name Three[2]"
S = split(S,","); S = S(:)
S = 6×1 string array
"Business Name One[5]" "Business Name Two[3]" "Business Name Three[2]" "Business Name One[5]" "Business Name Two[3]" "Business Name Three[2]"
Now it's just a matter of pulling out the text and the numbers. Pattern is much easier to use than regexp.
pat = "[" + digitsPattern(1) + "]";
biz = extractBefore(S,pat)
biz = 6×1 string array
"Business Name One" "Business Name Two" "Business Name Three" "Business Name One" "Business Name Two" "Business Name Three"
buz = extract(S,digitsPattern)
buz = 6×1 string array
"5" "3" "2" "5" "3" "2"
Now convert them to more useful types and put them in a table.
biz = categorical(biz);
buz = double(buz);
t = table(biz,buz)
t = 6×2 table
biz buz ___________________ ___ Business Name One 5 Business Name Two 3 Business Name Three 2 Business Name One 5 Business Name Two 3 Business Name Three 2

Voss
Voss on 15 Mar 2022
C = { ...
'Business Name One[5],Business Name Two[3],Business Name Three[2]'; ...
'Business Name One[5]'; ...
'Business Name Two[3],Business Name Three[2]'; ...
}
C = 3×1 cell array
{'Business Name One[5],Business Name Two[3],Business Name Three[2]'} {'Business Name One[5]' } {'Business Name Two[3],Business Name Three[2]' }
C = cellfun(@(x)strsplit(x,','),C,'UniformOutput',false);
C = [C{:}].'
C = 6×1 cell array
{'Business Name One[5]' } {'Business Name Two[3]' } {'Business Name Three[2]'} {'Business Name One[5]' } {'Business Name Two[3]' } {'Business Name Three[2]'}
C = regexp(C,'([^\[]+)\[(\d+)\]','tokens');
C = [C{:}]
C = 1×6 cell array
{1×2 cell} {1×2 cell} {1×2 cell} {1×2 cell} {1×2 cell} {1×2 cell}
C = vertcat(C{:})
C = 6×2 cell array
{'Business Name One' } {'5'} {'Business Name Two' } {'3'} {'Business Name Three'} {'2'} {'Business Name One' } {'5'} {'Business Name Two' } {'3'} {'Business Name Three'} {'2'}

Community Treasure Hunt

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

Start Hunting!