Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Compute monthly averages from daily data

Tags:

r

average

I have this dataframe "df1" as example which is actually part of a much larger one (15 years):

             X1          X2
3798 2009-12-29           0
3799 2009-12-30           0
3800 2009-12-31           0 
3802 2010-01-02           0
3803 2010-01-03         2.1
3804 2010-01-04           0
3805 2010-01-05           0
3806 2010-01-06           0
3807 2010-01-07           0
3808 2010-01-08           0
3809 2010-01-09           0
3810 2010-01-10         6.8
3811 2010-01-12           0
3812 2010-01-13           0
3813 2010-01-14        17.7
3814 2010-01-16           0
3815 2010-01-17           0
3816 2010-01-18         1.5
3817 2010-01-19           0
3818 2010-01-20           0
3819 2010-01-21           0
3820 2010-01-22           0
3821 2010-01-23           0
3822 2010-01-24           0
3823 2010-01-25           0
3824 2010-01-26           0
3825 2010-01-27         4.5
3826 2010-01-28           0
3827 2010-01-29           0
3828 2010-01-31           0
3829 2010-02-01           0
3830 2010-02-03           0
3831 2010-02-04           0
3832 2010-02-05           0
3833 2010-02-07           0
3834 2010-02-08           0
3835 2010-02-09         1.2  

I want to use this dataframe to create a new one "df2" with averages per month. Does anyone know how to do this? Help would be great!

like image 213
MB123 Avatar asked May 20 '13 14:05

MB123


People also ask

How do you calculate monthly averages?

Once you have all the numbers for each month, add all the numbers together for each month, and then divide them by the total amount of months.

How do I calculate monthly average data in Excel?

Select a blank cell, enter the formula =AVERAGEIF(J2:J24,P2,M2:M24) into it, and press the Enter key. Then you will get the average of the specified date.

How do I calculate my daily average?

Example Using The Average Daily Balance Method To determine your average daily balance, you need to sum up your daily balances in the billing cycle and divide it by the total number of days in the billing cycle, which in this case is 25.


1 Answers

One way, using base R would be to make sure your dates are of class Date or similar ( e.g. POSIXct) if you haven't already, and then to extract the months and years (as your data spans more than one year) and aggregate like so:

#  Convert to date if not already
df1$X1 <- as.Date(df1$X1)

#  Get months
df1$Month <- months(df1$X1)

#  Get years
df1$Year <- format(df1$X1,format="%y")

#  Aggregate 'X2' on months and year and get mean
aggregate( X2 ~ Month + Year , df1 , mean )
#    Month Year        X2
#1 December   09 0.0000000
#2 February   10 0.1714286
#3  January   10 1.2074074

There are quite a few ways of doing this if you have a look around.

like image 121
Simon O'Hanlon Avatar answered Nov 02 '22 19:11

Simon O'Hanlon