HW 3

setwd("C:/Users/jerem/Documents/Financial Database")

# Load the data from the .rds file
bike_orderline_tbl <- readRDS("bike_orderlines.rds")

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(scales)  # for formatting numbers as dollars

# Rename columns
bike_orderline_tbl <- bike_orderline_tbl %>%
  rename('Prime category' = category_1,
         'Secondary category' = category_2,
         'Frame Material' = frame_material)

# Group and summarize data
summary_df <- bike_orderline_tbl %>%
  group_by(`Prime category`, `Secondary category`, `Frame Material`) %>%
  summarize(Sales = sum(total_price))

## `summarise()` has grouped output by 'Prime category', 'Secondary category'. You
## can override using the `.groups` argument.

# Format Sales as dollars
summary_df$Sales <- dollar(summary_df$Sales)
print(summary_df)

## # A tibble: 13 × 4
## # Groups:   Prime category, Secondary category [9]
##    `Prime category` `Secondary category` `Frame Material` Sales      
##    <chr>            <chr>                <chr>            <chr>      
##  1 Mountain         Cross Country Race   Aluminum         $3,318,560 
##  2 Mountain         Cross Country Race   Carbon           $15,906,070
##  3 Mountain         Fat Bike             Aluminum         $1,052,620 
##  4 Mountain         Over Mountain        Carbon           $7,571,270 
##  5 Mountain         Sport                Aluminum         $1,932,755 
##  6 Mountain         Trail                Aluminum         $4,537,610 
##  7 Mountain         Trail                Carbon           $4,835,850 
##  8 Road             Cyclocross           Carbon           $2,108,120 
##  9 Road             Elite Road           Aluminum         $5,637,795 
## 10 Road             Elite Road           Carbon           $9,696,870 
## 11 Road             Endurance Road       Aluminum         $1,612,450 
## 12 Road             Endurance Road       Carbon           $8,768,610 
## 13 Road             Triathalon           Carbon           $4,053,750

# Which product categories have the most sales?
max_sales_category <- summary_df %>%
  filter(Sales == max(Sales))
print(max_sales_category)

## # A tibble: 9 × 4
## # Groups:   Prime category, Secondary category [9]
##   `Prime category` `Secondary category` `Frame Material` Sales     
##   <chr>            <chr>                <chr>            <chr>     
## 1 Mountain         Cross Country Race   Aluminum         $3,318,560
## 2 Mountain         Fat Bike             Aluminum         $1,052,620
## 3 Mountain         Over Mountain        Carbon           $7,571,270
## 4 Mountain         Sport                Aluminum         $1,932,755
## 5 Mountain         Trail                Carbon           $4,835,850
## 6 Road             Cyclocross           Carbon           $2,108,120
## 7 Road             Elite Road           Carbon           $9,696,870
## 8 Road             Endurance Road       Carbon           $8,768,610
## 9 Road             Triathalon           Carbon           $4,053,750