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

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)