1. Load required libraries
library(tidyverse)
## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.4 ✔ readr 2.1.5
## ✔ forcats 1.0.1 ✔ stringr 1.5.2
## ✔ ggplot2 4.0.0 ✔ tibble 3.3.0
## ✔ lubridate 1.9.4 ✔ tidyr 1.3.1
## ✔ purrr 1.1.0
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(lubridate)
library(tidyquant)
## Registered S3 method overwritten by 'quantmod':
## method from
## as.zoo.data.frame zoo
## ── Attaching core tidyquant packages ─────────────────────── tidyquant 1.0.11 ──
## ✔ PerformanceAnalytics 2.0.8 ✔ TTR 0.24.4
## ✔ quantmod 0.4.28 ✔ xts 0.14.1── Conflicts ────────────────────────────────────────── tidyquant_conflicts() ──
## ✖ zoo::as.Date() masks base::as.Date()
## ✖ zoo::as.Date.numeric() masks base::as.Date.numeric()
## ✖ dplyr::filter() masks stats::filter()
## ✖ xts::first() masks dplyr::first()
## ✖ dplyr::lag() masks stats::lag()
## ✖ xts::last() masks dplyr::last()
## ✖ PerformanceAnalytics::legend() masks graphics::legend()
## ✖ quantmod::summary() masks base::summary()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(readxl)
library(writexl)
2. Import the data file
bikes <- read_excel("bikes.xlsx")
bikes <- bikes %>% rename_with(tolower)
glimpse(bikes)
## 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 with price in descending order
bikes_desc <- bikes %>%
select(model, price) %>%
arrange(desc(price))
bikes_desc
## # 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 model and price where price > mean(price)
mean_price <- mean(bikes$price, na.rm = TRUE)
bikes_above_mean <- bikes %>%
select(model, price) %>%
filter(price > mean_price)
mean_price
## [1] 3953.763
bikes_above_mean
## # 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
5. (Optional) Save results to Excel
write_xlsx(bikes_desc, "bikes_price_desc.xlsx")
write_xlsx(bikes_above_mean, "bikes_price_above_mean.xlsx")