Count numbers for occurrences

Hi there,

I have a table

UserID, Market, Geo, Price, Product

I want to count for each userID how many times he bought product = 1 and how many times product=2

Final Dataset UserID, Product1, Product2

Thanks, Pan

Answer 1

[Sebastian Castro]

I'm guessing at your variable names and their format... but assuming that UserID and Product are both numeric variables, you can use the nnz (number of nonzero) function to get a count.

For example, say you want the number of Product 2 that UserID 6 purchased:

count = nnz( (myTable.UserID == 6) & (myTable.Product == 2) )

You can, of course, extend this to different user IDs and product numbers as you see fit. I'd make this into a function:

function count = getProdCount(t,uid,prod)
% t = Table
% uid = User ID
% prod = Product number

      count = nnz( (t.UserID == uid) & (t.Product == prod) );

end

Then you can call it as follows:

count = getProdCount(myTable,6,2)

- Sebastian

[Panayiotis Christodoulou]

i created my own code based on my data using the first sentence

count = nnz( (myTable.UserID == 6) & (myTable.Product == 2) )

thanks

Answer 2

[Duncan Po]

If you are using R2015b or later versions, you can use findgroups and splitapply:

% Set up a table with pretend data
userid  = [1; 1; 1; 1; 1; 2; 2; 2; 2; 2; 2; 3];
product = [1; 1; 2; 3; 3; 1; 2; 2; 2; 2; 2; 3];
T = table(userid,product);

% split into groups and compute counts
[g, T2] = findgroups(T);
T2.count = splitapply(@numel,T,g);  

% trim the table
T2 = T2(ismember(T2.product,[1 2]),:)

Answer 3

[the cyclist]

Here is one way, generally using table functions to do everything.

% Set up a table with pretend data
userid  = [1; 1; 1; 1; 1; 2; 2; 2; 2; 2; 2; 3];
product = [1; 1; 2; 3; 3; 1; 2; 2; 2; 2; 2; 3];
T = table(userid,product);

% Find counts where product = 1 (and rename the resulting variable)
T1 = varfun(@(x)sum(x==1),T,'InputVariables','product','GroupingVariables','userid');
T1.Properties.VariableNames{'Fun_product'} = 'Product1'

% Find counts where product = 2 (and rename the resulting variable)
T2 = varfun(@(x)sum(x==2),T,'InputVariables','product','GroupingVariables','userid');
T2.Properties.VariableNames{'Fun_product'} = 'Product2'

% Join the results
T12 = join(T1,T2)