CODEWITHSUNDEEP
r
Kendall Reid
Kendall Reid

Reputation: 75

How to create new rows in a data frame based on missing data in R

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!

Upvotes: 1

Views: 117

Answers (2)

aosmith
aosmith

Reputation: 36076

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))

Upvotes: 1

lmo
lmo

Reputation: 38500

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.

Upvotes: 1

Related Questions