CODEWITHSUNDEEP
rdplyrtidyr
maycca
maycca

Reputation: 4090

Expand grid by groups: every group needs to have all values from vector

I have a dataframe with groups, and a vector containing all possible values. I want to expand the dataframe that each group will have all of the values in vector. If the elements were not present before, it can be filled by zero.

Dummy example:

dd <- data.frame(trip_n = c(1,1,2),
                 sp = c('a', 'b', 'd'),
                 val = c(4,3,3))

sp = c('a', 'b', 'd', 'e')

Expected output, where the missing elements from vector are filled in by 0:

  trip_n sp val
1      1  a 4
2      1  b 3
3      1  d 0
4      1  e 0
5      2  a 0
6      2  b 0
7      2  d 3
8      2  e 0

I wonder, how to accomplsh this? THis is waht I tried, but neither of them leads to expected outcome. Somehow I need to include grouping, but how? THanks you!

expand.grid(dd$sp, sp)
merge(dd, sp) %>% 
  arrange(trip_n)

left_join(data.frame(sp), dd)

Upvotes: 1

Views: 317

Answers (1)

akrun
akrun

Reputation: 887148

We could use complete - as the object name sp is the same as the column name, either get the object from the global env (.env) or escape (!!) - (complete(dd, trip_n, sp = !!sp, fill = list(val = 0)))

library(tidyr)
complete(dd, trip_n, sp = .env$sp, fill = list(val = 0))

-output

# A tibble: 8 × 3
  trip_n sp      val
   <dbl> <chr> <dbl>
1      1 a         4
2      1 b         3
3      1 d         0
4      1 e         0
5      2 a         0
6      2 b         0
7      2 d         3
8      2 e         0

Upvotes: 3

Related Questions