Groupby Date and Categorical Column on Tables

6 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.
Adam Danz
Adam Danz on 29 Aug 2020
Edited: Adam Danz on 29 Aug 2020
Right, but that function only has 1 input and it's the correct input but the error message you shared was "Not enough input arguments", which doesn't occur on my end.
From this point I think your options are
  1. Install the updates to your current release and try again.
  2. Use a different machine / installation of matlab / or maybe even a newer release. If the updates didn't fix the problem, something seems to be wrong locally since none of these issues occur on my vs of r2017b update 9.
  3. Make a tech support request and point to this thread within the request so they can see what troubleshooting you've already done.
I would be interested in hearing the final solution and cause of the problem if you get to the bottom of this. Note that the error you reported in the answer below also doesn't match the error I got on the demo data in r2017b (update 9). So something's fishy.
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);
Furqan Hashim
Furqan Hashim on 29 Aug 2020
Output is
MATLAB Version: (R2017b)
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!