R Markdown

This is an R Markdown document. Markdown is a simple formatting syntax for authoring HTML, PDF, and MS Word documents. For more details on using R Markdown see http://rmarkdown.rstudio.com.

When you click the Knit button a document will be generated that includes both content as well as the output of any embedded R code chunks within the document. You can embed an R code chunk like this:

summary(cars)
##      speed           dist       
##  Min.   : 4.0   Min.   :  2.00  
##  1st Qu.:12.0   1st Qu.: 26.00  
##  Median :15.0   Median : 36.00  
##  Mean   :15.4   Mean   : 42.98  
##  3rd Qu.:19.0   3rd Qu.: 56.00  
##  Max.   :25.0   Max.   :120.00

Including Plots

You can also embed plots, for example:

Note that the echo = FALSE parameter was added to the code chunk to prevent printing of the R code that generated the plot.

Load libraries

library(tidyverse) library(lubridate)

library(readxl) library(dplyr)

Read the data from the Excel file

bike_orderline <- read_excel(“bike_orderlines.xlsx”)

1. Fix typos in the ‘model’ column

bike_orderline <- bike_orderline %>% mutate(model = gsub(“CAAD Disk Ultegrato”, “CAAD12 Disc Ultegra”, model)) %>% mutate(model = gsub(“Syapse Carbon Tiagrato”, “Synapse Carbon Tiagra”, model)) %>% mutate(model = gsub(“Supersix Evo Hi-Mod Utegrato”, “Supersix Evo Hi-Mod Ultegra”, model))

2. Find the month with the highest bike sales

month_with_highest_sales <- bike_orderlines %>% mutate(Month = month(order_date, label = TRUE)) %>% group_by(Month) %>% summarise(Sales = sum(total_price)) %>% arrange(desc(Sales)) %>% head(1)

3. Median orderline sales value for “Black Inc”, “Ultegra”, “Disc”

Median orderline sales value for “Black Inc”

median_black_inc <- bike_orderlines %>% filter(str_detect(model, “Black Inc”)) %>% summarise(Median Orderline = scales::dollar(median(as.numeric(gsub(“[\(,]", "", Sales)))) # Median orderline sales value for "Ultegra" median_ultegra <- bike_orderlines %>% filter(str_detect(model, "Ultegra")) %>% summarise(`Median Orderline` = scales::dollar(median(as.numeric(gsub("[\),]”, ““, Sales))))

4. Calculate average, minimum, and maximum prices by base model

price_summary <- bike_orderlines %>% group_by(category_1, category_2, model) %>% summarise( Mean Price = scales::dollar(mean(price)), Min Price = scales::dollar(min(price)), Max Price = scales::dollar(max(price)) ) %>% ungroup()