Bike Models and Sales II

Cleaning, Seeking, and Plotting Trends Using R

---

Back to Projects

1 Introduction

Summary: This report visualizes bike sales trends using R and ggplot. It examines revenue across models, categories, and dates, using bins, time data, and text to highlight top performers and seasonal patterns.

Navigation: Use the Table of Contents to navigate sections and click the 🞂 Code buttons throughout the document to view or hide R code for each example.

2 Datasets

Below I show the files used for this analysis.

Code

# Define paths
bikes_path      <- file.path(params$data_dir, "bikes.xlsx")
bikeshops_path  <- file.path(params$data_dir, "bikeshops.xlsx")
orderlines_path <- file.path(params$data_dir, "orderlines.xlsx")
bikeshop_revenue_wide_path <- file.path("00_support", "data", "bike_sales", "data_wrangled", "bikeshop_revenue_wide_tbl.xlsx")
bike_orderlines_wrangled_path <- file.path("00_support", "data", "bike_sales", "data_wrangled", "bike_orderlines_wrangled.xlsx")

# Verify all files exist
stopifnot(
  file.exists(bikes_path),
  file.exists(bikeshops_path),
  file.exists(orderlines_path),
  file.exists(bikeshop_revenue_wide_path),
  file.exists(bike_orderlines_wrangled_path)
)

# Load data
bike_tbl                  <- readxl::read_excel(bikes_path)
bikeshops_tbl             <- readxl::read_excel(bikeshops_path)
orderlines_tbl            <- readxl::read_excel(orderlines_path)
bikeshop_revenue_wide_tbl <- readxl::read_excel(bikeshop_revenue_wide_path)
bike_orderlines_wrangled_tbl <- readxl::read_excel(bike_orderlines_wrangled_path)

2.1 Overview of Data

2.1.1 Bike Orderlines Dataset

Code

bike_orderlines_wrangled_tbl

2.1.2 Count on All Models

Code

bike_orderlines_wrangled_tbl %>%
   count(model, sort = TRUE) %>%
   rename("Model" = model,
         "Count"  = n)

3 Flags and Bins

3.1 Creating Supersix Models Flags

Code

bike_orderlines_wrangled_tbl %>% 
    select(3,7,6) %>%
    mutate(is_supersix = model %>% str_to_lower() %>% str_detect("supersix")) %>%
    rename("Orderline" = order_line,
           "Model"      = model,
           "Total Sale" = total_sale)

3.1.1 Filtering All Supersix Models

Code

orderlines_test_tbl <- bike_orderlines_wrangled_tbl %>% 
    select(7,4:6,8) %>%
    mutate(is_supersix = model %>% str_to_lower() %>% str_detect("supersix")) %>%
    filter(is_supersix)

orderlines_test_tbl %>% 
    rename("Model"    = model,
           "Quantity" = quantity,
           "Price"    = price,
           "Total Sale" = total_sale, 
           "Category 1" = category_1)

3.1.2 Count on All Supersix Models

Code

orderlines_test_tbl %>%
   count(model, sort = TRUE) %>%
   rename("Model" = model,
         "Count"  = n)

3.2 Creating Using Ntile Bins

Code

orderlines_test_tbl %>% 
    select(1:5) %>%
    mutate(price_bin = ntile(price, 3)) %>%
   rename("Model"     = model,
         "Quantity"   = quantity,
         "Price"      = price,
         "Total Sale" = total_sale,
         "Category 1" = category_1,
         "Price Bin"  = price_bin)

3.3 Creating High Medium Low Bins

Code

orderlines_test_tbl %>% 
    select(1:5) %>%
    mutate(bin1 = ntile(price, 3)) %>%
    mutate(bin2 = case_when(
            # ~ Means Flagging
        total_sale > quantile(total_sale, 0.66) ~ "High", # Setting quantile and name
        total_sale > quantile(total_sale, 0.33) ~ "Medium",
        TRUE ~ "Low"
    )) %>% 
   rename("Model"       = model,
         "Quantity" = quantity,
         "Price"      = price,
         "Total Sale" = total_sale,
         "Category 1" = category_1,
         "Bin 1" = bin1, 
         "Bin 2" = bin2) 

3.4 Creating Bike Type Flags

Code

orderlines_test_tbl %>% 
    mutate(bike_type = case_when(
        model %>% str_to_lower() %>% str_detect("hi-mod") ~ "Hi-Mode", # Setting name
        model %>% str_to_lower() %>% str_detect("tiagra") ~ "Tiagra",
        TRUE ~ "Other")) %>% 
    mutate(price = price %>% dollar()) %>%
    rename("Model"      = model,
           "Category 1" = category_1,
           "Bike Type"  = bike_type,
           "Price"      = price) %>%
    select(1,5,7,3)

4 Flags and Bins