B&Co - Data Analysis

###INSTALL PACKAGES:

library(tidyverse)

## ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.2 ──
## ✔ ggplot2 3.3.6     ✔ purrr   0.3.4
## ✔ tibble  3.1.7     ✔ dplyr   1.0.9
## ✔ tidyr   1.2.0     ✔ stringr 1.4.0
## ✔ readr   2.1.2     ✔ forcats 0.5.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag()    masks stats::lag()

library(readxl)
library(plotly)

## Warning: package 'plotly' was built under R version 4.2.3

## 
## Attaching package: 'plotly'
## 
## The following object is masked from 'package:ggplot2':
## 
##     last_plot
## 
## The following object is masked from 'package:stats':
## 
##     filter
## 
## The following object is masked from 'package:graphics':
## 
##     layout

###UPLOAD DATA

B_Co <- read_excel("~/DATONICS/B&Co Benchmark.xlsx")

## New names:
## • `` -> `...1`
## • `` -> `...2`
## • `` -> `...4`
## • `` -> `...5`
## • `` -> `...7`
## • `` -> `...8`
## • `` -> `...10`
## • `` -> `...11`
## • `` -> `...13`
## • `` -> `...14`
## • `` -> `...16`

###TIDY DATA

#Extrapolate on rows with cost figures
B_Co_trade <- rbind(B_Co[43:67, ])
B_Co_trade <- B_Co_trade[-(25),]
B_Co_prelim <- rbind(B_Co[83:99, ])

#Reset row names
rownames(B_Co_trade) <- NULL
rownames(B_Co_prelim) <- NULL

#Remove NAs
rows_to_delete <- c(24)
B_Co_trade <- B_Co_trade[-rows_to_delete, ]

###B_Co_tender - TIDY DATA

func_tender <- function(data) {
  kill_col <- c(2, 4, 5, 7, 8, 10, 11, 13, 14, 16)
  data <- data[, -kill_col]
  data[, 2:6] <- lapply(data[, 2:6], as.numeric)
  colnames(data)[1] <- "Category"
  data[, 2:6] <- round(data[, 2:6], digits = 0)
  return(data)
}

#Long pivot the Tender column for Trade costs
tender_trade <- func_tender(B_Co_trade)
ten_trad_long <- pivot_longer(tender_trade, cols = -Category, names_to = "Project", values_to = "Amount ($)")  #long pivot dataframe
ten_trad_long$Section <- "Trade"

#Long pivot the Tender column for Preliminary costs
tender_prelim <- func_tender(B_Co_prelim)
ten_prel_long <- pivot_longer(tender_prelim, cols = -Category, names_to = "Project", values_to = "Amount ($)")
ten_prel_long$Section <- "Preliminary"

#Combine all Tender data
B_Co_tender <- rbind(ten_trad_long, ten_prel_long)

B_Co_tender[, c(1, 2, 4)] <- lapply(B_Co_tender[, c(1, 2, 4)], as.factor)

###B_Co_complete - TIDY DATA

func_tender <- function(data) {
  kill_col <- c(2, 3, 5, 6, 8, 9, 11, 12, 14, 15)
  data <- data[, -kill_col]
  data[, 2:6] <- lapply(data[, 2:6], as.numeric)
  cols <- colnames(tender_trade)
  colnames(data) <- cols
  data[, 2:6] <- round(data[, 2:6], digits = 0)
  return(data)
}

#Fix Complete costs
complete_costs <- func_tender(B_Co_trade)
com_tra_long <- pivot_longer(complete_costs, cols = -Category, names_to = "Project", values_to = "Amount ($)")
com_tra_long$Section <- "Trade"

#Fix Tender costs
complete_prelim <- func_tender(B_Co_prelim)
com_prel_long <- pivot_longer(complete_prelim, cols = -Category, names_to = "Project", values_to = "Amount ($)")
com_prel_long$Section <- "Preliminary"

#Combine all Complete data
B_Co_complete <- rbind(com_tra_long, com_prel_long)
B_Co_complete[, c(1, 2, 4)] <- lapply(B_Co_complete[, c(1, 2, 4)], as.factor)

###B_Co

B_Co_dat <- 
  B_Co_tender %>%
  inner_join(B_Co_complete, by = c("Category", "Project", "Section"))

cols <- c("Category", "Project", "Tendered ($)", "Section", "Actual ($)")
colnames(B_Co_dat) <- cols

B_Co_dat <- B_Co_dat %>%
  select("Category", "Project", "Section", "Tendered ($)", "Actual ($)")

###Metrics

metrics_dat <- 
  data.frame(
    GIFA <- c(3458, 4302, 2900, 4500, 12550),
    Projects <- c(colnames(tender_trade)[-1]),
    Units <- c(50,67,90,65,78),
    Projected_Weeks <- c(64,80,76,78,62),
    Actual_Weeks <- c(50,82,80,74,58)
  )

col = c("GIFA","Projects","Units","Projected_Weeks","Actual_Weeks")
colnames(metrics_dat) <- col

###Summary Statistics

#Total Cost
#Add Budgeted Margin (15%)
#Cost per M2
#Cost per Unit