Homework2

1. Load Required Libraries

# Load required libraries
library(tidyverse)
library(lubridate)
library(tidyquant)
library(readxl)
library(writexl)

2. Import Data Files

# Import excel files
bikes_tbl      <- read_excel("./bikes.xlsx")
bikeshops_tbl  <- read_excel("./bikeshops.xlsx")
orderlines_tbl <- read_excel("./orderlines.xlsx")

# Display structure of bikes data
glimpse(bikes_tbl)

## Rows: 97
## Columns: 4
## $ bike.id     <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,…
## $ model       <chr> "Supersix Evo Black Inc.", "Supersix Evo Hi-Mod Team", "Su…
## $ description <chr> "Road - Elite Road - Carbon", "Road - Elite Road - Carbon"…
## $ price       <dbl> 12790, 10660, 7990, 5330, 4260, 3940, 3200, 2660, 2240, 18…

3. Show Model and Price (Descending Order)

Show “model” and “price” columns with “price” in descending order.

# Select model and price columns, arrange by price in descending order
bikes_tbl %>% 
  select(model, price) %>% 
  arrange(desc(price))

## # A tibble: 97 × 2
##    model                          price
##    <chr>                          <dbl>
##  1 Supersix Evo Black Inc.        12790
##  2 Scalpel-Si Black Inc.          12790
##  3 Habit Hi-Mod Black Inc.        12250
##  4 F-Si Black Inc.                11190
##  5 Supersix Evo Hi-Mod Team       10660
##  6 Synapse Hi-Mod Disc Black Inc.  9590
##  7 Scalpel-Si Race                 9060
##  8 F-Si Hi-Mod Team                9060
##  9 Trigger Carbon 1                8200
## 10 Supersix Evo Hi-Mod Dura Ace 1  7990
## # ℹ 87 more rows

4. Show Models with Price Greater Than Mean

Show “model” and “price” columns where “price” is greater than the mean value of price.

# Filter bikes with price greater than mean price
bikes_tbl %>% 
  select(model, price) %>% 
  filter(price > mean(price))

## # A tibble: 35 × 2
##    model                          price
##    <chr>                          <dbl>
##  1 Supersix Evo Black Inc.        12790
##  2 Supersix Evo Hi-Mod Team       10660
##  3 Supersix Evo Hi-Mod Dura Ace 1  7990
##  4 Supersix Evo Hi-Mod Dura Ace 2  5330
##  5 Supersix Evo Hi-Mod Utegra      4260
##  6 CAAD12 Black Inc                5860
##  7 CAAD12 Disc Dura Ace            4260
##  8 Synapse Hi-Mod Disc Black Inc.  9590
##  9 Synapse Hi-Mod Disc Red         7460
## 10 Synapse Hi-Mod Dura Ace         5860
## # ℹ 25 more rows

Summary Statistics

# Calculate mean price for reference
mean_price <- bikes_tbl %>% pull(price) %>% mean()

# Count of bikes above and below mean
summary_stats <- bikes_tbl %>% 
  summarise(
    Total_Bikes = n(),
    Mean_Price = mean(price),
    Bikes_Above_Mean = sum(price > mean(price)),
    Bikes_Below_Mean = sum(price <= mean(price))
  )

print(summary_stats)

## # A tibble: 1 × 4
##   Total_Bikes Mean_Price Bikes_Above_Mean Bikes_Below_Mean
##         <int>      <dbl>            <int>            <int>
## 1          97      3954.               35               62

---