Icecream

# Load necessary libraries
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(tidyr)
library(plotly)

## 
## Attaching package: 'plotly'

## The following object is masked from 'package:ggplot2':
## 
##     last_plot

## The following object is masked from 'package:stats':
## 
##     filter

## The following object is masked from 'package:graphics':
## 
##     layout

# Load the data
australia <- read.csv("Australia.csv", stringsAsFactors = FALSE)
france <- read.csv("France.csv", stringsAsFactors = FALSE)
options(scipen = 999)

# Clean the column names
colnames(australia) <- make.names(colnames(australia))
colnames(france) <- make.names(colnames(france))

# Clean numerical columns (removing commas and converting to numeric)
australia$X52weeksEnding2022 <- as.numeric(gsub(",", "", australia$X52weeksEnding2022))
australia$X52weeksEnding2023 <- as.numeric(gsub(",", "", australia$X52weeksEnding2023))
australia$X52weeksEnding2024 <- as.numeric(gsub(",", "", australia$X52weeksEnding2024))

france$X52weeksEnding2022 <- as.numeric(gsub(",", "", france$X52weeksEnding2022))
france$X52weeksEnding2023 <- as.numeric(gsub(",", "", france$X52weeksEnding2023))
france$X52weeksEnding2024 <- as.numeric(gsub(",", "", france$X52weeksEnding2024))

# Aggregating data by Format for Australia
aus_summary <- australia %>%
  group_by(FORMAT) %>%
  summarise(
    Sales_2022 = sum(X52weeksEnding2022, na.rm = TRUE),
    Sales_2023 = sum(X52weeksEnding2023, na.rm = TRUE),
    Sales_2024 = sum(X52weeksEnding2024, na.rm = TRUE)
  )

# Aggregating data by Format for France
fr_summary <- france %>%
  group_by(FORMAT) %>%
  summarise(
    Sales_2022 = sum(X52weeksEnding2022, na.rm = TRUE),
    Sales_2023 = sum(X52weeksEnding2023, na.rm = TRUE),
    Sales_2024 = sum(X52weeksEnding2024, na.rm = TRUE)
  )

# Reshape data for visualization
aus_long <- gather(aus_summary, Year, Sales, Sales_2022:Sales_2024)
fr_long <- gather(fr_summary, Year, Sales, Sales_2022:Sales_2024)

# Plotting the trends for Australia
ggplotly(ggplot(aus_long, aes(x = Year, y = Sales, fill = FORMAT)) +
  geom_bar(stat = "identity", position = "dodge") +
  ggtitle("Sales Trends by Format - Australia") +
  theme_minimal())

# Plotting the trends for France
ggplotly(ggplot(fr_long, aes(x = Year, y = Sales, fill = FORMAT)) +
  geom_bar(stat = "identity", position = "dodge") +
  ggtitle("Sales Trends by Format - France") +
  theme_minimal())

# Analyze Retailer-wise sales for Australia in 2024
ggplotly(ggplot(australia, aes(x = FORMAT, y = X52weeksEnding2024, fill = RETAILER)) +
  geom_bar(stat = "identity", position = "dodge") +
  ggtitle("Retailer-wise Sales for Brand H - Australia 2024") +
  theme_minimal())

# Analyze Retailer-wise sales for France in 2024
ggplotly(ggplot(france, aes(x = FORMAT, y = X52weeksEnding2024, fill = RETAILER)) +
  geom_bar(stat = "identity", position = "dodge") +
  ggtitle("Retailer-wise Sales for Brand H - France 2024") +
  theme_minimal())

# Recommendations:
# - Australia: Focus on growing Cups and Tubs, as these formats show growth potential.
# - France: Investigate the decline in certain formats, particularly in the Cones segment.
# - Distribution: Expand into underperforming retailers where market share can be improved.

Recommendations:

- Australia: Focus on growing Cups and Tubs, as these formats show growth potential.

- France: Investigate the decline in certain formats, particularly in the Cones segment.

- Distribution: Expand into underperforming retailers where market share can be improved.