Homework5

1.Unique categories of products #Load and import Dataset

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(tidyr)
bike_orderlines <- readRDS("bike_orderlines.rds")

unique_categories_1 <- bike_orderlines %>%
  distinct(category_1) %>%
  arrange(category_1)

unique_categories_2 <- bike_orderlines %>%
  distinct(category_2) %>%
  arrange(category_2)

unique_frame_materials <- bike_orderlines %>%
  distinct(frame_material) %>%
  arrange(frame_material)

print(unique_categories_1)

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

print(unique_categories_2)

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

print(unique_frame_materials)

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

2.Product Categories with the Most Sales: # Primary Category (category_1)

sales_by_primary_category <- bike_orderlines %>%
  group_by(category_1) %>%
  summarise(Sales = sum(total_price, na.rm = TRUE)) %>%
  arrange(desc(Sales)) %>%
  rename('Primary Category' = category_1)

Secondary Category (category_2)

sales_by_secondary_category <- bike_orderlines %>%
  group_by(category_2) %>%
  summarise(Sales = sum(total_price, na.rm = TRUE)) %>%
  arrange(desc(Sales)) %>%
  rename('Secondary Category' = category_2)

Frame Material

sales_by_frame_material <- bike_orderlines %>%
  group_by(frame_material) %>%
  summarise(Sales = sum(total_price, na.rm = TRUE)) %>%
  arrange(desc(Sales)) %>%
  rename('Frame Material' = frame_material)

print(sales_by_primary_category)

## # A tibble: 2 × 2
##   `Primary Category`    Sales
##   <chr>                 <dbl>
## 1 Mountain           39154735
## 2 Road               31877595

print(sales_by_secondary_category)

## # A tibble: 9 × 2
##   `Secondary Category`    Sales
##   <chr>                   <dbl>
## 1 Cross Country Race   19224630
## 2 Elite Road           15334665
## 3 Endurance Road       10381060
## 4 Trail                 9373460
## 5 Over Mountain         7571270
## 6 Triathalon            4053750
## 7 Cyclocross            2108120
## 8 Sport                 1932755
## 9 Fat Bike              1052620

print(sales_by_frame_material)

## # A tibble: 2 × 2
##   `Frame Material`    Sales
##   <chr>               <dbl>
## 1 Carbon           52940540
## 2 Aluminum         18091790

3.Combinations of Primary and Secondary Bike Category for Both Aluminum and Carbon Frame Materials:

Calculate total sales for each combination of category_1, category_2, and frame_material

combined_sales <- bike_orderlines %>%
  group_by(category_1, category_2, frame_material) %>%
  summarise(Sales = sum(total_price, na.rm = TRUE), .groups = "drop")

Spread the frame_material sales into separate columns

combinations_sales <- combined_sales %>%
  spread(frame_material, Sales, fill=0) %>%
  mutate(`Total Sales` = Aluminum + Carbon) %>%
  select(category_1, category_2, Aluminum, Carbon, `Total Sales`) %>%
  arrange(desc(`Total Sales`)) %>%
  rename('Primary Category' = category_1, 'Secondary Category' = category_2)

print(combinations_sales)

## # A tibble: 9 × 5
##   `Primary Category` `Secondary Category` Aluminum   Carbon `Total Sales`
##   <chr>              <chr>                   <dbl>    <dbl>         <dbl>
## 1 Mountain           Cross Country Race    3318560 15906070      19224630
## 2 Road               Elite Road            5637795  9696870      15334665
## 3 Road               Endurance Road        1612450  8768610      10381060
## 4 Mountain           Trail                 4537610  4835850       9373460
## 5 Mountain           Over Mountain               0  7571270       7571270
## 6 Road               Triathalon                  0  4053750       4053750
## 7 Road               Cyclocross                  0  2108120       2108120
## 8 Mountain           Sport                 1932755        0       1932755
## 9 Mountain           Fat Bike              1052620        0       1052620