Groupby Date and Categorical Column on Tables

5 次查看（过去 30 天）

Furqan Hashim 2020-8-28

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/585932-groupby-date-and-categorical-column-on-tables

评论： Furqan Hashim 2020-8-29

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.

23 个评论
显示 21更早的评论隐藏 21更早的评论

Adam Danz 2020-8-28

编辑：Adam Danz 2020-8-28

在 MATLAB Online 中打开

I filled in your release info.

When I test your solution, it works well!

Perhaps you could show us a sample of your input using

head(data)

Avoid using mean as a variable name since it's a very commonly used function.

data = table(['A';'B';'A';'A';'B'], datetime('now')-days([1;2;1;2;2]), rand(5,1)*100, 'VariableNames', {'Stock','Date','Open'})
%     data =
%       5×3 table
%         Stock            Date             Open 
%         _____    ____________________    ______
%           A      27-Aug-2020 16:27:51    81.472
%           B      26-Aug-2020 16:27:51    90.579
%           A      27-Aug-2020 16:27:51    12.699
%           A      26-Aug-2020 16:27:51    91.338
%           B      26-Aug-2020 16:27:51    63.236
  
mu = varfun(@mean, data,'GroupingVariables',{'Stock', 'Date'},'InputVariables','Open')
%     mu =
%       3×4 table
%         Stock            Date            GroupCount    mean_Open
%         _____    ____________________    __________    _________
%           A      26-Aug-2020 16:27:51        1          91.338  
%           A      27-Aug-2020 16:27:51        2          47.086  
%           B      26-Aug-2020 16:27:51        2          76.908  

I've also verified that the r2017b documentation contains the same inputs and name-value options.

https://www.mathworks.com/help/releases/R2017b/matlab/ref/varfun.html

Furqan Hashim 2020-8-29

在 MATLAB Online 中打开

See the results below that you asked

>> which mean -all
C:\Program Files\MATLAB\R2017b\toolbox\matlab\datafun\mean.m
C:\Program Files\MATLAB\R2017b\toolbox\matlab\timefun\@duration\mean.m          % duration method
C:\Program Files\MATLAB\R2017b\toolbox\matlab\timefun\@datetime\mean.m          % datetime method
C:\Program Files\MATLAB\R2017b\toolbox\matlab\bigdata\@tall\mean.m              % tall method
C:\Program Files\MATLAB\R2017b\toolbox\distcomp\parallel\@codistributed\mean.m  % codistributed method
C:\Program Files\MATLAB\R2017b\toolbox\distcomp\gpu\@gpuArray\mean.m            % gpuArray method
C:\Program Files\MATLAB\R2017b\toolbox\finance\ftseries\@fints\mean.m           % fints method
C:\Program Files\MATLAB\R2017b\toolbox\mbc\mbctools\@sweepset\mean.m            % sweepset method
C:\Program Files\MATLAB\R2017b\toolbox\stats\stats\@ProbDistUnivParam\mean.m    % ProbDistUnivParam method
C:\Program Files\MATLAB\R2017b\toolbox\matlab\timeseries\@timeseries\mean.m     % timeseries method
>> which('mean','in','varfun')
C:\Program Files\MATLAB\R2017b\toolbox\matlab\datafun\mean.m

Adam Danz 2020-8-29

编辑：Adam Danz 2020-8-29

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

Install the updates to your current release and try again.
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.
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 2020-8-29

Adam Thanks a lot for the help! Much appreciated.

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Mohammad Sami 2020-8-29

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/585932-groupby-date-and-categorical-column-on-tables#answer_486635

在 MATLAB Online 中打开

Perhaps you can try the alternative below.

if true
[G,tabout] = findgroups(data(:,{'Stock','Date'}));
tabout.avgOpen = splitapply(@mean,data.Open,G);
end

4 个评论
显示 2更早的评论隐藏 2更早的评论

Furqan Hashim 2020-8-29

在 MATLAB Online 中打开

Output is

MATLAB Version: 9.3.0.713579 (R2017b)

Adam Danz 2020-8-29

编辑：Adam Danz 2020-8-29

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.