Mixing
dplyr behaviour within case_when and lag
I have a dataset which has studyid, year, and two flags: incident and prevalent.
I wish the prevalent variable to be TRUE (1) for all years after the incident flag is true (and the incident variable can be true only once). case_when and lag seem the perfect combination, but if incident is set to 1 in year N, prevalent is set to TRUE only in N+1, and reverse to 0 in N+1. This was not the expected behaviour.
Here is sample code:
library(tidyverse)
# make a fake dataset
testdat <- tribble(
~studyid, ~datestring, ~incident,
"1", "2000-01-01", 0,
"1", "2001-01-01", 1,
"1", "2002-01-01", 0,
"1", "2003-01-01", 0,
"2", "2003-01-01", 0,
"2", "2004-01-01", 1,
"2", "2005-01-01", 0,
"2", "2006-01-01", 0
) %>% mutate(
prevalent = 0,
date = lubridate::ymd(datestring)
) %>% group_by(studyid) %>%
arrange(studyid, date) %>%
mutate(prevalent = case_when(
#logic is, if prevalent in year N-1, the prevalent in year N
# if incident in year N-1, then prevalent in year N
# otherwise not prevalent (because never incident)
dplyr::lag(prevalent, 1L)==1 ~1,
dplyr::lag(incident, 1L)==1 ~1,
TRUE ~ 0
) #close case_when
) #close mutate
testdat
Output is:
# A tibble: 8 x 5
# Groups: studyid [2]
studyid datestring incident prevalent date
<chr> <chr> <dbl> <dbl> <date>
1 1 2000-01-01 0 0 2000-01-01
2 1 2001-01-01 1 0 2001-01-01
3 1 2002-01-01 0 1 2002-01-01
4 1 2003-01-01 0 0 2003-01-01
5 2 2003-01-01 0 0 2003-01-01
6 2 2004-01-01 1 0 2004-01-01
7 2 2005-01-01 0 1 2005-01-01
8 2 2006-01-01 0 0 2006-01-01
>
Desired output is:
studyid=1, year=2003 prevalent ==1 (not 0)
studyid=2, year=2006 prevalent ==1 (not 0)
I suspect this has to do with how case_when is interacting with dplyr::lag.
How can I improve the logic/syntax to obtain the needed results?
Many thanks,
r dplyr lag
asked Jan 1 at 20:24
user2292410user2292410
146110
add a comment |
I have a dataset which has studyid, year, and two flags: incident and prevalent.
I wish the prevalent variable to be TRUE (1) for all years after the incident flag is true (and the incident variable can be true only once). case_when and lag seem the perfect combination, but if incident is set to 1 in year N, prevalent is set to TRUE only in N+1, and reverse to 0 in N+1. This was not the expected behaviour.
Here is sample code:
library(tidyverse)
# make a fake dataset
testdat <- tribble(
~studyid, ~datestring, ~incident,
"1", "2000-01-01", 0,
"1", "2001-01-01", 1,
"1", "2002-01-01", 0,
"1", "2003-01-01", 0,
"2", "2003-01-01", 0,
"2", "2004-01-01", 1,
"2", "2005-01-01", 0,
"2", "2006-01-01", 0
) %>% mutate(
prevalent = 0,
date = lubridate::ymd(datestring)
) %>% group_by(studyid) %>%
arrange(studyid, date) %>%
mutate(prevalent = case_when(
#logic is, if prevalent in year N-1, the prevalent in year N
# if incident in year N-1, then prevalent in year N
# otherwise not prevalent (because never incident)
dplyr::lag(prevalent, 1L)==1 ~1,
dplyr::lag(incident, 1L)==1 ~1,
TRUE ~ 0
) #close case_when
) #close mutate
testdat
Output is:
# A tibble: 8 x 5
# Groups: studyid [2]
studyid datestring incident prevalent date
<chr> <chr> <dbl> <dbl> <date>
1 1 2000-01-01 0 0 2000-01-01
2 1 2001-01-01 1 0 2001-01-01
3 1 2002-01-01 0 1 2002-01-01
4 1 2003-01-01 0 0 2003-01-01
5 2 2003-01-01 0 0 2003-01-01
6 2 2004-01-01 1 0 2004-01-01
7 2 2005-01-01 0 1 2005-01-01
8 2 2006-01-01 0 0 2006-01-01
>
Desired output is:
studyid=1, year=2003 prevalent ==1 (not 0)
studyid=2, year=2006 prevalent ==1 (not 0)
I suspect this has to do with how case_when is interacting with dplyr::lag.
How can I improve the logic/syntax to obtain the needed results?
Many thanks,
r dplyr lag
asked Jan 1 at 20:24
user2292410user2292410
146110
Test with an ifelse (with an OR condition) instead of a case when.
– anotherfred
Jan 1 at 21:05
@user2292410 Its doing what you desired. check the logic: do you want to check prevalent/ incident in any of the n-1 years ??
– Mankind_008
Jan 1 at 21:55
add a comment |
I have a dataset which has studyid, year, and two flags: incident and prevalent.
I wish the prevalent variable to be TRUE (1) for all years after the incident flag is true (and the incident variable can be true only once). case_when and lag seem the perfect combination, but if incident is set to 1 in year N, prevalent is set to TRUE only in N+1, and reverse to 0 in N+1. This was not the expected behaviour.
Here is sample code:
library(tidyverse)
# make a fake dataset
testdat <- tribble(
~studyid, ~datestring, ~incident,
"1", "2000-01-01", 0,
"1", "2001-01-01", 1,
"1", "2002-01-01", 0,
"1", "2003-01-01", 0,
"2", "2003-01-01", 0,
"2", "2004-01-01", 1,
"2", "2005-01-01", 0,
"2", "2006-01-01", 0
) %>% mutate(
prevalent = 0,
date = lubridate::ymd(datestring)
) %>% group_by(studyid) %>%
arrange(studyid, date) %>%
mutate(prevalent = case_when(
#logic is, if prevalent in year N-1, the prevalent in year N
# if incident in year N-1, then prevalent in year N
# otherwise not prevalent (because never incident)
dplyr::lag(prevalent, 1L)==1 ~1,
dplyr::lag(incident, 1L)==1 ~1,
TRUE ~ 0
) #close case_when
) #close mutate
testdat
Output is:
# A tibble: 8 x 5
# Groups: studyid [2]
studyid datestring incident prevalent date
<chr> <chr> <dbl> <dbl> <date>
1 1 2000-01-01 0 0 2000-01-01
2 1 2001-01-01 1 0 2001-01-01
3 1 2002-01-01 0 1 2002-01-01
4 1 2003-01-01 0 0 2003-01-01
5 2 2003-01-01 0 0 2003-01-01
6 2 2004-01-01 1 0 2004-01-01
7 2 2005-01-01 0 1 2005-01-01
8 2 2006-01-01 0 0 2006-01-01
>
Desired output is:
studyid=1, year=2003 prevalent ==1 (not 0)
studyid=2, year=2006 prevalent ==1 (not 0)
I suspect this has to do with how case_when is interacting with dplyr::lag.
How can I improve the logic/syntax to obtain the needed results?
Many thanks,
r dplyr lag
asked Jan 1 at 20:24
user2292410user2292410
146110
I have a dataset which has studyid, year, and two flags: incident and prevalent.
I wish the prevalent variable to be TRUE (1) for all years after the incident flag is true (and the incident variable can be true only once). case_when and lag seem the perfect combination, but if incident is set to 1 in year N, prevalent is set to TRUE only in N+1, and reverse to 0 in N+1. This was not the expected behaviour.
Here is sample code:
library(tidyverse)
# make a fake dataset
testdat <- tribble(
~studyid, ~datestring, ~incident,
"1", "2000-01-01", 0,
"1", "2001-01-01", 1,
"1", "2002-01-01", 0,
"1", "2003-01-01", 0,
"2", "2003-01-01", 0,
"2", "2004-01-01", 1,
"2", "2005-01-01", 0,
"2", "2006-01-01", 0
) %>% mutate(
prevalent = 0,
date = lubridate::ymd(datestring)
) %>% group_by(studyid) %>%
arrange(studyid, date) %>%
mutate(prevalent = case_when(
#logic is, if prevalent in year N-1, the prevalent in year N
# if incident in year N-1, then prevalent in year N
# otherwise not prevalent (because never incident)
dplyr::lag(prevalent, 1L)==1 ~1,
dplyr::lag(incident, 1L)==1 ~1,
TRUE ~ 0
) #close case_when
) #close mutate
testdat
Output is:
# A tibble: 8 x 5
# Groups: studyid [2]
studyid datestring incident prevalent date
<chr> <chr> <dbl> <dbl> <date>
1 1 2000-01-01 0 0 2000-01-01
2 1 2001-01-01 1 0 2001-01-01
3 1 2002-01-01 0 1 2002-01-01
4 1 2003-01-01 0 0 2003-01-01
5 2 2003-01-01 0 0 2003-01-01
6 2 2004-01-01 1 0 2004-01-01
7 2 2005-01-01 0 1 2005-01-01
8 2 2006-01-01 0 0 2006-01-01
>
Desired output is:
studyid=1, year=2003 prevalent ==1 (not 0)
studyid=2, year=2006 prevalent ==1 (not 0)
I suspect this has to do with how case_when is interacting with dplyr::lag.
How can I improve the logic/syntax to obtain the needed results?
Many thanks,
r dplyr lag
r dplyr lag
asked Jan 1 at 20:24
user2292410user2292410
146110
asked Jan 1 at 20:24
user2292410user2292410
146110
asked Jan 1 at 20:24
user2292410user2292410
146110
asked Jan 1 at 20:24
user2292410user2292410
146110
asked Jan 1 at 20:24
user2292410user2292410
146110
146110
Test with an ifelse (with an OR condition) instead of a case when.
– anotherfred
Jan 1 at 21:05
@user2292410 Its doing what you desired. check the logic: do you want to check prevalent/ incident in any of the n-1 years ??
– Mankind_008
Jan 1 at 21:55
add a comment |
Test with an ifelse (with an OR condition) instead of a case when.
– anotherfred
Jan 1 at 21:05
@user2292410 Its doing what you desired. check the logic: do you want to check prevalent/ incident in any of the n-1 years ??
– Mankind_008
Jan 1 at 21:55
Test with an ifelse (with an OR condition) instead of a case when.
– anotherfred
Jan 1 at 21:05
Test with an ifelse (with an OR condition) instead of a case when.
– anotherfred
Jan 1 at 21:05
@user2292410 Its doing what you desired. check the logic: do you want to check prevalent/ incident in any of the n-1 years ??
– Mankind_008
Jan 1 at 21:55
@user2292410 Its doing what you desired. check the logic: do you want to check prevalent/ incident in any of the n-1 years ??
– Mankind_008
Jan 1 at 21:55
add a comment |
1 Answer
1
active
oldest
votes
You are looking for something like the last observation carried forward e.g zoo::na.locf or tidyr::fill, but I'll use something simple like:
library(dplyr)
testdat %>%
mutate(date = lubridate::ymd(datestring)) %>% group_by(studyid) %>%
arrange(studyid, date) %>% mutate(prevalent=cumsum(lag(incident,default = 0)==1))
# A tibble: 8 x 5
# Groups: studyid [2]
studyid datestring incident date prevalent
<chr> <chr> <dbl> <date> <int>
1 1 2000-01-01 0 2000-01-01 0
2 1 2001-01-01 1 2001-01-01 0
3 1 2002-01-01 0 2002-01-01 1
4 1 2003-01-01 0 2003-01-01 1
5 2 2003-01-01 0 2003-01-01 0
6 2 2004-01-01 1 2004-01-01 0
7 2 2005-01-01 0 2005-01-01 1
8 2 2006-01-01 0 2006-01-01 1
answered Jan 1 at 23:09
A. SulimanA. Suliman
5,38441123
1
Most helpful, and not "simple". Never thought to use cumsum in this way. Many thanks!
– user2292410
Jan 1 at 23:48
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53998684%2fdplyr-behaviour-within-case-when-and-lag%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You are looking for something like the last observation carried forward e.g zoo::na.locf or tidyr::fill, but I'll use something simple like:
library(dplyr)
testdat %>%
mutate(date = lubridate::ymd(datestring)) %>% group_by(studyid) %>%
arrange(studyid, date) %>% mutate(prevalent=cumsum(lag(incident,default = 0)==1))
# A tibble: 8 x 5
# Groups: studyid [2]
studyid datestring incident date prevalent
<chr> <chr> <dbl> <date> <int>
1 1 2000-01-01 0 2000-01-01 0
2 1 2001-01-01 1 2001-01-01 0
3 1 2002-01-01 0 2002-01-01 1
4 1 2003-01-01 0 2003-01-01 1
5 2 2003-01-01 0 2003-01-01 0
6 2 2004-01-01 1 2004-01-01 0
7 2 2005-01-01 0 2005-01-01 1
8 2 2006-01-01 0 2006-01-01 1
answered Jan 1 at 23:09
A. SulimanA. Suliman
5,38441123
1
Most helpful, and not "simple". Never thought to use cumsum in this way. Many thanks!
– user2292410
Jan 1 at 23:48
add a comment |
You are looking for something like the last observation carried forward e.g zoo::na.locf or tidyr::fill, but I'll use something simple like:
library(dplyr)
testdat %>%
mutate(date = lubridate::ymd(datestring)) %>% group_by(studyid) %>%
arrange(studyid, date) %>% mutate(prevalent=cumsum(lag(incident,default = 0)==1))
# A tibble: 8 x 5
# Groups: studyid [2]
studyid datestring incident date prevalent
<chr> <chr> <dbl> <date> <int>
1 1 2000-01-01 0 2000-01-01 0
2 1 2001-01-01 1 2001-01-01 0
3 1 2002-01-01 0 2002-01-01 1
4 1 2003-01-01 0 2003-01-01 1
5 2 2003-01-01 0 2003-01-01 0
6 2 2004-01-01 1 2004-01-01 0
7 2 2005-01-01 0 2005-01-01 1
8 2 2006-01-01 0 2006-01-01 1
answered Jan 1 at 23:09
A. SulimanA. Suliman
5,38441123
1
Most helpful, and not "simple". Never thought to use cumsum in this way. Many thanks!
– user2292410
Jan 1 at 23:48
add a comment |
You are looking for something like the last observation carried forward e.g zoo::na.locf or tidyr::fill, but I'll use something simple like:
library(dplyr)
testdat %>%
mutate(date = lubridate::ymd(datestring)) %>% group_by(studyid) %>%
arrange(studyid, date) %>% mutate(prevalent=cumsum(lag(incident,default = 0)==1))
# A tibble: 8 x 5
# Groups: studyid [2]
studyid datestring incident date prevalent
<chr> <chr> <dbl> <date> <int>
1 1 2000-01-01 0 2000-01-01 0
2 1 2001-01-01 1 2001-01-01 0
3 1 2002-01-01 0 2002-01-01 1
4 1 2003-01-01 0 2003-01-01 1
5 2 2003-01-01 0 2003-01-01 0
6 2 2004-01-01 1 2004-01-01 0
7 2 2005-01-01 0 2005-01-01 1
8 2 2006-01-01 0 2006-01-01 1
answered Jan 1 at 23:09
A. SulimanA. Suliman
5,38441123
You are looking for something like the last observation carried forward e.g zoo::na.locf or tidyr::fill, but I'll use something simple like:
library(dplyr)
testdat %>%
mutate(date = lubridate::ymd(datestring)) %>% group_by(studyid) %>%
arrange(studyid, date) %>% mutate(prevalent=cumsum(lag(incident,default = 0)==1))
# A tibble: 8 x 5
# Groups: studyid [2]
studyid datestring incident date prevalent
<chr> <chr> <dbl> <date> <int>
1 1 2000-01-01 0 2000-01-01 0
2 1 2001-01-01 1 2001-01-01 0
3 1 2002-01-01 0 2002-01-01 1
4 1 2003-01-01 0 2003-01-01 1
5 2 2003-01-01 0 2003-01-01 0
6 2 2004-01-01 1 2004-01-01 0
7 2 2005-01-01 0 2005-01-01 1
8 2 2006-01-01 0 2006-01-01 1
answered Jan 1 at 23:09
A. SulimanA. Suliman
5,38441123
answered Jan 1 at 23:09
A. SulimanA. Suliman
5,38441123
answered Jan 1 at 23:09
A. SulimanA. Suliman
5,38441123
answered Jan 1 at 23:09
A. SulimanA. Suliman
5,38441123
5,38441123
1
Most helpful, and not "simple". Never thought to use cumsum in this way. Many thanks!
– user2292410
Jan 1 at 23:48
add a comment |
1
Most helpful, and not "simple". Never thought to use cumsum in this way. Many thanks!
– user2292410
Jan 1 at 23:48
1
1
Most helpful, and not "simple". Never thought to use cumsum in this way. Many thanks!
– user2292410
Jan 1 at 23:48
Most helpful, and not "simple". Never thought to use cumsum in this way. Many thanks!
– user2292410
Jan 1 at 23:48
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53998684%2fdplyr-behaviour-within-case-when-and-lag%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Test with an ifelse (with an OR condition) instead of a case when.
– anotherfred
Jan 1 at 21:05
@user2292410 Its doing what you desired. check the logic: do you want to check prevalent/ incident in any of the n-1 years ??
– Mankind_008
Jan 1 at 21:55