library(ggplot2)
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(readxl)
library(stringr)
library(openxlsx)
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ forcats   1.0.0     ✔ readr     2.1.5
## ✔ lubridate 1.9.3     ✔ tibble    3.2.1
## ✔ purrr     1.0.2     ✔ tidyr     1.3.1
## ── 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
library(treemapify)
## Warning: package 'treemapify' was built under R version 4.3.3
VentasUS = read.xlsx("Ev2_VentasUS.xlsx")
VentasMex = read.xlsx("Ev2_VentasMex.xlsx")
OrdMonth = c("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
OrdMonthEs = c("Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre")
VentasMexL = VentasMex
VentasUSL = VentasUS
apply(is.na(VentasMex), 2, sum)>=1
##     Month    Region  CrujiCos  PicanCos SaladoCos    BBQCos  SalsaCos   QuesoCo 
##     FALSE     FALSE      TRUE     FALSE      TRUE      TRUE     FALSE      TRUE
Media_CC= mean(VentasMex$CrujiCos,na.rm=T)
VentasMexL$CrujiCos[is.na(VentasMex$CrujiCos)] = Media_CC

Media_SC= mean(VentasMex$SaladoCos,na.rm=T)
VentasMexL$SaladoCos[is.na(VentasMex$SaladoCos)] = Media_SC

Media_BC= mean(VentasMex$BBQCos,na.rm=T)
VentasMexL$BBQCos[is.na(VentasMex$BBQCos)] = Media_BC

Media_QC= mean(VentasMex$QuesoCo,na.rm=T)
VentasMexL$QuesoCo[is.na(VentasMex$QuesoCo)] = Media_QC

Media_ChC= mean(VentasUS$CheeseCo,na.rm=T)
VentasUSL$CheeseCo[is.na(VentasUS$CheeseCo)] = Media_ChC
VentasMexTotales = VentasMexL

VentasMexTotales$MesesNum = match(VentasMexL$Month, OrdMonthEs)

VentasMexTotales$VentasTotal = VentasMexL$CrujiCos + VentasMexL$PicanCos + VentasMexL$SaladoCos + VentasMexL$BBQCos + VentasMexL$SalsaCos + VentasMexL$QuesoCo

ventas_por_mesMex = VentasMexTotales %>%
  group_by(MesesNum) %>%
  summarise(Ventas = sum(VentasTotal))

VentasUSLTotales= VentasUSL

VentasUSLTotales$MesesNum = match(VentasUSL$Month,OrdMonth)

  
VentasUSLTotales$VentasTotal = VentasUSL$CrunchyCo + VentasUSL$SpicyCo + VentasUSL$SaltCo + VentasUSL$BBQCo + VentasUSL$SalsaCo +VentasUSL$CheeseCo

ventas_por_mesUS = VentasUSLTotales %>%
  group_by(MesesNum) %>%
  summarise(Ventas = sum(VentasTotal))

ventas_por_mesUS$Variable = "US"
ventas_por_mesMex$Variable = "MEX"

datos_combinados = rbind(ventas_por_mesUS, ventas_por_mesMex)

0.1 The product that sells the most in Mexico and the United States per region

ventas_RegionUS = VentasUSLTotales %>%
  group_by(Region) %>%
  reframe(Venta = c(sum(CrunchyCo), sum(SaltCo), sum(BBQCo), sum(SalsaCo), sum(SpicyCo), sum(CheeseCo)))
num_filas = (3*6)
Marca = c("CrunchyCo", "SaltCo", "BBQCo","SalsaCo", "SpicyCo", "CheeseCo")
ventas_RegionUS$Sabores =  rep(Marca, length.out = num_filas)

ventas_RegionMex= VentasMexTotales %>%
  group_by(Region) %>%
  reframe(Venta =c(sum(CrujiCos),sum(SaladoCos), sum(BBQCos), sum(SalsaCos), sum(PicanCos), sum(QuesoCo)))
Marca2 = c("CrujiCos", "SaladoCos", "BBQCos","SalsaCos", "PicanCos", "QuesoCo")
ventas_RegionMex$Sabores =  rep(Marca2, length.out = num_filas)
 ggplot(ventas_RegionMex, aes(y = factor(Sabores), x = Venta, fill = Sabores))+
  geom_bar(stat = "identity") +
  facet_wrap(~Region) +
  labs(
    title = "the product that sells the most in Mexico per region",
    y = "Product",
    x = "Sales")

#PicanCos is the Product that sells the most in Mexico

 ggplot(ventas_RegionUS, aes(y = factor(Sabores), x = Venta, fill = Sabores))+
  geom_bar(stat = "identity") +
  facet_wrap(~Region) +
  labs(
    title = "the product that sells the most in US per region",
    y = "Product",
    x = "Sales")

#SpicyCo is the Product that sells the most in United States

ventas_producto_mesUS = VentasUSLTotales %>%
  group_by(MesesNum) %>%
  reframe(Venta = c(sum(CrunchyCo), sum(SaltCo), sum(BBQCo), sum(SalsaCo), sum(SpicyCo), sum(CheeseCo)))
num_filas = (12*6)
Marca = c("CrunchyCo", "SaltCo", "BBQCo","SalsaCo", "SpicyCo", "CheeseCo")
ventas_producto_mesUS$Sabores =  rep(Marca, length.out = num_filas)

ventas_producto_mesMEX = VentasMexTotales %>%
  group_by(MesesNum) %>%
  reframe(Venta =c(sum(CrujiCos),sum(SaladoCos), sum(BBQCos), sum(SalsaCos), sum(PicanCos), sum(QuesoCo)))
Marca2 = c("CrujiCos", "SaladoCos", "BBQCos","SalsaCos", "PicanCos", "QuesoCo")
ventas_producto_mesMEX$Sabores =  rep(Marca2, length.out = num_filas)

0.2 The month with more sells for each product

 ggplot(ventas_producto_mesUS, aes(x = factor(MesesNum), y = Venta, fill = Sabores)) +
  geom_bar(stat = "identity") +
  facet_wrap(~Sabores) +
  labs(
    title = "Mensual Sales by Flavor in US",
    x = "Month",
    y = "Sales"
  )

# December is usually the month with the most sales in the United States, however, SaltCo sold more in its first quarter

 ggplot(ventas_producto_mesMEX, aes(x = factor(MesesNum), y = Venta, fill = Sabores)) +
  geom_bar(stat = "identity") +
  facet_wrap(~Sabores) +
  labs(
    title = "Mensual Sales by Flavor in MEX",
    x = "Month",
    y = "Sales"
  )

# The monthly trends of products in Mexico vary, but the number of mid-year sales of PicanCos stands out

0.2.1 The sales of the products of the last three months

ThreeMonthsSalesUS = filter(ventas_producto_mesUS, MesesNum > 9)
ThreeMonthsSalesMex = filter(ventas_producto_mesMEX, MesesNum > 9)
 ggplot(ThreeMonthsSalesUS, aes(x = factor(MesesNum), y = Venta, fill = Sabores)) +
  geom_bar(stat = "identity") +
  facet_wrap(~Sabores) +
  labs(
    title = "Mensual Sales by Flavor in US",
    x = "Month",
    y = "Sales"
  )

# In the United States, sales usually grow from October to December

 ggplot(ThreeMonthsSalesMex, aes(x = factor(MesesNum), y = Venta, fill = Sabores)) +
  geom_bar(stat = "identity") +
  facet_wrap(~Sabores) +
  labs(
    title = "Mensual Sales by Flavor in MEX",
    x = "Month",
    y = "Sales"
  )

$ In Mexico, sales usually grow from October to December

0.3 The behavour of the sales of CheeseCo and QuesoCo

ventas_por_mesChe = VentasUSLTotales %>%
  group_by(MesesNum) %>%
  summarise(Ventas = sum(CheeseCo))

ventas_por_mesQue = VentasMexTotales %>%
  group_by(MesesNum) %>%
  summarise(Ventas = sum(QuesoCo))

ventas_por_mesChe$Variable = "CheeseCo"
ventas_por_mesQue$Variable = "QuesoCo"

datos_combinados = rbind(ventas_por_mesChe, ventas_por_mesQue)
ggplot2::ggplot(data = datos_combinados, mapping = aes(x= MesesNum,y = Ventas, color = Variable))+
geom_line() +  theme_classic() + scale_x_continuous(limits = c(1, 12), breaks = seq(1, 12, by = 1)) +
  labs(
    title = "Mensual CheeseCo/QuesoCo Sales in US and MEX",
    x = "Month",
    y = "Sales")

# Sales of CheeseCo and QuesoCo seem to behave in a similar way in Mexico and the United States, but since the American market is a larger market, the number of sales of this product is worrying