Count numbers for occurrences
54 views (last 30 days)
Show older comments
Panayiotis Christodoulou
on 16 May 2016
Edited: the cyclist
on 18 May 2016
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
0 Comments
Accepted Answer
Sebastian Castro
on 16 May 2016
Edited: Sebastian Castro
on 16 May 2016
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
More Answers (2)
Duncan Po
on 17 May 2016
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]),:)
0 Comments
the cyclist
on 16 May 2016
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)
0 Comments
See Also
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!