Hw05 113035124

# Load the libraries we need
library(readxl)
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

## 
## 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

# Read the Excel file into R
bike_data <- read_excel("bike_orderlines.xlsx")

# Create a subset with only Mountain and Road bikes
mountain_bikes <- subset(bike_data, category_1 == "Mountain")
road_bikes <- subset(bike_data, category_1 == "Road")
filtered_bikes <- rbind(mountain_bikes, road_bikes)

# Calculate total sales for each bike shop and category
sales_summary <- aggregate(
  quantity ~ bikeshop_name + category_1, 
  data = filtered_bikes,
  sum
)

# # Find top 3 bike shops for Mountain bikes
mountain_top3 <- sales_summary[
  sales_summary$category_1 == "Mountain",
][order(sales_summary$quantity[sales_summary$category_1 == "Mountain"], 
        decreasing = TRUE),
][1:3,]

# Find top 3 bike shops for Road bikes
road_top3 <- sales_summary[
  sales_summary$category_1 == "Road",
][order(sales_summary$quantity[sales_summary$category_1 == "Road"], 
        decreasing = TRUE),
][1:3,]

# Combine the results
final_results <- rbind(mountain_top3, road_top3)

# Show the results
print(final_results)

##                   bikeshop_name category_1 quantity
## 11            Kansas City 29ers   Mountain     2484
## 7              Denver Bike Shop   Mountain     1623
## 10     Ithaca Mountain Climbers   Mountain      953
## 41            Kansas City 29ers       Road      987
## 50 Oklahoma City Race Equipment       Road      822
## 52              Phoenix Bi-peds       Road      697