Establishing Transition Counts in R

t.hariprabhath 0
Viewed 24

I have a data that looks like this

PTNUM STATE AGE WAVE
12345 1 17 1
12345 1 20 2
12345 2 26 3
12345 3 31 4
12345 2 37 5
12346 1 15 1
12346 1 23 2
12346 1 28 3
12346 1 33 4
12346 1 41 5
12347 1 19 1
12347 2 23 2
12347 2 29 3
12347 3 32 4
12347 3 39 5
12348 1 17 1
12348 2 22 2
12348 3 29 3
12348 2 31 4
12348 3 40 5
12349 1 12 1
12349 2 19 2
12349 2 24 3
12349 2 30 4
12349 3 36 5

I would like to establish the transition of states from 1 to 2, 2 to 3 and 3 to 2 i.e., the age they are at 1 and and the age when they move to 2 similarly for other transitions and have them in a table for each individual. how can this be done?

expected output:

PTNUM 1-2 2-3 3-2
12345 (20,26) (26,31) (31,37)
12346 NA NA NA
12347 (19,23) (29,32) NA
12348 (17,22) (22,29) (29,31)
12349 (12,19) (30,36) NA

help is much appreciated.

r
1 Answers

Here is an option

library(dplyr)
df1 %>% 
  group_by(PTNUM) %>% 
  mutate(AGE12 = case_when(STATE == 1 & lead(STATE) == 2| 
     STATE == 2 & lag(STATE) == 1 ~ AGE), 
     AGE23 =case_when(STATE == 2 & lead(STATE) == 3| STATE == 3 &
        lag(STATE) == 2 ~ AGE), 
    AGE32 =case_when(STATE == 3 & lead(STATE) == 2| STATE == 2 &
      lag(STATE) == 3 ~ AGE) ) %>% 
   summarise(across(matches("AGE\\d+"),
    ~ if(all(is.na(.x))) NA else sprintf("(%s)", 
    toString(na.omit(.x)))), .groups = 'drop')
akrun0
Related