Groupby Date and Categorical Column on Tables

7 views (last 30 days)
Suppose that I have 5 columns in my table as Stock, Date, Open, High, Low, Close. Stock is a string column and Date is in Date format. Suppose that same stock can repeat on same day.
How can I count of unique stocks on each Day? That would be table with number of unique stock counts against each date.
Given that each stock can have mutiple observation on same day how can I group by both Date and Stock to get average Open price for each stock on each day.
Furqan Hashim
Furqan Hashim on 29 Aug 2020
Adam Thanks a lot for the help! Much appreciated.

Sign in to comment.

Answers (1)

Mohammad Sami
Mohammad Sami on 29 Aug 2020
Perhaps you can try the alternative below.
if true
[G,tabout] = findgroups(data(:,{'Stock','Date'}));
tabout.avgOpen = splitapply(@mean,data.Open,G);
Adam Danz
Adam Danz on 29 Aug 2020
Edited: Adam Danz on 29 Aug 2020
The only difference between your and my versions is the update #9. You could easily update your version for free by pressing the little bell icon in the upper right of the main window. I can't promise that will affect this problem but it's a good idea to get the updates anyway.

Sign in to comment.





Community Treasure Hunt

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

Start Hunting!