QUESTION 1
setwd("C:/Users/jerem/Documents/Financial Database")
# Load the data from the .rds file
bike_orderline_tbl <- readRDS("bike_orderlines (1).rds")
library(tidyverse)## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.0 ✔ readr 2.1.4
## ✔ forcats 1.0.0 ✔ stringr 1.5.0
## ✔ ggplot2 3.4.1 ✔ tibble 3.1.8
## ✔ lubridate 1.9.2 ✔ tidyr 1.3.0
## ✔ purrr 1.0.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the ]8;;http://conflicted.r-lib.org/conflicted package]8;; to force all conflicts to become errorslibrary(lubridate)
# Fix typos in the 'model' column
bike_orderline_tbl$model <- gsub("CAAD Disk Ultegra", "CAAD12 Disc Ultegra", bike_orderline_tbl$model)
bike_orderline_tbl$model <- gsub("Syapse Carbon Tiagra", "Synapse Carbon Tiagra", bike_orderline_tbl$model)
bike_orderline_tbl$model <- gsub("Supersix Evo Hi-Mod Utegra", "Supersix Evo Hi-Mod Ultegra", bike_orderline_tbl$model)
head(bike_orderline_tbl)## # A tibble: 6 × 13
## order_date order_id order_line quantity price total_p…¹ model categ…²
## <dttm> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr>
## 1 2011-01-07 00:00:00 1 1 1 6070 6070 Jeky… Mounta…
## 2 2011-01-07 00:00:00 1 2 1 5970 5970 Trig… Mounta…
## 3 2011-01-10 00:00:00 2 1 1 2770 2770 Beas… Mounta…
## 4 2011-01-10 00:00:00 2 2 1 5970 5970 Trig… Mounta…
## 5 2011-01-10 00:00:00 3 1 1 10660 10660 Supe… Road
## 6 2011-01-10 00:00:00 3 2 1 3200 3200 Jeky… Mounta…
## # … with 5 more variables: category_2 <chr>, frame_material <chr>,
## # bikeshop_name <chr>, city <chr>, state <chr>, and abbreviated variable
## # names ¹total_price, ²category_1QUESTION 2
# Convert 'order_date' to a date and extract the month
bike_orderline_tbl <- bike_orderline_tbl %>%
mutate(order_month = format(order_date, "%B"))
# Group by month and calculate total sales
monthly_sales <- bike_orderline_tbl %>%
group_by(order_month) %>%
summarize(Sales = sum(total_price, na.rm = TRUE)) %>%
arrange(desc(Sales))
# Format 'Sales' column with a dollar sign and comma
monthly_sales$Sales <- scales::dollar(monthly_sales$Sales, prefix = "$", scale = 1) # Scale for dollar sign and
# Print the updated 'monthly_sales' data frame
print(monthly_sales)## # A tibble: 12 × 2
## order_month Sales
## <chr> <chr>
## 1 April $8,386,170
## 2 May $7,935,055
## 3 June $7,813,105
## 4 July $7,602,005
## 5 March $7,282,280
## 6 September $5,556,055
## 7 August $5,346,125
## 8 February $5,343,295
## 9 October $4,394,300
## 10 November $4,169,755
## 11 January $4,089,460
## 12 December $3,114,725QUESTION 3
# Filter data for 'Black Inc'
black_inc_data <- bike_orderline_tbl %>%
filter(str_detect(model, "Black Inc"))
# Calculate the median orderline sales value for 'Black Inc' (TRUE)
median_sales_black_inc_true <- scales::dollar(median(black_inc_data$total_price, na.rm = TRUE), scale = 1)
# Filter data for non-'Black Inc'
non_black_inc_data <- bike_orderline_tbl %>%
filter(!str_detect(model, "Black Inc"))
# Calculate the median orderline sales value for non-'Black Inc' (FALSE)
median_sales_black_inc_false <- scales::dollar(median(non_black_inc_data$total_price, na.rm = TRUE), scale = 1)
# Create a tibble with 'TRUE' and 'FALSE' values
result_black_inc <- tibble(
'Black Inc' = c(FALSE, TRUE),
'Median Orderline' = c(median_sales_black_inc_false, median_sales_black_inc_true)
)
# Print the result for 'Black Inc'
print(result_black_inc)## # A tibble: 2 × 2
## `Black Inc` `Median Orderline`
## <lgl> <chr>
## 1 FALSE $2,880
## 2 TRUE $12,250# Filter data for 'Ultegra'
ultegra_data <- bike_orderline_tbl %>%
filter(str_detect(model, "Ultegra"))
# Calculate the median orderline sales value for 'Ultegra' (TRUE)
median_sales_ultegra_true <- scales::dollar(median(ultegra_data$total_price, na.rm = TRUE), scale = 1)
# Filter data for non-'Ultegra'
non_ultegra_data <- bike_orderline_tbl %>%
filter(!str_detect(model, "Ultegra"))
# Calculate the median orderline sales value for non-'Ultegra' (FALSE)
median_sales_ultegra_false <- scales::dollar(median(non_ultegra_data$total_price, na.rm = TRUE), scale = 1)
# Create a tibble with 'TRUE' and 'FALSE' values for 'Ultegra'
result_ultegra <- tibble(
"Ultegra" = c(FALSE, TRUE),
'Median Orderline' = c(median_sales_ultegra_false, median_sales_ultegra_true)
)
# Print the result for 'Ultegra'
print(result_ultegra)## # A tibble: 2 × 2
## Ultegra `Median Orderline`
## <lgl> <chr>
## 1 FALSE $3,200
## 2 TRUE $3,200# Filter data for 'Disc'
disc_data <- bike_orderline_tbl %>%
filter(str_detect(model, "Disc"))
# Calculate the median orderline sales value for 'Disc' (TRUE)
median_sales_disc_true <- scales::dollar(median(disc_data$total_price, na.rm = TRUE), scale = 1)
# Filter data for non-'Disc'
non_disc_data <- bike_orderline_tbl %>%
filter(!str_detect(model, "Disc"))
# Calculate the median orderline sales value for non-'Disc' (FALSE)
median_sales_disc_false <- scales::dollar(median(non_disc_data$total_price, na.rm = TRUE), scale = 1)
# Create a tibble with 'TRUE' and 'FALSE' values for 'Disc'
result_disc <- tibble(
"Disc" = c(FALSE, TRUE),
'Median Orderline' = c(median_sales_disc_false, median_sales_disc_true)
)
# Print the result for 'Disc'
print(result_disc)## # A tibble: 2 × 2
## Disc `Median Orderline`
## <lgl> <chr>
## 1 FALSE $3,200
## 2 TRUE $2,660QUESTION 4
# Extract the common category based on the first word
bike_orderline_tbl_mutate <- bike_orderline_tbl %>%
mutate(common_model = sub("^(\\w+).*", "\\1", model))
# Group by category_1 and category_2, and summarize the total sales for Aluminum and Carbon
combinations_summary <- bike_orderline_tbl_mutate %>%
group_by(category_1, category_2, common_model) %>%
summarize(
`Mean Price` = scales::dollar(mean(price, na.rm = TRUE), scale = 1, prefix = "$"),
`Min Price` = scales::dollar(min(price, na.rm = TRUE), scale = 1, prefix = "$"),
`Max Price` = scales::dollar(max(price, na.rm = TRUE), scale = 1, prefix = "$")
)## `summarise()` has grouped output by 'category_1', 'category_2'. You can
## override using the `.groups` argument.print(combinations_summary) ## # A tibble: 17 × 6
## # Groups: category_1, category_2 [9]
## category_1 category_2 common_model `Mean Price` `Min Price` Max Pri…¹
## <chr> <chr> <chr> <chr> <chr> <chr>
## 1 Mountain Cross Country Race F $4,503.89 $1,840 $11,190
## 2 Mountain Cross Country Race Scalpel $5,900.91 $3,200 $12,790
## 3 Mountain Fat Bike Fat $2,766.73 $2,130 $3,730
## 4 Mountain Over Mountain Jekyll $5,041.50 $3,200 $7,990
## 5 Mountain Over Mountain Trigger $4,970.07 $3,200 $8,200
## 6 Mountain Sport Catalyst $540.95 $415 $705
## 7 Mountain Sport Trail $1,153.47 $815 $1,520
## 8 Mountain Trail Bad $2,953.51 $2,660 $3,200
## 9 Mountain Trail Beast $2,194.16 $1,620 $2,770
## 10 Mountain Trail Habit $4,610.89 $1,950 $12,250
## 11 Road Cyclocross SuperX $2,338.71 $1,750 $3,500
## 12 Road Elite Road CAAD $2,660 $2,660 $2,660
## 13 Road Elite Road CAAD12 $2,977.98 $1,680 $5,860
## 14 Road Elite Road CAAD8 $1,136.43 $815 $1,410
## 15 Road Elite Road Supersix $4,978.21 $1,840 $12,790
## 16 Road Endurance Road Synapse $3,080.09 $870 $9,590
## 17 Road Triathalon Slice $3,526.59 $1,950 $7,000
## # … with abbreviated variable name ¹`Max Price`