How to do monthly average in a Table?

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

Answer 1

[Peter Perkins]

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

[JFz]

Yes. You are right. It is a string showing dates but not dates as type. Thank you so much. I will take a look and experiment with your comment and report back here.

[JFz]

Peter, Thank you so much for the help! It helped me a lot. Really appreciate this!

Have a nice day.

jennifer

Answer 2

[Brendan Hamm]

If you have the Statistics and MAchine Learning Toolbox, this can be done quite easily. For simplicity I will assume your Table T has the variables: Date, Terms and Prices. What we want to do is use the Month as a grouping variable. We can get the month from a datetime with the month function:

mth = month(T.Dates); % Numeric Values (optional input 'name' will give the full name)

Now we want to calculate the mean for each month which is easy to do with grpstats which will calculate statistics of its first input, grouped by the second input:

doc grpstats
[monthlyPriceMean,groupName] = grpstats(T.Prices,mth,{'mean','gname'});

monthTable = table(groupName,monthlyPriceMean); % Place in a table

You could do the same thing with the Terms. If you really want to apply this and get a table back out immediatelly, you could always use this in conjunction with varfun.

[JFz]

Thank you! Yes, this is what I wanted. But I also want to group by the term too. How to add the term into this group by?

[JFz]

I got an error: Undefined function 'month' for input arguments of type 'cell'.

[JFz]

My table looks like:

---------------------------------------
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.252400000000;
'12/8/2005'  '1/1/2006'  121.484500000000;
'12/22/2005'  '1/1/2006'  115.573500000000;

[Walter Roberson]

month() was introduced in R2014b. The Statistics And Machine Learning Toolbox was not named that until R2015a; before that it was the Statistics Toolbox, so if you do not have month() then you do not have the modern version of the Statistics And Machine Learning Toolbox. However, grpstats() was introduced in R2014a so there is still a possibility that you have the routine if your Statistics Toolbox is at least that old.

A workaround to get the month number is:

date_vectors = datevec(T.dates);
mth = date_vectors(:,2);

[Brendan Hamm]

Thanks Walter. She may indeed still have the month function, but it only works on datetimes and not on datestrings which is what she has. For grouping by term just pass in T.Term as your grouping variable. It will automatically use the unique string values as groups.

[JFz]

Thanks a lot Walter and Brendan, appreciate your effort. I indeed made a mistake in my description about the problem since I am not very familiar with certain termilogogies. Appologies.

Jennifer

[Walter Roberson]

I misread the release notes about when grpstats was introduced; when I look again I can no longer say when it was introduced.