Rotate the data frame with a line for each identifier and law (from the beginning and end of the year) into a file with a line for each identifier and year

Question

I have a df called laws, with a string for each law (one for each id):

laws <- data.frame(id=c(1,2,3),beginyear=c(2001,2002,2005),endyear=c(2003,2005,2006), law1=c(0,0,1), law2=c(1,0,1))

from which I want to create a middle name idyearwith a string for each id and year:

idyear <- data.frame(id=c(rep(1,6),rep(2,6),rep(3,6)), year=(rep(c(2001:2006),3)), law1=c(rep(0,16),1,1), law2=c(1,1,1,rep(0,13),1,1))

How can I efficiently write code to get idyeardf output from lawsdf? Two variables of the law are indicator variables == 1 if idyear$yearis> = laws$beginyearAND idyear$yearis <= laws$endyear.

I start with R, but I'm ready to try anything (apply, loops, etc.) to make this work.

Answer 1

tidyverse. as.data.frame() , tbl .

library(tidyverse)

idyear <- laws %>%
  mutate(year = map2(beginyear, endyear, `:`)) %>%
  unnest() %>%
  complete(id, year = full_seq(year, period = 1L), fill = list(law1 = 0L, law2 = 0L)) %>%
  select(-beginyear, -endyear) %>%
  as.data.frame()
idyear
#    id year law1 law2
# 1   1 2001    0    1
# 2   1 2002    0    1
# 3   1 2003    0    1
# 4   1 2004    0    0
# 5   1 2005    0    0
# 6   1 2006    0    0
# 7   2 2001    0    0
# 8   2 2002    0    0
# 9   2 2003    0    0
# 10  2 2004    0    0
# 11  2 2005    0    0
# 12  2 2006    0    0
# 13  3 2001    0    0
# 14  3 2002    0    0
# 15  3 2003    0    0
# 16  3 2004    0    0
# 17  3 2005    1    1
# 18  3 2006    1    1

Answer 2

1) base expand.grid 18 x 2 id year, merge laws. law1 law2, year beginyear endyear. beginyear endyear. .

g <- with(laws, expand.grid(year = min(beginyear):max(endyear), id = id))
m <- merge(g, laws)
m[m$year < m$beginyear | m$year > m$endyear, c("law1", "law2")] <- 0
m <- subset(m, select = - c(beginyear, endyear))

# check
identical(m, idyear)
## [1] TRUE

2) magrittr , (1), , magrittr . .

library(magrittr)

laws %$%
     expand.grid(year = min(beginyear):max(endyear), id = id) %>%
     merge(laws) %$%
     { .[year < beginyear | year > endyear, c("law1", "law2")] <- 0; .} %>%
     subset(select = - c(beginyear, endyear))

: . (2).

Answer 3

mapply .

# Function to expand year between begin and end
gen_data <- function(x_id, x_beginyear, x_endyear, x_law1, x_law2){
  df <- data.frame(x_id, x_beginyear:x_endyear, x_law1, x_law2)
  df
}

idyearlst <- data.frame()

idyearlst <- rbind(idyearlst, mapply(gen_data, laws$id, laws$beginyear,
 laws$endyear, laws$law1, laws$law2))

# Finally convert list to data.frame
idyear <- setNames(do.call(rbind.data.frame, idyearlst), c("id", "year", "law1", "law2"))

Result will be like:
> idyear
     id year law1 law2
V1.1  1 2001    0    1
V1.2  1 2002    0    1
V1.3  1 2003    0    1
V2.4  2 2002    0    0
V2.5  2 2003    0    0
V2.6  2 2004    0    0
V2.7  2 2005    0    0
V3.8  3 2005    1    1
V3.9  3 2006    1    1

Answer 4

Kind of an ugly approach, but I think it gets what you need using G. Grothendieck's data frame g expand.gridas the base and your lawsdataframe.

new.df <- data.frame(t(apply(g, 1, function(x){
  yearspan = laws[laws$id == x['id'], 'beginyear']:laws[laws$id == x['id'], 'endyear']
  law1 = laws$law1[laws$id == x['id'] & x['year'] %in% yearspan]
  law2 = laws$law2[laws$id == x['id'] & x['year'] %in% yearspan]
  x['law1'] = ifelse(length(law1 > 0), law1, 0)
  x['law2'] = ifelse(length(law2 > 0), law2, 0)
  return(x)
})))

> new.df
   id year law1 law2
1   1 2001    0    1
2   1 2002    0    1
3   1 2003    0    1
4   1 2004    0    0
5   1 2005    0    0
6   1 2006    0    0
7   2 2001    0    0
8   2 2002    0    0
9   2 2003    0    0
10  2 2004    0    0
11  2 2005    0    0
12  2 2006    0    0
13  3 2001    0    0
14  3 2002    0    0
15  3 2003    0    0
16  3 2004    0    0
17  3 2005    1    1
18  3 2006    1    1

Libraries:

dplyr(for arrange, optional)

Data:

laws <- data.frame(id=c(1,2,3),
                   beginyear=c(2001,2002,2005),
                   endyear=c(2003,2005,2006), 
                   law1=c(0,0,1), law2=c(1,0,1))

g <- with(laws, expand.grid(id = id, year = min(beginyear):max(endyear)))
g <- arrange(g, id)