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

1 view (last 30 days)
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!

Accepted Answer

Are Mjaavatten
Are Mjaavatten on 30 Apr 2020
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
  3 Comments
Are Mjaavatten
Are Mjaavatten on 30 Apr 2020
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
Madhat on 30 Apr 2020
Yes, this looks great too. Thank you very much, you were a tremendous help!

Sign in to comment.

More Answers (1)

Stephen23
Stephen23 on 30 Apr 2020
Edited: Stephen23 on 30 Apr 2020
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

Tags

Products

Community Treasure Hunt

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

Start Hunting!