How to make complicated binding of my two tables?

Question

I have a single column dataframe with column type with all possible "types":

type

enter 
open
close
update
delete

I get dataframe from my database. But in that dataframe, not all "types" might be. Here is example of that table:

ID    date            type           value
a1    2020-09-01       enter          18
a1    2020-09-01       close          15
a1    2020-09-02       enter          4
a2    2020-09-01       close          10
b1    2020-09-02       update         10

As you see ID a1 has only two types: enter and close. a2 has only close, b1 has only update.

I want to bind these two tables in that way, so "types" which were not in my table have value zero for each ID and date. So, how to bind these two tables to get this:

ID    date            type           value
a1    2020-09-01       enter          18
a1    2020-09-01       open           0
a1    2020-09-01       close          15
a1    2020-09-01       update         0
a1    2020-09-01       delete         0
a1    2020-09-02       enter          4
a1    2020-09-02       open           0
a1    2020-09-02       close          0
a1    2020-09-02       update         0
a1    2020-09-02       delete         0
a2    2020-09-01       enter          0
a2    2020-09-01       open           0
a2    2020-09-01       close          10
a2    2020-09-01       update         0
a2    2020-09-01       delete         0
b1    2020-09-01       enter          0
b1    2020-09-01       open           0
b1    2020-09-01       close          0
b1    2020-09-01       update         10
b1    2020-09-01       delete         0

How could i do that?

Answer 1

You can try using complete :

library(dplyr)
library(tidyr)

df2 %>%
  mutate(type = factor(type, levels = df1$type)) %>%
  group_by(ID, date) %>%
  complete(type, fill = list(value = 0))

#    ID    date       type   value
#   <chr> <chr>      <fct>  <dbl>
# 1 a1    2020-09-01 enter     18
# 2 a1    2020-09-01 open       0
# 3 a1    2020-09-01 close     15
# 4 a1    2020-09-01 update     0
# 5 a1    2020-09-01 delete     0
# 6 a1    2020-09-02 enter      4
# 7 a1    2020-09-02 open       0
# 8 a1    2020-09-02 close      0
# 9 a1    2020-09-02 update     0
#10 a1    2020-09-02 delete     0
#11 a2    2020-09-01 enter      0
#12 a2    2020-09-01 open       0
#13 a2    2020-09-01 close     10
#14 a2    2020-09-01 update     0
#15 a2    2020-09-01 delete     0
#16 b1    2020-09-02 enter      0
#17 b1    2020-09-02 open       0
#18 b1    2020-09-02 close      0
#19 b1    2020-09-02 update    10
#20 b1    2020-09-02 delete     0