hw5 Financial Database

bike_orderline_table <- readRDS("C:\\Users\\emman\\Downloads\\bike_orderlines(1).rds")
#bike_orderline_table

#1
columns <- c("category_1", "category_2", "frame_material")


for (col in columns) {
  unique_categories <- unique(bike_orderline_table[[col]])
  cat("Unique categories for", col, ":\n")
  for (category in unique_categories) {
    cat(strsplit(category, " ")[[1]], "\n")
  }
  cat("\n")
}

## Unique categories for category_1 :
## Mountain 
## Road 
## 
## Unique categories for category_2 :
## Over Mountain 
## Trail 
## Elite Road 
## Endurance Road 
## Sport 
## Cross Country Race 
## Cyclocross 
## Triathalon 
## Fat Bike 
## 
## Unique categories for frame_material :
## Carbon 
## Aluminum

#2

#install.packages("formattable")
library(formattable)


format_category_total <- function(data, category_col) {
  total_sales <- aggregate(total_price ~ ., data = data[, c(category_col, "total_price")], FUN = sum)
  total_sales$total_price <- currency(total_sales$total_price, symbol = "$", digits = 0)
  return(total_sales)
}


categories <- c("category_1", "category_2", "frame_material")


for (category in categories) {
  cat("Total Sales by", category, ":\n")
  print(format_category_total(bike_orderline_table, category))
  cat("\n")
}

## Total Sales by category_1 :
##   category_1 total_price
## 1   Mountain $39,154,735
## 2       Road $31,877,595
## 
## Total Sales by category_2 :
##           category_2 total_price
## 1 Cross Country Race $19,224,630
## 2         Cyclocross  $2,108,120
## 3         Elite Road $15,334,665
## 4     Endurance Road $10,381,060
## 5           Fat Bike  $1,052,620
## 6      Over Mountain  $7,571,270
## 7              Sport  $1,932,755
## 8              Trail  $9,373,460
## 9         Triathalon  $4,053,750
## 
## Total Sales by frame_material :
##   frame_material total_price
## 1       Aluminum $18,091,790
## 2         Carbon $52,940,540

#3


library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

library(scales)

## 
## Attaching package: 'scales'

## The following objects are masked from 'package:formattable':
## 
##     comma, percent, scientific

result <- bike_orderline_table %>%
  group_by(`category_1`, `category_2`) %>%
  summarize(
    Aluminum = sum(ifelse(frame_material == "Aluminum", total_price, 0)),
    Carbon = sum(ifelse(frame_material == "Carbon", total_price, 0)),
    `Total Sales` = sum(total_price)
  )

## `summarise()` has grouped output by 'category_1'. You can override using the
## `.groups` argument.

result$Aluminum <- dollar_format(prefix = "$")(result$Aluminum)  
result$Carbon <- dollar_format(prefix = "$")(result$Carbon)  
result$`Total Sales` <- dollar_format(prefix = "$")(result$`Total Sales`)  


print(result)

## # A tibble: 9 × 5
## # Groups:   category_1 [2]
##   category_1 category_2         Aluminum   Carbon      `Total Sales`
##   <chr>      <chr>              <chr>      <chr>       <chr>        
## 1 Mountain   Cross Country Race $3,318,560 $15,906,070 $19,224,630  
## 2 Mountain   Fat Bike           $1,052,620 $0          $1,052,620   
## 3 Mountain   Over Mountain      $0         $7,571,270  $7,571,270   
## 4 Mountain   Sport              $1,932,755 $0          $1,932,755   
## 5 Mountain   Trail              $4,537,610 $4,835,850  $9,373,460   
## 6 Road       Cyclocross         $0         $2,108,120  $2,108,120   
## 7 Road       Elite Road         $5,637,795 $9,696,870  $15,334,665  
## 8 Road       Endurance Road     $1,612,450 $8,768,610  $10,381,060  
## 9 Road       Triathalon         $0         $4,053,750  $4,053,750

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.