Interactive Bike Sales Analysis

Introduction

This document explores the Bike Sales dataset using ggplot2 for data visualization. The plots are made interactive using the plotly package.

---

Load the Data

# Load required libraries 
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(ggplot2)
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

getwd()

[1] "D:/ERU/2024- 2025/Spring 2024-2025/Quarto"

Read the Excel file (update the file path if necessary)

bike_sales <- read_excel("D:\\ERU\\2024- 2025\\Fall 2024-2025\\Statistical Computing Fall 2024\\Bike Sales.xlsx")

Display the first few rows

head(bike_sales)

# A tibble: 6 × 11
  Date                Customer_Age Customer_Gender Branch Product_Description   
  <dttm>                     <dbl> <chr>           <chr>  <chr>                 
1 2021-12-01 00:00:00           39 F               Cairo  Mountain-200 Black, 46
2 2021-12-01 00:00:00           44 M               Giza   Mountain-200 Silver, …
3 2021-12-02 00:00:00           37 M               Fayoum Mountain-400-W Silver…
4 2021-12-02 00:00:00           31 F               Aswan  Mountain-400-W Silver…
5 2021-12-03 00:00:00           37 F               Fayoum Mountain-200 Black, 46
6 2021-12-03 00:00:00           24 F               Giza   Mountain-200 Black, 38
# ℹ 6 more variables: Order_Quantity <dbl>, Unit_Cost <dbl>, Unit_Price <dbl>,
#   Cost <dbl>, Revenue <dbl>, Profit <dbl>

Basic Summary of the Data

# Summary statistics
summary(bike_sales)

      Date                         Customer_Age  Customer_Gender   
 Min.   :2021-12-01 00:00:00.00   Min.   :17.0   Length:89         
 1st Qu.:2021-12-08 00:00:00.00   1st Qu.:30.0   Class :character  
 Median :2021-12-13 00:00:00.00   Median :35.0   Mode  :character  
 Mean   :2021-12-13 10:31:00.67   Mean   :34.2                     
 3rd Qu.:2021-12-19 00:00:00.00   3rd Qu.:38.0                     
 Max.   :2021-12-24 00:00:00.00   Max.   :63.0                     
                                                                   
    Branch          Product_Description Order_Quantity    Unit_Cost   
 Length:89          Length:89           Min.   :1.000   Min.   :   0  
 Class :character   Class :character    1st Qu.:1.000   1st Qu.: 420  
 Mode  :character   Mode  :character    Median :2.000   Median :1252  
                                        Mean   :2.125   Mean   :1052  
                                        3rd Qu.:3.000   3rd Qu.:1266  
                                        Max.   :4.000   Max.   :1912  
                                        NA's   :1                     
   Unit_Price        Cost         Revenue          Profit    
 Min.   : 540   Min.   :   0   Min.   :    0   Min.   :   0  
 1st Qu.:2295   1st Qu.:1252   1st Qu.: 2295   1st Qu.:1043  
 Median :2295   Median :1266   Median : 2320   Median :1054  
 Mean   :1950   Mean   :2204   Mean   : 4079   Mean   :1874  
 3rd Qu.:2320   3rd Qu.:3756   3rd Qu.: 6750   3rd Qu.:3129  
 Max.   :3400   Max.   :7592   Max.   :13500   Max.   :5908  
                                                             

# Check the structure of the data
str(bike_sales)

tibble [89 × 11] (S3: tbl_df/tbl/data.frame)
 $ Date               : POSIXct[1:89], format: "2021-12-01" "2021-12-01" ...
 $ Customer_Age       : num [1:89] 39 44 37 31 37 24 37 37 31 39 ...
 $ Customer_Gender    : chr [1:89] "F" "M" "M" "F" ...
 $ Branch             : chr [1:89] "Cairo" "Giza" "Fayoum" "Aswan" ...
 $ Product_Description: chr [1:89] "Mountain-200 Black, 46" "Mountain-200 Silver, 42" "Mountain-400-W Silver, 46" "Mountain-400-W Silver, 42" ...
 $ Order_Quantity     : num [1:89] 4 1 2 1 2 1 1 1 4 4 ...
 $ Unit_Cost          : num [1:89] 1252 1266 420 420 0 ...
 $ Unit_Price         : num [1:89] 2295 2320 769 769 2295 ...
 $ Cost               : num [1:89] 5008 1266 840 420 0 ...
 $ Revenue            : num [1:89] 9180 2320 1538 769 4590 ...
 $ Profit             : num [1:89] 4172 1054 698 349 4590 ...

Total Cost by Product

cost_by_product <- bike_sales %>%
  group_by(Product_Description) %>%
  summarise(Total_Cost = sum(Cost, na.rm = TRUE))

p1 <- ggplot(cost_by_product, aes(x = reorder(Product_Description, Total_Cost), y = Total_Cost, fill = Product_Description)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Total Cost by Product", x = "Product", y = "Total Cost") +
  theme_minimal()

ggplotly(p1)

Total Cost by Branch

cost_by_branch <- bike_sales %>%
  group_by(Branch) %>%
  summarise(Total_Cost = sum(Cost, na.rm = TRUE))

p2 <- ggplot(cost_by_branch, aes(x = Branch, y = Total_Cost, fill = Branch)) +
  geom_bar(stat = "identity") +
  labs(title = "Total Cost by Branch", x = "Branch", y = "Total Cost") +
  theme_minimal()

ggplotly(p2)

Total Cost by Customer Gender

cost_by_gender <- bike_sales %>%
  group_by(Customer_Gender) %>%
  summarise(Total_Cost = sum(Cost, na.rm = TRUE))

p3 <- ggplot(cost_by_gender, aes(x = Customer_Gender, y = Total_Cost, fill = Customer_Gender)) +
  geom_bar(stat = "identity") +
  labs(title = "Total Cost by Customer Gender", x = "Gender", y = "Total Cost") +
  theme_minimal()

ggplotly(p3)

Total Cost by Product by Branch

cost_by_product_branch <- bike_sales %>%
  group_by(Product_Description, Branch) %>%
  summarise(Total_Cost = sum(Cost, na.rm = TRUE))

`summarise()` has grouped output by 'Product_Description'. You can override
using the `.groups` argument.

p4 <- ggplot(cost_by_product_branch, aes(x = Branch, y = Total_Cost, fill = Product_Description)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Total Cost by Product by Branch", x = "Branch", y = "Total Cost") +
  theme_minimal()

ggplotly(p4)

Total Cost by Product by Customer Gender

cost_by_product_gender <- bike_sales %>%
  group_by(Product_Description, Customer_Gender) %>%
  summarise(Total_Cost = sum(Cost, na.rm = TRUE))

`summarise()` has grouped output by 'Product_Description'. You can override
using the `.groups` argument.

p5 <- ggplot(cost_by_product_gender, aes(x = Customer_Gender, y = Total_Cost, fill = Product_Description)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Total Cost by Product by Customer Gender", x = "Gender", y = "Total Cost") +
  theme_minimal()

ggplotly(p5)

Total Revenue by Product

revenue_by_product <- bike_sales %>%
  group_by(Product_Description) %>%
  summarise(Total_Revenue = sum(Revenue, na.rm = TRUE))

p6 <- ggplot(revenue_by_product, aes(x = reorder(Product_Description, Total_Revenue), y = Total_Revenue, fill = Product_Description)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Total Revenue by Product", x = "Product", y = "Total Revenue") +
  theme_minimal()

ggplotly(p6)

Total Revenue by Branch

revenue_by_branch <- bike_sales %>%
  group_by(Branch) %>%
  summarise(Total_Revenue = sum(Revenue, na.rm = TRUE))

p7 <- ggplot(revenue_by_branch, aes(x = Branch, y = Total_Revenue, fill = Branch)) +
  geom_bar(stat = "identity") +
  labs(title = "Total Revenue by Branch", x = "Branch", y = "Total Revenue") +
  theme_minimal()

ggplotly(p7)

Total Revenue by Product by Branch

revenue_by_product_branch <- bike_sales %>%
  group_by(Product_Description, Branch) %>%
  summarise(Total_Revenue = sum(Revenue, na.rm = TRUE))

`summarise()` has grouped output by 'Product_Description'. You can override
using the `.groups` argument.

p8 <- ggplot(revenue_by_product_branch, aes(x = Branch, y = Total_Revenue, fill = Product_Description)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Total Revenue by Product by Branch", x = "Branch", y = "Total Revenue") +
  theme_minimal()

ggplotly(p8)

Total Revenue by Product by Customer Gender

revenue_by_product_gender <- bike_sales %>%
  group_by(Product_Description, Customer_Gender) %>%
  summarise(Total_Revenue = sum(Revenue, na.rm = TRUE))

`summarise()` has grouped output by 'Product_Description'. You can override
using the `.groups` argument.

p9 <- ggplot(revenue_by_product_gender, aes(x = Customer_Gender, y = Total_Revenue, fill = Product_Description)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Total Revenue by Product by Customer Gender", x = "Gender", y = "Total Revenue") +
  theme_minimal()

ggplotly(p9)

Total Profit by Branch

profit_by_branch <- bike_sales %>%
  group_by(Branch) %>%
  summarise(Total_Profit = sum(Profit, na.rm = TRUE))

p11 <- ggplot(profit_by_branch, aes(x = Branch, y = Total_Profit, fill = Branch)) +
  geom_bar(stat = "identity") +
  labs(title = "Total Profit by Branch", x = "Branch", y = "Total Profit") +
  theme_minimal()

ggplotly(p11)

Total Profit by Customer Gender

profit_by_gender <- bike_sales %>%
  group_by(Customer_Gender) %>%
  summarise(Total_Profit = sum(Profit, na.rm = TRUE))

p12 <- ggplot(profit_by_gender, aes(x = Customer_Gender, y = Total_Profit, fill = Customer_Gender)) +
  geom_bar(stat = "identity") +
  labs(title = "Total Profit by Customer Gender", x = "Gender", y = "Total Profit") +
  theme_minimal()

ggplotly(p12)

Total Profit by Branch by Customer Gender

# Aggregate Total Profit by Branch and Customer Gender
profit_by_branch_gender <- bike_sales %>%
  group_by(Branch, Customer_Gender) %>%
  summarise(Total_Profit = sum(Profit, na.rm = TRUE))

`summarise()` has grouped output by 'Branch'. You can override using the
`.groups` argument.

# Create a grouped bar chart
p15 <- ggplot(profit_by_branch_gender, aes(x = Branch, y = Total_Profit, fill = Customer_Gender)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Total Profit by Branch by Customer Gender", 
       x = "Branch", 
       y = "Total Profit") +
  theme_minimal()

# Convert to an interactive plot
ggplotly(p15)

colnames(bike_sales)

 [1] "Date"                "Customer_Age"        "Customer_Gender"    
 [4] "Branch"              "Product_Description" "Order_Quantity"     
 [7] "Unit_Cost"           "Unit_Price"          "Cost"               
[10] "Revenue"             "Profit"             

# Ensure the Date column is in Date format
bike_sales$Date <- as.Date(bike_sales$Date)

# Aggregate revenue by date
revenue_trend <- bike_sales %>%
  group_by(Date) %>%
  summarise(Total_Revenue = sum(Revenue, na.rm = TRUE))

# Create a line chart
p2 <- ggplot(revenue_trend, aes(x = Date, y = Total_Revenue)) +
  geom_line(color = "blue", linewidth = 1) +  
  labs(title = "Revenue Trend Over Time", x = "Date", y = "Total Revenue") +
  theme_minimal()

# Convert to an interactive plot
ggplotly(p2)

Top Products by Revenue

# Aggregate total revenue by product description
revenue_by_product <- bike_sales %>%
  group_by(Product_Description) %>%
  summarise(Total_Revenue = sum(Revenue, na.rm = TRUE)) %>%
  arrange(desc(Total_Revenue)) %>%
  slice(1:10)  # Select the top 10 products

# Create a bar chart
p3 <- ggplot(revenue_by_product, aes(x = reorder(Product_Description, Total_Revenue), y = Total_Revenue, fill = Product_Description)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  labs(title = "Top 10 Products by Revenue", x = "Product Description", y = "Total Revenue") +
  theme_minimal()

# Convert to an interactive plot
ggplotly(p3)

##Scatter Plot: Unit Price vs. Revenue

# Create a scatter plot
p4 <- ggplot(bike_sales, aes(x = Unit_Price, y = Revenue, color = Branch)) +
  geom_point(alpha = 0.7) +
  labs(title = "Relationship Between Unit Price and Revenue", x = "Unit Price", y = "Revenue") +
  theme_minimal()

# Convert to an interactive plot
ggplotly(p4)

##Scatter Plot: Unit Price vs. Unit Cost

# Create a scatter plot
p4 <- ggplot(bike_sales, aes(x = Unit_Price, y = Unit_Cost, color = Branch)) +
  geom_point(alpha = 0.7) +
  labs(title = "Relationship Between Unit Price and Unit Cost", x = "Unit Price", y = "Unit_Cost") +
  theme_minimal()

# Convert to an interactive plot
ggplotly(p4)

##Scatter Plot: Cost vs. Revenue

# Create a scatter plot
p4 <- ggplot(bike_sales, aes(x = Cost, y = Revenue, color = Branch)) +
  geom_point(alpha = 0.7) +
  labs(title = "Relationship Between Cost and Revenue", x = "Cost",
       y = "Revenue") +
  theme_minimal()

# Convert to an interactive plot
ggplotly(p4)