Expanding a data.frame based on (group) values from the data.frame

Question

Lets say I have the following data frame:

tibble(user = c('A', 'B'), first = c(1,4), last = c(6, 9))

# A tibble: 2 x 3
  user  first  last
  <chr> <dbl> <dbl>
1 A         1     6
2 B         4     9

And want to create a tibble that looks like:

bind_rows(tibble(user = 'A', weeks = 1:6), 
          tibble(user = 'B', weeks = 4:9))

# A tibble: 12 x 2
   user  weeks
   <chr> <int>
 1 A         1
 2 A         2
 3 A         3
 4 A         4
 5 A         5
 6 A         6
 7 B         4
 8 B         5
 9 B         6
10 B         7
11 B         8
12 B         9

How could I go about doing this? I have tried:

tibble(user = c('A', 'B'), first = c(1,4), last = c(6, 9)) %>% 
group_by(user) %>% 
mutate(weeks = first:last)

I wonder if I should try a combination of complete map or nest?

This can be done quite elegantly using `data.table`: `library(data.table); out <- setDT(x)[, .(weeks = first:last), by=user]` — markus, Nov 25 '19 at 20:11
Does this answer your question? [Expand rows by date range using start and end date](https://stackoverflow.com/questions/24803361/expand-rows-by-date-range-using-start-and-end-date) — camille, Nov 25 '19 at 21:03

akrun · Accepted Answer · 2019-11-25T21:02:03.800

One option is unnest after creating a sequence

library(dplyr)
library(purrr)
df1 %>% 
  transmute(user, weeks = map2(first, last, `:`)) %>%
  unnest(weeks)
# A tibble: 12 x 2
#   user  weeks
#   <chr> <int>
# 1 A         1
# 2 A         2
# 3 A         3
# 4 A         4
# 5 A         5
# 6 A         6
# 7 B         4
# 8 B         5
# 9 B         6
#10 B         7
#11 B         8
#12 B         9

Or another option is rowwise

df1 %>% 
  rowwise %>%
  transmute(user, weeks = list(first:last)) %>% 
  unnest(weeks)

Or without any packages

stack(setNames(Map(`:`, df1$first, df1$last), df1$user))

Or otherwise written as

stack(setNames(do.call(Map, c(f = `:`, df1[-1])), df1$user))

data

df1 <- tibble(user = c('A', 'B'), first = c(1,4), last = c(6, 9))

Thanks for the thorough answer – kmace Dec 02 '19 at 18:32 — kmace, Dec 02 '19 at 18:32

score 1 · Answer 2 · answered Nov 25 '19 at 19:45

One option involving dplyr and tidyr could be:

df %>%
 uncount(last - first + 1) %>%
 group_by(user) %>%
 transmute(weeks = first + 1:n() - 1)

   user  weeks
   <chr> <dbl>
 1 A         1
 2 A         2
 3 A         3
 4 A         4
 5 A         5
 6 A         6
 7 B         4
 8 B         5
 9 B         6
10 B         7
11 B         8
12 B         9

Expanding a data.frame based on (group) values from the data.frame

2 Answers2

data