Loading libraries, read the files

## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr     1.1.3     ✔ readr     2.1.4
## ✔ forcats   1.0.0     ✔ stringr   1.5.0
## ✔ ggplot2   3.4.4     ✔ tibble    3.2.1
## ✔ lubridate 1.9.3     ✔ tidyr     1.3.0
## ✔ purrr     1.0.2     
## ── 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
  1. Q1
bike_orderlines <- readRDS("./bike_orderlines.rds")


bike_rename <- bike_orderlines %>% 
  mutate(model = case_when(
    model == "CAAD Disc Ultegra" ~"CAAD12 Disc Ultegra", 
    TRUE ~ model))

bike_rename <-bike_rename %>% 
  mutate(model = case_when(
    model == "Syapse Carbon Tiagra" ~"Synapse Carbon Tiagra", 
    TRUE ~ model))

bike_rename <- bike_rename %>% 
  mutate(model = case_when(
    model == "Supersix Evo Hi-Mod Utegra" ~"Supersix Evo Hi-Mod Ultegra", 
    TRUE ~ model))

glimpse(bike_rename)
## Rows: 15,644
## Columns: 13
## $ order_date     <dttm> 2011-01-07, 2011-01-07, 2011-01-10, 2011-01-10, 2011-0…
## $ order_id       <dbl> 1, 1, 2, 2, 3, 3, 3, 3, 3, 4, 5, 5, 5, 5, 6, 6, 6, 6, 7…
## $ order_line     <dbl> 1, 2, 1, 2, 1, 2, 3, 4, 5, 1, 1, 2, 3, 4, 1, 2, 3, 4, 1…
## $ quantity       <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1…
## $ price          <dbl> 6070, 5970, 2770, 5970, 10660, 3200, 12790, 5330, 1570,…
## $ total_price    <dbl> 6070, 5970, 2770, 5970, 10660, 3200, 12790, 5330, 1570,…
## $ model          <chr> "Jekyll Carbon 2", "Trigger Carbon 2", "Beast of the Ea…
## $ category_1     <chr> "Mountain", "Mountain", "Mountain", "Mountain", "Road",…
## $ category_2     <chr> "Over Mountain", "Over Mountain", "Trail", "Over Mounta…
## $ frame_material <chr> "Carbon", "Carbon", "Aluminum", "Carbon", "Carbon", "Ca…
## $ bikeshop_name  <chr> "Ithaca Mountain Climbers", "Ithaca Mountain Climbers",…
## $ city           <chr> "Ithaca", "Ithaca", "Kansas City", "Kansas City", "Loui…
## $ state          <chr> "NY", "NY", "KS", "KS", "KY", "KY", "KY", "KY", "KY", "…
  1. Q2 The month of ’Apr’has the highest amounts of Sales with 8M$.
bike_sales_tbl <- bike_rename %>% 
  select(order_date, total_price) %>% 
  mutate(order_date = ymd(order_date)) %>% 
  mutate(Month = month(order_date, label = TRUE)) %>% 
  group_by(Month) %>% 
  summarize(Sales = sum(total_price)) %>%
  mutate(Sales = scales::dollar(Sales))

bike_sales_tbl %>%  View()
  1. Q3
bike_blackInc <- bike_rename %>%
  mutate(BlackInc = str_detect(model, "Black Inc")) %>%
  group_by(BlackInc) %>%
  summarize(MeanOrderline = mean(total_price)) %>%
  mutate(MeanOrderline = scales::dollar(MeanOrderline))

bike_blackInc
## # A tibble: 2 × 2
##   BlackInc MeanOrderline
##   <lgl>    <chr>        
## 1 FALSE    $4,037.61    
## 2 TRUE     $13,154.58
#There is a huge spread between the median of the products that contain the 'Black Inc' attribute, with it having a higher median.

## Median sales by 'Ultegra' attribute
bike_ultegra <- bike_rename %>%
  mutate(Ultegra = str_detect(model, "Ultegra")) %>%
  group_by(Ultegra) %>%
  summarize(MeanOrderline = mean(total_price)) %>%
  mutate(MeanOrderline = scales::dollar(MeanOrderline))

bike_ultegra
## # A tibble: 2 × 2
##   Ultegra MeanOrderline
##   <lgl>   <chr>        
## 1 FALSE   $4,601.17    
## 2 TRUE    $4,175.90
#Items with the 'Ultegra' feature tend to have a slightly lower median that the remaining items.
## Median sales by 'Disc' attribute
bike_disc <- bike_rename %>%
  mutate(Disc = str_detect(model, "Disc")) %>%
  group_by(Disc) %>%
  summarize(MeanOrderline = mean(total_price)) %>%
  mutate(MeanOrderline = scales::dollar(MeanOrderline))

bike_disc
## # A tibble: 2 × 2
##   Disc  MeanOrderline
##   <lgl> <chr>        
## 1 FALSE $4,544.44    
## 2 TRUE  $4,511.86
# Items containing the 'Disc' attribute have almost the same median as those that don't.
  1. Q4
bike_rename %>% select(category_1, category_2, model, total_price) %>% 
  mutate(Model_Base = str_extract(model, "^[a-zA-Z\\s-]+")) %>%
  group_by(category_1, category_2, Model_Base) %>%
  summarize(
    Mean_Price = scales::dollar(mean(total_price)),
    Max_Price = scales::dollar(max(total_price)),
    Min_Price = scales::dollar(min(total_price))
  ) %>% View()
## `summarise()` has grouped output by 'category_1', 'category_2'. You can
## override using the `.groups` argument.
#This data display shows that having a high max price doesn't necessarly translate to a high min, due to the spread of having very low prices in between models.