K. Wamae
Reputation: 225
Unexpected output from dplyr (if_else statement)
I am having trouble trying to figure out why if_else is behaving the way it is, it may be my code or the way the data is structured.
Below is a snapshot of a database am working on and it represents a longitudinal survey of study participants in a trial with weekly follow up.
The variable "survey_start" represents the start of the study-defined one year follow up (which we called "survey_year").
I am trying to populate all subsequent entries for each participant, per survey year, with the entry "survey" followed by an underscore and the respective year, eg. survey_2014.
There are missing entries such as the participant represented here, wasn't available at the start of the 2015 survey.
I have written two codes, first one fails while the second works, the only difference being I have reversed the order in which the entries are populated in the second code (from 2007-2016 to 2016-2007) and removed the if_else statement for 2015.
Kindly assist in figuring this out...
trialData <- structure(list(study = c("site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1", "site_1", "site_1", "site_1", "site_1", "site_1",
"site_1", "site_1"), studyno = c("child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1", "child_1", "child_1", "child_1", "child_1",
"child_1", "child_1"), date = structure(c(16078, 16085, 16092,
16098, 16104, 16115, 16121, 16129, 16135, 16140, 16146, 16156,
16162, 16168, 16177, 16185, 16191, 16195, 16203, 16210, 16217,
16225, 16234, 16237, 16246, 16253, 16262, 16269, 16278, 16283,
16288, 16297, 16304, 16311, 16319, 16326, 16332, 16337, 16346,
16353, 16360, 16366, 16370, 16381, 16384, 16395, 16399, 16407,
16415, 16422, 16444, 16452, 16454, 16467, 16474, 16477, 16484,
16490, 16501, 16508, 16514, 16520, 16529, 16533, 16539, 16550,
16556, 16564, 16566, 16578, 16582, 16593, 16599, 16604, 16613,
16620, 16623, 16635, 16636, 16654, 16660, 16666, 16673, 16681,
16688, 16693, 16702, 16706, 16714, 16721, 16728, 16734, 16745,
16749, 16757, 16764, 16769, 16778, 16785, 16792, 16805, 16812,
16819, 16830, 16832, 16839, 16846, 16856, 16862, 16867, 16877,
16884, 16890, 16898, 16904, 16912, 16917, 16923, 16936, 16938,
16953, 16960, 16966, 16973, 16980), class = "Date"), year = c(2014L,
2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L,
2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L,
2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L,
2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L,
2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L, 2014L,
2014L, 2014L, 2014L, 2014L, 2015L, 2015L, 2015L, 2015L, 2015L,
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L,
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L,
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L,
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L,
2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L, 2015L,
2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L,
2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L,
2016L, 2016L, 2016L, 2016L, 2016L, 2016L, 2016L), month = c(1L,
1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 5L,
5L, 5L, 5L, 5L, 6L, 6L, 6L, 6L, 7L, 7L, 7L, 7L, 8L, 8L, 8L, 8L,
8L, 9L, 9L, 9L, 9L, 10L, 10L, 10L, 10L, 10L, 11L, 11L, 11L, 11L,
12L, 12L, 12L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L,
4L, 4L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 6L, 6L, 6L, 6L, 7L, 7L, 7L,
7L, 8L, 8L, 8L, 8L, 9L, 9L, 9L, 9L, 9L, 10L, 10L, 10L, 10L, 11L,
11L, 11L, 11L, 11L, 12L, 12L, 12L, 1L, 1L, 1L, 1L, 2L, 2L, 2L,
2L, 3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 5L, 5L, 5L, 6L, 6L, 6L,
6L, 6L), survey_start = c("", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "Y", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "",
"", "", "", "", "", "", "Y", "", "", "", "", "", "", "", "",
"", "", "", "", "", "")), class = "data.frame", row.names = c(NA,
-125L), .Names = c("study", "studyno", "date", "year", "month",
"survey_start"))
code 1 fails:
trialData <- trialData %>% arrange(studyno, date) %>% group_by(studyno) %>%
mutate(survey_year = if_else(date >= date[survey_start == "Y" & year == 2007 & study == "site_1"][1] & date < date[month == 5 & year == 2008 & study == "site_1"][1], "survey_2007",
if_else(date >= date[survey_start == "Y" & year == 2008 & study == "site_1"][1] & date < date[month == 4 & year == 2009 & study == "site_1"][1], "survey_2008",
if_else(date >= date[survey_start == "Y" & year == 2009 & study == "site_1"][1] & date < date[month == 5 & year == 2010 & study == "site_1"][1], "survey_2009",
if_else(date >= date[survey_start == "Y" & year == 2010 & study == "site_1"][1] & date < date[month == 5 & year == 2011 & study == "site_1"][1], "survey_2010",
if_else(date >= date[survey_start == "Y" & year == 2011 & study == "site_1"][1] & date < date[month == 4 & year == 2012 & study == "site_1"][1], "survey_2011",
if_else(date >= date[survey_start == "Y" & year == 2012 & study == "site_1"][1] & date < date[month == 4 & year == 2013 & study == "site_1"][1], "survey_2012",
if_else(date >= date[survey_start == "Y" & year == 2013 & study == "site_1"][1] & date < date[month == 4 & year == 2014 & study == "site_1"][1], "survey_2013",
if_else(date >= date[survey_start == "Y" & year == 2014 & study == "site_1"][1] & date < date[month == 4 & year == 2015 & study == "site_1"][1], "survey_2014",
if_else(date >= date[survey_start == "Y" & year == 2015 & study == "site_1"][1] & date < date[month == 3 & year == 2016 & study == "site_1"][1], "survey_2015",
if_else(date >= date[survey_start == "Y" & year == 2016 & study == "site_1"][1], "survey_2016","")))))))))))
code 2 works:
trialData <- trialData %>% arrange(studyno, date) %>% group_by(studyno) %>%
mutate(survey_year = if_else(date >= date[survey_start == "Y" & year == 2016 & study == "site_1"][1] , "survey_2016",
if_else(date >= date[survey_start == "Y" & year == 2014 & study == "site_1"][1] & date < date[month == 4 & year == 2015 & study == "site_1"][1], "survey_2014",
if_else(date >= date[survey_start == "Y" & year == 2013 & study == "site_1"][1] & date < date[month == 4 & year == 2014 & study == "site_1"][1], "survey_2013",
if_else(date >= date[survey_start == "Y" & year == 2012 & study == "site_1"][1] & date < date[month == 4 & year == 2013 & study == "site_1"][1], "survey_2012",
if_else(date >= date[survey_start == "Y" & year == 2011 & study == "site_1"][1] & date < date[month == 4 & year == 2012 & study == "site_1"][1], "survey_2011",
if_else(date >= date[survey_start == "Y" & year == 2010 & study == "site_1"][1] & date < date[month == 5 & year == 2011 & study == "site_1"][1], "survey_2010",
if_else(date >= date[survey_start == "Y" & year == 2009 & study == "site_1"][1] & date < date[month == 5 & year == 2010 & study == "site_1"][1], "survey_2009",
if_else(date >= date[survey_start == "Y" & year == 2008 & study == "site_1"][1] & date < date[month == 4 & year == 2009 & study == "site_1"][1], "survey_2008",
if_else(date >= date[survey_start == "Y" & year == 2007 & study == "site_1"][1] & date < date[month == 5 & year == 2008 & study == "site_1"][1], "survey_2007",""))))))))))
Upvotes: 0
Views: 109
Answers (1)
Mikko Marttila
Reputation: 11933
As @akrun commented, you can accomplish this by merging data rather than using if_else. The process goes something along these lines:
- Create a data set containing only the visits that start a survey year.
- define the start and end dates, along with the survey year label here
- Join the starting visits data to the original data
- keep the rows that fall within a survey year
- select only the columns needed to identify a visit, and the survey year label
- Join the result back to the original data.
And here's how you could go about doing that using dplyr:
library(tidyverse)
library(lubridate)
# Modify the data so that there's an overlap of survey years,
# in order to demonstrate how to deal with it
df <- as_tibble(trialData) %>%
mutate(survey_start = if_else(row_number() == 52, "Y", survey_start))
# Pick out rows that start a "survey year"
starts <- df %>%
filter(survey_start == "Y") %>%
group_by(study, studyno) %>%
transmute(
survey_year = str_c("survey_", year),
start_date = date,
end_date = pmin(
start_date + years(1), # make sure that the survey year
lead(start_date), # ends before next one starts
na.rm = T
)
) %>% ungroup()
#> Adding missing grouping variables: `study`, `studyno`
# Join all starts to the visit data
years <- df %>%
left_join(starts) %>%
# Keep rows which fall within one year of a start
filter(date >= start_date, date < end_date) %>%
select(study, studyno, date, survey_year)
#> Joining, by = c("study", "studyno")
Now years contains all visits that fall within a "survey year"
# Join the year classifications to the original data
result <- df %>%
left_join(years)
#> Joining, by = c("study", "studyno", "date")
stopifnot(nrow(result) == nrow(df))
We can also check the result:
# Check the rows before and after each start
i <- which(result$survey_start == "Y")
result %>% slice(sort(c(i - 1, i, i + 1)))
#> # A tibble: 9 x 7
#> study studyno date year month survey_start survey_year
#> <chr> <chr> <date> <int> <int> <chr> <chr>
#> 1 site_1 child_1 2014-05-01 2014 5 "" <NA>
#> 2 site_1 child_1 2014-05-05 2014 5 Y survey_2014
#> 3 site_1 child_1 2014-05-13 2014 5 "" survey_2014
#> 4 site_1 child_1 2015-01-09 2015 1 "" survey_2014
#> 5 site_1 child_1 2015-01-17 2015 1 Y survey_2015
#> 6 site_1 child_1 2015-01-19 2015 1 "" survey_2015
#> 7 site_1 child_1 2016-03-07 2016 3 "" <NA>
#> 8 site_1 child_1 2016-03-17 2016 3 Y survey_2016
#> 9 site_1 child_1 2016-03-24 2016 3 "" survey_2016
Created on 2018-02-22 by the reprex package (v0.2.0).
Upvotes: 2
Related Questions
- Issues with multiple nested if-else in R
- R weird {if-else} behaviour
- R: Troubles with ifelse
- Weird Behaviour in R if else Statement
- IF_ELSE statement not working as expected
- Different behavior of if else statement and if_else
- if-else is not producing the expected output in R
- nested if_else in dplyr mutate_ behaving unexpectedly
- ifelse dplyr showing wrong output
- Why does dplyr error in this nested if_else, when logical condition means output should not be evaluated?