recommerce_calculation

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.2          ✔ readr     2.1.4     
✔ forcats   1.0.0          ✔ stringr   1.5.0     
✔ ggplot2   3.4.4          ✔ tibble    3.2.1     
✔ lubridate 1.9.2          ✔ tidyr     1.3.0     
✔ purrr     1.0.1.9000     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

DF_Rechnungen_raw <- read.csv("Rechnungen.csv")
DF_Categerory_brands <- read.csv("Category-brands.csv")

DF_Rechnungen <- DF_Rechnungen_raw %>%
  mutate(Datum = as.Date(Datum)) %>% 
  select(Datum, PG, Produktname)

DF_Rechnungen_Grouped <- DF_Rechnungen%>% 
  mutate(Year = year(Datum), 
         Month = month(Datum)) %>%
  filter(Year >= 2018) %>% 
  filter(PG !="") %>%
  filter(!is.na(PG)) %>% 
  group_by(Year, PG) %>%
  summarise(Count = n(), .groups = 'drop')

DF_Rechnungen_cumsum <- DF_Rechnungen_Grouped %>%
  arrange(PG, Year) %>%
  group_by(PG) %>%
  mutate(CumulativeSum = cumsum(Count)) %>%
  ungroup()
  
DF_Rechnungen_unique <- DF_Rechnungen %>%
  group_by(PG) %>%
  arrange(desc(Datum), .by_group = TRUE) %>%
  slice(1) %>%
  ungroup()

DF_FINAL <- DF_Rechnungen_cumsum %>% 
 left_join(DF_Rechnungen_unique %>% select(PG, Produktname), by = "PG") %>%
  mutate(
    date = paste0(Year, "-01-01"),
    name = paste0("Apple ",Produktname) ,
    category = PG,
    value = CumulativeSum
  ) %>%
  select(
    date, name, category, value 
  )

DF_FINAL <- DF_FINAL %>% 
  filter(value > 100) %>% 
  filter(!category %in% c(1412,1793,1006,1071, 70,150, 278, 468, 1284,2135, 2566, 65, 219,1770,889, 1816, 1694))

DF_FINAL <- DF_FINAL %>% 
  mutate(category = case_when(
    grepl("imac", name, ignore.case = TRUE) ~ 2,
    grepl("ipad", name, ignore.case = TRUE) ~ 3,
    grepl("iphone", name, ignore.case = TRUE) ~ 4,
    grepl("macbook", name, ignore.case = TRUE) ~ 5,
    TRUE ~ 1 # This line is optional, for cases that don't match any condition
  ))

write.csv(DF_FINAL, "recommerce.csv", row.names = FALSE)