1

I would like to add new rows to a dataframe that I am working with, based on data that is missing from the dataframe.

Here is an example dataframe.

year <- c(2001,2001,2002,2002,2003,2004,2004,2005)
make <- c('Honda', 'Ford', 'Honda', 'Ford', 'Honda', 'Honda', 'Ford', 'Honda')
number_manufactured <- c(10, 20, 15, 47, 14, 19, 35, 9)

cars <- data.frame(year, make, number_manufactured)

I would like to add a row to the data frame for values that are missing with number_manufactured = 0, such as: (2003, Ford, 0) and (2005, Ford, 0)

My desired data frame would be this:

year <- c(2001,2001,2002,2002,2003,2003,2004,2004,2005,2005)
make <- c('Honda', 'Ford', 'Honda', 'Ford', 'Honda','Ford', 'Honda', 'Ford', 'Honda', 'Ford')
number_manufactured <- c(10, 20, 15, 47, 14, 0, 19, 35, 9, 0)

cars <- data.frame(year, make, number_manufactured)

Thanks for the help!

Kendall Reid
  • 75
  • 1
  • 2
  • 6

2 Answers2

1

Here is a base R method using expand.grid and merge.

# get new data.frame
dfNew <- merge(cars, expand.grid(unique(cars$year), unique(cars$make)), 
               by.x=c("year", "make"), by.y=c("Var1", "Var2"), all=TRUE)
# fill in 0s
dfNew$number_manufactured[is.na(dfNew$number_manufactured)] <- 0

expand.grid returns a data.frame with all combinations of two vectors. Here, it is fed the unique levels of year and make. this is merged onto the original data.frame to produce the new data.frame, with new observations included using the all=TRUE argument. The new observations are NA for number manufactured, so the second line converts these to 0s.

lmo
  • 37,904
  • 9
  • 56
  • 69
1

tidyr::complete is handy for this sort of thing. You list the variables in the dataset you want to add rows for. You can also list what you want to fill the other variables with (by default you get NA) via fill.

library(tidyr)
complete(cars, year, make, fill = list(number_manufactured = 0))
aosmith
  • 34,856
  • 9
  • 84
  • 118