R - Transferring values from certain columns in certain rows to matched IDs from propensity score matching

user_13807049 0
Viewed 45

I have two data frames: one containing the IDs of pairs of participants that were matched using propensity score matching (i.e., each row has the two matched IDs; df1) and one containing longitudinal data in long format for all participants (df2).

In each matched pair, one person is from the experimental group and the other is from the control group. Group is indicated by the variable Group. In df2, participants from the experimental group have values on the variables Year and Month whereas participants from the control group only have NA’s on these variables. I now aim to copy the values on Year and Month of the experimental group participant to their matching partner from the control group (based on the information about matched IDs from df1).

df1 <- read.table(text=
"ID_EG  ID_CG
800057  834341
800119  897177
800125  834011", header = TRUE)

df2 <- read.table(text=
"ID       Group    Year      Month
800057    1        2008      2         
800057    1        2008      2   
800057    1        2008      2   
800057    1        2008      2   
800057    1        2008      2   
800119    1        2014      10  
800119    1        2014      10         
800119    1        2014      10   
834011    0        NA        NA  
834011    0        NA        NA    
834341    0        NA        NA   
834341    0        NA        NA   
834341    0        NA        NA    
834341    0        NA        NA    
834341    0        NA        NA   
800125    1        2010     5
800125    1        2010     5
897177    0        NA       NA
897177    0        NA       NA
897177    0        NA       NA", header=TRUE)

Can anyone help me how to get this done for each matched pair? I tried looping but since I'm very new to R, I was swamped.

My expected outcome would look like this (df3):

df3 <- read.table(text=
"ID       Group    Year      Month
800057    1        2008      2         
800057    1        2008      2   
800057    1        2008      2   
800057    1        2008      2   
800057    1        2008      2   
800119    1        2014      10  
800119    1        2014      10         
800119    1        2014      10   
834011    0        2010      5  
834011    0        2010      5    
834341    0        2008      2   
834341    0        2008      2   
834341    0        2008      2    
834341    0        2008      2    
834341    0        2008      2   
800125    1        2010      5
800125    1        2010      5
897177    0        2014      10
897177    0        2014      10
897177    0        2014      10", header=TRUE)

Any help is highly appreciated!

dplyr match r join
2 Answers

This is one possible solution to achieve such task. Basically, we first create a lookup table where each ID with Year and Month is paired with its relative participant without Year and Month. Afterwards, we use a left join and coalesce the NA values of Year and Month in order to obtain a new column without missing values.

library(dplyr)

lookup <- df1 %>% inner_join(df2, by = c("ID_EG" = "ID")) %>% select(-Group) %>% distinct()
#    ID_EG  ID_CG Year Month
# 1 800057 834341 2008     2
# 2 800119 897177 2014    10
# 3 800125 834011 2010     5

df2 %>%
  left_join(lookup, by = c("ID" = "ID_CG")) %>% 
  mutate(
    Year = coalesce(Year.x, Year.y),
    Month = coalesce(Month.x, Month.y)
    ) %>% 
  select(!ends_with(".x") & !ends_with(".y"), -ID_EG)

Final output

       ID Group Year Month
1  800057     1 2008     2
2  800057     1 2008     2
3  800057     1 2008     2
4  800057     1 2008     2
5  800057     1 2008     2
6  800119     1 2014    10
7  800119     1 2014    10
8  800119     1 2014    10
9  834011     0 2010     5
10 834011     0 2010     5
11 834341     0 2008     2
12 834341     0 2008     2
13 834341     0 2008     2
14 834341     0 2008     2
15 834341     0 2008     2
16 800125     1 2010     5
17 800125     1 2010     5
18 897177     0 2014    10
19 897177     0 2014    10
20 897177     0 2014    10
user_74654620 
df <- df1 %>% 
  left_join(df2 %>% select(-Group), by = c("ID_EG" = "ID")) %>% 
  unique() %>% 
  pivot_longer(contains("ID"), values_to = "ID", names_to = "Group") %>% 
  mutate(Group = ifelse(Group == "ID_EG", 1, 0)) %>% 
  left_join(df2, ., by = "ID") %>%
  select(-contains(".x")) %>%
  data.table::setnames(str_subset(names(.), ".y"), str_subset(names(.), ".y") %>% str_remove(".y"))

gives:

       ID Year Month Group
1  800057 2008     2     1
2  800057 2008     2     1
3  800057 2008     2     1
4  800057 2008     2     1
5  800057 2008     2     1
6  800119 2014    10     1
7  800119 2014    10     1
8  800119 2014    10     1
9  834011 2010     5     0
10 834011 2010     5     0
11 834341 2008     2     0
12 834341 2008     2     0
13 834341 2008     2     0
14 834341 2008     2     0
15 834341 2008     2     0
16 800125 2010     5     1
17 800125 2010     5     1
18 897177 2014    10     0
19 897177 2014    10     0
20 897177 2014    10     0
user_121484020
Related