# Install the package if not already installed#install.packages("readxl")# Load necessary packageslibrary(readxl)library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.1.4 ✔ readr 2.1.5
✔ forcats 1.0.0 ✔ stringr 1.5.1
✔ ggplot2 3.5.1 ✔ tibble 3.2.1
✔ lubridate 1.9.3 ✔ tidyr 1.3.1
✔ purrr 1.0.2
── 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
Data/Sources Used:
This is a list of the data sets and sources that are used for the calculations below:
FY2024 Data. xlsx: extracted Annual VMT per route and % of ZEB per route
Sustainability Report 2023
Methodology Document:
Rough notes of my methodology so far:
import data form FY2024 Data.xlsx –> read each sheet of the data, noting relevant informaiton
remove last rows of data and clean up column names
join both sheets by route number to get the mileage, %ZEB in same sheet
left join RouteFact with Clean Corridor
store relevant columns in the new combined data frame
Get Vehicle Miles Traveled for ZEB and not ZEB on Each Route:
create new columns for ZEB mileage per route and non-ZEB mileage per route
clean up data by adding 0s instead of NAs to the columns with no ZEB info
Find and store Fuel Efficiency:
store data from Sustainability Report 2023, estimate values for Battery, Fuel Cell, and Diesel fuel efficiencFy
Find and store Carbon Factors:
search AC Transit, EPA, and other reliable sources to find data on carbon factors for each bus type
To-do:
get fuel cell carbon factor
get annual carbon emissions factor for diesel buses
Importing both sheets of Data from FY2024 Data.xlsx
# Import sheet 1, Mileage datamileage <-read_excel("VMT_Route.xlsx", sheet ="RouteFact") # There are 134 rows and 8 columns in this data framemileage =slice(mileage, 1:133) # Remove last row , which is totals# View first 10 rows of dataslice_head(mileage, n =10)
# Import sheet 2 Clean Corridor data# There are 52 rows and 9 columns in this data framepercent_ZEB <-read_excel("VMT_Route.xlsx", sheet ="Clean Corridor") # Remove last row which is the totals, change RouteAlpha to Routepercent_ZEB =slice(percent_ZEB, 1:51) |>select("Route"= RouteAlpha, 2:9)# View first 10 rows of dataslice_head(percent_ZEB, n =10)
# Make sure to exclude last column which is the totals on both sheets# Left join mileage with percent_ZEB on route numbermileage_ZEB =left_join(x = mileage, y = percent_ZEB,by =join_by(Route))# View first 10 rows of new data set of necessary columns Route, Mi., Op ZEB, ZEB%, Op, Not Op, SO Y%mileage_ZEB = mileage_ZEB |>select(Route, `Mi.`, `Op`, `Not Op`, `SO Y %`, `Op ZEB`, `ZEB%`) slice_head(mileage_ZEB, n =10)
Get Vehicle Miles Traveled for ZEB and not ZEB on Each Route:
# Mutating new columns to calcualte VMT for ZEB and not ZEB per routemileage_ZEB = mileage_ZEB |>mutate(VMT_ZEB =`Mi.`*`ZEB%`,VMT_ZEB =replace_na(VMT_ZEB, 0),VMT_Not_ZEB =`Mi.`-(VMT_ZEB))# View VMT ZEB and not ZEB for first 10 rowsmileage_ZEB |>select(Route, VMT_ZEB, VMT_Not_ZEB) |>slice_head(n =10)
# Storing variables for diesel emissions factors & conversions:# emissions factor * global warming potential * g to kg conversion / CO2 diesel gallons conversionCO2_gallons_conversion =10.21CH4_emissions_diesel = (0.0005*25*0.001) / CO2_gallons_conversionN20_emissions_diesel = (0.0010*298*0.001) / CO2_gallons_conversionemissions_factor = CH4_emissions_diesel + N20_emissions_diesel# check with luke if CO2 emissions diesel works out for calculations (kg CO2 per gallon)# Storing variables for battery factors and conversions to from mwh to kwhCO2_emissions_electric =497.4*0.001SO2_emissions_electric =0.03*0.001NOX_emissions_electric =0.467*0.001
Calculate Annual Diesel (Non-ZEB) Emissions for each Route: