MATLAB: How to do monthly average in a Table

MATLAB

Hi,
I have a Matlab Table that has 3 columns. The first column is dates, the 2nd column is Term (integer number), and the 3rd column is prices (double).
The columns are: Dates Terms Prices
I want to create a new table, that the the dates of only the first of each month, and have the average price for each month.
I thought it should be easy to do, as I can do it in Excel pivot table by hand.
How to do that in Matlab? I imagine Matlab should make it easier. Besides, I have many such Excel so I want to do it more efficiently.
Thanks,
Jennifer

Best Answer

Even without Statistics And Machine Learning Toolbox (or Statistics Toolbox prior to R2015a), the varfun method of table also does this kind of grouped calculation. (And for the record, grpstats did exist in Statistics Toolbox well before R2014a.)
Jennifer, the example table that you provided does not match your original description. Term in the table is a date string, not an integer. I'll assume you have a table like this:
>> data
data = 
      Datetime         Term        Price 
    ____________    ___________    ______
    '12/7/2005'     '12/1/2005'       106
    '12/8/2005'     '12/1/2005'       106
    '12/22/2005'    '12/1/2005'       106
    '12/23/2005'    '12/1/2005'       106
    '12/28/2005'    '12/1/2005'       106
    '12/29/2005'    '12/1/2005'       106
    '12/30/2005'    '12/1/2005'       106
    '12/7/2005'     '1/1/2006'     117.25
    '12/8/2005'     '1/1/2006'     121.48
    '12/22/2005'    '1/1/2006'     115.57
Grouping by month, unless your data span less than 12 months, really means, "group by month and year". I'll assume you are in the general case.
There are better ways to do this using the datetime data type, new in R2014b. But here's a way to do it with strings:
>> dv = datevec(data.Datetime);
>> data.Year = dv(:,1);
>> data.Month = dv(:,2)
data = 
      Datetime         Term        Price     Year    Month
    ____________    ___________    ______    ____    _____
    '12/7/2005'     '12/1/2005'       106    2005    12   
    '12/8/2005'     '12/1/2005'       106    2005    12   
    '12/22/2005'    '12/1/2005'       106    2005    12   
    '12/23/2005'    '12/1/2005'       106    2005    12   
    '12/28/2005'    '12/1/2005'       106    2005    12   
    '12/29/2005'    '12/1/2005'       106    2005    12   
    '12/30/2005'    '12/1/2005'       106    2005    12   
    '12/7/2005'     '1/1/2006'     117.25    2005    12   
    '12/8/2005'     '1/1/2006'     121.48    2005    12   
    '12/22/2005'    '1/1/2006'     115.57    2005    12   
>> monthlyAvg = varfun(@mean,data,'GroupingVariables',{'Year','Month'},'InputVariable','Price')
monthlyAvg = 
               Year    Month    GroupCount    mean_Price
               ____    _____    __________    __________
    2005_12    2005    12       10            109.63
Obviously if your example data had contained rows from more than just Dec 2005, that result would have more than one row. Not sure what you mean by, "also want to group by the term". You may want to group both by month/year, and by Term. Or you might want a separate result, grouped just by Term. Either is straight-forward, here's the latter.
>> termAvg = varfun(@mean,data,'GroupingVariables','Term','InputVariable','Price')
termAvg = 
                    Term        GroupCount    mean_Price
                 ___________    __________    __________
    1/1/2006     '1/1/2006'     3             118.1     
    12/1/2005    '12/1/2005'    7               106
Related Question