Merging two data frames with missing values

Question

I would like to merge two data frames (df1 and df2). The script below works well for the letters. But, when I added a df with "-", it doesn't work anymore. I tried to add the "-" as "" in the df1, but that failed, too.

I would like the script to skip or leave a blank when it meets "-".

To note: I would need to know where the blanks are so I wouldn't like to remove the blanks from a data frame.

Any input highly appreciated!

  df1 <- data.frame(scale = c(0.62, 0.29, -0.9, -0.74, 1.19, 0.48, -0.4, 1.38, -1.5, 1.06, 0.64, -0.78, 0.12, -0.85, -2.53, -0.18, -0.05, 1.08, 0.81, 0.26,""), 
                            aa = c('A','C','D','E','F','G','H','I','K','L','M','N','P','Q','R','S','T','V','W','Y','-'))
   



df2 <- df(col1 = c('-', '-' ,'E', 'S', 'P', 'V', 'F', 'A', 'F', 'P', 'K', 'A', 'L', 'D', 'L', 'E', 'T' ,'H', 'I', 'E', 'K' ,'L', 'F', 'L', 'Y'),
col2 = c('D','D','T','L','D','D','S','D','E','D','D','I','V','V','E','S','Q','D','P','P','L','-','S','W','-'))



matched_res_all <- df2 %>%
  left_join(df1, by = c(col1 = "aa")) %>% 
  left_join(df1, by = c(col2 = "aa"))

view(matched_res_all)

Ronak Shah · Accepted Answer · 2020-10-01 03:21:40Z

To make the solution scalable for many such 'col' variables in df2 we can get the data in long format remove the rows where "-" is present and join the data with df1.

library(dplyr)
library(tidyr)

df2 %>%
  pivot_longer(cols = everything(), values_to = 'aa') %>%
  mutate(aa = replace(aa, aa == '-', '')) %>%
  left_join(df1, by = 'aa') %>%
  arrange(name) %>%
  group_by(name) %>%
  mutate(row = row_number()) %>%
  pivot_wider(names_from = name, values_from = c(aa, scale)) %>%
  select(-row)

#  aa_col1 aa_col2 scale_col1 scale_col2
#   <chr>   <chr>   <chr>      <chr>     
# 1 ""      D       NA         -0.9      
# 2 ""      D       NA         -0.9      
# 3 "E"     T       -0.74      -0.05     
# 4 "S"     L       -0.18      1.06      
# 5 "P"     D       0.12       -0.9      
# 6 "V"     D       1.08       -0.9      
# 7 "F"     S       1.19       -0.18     
# 8 "A"     D       0.62       -0.9      
# 9 "F"     E       1.19       -0.74     
#10 "P"     D       0.12       -0.9      
# … with 15 more rows

Collectives™ on Stack Overflow

Merging two data frames with missing values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related