R: merge two tables when one table is a special case of the another one-CodePudding

I want to merge two table with different row numbers by the specific way. I have the next:


df1 <- data.frame(num = c(1,1,1,2,2,2),
                  lab = c("A", "B", "C", "A", "B", "C"),
                  val = c(0,0,0,0,0,0))
df1
  num lab val
1   1   A   0
2   1   B   0
3   1   C   0
4   2   A   0
5   2   B   0
6   2   C   0

df2 <- data.frame(num = c(1,1,2),
                  lab = c("A", "B", "A"),
                  val = c(10,10,10))
df2
  num lab val
1   1   A  10
2   1   B  10
3   2   A  10

I want to merge df1 and df2 to get df3:

df3 <- data.frame(num = c(1,1,1,2,2,2),
                  lab = c("A", "B", "C", "A", "B", "C"),
                  val = c(10,10,0,10,0,0))
df3
  num lab val
1   1   A  10
2   1   B  10
3   1   C   0
4   2   A  10
5   2   B   0
6   2   C   0

How to do that?

CodePudding user response：

You can do it with one line:

dplyr::rows_update(df1, df2, by = c("num", "lab"))
#   num lab val
# 1   1   A  10
# 2   1   B  10
# 3   1   C   0
# 4   2   A  10
# 5   2   B   0
# 6   2   C   0

CodePudding user response：

Use dplyr::coalesce()

library(tidyverse)
df1 <- data.frame(num = c(1,1,1,2,2,2),
                  lab = c("A", "B", "C", "A", "B", "C"),
                  val = c(0,0,0,0,0,0))
df1
#>   num lab val
#> 1   1   A   0
#> 2   1   B   0
#> 3   1   C   0
#> 4   2   A   0
#> 5   2   B   0
#> 6   2   C   0
df2 <- data.frame(num = c(1,1,2),
                  lab = c("A", "B", "A"),
                  val = c(10,10,10))

df1 %>% 
  left_join(df2, by = c("num", "lab")) %>% 
  mutate(val = coalesce(val.y, val.x)) %>% 
  select(-val.x, -val.y)
#>   num lab val
#> 1   1   A  10
#> 2   1   B  10
#> 3   1   C   0
#> 4   2   A  10
#> 5   2   B   0
#> 6   2   C   0

^{Created on 2022-07-04 by the reprex package (v2.0.1)}

CodePudding user response：

Using data.table package, you could update the column val as follows:

library(data.table)

setDT(df1)[df2, val := i.val, on=.(num, lab)]

#      num    lab   val
# 1:     1      A    10
# 2:     1      B    10
# 3:     1      C     0
# 4:     2      A    10
# 5:     2      B     0
# 6:     2      C     0

CodePudding user response：

Another option using anti_join and bind_rows:

df1 <- data.frame(num = c(1,1,1,2,2,2),
                  lab = c("A", "B", "C", "A", "B", "C"),
                  val = c(0,0,0,0,0,0))
df2 <- data.frame(num = c(1,1,2),
                  lab = c("A", "B", "A"),
                  val = c(10,10,10))
library(dplyr)
anti_join(df1, df2, by = c("num", "lab")) %>%
  bind_rows(df2)
#>   num lab val
#> 1   1   C   0
#> 2   2   B   0
#> 3   2   C   0
#> 4   1   A  10
#> 5   1   B  10
#> 6   2   A  10

^{Created on 2022-07-04 by the reprex package (v2.0.1)}