Subproject 1: Methodology Estimate for Spacial Emissions
In this project, we’re estimating annual AC Transit route CO2e emissions by calculating emissions for each bus route based on exact fuel efficiency data. We begin by importing and preparing various datasets, and then join these datasets to calculate emissions per route.
# Install readxl package if not already installed to handle Excel files# install.packages("readxl") # Load required libraries for data import, manipulation, and output formattinglibrary(readxl) # For reading Excel fileslibrary(tidyverse) # For data manipulation
── 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
library(knitr) # For handling document outputknitr::opts_chunk$set(warning =FALSE) # Suppress warnings in output
Importing both sheets of Data from FY2024 Data for GRC.xlsx
The data for this analysis is stored in two sheets of the Excel file: Vehicle Miles - Route and Vehicle Metrics. Each sheet provides different information essential to calculating route emissions.
Sheet 2: Vehicle Miles - Route Data
This sheet includes vehicle miles traveled (VMT) for each route. We’ll also handle any warnings related to route name expectations.
# Import vehicle mileage data from "Vehicle Miles - Route" sheet# There are 14365 rows and 13 columns in this data framesuppressWarnings(route_mileage <-read_excel("FY2024 Data for GRC.xlsx", sheet ="Vehicle Miles - Route") )# Select specific columns, renaming as needed for consistency in later joinsroute_mileage = route_mileage |>select("VehicleId"= VehicleNumber, 1:13)
Sheet 4: Vehicle Metrics Data
This sheet contains vehicle fuel efficiency metrics needed to calculate emissions per route.
# Import vehicle metrics data from the "Vehicle Metrics" sheet# There are 619 rows and 16 columns in this data framevehicle_metrics <-read_excel("FY2024 Data for GRC.xlsx", sheet ="Vehicle Metrics") # Preview the first 10 rowsslice_head(vehicle_metrics, n =10)
Next, we join Vehicle Miles - Route and Vehicle Metrics by vehicle number to add fuel efficiency (miles per gallon diesel gallon equivalent, MPGdge) data for each vehicle on eac
# Left join route_mileage with vehicle metrics by VehicleIdvehicle_fe =left_join(x = route_mileage, y = vehicle_metrics,by =join_by(VehicleId))# Filter out rows with NA values in VehicleId (totals rows) and select needed columnsvehicle_fe = vehicle_fe |>filter(!is.na(VehicleId)) |>select(VehicleId, Route, Mi., MPGdge, `Fuel Used`)# Preview 10 rows of the data out of 14,223 rowsslice(vehicle_fe, 1:10)
Note that since we are using fuel efficiency equivalent estimates in (miles per diesel gallons equivalent) for each bus in a route, we will be using the emissions factor for diesel busses extracted from the EPA sources for our calculations. Additionally, we will be assuming fuel efficiency equivalents that have not been given in the table are 0. there are only 117 out of 14,223 rows that do not have fuel efficiency equivalents (MPGdge) given. The emissions factor (CO2e) is roughly 10.21 kg CO2e/DGE based on the EPA spreadsheet and the conversions to CH4 and N2O emissions factors to CO2e are very small (less than 10^-7), so we will be excluding those values for computational efficiency.
# edit NA columns to be 0 because MPGdge are not provided for 117 rowsvehicle_fe$MPGdge[is.na(vehicle_fe$MPGdge)] <-0# find total DGE for each route by computing Mi./fuel efficiency in DGE # adding up total DGE for each route# calculate emissions by multiplying CO2 factor# CO2 emissions factor from EPA Source Table 2CO2e =10.21# kg CO2/DGEemissions = vehicle_fe |>group_by(Route) |>mutate(DGE = Mi. / MPGdge) |>summarize(VMT =sum(Mi.), total_DGE =sum(DGE)) |>mutate(C02e_emissions = total_DGE * CO2e)# see 10 rows of emissions calculations out of 112 rowsslice(emissions, 1:10)
Rows: 167 Columns: 4
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (4): Route Name, Type, Service, The Original Route Name
ℹ 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.
route_cheat = route_cheat |>mutate(Route =as.numeric(`Route Name`))# mutate a column with total_emissions =left_join(x = emissions, y = route_cheat, by =join_by(Route)) |>slice(1:111) |>select(Route, total_DGE, VMT, C02e_emissions, Type, Service, `The Original Route Name`)# view 10 rows of total_emissions spreadsheet:slice(total_emissions, 1:10)
# A tibble: 10 × 7
Route total_DGE VMT C02e_emissions Type Service The Original Route Nam…¹
<dbl> <dbl> <dbl> <dbl> <chr> <chr> <chr>
1 6 62844. 326602. 641636. Zone Local 6
2 7 67946. 271924. 693732. Zone Local 7
3 10 62342. 307716. 636508. Zone Local 10
4 12 55262. 289773 564222. Zone Local 12
5 14 87795. 374409. 896392. Zone Local 14
6 18 78772. 401811. 804264. Zone Local 18
7 19 18003. 76666. 183810. Zone Local 19
8 20 46500. 210625. 474769. Zone Local 20
9 21 46368. 203962. 473414. Zone Local 21
10 28 32367. 159550. 330465. Zone Local 28
# ℹ abbreviated name: ¹`The Original Route Name`