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

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

Contents

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)
``````
Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.