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(readxl)
library(writexl)
library(knitr)
# 2. Data import
# ./, ../,
bikes_tbl <- read_excel("C:/Users/dell/OneDrive/Documents/bikes.xlsx") # fast key: alt+-
bikeshops_tbl <- read_excel("C:/Users/dell/OneDrive/Documents/bikeshops.xlsx")
orderlines_tbl <- read_excel("C:/Users/dell/OneDrive/Documents/orderlines.xlsx")
## New names:
## • `` -> `...1`
# Examine data:
bikes_tbl
## # A tibble: 97 × 4
## bike.id model description price
## <dbl> <chr> <chr> <dbl>
## 1 1 Supersix Evo Black Inc. Road - Elite Road - Carbon 12790
## 2 2 Supersix Evo Hi-Mod Team Road - Elite Road - Carbon 10660
## 3 3 Supersix Evo Hi-Mod Dura Ace 1 Road - Elite Road - Carbon 7990
## 4 4 Supersix Evo Hi-Mod Dura Ace 2 Road - Elite Road - Carbon 5330
## 5 5 Supersix Evo Hi-Mod Utegra Road - Elite Road - Carbon 4260
## 6 6 Supersix Evo Red Road - Elite Road - Carbon 3940
## 7 7 Supersix Evo Ultegra 3 Road - Elite Road - Carbon 3200
## 8 8 Supersix Evo Ultegra 4 Road - Elite Road - Carbon 2660
## 9 9 Supersix Evo 105 Road - Elite Road - Carbon 2240
## 10 10 Supersix Evo Tiagra Road - Elite Road - Carbon 1840
## # ℹ 87 more rows
head(bikes_tbl)
## # A tibble: 6 × 4
## bike.id model description price
## <dbl> <chr> <chr> <dbl>
## 1 1 Supersix Evo Black Inc. Road - Elite Road - Carbon 12790
## 2 2 Supersix Evo Hi-Mod Team Road - Elite Road - Carbon 10660
## 3 3 Supersix Evo Hi-Mod Dura Ace 1 Road - Elite Road - Carbon 7990
## 4 4 Supersix Evo Hi-Mod Dura Ace 2 Road - Elite Road - Carbon 5330
## 5 5 Supersix Evo Hi-Mod Utegra Road - Elite Road - Carbon 4260
## 6 6 Supersix Evo Red Road - Elite Road - Carbon 3940
# Import csv file:
bike_orderlines_tbl <- read_csv("C:/Users/dell/OneDrive/Documents/bike_orderlines.csv")
## Rows: 15644 Columns: 13
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (7): model, category_1, category_2, frame_material, bikeshop_name, city...
## dbl (5): order_id, order_line, quantity, price, total_price
## dttm (1): order_date
##
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
# Joining data:
orderlines_bikes_tbl <- left_join(orderlines_tbl, bikes_tbl, by = c("product.id" = "bike.id"))
bike_orderlines_bikeshops_joined <- left_join(orderlines_bikes_tbl, bikeshops_tbl,
by = c('customer.id' = 'bikeshop.id'))
# %>% is called pipe: fast key: ctl + shift + m
bike_orderlines_bikeshops_joined <- left_join(orderlines_tbl, bikes_tbl, by = c("product.id" = "bike.id")) %>%
left_join(bikeshops_tbl, by = c("customer.id" = "bikeshop.id"))
# Wrangling data: decompose description into three columns: category.1, category.2 and frame.material
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)
# Show model and price in descending order
bikes_desc <- bike_orderlines_bikeshops_joined %>%
select(model, price) %>%
distinct() %>%
arrange(desc(price))
kable(bikes_desc, caption = "Bikes by Price in descending order")
Bikes by Price in descending order
| 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 |
# Show model and price where price is greater than mean price
mean_price <- mean(bike_orderlines_bikeshops_joined$price, na.rm = TRUE)
bikes_greater_than_mean <- bike_orderlines_bikeshops_joined %>%
select(model, price) %>%
distinct() %>%
filter(price > mean_price)
kable(bikes_greater_than_mean, caption = "Bikes by price greater than mean price")
Bikes by price greater than mean price
| Jekyll Carbon 2 |
6070 |
| Trigger Carbon 2 |
5970 |
| Supersix Evo Hi-Mod Team |
10660 |
| Supersix Evo Black Inc. |
12790 |
| Supersix Evo Hi-Mod Dura Ace 2 |
5330 |
| Synapse Carbon Disc Ultegra D12 |
4800 |
| F-Si Black Inc. |
11190 |
| Slice Hi-Mod Dura Ace D12 |
4500 |
| Scalpel-Si Black Inc. |
12790 |
| F-Si Hi-Mod Team |
9060 |
| Scalpel-Si Race |
9060 |
| CAAD12 Black Inc |
5860 |
| Synapse Hi-Mod Disc Red |
7460 |
| Synapse Carbon Disc Ultegra |
3730 |
| Jekyll Carbon 3 |
3840 |
| Synapse Hi-Mod Disc Black Inc. |
9590 |
| Scalpel 29 Carbon 2 |
5330 |
| Scalpel-Si Hi-Mod 1 |
7460 |
| F-Si Carbon 2 |
4800 |
| Supersix Evo Hi-Mod Dura Ace 1 |
7990 |
| Supersix Evo Hi-Mod Utegra |
4260 |
| Habit Carbon SE |
4480 |
| Slice Hi-Mod Black Inc. |
7000 |
| Scalpel 29 Carbon 3 |
4260 |
| Habit Carbon 3 |
3730 |
| Trigger Carbon 1 |
8200 |
| Fat CAAD1 |
3730 |
| Synapse Hi-Mod Dura Ace |
5860 |
| Supersix Evo Red |
3940 |
| CAAD12 Disc Dura Ace |
4260 |
| Jekyll Carbon 1 |
7990 |
| Scalpel-Si Carbon 2 |
6390 |
| Synapse Hi-Mod Disc Ultegra |
5330 |
| Scalpel 29 Carbon Race |
6390 |
| Scalpel-Si Carbon 4 |
4260 |
| Habit Carbon 1 |
7460 |
| Trigger Carbon 3 |
3730 |
| Habit Carbon 2 |
5330 |
| Habit Hi-Mod Black Inc. |
12250 |
| F-Si Hi-Mod 1 |
6390 |
| Scalpel-Si Carbon 3 |
5330 |