MATLAB: Table: group by datetime

table group by varfun datetime

Hi all,
I have defined a table and I now want to group by a column of type 'datetime'. I tried varfun, but this function doesn't work for this kind. Can someone help? Thanks a lot! So table looks like:
    [TYPE Datetime VALUE]
And the result should be the value per datetime, summed by TYPE.

Best Answer

It isn't possible. To illustrate, I'll sum by matching date (instead of hour) using datetime versus datenum.
VALUE=[2;3;4];
dt = datetime(2015,[1;2;1],1);
tbl_dt=table(dt,VALUE,'VariableNames',{'dt','VALUE'});
tbl_dt=varfun(@nansum,tbl_dt,'GroupingVariables',{'dt'});
MATLAB returns
Error using table/varfun (line 154)
A grouping variable must be a categorical, numeric, or logical vector, a cell vector of strings, or a 2D character array.
Now use datenum instead:
dn = datenum(2015,[1;2;1],1);
tbl_dn=table(dn,VALUE,'VariableNames',{'dn','VALUE'});
tbl_dn=varfun(@nansum,tbl_dn,'GroupingVariables',{'dn'});
Because datenum is an integer, MATLAB has no problem:
tbl_dn = 
                    dn        GroupCount    nansum_VALUE
                __________    __________    ____________
      735965    7.3597e+05    2             6           
      735996      7.36e+05    1             3
If you have table variables with dates or times and need to do grouping or summary functions (i.e. varfun or grpstats), this makes datetime pretty much useless. You could work around it by casting the value from datetime to datenum around each operation. Perhaps there is a forthcoming modification to varfun that would solve this limitation.
Related Question