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!
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With