Edu Edu - 1 month ago 10
R Question

R include rows conditioned to other variables with `add_row`

I have a data.frame like

test
. It corresponds to information associated with a registry of firms.
year.entry
reflects the time period when a firm gets into the registry.
items
are elements that represent capacity and remain fixed throughout time. It may happen that the firm increases its capacity in a particular year. My aim is to present this information longitudinally.

For doing that I would ideally include rows for the years that are missing between 2010 and 2015. I have tried with this with
add_row()
from
tibble
but I am having difficulties to make it work.

> test %>% add_row(firm = firm, year.entry == (year.entry)+1, item = item, .before = row_number(year.entry) == n())

Error in eval(expr, envir, enclos) : object 'firm' not found


I wonder whether there is an easier way to solve this problem. The ideal data frame should look like this:

firm year.entry item
<chr> <chr> <int>
1 1-102642692 2010 15
2 1-102642692 2011 15
3 1-102642692 2012 15
4 1-102642692 2013 15
5 1-102642692 2014 15
6 1-102642692 2015 8


test
is given by:

test = data.frame(firm = c("1-102642692", "1-102642692"), year.entry = c(2010, 2015), item =c(15,8))

Answer

I add a dummy firm to the data to use later.

  • First I make sure every firm has all the years of the period of interest with complete. That is why I entered a dummy firm.
  • The missing years are added to the dataframe.
  • Then I take the last known value carried forward with na.locf.
  • When completed I remove the dummy firm.

comp <- data.frame(firm="test", year.entry= (2009:2016), item=0)

 test = data.frame(firm = c("1-102642692", "1-102642692"), year.entry = c(2010, 2015), item =c(15,8))

      library(zoo)

 rbind(test,comp) %>% 
   complete(firm,year.entry) %>% 
   arrange(firm, year.entry)%>%
   group_by(firm) %>%
   mutate(item = na.locf(item, na.rm=FALSE)) %>%
   filter(firm !="test")

result:

 firm              year.entry item
 <fctr>            <dbl>      <dbl>
 1-102642692       2009       NA
 1-102642692       2010       15
 1-102642692       2011       15
 1-102642692       2012       15
 1-102642692       2013       15
 1-102642692       2014       15
 1-102642692       2015        8
 1-102642692       2016        8