dplyr - How can you efficiently identify sales patterns using R?

Question

Closed. This question needs to be more focused. It is not currently accepting answers. question from:https://stackoverflow.com/questions/65910373/how-can-you-efficiently-identify-sales-patterns-using-r

Answer 1

Though your expected output is not very clear, I think you want something like this

library(tidyverse)
library(zoo)

df %>% pivot_longer(cols = -c(1:3), names_to = "Month", values_to = "Sales") %>%
  mutate(Month = as.Date(paste0("01_", Month), "%d_%b_%y")) %>%
  group_by(Customer, Customer.ID, Month) %>%
  arrange(Customer, Month, Product) %>%
  mutate(Main = case_when(Sales == 0 ~ "nil",
                          Sales == max(Sales) ~ "main", 
                          TRUE ~ "secondary")) %>%
  group_by(Customer, Customer.ID, Product) %>%
  mutate(cumSales = cumsum(Sales)) %>%
  ungroup() %>% 
  filter(Main == "main") %>%
  group_by(Customer) %>%
  mutate(shift = ifelse(Product == lag(Product, default = first(Product)), 0, 1),
         shifted_from = ifelse(shift == 1, lag(Product), NA),
         prev_prod_sales = ifelse(shift ==1, lag(cumSales), NA),
         shift_month = as.yearmon(Month, "%b %Y")) %>%
  filter(shift == 1) %>%
  select(Customer, Customer.ID, shifted_from, shifted_to = Product, shift_month, prev_prod_sales)

# A tibble: 20 x 6
# Groups:   Customer [7]
   Customer   Customer.ID shifted_from shifted_to shift_month prev_prod_sales
   <chr>      <chr>       <chr>        <chr>      <yearmon>             <int>
 1 Customer A 1A          Product Z    Product Y  Feb 2019                186
 2 Customer A 1A          Product Y    Product X  May 2020                207
 3 Customer B 2B          Product Y    Product X  Dec 2019                327
 4 Customer D 4D          Product X    Product U  Feb 2019                186
 5 Customer D 4D          Product U    Product Y  Mar 2019                 24
 6 Customer D 4D          Product Y    Product U  Jul 2019                 24
 7 Customer D 4D          Product U    Product Y  Jan 2020                137
 8 Customer D 4D          Product Y    Product U  Mar 2020                 36
 9 Customer D 4D          Product U    Product M  May 2020                207
10 Customer E 5E          Product Z    Product X  Aug 2018                 55
11 Customer E 5E          Product X    Product Z  Dec 2018                 12
12 Customer G 7G          Product Z    Product Y  Feb 2019                186
13 Customer G 7G          Product Y    Product Z  Feb 2020                149
14 Customer G 7G          Product Z    Product Y  Mar 2020                192
15 Customer G 7G          Product Y    Product X  May 2020                219
16 Customer I 9I          Product Y    Product X  Feb 2019                 17
17 Customer K 10K         Product U    Product M  Feb 2019                186
18 Customer K 10K         Product M    Product U  Feb 2020                149
19 Customer K 10K         Product U    Product M  Mar 2020                192
20 Customer K 10K         Product M    Product X  May 2020                219