Create a new dataframe with rows for every value in a sequence between two columns in a previous dataframe

user_3369614 0
Viewed 1026

I have a dataframe, where two columns represent the beginning and end of a range of dates. So:

df <- data.frame(var=c("A", "B"), start_year=c(2000, 2002), end_year=c(2005, 2004))

> df
  var start_year end_year
1   A       2000     2005
2   B       2002     2004

And I'd like to create a new dataframe, where there is a row for every value between start_year and end_year, for each var.

So the result should look like:

> newdf
  var year
1   A 2000
2   A 2001
3   A 2002
4   A 2003
5   A 2004
6   A 2005
7   B 2002
8   B 2003
9   B 2004

Ideally this would involve something from the tidyverse. I've been trying different things with dplyr::group_by and tidyr::gather, but I'm not having any luck.

tidyr dplyr dataframe r
3 Answers

We can use map2 to get the sequence from 'start_year' to 'end_year' and unnest the list column to expand the data into 'long' format

library(tidyverse)
df %>%
   transmute(var, year = map2(start_year, end_year, `:`)) %>%
   unnest
#   var year
#1   A 2000
#2   A 2001
#3   A 2002
#4   A 2003
#5   A 2004
#6   A 2005
#7   B 2002
#8   B 2003
#9   B 2004

Or another option is complete

df %>%
     group_by(var) %>% 
     complete(start_year = start_year:end_year) %>% 
     select(var, year = start_year)

Or in base R with stack and Map

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

NOTE: First posted the solution with Map and stack

In case of other variations,

stack(setNames(Map(`:`, df[[2]], df[[3]]), df$var))
stack(setNames(do.call(mapply, c(FUN = `:`, df[-1])), df$var))
user_37322710

As akrun demonstrated, it's probably easier to do it without gather and group_by (as mentioned in the question). But in case you're curious how to do it that way, here it is

df %>% 
  gather(key, value, -var) %>% 
  group_by(var) %>% 
  expand(year = value[1]:value[2])

# # A tibble: 9 x 2
# # Groups:   var [2]
#   var    year
#   <fct> <int>
# 1 A      2000
# 2 A      2001
# 3 A      2002
# 4 A      2003
# 5 A      2004
# 6 A      2005
# 7 B      2002
# 8 B      2003
# 9 B      2004

Here's the same idea, convert to long and expand, in data.table (same output)

library(data.table)
setDT(df)

melt(df, 'var')[, .(year = value[1]:value[2]), var]

Edit: As markus points out, you don't need to convert to long first with data.table, you can do it in one step (not counting the two lines library/setDT in the code block above). This is a similar approach to akrun's tidyverse answer.

df[, .(year = start_year:end_year), by=var]
user_67627550

A short base R solution with seq.

stack(setNames(Map(seq, df[[2]], df[[3]]), df[[1]]))
#   values ind
# 1   2000   A
# 2   2001   A
# 3   2002   A
# 4   2003   A
# 5   2004   A
# 6   2005   A
# 7   2002   B
# 8   2003   B
# 9   2004   B

Data

df <- structure(list(var = structure(1:2, .Label = c("A", "B"), class = "factor"), 
    start_year = c(2000, 2002), end_year = c(2005, 2004)), class = "data.frame", row.names = c(NA, 
-2L))
user_65740380
Related