Separate multi-word comma separated cell into rows

조회 수: 3(최근 30일)
Maddie Long
Maddie Long 2022년 3월 15일
편집: Peter Perkins 2022년 3월 17일
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!

채택된 답변

Stephen23
Stephen23 2022년 3월 15일
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'}

추가 답변(2개)

Peter Perkins
Peter Perkins 2022년 3월 17일
편집: Peter Perkins 2022년 3월 17일
[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 2022년 3월 15일
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'}
  댓글 수: 2
Voss
Voss 2022년 3월 15일
You're welcome! Not quite perfect, but just ok.

댓글을 달려면 로그인하십시오.

Community Treasure Hunt

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

Start Hunting!

Translated by