How to find the mean of a column grouped by date in R?

To find the mean of a column grouped by date, we can simply use aggregate function. For Example, if we have a data frame called df that contains a date column say Date and numerical column say Num then we can find the mean of Num by dates in the Date column by using the below command −

aggregate(Num~Date,df,mean)

Example 1

Following snippet creates a sample data frame −

Date<-
sample(c(as.Date("2021/05/01"),as.Date("2021/05/10"),as.Date("2021/05/15"),as.Date("2021/05/20")),20,replace=TRUE)
Sales<-sample(100:500,20)
df1<-data.frame(Date,Sales)
df1

The following dataframe is created

       Date  Sales
1  2021-05-15 221
2  2021-05-10 414
3  2021-05-20 469
4  2021-05-01 101
5  2021-05-20 313
6  2021-05-15 264
7  2021-05-01 302
8  2021-05-01 137
9  2021-05-15 109
10 2021-05-01 126
11 2021-05-20 498
12 2021-05-20 163
13 2021-05-01 446
14 2021-05-20 318
15 2021-05-15 180
16 2021-05-20 291
17 2021-05-10 390
18 2021-05-10 139
19 2021-05-15 337
20 2021-05-20 231

To find the mean of Sales by Date on the above created data frame, add the following code to the above snippet −

Date<-
sample(c(as.Date("2021/05/01"),as.Date("2021/05/10"),as.Date("2021/05/15"),as.Date("2021/05/20")),20,replace=TRUE)
Sales<-sample(100:500,20)
df1<-data.frame(Date,Sales)
aggregate(Sales~Date,df1,mean)

Output

If you execute all the above given snippets as a single program, it generates the following Output −

       Date   Sales
1 2021-05-01 222.4000
2 2021-05-10 314.3333
3 2021-05-15 222.2000
4 2021-05-20 326.1429

Example 2

Following snippet creates a sample data frame −

Date<-
sample(c(as.Date("2021/01/01"),as.Date("2021/02/15"),as.Date("2021/02/22"),as.Date("2021/01/18")),20,replace=TRUE)
Result<-sample(1:50,20)
df2<-data.frame(Date,Result)
df2

The following dataframe is created

      Date    Result
1  2021-01-01 40
2  2021-02-22 19
3  2021-01-18 15
4  2021-02-15 39
5  2021-02-15 35
6  2021-02-22  1
7  2021-02-22 18
8  2021-01-18 46
9  2021-01-01 23
10 2021-02-15 50
11 2021-02-22 12
12 2021-02-15 28
13 2021-02-15 48
14 2021-01-18 13
15 2021-02-15 11
16 2021-01-18 27
17 2021-01-01 22
18 2021-02-22 49
19 2021-02-15 45
20 2021-01-18 47

To find the mean of Result by Date on the above created data frame, add the following code to the above snippet −

Date<-
sample(c(as.Date("2021/01/01"),as.Date("2021/02/15"),as.Date("2021/02/22"),as.Date("2021/01/18")),20,replace=TRUE)
Result<-sample(1:50,20)
df2<-data.frame(Date,Result)
aggregate(Result~Date,df2,mean)

Output

If you execute all the above given snippets as a single program, it generates the following Output −

       Date   Result
1 2021-01-01 28.33333
2 2021-01-18 29.60000
3 2021-02-15 36.57143
4 2021-02-22 19.80000

Nizamuddin Siddiqui

e

Updated on: 05-Nov-2021

846 Views

Kickstart Your Career

Get certified by completing the course

Get Started