Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to find the monthly average from different date data in R?
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 −
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)
df1Output
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
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)
df2Output
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
Updated on: 06-Mar-2021
2K+ Views
Advertisements