Hello! I am new to R and I am working on a project that requires me to calculated the difference in new cases two weeks from the date that a mask mandate was enforced. My dataset consist of state, date, mandate (yes/no), daily new cases. Therefore, if mandate = yes, then I need to calculated the difference in new cases two weeks from the date that the mandate was placed. I am not sure how to code this, much help will be appreciated.
Best,
xbechtel
What have you tried? Can you provide a reproducible example?
A portion of my dataset looks like this
masks_df = read_excel("./data/mask_state.xlsx") %>%
janitor::clean_names() %>%
mutate(date = as.Date(as.numeric(as.character(date)), origin="1899-12-30")) %>%
select(-x4)
Mask date state new_case
NA 2020-07-15 AL 1236
NA 2020-07-16 AL 1254
NA 2020-07-17 AL 1579
Yes 2020-07-18 AL 2003
NA 2020-07-19 AL 1745
NA 2020-07-20 AL 1945
NA 2020-07-21 AL 2114
NA 2020-07-22 AL 1819
NA 2020-07-23 AL 1126
NA 2020-07-24 AL 1282
The dataset is grouped by state and there is a date for everyday beginning from 2020-01-20. I wasn't sure if it would be best to use a function or mutate from the dplyr package.
can you provide the output of dput(masks_df) or dput(head(masks_df))?
This is the output
structure(list(mask = c(NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_), date = structure(c(18327,
18328, 18329, 18330, 18331, 18332), class = "Date"), state = c("AL",
"AL", "AL", "AL", "AL", "AL"), new_case = c(NA_real_, NA_real_,
NA_real_, NA_real_, NA_real_, NA_real_)), row.names = c(NA, -6L
), groups = structure(list(state = "AL", .rows = structure(list(
1:6), ptype = integer(0), class = c("vctrs_list_of", "vctrs_vctr",
"list"))), row.names = 1L, class = c("tbl_df", "tbl", "data.frame"
), .drop = TRUE), class = c("grouped_df", "tbl_df", "tbl", "data.frame"
))
For there first 13 days, there were no new cases
There are only six days in that code with no cases.
> masks_df
# A tibble: 6 x 4
# Groups: state [1]
mask date state new_case
<chr> <date> <chr> <dbl>
1 NA 2020-03-06 AL NA
2 NA 2020-03-07 AL NA
3 NA 2020-03-08 AL NA
4 NA 2020-03-09 AL NA
5 NA 2020-03-10 AL NA
6 NA 2020-03-11 AL NA
Can you provide the data for the dates that you had above (July)?
dput(head(masks_df, n = 20))
structure(list(mask = c(NA_character_, NA_character_, NA_character_,
NA_character_, "Yes", NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_,
NA_character_, NA_character_), date = structure(c(18334, 18335,
18336, 18337, 18338, 18339, 18340, 18341, 18342, 18343, 18344,
18345, 18346, 18347, 18348, 18349, 18350, 18351, 18352, 18353
), class = "Date"), state = c("AL", "AL", "AL", "AL", "AL", "AL",
"AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL", "AL",
"AL", "AL", "AL"), new_case = c(1, 5, 6, 16, 8, 10, 22, 13, 43,
14, 29, 48, 68, 223, 81, 109, 110, 53, 122, 96)), row.names = c(NA,
-20L), groups = structure(list(state = "AL", .rows = structure(list(
1:20), ptype = integer(0), class = c("vctrs_list_of", "vctrs_vctr",
"list"))), row.names = 1L, class = c("tbl_df", "tbl", "data.frame"
), .drop = TRUE), class = c("grouped_df", "tbl_df", "tbl", "data.frame"
))
Does this work, it has the first 20 observations?
What are you after? When there is a 'Yes' e.g. 2020-03-17, what should there be?
library(tidyverse)
masks_df %>%
mutate(two_weeks_ago = lag(new_case, 14)) %>%
mutate(mask_mandate_14 = lag(mask, 14)) %>%
mutate(diff = two_weeks_ago - new_case)
# A tibble: 20 x 7
# Groups: state [1]
mask date state new_case two_weeks_ago mask_mandate_14 diff
<chr> <date> <chr> <dbl> <dbl> <chr> <dbl>
1 NA 2020-03-13 AL 1 NA NA NA
2 NA 2020-03-14 AL 5 NA NA NA
3 NA 2020-03-15 AL 6 NA NA NA
4 NA 2020-03-16 AL 16 NA NA NA
5 Yes 2020-03-17 AL 8 NA NA NA
6 NA 2020-03-18 AL 10 NA NA NA
7 NA 2020-03-19 AL 22 NA NA NA
8 NA 2020-03-20 AL 13 NA NA NA
9 NA 2020-03-21 AL 43 NA NA NA
10 NA 2020-03-22 AL 14 NA NA NA
11 NA 2020-03-23 AL 29 NA NA NA
12 NA 2020-03-24 AL 48 NA NA NA
13 NA 2020-03-25 AL 68 NA NA NA
14 NA 2020-03-26 AL 223 NA NA NA
15 NA 2020-03-27 AL 81 1 NA -80
16 NA 2020-03-28 AL 109 5 NA -104
17 NA 2020-03-29 AL 110 6 NA -104
18 NA 2020-03-30 AL 53 16 NA -37
19 NA 2020-03-31 AL 122 8 Yes -114
20 NA 2020-04-01 AL 96 10 NA -86
I would like to calculate the difference between new cases two weeks from the date when there is a "yes" for mask (when a mask mandate was enforced). Therefore, it should only be one number. If mask is yes, calculate the difference in new cases from the date two weeks from when the mandate was enforced.
Like this?
library(tidyverse)
masks_df %>%
mutate(in_two_weeks_mask = if_else(mask == "Yes", lead(new_case, 14), NA_real_)) %>%
mutate(diff = in_two_weeks_mask - new_case)
# A tibble: 20 x 6
# Groups: state [1]
mask date state new_case in_two_weeks_mask diff
<chr> <date> <chr> <dbl> <dbl> <dbl>
1 NA 2020-03-13 AL 1 NA NA
2 NA 2020-03-14 AL 5 NA NA
3 NA 2020-03-15 AL 6 NA NA
4 NA 2020-03-16 AL 16 NA NA
5 Yes 2020-03-17 AL 8 122 114
6 NA 2020-03-18 AL 10 NA NA
7 NA 2020-03-19 AL 22 NA NA
8 NA 2020-03-20 AL 13 NA NA
9 NA 2020-03-21 AL 43 NA NA
10 NA 2020-03-22 AL 14 NA NA
11 NA 2020-03-23 AL 29 NA NA
12 NA 2020-03-24 AL 48 NA NA
13 NA 2020-03-25 AL 68 NA NA
14 NA 2020-03-26 AL 223 NA NA
15 NA 2020-03-27 AL 81 NA NA
16 NA 2020-03-28 AL 109 NA NA
17 NA 2020-03-29 AL 110 NA NA
18 NA 2020-03-30 AL 53 NA NA
19 NA 2020-03-31 AL 122 NA NA
20 NA 2020-04-01 AL 96 NA NA
Yes! Thank you so much!
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
If you have a query related to it or one of the replies, start a new topic and refer back with a link.