-1

I want to update the df1 with new column from df2 matched columns

my df1 looks like

PID Record date Drugs
123 22-04-1996  D1+D2
123 23-04-1996  D2+D3
123 28-05-2000  D3+D4+D5+D6
234 22-04-1996  D1+D3
234 23-04-1996  D2+D4
347 28-05-2000  D3+D4+D5+D7
456 22-04-1996  D1+D4

my df2 looks like

PID Record date V1  V2
123 22-04-1996  5.5 6.5
234 05-07-1997  6.8 6.8
347 28-05-2000  8.5 8.6
456 23-04-1996  7.4 7.2

I want my df1 with updated column like

expected df1

PID Record date V1  V2  Drugs
123 22-04-1996  5.5 6.5 D1+D2
234 05-07-1997  6.8 6.8 NA
347 28-05-2000  8.5 8.6 D3+D4+D5+D7
456 23-04-1996  7.4 7.2 NA

I don't want use merge and create new df I want to update with existing df

Thank you

Rebel_47
  • 69
  • 4
  • you can use `dplyr` like this: `df2 %>% left_join(df1)` – AlexB Oct 16 '20 at 08:58
  • 1
    Why don't you want to use `merge`? Using `merge` is straight forward approach `df1 <- merge(df1, df2, by = c('PID', 'Record_date'), all.y = TRUE)` – Ronak Shah Oct 16 '20 at 08:58

1 Answers1

0

Does this work:

> df1 %>% right_join(df2, by = c('PID', 'Record date')) %>% 
+   arrange(PID) %>% relocate(1,2,4,5,3)
# A tibble: 4 x 5
# Groups:   PID [4]
    PID `Record date`    V1    V2 Drugs      
  <dbl> <date>        <dbl> <dbl> <chr>      
1   123 1996-04-22      5.5   6.5 D1+D2      
2   234 1997-07-05      6.8   6.8 NA         
3   347 2000-05-28      8.5   8.6 D3+D4+D5+D7
4   456 1996-04-23      7.4   7.2 NA         
>
Karthik S
  • 11,348
  • 2
  • 11
  • 25