Fill a dataframe in R according to another dataframe

Question

Hello eveyron I would need help in R in order to fill columns within a dataframe according to this dataframe 1 :

  COL0 
    A 
    B 
    C 
    D 

So the idea is that I have a dataframe 2 :

COL1 COL2 COL3 COL4 
SP1 A 3 OK 
SP2 A 7 OK 
SP3 A 9 OK 
SP1 B 2 OK 
SP3 B 3 OK 
SP4 B 1 OK
SP5 C 4 OK 
SP6 C 3 OK
SP2 C 8 OK

and I would like to add for each df1$COL0 (A,B,C and D) all the possible df2$COL1 and then fill df2$COL2 and df2$COL3 already present values, but add df2$COL2=0 and df2$COL3=NONE if there were no df1$COL0 available in df2$COL1

DOes someone have an idea for that using a R script ?

Here is the expected output

COL1 COL2 COL3 COL4
SP1 A 3 OK
SP2 A 7 OK
SP3 A 9 OK 
SP4 A 0 NONE 
SP5 A 0 NONE 
SP6 A 0 NONE 
SP1 B 2 OK 
SP2 B 0 NONE 
SP3 B 3 OK 
SP4 B 1 OK
SP5 B 0 NONE 
SP6 B 0 NONE 
SP1 C 0 NONE 
SP2 C 3 OK
SP3 C 0 NONE 
SP4 C 0 NONE 
SP5 C 4 OK 
SP6 C 8 OK
SP1 D 0 NONE 
SP2 D 0 NONE 
SP3 D 0 NONE 
SP4 D 0 NONE 
SP5 D 0 NONE 

Here are the data :

dput(df1)
structure(list(COL0 = c("A", "B", "C", "D", "E", "F", "G")), class = c("spec_tbl_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -7L), spec = structure(list(
    cols = list(COL0 = structure(list(), class = c("collector_character", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
    "collector")), skip = 1L), class = "col_spec"))


dput(df2)
structure(list(`COL1 COL2 COL3 COL4` = c("SP1 A 3 OK", "SP2 A 7 OK", 
"SP3 A 9 OK", "SP1 B 2 OK", "SP3 B 3 OK", "SP4 B 1 OK", "SP6 C 3 OK", 
"SP2 C 8 OK")), class = c("spec_tbl_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -8L), spec = structure(list(cols = list(
    `COL1 COL2 COL3 COL4` = structure(list(), class = c("collector_character", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
"collector")), skip = 1L), class = "col_spec"))

SP6 D 0 NONE

Answer 1

You can use tidyr::complete :

tidyr::complete(df, COL1, COL2 = df1$COL0, fill = list(COL3 = 0, COL4 = 'NONE'))

#  COL1  COL2   COL3 COL4 
#   <chr> <chr> <dbl> <chr>
# 1 SP1   A         3 OK   
# 2 SP1   B         2 OK   
# 3 SP1   C         0 NONE 
# 4 SP1   D         0 NONE 
# 5 SP2   A         7 OK   
# 6 SP2   B         0 NONE 
# 7 SP2   C         8 OK   
# 8 SP2   D         0 NONE 
# 9 SP3   A         9 OK   
#10 SP3   B         3 OK   
# … with 14 more rows