Hello All!
Is there a way to complete the data by group per month. There are some months where we don't have specific group type, but we would like to still include it with NA values. It depends on group type as for some we have few categories and for others more. We are looking to include categories per group if it exists for that group in any given month.
Any help will be greatly appreciated!!
# Sample Data
library(tidyverse)
df <- data.frame(
stringsAsFactors = FALSE,
ACCT_DT = c(202301L,202301L,
202301L,202301L,202301L,202301L,202301L,202301L,
202301L,202301L,202301L,202301L,202302L,
202302L,202302L,202302L,202302L,202302L,202302L,
202302L,202302L,202302L,202302L,202302L,202302L,
202303L,202303L,202303L,202303L,202303L,
202303L,202303L,202303L,202303L,202303L,202303L,
202304L,202304L,202304L,202304L,202304L,202304L,
202304L,202304L,202304L,202304L,202304L,
202304L,202305L,202305L,202305L,202305L,202305L,
202305L,202305L,202305L,202305L,202305L,202305L,
202305L,202305L,202306L,202306L,202306L,
202306L,202306L,202306L,202306L,202306L,202306L,
202306L,202306L,202306L,202306L,202307L,202307L,
202307L,202307L,202307L,202307L,202307L,
202307L,202307L,202307L,202307L,202307L,202308L,
202308L,202308L,202308L,202308L,202308L,202308L,
202308L,202308L,202308L,202308L,202308L,
202308L,202309L,202309L,202309L,202309L,202309L,
202309L,202309L,202309L,202309L,202309L,202309L,
202309L,202309L,202310L,202310L,202310L,
202310L,202310L,202310L,202310L,202310L,202310L,
202310L,202310L),
Country = c("USA","USA",
"Canada","Canada","Canada","Canada","Canada",
"Canada","Canada","Canada","Canada","Canada","USA",
"USA","USA","Canada","Canada","Canada",
"Canada","Canada","Canada","Canada","Canada","Canada",
"Canada","USA","USA","Canada","Canada",
"Canada","Canada","Canada","Canada","Canada",
"Canada","Canada","USA","USA","Canada","Canada",
"Canada","Canada","Canada","Canada","Canada",
"Canada","Canada","Canada","USA","USA","Canada",
"Canada","Canada","Canada","Canada","Canada",
"Canada","Canada","Canada","Canada","Canada",
"USA","USA","USA","Canada","Canada","Canada",
"Canada","Canada","Canada","Canada","Canada",
"Canada","Canada","USA","USA","USA","Canada",
"Canada","Canada","Canada","Canada","Canada",
"Canada","Canada","Canada","USA","USA","USA",
"Canada","Canada","Canada","Canada","Canada",
"Canada","Canada","Canada","Canada","Canada","USA",
"USA","USA","Canada","Canada","Canada",
"Canada","Canada","Canada","Canada","Canada","Canada",
"Canada","USA","USA","Canada","Canada",
"Canada","Canada","Canada","Canada","Canada",
"Canada","Canada"),
City = c("NYC","NYC",
"Toronto","Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto",
"Toronto","NYC","NYC","NYC","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","NYC","NYC",
"Toronto","Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto","NYC",
"NYC","Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","NYC","NYC","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto","Toronto",
"NYC","NYC","NYC","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","NYC","NYC","NYC",
"Toronto","Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto","NYC",
"NYC","NYC","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","NYC","NYC","NYC","Toronto",
"Toronto","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto","Toronto",
"NYC","NYC","Toronto","Toronto","Toronto",
"Toronto","Toronto","Toronto","Toronto","Toronto",
"Toronto"),
Group = c("Bikes","Bikes",
"MotorBikes","MotorBikes","MotorBikes",
"MotorBikes","Bikes","Bikes","Bikes","Bikes","Bikes",
"Bikes","Bikes","Bikes","Bikes","MotorBikes",
"MotorBikes","MotorBikes","MotorBikes","Bikes",
"Bikes","Bikes","Bikes","Bikes","Bikes","Bikes",
"Bikes","MotorBikes","MotorBikes","MotorBikes",
"MotorBikes","Bikes","Bikes","Bikes","Bikes",
"Bikes","Bikes","Bikes","MotorBikes",
"MotorBikes","MotorBikes","MotorBikes","Bikes","Bikes",
"Bikes","Bikes","Bikes","Bikes","Bikes","Bikes",
"MotorBikes","MotorBikes","MotorBikes",
"MotorBikes","Bikes","Bikes","Bikes","Bikes","Bikes",
"Bikes","Bikes","MotorBikes","Bikes","Bikes",
"MotorBikes","MotorBikes","MotorBikes",
"MotorBikes","Bikes","Bikes","Bikes","Bikes","Bikes",
"Bikes","MotorBikes","Bikes","Bikes","MotorBikes",
"MotorBikes","MotorBikes","MotorBikes","Bikes",
"Bikes","Bikes","Bikes","Bikes","MotorBikes",
"Bikes","Bikes","MotorBikes","MotorBikes",
"MotorBikes","MotorBikes","Bikes","Bikes","Bikes",
"Bikes","Bikes","Bikes","Bikes","Bikes",
"Bikes","MotorBikes","MotorBikes","MotorBikes",
"MotorBikes","Bikes","Bikes","Bikes","Bikes","Bikes",
"Bikes","Bikes","Bikes","MotorBikes",
"MotorBikes","MotorBikes","MotorBikes","Bikes","Bikes",
"Bikes","Bikes","Bikes"),
Category = c("CatC","CatA",
"CatA","CatA","CatA","CatA","CatC","CatC","CatA",
"CatA","CatA","CatB","CatC","CatC","CatA",
"CatA","CatA","CatA","CatA","CatC","CatA",
"CatA","CatA","CatB","CatB","CatC","CatA","CatA",
"CatA","CatA","CatA","CatC","CatA","CatA",
"CatA","CatB","CatC","CatA","CatA","CatA",
"CatA","CatA","CatC","CatC","CatA","CatA","CatA",
"CatB","CatC","CatA","CatA","CatA","CatA",
"CatA","CatC","CatC","CatA","CatA","CatA","CatB",
"CatB","CatA","CatC","CatA","CatA","CatA",
"CatA","CatA","CatC","CatC","CatA","CatA",
"CatA","CatB","CatA","CatC","CatA","CatA","CatA",
"CatA","CatA","CatC","CatA","CatA","CatA",
"CatB","CatA","CatC","CatA","CatA","CatA",
"CatA","CatA","CatC","CatC","CatA","CatA","CatA",
"CatB","CatC","CatA","CatA","CatA","CatA",
"CatA","CatA","CatC","CatC","CatA","CatA","CatA",
"CatB","CatC","CatA","CatA","CatA","CatA",
"CatA","CatC","CatA","CatA","CatA","CatB"),
Type = c("TypeC","TypeA",
"TypeA","TypeA","TypeA","TypeA","TypeC","TypeC",
"TypeA","TypeA","TypeA1","TypeB","TypeC",
"TypeC","TypeA","TypeA","TypeA","TypeA","TypeA",
"TypeC","TypeA","TypeA","TypeA1","TypeB",
"TypeB","TypeC","TypeA","TypeA","TypeA","TypeA",
"TypeA","TypeC","TypeA","TypeA","TypeA1","TypeB",
"TypeC","TypeA","TypeA","TypeA","TypeA",
"TypeA","TypeC","TypeC","TypeA","TypeA","TypeA1",
"TypeB","TypeC","TypeA","TypeA","TypeA",
"TypeA","TypeA","TypeC","TypeC","TypeA","TypeA",
"TypeA1","TypeB","TypeB","TypeA","TypeC","TypeA",
"TypeA","TypeA","TypeA","TypeA","TypeC",
"TypeC","TypeA","TypeA","TypeA1","TypeB","TypeA",
"TypeC","TypeA","TypeA","TypeA","TypeA","TypeA",
"TypeC","TypeA","TypeA","TypeA1","TypeB",
"TypeA","TypeC","TypeA","TypeA","TypeA","TypeA",
"TypeA","TypeC","TypeC","TypeA","TypeA",
"TypeA1","TypeB","TypeC","TypeA","TypeA","TypeA",
"TypeA","TypeA","TypeA","TypeC","TypeC","TypeA",
"TypeA","TypeA1","TypeB","TypeC","TypeA",
"TypeA","TypeA","TypeA","TypeA","TypeC","TypeA",
"TypeA","TypeA1","TypeB"),
Status = c("Active","Active",
"New_1","New_2","New_3","New_4","Active",
"New","Active","Non Active","Active","Active",
"Active","New","Active","New_1","New_2","New_3",
"New_4","Active","Active","Non Active","Active",
"Active","New","Active","Active","New_1",
"New_2","New_3","New_4","Active","Active",
"Non Active","Active","Active","Active","Active",
"New_1","New_2","New_3","New_4","Active","New",
"Active","Non Active","Active","Active","Active",
"Active","New_1","New_2","New_3","New_4",
"Active","New","Active","Non Active","Active",
"Active","New","New_1","Active","Active","New_1",
"New_2","New_3","New_4","Active","New","Active",
"Non Active","Active","Active","New_3",
"Active","Active","New_1","New_2","New_3","New_4",
"Active","Active","Non Active","Active","Active",
"New_3","Active","Active","New_1","New_2",
"New_3","New_4","Active","New","Active",
"Non Active","Active","Active","Active","Active",
"Non Active","New_1","New_2","New_3","New_4",
"Active","New","Active","Non Active","Active",
"Active","Active","Active","New_1","New_2","New_3",
"New_4","Active","Active","Non Active","Active",
"Active"),
Qty = c(4,18,9,13,10,
24,62,3,326.74,44,13,4,3,1,18,9,9,14,9,
63,359.73,23,11,4,1,4,18,22,9,21,1,61,
366.98,9,10,6,6,14,30,22,7,7,59,2,
352.08,13,20,6,5,14,27,30,22,7,59,5,341.08,
7,18,5,1,6,5,14,23,25,28,22,63,2,
331.83,7,18,6,6,5,13,11,24,21,25,64,319.83,
22,19,6,6,5,13,10,11,22,20,63,4,321.83,
24,17,6,5,12,6,3,9,12,20,67,2,329.19,
20,16,7,7,19,34,3,10,11,68,329.19,22,
14,7)
)
# Looking to add all Combinations per month per group type as long as it exists for any given month for that group category
combo <- df %>%
distinct(Country, City, Group, Category, Type, Status) %>%
arrange(Country, City, Group, Category, Type, Status)
``