How to count number of times a number appears in column B based on column A

Hi. I am wondering how I can make a table that shows how many times a number appears in a column based off another column.

For example,

If I have the dataset below.

Letters Coulmn B

A 1

B 2

A 2

A 1

C 1

D 3

I want to make an array or table that will have column that shows the following.

Letters Time #1 appears Time #2 appears Time #3 appears

A 2 1 0

B 0 1 0

C 1 0 0

D 1 0 1

Is this something that is possible? I can count the total, but am having trouble figuring out how to seperate based on criteria. The data is currently in a string array and a table. Thank you!

Answer 1

[Are Mjaavatten]

letters = 'ABAACD';
numbers = [1,2,2,1,1,3];
uniqueletters = unique(letters);
uniquenumbers = unique(numbers);
n = length(uniqueletters);
m = length(uniquenumbers);
R = zeros(n,m);
for i = 1:n
  fprintf('%3s',uniqueletters(i))
  for j = 1:m
    R(i,j) = sum(letters == uniqueletters(i) & numbers == uniquenumbers(j));
    fprintf('%3d',R(i,j));
  end
  fprintf('\n')
end

[Madhat]

Hi Are, Thank you! This works great. When I do T = array2table(R) , it gives me a 4 x 3 table that doesn't include the uniqueletters column.

My solution looks like this. I already have my Letters in 4 x 1 format so it adds the extra column like I want it to. Do you recommend a different method to use with your solution? Thanks again!

T = array2table(R);
T2 = table;
T2.Letters = uniqueletters;
T2(:,:);
T3 = [T3 T]

[Are Mjaavatten]

I wasn't thinking of tables, but they are the obvious choice here. This seems to give an OK result:

T = array2table(R,'RowNames',string(reshape(uniqueletters,[n,1])));

[Madhat]

Yes, this looks great too. Thank you very much, you were a tremendous help!

Answer 2

[Stephen23]

Using a table makes the counting easy:

>> T = table();
>> T.letters = {'A';'B';'A';'A';'C';'D'};
>> T.columnB = [  1;  2;  2;  1;  1;  3];
>> T.temp = T.columnB;
>> T.temp(:) = 1;
>> U = unstack(T,'temp','columnB')
U = 
    letters    x1     x2     x3 
    _______    ___    ___    ___
    'A'          2      1    NaN
    'B'        NaN      1    NaN
    'C'          1    NaN    NaN
    'D'        NaN    NaN      1