Inserting missing years to complete a data.frame

5
votes

I'm creating a dataframe containing the number of incidents of a certain kind in each state in each year from 2000 to 2010 (pretend that they are gun incidents):

states <- c('Texas', 'Texas', 'Arizona', 'California', 'California')
incidents <- c(1, 1, 2, 1, 4)
years <- c(2000, 2008, 2004, 2002, 2007)

DF <- data.frame(states, incidents, years)

> DF
      states incidents years
1      Texas         1  2000
2      Texas         1  2008
3    Arizona         2  2004
4 California         1  2002
5 California         4  2007

I want to insert rows to complete the dataset, e.g. zeros for Texas for 2001, 2002, 2003, ... 2007, and for 2009 and 2010. And likewise, zeros for Arizona for all years except 2004. Same thing for California.

How can I do this?

2
rdataframe

2 Answers

6
votes

You can use tidyr::complete to fill in missing years (2010:2010) and values with 0.

library(tidyr)
DFfilled <- DF %>%
    complete(states, years = 2000:2010, 
             fill = list(incidents = 0)) %>%
    as.data.frame()

PS:
If there are entries with year 2010 in your data (now it's only up to 2008) you can use full_seq(years, 1) instead of 2000:2010.

0
votes

I would do it by creating an artifical data.frame and merge this data.frame with DF:

states <- c('Texas', 'Texas', 'Arizona', 'California', 'California')
incidents <- c(1, 1, 2, 1, 4)
years <- c(2000, 2008, 2004, 2002, 2007)

DF <- data.frame(states, incidents, years)

tmp <- data.frame(years=rep(seq(min(DF$years), 
                          max(DF$years)),
                          each=length(unique(DF$states))), 
                  states=unique(DF$states) )
DF2 <- merge(DF, tmp, by=c('years','states'),all=T)
DF2[is.na(DF2$incidents),]$incidents <- 0