1. Load libraries
library(tidyverse)
library(readxl)
library(writexl)
library(knitr)
2. Import Excel & CSV files
# Update these paths to match your file locations
bikes_tbl <- read_excel("C:/Users/dell/Documents/datas_rstudio/bikes.xlsx")
bikeshops_tbl <- read_excel("C:/Users/dell/Documents/datas_rstudio/bikeshops.xlsx")
orderlines_tbl <- read_excel("C:/Users/dell/Documents/datas_rstudio/orderlines.xlsx")
bike_orderlines_tbl <- read_csv("C:/Users/dell/Documents/datas_rstudio/bike_orderlines.csv")
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. Join data: orderlines + bikes + bikeshops
bike_orderlines_bikeshops_joined <- orderlines_tbl %>%
left_join(bikes_tbl, by = c("product.id" = "bike.id")) %>%
left_join(bikeshops_tbl, by = c("customer.id" = "bikeshop.id"))
glimpse(bike_orderlines_bikeshops_joined)
## Rows: 15,644
## Columns: 12
## $ ...1 <chr> "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11",…
## $ order.id <dbl> 1, 1, 2, 2, 3, 3, 3, 3, 3, 4, 5, 5, 5, 5, 6, 6, 6, 6, 7,…
## $ order.line <dbl> 1, 2, 1, 2, 1, 2, 3, 4, 5, 1, 1, 2, 3, 4, 1, 2, 3, 4, 1,…
## $ order.date <dttm> 2011-01-07, 2011-01-07, 2011-01-10, 2011-01-10, 2011-01…
## $ customer.id <dbl> 2, 2, 10, 10, 6, 6, 6, 6, 6, 22, 8, 8, 8, 8, 16, 16, 16,…
## $ product.id <dbl> 48, 52, 76, 52, 2, 50, 1, 4, 34, 26, 96, 66, 35, 72, 45,…
## $ quantity <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1,…
## $ model <chr> "Jekyll Carbon 2", "Trigger Carbon 2", "Beast of the Eas…
## $ description <chr> "Mountain - Over Mountain - Carbon", "Mountain - Over Mo…
## $ price <dbl> 6070, 5970, 2770, 5970, 10660, 3200, 12790, 5330, 1570, …
## $ bikeshop.name <chr> "Ithaca Mountain Climbers", "Ithaca Mountain Climbers", …
## $ location <chr> "Ithaca, NY", "Ithaca, NY", "Kansas City, KS", "Kansas C…
4. Wrangle data: split description & location
bike_orderlines_wrangled_tbl <- bike_orderlines_bikeshops_joined %>%
separate(description, into = c("category.1", "category.2", "frame.material"), sep = " - ") %>%
separate(location, into = c("city", "state"), sep = ", ", remove = FALSE)
glimpse(bike_orderlines_wrangled_tbl)
## Rows: 15,644
## Columns: 16
## $ ...1 <chr> "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11"…
## $ order.id <dbl> 1, 1, 2, 2, 3, 3, 3, 3, 3, 4, 5, 5, 5, 5, 6, 6, 6, 6, 7…
## $ order.line <dbl> 1, 2, 1, 2, 1, 2, 3, 4, 5, 1, 1, 2, 3, 4, 1, 2, 3, 4, 1…
## $ order.date <dttm> 2011-01-07, 2011-01-07, 2011-01-10, 2011-01-10, 2011-0…
## $ customer.id <dbl> 2, 2, 10, 10, 6, 6, 6, 6, 6, 22, 8, 8, 8, 8, 16, 16, 16…
## $ product.id <dbl> 48, 52, 76, 52, 2, 50, 1, 4, 34, 26, 96, 66, 35, 72, 45…
## $ quantity <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1…
## $ model <chr> "Jekyll Carbon 2", "Trigger Carbon 2", "Beast of the Ea…
## $ category.1 <chr> "Mountain", "Mountain", "Mountain", "Mountain", "Road",…
## $ category.2 <chr> "Over Mountain", "Over Mountain", "Trail", "Over Mounta…
## $ frame.material <chr> "Carbon", "Carbon", "Aluminum", "Carbon", "Carbon", "Ca…
## $ price <dbl> 6070, 5970, 2770, 5970, 10660, 3200, 12790, 5330, 1570,…
## $ bikeshop.name <chr> "Ithaca Mountain Climbers", "Ithaca Mountain Climbers",…
## $ location <chr> "Ithaca, NY", "Ithaca, NY", "Kansas City, KS", "Kansas …
## $ city <chr> "Ithaca", "Ithaca", "Kansas City", "Kansas City", "Loui…
## $ state <chr> "NY", "NY", "KS", "KS", "KY", "KY", "KY", "KY", "KY", "…
5. Show model and price with price in descending order
result_desc <- bike_orderlines_bikeshops_joined %>%
select(model, price) %>%
distinct() %>%
arrange(desc(price))
kable(result_desc, caption = "Bike Models by Price (Descending)")
Bike Models by Price (Descending)
| Supersix Evo Black Inc. |
12790 |
| Scalpel-Si Black Inc. |
12790 |
| Habit Hi-Mod Black Inc. |
12250 |
| F-Si Black Inc. |
11190 |
| Supersix Evo Hi-Mod Team |
10660 |
| Synapse Hi-Mod Disc Black Inc. |
9590 |
| F-Si Hi-Mod Team |
9060 |
| Scalpel-Si Race |
9060 |
| Trigger Carbon 1 |
8200 |
| Supersix Evo Hi-Mod Dura Ace 1 |
7990 |
| Jekyll Carbon 1 |
7990 |
| Synapse Hi-Mod Disc Red |
7460 |
| Scalpel-Si Hi-Mod 1 |
7460 |
| Habit Carbon 1 |
7460 |
| Slice Hi-Mod Black Inc. |
7000 |
| Scalpel-Si Carbon 2 |
6390 |
| Scalpel 29 Carbon Race |
6390 |
| F-Si Hi-Mod 1 |
6390 |
| Jekyll Carbon 2 |
6070 |
| Trigger Carbon 2 |
5970 |
| CAAD12 Black Inc |
5860 |
| Synapse Hi-Mod Dura Ace |
5860 |
| Supersix Evo Hi-Mod Dura Ace 2 |
5330 |
| Scalpel 29 Carbon 2 |
5330 |
| Synapse Hi-Mod Disc Ultegra |
5330 |
| Habit Carbon 2 |
5330 |
| Scalpel-Si Carbon 3 |
5330 |
| Synapse Carbon Disc Ultegra D12 |
4800 |
| F-Si Carbon 2 |
4800 |
| Slice Hi-Mod Dura Ace D12 |
4500 |
| Habit Carbon SE |
4480 |
| Supersix Evo Hi-Mod Utegra |
4260 |
| Scalpel 29 Carbon 3 |
4260 |
| CAAD12 Disc Dura Ace |
4260 |
| Scalpel-Si Carbon 4 |
4260 |
| Supersix Evo Red |
3940 |
| Jekyll Carbon 3 |
3840 |
| Synapse Carbon Disc Ultegra |
3730 |
| Habit Carbon 3 |
3730 |
| Fat CAAD1 |
3730 |
| Trigger Carbon 3 |
3730 |
| SuperX Hi-Mod CX1 |
3500 |
| Jekyll Carbon 4 |
3200 |
| Scalpel 29 4 |
3200 |
| Supersix Evo Ultegra 3 |
3200 |
| Scalpel-Si 5 |
3200 |
| CAAD12 Red |
3200 |
| Synapse Carbon Ultegra 3 |
3200 |
| Slice Ultegra D12 |
3200 |
| Bad Habit 1 |
3200 |
| Trigger Carbon 4 |
3200 |
| F-Si Carbon 4 |
2880 |
| Habit 4 |
2880 |
| Beast of the East 1 |
2770 |
| Slice Ultegra |
2700 |
| Synapse Carbon Ultegra 4 |
2660 |
| Supersix Evo Ultegra 4 |
2660 |
| CAAD Disc Ultegra |
2660 |
| Synapse Carbon Disc 105 |
2660 |
| Bad Habit 2 |
2660 |
| SuperX Ultegra |
2450 |
| Habit 5 |
2340 |
| F-Si 1 |
2340 |
| Synapse Carbon 105 |
2240 |
| CAAD12 Ultegra |
2240 |
| Supersix Evo 105 |
2240 |
| Beast of the East 2 |
2130 |
| Synapse Disc Adventure |
2130 |
| Fat CAAD2 |
2130 |
| F-Si 2 |
2060 |
| SuperX 105 |
1960 |
| Slice 105 |
1950 |
| CAAD12 Disc 105 |
1950 |
| Habit 6 |
1950 |
| Supersix Evo Tiagra |
1840 |
| Syapse Carbon Tiagra |
1840 |
| F-Si 3 |
1840 |
| SuperX Rival CX1 |
1750 |
| CAAD12 105 |
1680 |
| Beast of the East 3 |
1620 |
| Synapse Disc 105 |
1570 |
| Trail 1 |
1520 |
| CAAD8 105 |
1410 |
| Trail 2 |
1350 |
| Synapse Disc Tiagra |
1250 |
| CAAD8 Tiagra |
1250 |
| Trail 3 |
1080 |
| Synapse Sora |
1030 |
| CAAD8 Sora |
1030 |
| Trail 4 |
980 |
| Synapse Claris |
870 |
| Trail 5 |
815 |
| CAAD8 Claris |
815 |
| Catalyst 1 |
705 |
| Catalyst 2 |
585 |
| Catalyst 3 |
480 |
| Catalyst 4 |
415 |
6. Show model and price where price > mean(price)
result_above_mean <- bike_orderlines_bikeshops_joined %>%
filter(price > mean(price, na.rm = TRUE)) %>%
select(model, price) %>%
distinct() %>%
arrange(desc(price))
kable(result_above_mean, caption = "Bike Models Above Average Price")
Bike Models Above Average Price
| Supersix Evo Black Inc. |
12790 |
| Scalpel-Si Black Inc. |
12790 |
| Habit Hi-Mod Black Inc. |
12250 |
| F-Si Black Inc. |
11190 |
| Supersix Evo Hi-Mod Team |
10660 |
| Synapse Hi-Mod Disc Black Inc. |
9590 |
| F-Si Hi-Mod Team |
9060 |
| Scalpel-Si Race |
9060 |
| Trigger Carbon 1 |
8200 |
| Supersix Evo Hi-Mod Dura Ace 1 |
7990 |
| Jekyll Carbon 1 |
7990 |
| Synapse Hi-Mod Disc Red |
7460 |
| Scalpel-Si Hi-Mod 1 |
7460 |
| Habit Carbon 1 |
7460 |
| Slice Hi-Mod Black Inc. |
7000 |
| Scalpel-Si Carbon 2 |
6390 |
| Scalpel 29 Carbon Race |
6390 |
| F-Si Hi-Mod 1 |
6390 |
| Jekyll Carbon 2 |
6070 |
| Trigger Carbon 2 |
5970 |
| CAAD12 Black Inc |
5860 |
| Synapse Hi-Mod Dura Ace |
5860 |
| Supersix Evo Hi-Mod Dura Ace 2 |
5330 |
| Scalpel 29 Carbon 2 |
5330 |
| Synapse Hi-Mod Disc Ultegra |
5330 |
| Habit Carbon 2 |
5330 |
| Scalpel-Si Carbon 3 |
5330 |
| Synapse Carbon Disc Ultegra D12 |
4800 |
| F-Si Carbon 2 |
4800 |
| Slice Hi-Mod Dura Ace D12 |
4500 |
| Habit Carbon SE |
4480 |
| Supersix Evo Hi-Mod Utegra |
4260 |
| Scalpel 29 Carbon 3 |
4260 |
| CAAD12 Disc Dura Ace |
4260 |
| Scalpel-Si Carbon 4 |
4260 |
| Supersix Evo Red |
3940 |
| Jekyll Carbon 3 |
3840 |
| Synapse Carbon Disc Ultegra |
3730 |
| Habit Carbon 3 |
3730 |
| Fat CAAD1 |
3730 |
| Trigger Carbon 3 |
3730 |