HW 5

# Set the working directory
setwd("C:/Users/jerem/Documents/Financial Database")

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

# Load necessary libraries
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)

#QUESTION 1
# Find unique categories
unique_categories_1 <- bike_orderline_tbl %>%
  distinct(category_1)
print("Unique Category 1:")

## [1] "Unique Category 1:"

print(unique_categories_1)

## # A tibble: 2 × 1
##   category_1
##   <chr>     
## 1 Mountain  
## 2 Road

unique_categories_2 <- bike_orderline_tbl %>%
  distinct(category_2)
print("Unique Category 2:")

## [1] "Unique Category 2:"

print(unique_categories_2)

## # A tibble: 9 × 1
##   category_2        
##   <chr>             
## 1 Over Mountain     
## 2 Trail             
## 3 Elite Road        
## 4 Endurance Road    
## 5 Sport             
## 6 Cross Country Race
## 7 Cyclocross        
## 8 Triathalon        
## 9 Fat Bike

unique_frame_materials <- bike_orderline_tbl %>%
  distinct(frame_material)
print("Unique Frame Materials:")

## [1] "Unique Frame Materials:"

print(unique_frame_materials)

## # A tibble: 2 × 1
##   frame_material
##   <chr>         
## 1 Carbon        
## 2 Aluminum

#QUESTION 2
# Group by category_1 and summarize the total sales
category_1_sales <- bike_orderline_tbl %>%
  group_by(category_1) %>%
  summarize(Sales = sum(total_price))

# Format the Sales column with the dollar sign and comma
category_1_sales$Sales <- dollar(category_1_sales$Sales)

# Print the results
print("Category 1 Sales:")

## [1] "Category 1 Sales:"

print(category_1_sales)

## # A tibble: 2 × 2
##   category_1 Sales      
##   <chr>      <chr>      
## 1 Mountain   $39,154,735
## 2 Road       $31,877,595

# Group by category_2 and summarize the total sales
category_2_sales <- bike_orderline_tbl %>%
  group_by(category_2) %>%
  summarize(Sales = sum(total_price))

#Descendant Sorting
category_2_sales <- category_2_sales %>%
  arrange(desc(Sales))

# Format the Sales column with the dollar sign and comma
category_2_sales$Sales <- dollar(category_2_sales$Sales)

# Print the results
print("Category 2 Sales:")

## [1] "Category 2 Sales:"

print(category_2_sales)

## # A tibble: 9 × 2
##   category_2         Sales      
##   <chr>              <chr>      
## 1 Cross Country Race $19,224,630
## 2 Elite Road         $15,334,665
## 3 Endurance Road     $10,381,060
## 4 Trail              $9,373,460 
## 5 Over Mountain      $7,571,270 
## 6 Triathalon         $4,053,750 
## 7 Cyclocross         $2,108,120 
## 8 Sport              $1,932,755 
## 9 Fat Bike           $1,052,620

# Group by category_3 and summarize the total sales
frame_material_sales <- bike_orderline_tbl %>%
  group_by(frame_material) %>%
  summarize(Sales = sum(total_price))

#QUESTION 3
# Format the Sales column with the dollar sign and comma
frame_material_sales$Sales <- dollar(frame_material_sales$Sales)

#Descendant Sorting
frame_material_sales <- frame_material_sales %>%
  arrange(desc(Sales))

# Print the results
print("Frame Material Sales:")

## [1] "Frame Material Sales:"

print(frame_material_sales)

## # A tibble: 2 × 2
##   frame_material Sales      
##   <chr>          <chr>      
## 1 Carbon         $52,940,540
## 2 Aluminum       $18,091,790

# Group by category_1 and category_2, and summarize the total sales for Aluminum and Carbon
combinations_summary <- bike_orderline_tbl %>%
  group_by(category_1, category_2) %>%
  summarize(
    Aluminum = sum(ifelse(frame_material == "Aluminum", total_price, 0)),
    Carbon = sum(ifelse(frame_material == "Carbon", total_price, 0)),
    `Total Sales` = sum(total_price)
  )

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

# Format the data as a character vector in the desired format
formatted_result <- paste(
  combinations_summary$category_1,
  combinations_summary$category_2,
  dollar(combinations_summary$Aluminum),
  dollar(combinations_summary$Carbon),
  dollar(combinations_summary$`Total Sales`),
  sep = "   "
)

# Print the formatted result
cat("‘Primary Category‘ ‘Secondary Category‘ Aluminum   Carbon      ‘Total Sales‘\n")

## ‘Primary Category‘ ‘Secondary Category‘ Aluminum   Carbon      ‘Total Sales‘

cat(formatted_result, sep = "\n")

## Mountain   Cross Country Race   $3,318,560   $15,906,070   $19,224,630
## Mountain   Fat Bike   $1,052,620   $0   $1,052,620
## Mountain   Over Mountain   $0   $7,571,270   $7,571,270
## Mountain   Sport   $1,932,755   $0   $1,932,755
## Mountain   Trail   $4,537,610   $4,835,850   $9,373,460
## Road   Cyclocross   $0   $2,108,120   $2,108,120
## Road   Elite Road   $5,637,795   $9,696,870   $15,334,665
## Road   Endurance Road   $1,612,450   $8,768,610   $10,381,060
## Road   Triathalon   $0   $4,053,750   $4,053,750