Repairing a data frame's missing rows

user_12549160 0
Viewed 41

I have the following dataframe

df <- structure(list(position = c("a", "a", "c"), value = c(1, 1, 2
), name = c("bar", "foo", "foo")), row.names = c(NA, -3L), class = c("tbl_df", 
"tbl", "data.frame"))

  position value name 
  <chr>    <dbl> <chr>
1 a            1 bar  
2 a            1 foo  
3 c            2 foo

And there is a reference dataframe that lists 4 conditions for each value in df$position.

ref <- structure(list(group = c("A", "B", "C", "C"), position = c("a", 
"a", "b", "c")), row.names = c(NA, -4L), class = c("tbl_df", 
"tbl", "data.frame"))

# A tibble: 4 x 2
  group position
  <chr> <chr>   
1 A     a       
2 B     a       
3 C     b       
4 C     c

My expected output is:

  group  position  name  value
1 A      a         bar   1
2 B      a         bar   1
3 C      b         bar   NA
4 C      c         bar   NA
5 A      a         foo   1
6 B      a         foo   1
7 C      b         foo   NA
8 C      c         foo   2

every unique value in df$name should have 4 rows based on the position column in df and ref.

I have tried left_join, crossing, complete to no avail.

tidyverse r
2 Answers

In tidyverse, we can use crossing to get the combinations and then do the left_join

library(tidyr)
library(dplyr)
distinct(df, name) %>%
      crossing(ref) %>% 
      left_join(df)
# A tibble: 8 x 4
#  name  group position value
#* <chr> <chr> <chr>    <dbl>
#1 bar   A     a            1
#2 bar   B     a            1
#3 bar   C     b           NA
#4 bar   C     c           NA
#5 foo   A     a            1
#6 foo   B     a            1
#7 foo   C     b           NA
#8 foo   C     c            2
user_37322710

You need to expand ref to include all the values of name before the left_join:

names <- unique(df$name)
new_ref <- do.call(rbind, lapply(names, function(x) cbind(ref, name = rep(x, nrow(ref)))))
left_join(new_ref, df, by = c("name", "position"))
#>   group position name value
#> 1     A        a  bar     1
#> 2     B        a  bar     1
#> 3     C        b  bar    NA
#> 4     C        c  bar    NA
#> 5     A        a  foo     1
#> 6     B        a  foo     1
#> 7     C        b  foo    NA
#> 8     C        c  foo     2
user_125003150
Related