coalescing join

I have two tables, which I want to join in a coalescing fashion.
Here, row 3 is NA in table x but is full of values in y.
I'd like to join them in a "coalescing" manner. I have a feeling it should be doable with the new join_by() helper function, but I can't seem to figure it out.

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

x <- tibble(
  drop = c(0, 0, NA, NA,-2.111),
  stranger = c(0.3455, 0.3455, NA, NA, -0.3455),
  dexterity = c(0.366, 0.366, NA, NA, 0),
  eye = c(0, 0, NA, NA, 0),
  belief = c(0.481, 0.481, NA, NA, -0.481)
)

y <- tibble(
  drop = c(1.494, -1.494, 0, NA, -1.494),
  stranger = c(0.647, 0, 0, NA, 0),
  dexterity = c(0.458, 0.458, -0.458, NA, 0),
  voice = c(0, 0, -0.347, NA, 0),
  belief = c(0.417, 0.417, -0.417, NA, -0.417)
)

x
#> # A tibble: 5 × 5
#>    drop stranger dexterity   eye belief
#>   <dbl>    <dbl>     <dbl> <dbl>  <dbl>
#> 1  0       0.346     0.366     0  0.481
#> 2  0       0.346     0.366     0  0.481
#> 3 NA      NA        NA        NA NA    
#> 4 NA      NA        NA        NA NA    
#> 5 -2.11   -0.346     0         0 -0.481
y
#> # A tibble: 5 × 5
#>    drop stranger dexterity  voice belief
#>   <dbl>    <dbl>     <dbl>  <dbl>  <dbl>
#> 1  1.49    0.647     0.458  0      0.417
#> 2 -1.49    0         0.458  0      0.417
#> 3  0       0        -0.458 -0.347 -0.417
#> 4 NA      NA        NA     NA     NA    
#> 5 -1.49    0         0      0     -0.417

# expected output
tibble(
  drop = c(0, 0, 0, NA,-2.111),
  stranger = c(0.3455, 0.3455, 0, NA, -0.3455),
  dexterity = c(0.366, 0.366, -0.458, NA, 0),
  eye = c(0, 0, -0.347, NA, 0),
  belief = c(0.481, 0.481, -0.417, NA, -0.481),
 voice = c(0, 0, -0347, NA, 0)
)
# A tibble: 5 × 6
   drop stranger dexterity    eye belief voice
  <dbl>    <dbl>     <dbl>  <dbl>  <dbl> <dbl>
1  0       0.346     0.366  0      0.481     0
2  0       0.346     0.366  0      0.481     0
3  0       0        -0.458 -0.347 -0.417  -347
4 NA      NA        NA     NA     NA        NA
5 -2.11   -0.346     0      0     -0.481     0

Created on 2023-02-16 with reprex v2.0.2

I received a lovely solution combining row_path and bind_cols, on twitter.

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

x <- tibble(
  drop = c(0, 0, NA, NA,-2.111),
  stranger = c(0.3455, 0.3455, NA, NA, -0.3455),
  dexterity = c(0.366, 0.366, NA, NA, 0),
  eye = c(0, 0, NA, NA, 0),
  belief = c(0.481, 0.481, NA, NA, -0.481)
) |> 
  mutate(ID = row_number())

y <- tibble(
  drop = c(1.494, -1.494, 0, NA, -1.494),
  stranger = c(0.647, 0, 0, NA, 0),
  dexterity = c(0.458, 0.458, -0.458, NA, 0),
  voice = c(0, 0, -0.347, NA, 0),
  belief = c(0.417, 0.417, -0.417, NA, -0.417)
) |> 
  mutate(ID = row_number())

x
#> # A tibble: 5 × 6
#>    drop stranger dexterity   eye belief    ID
#>   <dbl>    <dbl>     <dbl> <dbl>  <dbl> <int>
#> 1  0       0.346     0.366     0  0.481     1
#> 2  0       0.346     0.366     0  0.481     2
#> 3 NA      NA        NA        NA NA         3
#> 4 NA      NA        NA        NA NA         4
#> 5 -2.11   -0.346     0         0 -0.481     5
y
#> # A tibble: 5 × 6
#>    drop stranger dexterity  voice belief    ID
#>   <dbl>    <dbl>     <dbl>  <dbl>  <dbl> <int>
#> 1  1.49    0.647     0.458  0      0.417     1
#> 2 -1.49    0         0.458  0      0.417     2
#> 3  0       0        -0.458 -0.347 -0.417     3
#> 4 NA      NA        NA     NA     NA         4
#> 5 -1.49    0         0      0     -0.417     5


# Solution
rows_patch(x, select(y, -voice), by = "ID") |> 
  bind_cols(select(y, voice)) |> 
  select(-ID)
#> # A tibble: 5 × 6
#>    drop stranger dexterity   eye belief  voice
#>   <dbl>    <dbl>     <dbl> <dbl>  <dbl>  <dbl>
#> 1  0       0.346     0.366     0  0.481  0    
#> 2  0       0.346     0.366     0  0.481  0    
#> 3  0       0        -0.458    NA -0.417 -0.347
#> 4 NA      NA        NA        NA NA     NA    
#> 5 -2.11   -0.346     0         0 -0.481  0

Created on 2023-02-17 with reprex v2.0.2

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.