Sustainability Review — Buildings

1 Libraries & Data

library(dplyr)
library(tidyr)
library(stringr)
library(readxl)
library(ggplot2)
library(plotly)
library(DT)

# Place PM_V4.xlsx in the same folder as this Rmd
xlsx_path <- "PM_V4.xlsx"
stopifnot(file.exists(xlsx_path))
raw <- read_excel(xlsx_path, sheet = "Raw")
# Clean/safe column names
names(raw) <- make.names(names(raw))

# Identify key columns
bld_col <- if ("Parent.Asset.Name" %in% names(raw)) "Parent.Asset.Name" else names(raw)[1]
bld_id_col <- if ("Parent.ID" %in% names(raw)) "Parent.ID" else NA_character_
comp_name_col <- if ("Component.Name" %in% names(raw)) "Component.Name" else NA_character_
assoc_col <- if ("Associated.Asset" %in% names(raw)) "Associated.Asset" else NA_character_
asset_id_col <- if ("Asset.ID" %in% names(raw)) "Asset.ID" else NA_character_

# Helper: normaliser
norm <- function(x) trimws(tolower(as.character(x)))

# Output directory for downloadable CSVs
out_dir <- knitr::opts_knit$get("output.dir"); if (is.null(out_dir)) out_dir <- "."

# Base building list for joins and pies
if (!is.na(bld_id_col)) {
  all_buildings <- raw %>% distinct(Building = .data[[bld_col]], Building_ID = .data[[bld_id_col]])
} else {
  all_buildings <- raw %>% distinct(Building = .data[[bld_col]]) %>% mutate(Building_ID = NA_character_)
}

# Write raw data CSV for download
raw_csv <- file.path(out_dir, "raw_dataset.csv")
write.csv(raw, raw_csv, row.names = FALSE)

1.1 Portfolio Context & Raw Data Download

City of Whittlesea currently owns 203 buildings; 10 are presently out of maintenance scope. The metadata and source compilation for this project were provided by Daniel Desimone. This dataset covers 193 council buildings included in the present analysis.

This review includes AC, EV Charging, Solar, Water Tanks, Refrigeration, and Gas.

Download: Raw dataset CSV

library(dplyr)
library(tidyr)
library(stringr)
library(readxl)
library(ggplot2)
library(plotly)
library(DT)

# Place PM_V4.xlsx in the same folder as this Rmd (raw data)
xlsx_path <- "PM_V4.xlsx"
stopifnot(file.exists(xlsx_path))
raw <- read_excel(xlsx_path, sheet = "Raw")
# Clean/safe column names
names(raw) <- make.names(names(raw))

# Identify key columns
bld_col <- if ("Parent.Asset.Name" %in% names(raw)) "Parent.Asset.Name" else names(raw)[1]
comp_name_col <- if ("Component.Name" %in% names(raw)) "Component.Name" else NA_character_
assoc_col <- if ("Associated.Asset" %in% names(raw)) "Associated.Asset" else NA_character_
asset_id_col <- if ("Asset.ID" %in% names(raw)) "Asset.ID" else NA_character_

# Helper: normaliser
norm <- function(x) trimws(tolower(as.character(x)))

# Output directory for downloadable CSVs
out_dir <- knitr::opts_knit$get("output.dir"); if (is.null(out_dir)) out_dir <- "."

2 Air Conditioning Coverage (by Building)

Rule: A building is marked as Has AC if any of the following component names appear in either Component Name or Associated Asset (case-insensitive, exact-name match):

• Air Conditioning - Ceiling Cassette
• Air Conditioning - Ducted Split Indoor Unit
• Air Conditioning - Outdoor Condenser
• Air Conditioning - Packaged Unit
• Air Conditioning - Wall Mounted Indoor Unit
• Air Curtain
• Air Handling Unit (AHU)

ac_names <- c(
  "Air Conditioning - Ceiling Cassette",
  "Air Conditioning - Ducted Split Indoor Unit",
  "Air Conditioning - Outdoor Condenser",
  "Air Conditioning - Packaged Unit",
  "Air Conditioning - Wall Mounted Indoor Unit",
  "Air Curtain",
  "Air Handling Unit (AHU)"
)

has_ac_row <- rep(FALSE, nrow(raw))
if (!is.na(comp_name_col)) has_ac_row <- has_ac_row | norm(raw[[comp_name_col]]) %in% norm(ac_names)
if (!is.na(assoc_col))      has_ac_row <- has_ac_row | norm(raw[[assoc_col]]) %in% norm(ac_names)
raw$Has_AC_Row <- has_ac_row

ac_by_building <- raw %>%
  group_by(.data[[bld_col]], .add = FALSE) %>%
  summarise(Has_AC = any(Has_AC_Row, na.rm = TRUE), .groups = "drop") %>%
  rename(Building = 1)

# Attach Building ID if available
if (!is.na(bld_id_col)) {
  ac_ids <- raw %>% distinct(Building = .data[[bld_col]], Building_ID = .data[[bld_id_col]])
  ac_by_building <- ac_by_building %>% left_join(ac_ids, by = "Building") %>% relocate(Building_ID, .before = Building)
}

ac_by_building <- ac_by_building %>% arrange(desc(Has_AC), Building)

n_buildings <- dplyr::n_distinct(raw[[bld_col]])
ac_count <- sum(ac_by_building$Has_AC, na.rm = TRUE)
ac_percent <- round(100 * ac_count / n_buildings, 1)

2.1 KPI Summary

cat(paste0("Total buildings: ", n_buildings, "
"))

## Total buildings: 193

cat(paste0("Buildings with AC: ", ac_count, " (", ac_percent, "%)
"))

## Buildings with AC: 117 (60.6%)

2.2 List of Buildings with AC

ac_list <- ac_by_building %>% filter(Has_AC) %>% select(Building_ID, Building)
# Write downloadable CSV next to the HTML output
ac_csv <- file.path(out_dir, "ac_buildings.csv")
write.csv(ac_list, ac_csv, row.names = FALSE)
DT::datatable(ac_list, options = list(pageLength = 25, scrollX = TRUE))

Download: AC buildings CSV

2.3 Pie Chart — Share of Buildings with/without AC

ac_pie <- ac_by_building %>%
  transmute(group = ifelse(Has_AC, "Has AC", "No AC")) %>%
  count(group, name = "value")

plotly::plot_ly(ac_pie, labels = ~group, values = ~value, type = 'pie', textinfo = 'label+percent') %>%
  layout(title = paste0("Air Conditioning Coverage (", ac_percent, "%)"))

ac_pie <- ac_by_building %>%
  transmute(group = ifelse(Has_AC, "Has AC", "No AC")) %>%
  count(group, name = "value")

plotly::plot_ly(ac_pie, labels = ~group, values = ~value, type = 'pie', textinfo = 'label+percent') %>%
  layout(title = paste0("Air Conditioning Coverage (", ac_percent, "%)"))

3 EV Charging — Buildings and Device Counts

Goal: count (a) how many buildings have EV chargers, and (b) the total number of EV chargers. List buildings ordered by number of EV chargers (descending).

Label: detection uses exact-name match (case-insensitive) to “EV Charging Stations” in either Component Name or Associated Asset.

ev_label <- "EV Charging Stations"

# Row-level EV flag (exact name)
ev_row <- rep(FALSE, nrow(raw))
if (!is.na(comp_name_col)) ev_row <- ev_row | (norm(raw[[comp_name_col]]) == norm(ev_label))
if (!is.na(assoc_col))      ev_row <- ev_row | (norm(raw[[assoc_col]])    == norm(ev_label))
raw$EV_Row <- ev_row

# Per-building device counts (prefer distinct Asset.ID if available)
if (!is.na(asset_id_col)) {
  ev_by_building <- raw %>% filter(EV_Row) %>% group_by(.data[[bld_col]]) %>% summarise(EV_Chargers = n_distinct(.data[[asset_id_col]]), .groups = "drop")
} else {
  ev_by_building <- raw %>% filter(EV_Row) %>% group_by(.data[[bld_col]]) %>% summarise(EV_Chargers = n(), .groups = "drop")
}

# Attach Building ID
if (!is.na(bld_id_col)) {
  ev_ids <- raw %>% distinct(Building = .data[[bld_col]], Building_ID = .data[[bld_id_col]])
  ev_by_building <- ev_by_building %>% rename(Building = 1) %>% left_join(ev_ids, by = "Building") %>% relocate(Building_ID, .before = Building)
} else {
  ev_by_building <- ev_by_building %>% rename(Building = 1)
}

# KPIs
n_ev_buildings <- ev_by_building %>% filter(EV_Chargers > 0) %>% nrow()
ev_total_devices <- sum(ev_by_building$EV_Chargers, na.rm = TRUE)

# Ranked list
ev_ranked <- ev_by_building %>% arrange(desc(EV_Chargers), Building)

# Write CSV
ev_csv <- file.path(out_dir, "ev_buildings_ranked.csv")
write.csv(ev_ranked, ev_csv, row.names = FALSE)

3.1 KPIs — EV Charging

cat(paste0("Buildings with EV chargers: ", n_ev_buildings, " of ", n_buildings, "\n"))

## Buildings with EV chargers: 3 of 193

cat(paste0("Total EV charger devices: ", ev_total_devices, "\n"))

## Total EV charger devices: 20

3.1.1 Pie Chart — Share of Buildings with/without EV

ev_presence <- all_buildings %>% left_join(ev_by_building, by = c("Building", "Building_ID"))
ev_pie <- ev_presence %>%
  transmute(group = ifelse(!is.na(EV_Chargers) & EV_Chargers > 0, "Has EV", "No EV")) %>%
  count(group, name = "value")

plotly::plot_ly(ev_pie, labels = ~group, values = ~value, type = 'pie', textinfo = 'label+percent') %>%
  layout(title = "EV Charging Coverage")

3.2 Buildings Ranked by EV Chargers

DT::datatable(ev_ranked, options = list(pageLength = 25, scrollX = TRUE))

Download: EV buildings ranked CSV

4 Solar — Building Coverage and Type Mix

Rule: A building is marked as Has Solar if any of the following component names appear (case-insensitive, exact-name match in Component Name or Associated Asset):

• Solar Battery
• Solar Hot Water - Control Panel
• Solar Hot Water - Solar Collector
• Solar Inverter
• Solar Panel

solar_names <- c(
  "Solar Battery",
  "Solar Hot Water - Control Panel",
  "Solar Hot Water - Solar Collector",
  "Solar Inverter",
  "Solar Panel"
)

# Row-level flags per type
solar_flags <- lapply(solar_names, function(nm) {
  f <- rep(FALSE, nrow(raw))
  if (!is.na(comp_name_col)) f <- f | norm(raw[[comp_name_col]]) == norm(nm)
  if (!is.na(assoc_col))      f <- f | norm(raw[[assoc_col]])      == norm(nm)
  f
})

for (i in seq_along(solar_names)) raw[[paste0("SOLAR_", gsub("[^A-Za-z0-9]", "_", solar_names[i]))]] <- solar_flags[[i]]

# Building-level roll-up: presence and type mix
solar_cols <- grep("^SOLAR_", names(raw), value = TRUE)
solar_by_building <- raw %>%
  group_by(.data[[bld_col]]) %>%
  summarise(across(all_of(solar_cols), ~ any(.x, na.rm = TRUE)), .groups = "drop") %>%
  rename(Building = 1)

# Attach Building ID
if (!is.na(bld_id_col)) {
  solar_ids <- raw %>% distinct(Building = .data[[bld_col]], Building_ID = .data[[bld_id_col]])
  solar_by_building <- solar_by_building %>% left_join(solar_ids, by = "Building") %>% relocate(Building_ID, .before = Building)
}

# Has any solar
solar_by_building <- solar_by_building %>% mutate(Has_Solar = if_any(all_of(solar_cols), ~ .x))

n_solar_buildings <- sum(solar_by_building$Has_Solar, na.rm = TRUE)
solar_percent <- round(100 * n_solar_buildings / n_buildings, 1)

# Tidy per-building types
solar_long <- solar_by_building %>%
  filter(Has_Solar) %>%
  pivot_longer(cols = all_of(solar_cols), names_to = "Solar_Type", values_to = "Present") %>%
  filter(Present) %>%
  mutate(Solar_Type = gsub("^SOLAR_", "", Solar_Type), Solar_Type = gsub("_+", " ", Solar_Type)) %>%
  arrange(Building_ID, Building, Solar_Type)

# Compact per-building summary
solar_compact <- solar_long %>%
  group_by(Building_ID, Building) %>%
  summarise(Types_Present = paste(Solar_Type, collapse = ", "), .groups = "drop") %>%
  arrange(Building_ID, Building)

# Write CSV
solar_csv <- file.path(out_dir, "solar_buildings_types.csv")
write.csv(solar_compact, solar_csv, row.names = FALSE)

4.1 KPIs — Solar

cat(paste0("Buildings with Solar: ", n_solar_buildings, " of ", n_buildings, " (", solar_percent, "%)\n"))

## Buildings with Solar: 96 of 193 (49.7%)

4.1.1 Pie Chart — Share of Buildings with/without Solar

solar_pie <- solar_by_building %>%
  transmute(group = ifelse(Has_Solar, "Has Solar", "No Solar")) %>%
  count(group, name = "value")

plotly::plot_ly(solar_pie, labels = ~group, values = ~value, type = 'pie', textinfo = 'label+percent') %>%
  layout(title = "Solar Coverage")

4.2 Buildings with Solar — Types Present (per Building)

DT::datatable(solar_compact, options = list(pageLength = 25, scrollX = TRUE))

Download: Solar buildings & types CSV

5 Water Tanks — Building Coverage

Rule: A building is marked as Has Water Tank if any of the following appear (exact-name, case-insensitive) in Component Name or Associated Asset: - Rainwater Tank - Water Tank - Rainwater Pump

water_names <- c("Rainwater Tank", "Water Tank", "Rainwater Pump")

water_row <- rep(FALSE, nrow(raw))
if (!is.na(comp_name_col)) water_row <- water_row | norm(raw[[comp_name_col]]) %in% norm(water_names)
if (!is.na(assoc_col))      water_row <- water_row | norm(raw[[assoc_col]])      %in% norm(water_names)
raw$Water_Row <- water_row

water_by_building <- raw %>% group_by(.data[[bld_col]]) %>% summarise(Has_Water_Tank = any(Water_Row, na.rm = TRUE), .groups = "drop") %>% rename(Building = 1)
if (!is.na(bld_id_col)) {
  water_ids <- raw %>% distinct(Building = .data[[bld_col]], Building_ID = .data[[bld_id_col]])
  water_by_building <- water_by_building %>% left_join(water_ids, by = "Building") %>% relocate(Building_ID, .before = Building)
}

n_water_buildings <- sum(water_by_building$Has_Water_Tank, na.rm = TRUE)
water_percent <- round(100 * n_water_buildings / n_buildings, 1)

water_list <- water_by_building %>% filter(Has_Water_Tank) %>% select(Building_ID, Building)
water_csv <- file.path(out_dir, "water_tank_buildings.csv")
write.csv(water_list, water_csv, row.names = FALSE)

5.1 KPIs — Water Tanks

cat(paste0("Buildings with Water Tank: ", n_water_buildings, " of ", n_buildings, " (", water_percent, "%)\n"))

## Buildings with Water Tank: 104 of 193 (53.9%)

5.1.1 Pie Chart — Share of Buildings with/without Water Tanks

water_pie <- water_by_building %>%
  transmute(group = ifelse(Has_Water_Tank, "Has Water Tank", "No Water Tank")) %>%
  count(group, name = "value")

plotly::plot_ly(water_pie, labels = ~group, values = ~value, type = 'pie', textinfo = 'label+percent') %>%
  layout(title = "Water Tank Coverage")

5.2 Buildings with Water Tanks

DT::datatable(water_list, options = list(pageLength = 25, scrollX = TRUE))

Download: Water tank buildings CSV

6 Commercial Refrigeration / Cooling — Building Coverage

Rule: A building is marked as Has Commercial Refrigeration/Cooling if any of the following appear (exact-name, case-insensitive) in Component Name or Associated Asset: - Commercial Freezer - Commercial Fridge - Commercial Kitchen Rangehood - Computer Room Air Conditioning Unit (CRAC) - Cool Room Evaporator - Cool Room Refrigeration Condenser

refrig_names <- c(
  "Commercial Freezer",
  "Commercial Fridge",
  "Commercial Kitchen Rangehood",
  "Computer Room Air Conditioning Unit (CRAC)",
  "Cool Room Evaporator",
  "Cool Room Refrigeration Condenser"
)

refrig_row <- rep(FALSE, nrow(raw))
if (!is.na(comp_name_col)) refrig_row <- refrig_row | norm(raw[[comp_name_col]]) %in% norm(refrig_names)
if (!is.na(assoc_col))      refrig_row <- refrig_row | norm(raw[[assoc_col]])      %in% norm(refrig_names)
raw$Refrig_Row <- refrig_row

refrig_by_building <- raw %>% group_by(.data[[bld_col]]) %>% summarise(Has_Refrigeration = any(Refrig_Row, na.rm = TRUE), .groups = "drop") %>% rename(Building = 1)
if (!is.na(bld_id_col)) {
  refrig_ids <- raw %>% distinct(Building = .data[[bld_col]], Building_ID = .data[[bld_id_col]])
  refrig_by_building <- refrig_by_building %>% left_join(refrig_ids, by = "Building") %>% relocate(Building_ID, .before = Building)
}

n_refrig_buildings <- sum(refrig_by_building$Has_Refrigeration, na.rm = TRUE)
refrig_percent <- round(100 * n_refrig_buildings / n_buildings, 1)

refrig_list <- refrig_by_building %>% filter(Has_Refrigeration) %>% select(Building_ID, Building)
refrig_csv <- file.path(out_dir, "refrigeration_buildings.csv")
write.csv(refrig_list, refrig_csv, row.names = FALSE)

6.1 KPIs — Commercial Refrigeration/Cooling

cat(paste0("Buildings with commercial refrigeration/cooling: ", n_refrig_buildings, " of ", n_buildings, " (", refrig_percent, "%)\n"))

## Buildings with commercial refrigeration/cooling: 87 of 193 (45.1%)

6.1.1 Pie Chart — Share of Buildings with/without Refrigeration/Cooling

refrig_pie <- refrig_by_building %>%
  transmute(group = ifelse(Has_Refrigeration, "Has Refrigeration", "No Refrigeration")) %>%
  count(group, name = "value")

plotly::plot_ly(refrig_pie, labels = ~group, values = ~value, type = 'pie', textinfo = 'label+percent') %>%
  layout(title = "Refrigeration/Cooling Coverage")

6.2 Buildings with Commercial Refrigeration/Cooling

DT::datatable(refrig_list, options = list(pageLength = 25, scrollX = TRUE))

Download: Commercial refrigeration buildings CSV

7 Gas — Building Coverage

Rule: A building is marked as Uses Gas if any of the following appear (exact-name, case-insensitive) in Component Name or Associated Asset: - DHW Boiler - Gas - Gas Fired Wall Furnace - Bench Top Stove - Gas - Ducted Gas Heater - Freestanding Stove & Oven - Gas

gas_names <- c(
  "DHW Boiler - Gas",
  "Gas Fired Wall Furnace",
  "Bench Top Stove - Gas",
  "Ducted Gas Heater",
  "Freestanding Stove & Oven - Gas"
)

gas_row <- rep(FALSE, nrow(raw))
if (!is.na(comp_name_col)) gas_row <- gas_row | norm(raw[[comp_name_col]]) %in% norm(gas_names)
if (!is.na(assoc_col))      gas_row <- gas_row | norm(raw[[assoc_col]])      %in% norm(gas_names)
raw$Gas_Row <- gas_row

gas_by_building <- raw %>% group_by(.data[[bld_col]]) %>% summarise(Uses_Gas = any(Gas_Row, na.rm = TRUE), .groups = "drop") %>% rename(Building = 1)
if (!is.na(bld_id_col)) {
  gas_ids <- raw %>% distinct(Building = .data[[bld_col]], Building_ID = .data[[bld_id_col]])
  gas_by_building <- gas_by_building %>% left_join(gas_ids, by = "Building") %>% relocate(Building_ID, .before = Building)
}

n_gas_buildings <- sum(gas_by_building$Uses_Gas, na.rm = TRUE)
gas_percent <- round(100 * n_gas_buildings / n_buildings, 1)

gas_list <- gas_by_building %>% filter(Uses_Gas) %>% select(Building_ID, Building)
gas_csv <- file.path(out_dir, "gas_buildings.csv")
write.csv(gas_list, gas_csv, row.names = FALSE)

7.1 KPIs — Gas

cat(paste0("Buildings using Gas: ", n_gas_buildings, " of ", n_buildings, " (", gas_percent, "%)\n"))

## Buildings using Gas: 89 of 193 (46.1%)

7.1.1 Pie Chart — Share of Buildings with/without Gas

gas_pie <- gas_by_building %>%
  transmute(group = ifelse(Uses_Gas, "Uses Gas", "No Gas")) %>%
  count(group, name = "value")

plotly::plot_ly(gas_pie, labels = ~group, values = ~value, type = 'pie', textinfo = 'label+percent') %>%
  layout(title = "Gas Usage Coverage")

7.2 Buildings using Gas

DT::datatable(gas_list, options = list(pageLength = 25, scrollX = TRUE))

Download: Gas buildings CSV