R: inserting rows for missing observations in sales data

Question

This might be a duplicate. I tried searching for a solution but couldn't come up with one mostly because I don't really know how to frame my question. So I will include a working example:

Imagine I have this df:

 df <- x <- data.frame(Product = c("A", "A", "A", "B","B", "C", "C", "C", "C", "C"), Year = c(2014, 2017, 2018, 2017, 2018, 2013, 2014, 2016, 2017, 2018), Sales  = c(4, 2, 3, 5, 1, 3, 3, 4, 7, 5))

What I want to do is: in the range 2013:2019, add a row for each product for each year even though the product was not sold in that year. So my desired output would be like:

Product   Year   Sales
    A     2013       0
    A     2014       4
    A     2015       0
    A     2016       0
    A     2017       2
    A     2018       3
    A     2019       0

Thanks for the help.

Answer 1

We can use tidyr::complete

tidyr::complete(df,Product,Year = seq(min(Year), max(Year)), fill=list(Sales = 0))

#  Product  Year Sales
#  <fct>   <dbl> <dbl>
# 1 A        2013     0
# 2 A        2014     4
# 3 A        2015     0
# 4 A        2016     0
# 5 A        2017     2
# 6 A        2018     3
# 7 B        2013     0
# 8 B        2014     0
# 9 B        2015     0
#....

If the range has to be fixed (2013:2019) irrespective of the years in the data, we can specify it explicitly.

tidyr::complete(df, Product, Year = 2013:2019, fill = list(Sales = 0))