Data Analytics

# ----------------------------
# 1. Load libraries
# ----------------------------
library(ggplot2)
library(readxl)
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(lubridate)   # for easy date handling

## 
## Attaching package: 'lubridate'

## The following objects are masked from 'package:base':
## 
##     date, intersect, setdiff, union

library(tidyr)       # for complete() if needed

# ----------------------------
# 2. Read your Excel file
# ----------------------------
file_path <- "estelle sales.xlsx"   # <-- adjust path if needed
sales_data <- read_xlsx(file_path)

# Check column names
print(names(sales_data))

## [1] "date"   "amount"

# ----------------------------
# 3. Rename columns to standard names
#    (change these to match your actual column names)
# ----------------------------
# Assuming your columns are "date" and "amount" (as in your code)
# If they are different, adjust the right-hand side.
colnames(sales_data)[colnames(sales_data) == "date"] <- "Date"
colnames(sales_data)[colnames(sales_data) == "amount"] <- "Sales"

# If your actual names are "Date" and "Sales_Amount", 
# you may already have them; adjust accordingly.

# ----------------------------
# 4. Convert Date column (format: m/d/YYYY)
# ----------------------------
sales_data$Date <- as.Date(sales_data$Date, format = "%m/%d/%Y")

# Remove rows with missing dates or sales
sales_data <- na.omit(sales_data)

# ----------------------------
# 5. Add a weekday column
# ----------------------------
sales_data$Weekday <- wday(sales_data$Date, label = TRUE, abbr = FALSE)
# label = TRUE gives names like "Monday", abbr = FALSE gives full names.
# You can also use weekdays(sales_data$Date) but wday() orders weekdays properly.

# ----------------------------
# 6. Calculate average sales per weekday
# ----------------------------
weekday_avg <- sales_data %>%
  group_by(Weekday) %>%
  summarise(Avg_Sales = mean(Sales, na.rm = TRUE),
            Count = n()) %>%
  ungroup()

print(weekday_avg)

## # A tibble: 6 × 3
##   Weekday   Avg_Sales Count
##   <ord>         <dbl> <int>
## 1 Monday        3250     13
## 2 Tuesday       2708.    13
## 3 Wednesday     2736.    14
## 4 Thursday      3264.    14
## 5 Friday        3643.    15
## 6 Saturday      4067.    15

# ----------------------------
# 7. Visualisation 1: Bar chart of average sales by weekday
# ----------------------------
ggplot(weekday_avg, aes(x = Weekday, y = Avg_Sales, fill = Weekday)) +
  geom_col() +
  labs(title = "Average Sales by Day of the Week",
       x = "Day of Week", y = "Average Sales Amount") +
  theme_minimal() +
  theme(legend.position = "none")   # remove legend (colours are labels)

# ----------------------------
# 8. Visualisation 2: Boxplot – shows spread of sales per weekday
# ----------------------------
ggplot(sales_data, aes(x = Weekday, y = Sales, fill = Weekday)) +
  geom_boxplot() +
  labs(title = "Distribution of Sales by Day of the Week",
       x = "Day of Week", y = "Sales Amount") +
  theme_minimal() +
  theme(legend.position = "none")

# 9. (Optional) Line plot over time with weekday colours
#    This helps see if the weekly pattern is consistent over months.
# ----------------------------
ggplot(sales_data, aes(x = Date, y = Sales, color = Weekday)) +
  geom_line() +
  geom_point(size = 1) +
  labs(title = "Daily Sales Coloured by Weekday",
       x = "Date", y = "Sales") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))