Counting outcomes of names

Hi, i have a collumn of names of investors in a large dataset and im trying to extract the occurance of every investor. A large quanitity have a combination of investors, meaning there are several names in one cell, so they would only be counted once if i were to count them alone. Is there any way to count all names, even in combination with others?

Answer 1

[Matt J]

Something like this, perhaps?

investors=["Sam";"Joe";"Sam and Joe"]

investors = 3×1 string array
    "Sam"
    "Joe"
    "Sam and Joe"

contains(investors,"Sam")

ans = 3×1 logical array
   1
   0
   1

[Ruben Moreno]

In simple terms yes, but considering the magnitude of my data and the amount of investors, its not possible to simply write down all names. Added picture for visualization of the problem

[Ruben Moreno]

because i want to count all investors in every combination in a data of 33,000 observations. in a total of 8132 unique investor cominations. I cant write the name of all of these investors to count how many there are?

[Matt J]

Why isn't my solution applicable? Is it that you don't hav a prior list of all the investors? If not, will multiple investors always be delimited by commas? If so, you can use strsplit:

investors=["Sam";"Joe";"TPG, Sam, Joe";"TPG, Joe"]

investors = 4×1 string array
    "Sam"
    "Joe"
    "TPG, Sam, Joe"
    "TPG, Joe"

Names = cellfun(@(x)strsplit(x,{', '}), investors, 'UniformOutput', false);

Names=string(unique(horzcat(Names{:})'))

Names = 3×1 string array
    "Joe"
    "Sam"
    "TPG"

count=nan(size(Names));
for i=1:numel(Names)
    
    count(i)=sum(contains(investors,Names{i}));
    
end
table(Names,count)

ans = 3×2 table
    Names    count
    _____    _____

    "Joe"      3  
    "Sam"      2  
    "TPG"      2  

[Ruben Moreno]

thank you, will look into this, what does the UniformOutput do in this case?

[Matt J]

You're welcome, but if it does what you need, please Accept-click the answer.

UniformOutput=false forces the result of cellfun to be returnd as a cell array,

https://www.mathworks.com/help/matlab/ref/cellfun.html#d123e172702

[Ruben Moreno]

i get an error on the first line, Invalid expression. Check for missing multiplication operator, missing or unbalanced delimiters, or other

syntax error. To construct matrices, use brackets instead of parentheses.

[Matt J]

The error is not coming from code that you've shown us (or that I've shown you).

[Ruben Moreno]

Nvm, i fixed it. It worked now, Wow. thank you very much!

Answer 2

[Stephen23]

str = ["Sam";"Joe";"TPG, Sam, Joe";"TPG, Joe"]

str = 4×1 string array
    "Sam"
    "Joe"
    "TPG, Sam, Joe"
    "TPG, Joe"

spl = regexp(str,',','split');
[uni,~,idx] = unique(strtrim([spl{:}]));
cnt = histcounts(idx);
[uni;cnt]

ans = 2×3 string array
    "Joe"    "Sam"    "TPG"
    "3"      "2"      "2"  

[Ruben Moreno]

i figured the problem out. But i have a follow up question, I want to use the information i gained. Count of every investors frequency. to add a indication for experience in the original dataset. Creating a dummy for investors observations > 100 and 0 for the rest.