How to find the monthly average from different date data in R?

R ProgrammingServer Side ProgrammingProgramming

To find the monthly average from different date data, we first need to extract the months and year from date column and then find the average using aggregate function. For example, if we have a data frame called df which has three columns say x, month, and year then the monthly average can be found by using the command −

aggregate(x~Month+Year,df,mean)

Example1

Consider the below data frame −

 Live Demo

Date1<-sample(c(as.Date("2021-01-11"),as.Date("2021-01-15"),as.Date("2021-02-13"),as.Date("2021-02-05"),as.Date("2021-02-09")),20,replace=TRUE)
x<-rnorm(20,25,3.6)
df1<-data.frame(Date1,x)
df1

Output

      Date1       x
1  2021-01-11  25.62696
2  2021-02-09  22.36829
3  2021-02-09  20.35938
4  2021-02-09  20.55244
5  2021-02-05  25.81417
6  2021-01-15  24.38129
7  2021-02-09  26.10268
8  2021-01-15  27.67208
9  2021-01-15  30.99175
10 2021-02-09  23.98216
11 2021-02-05  26.80633
12 2021-02-05  29.34845
13 2021-01-15  30.03150
14 2021-02-05  28.04617
15 2021-02-05  21.64901
16 2021-01-15  23.25788
17 2021-02-09  27.60417
18 2021-02-09  18.94671
19 2021-01-11  25.41612
20 2021-02-13  23.14153

Creating a column for Month and Year in df1 using Date1 column −

df1$Month<-months(df1$Date1)
df1$Year<-format(df1$Date1,format="%y")
df1
     Date1        x      Month    Year
1  2021-01-11  25.62696  January   21
2  2021-02-09  22.36829  February  21
3  2021-02-09  20.35938  February  21
4  2021-02-09  20.55244  February  21
5  2021-02-05  25.81417  February  21
6  2021-01-15  24.38129  January   21
7  2021-02-09  26.10268  February  21
8  2021-01-15  27.67208  January   21
9  2021-01-15  30.99175  January   21
10 2021-02-09  23.98216  February  21
11 2021-02-05  26.80633  February  21
12 2021-02-05  29.34845  February  21
13 2021-01-15  30.03150  January   21
14 2021-02-05  28.04617  February  21
15 2021-02-05  21.64901  February  21
16 2021-01-15  23.25788  January   21
17 2021-02-09  27.60417  February  21
18 2021-02-09  18.94671  February  21
19 2021-01-11  25.41612  January   21
20 2021-02-13  23.14153  February  21

Finding monthly average for data in df1 −

aggregate(x~Month+Year,df1,mean)

Month Year x
1 February 21 24.20934
2 January 21 26.76823

Example2

 Live Demo

Date2<-sample(c(as.Date("2020-01-01"),as.Date("2020-04-15"),as.Date("2020-06-03"),as.Date("2020-02-25"),as.Date("2020-10-09")),20,replace=TRUE)
y<-rpois(20,8)
df2<-data.frame(Date2,y)
df2

Output

     Date2     y
1  2020-02-25  9
2  2020-01-01  8
3  2020-06-03 12
4  2020-01-01  6
5  2020-01-01  7
6  2020-04-15  9
7  2020-10-09  8
8  2020-10-09  4
9  2020-10-09  7
10 2020-06-03 10
11 2020-06-03  6
12 2020-10-09 10
13 2020-04-15 10
14 2020-10-09 10
15 2020-01-01 11
16 2020-04-15  7
17 2020-10-09  6
18 2020-04-15  7
19 2020-01-01  5
20 2020-10-09  4

Creating a column for Month and Year in df2 using Date2 column −

df2$Month<-months(df2$Date2)
df2$Year<-format(df2$Date2,format="%y")
df2
     Date2     y  Month    Year
1  2020-02-25  9  February  20
2  2020-01-01  8  January   20
3  2020-06-03 12  June      20
4  2020-01-01  6  January   20
5  2020-01-01  7  January   20
6  2020-04-15  9  April     20
7  2020-10-09  8  October   20
8  2020-10-09  4  October   20
9  2020-10-09  7  October   20
10 2020-06-03 10  June      20
11 2020-06-03  6  June      20
12 2020-10-09 10  October   20
13 2020-04-15 10  April     20
14 2020-10-09 10  October   20
15 2020-01-01 11  January   20
16 2020-04-15  7  April     20
17 2020-10-09  6  October   20
18 2020-04-15  7  April     20
19 2020-01-01  5  January   20
20 2020-10-09  4  October   20

Finding monthly average for data in df2 −

aggregate(y~Month+Year,df2,mean)

   Month    Year   y
1  April    20   8.250000
2  February 20   9.000000
3  January  20   7.400000
4  June     20  9.333333
5  October  20   7.000000
raja
Published on 06-Mar-2021 14:12:10
Advertisements