Help with Adding all Dates for all Products

General
1

Hello!

I have a dataset that goes back to 2012 & there are several products that are not currently selling. However, I would like to have a dataset that includes all of the products within current dataset for all the dates from 2012 - present. In other words, even if we are not selling any product, we should see it in the list with ZERO sales. I tried taking all variables except for date as a separate dataset and all dates as a separate dataset, and then merged the two with current dataset. I thought it worked, but it seems to have several duplicates. In my original data, I did have complete dates in the data as it covered sales of many other products and thus, I thought extracting dates separately to have complete set of dates kind of worked. Unfortunately it was incorrect approach and thus, some duplicates.

Any help creating a new dataset with all dates and all products and all variables to have a complete picture would be highly appreciated.

# Sample data for concept
library(datapasta)
df <- data.frame(
  stringsAsFactors = FALSE,
              Date = c("1/1/2012","2/1/2012",
                       "3/1/2012","4/1/2012","5/1/2012","6/1/2012","7/1/2012",
                       "8/1/2012","1/1/2012","2/1/2012","3/1/2012","4/1/2012",
                       "5/1/2012","6/1/2012","7/1/2012","8/1/2012","1/1/2013",
                       "2/1/2013","3/1/2013","4/1/2013","5/1/2013",
                       "1/1/2013","2/1/2013","3/1/2013","4/1/2013","5/1/2013",
                       "1/1/2014","2/1/2014","3/1/2014","4/1/2014","5/1/2014",
                       "1/1/2014","2/1/2014","3/1/2014","4/1/2014","5/1/2014",
                       "6/1/2014","7/1/2014","8/1/2014","1/1/2022",
                       "2/1/2022","3/1/2022","4/1/2022","5/1/2022","6/1/2022",
                       "7/1/2022","1/1/2022","2/1/2022","3/1/2022","4/1/2022",
                       "5/1/2022","6/1/2022","7/1/2022"),
           Country = c("USA","USA","USA","USA",
                       "USA","USA","USA","USA","Canada","Canada","Canada",
                       "Canada","Canada","Canada","Canada","Canada","USA",
                       "USA","USA","USA","USA","Canada","Canada","Canada",
                       "Canada","Canada","USA","USA","USA","USA","USA",
                       "USA","USA","USA","USA","USA","USA","USA","USA",
                       "USA","USA","USA","USA","USA","USA","USA","Canada",
                       "Canada","Canada","Canada","Canada","Canada","Canada"),
  Product.Category = c("ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC2","ABC2","ABC2","ABC2","ABC2","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC2","ABC2","ABC2","ABC2","ABC2","ABC2","ABC2"),
           Product = c("ABC0001","ABC0001","ABC0001",
                       "ABC0001","ABC0001","ABC0001","ABC0001","ABC0001",
                       "ABC0001","ABC0001","ABC0001","ABC0001","ABC0001",
                       "ABC0001","ABC0001","ABC0001","ABC0002","ABC0002",
                       "ABC0002","ABC0002","ABC0002","ABC0002","ABC0002",
                       "ABC0002","ABC0002","ABC0002","ABC00022","ABC00022",
                       "ABC00022","ABC00022","ABC00022","ABC0002","ABC0002",
                       "ABC0002","ABC0002","ABC0002","ABC0002","ABC0002",
                       "ABC0002","ABC0002","ABC0002","ABC0002","ABC0002","ABC0002",
                       "ABC0002","ABC0002","ABC00022","ABC00022",
                       "ABC00022","ABC00022","ABC00022","ABC00022","ABC00022"),
             Sales = c(100L,200L,300L,400L,500L,
                       600L,700L,800L,900L,1000L,1100L,1200L,1300L,1400L,
                       1500L,1600L,1000L,1100L,1200L,1300L,1400L,1500L,
                       1600L,1000L,1100L,1200L,700L,800L,900L,1000L,
                       1100L,300L,400L,500L,600L,700L,800L,900L,1000L,500L,
                       600L,300L,400L,300L,240L,180L,100L,200L,300L,
                       400L,500L,600L,700L)

# Tried to consider all products for all months
all_prods <- df%>%
  select(Country, `Product.Category`, Product)%>%
  unique()
2

I think the complete() function from tidyr gives you what you want.

df <- data.frame(
  stringsAsFactors = FALSE,
  Date = c("1/1/2012","2/1/2012",
           "3/1/2012","4/1/2012","5/1/2012","6/1/2012","7/1/2012",
           "8/1/2012","1/1/2012","2/1/2012","3/1/2012","4/1/2012",
           "5/1/2012","6/1/2012","7/1/2012","8/1/2012","1/1/2013",
           "2/1/2013","3/1/2013","4/1/2013","5/1/2013",
           "1/1/2013","2/1/2013","3/1/2013","4/1/2013","5/1/2013",
           "1/1/2014","2/1/2014","3/1/2014","4/1/2014","5/1/2014",
           "1/1/2014","2/1/2014","3/1/2014","4/1/2014","5/1/2014",
           "6/1/2014","7/1/2014","8/1/2014","1/1/2022",
           "2/1/2022","3/1/2022","4/1/2022","5/1/2022","6/1/2022",
           "7/1/2022","1/1/2022","2/1/2022","3/1/2022","4/1/2022",
           "5/1/2022","6/1/2022","7/1/2022"),
  Country = c("USA","USA","USA","USA",
              "USA","USA","USA","USA","Canada","Canada","Canada",
              "Canada","Canada","Canada","Canada","Canada","USA",
              "USA","USA","USA","USA","Canada","Canada","Canada",
              "Canada","Canada","USA","USA","USA","USA","USA",
              "USA","USA","USA","USA","USA","USA","USA","USA",
              "USA","USA","USA","USA","USA","USA","USA","Canada",
              "Canada","Canada","Canada","Canada","Canada","Canada"),
  Product.Category = c("ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC2","ABC2","ABC2","ABC2","ABC2","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC2","ABC2","ABC2","ABC2","ABC2","ABC2","ABC2"),
  Product = c("ABC0001","ABC0001","ABC0001",
              "ABC0001","ABC0001","ABC0001","ABC0001","ABC0001",
              "ABC0001","ABC0001","ABC0001","ABC0001","ABC0001",
              "ABC0001","ABC0001","ABC0001","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC0002","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC0002","ABC00022","ABC00022",
              "ABC00022","ABC00022","ABC00022","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC0002","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC0002","ABC0002","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC00022","ABC00022",
              "ABC00022","ABC00022","ABC00022","ABC00022","ABC00022"),
  Sales = c(100L,200L,300L,400L,500L,
            600L,700L,800L,900L,1000L,1100L,1200L,1300L,1400L,
            1500L,1600L,1000L,1100L,1200L,1300L,1400L,1500L,
            1600L,1000L,1100L,1200L,700L,800L,900L,1000L,
            1100L,300L,400L,500L,600L,700L,800L,900L,1000L,500L,
            600L,300L,400L,300L,240L,180L,100L,200L,300L,
            400L,500L,600L,700L))
dfComplete <- tidyr::complete(data = df,Date,Country,Product.Category,Product,
                              fill = list(Sales=0))
dfComplete
#> # A tibble: 336 x 5
#>    Date     Country Product.Category Product  Sales
#>    <chr>    <chr>   <chr>            <chr>    <dbl>
#>  1 1/1/2012 Canada  ABC1             ABC0001    900
#>  2 1/1/2012 Canada  ABC1             ABC0002      0
#>  3 1/1/2012 Canada  ABC1             ABC00022     0
#>  4 1/1/2012 Canada  ABC2             ABC0001      0
#>  5 1/1/2012 Canada  ABC2             ABC0002      0
#>  6 1/1/2012 Canada  ABC2             ABC00022     0
#>  7 1/1/2012 USA     ABC1             ABC0001    100
#>  8 1/1/2012 USA     ABC1             ABC0002      0
#>  9 1/1/2012 USA     ABC1             ABC00022     0
#> 10 1/1/2012 USA     ABC2             ABC0001      0
#> # ... with 326 more rows

Created on 2022-08-09 by the reprex package (v2.0.1)

3

Thank you @FJCC ! Yes, I think it is working fine. We would like to also see the missing dates/months as well somehow, of course with zeros.

4

Here is one way to do that.

library(tidyverse)
df <- data.frame(
  stringsAsFactors = FALSE,
  Date = c("1/1/2012","2/1/2012",
           "3/1/2012","4/1/2012","5/1/2012","6/1/2012","7/1/2012",
           "8/1/2012","1/1/2012","2/1/2012","3/1/2012","4/1/2012",
           "5/1/2012","6/1/2012","7/1/2012","8/1/2012","1/1/2013",
           "2/1/2013","3/1/2013","4/1/2013","5/1/2013",
           "1/1/2013","2/1/2013","3/1/2013","4/1/2013","5/1/2013",
           "1/1/2014","2/1/2014","3/1/2014","4/1/2014","5/1/2014",
           "1/1/2014","2/1/2014","3/1/2014","4/1/2014","5/1/2014",
           "6/1/2014","7/1/2014","8/1/2014","1/1/2022",
           "2/1/2022","3/1/2022","4/1/2022","5/1/2022","6/1/2022",
           "7/1/2022","1/1/2022","2/1/2022","3/1/2022","4/1/2022",
           "5/1/2022","6/1/2022","7/1/2022"),
  Country = c("USA","USA","USA","USA",
              "USA","USA","USA","USA","Canada","Canada","Canada",
              "Canada","Canada","Canada","Canada","Canada","USA",
              "USA","USA","USA","USA","Canada","Canada","Canada",
              "Canada","Canada","USA","USA","USA","USA","USA",
              "USA","USA","USA","USA","USA","USA","USA","USA",
              "USA","USA","USA","USA","USA","USA","USA","Canada",
              "Canada","Canada","Canada","Canada","Canada","Canada"),
  Product.Category = c("ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC2","ABC2","ABC2","ABC2","ABC2","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC1","ABC1","ABC1","ABC1","ABC1","ABC1","ABC1",
                       "ABC2","ABC2","ABC2","ABC2","ABC2","ABC2","ABC2"),
  Product = c("ABC0001","ABC0001","ABC0001",
              "ABC0001","ABC0001","ABC0001","ABC0001","ABC0001",
              "ABC0001","ABC0001","ABC0001","ABC0001","ABC0001",
              "ABC0001","ABC0001","ABC0001","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC0002","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC0002","ABC00022","ABC00022",
              "ABC00022","ABC00022","ABC00022","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC0002","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC0002","ABC0002","ABC0002","ABC0002",
              "ABC0002","ABC0002","ABC00022","ABC00022",
              "ABC00022","ABC00022","ABC00022","ABC00022","ABC00022"),
  Sales = c(100L,200L,300L,400L,500L,
            600L,700L,800L,900L,1000L,1100L,1200L,1300L,1400L,
            1500L,1600L,1000L,1100L,1200L,1300L,1400L,1500L,
            1600L,1000L,1100L,1200L,700L,800L,900L,1000L,
            1100L,300L,400L,500L,600L,700L,800L,900L,1000L,500L,
            600L,300L,400L,300L,240L,180L,100L,200L,300L,
            400L,500L,600L,700L))
df <- df |> mutate(Date=lubridate::mdy(Date))
StartDate <- as.Date("2012-01-01")
EndDate <- as.Date("2022-07-01")
DateSeq <- data.frame(Date=seq.Date(StartDate,EndDate,by="month"),
                      Country="USA",
                      Product.Category="ABC1",
                      Product="ABC0001",
                      Sales=0)

dfComplete <- rbind(df,DateSeq)
#make sure every label combination appears only once
dfComplete <- dfComplete |> group_by(Date,Country,Product.Category,
                                     Product) |> 
  summarize(Sales=sum(Sales)) |> 
  ungroup()
dfComplete <- complete(data = dfComplete,Date,Country,Product.Category,Product,
                              fill = list(Sales=0))
5

Thanks @FJCC !
Looks like it would work. However, for my case, because my original data is very large (558,824 entries, 10 total columns). Its throwing me the following error in the very last command where we use complete().

This data will only increase with new months added in the future. Any possible solution for this.

Error: Long vectors are not yet supported. Requested output size must be less than 2147483647.
6

I think you could try this

(dist_df <- distinct(df,
                    Country,Product.Category,Product))

dfComplete_2 <- expand_grid(
  Date = seq.Date(StartDate,EndDate,by="month"),
  dist_df
) |> left_join(df |>group_by(Date,Country,Product.Category,
                             Product) |> 
                 summarize(Sales=sum(Sales)) |> 
                 ungroup()) |> mutate(Sales=if_else(condition = is.na(Sales),true = 0L,false = Sales)) |>
  arrange(Product,Product.Category,Country,Date)
7

I don't know enough about handling very large data sets to suggest a solution. It seems likely that even if you could generate the output of the complete() function it would be too large to work with on an ordinary computer. You can calculate the number of rows in the output by multiplying the number of levels in each column. For example, in the data set you provided above, there are 127 months, 2 countries, 2 product categories and 3 products. That yields 127 * 2 * 2 * 3 = 1524 rows. The error message you got shows your real data results in more than 2 billion rows. People do work with large data sets like that, though I don't know if R is the right to tool, but is the burden of turning implicit zeros into explicit zeros going to provide an adequate benefit? If it is, then you might want to start a separate topic about handling large data.

8

explanation of my code above -
I think there's an issue with using complete() on ksingh19's data, which is that complete will want to process every category against every other, so it will make empty slots on Product ABC0001 under Product.Category ABC2 as well as ABC1; I dodged this with using expand_grid so we can can just do all observed distinct groups against every date, which I expect to be significantly less than what I think complete would give from listing out the columns.

i.e. on the data provided, the 'complete' solution would give 1524 rows as you said, but the expand_grid would result in 762

9

Thanks @FJCC , Thanks @nirgrahamuk

Looks like R can't handle this data. I am still getting error with expand_grid()

Error: cannot allocate vector of size 541.5 Mb
10

Thats half a gigabyte. How much RAM is in your system ?

11

Thanks @nirgrahamuk!
So, I reduced my data by grouping to main category levels and I don't see size issue at the moment. However, when we use expand_grid, it shows me the following error on Date as below. dist_df in my original data still shows Date as a column even though its not included in here.

(dist_df <- distinct(df,
                    Country,Product.Category,Product))

# Error
Error in `df_list()`:
! Names must be unique.
x These names are duplicated:
  * "Date" at locations 1 and 2.
i Use argument `.name_repair` to specify repair strategy.
12

Im at a loss because your error is the first mention of df_list that ive seen.
Perhaps you can provide a reprex ?

13

So, when I try to create reprex with smaller data, it runs fine and gives no error.
Looks like size is the main issue.

I will open a new post to solve the size issue, I believe and hopefully, this solution might work then.

Thanks @nirgrahamuk and Thanks @FJCC!
I would like to run both of your solutions after I solve my size issue.

14

@nirgrahamuk , I tried to create reprex again and this time I see error with mutate(). I did create slightly bigger sample this time around with some possible combinations

# Sample Data
#Only Considering 2007 & 2018 to keep data rows within 200 rows limit. 
df <- data.frame(
  stringsAsFactors = FALSE,
       check.names = FALSE,
                Date = c("2007-01-01","2007-01-01",
                         "2007-01-01","2007-01-01","2007-01-01","2007-01-01",
                         "2007-02-01","2007-02-01","2007-02-01",
                         "2007-02-01","2007-02-01","2007-02-01","2007-03-01",
                         "2007-03-01","2007-03-01","2007-03-01","2007-03-01",
                         "2007-03-01","2007-04-01","2007-04-01","2007-04-01",
                         "2007-04-01","2007-04-01","2007-04-01","2007-05-01",
                         "2007-05-01","2007-05-01","2007-05-01","2007-05-01",
                         "2007-05-01","2007-06-01","2007-06-01","2007-06-01",
                         "2007-06-01","2007-06-01","2007-06-01","2007-07-01",
                         "2007-07-01","2007-07-01","2007-07-01","2007-07-01",
                         "2007-07-01","2007-08-01","2007-08-01","2007-08-01",
                         "2007-08-01","2007-08-01","2007-08-01","2007-09-01",
                         "2007-09-01","2007-09-01","2007-09-01","2007-09-01",
                         "2007-09-01","2007-10-01","2007-10-01","2007-10-01",
                         "2007-10-01","2007-10-01","2007-10-01","2007-11-01",
                         "2007-11-01","2007-11-01","2007-11-01",
                         "2007-11-01","2007-11-01","2007-12-01","2007-12-01",
                         "2007-12-01","2007-12-01","2007-12-01","2007-12-01",
                         "2018-01-01","2018-01-01","2018-01-01","2018-01-01",
                         "2018-01-01","2018-01-01","2018-01-01","2018-01-01",
                         "2018-02-01","2018-02-01","2018-02-01","2018-02-01",
                         "2018-02-01","2018-02-01","2018-02-01","2018-02-01",
                         "2018-03-01","2018-03-01","2018-03-01","2018-03-01",
                         "2018-03-01","2018-03-01","2018-03-01","2018-03-01",
                         "2018-03-01","2018-04-01","2018-04-01","2018-04-01",
                         "2018-04-01","2018-04-01","2018-04-01","2018-04-01",
                         "2018-04-01","2018-05-01","2018-05-01","2018-05-01",
                         "2018-05-01","2018-05-01","2018-05-01","2018-05-01",
                         "2018-05-01","2018-06-01","2018-06-01","2018-06-01",
                         "2018-06-01","2018-06-01","2018-06-01",
                         "2018-06-01","2018-06-01","2018-06-01","2018-07-01",
                         "2018-07-01","2018-07-01","2018-07-01","2018-07-01",
                         "2018-07-01","2018-07-01","2018-07-01","2018-07-01",
                         "2018-08-01","2018-08-01","2018-08-01","2018-08-01",
                         "2018-08-01","2018-08-01","2018-08-01","2018-08-01",
                         "2018-09-01","2018-09-01","2018-09-01","2018-09-01",
                         "2018-09-01","2018-09-01","2018-09-01","2018-09-01",
                         "2018-10-01","2018-10-01","2018-10-01","2018-10-01",
                         "2018-10-01","2018-10-01","2018-10-01","2018-10-01",
                         "2018-10-01","2018-11-01","2018-11-01","2018-11-01",
                         "2018-11-01","2018-11-01","2018-11-01","2018-11-01",
                         "2018-11-01","2018-11-01","2018-11-01","2018-12-01",
                         "2018-12-01","2018-12-01","2018-12-01","2018-12-01",
                         "2018-12-01","2018-12-01","2018-12-01",
                         "2018-12-01","2018-12-01","2018-12-01","2018-12-01"),
             Country = c("Canada","Canada",
                         "Canada","USA","USA","USA","Canada","Canada","Canada",
                         "USA","USA","USA","Canada","Canada","Canada","USA",
                         "USA","USA","Canada","Canada","Canada","USA",
                         "USA","USA","Canada","Canada","Canada","USA","USA",
                         "USA","Canada","Canada","Canada","USA","USA",
                         "USA","Canada","Canada","Canada","USA","USA","USA",
                         "Canada","Canada","Canada","USA","USA","USA",
                         "Canada","Canada","Canada","USA","USA","USA","Canada",
                         "Canada","Canada","USA","USA","USA","Canada",
                         "Canada","Canada","USA","USA","USA","Canada",
                         "Canada","Canada","USA","USA","USA","Canada","Canada",
                         "Canada","Canada","USA","USA","USA","USA",
                         "Canada","Canada","Canada","Canada","USA","USA","USA",
                         "USA","Canada","Canada","Canada","Canada","USA",
                         "USA","USA","USA","USA","Canada","Canada","Canada",
                         "Canada","USA","USA","USA","USA","Canada",
                         "Canada","Canada","Canada","USA","USA","USA","USA",
                         "Canada","Canada","Canada","Canada","USA","USA",
                         "USA","USA","USA","Canada","Canada","Canada","Canada",
                         "USA","USA","USA","USA","USA","Canada","Canada",
                         "Canada","Canada","USA","USA","USA","USA",
                         "Canada","Canada","Canada","Canada","USA","USA","USA",
                         "USA","Canada","Canada","Canada","Canada","USA",
                         "USA","USA","USA","USA","Canada","Canada",
                         "Canada","Canada","USA","USA","USA","USA","USA","USA",
                         "Canada","Canada","Canada","Canada","Canada","USA",
                         "USA","USA","USA","USA","USA","USA"),
            Category = c("BM","SS","TM","BM",
                         "SS","TM","BM","SS","TM","BM","SS","TM","BM","SS",
                         "TM","BM","SS","TM","BM","SS","TM","BM","SS",
                         "TM","BM","SS","TM","BM","SS","TM","BM","SS",
                         "TM","BM","SS","TM","BM","SS","TM","BM","SS",
                         "TM","BM","SS","TM","BM","SS","TM","BM","SS",
                         "TM","BM","SS","TM","BM","SS","TM","BM","SS",
                         "TM","BM","SS","TM","BM","SS","TM","BM","SS","TM",
                         "BM","SS","TM","BM","BM","SS","TM","BM","BM",
                         "SS","TM","BM","BM","SS","TM","BM","BM","SS",
                         "TM","BM","BM","SS","TM","BM","BM","SS","SS",
                         "TM","BM","BM","SS","TM","BM","BM","SS","TM",
                         "BM","BM","SS","TM","BM","BM","SS","TM","BM",
                         "BM","SS","TM","BM","BM","BM","SS","TM","BM","BM",
                         "SS","TM","BM","BM","SS","SS","TM","BM","BM",
                         "SS","TM","BM","BM","SS","TM","BM","BM","SS",
                         "TM","BM","BM","SS","TM","BM","BM","SS","TM",
                         "BM","BM","SS","SS","TM","BM","BM","SS","TM",
                         "BM","BM","BM","SS","SS","TM","BM","BM","BM",
                         "SS","TM","BM","BM","BM","BM","SS","SS","TM"),
                Type = c("FBF","FSS","MTF","FBF",
                         "FSS","MTF","FBF","FSS","MTF","FBF","FSS","MTF",
                         "FBF","FSS","MTF","FBF","FSS","MTF","FBF",
                         "FSS","MTF","FBF","FSS","MTF","FBF","FSS","MTF",
                         "FBF","FSS","MTF","FBF","FSS","MTF","FBF","FSS",
                         "MTF","FBF","FSS","MTF","FBF","FSS","MTF","FBF",
                         "FSS","MTF","FBF","FSS","MTF","FBF","FSS","MTF",
                         "FBF","FSS","MTF","FBF","FSS","MTF","FBF","FSS",
                         "MTF","FBF","FSS","MTF","FBF","FSS","MTF","FBF",
                         "FSS","MTF","FBF","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD","Freestanding Multidoor 3 Doors",
                         "FSS","MTF","Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD","Freestanding Multidoor 3 Doors",
                         "FSS","MTF","Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF","Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","Built-in Side by Side",
                         "FSS","MTF","Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF","Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD","Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","N/A Freestanding Multidoor / French Door",
                         "FSS","MTF","Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","Built-in Side by Side","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD","Freestanding Multidoor 3 Doors",
                         "FSS","MTF","Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF","Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD","Freestanding Multidoor 3 Doors",
                         "Built-in Side by Side","FSS","MTF","Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD",
                         "Freestanding Multidoor 3 Doors","Freestanding Multidoor 4 Doors",
                         "Built-in Side by Side","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD","Freestanding Multidoor 3 Doors",
                         "Freestanding Multidoor 4 Door CD","FSS","MTF",
                         "Freestanding Multidoor 3 Door CD","Freestanding Multidoor 3 Doors",
                         "Freestanding Multidoor 4 Door CD",
                         "Freestanding Multidoor 4 Doors","Built-in Side by Side","FSS","MTF"),
       `Sales/Value` = c("Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales","Sales","Sales",
                         "Sales","Sales","Sales","Sales"),
               Sales = c(599,494,12405,2527,
                         21340,85437,704,548,13458,2753,16114,77410,1444,
                         788,14971,3997,26401,127672,1000,662,19306,3306,
                         29280,118265,1945,657,21066,4985,40786,119898,
                         2293,734,22281,4283,39411,132545,2473,988,23612,
                         3995,36324,146997,2790,1268,25277,6796,34440,
                         140139,2160,1000,22869,10091,50981,125366,2368,
                         857,24131,13340,45063,118031,2958,807,18723,
                         14987,39653,119328,2145,557,19435,8735,36685,89365,
                         439,385,151,8133,2251,13578,37775,93173,363,
                         434,139,9070,3191,12446,38431,99606,411,434,286,
                         9314,3987,16617,1,60131,139088,494,738,265,
                         8410,3110,13480,43046,126176,568,452,227,11072,
                         5886,18735,70588,149781,565,462,431,13645,5754,
                         16446,0,66483,168435,590,471,253,12768,4900,
                         11426,0,54272,111584,476,668,226,14349,4970,
                         14874,58666,159624,744,563,368,13005,9791,14027,
                         48159,134546,648,417,833,11123,9495,25121,8,
                         75378,139355,541,623,226,10735,9286,17687,557,2,
                         47047,117296,529,640,5,137,8208,6601,12610,28,
                         118,19,30552,96270)
  )
df <-df%>%
  mutate(Date = ymd(Date))

StartDate <- as.Date("2007-01-01")
EndDate <- as.Date("2019-07-01")

df <- df%>%
  mutate(Sales = as.numeric(Sales))

(dist_df <- distinct(df,
                     Country, Category, Type, `Sales/Value`))

dfComplete_2 <- expand_grid(
  Date = seq.Date(StartDate,EndDate,by="month"),
  dist_df
) |> left_join(df |>group_by(Date,Country, Category, Type, `Sales/Value`) |> 
                 summarize(`Sales` =sum(`Sales`)) |> 
                 ungroup()) |> mutate(`Sales`=if_else(condition = is.na(`Sales`),true = 0L,false = `Sales`)) |>
  arrange(`Sales/Value`, `Type`, `Category`, Country, Date)
15

in this data Sales columns is numeric/double and not integer as previously so the following fragment should change from

if_else(condition = is.na(`Sales`),true = 0L,false = `Sales`)

to

if_else(condition = is.na(`Sales`),true = 0,false = `Sales`)
16

Thanks @nirgrahamuk! Yes, the sample works with it. But my original large dataset still shows the following error:

Error in `df_list()`:
! Names must be unique.
x These names are duplicated:
  * "Date" at locations 1 and 2.
i Use argument `.name_repair` to specify repair strategy.

Do you think it could be because of size issue?

17

I can't begin to guess what the issue might be, because you haven't presented anything to me as df_list that I could consider, does it have an analogue with any particular data.frame we have defined in our example ? is it a perfect analogue, or has it been modified in some way ?

18

Last Sample data is exact same as my original data. The only difference is the Dates are only for 2017 & 2018 to be under 200 rows. In other words, original data does have values from 2017-01-01 all the way to 2022-07-01. However, sample data has missing dates here.

19

I don't know what you are trying to tell me. I can only tell you , that we have shared code, with objects with specific names, and you are reporting to me an error, involving a name that I've no reference for. I don't know what I should think that 'df_list' is or contains.

20

No worries @nirgrahamuk! Thanks for checking it out!