CODEWITHSUNDEEP
rdataframe
Surendra babu Pasumarhti
Surendra babu Pasumarhti

Reputation: 463

How to ignore NA's while joining two data frames by two columns?

I have two data frames and I want to join by id and date by ignoring NA's from the second data frame.

dataset_A

id         date
e1234      2018-01-19
e1234      2018-01-14
e1234      2018-02-13
e2345      2018-07-18
e2345      2018-09-29
e2345      2018-10-10
e3456      2018-11-12

dataset_B

id       date          item_id     item_name
e1234    2018-01-19    NA          NA
e1234    2018-01-19    75889       NA
e1234    2018-01-19    NA          biscuits
e1234    2018-01-19    NA          biscuits
e2345    2018-07-18    34567       NA
e2345    2018-09-29    NA          choclate
e2345    2018-10-10    NA          NA
e2345    2018-10-10    53566       icecream
e3456    2018-11-12    NA          NA
e3456    2018-11-12    NA          NA
e3456    2018-11-12    94585       NA
e3456    2018-11-12    94585       cake
e3456    2018-11-12    NA          cake

Expected_output

id       date          item_id     item_name
e1234    2018-01-19    75889       biscuits
e1234    2018-01-14    NA          NA
e1234    2018-02-13    NA          NA
e2345    2018-07-18    34567       NA
e2345    2018-09-29    NA          choclate
e2345    2018-10-10    53566       icecream
e3456    2018-11-12    94585       cake

Tried:

library(dplyr)
final_data = dplyr::left_join(dataset_A,dataset_B,by=c(id,date),ignore.na = T)

I would like to have a proper solution to solve the above issue.

Upvotes: 0

Views: 429

Answers (1)

Roman
Roman

Reputation: 4989

Almost. You just need to wrangle your data a bit, first.

dataset_B %>%
    group_by(id, date) %>%
    summarize(item_id = first(na.omit(item_id)),
           item_name = first(na.omit(item_name))) %>%
    right_join(., dataset_A)

# A tibble: 7 x 4
# Groups:   id [?]
  id    date       item_id item_name
  <fct> <chr>        <int> <fct>    
1 e1234 2018-01-19   75889 biscuits 
2 e1234 2018-01-14      NA NA       
3 e1234 2018-02-13      NA NA       
4 e2345 2018-07-18   34567 NA       
5 e2345 2018-09-29      NA choclate 
6 e2345 2018-10-10   53566 icecream 
7 e3456 2018-11-12   94585 cake

Explanation: na.omit() omits NA values (duh), first() returns the first value from the result. As we grouped by id and date before, our summarize() nicely reduces the dataset to the first non-NA value for each group.

Caveat: This approach assumes that you do not have multiple different non-NA values per group.

Upvotes: 1

Related Questions