Complete values in column based on condition and value in another column

Question

I’ve been trying to figure this out for a while, but it didn’t work out. I have this table below. What I want is for the inchikey column to be completed according to the "cid_pub_chem" column. If you notice, in lines 1 and 2, we have the value of 31253 for the column "cid_pub_chem" and values for the column "inchikey" but in lines 5 and 6 I have the same value as "cid_pub_chem", but the column "inchikey" has NA. It should be very simple to solve this, but there was no way...

abc = structure(
  list(cid_pub_chem = c("31253", "31253", "6654", "11040762", "31253", "31253"), 
       inchikey = c("UAHWPYUMFXYFJY-UHFFFAOYSA-N", "UAHWPYUMFXYFJY-UHFFFAOYSA-N", "GRWFGVWFFZKLTI-UHFFFAOYSA-N", "WPOQYXKDKVBMSB-UHFFFAOYSA-N", NA, NA), 
       height = c("37740", "25556", "39861", "51715", "26048", "52332")), 
  row.names = c(NA, -6L), 
  class = "data.frame")

Any help is welcome. I posted some questions on stackoverflow in English (I didn’t know about this one), but a few months ago I forgot how to format the code better (by the way, how do you do it?). Sorry...

Answer 1

One of the ways to do this is to build a "dictionary" or "jig" with the values you want to unite and then perform a union.

The code to create the feedback can be something like this:

library(tidyverse)
valores <- abc %>% 
  filter(!is.na(inchikey)) %>% 
  select(cid_pub_chem, inchikey) %>% 
  unique()

And then we can remove the column with the wrong information and scold it using the feedback we’ve just created.

abc %>% 
  select(-inchikey) %>% 
  left_join(valores, "cid_pub_chem")
#>   cid_pub_chem height                    inchikey
#> 1        31253  37740 UAHWPYUMFXYFJY-UHFFFAOYSA-N
#> 2        31253  25556 UAHWPYUMFXYFJY-UHFFFAOYSA-N
#> 3         6654  39861 GRWFGVWFFZKLTI-UHFFFAOYSA-N
#> 4     11040762  51715 WPOQYXKDKVBMSB-UHFFFAOYSA-N
#> 5        31253  26048 UAHWPYUMFXYFJY-UHFFFAOYSA-N
#> 6        31253  52332 UAHWPYUMFXYFJY-UHFFFAOYSA-N