Finding the Weekday Average from numerical values in R

Question

So I have values like Mon 162 Tue 123 Wed 29 and so on. I need to find the average for all weekdays in R. I have tried filter and group_by but cannot get an answer.

     Time Day Count Speed
1   00:00 Sun   169  60.2
2   00:00 Mon    71  58.5
3   00:00 Tue    70  57.2
4   00:00 Wed    68  58.5
5   00:00 Thu    91  58.8
6   00:00 Fri    94  58.7
7   00:00 Sat   135  58.5
8   01:00 Sun   111  60.0
9   01:00 Mon    45  59.2
10  01:00 Tue    50  57.6

I need the out come to be Weekday Average = ####

Please post a small reproducible example https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Mouad_Seridi, May 07 '21 at 09:13

score 1 · Answer 1 · answered May 07 '21 at 09:26

This might do the trick

days <- c("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
d.f <- data.frame(Day = rep(days, 3), Speed = rnorm(21))

# split dataframe by days then take the mean over the speed
lapply(split(d.f, f=days), function(d) mean(d$Speed))

imdevskp · Accepted Answer · 2021-05-07T09:44:49.923

Let's say your df is

> df
# A tibble: 14 x 2
   Day   Count
   <chr> <dbl>
 1 Sun      31
 2 Mon      51
 3 Tue      21
 4 Wed      61
 5 Thu      31
 6 Fri      51
 7 Sat      65
 8 Sun      31
 9 Mon      13
10 Tue      61
11 Wed      72
12 Thu      46
13 Fri      62
14 Sat      13

You can use

df %>% 
  filter(!Day %in% c('Sun', 'Sat')) %>%
  group_by(Day) %>%
  summarize(mean(Count))

To get

# A tibble: 5 x 2
  Day   `mean(Count)`
  <chr>         <dbl>
1 Fri            56.5
2 Mon            32  
3 Thu            38.5
4 Tue            41  
5 Wed            66.5

For the average of all filtered values

df %>% 
  filter(!Day %in% c("Sun", "Sat")) %>%
  summarize("Average of all Weekday counts" = mean(Count))

Output

# A tibble: 1 x 1
  `Average of all Weekday counts`
                            <dbl>
1                            46.9

To get a numeric value instead of a tibble

df %>% 
  filter(!Day %in% c("Sun", "Sat")) %>%
  summarize("Average of all Weekday counts" = mean(Count)) %>%
  as.numeric()

Output

[1] 46.9

Thats really close how would I find the average of those values — Stubby1044, May 07 '21 at 09:36

score 0 · Answer 3 · answered May 07 '21 at 09:37

If you're looking for the single mean for just the weekdays, you could do something like this:

dat = data.frame(Time = rep(c("00:00","01:00"),c(7,3)),
                 Day = c("Sun","Mon","Tue","Wed","Thu","Fri","Sat","Sun","Mon","Tue"),
                 Count = c(169,71,70,68,91,94,135,111,45,50),
                 Speed = c(60.2,58.5,57.2,58.5,58.8,58.7,58.5,60.0,59.2,57.6))

mean(dat$Count[dat$Day %in% c("Mon","Tue","Wed","Thu","Fri")])
# [1] 69.85714

If, on the other hand, you're looking for the mean across each individual day then you could do this using base R:

aggregate(dat$Count, by=list(dat$Day), FUN = mean)
#   Group.1   x
# 1     Fri  94
# 2     Mon  58
# 3     Sat 135
# 4     Sun 140
# 5     Thu  91
# 6     Tue  60
# 7     Wed  68

It looks like you've tried dplyr, so the syntax for that same operation in dplyr would be:

library(dplyr)

dat %>% group_by(Day) %>% summarize(mean_count = mean(Count))
#  Day   mean_count
#  <chr>      <dbl>
# 1 Fri           94
# 2 Mon           58
# 3 Sat          135
# 4 Sun          140
# 5 Thu           91
# 6 Tue           60
# 7 Wed           68

And if you want to do the same thing in data.table you would do this:

library(data.table)

as.data.table(dat)[,.(mean_count = mean(Count)), by = Day]
#    Day mean_count
# 1: Sun        140
# 2: Mon         58
# 3: Tue         60
# 4: Wed         68
# 5: Thu         91
# 6: Fri         94
# 7: Sat        135

Finding the Weekday Average from numerical values in R

3 Answers3