Using dplyr mutate function to create new variable conditionally based on current row

user_13667530 0
Viewed 96

I am working on creating conditional averages for a large data set that involves # of flu cases seen during the week for several years. The data is organized as such:

Data Table

What I want to do is create a new column that tabulates that average number of cases for that same week in previous years. For instance, for the row where Week.Number is 1 and Flu.Year is 2017, I would like the new row to give the average count for any year with Week.Number==1 & Flu.Year<2017. Normally, I would use the case_when() function to conditionally tabulate something like this. For instance, when calculating the average weekly volume I used this code:

   mutate(average = case_when(
    Flu.Year==2016 ~ mean(chcc$count[chcc$Flu.Year==2016]),
    Flu.Year==2017 ~ mean(chcc$count[chcc$Flu.Year==2017]),
    Flu.Year==2018 ~ mean(chcc$count[chcc$Flu.Year==2018]),
    Flu.Year==2019 ~ mean(chcc$count[chcc$Flu.Year==2019]),
  ),

However, since there are four years of data * 52 weeks which is a lot of iterations to spell out the conditions for. Is there a way to elegantly code this in dplyr? The problem I keep running into is that I want to call values in counts column based on Week.Number and Flu.Year values in other rows conditioned on the current value of Week.Number and Flu.Year, and I am not sure how to accomplish that. Please let me know if there is further information / detail I can provide.

Thanks, Steven

dat <- tibble( Flu.Year = rep(2016:2019,each = 52), Week.Number = rep(1:52,4), count = sample(1000, size=52*4, replace=TRUE) )
dplyr conditional-statements r
2 Answers

It's bad-form and, in some cases, an error when you use $-indexing within dplyr verbs. I think a better way to get that average field is to group_by(Flu.Year) and calculate it straight-up.

library(dplyr)
set.seed(42)
dat <- tibble(
  Flu.Year = sample(2016:2020, size=100, replace=TRUE),
  count = sample(1000, size=100, replace=TRUE)
)

dat %>%
  group_by(Flu.Year) %>%
  mutate(average = mean(count)) %>%
  # just to show a quick summary
  slice(1:3) %>%
  ungroup()
# # A tibble: 15 x 3
#    Flu.Year count average
#       <int> <int>   <dbl>
#  1     2016   734    578.
#  2     2016   356    578.
#  3     2016   411    578.
#  4     2017   217    436.
#  5     2017   453    436.
#  6     2017   920    436.
#  7     2018   963    558 
#  8     2018   609    558 
#  9     2018   536    558 
# 10     2019   943    543.
# 11     2019   740    543.
# 12     2019   536    543.
# 13     2020   627    494.
# 14     2020   218    494.
# 15     2020   389    494.

An alternative approach is to generate a summary table (just one row per year) and join it back in to the original data.

dat %>%
  group_by(Flu.Year) %>%
  summarize(average = mean(count))
# # A tibble: 5 x 2
#   Flu.Year average
#      <int>   <dbl>
# 1     2016    578.
# 2     2017    436.
# 3     2018    558 
# 4     2019    543.
# 5     2020    494.

dat %>%
  group_by(Flu.Year) %>%
  summarize(average = mean(count)) %>%
  full_join(dat, by = "Flu.Year")
# # A tibble: 100 x 3
#    Flu.Year average count
#       <int>   <dbl> <int>
#  1     2016    578.   734
#  2     2016    578.   356
#  3     2016    578.   411
#  4     2016    578.   720
#  5     2016    578.   851
#  6     2016    578.   822
#  7     2016    578.   465
#  8     2016    578.   679
#  9     2016    578.    30
# 10     2016    578.   180
# # ... with 90 more rows

The result, after chat:

tibble( Flu.Year = rep(2016:2018,each = 3), Week.Number = rep(1:3,3), count = 1:9 )  %>%
  arrange(Flu.Year, Week.Number) %>%
  group_by(Week.Number) %>%
  mutate(year_week.average = lag(cumsum(count) / seq_along(count)))
# # A tibble: 9 x 4
# # Groups:   Week.Number [3]
#   Flu.Year Week.Number count year_week.average
#      <int>       <int> <int>             <dbl>
# 1     2016           1     1              NA  
# 2     2016           2     2              NA  
# 3     2016           3     3              NA  
# 4     2017           1     4               1  
# 5     2017           2     5               2  
# 6     2017           3     6               3  
# 7     2018           1     7               2.5
# 8     2018           2     8               3.5
# 9     2018           3     9               4.5
user_33582720

We can use aggregate from base R

aggregate(count ~ Flu.Year, data, FUN = mean)
user_37322710
Related