## Pivot data from wide to long with table containing `from` and `to` columns representing a sequence of numbers

Problem Description:

Below is a reprex of the problem including the solution I’ve come up with. However, I feel like there is a more efficient way of doing this.

Specifically check out the `have`

and `want`

tibbles to see what I am going for. The former has columns `from`

and `to`

that should translate to the `year`

column in the latter.

```
library(tidyverse)
have <- tribble(
~V1, ~V2, ~from, ~to,
"a", "b", 2010, 2013,
"c", "d", 2010, 2010,
"c", "e", 2011, 2013
)
have
#> # A tibble: 3 × 4
#> V1 V2 from to
#> <chr> <chr> <dbl> <dbl>
#> 1 a b 2010 2013
#> 2 c d 2010 2010
#> 3 c e 2011 2013
want <- list()
for(i in 1:nrow(have)){
slice_i <- have %>% slice(i)
seqtab_i <- tibble(year = seq(slice_i$from, slice_i$to))
want[[i]] <- slice_i %>%
select(-from, -to) %>%
bind_cols(seqtab_i)
}
want <- bind_rows(want)
want
#> # A tibble: 8 × 3
#> V1 V2 year
#> <chr> <chr> <int>
#> 1 a b 2010
#> 2 a b 2011
#> 3 a b 2012
#> 4 a b 2013
#> 5 c d 2010
#> 6 c e 2011
#> 7 c e 2012
#> 8 c e 2013
```

^{Created on 2022-12-06 with reprex v2.0.2}

## Solution – 1

```
library(tidyverse)
df %>%
group_by(V1, V2) %>%
group_modify(~ add_row(from = first(.$to), .x)) %>%
select(-to, year = from) %>%
complete(year = first(year):max(year)) %>%
distinct_all()
# A tibble: 8 × 3
# Groups: V1, V2 [3]
V1 V2 year
<chr> <chr> <dbl>
1 a b 2010
2 a b 2011
3 a b 2012
4 a b 2013
5 c d 2010
6 c e 2011
7 c e 2012
8 c e 2013
```

## Solution – 2

You can use map to sequence the years between each row. This will provide you with a list of the years for each applicable row, you can then use unnest() to flatten this.

see code below:

```
have <- tribble(
~V1, ~V2, ~from, ~to,
"a", "b", 2010, 2013,
"c", "d", 2010, 2010,
"c", "e", 2011, 2013
)
df <- have %>%
mutate(years = map2(from, to, seq)) %>%
unnest(years) %>%
select(V1, V2, years)
```

## Solution – 3

another solution

```
library(tidyverse)
have |>
rowwise() |>
mutate(year = list(seq(from, to))) |>
unnest(year) |>
select(-c(from, to))
#> # A tibble: 8 × 3
#> V1 V2 year
#> <chr> <chr> <int>
#> 1 a b 2010
#> 2 a b 2011
#> 3 a b 2012
#> 4 a b 2013
#> 5 c d 2010
#> 6 c e 2011
#> 7 c e 2012
#> 8 c e 2013
```

## Solution – 4

Pivot wide data to the long format:

```
df <- data.frame(v1 = c("a", "c", "c"), v2 = c("b", "d", "e"), from = c("2010", "2010", "2011"), to = c("2013", "2010", "2013"))
df1 <- df %>%
tidyr::pivot_longer(c(from, to), values_to = "year") %>%
dplyr::select(v1, v2, year)
```