How to join only certain rows using dplyr?

user_11517856 0
Viewed 349

So I have a dataframe as such

ID     Date     TIME     var    Data     misc
1  1/3/2018  3:30 AM       a  string1  string1
1  4/23/2019 1:32 PM       b  string2  string1
1  1/3/2018  4:53 PM       c  string3  string1
2  1/4/2018  3:32 AM       d  string4  string2
2  3/3/2018  3:30 PM       s  string5  string2
2  3/3/2018  3:30 PM       e  string6  string2
3  4/23/2019 6:24 AM       w
3  4/23/2019 1:32 PM       s 
3  4/24/2019 3:20 PM       s
3  4/24/2019 3:20 PM       a

There are a number of columns similar to Data and misc which I would like to join to fill in the df, using another df comprised of ID = 3 data.

ID3_data

     DATE    Time       Data       misc
4/23/2019 6:24 AM    string7    stringA
4/23/2019 1:32 PM    string8    stringB
4/24/2019 3:20 PM    string9    stringC
4/24/2019 3:20 PM   string10    stringC

So how could I left join my DF with this ID3_data for just the rows where ID =3?

Additionally, there is another issue where the only identifier I have is the Date and TIME but I do have different matches with the same identifiers, is there a way to say the first instance goes to the first and second the second??? So in short the final DF should look as such:

ID     Date     TIME     var     Data       misc
1  1/3/2018  3:30 AM       a   string1    string1
1  4/23/2019 1:32 PM       b   string2    string1
1  1/3/2018  4:53 PM       c   string3    string1
2  1/4/2018  3:32 AM       d   string4    string2
2  3/3/2018  3:30 PM       s   string5    string2
2  3/3/2018  3:30 PM       e   string6    string2
3 4/23/2019  6:24 AM       w   string7    stringA
3 4/23/2019  1:32 PM       s   string8    stringB
3 4/24/2019  3:20 PM       s   string9    stringC
3 4/24/2019  3:20 PM       a  string10    stringC

Again, the priority is joining select rows but if the duplicate issue could also be done in the same swoop using dplyr that would be great.

dplyr tidyverse r join
1 Answers

We could do a join with coalesce. Assuming the missing values as NA

library(dplyr)# 1.0.0
left_join(DF, ID3_data %>%
           mutate(ID = 3), by = c('ID', 'Date' = 'DATE', 'TIME' = 'Time')) %>%
       mutate(Data = coalesce(Data.x, Data.y), misc = coalesce(misc.x, misc.y))

Or if there are duplicates, then an option is bind the rows of the two dataset and then do a group by summarise with only non-NA rows (dplyr 1.0.0 allows summarise with more than one row)

cbind(ID = 3, ID3_data) %>%
   set_names(names(DF)) %>% 
  bind_rows(DF) %>%
  group_by(ID, Date, TIME) %>% 
  summarise(across(everything(), ~ .[!is.na(.)]))
# A tibble: 10 x 5
# Groups:   ID, Date, TIME [8]
#      ID Date      TIME    Data     misc   
#   <dbl> <chr>     <chr>   <chr>    <chr>  
# 1     1 1/3/2018  3:30 AM string1  string1
# 2     1 1/3/2018  4:53 PM string3  string1
# 3     1 4/23/2019 1:32 PM string2  string1
# 4     2 1/4/2018  3:32 AM string4  string2
# 5     2 3/3/2018  3:30 PM string5  string2
# 6     2 3/3/2018  3:30 PM string6  string2
# 7     3 4/23/2019 1:32 PM string8  stringB
# 8     3 4/23/2019 6:24 AM string7  stringA
# 9     3 4/24/2019 3:20 PM string9  stringC
#10     3 4/24/2019 3:20 PM string10 stringC

data

DF <- structure(list(ID = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 3L), 
    Date = c("1/3/2018", "4/23/2019", "1/3/2018", "1/4/2018", 
    "3/3/2018", "3/3/2018", "4/23/2019", "4/23/2019", "4/24/2019", 
    "4/24/2019"), TIME = c("3:30 AM", "1:32 PM", "4:53 PM", "3:32 AM", 
    "3:30 PM", "3:30 PM", "6:24 AM", "1:32 PM", "3:20 PM", "3:20 PM"
    ), Data = c("string1", "string2", "string3", "string4", "string5", 
    "string6", NA, NA, NA, NA), misc = c("string1", "string1", 
    "string1", "string2", "string2", "string2", NA, NA, NA, NA
    )), class = "data.frame", row.names = c(NA, -10L))
ID3_data <- structure(list(DATE = c("4/23/2019", "4/23/2019", "4/24/2019", 
"4/24/2019"), Time = c("6:24 AM", "1:32 PM", "3:20 PM", "3:20 PM"
), Data = c("string7", "string8", "string9", "string10"), misc = c("stringA", 
"stringB", "stringC", "stringC")), class = "data.frame",
row.names = c(NA, 
-4L))
user_37322710
Related