I have a longitudinal dataset containing individuals along with information about where they are currently residing. The code below creates an example df:
set.seed(123)
df <- tibble(
id = c(1, 2, 3, 4, 5,
1, 2, 3, 5, 6, 7,
2, 3, 4, 6, 7, 8,
1, 2, 3, 4, 6, 7, 8
),
year = c(rep(2009, 5),
rep(2010, 6),
rep(2011, 6),
rep(2012, 7)),
age = c(0, 0, 0, 0, 0,
1, 1, 1, 1, 0, 1,
2, 2, 2, 1, 2, 2,
3, 3, 3, 3, 2, 3, 3),
town = c("0", "0", "2", "0", "0",
"1", "2", "1", "3", "0", "1",
"3", "1", "4", "1", "2", "1",
"4", "2", "2", "1", "2", "1", "5")
)
I'm interested in reasons for moving (e.g., whether income, attained education, family structure etc plays a role in whether you move at all, and whether it affects the area you move to), so I've coded the event, "moved", along with "flag_first_move" using the following code:
df3 <- df %>%
arrange(year, id) %>%
group_by(id) %>%
mutate(first_year = min(year)) %>%
mutate(first_town = list(town[year==first_year])) %>%
mutate(flag_move = as.numeric(year != first_year & !(town %in% unlist(first_town)) & town !="")) %>%
mutate(flag_first_move = (flag_move==1 & as.numeric(!duplicated(flag_move)))) %>%
mutate(moved = case_when(town !=lag(town) ~ 1,
TRUE ~ 0)) %>%
mutate(flag_cum_move = (cumsum(c(0, diff(moved)) !=0) 1)) #This doesn't work as intended
"flag_first_move" gives me the first event of a move. "moved" gives me a flag for every time an individual move. Lastly, with the attempt of creating the variable "flag_cum_move", I want a cumulative count for every event (so that every time an individual moves it adds 1) - I can't figure out how to do this!
Lastly, I want to look at the year before and after every event (move) for every individual. This is the code I've tried to accomplish this task:
df4 <- df3 %>%
group_by(id) %>%
filter(any(flag_first_move == 1)) %>%
mutate(
year_before = ifelse(
between(year[moved == 1] - year, 1, 1), 1, 0),
year_after = ifelse(
between(year - year[moved == 1], 1, 1), 1, 0),
)
It works fine in the cases where only one event occurs, but in the case where multiple events follow for every year it gives me a warning for the "year_after" variable, and I don't get the intended result for this neither. I can't figure out why.
CodePudding user response：
See if this is what you want. It's better if you could show the expected output in the question, so if I have misunderstood something please note in the comments below and I can adjust accordingly.
library(tidyverse)
df <- tibble(
id = c(
1, 2, 3, 4, 5,
1, 2, 3, 5, 6, 7,
2, 3, 4, 6, 7, 8,
1, 2, 3, 4, 6, 7, 8
),
year = c(
rep(2009, 5),
rep(2010, 6),
rep(2011, 6),
rep(2012, 7)
),
age = c(
0, 0, 0, 0, 0,
1, 1, 1, 1, 0, 1,
2, 2, 2, 1, 2, 2,
3, 3, 3, 3, 2, 3, 3
),
town = c(
"0", "0", "2", "0", "0",
"1", "2", "1", "3", "0", "1",
"3", "1", "4", "1", "2", "1",
"4", "2", "2", "1", "2", "1", "5"
)
)
df3 <- df %>%
arrange(id, year) %>%
group_by(id) %>%
mutate(
first_year = min(year),
first_town = if_else(year == first_year, town, NA_character_)
) %>%
fill(first_town) %>%
mutate(
flag_move = if_else(year != first_year & town != first_town, 1, 0),
flag_first_move = if_else(cumsum(flag_move) == 1, 1, 0),
moved = if_else(town != lag(town), 1, 0)
) %>%
replace_na(list(moved = 0)) %>%
mutate(flag_cum_move = cumsum(moved))
df4 <- df3 %>%
filter(any(flag_first_move == 1)) %>%
mutate(
year_before = if_else(lead(moved) == 1, 1, 0),
year_after = if_else(lag(moved) == 1, 1, 0)
) %>%
ungroup()
df4
#> # A tibble: 24 × 12
#> id year age town first_year first_town flag_move flag_first_move moved
#> <dbl> <dbl> <dbl> <chr> <dbl> <chr> <dbl> <dbl> <dbl>
#> 1 1 2009 0 0 2009 0 0 0 0
#> 2 1 2010 1 1 2009 0 1 1 1
#> 3 1 2012 3 4 2009 0 1 0 1
#> 4 2 2009 0 0 2009 0 0 0 0
#> 5 2 2010 1 2 2009 0 1 1 1
#> 6 2 2011 2 3 2009 0 1 0 1
#> 7 2 2012 3 2 2009 0 1 0 1
#> 8 3 2009 0 2 2009 2 0 0 0
#> 9 3 2010 1 1 2009 2 1 1 1
#> 10 3 2011 2 1 2009 2 1 0 0
#> # … with 14 more rows, and 3 more variables: flag_cum_move <dbl>,
#> # year_before <dbl>, year_after <dbl>
Created on 2022-06-22 by the reprex package (v2.0.1)
CodePudding user response：
here is a data.table approach
library(data.table)
setDT(df, key = c("id", "year"))
df[, moved := ifelse(town == shift(town, type = "lag", fill = town[1]), 0, 1), keyby = id]
df[, cummoved := cumsum(moved), keyby = id]
df[, firstmove := 0][cummoved == 1 & moved == 1, firstmove := 1][]
#
# id year age town moved cummoved firstmove
# 1: 1 2009 0 0 0 0 0
# 2: 1 2010 1 1 1 1 1
# 3: 1 2012 3 4 1 2 0
# 4: 2 2009 0 0 0 0 0
# 5: 2 2010 1 2 1 1 1
# 6: 2 2011 2 3 1 2 0
# 7: 2 2012 3 2 1 3 0
# 8: 3 2009 0 2 0 0 0
# 9: 3 2010 1 1 1 1 1
# 10: 3 2011 2 1 0 1 0
# 11: 3 2012 3 2 1 2 0
# 12: 4 2009 0 0 0 0 0
# 13: 4 2011 2 4 1 1 1
# 14: 4 2012 3 1 1 2 0
# 15: 5 2009 0 0 0 0 0
# 16: 5 2010 1 3 1 1 1
# 17: 6 2010 0 0 0 0 0
# 18: 6 2011 1 1 1 1 1
# 19: 6 2012 2 2 1 2 0
# 20: 7 2010 1 1 0 0 0
# 21: 7 2011 2 2 1 1 1
# 22: 7 2012 3 1 1 2 0
# 23: 8 2011 2 1 0 0 0
# 24: 8 2012 3 5 1 1 1
# id year age town moved cummoved firstmove