Expand ranges defined by "from" and "to" columns

user_157089 0
Viewed 8886

I have a data frame containing "name" of U.S. Presidents, the years when they start and end in office, ("from" and "to" columns). Here is a sample:

name           from  to
Bill Clinton   1993 2001
George W. Bush 2001 2009
Barack Obama   2009 2012

...and the output from dput:

dput(tail(presidents, 3))
structure(list(name = c("Bill Clinton", "George W. Bush", "Barack Obama"
), from = c(1993, 2001, 2009), to = c(2001, 2009, 2012)), .Names = c("name", 
"from", "to"), row.names = 42:44, class = "data.frame")

I want to create data frame with two columns ("name" and "year"), with a row for each year that a president was in office. Thus, I need to create a regular sequence with each year from "from", to "to". Here's my expected out:

name           year
Bill Clinton   1993
Bill Clinton   1994
...
Bill Clinton   2000
Bill Clinton   2001
George W. Bush 2001
George W. Bush 2002
... 
George W. Bush 2008
George W. Bush 2009
Barack Obama   2009
Barack Obama   2010
Barack Obama   2011
Barack Obama   2012

I know that I can use data.frame(name = "Bill Clinton", year = seq(1993, 2001)) to expand things for a single president, but I can't figure out how to iterate for each president.

How do I do this? I feel that I should know this, but I'm drawing a blank.

Update 1

OK, I've tried both solutions, and I'm getting an error:

foo<-structure(list(name = c("Grover Cleveland", "Benjamin Harrison", "Grover Cleveland"), from = c(1885, 1889, 1893), to = c(1889, 1893, 1897)), .Names = c("name", "from", "to"), row.names = 22:24, class = "data.frame")
ddply(foo, "name", summarise, year = seq(from, to))
Error in seq.default(from, to) : 'from' must be of length 1
dataframe sequence r
9 Answers

An alternate tidyverse approach using unnest and map2. 

library(tidyverse)

presidents %>%
  unnest(year = map2(from, to, seq)) %>%
  select(-from, -to)

#              name  year
# 1    Bill Clinton  1993
# 2    Bill Clinton  1994
...
# 21   Barack Obama  2011
# 22   Barack Obama  2012

Edit: From tidyr v1.0.0 new variables can no longer be created as part of unnest().

presidents %>%
  mutate(year = map2(from, to, seq)) %>%
  unnest(year) %>%
  select(-from, -to)
user_28352610

Two base solutions.

Using sequence:

len = d$to - d$from + 1
data.frame(name = d$name[rep(1:nrow(d), len)], year = sequence(len, d$from)).

Using mapply:

l <- mapply(`:`, d$from, d$to) 
data.frame(name = d$name[rep(1:nrow(d), lengths(l))], year = unlist(l))

#              name year
# 1    Bill Clinton 1993
# 2    Bill Clinton 1994
# ...snip
# 8    Bill Clinton 2000
# 9    Bill Clinton 2001
# 10 George W. Bush 2001
# 11 George W. Bush 2002
# ...snip
# 17 George W. Bush 2008
# 18 George W. Bush 2009
# 19   Barack Obama 2009
# 20   Barack Obama 2010
# 21   Barack Obama 2011
# 22   Barack Obama 2012
user_18517120

Another option using tidyverse could be to gather data into long format, group_by name and create a sequence between from and to date. 

library(tidyverse)

presidents %>%
  gather(key, date, -name) %>%
  group_by(name) %>%
  complete(date = seq(date[1], date[2]))%>%
  select(-key) 

# A tibble: 22 x 2
# Groups:   name [3]
#   name          date
#   <chr>        <dbl>
# 1 Barack Obama  2009
# 2 Barack Obama  2010
# 3 Barack Obama  2011
# 4 Barack Obama  2012
# 5 Bill Clinton  1993
# 6 Bill Clinton  1994
# 7 Bill Clinton  1995
# 8 Bill Clinton  1996
# 9 Bill Clinton  1997
#10 Bill Clinton  1998
# … with 12 more rows
user_39629140

Use by to create a by list L of data.frames, one data.frame per president, and then rbind them together. No packages are used. 

L <- by(presidents, presidents$name, with, data.frame(name, year = from:to))
do.call("rbind", setNames(L, NULL))

If you don't mind row names then the last line could be reduced to just:

do.call("rbind", L)
user_5165480

Another solution using dplyr and tidyr:

library(magrittr) # for pipes
df <- data.frame(tata = c('toto1', 'toto2'), from = c(2000, 2004), to = c(2001, 2009))

#    tata from   to
# 1 toto1 2000 2001
# 2 toto2 2004 2009

df %>% 
  dplyr::as.tbl() %>%
  dplyr::rowwise() %>%
  dplyr::mutate(combined = list(seq(from, to))) %>%
  dplyr::select(-from, -to) %>%
  tidyr::unnest(combined)

#   tata  combined
#   <fct>    <int>
# 1 toto1     2000
# 2 toto1     2001
# 3 toto2     2004
# 4 toto2     2005
# 5 toto2     2006
# 6 toto2     2007
# 7 toto2     2008
# 8 toto2     2009
user_66826800
Related