STA 9750 Mini-Project #02: Identifying Environmentally Responsible US Public Transit Systems

\[\newcommand{\P}{\mathbb{P}} \newcommand{\E}{\mathbb{E}} \newcommand{\R}{\mathbb{R}}\]

Key Dates

• Released to Students: 2026-10-15
• Initial Submission: 2026-11-06 11:59pm ET on GitHub and Brightspace
• Peer Feedback:
  • Peer Feedback Assigned: 2026-11-09 on GitHub
  • Peer Feedback Due: 2026-11-16 11:59pm ET on Brightspace
• Estimated Return of Grades and Comments:
  • Initial Submission: 2026-11-20
  • Meta-Review: 2026-11-24

Estimated Time to Complete: 13-15 Hours

Estimated Time for Peer Feedback: 1 Hour

---

Introduction

Welcome to Mini-Project #02!

In this project, you will take on the role of Executive Director of the Green Transit Alliance (GTA), as you prepare to publish your annual awards for the “greenest” public transit agencies. Each year, GTA awards prizes to the small, medium, and large transit agencies that score the highest on the GTA Value Index (GTA VI). The GTA VI estimates the effective emissions reductions achieved by each agency, using a combination of:

• Local ridership data (both number and distance); and
• Emissions estimates of public transit vehicles (either direct tailpipe emissions or upstream emissions from electrical generation)

By comparing these to the tailpipe emissions that would have occured if all riders had instead driven to their destination, the VI identifies the most valuable public transit agencies in the country.

Your final submission for this mini-project should take the form of a press release, announcing the winners of the various GTA awards and giving the key statistics that lead to their selection.

For this project, you will use “multi-table” operations such as joins and pivots, to combine data from different sources to generate novel insights not attainable from any individual table. Our analysis will bring in data from two federal sources, the National Transit Database and the Energy Information Administration.

In this mini-project, you will:

1. Practice use of dplyr for combining and manipulating data from distinct sources
2. Practice use of ggplot2 to create compelling statistical visualizations. Note that this assignment only requires “basic” ggplot2 visualizations like scatterplots or line graphs; Mini-Project #03 will require more complex visualization.
3. Practice summarizing complex analytical pipelines for non-technical audiences.

As with Mini-Project #01, a major portion of your grade is based on the communication and writing elements of this mini-project, so make sure to budget sufficient time for the writing process. Code without context has little practical value - you are always writing and conveying your analysis to a specific target audience.

Student Responsibilities

Recall our basic analytic workflow and table of student responsibilities:

• Data Ingest and Cleaning: Given a data source, read it into R and transform it to a reasonably useful and standardized (‘tidy’) format.
• Data Combination and Alignment: Combine multiple data sources to enable insights not possible from a single source.
• Descriptive Statistical Analysis: Take a data table and compute informative summary statistics from both the entire population and relevant subgroups
• Data Visualization: Generate insightful data visualizations to spur insights not attainable from point statistics
• Inferential Statistical Analysis and Modeling: Develop relevant predictive models and statistical analyses to generate insights about the underlying population and not simply the data at hand.

In this course, our primary focus is on the first four stages: you will take other courses that develop analytical and modeling techniques for a variety of data types. As we progress through the course, you will eventually be responsible for the first four steps. Specifically, you are responsible for the following stages of each mini-project:

Students’ Responsibilities in Mini-Project Analyses

	Ingest and Cleaning	Combination and Alignment	Descriptive Statistical Analysis	Visualization
Mini-Project #01			✓
Mini-Project #02		✓	✓	½
Mini-Project #03	½	✓	✓	✓
Mini-Project #04	✓	✓	✓	✓

In this mini-project, you are mainly responsible for data alignment and basic statistical analyses. While not the main focus of this mini-project, you are also expected to provide basic data visualizations to support your findings; the grading rubric, below, emphasizes the dplyr tools used in this project, but reports without any visualization will be penalized severely. Note that data visualization will play a larger role in Mini-Project #03.

As before, I will provide code to automatically download and read in the data used for this project. Also note that, as compared with Mini-Project #01, I am providing less ‘scaffolding’ for tabular display and EDA: students are more responsible for directing their own analyses.

Rubric

STA 9750 Mini-Projects are evaluated using peer grading with meta-review by the course staff (GTAs and the instructor). The following basic rubric will be used for all mini-projects:

Course Element	Excellent (9-10)	Great (7-8)	Good (5-6)	Adequate (3-4)	Needs Improvement (1-2)
Written Communication	Report is very well-written and flows naturally. Motivation for key steps is clearly explained to reader without excessive detail. Key findings are highlighted and appropriately given sufficient context, including reference to related work where appropriate.	Report has no grammatical or writing issues.1 Writing is accessible and flows naturally. Key findings are highlighted and clearly explained, but lack suitable motivation and context.	Report has no grammatical or writing issues. Key findings are present but insufficiently highlighted or unclearly explained.	Writing is intelligible, but has some grammatical errors. Key findings are difficult to discern.	Report exhibits significant weakness in written communication. Key points are nearly impossible to identify.
Project Skeleton	Code completes all instructor-provided tasks correctly. Responses to open-ended tasks are especially insightful and creative.	Code completes all instructor-provided tasks satisfactorily. Responses to open-ended tasks are insightful, creative, and do not have any minor flaws.	Response to one instructor provided task is skipped, incorrect, or otherwise incomplete. Responses to open-ended tasks are solid and without serious flaws.	Responses to two instructor provided tasks are skipped, incorrect, or otherwise incomplete. Responses to open-ended tasks are acceptable, but have at least one serious flaw.	Response to three or more instructor provided tasks are skipped, incorrect, or otherwise incomplete. Responses to open-ended tasks are seriously lacking.
Tables & Document Presentation	Tables go beyond standard publication-quality formatting, using advanced features like color formatting, interactivity, or embedded visualization.	Tables are well-formatted, with publication-quality selection of data to present, formatting of table contents (e.g., significant figures) and column names.	Tables are well-formatted, but still have room for improvement in one of these categories: subsetting and selection of data to present, formatting of table contents (e.g., significant figures), column names.	Tables lack significant ‘polish’ and need improvement in substance (filtering and down-selecting of presented data) or style. Document is difficult to read due to distracting formatting choices.	Unfiltered ‘data dump’ instead of curated table. Document is illegible at points.
Data Visualization	Figures go beyond standard publication-quality formatting, using advanced features like animation, interactivity, or advanced plot types implemented in ggplot2 extension packages.	Figures are ‘publication-quality,’ with suitable axis labels, well-chosen structure, attractive color schemes, titles, subtitles, and captions, etc.	Figures are above ‘exploratory-quality’ and reflect a moderate degree of polish, but do not reach full ‘publication-quality’ in one-to-two ways.	Figures are above ‘exploratory-quality’ and reflect a moderate degree of polish, but do not reach full ‘publication-quality’ in three or more distinct ways.	Figures are suitable to support claims made, but are ‘exploratory-quality,’ reflecting zero-to-minimal effort to customize and ‘polish’ beyond ggplot2 defaults.
Exploratory Data Analysis	Deep and ‘story-telling’ EDA identifying non-obvious patterns that are then used to drive further analysis in support of the project. All patterns and irregularities are noted and well characterized, demonstrating mastery and deep understanding of all data sets used.	Meaningful ‘story-telling’ EDA identifying non-obvious patterns in the data. Major and pinor patterns and irregularities are noted and well characterized at a level sufficient to achieve the goals of the analysis. EDA demonstrates clear understanding of all data sets used.	Extensive EDA that thoroughly explores the data, but lacks narrative and does not deliver a meaningful ‘story’ to the reader. Obvious patterns or irregularities noted and well characterized, but more subtle structure may be overlooked or not fully discussed. EDA demonstrates competence and basic understanding of the data sets used.	Solid EDA that identifies major structure to the data, but does not fully explore all relevant structure. Obvious patterns or irregularities ignored or missed. EDA demonstrates familiarity with high-level structure of the data sets used.	Minimal EDA, covering only standard summary statistics, and providing limited insight into data patterns or irregularities. EDA fails to demonstrate familiarity with even the most basic properties of the data sets being analyzed.
Code Quality	Code is (near) flawless. Intent is clear throughout and all code is efficient, clear, and fully idiomatic. Code passes all styler and lintr type analyses without issue.	Comments give context and structure of the analysis, not simply defining functions used in a particular line. Intent is clear throughout, but code can be minorly improved in certain sections.	Code has well-chosen variable names and basic comments. Intent is generally clear, though some sections may be messy and code may have serious clarity or efficiency issues.	Code executes properly, but is difficult to read. Intent is generally clear and code is messy or inefficient.	Code fails to execute properly.
Data Preparation	Data import is fully-automated and efficient, taking care to only download from web-sources if not available locally. All data cleaning steps are fully-automated and robustly implemented, yielding a clean data set that can be widely used.	Data is imported and prepared effectively, in an automated fashion with minimal hard-coding of URLs and file paths. Data cleaning is fully-automated and sufficient to address all issues relevant to the analysis at plan.	Data is imported and prepared effectively, though source and destination file names are hard-coded. Data cleaning is rather manual and hard-codes most transformations.	Data is imported in a manner likely to have errors. Data cleaning is insufficient and fails to address clear problems.	Data is hard-coded and not imported from an external source.
Analysis and Findings	Analysis demonstrates uncommon insight and quality, providing unexpected and subtle insights.	Analysis is clear and convincing, leaving essentially no doubts about correctness.	Analysis clearly appears to be correct and passes the “sniff test” for all findings, but a detailed review notes some questions remain unanswered.	Analysis is not clearly flawed at any point and is likely to be within the right order of magnitude for all findings.	Analysis is clearly incorrect in at least one major finding, reporting clearly implausible results that are likely off by an order of magnitude or more.

Note that the “Excellent” category for most elements applies only to truly exceptional “above-and-beyond” work. Most student submissions will likely fall in the “Good” to “Great” range.

At this point, you are responsible for (elementary) data visualization and will be graded on that aspect of the rubric, as well as the sections on which you were assessed in the previous mini-project.

Note that, because I am still providing code to download the data, load it into R, and prepare it for analysis, all reports submitted using my code will receive an automatic 10/10 for the ‘Data Preparation’ element of the rubric. Additionally, reports completing all tasks described under Data Integration and Exploration below should receive a 10/10 for the ‘Exploratory Data Analysis’ rubric element.

Taken together, you are only really responsible for these portions of the rubric in this assignment:

• Written Communication
• Project Skeleton
• Tables & Document Presentation
• Data Visualization
• Code Quality
• Analysis and Findings

Reports completing all key steps outlined below essentially start with 20 free points.

Exploratory Data Analysis;Data Preparation

For this mini-project, no more than 4 total points of extra credit can be be awarded. Opportunities for extra credit exist for students who go above and beyond the instructor-provided scaffolding. Specific opportunities for extra credit can be found below.

Students pursuing careers in data analytics are strongly encouraged to go beyond the strict ambit of the mini-projects to

1. further refine their skills;
2. learn additional techniques that can be used in the final course project; and
3. develop a more impressive professional portfolio.

Because students are encouraged to use STA 9750 mini-projects as the basis for a professional portfolio, the basic skeleton of each project will be released under a fairly permissive usage license. Take advantage of it!

Submission Instructions

After completing the analysis, write up your findings, showing all of your code, using a dynamic quarto document and post it to your course repository. The qmd file should be named mp02.qmd (lower case!) so the rendered document can be found at docs/mp02.html in the student’s repository and will be served at the URL:²

https://YOUR_GITHUB_ID.github.io/STA9750-2026-FALL/mp02.html

You can use the helper function mp_start available at in the Course Helper Functions to create a file with the appropriate name and some meta-data already included. Do so by running the following command at the R Console:

source("https://michael-weylandt.com/STA9750/load_helpers.R"); mp_start(N=02)

After completing this mini-project, upload your rendered output and necessary ancillary files to GitHub to make sure your site works. The mp_submission_ready function in the Course Helper Functions can perform some of these checks automatically. You can run this function by running the following commands at the R Console:

source("https://michael-weylandt.com/STA9750/load_helpers.R"); mp_submission_ready(N=02)

Once you confirm this website works (substituting YOUR_GITHUB_ID for the actual GitHub username provided to the professor in MP#00 of course), open a GitHub issue on the instructor’s repository to submit your completed work.

The easiest way to do so is by use of the mp_submission_create function in the Course Helper Functions, which can be used by running the following command at the R Console:

source("https://michael-weylandt.com/STA9750/load_helpers.R"); mp_submission_create(N=02)

Alternatively, if you wish to submit manually, open a new issue at

https://github.com/michaelweylandt/STA9750-2026-FALL/issues/new .

Title the issue STA 9750 YOUR_GITHUB_ID MiniProject #02 and fill in the following text for the issue:

Hi @michaelweylandt!

I've uploaded my work for MiniProject #**02** - check it out!

<https://YOUR_GITHUB_ID.github.io/STA9750-2026-FALL/mp02.html>

At various points before and after the submission deadline, the instructor will run some automated checks to ensure your submission has all necessary components. Please respond to any issues raised in a timely fashion as failing to address them may lead to a lower set of scores when graded.

Additionally, a PDF export of this report should be submitted on Brightspace. To create a PDF from the uploaded report, simply use your browser’s ‘Print to PDF’ functionality.

NB: The analysis outline below specifies key tasks you need to perform within your write up. Your peer evaluators will check that you complete these. You are encouraged to do extra analysis, but the bolded Tasks are mandatory.

NB: Your final submission should look like a report, not simply a list of facts answering questions. Add introductions, conclusions, and your own commentary. You should be practicing both raw coding skills and written communication in all mini-projects. There is little value in data points stated without context or motivation.

Mini-Project #02: Identifying Environmentally Responsible US Public Transit Systems

Data Acquisition

Data Acquisition - State Electricity Profiles

For purposes of this analysis, we will assume that all electricity generation in a state is fungible. That is, we will assign the average generation emissions to all electric transit in a state, even if the transit authority officially has a “green supply” agreement in place.

The following code will parse the EIA SEP summary pages and create a “tidy” table for your use:

library(tidyverse)
library(stringr)
library(httr2)
library(rvest)

get_eia_sep <- function(state, abbr){
    library(tidyverse)
    library(stringr)
    library(httr2)
    library(rvest)
    library(fs)
  
    state_formatted <- str_to_lower(state) |> str_replace_all("\\s", "")
    
    dir_name <- fs::path("data", "mp02")
    file_name <- fs::path(dir_name, state_formatted)
    
    fs::dir_create(dir_name, recurse=TRUE)
    
    if(!fs::file_exists(file_name)){
        BASE_URL <- "https://www.eia.gov"
        RESPONSE <- request(BASE_URL) |> 
          req_url_path("electricity", 
                       "state", 
                       state_formatted) |>
          req_headers(
            `User-Agent` = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:149.0) Gecko/20100101 Firefox/149.0"
            ) |>   
          req_retry(
                max_tries = 4, 
          ) |>
          req_perform(file_name)
    
        resp_check_status(RESPONSE)
    }
    
    TABLE <- read_html(file_name) |> 
        html_element("table") |> 
        html_table() |>
        mutate(Item = str_to_lower(Item))
    
    if("U.S. rank" %in% colnames(TABLE)){
        TABLE <- TABLE |> rename(Rank = `U.S. rank`)
    }
    
    CO2_MWh <- TABLE |> 
        filter(Item == "carbon dioxide (lbs/mwh)") |>
        pull(Value) |> 
        str_replace_all(",", "") |>
        as.numeric()
    
    PRIMARY <- TABLE |> 
        filter(Item == "primary energy source") |> 
        pull(Rank)
    
    RATE <- TABLE |>
        filter(Item == "average retail price (cents/kwh)") |>
        pull(Value) |>
        as.numeric()
    
    GENERATION_MWh <- TABLE |>
        filter(Item == "net generation (megawatthours)") |>
        pull(Value) |>
        str_replace_all(",", "") |>
        as.numeric()
    
    data.frame(CO2_MWh               = CO2_MWh, 
               primary_source        = PRIMARY,
               electricity_price_MWh = RATE * 10, # / 100 cents to dollars &
               # * 1000 kWh to MWH 
               generation_MWh        = GENERATION_MWh, 
               state                 = state, 
               abbreviation          = abbr
    )
}

EIA_SEP_REPORT <- map2(state.name, state.abb, get_eia_sep) |> list_rbind()

This code should run without issues, but you might need to run it more than once, ideally with a small time break in between to avoid data throttling on the server side. Once you have downloaded this data once, a copy will be saved locally so you do not need to repeatedly connect to the EIA data pages.

TipTask 1a: Data Acquisition - State Electricity Profiles

Using the code above, acquire the relevant data. Copy the code into your Quarto document and make sure it runs successfully. After doing so, consider which types of joins and filters will be appropriate for this data.

CautionDo Not git add Data Files

Make sure that git is set to ignore data files, such as the one created above. Check the git pane in RStudio and make sure that the files created in data/mp02 do not appear. (If you set up your .gitignore file correctly in MP#00, it should already be ignored.) If it is appearing, you may need to edit your .gitignore file.

Removing a large data file from git is possible, but difficult. Don’t get into a bad state!

This produces the following table:

Pounds of CO2 Emitted Per MWh of Electricity Produced	Primary Source of Electricity Generation	Average Retail Price for 1000 kWh	Total Generation Capacity (MWh)	State
823	Natural gas	$97.90	566,502,688	Texas (TX)
765	Natural gas	$125.30	266,119,236	Florida (FL)
659	Natural gas	$125.10	241,498,648	Pennsylvania (PA)
407	Natural gas	$270.40	214,191,383	California (CA)
510	Nuclear	$122.10	185,012,935	Illinois (IL)
747	Natural gas	$119.00	142,921,216	Alabama (AL)
1,005	Natural gas	$112.90	142,746,706	Ohio (OH)
672	Natural gas	$114.00	139,805,428	Georgia (GA)
670	Natural gas	$116.50	134,965,396	North Carolina (NC)
537	Natural gas	$196.60	129,015,493	New York (NY)
957	Natural gas	$141.60	124,200,090	Michigan (MI)
634	Natural gas	$127.40	116,026,511	Arizona (AZ)
631	Natural gas	$106.20	102,698,615	Virginia (VA)
249	Hydroelectric	$101.30	102,397,711	Washington (WA)
589	Nuclear	$109.00	100,485,416	South Carolina (SC)
927	Natural gas	$88.00	99,622,835	Louisiana (LA)
1,393	Coal	$113.80	97,012,880	Indiana (IN)
673	Natural gas	$90.90	94,069,145	Oklahoma (OK)
794	Natural gas	$109.30	77,336,712	Mississippi (MS)
804	Nuclear	$109.00	76,166,766	Tennessee (TN)
699	Wind	$93.40	70,464,148	Iowa (IA)
1,744	Coal	$100.70	66,950,848	Kentucky (KY)
1,456	Coal	$110.60	66,875,710	Missouri (MO)
1,090	Natural gas	$127.20	65,276,344	Wisconsin (WI)
352	Hydroelectric	$111.10	64,661,249	Oregon (OR)
960	Natural gas	$95.90	61,542,484	Arkansas (AR)
507	Natural gas	$162.90	60,175,245	New Jersey (NJ)
958	Natural gas	$120.70	58,798,395	Colorado (CO)
766	Natural gas	$123.50	58,595,222	Minnesota (MN)
680	Wind	$112.10	57,696,155	Kansas (KS)
1,912	Coal	$110.50	50,594,818	West Virginia (WV)
631	Natural gas	$114.70	45,528,427	Nevada (NV)
541	Natural gas	$243.70	44,761,412	Connecticut (CT)
1,424	Coal	$79.30	42,557,243	North Dakota (ND)
1,724	Coal	$91.40	40,709,150	Wyoming (WY)
743	Wind	$91.80	39,697,294	New Mexico (NM)
1,082	Coal	$90.70	37,228,950	Nebraska (NE)
604	Nuclear	$150.40	35,424,816	Maryland (MD)
1,322	Coal	$99.70	35,133,906	Utah (UT)
969	Coal	$108.30	26,872,883	Montana (MT)
936	Natural gas	$239.40	20,914,890	Massachusetts (MA)
318	Wind	$108.70	20,871,994	South Dakota (SD)
382	Hydroelectric	$95.10	20,035,438	Idaho (ID)
280	Nuclear	$206.10	16,942,479	New Hampshire (NH)
487	Natural gas	$196.60	14,655,178	Maine (ME)
828	Natural gas	$241.50	9,777,026	Rhode Island (RI)
1,426	Petroleum	$380.00	9,160,979	Hawaii (HI)
1,196	Natural gas	$221.70	6,690,506	Alaska (AK)
1,126	Natural gas	$135.60	4,799,212	Delaware (DE)
8	Hydroelectric	$184.10	2,258,891	Vermont (VT)

Here, we have collected the effective emissions per MWh[^mwh] as well as price and total state-wide generation capacity. We will use this data to compare the emissions of different transit modalities. We next turn to the National Transit Database to get information on various public transit authorities. B

Data Acquisition - Energy Consumption

Next, we download the 2025 Annual Database Energy Consumption report. This report is shared as a Excel spreadsheet (.xlsx filetype), so we use the readxl package to import it to R.

load_ntd_energy <- function(){
  library(readxl)
  library(httr2)
  library(tidyverse)
  library(fs)
  
  # Create 'data/mp02' directory if not already present
  DATA_DIR <- fs::path("data", "mp02")
  fs::dir_create(DATA_DIR, recurse=TRUE)

  NTD_ENERGY_FILE <- fs::path(DATA_DIR, "2025_ntd_energy.xlsx")

  if(!fs::file_exists(NTD_ENERGY_FILE)){
    library(httr2)
  
    request("https://www.transit.dot.gov/") |>
      req_url_path("sites",
                   "fta.dot.gov",
                   "files",
                   "2026-09",
                   "2025 Energy Consumption_260806.xlsx") |> 
      req_headers(
        `User-Agent` = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:149.0) Gecko/20100101 Firefox/149.0") |> 
      req_perform(NTD_ENERGY_FILE)
  }
  
  if(file.info(NTD_ENERGY_FILE)$size < 1e4){
        cat("I was unable to download the NTD Energy File. Please try again.\n")
        stop("Download failed. Please delete the corrupted file by running `unlink('data/mp02/2025_ntd_energy.xlsx')` and try again.")
    }

  to_numeric_fill_0 <- function(x){
    x <- if_else(x == "-", NA, x)
    replace_na(as.numeric(x), 0)
  }

  read_excel(NTD_ENERGY_FILE) |> 
    select(-c(`Reporter Type`, 
              `Reporting Module`, 
              `Gallons of Other Fuel`, 
              `Other Fuel Description`)) |>
    mutate(across(-c(`Agency Name`, 
                     `Mode`,
                     `TOS`), 
                  to_numeric_fill_0)) |>
    group_by(`NTD ID`, `Mode`, `Agency Name`) |>
    summarize(across(where(is.numeric), sum), 
              .groups = "keep") |>
    mutate(ENERGY = sum(c_across(c(where(is.numeric))))) |>
    filter(ENERGY > 0) |>
    select(-ENERGY) |>
    ungroup()
}

NTD_ENERGY <- load_ntd_energy()

TipTask 1b: Data Acquisition - NTD Energy Usage

Using the code above, acquire the relevant data. Copy the code into your Quarto document and make sure it runs successfully. After doing so, consider which types of joins and filters will be appropriate for this data.

CautionDo Not git add Data Files

Make sure that git is set to ignore data files, such as the one created above. Check the git pane in RStudio and make sure that the files created in data/mp02 do not appear. (If you set up your .gitignore file correctly in MP#00, it should already be ignored.) If it is appearing, you may need to edit your .gitignore file.

Removing a large data file from git is possible, but difficult. Don’t get into a bad state!

The resulting table is almost ready for us to work with:

NTD ID	Mode	Agency Name	Gallons of Bio-Diesel	Bio-Diesel Description	Gallons of Bunker Fuel	Gallons of Compressed Natural Gas	Gallons of Diesel Fuel	Kilowatt hours of Electric Battery	Kilowatt hours of Electric Propulsion	Gallons of Ethanol	Gallons of Gasoline	Kilograms of Hydrogen	Gallons of Kerosene	Gallons of Liquified Nat Gas	Gallons of Liquified Petroleum Gas
90087	MB	City of Santa Maria	0	0	0	0	126763	168794	0	0	16014	0	0	0	0
40009	MB	City of Fayetteville	0	0	0	0	257545	1899	0	0	3677	0	0	0	336
40036	MB	City of Tallahassee	0	0	0	100702	324204	655942	0	0	0	0	0	0	0
90015	LR	City and County of San Francisco	0	0	0	0	0	0	62172280	0	0	0	0	0	0
40034	DR	County of Miami-Dade	0	0	0	0	0	7063	0	0	984514	0	0	0	0
60017	SR	Central Oklahoma Transportation and Parking Authority	0	0	0	0	0	0	1903376	0	0	0	0	0	0
40029	MB	Broward County Board of County Commissioners	0	0	0	0	4334772	0	0	0	26259	0	0	0	61295
30051	MB	Montgomery County, Maryland	0	0	0	1318861	2436107	4	0	0	0	0	0	0	0
50025	MB	Duluth Transit Authority	0	0	0	0	428351	148991	0	0	0	0	0	0	0
90087	DR	City of Santa Maria	0	0	0	0	0	17884	0	0	18671	0	0	0	0

Here, the first 3 columns (NTD ID, Mode, and Agency Name) identify the operating agency and the relevant mode of transportation while the remaining columns denote how much of each type of fuel that agency used during 2023. The non-electric sources are all quoted in gallons, while Electric Battery and Electric Propulsion are quoted in kWh used.

The Mode column is important: it is used to distinguish multiple transport types operated the same agency, e.g. MTA subways and buses in NYC. The two letter codes used by the NTD, however, are not immediately interpretable.

Data Acquisition - Transit Service Provided

Next, we download the 2025 Service by Agency report, from which we will extract characteristics of typical passenger trips on each transit service.

load_ntd_service <- function(){
  library(httr2)
  library(tidyverse)
  library(fs)
  
  # Create 'data/mp02' directory if not already present
  DATA_DIR <- fs::path("data", "mp02")
  fs::dir_create(DATA_DIR, recurse=TRUE)

  NTD_SERVICE_FILE <- fs::path(DATA_DIR, "2025_ntd_service.csv")

  if(!fs::file_exists(NTD_SERVICE_FILE)){
    library(httr2)
  
    request("https://data.transportation.gov/resource/4fir-qbim.csv") |>
      req_url_query(`$query`=paste(
        "SELECT  `_5_digit_ntd_id` AS `NTD_ID`", 
        "`max_agency` AS `AGENCY`",
        "`max_reporter_type` AS `reporter_type`",
        "`max_organization_type` AS `organization_type`",
        "`max_city` AS `city`",
        "`max_state` AS `state`",
        "`mode`",
        "`type_of_service`",
        "`max_primary_uza_code` as `uza_code`",
        "`max_primary_uza_name` as `uza_name`",
        "`max_primary_uza_area_sq_miles` as `uza_sq_miles`",
        "`max_primary_uza_population` as `uza_population`",
        "`max_service_area_sq_miles` as `service_area_sq_miles`",
        "`max_service_area_population` as `service_area_population`",
        "`questionable_record` as `flagged_likely_inaccurate`",
        "`sum_unlinked_passenger_trips_upt` as `total_upt`",
        "`sum_passenger_miles` WHERE caseless_one_of(`report_year`, '2025') limit 50000", 
        sep=",")) |>
      req_headers(
        `User-Agent` = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:149.0) Gecko/20100101 Firefox/149.0") |> 
      req_perform(NTD_SERVICE_FILE)
  }
  
  if(file.info(NTD_SERVICE_FILE)$size < 1e4){
        cat("I was unable to download the NTD Service File. Please try again.\n")
        stop("Download failed. Please delete the corrupted file by running `unlink('data/mp02/2025_ntd_service.xlsx')` and try again.")
    }

  read_csv(NTD_SERVICE_FILE) |>
    mutate(flagged_likely_inaccurate = !is.na(flagged_likely_inaccurate))
}

NTD_SERVICE <- load_ntd_service()

TipTask 1c: Data Acquisition - Service Provided

Using the code above, acquire the relevant transit service data. Copy the code intoyour Quarto document and make sure it runs successfully.

CautionDo Not git add Data Files

Make sure that git is set to ignore data files, such as the one created above. Check the git pane in RStudio and make sure that the files created in data/mp02 do not appear. (If you set up your .gitignore file correctly in MP#00, it should already be ignored.) If it is appearing, you may need to edit your .gitignore file.

Removing a large data file from git is possible, but difficult. Don’t get into a bad state!

Here UPT is the number of total Unlinked Passenger Trips during 2025; that is, the number of distinct (non-transfer) trips taken. E.g., if you transfer from the 7 to the 6 to get to Baruch. You ride two segments, but only take 1 UPT to get to Baruch (2 total UPT if you go home the same way).

Data Cleaning and Preparation

Use the functions above to create the 3 data frames you will use for this assignment. After each data frame has been read into R, use the baseline type and distribution checks described in the previous assignment to confirm that you understand the types of all columns in the various tables and that they will be suitable for your analyses. You do not need to actually include the output of dplyr::glimpse() or skimr::skim() in your final submission, but it is good to get in the habit of using them regularly.

As part of this analysis, review the various tables and begin to map out how they fit together and will be used for your analysis; see below for one possible way to visualize and explore data of this format.

The above data is relatively standardized and easy to work with, but the Mode columns in the NTD files are not easy to work with and should be recoded into more easily intepreted values.

TipTask 2: Recoding the Mode column

First, find the unique Mode codes in our data using the distinct() function. Next, examine the NTD website and find the interpretations of these codes. Complete the following snippet to recode the Mode column.

## This code needs to be completed
NTD_ENERGY <- NTD_ENERGY |>
    mutate(Mode=recode_values(Mode, 
        "HR" ~ "Heavy Rail", 
        ...
        ...
        default="Unknown"))

Alternative approach, more advanced, not required: Modify the load_ntd_service() function above to additionally query the mode_name column. This column will give you the ‘expanded’ names for each two-letter mode code and can be used, along with clever use of joins, to recode the NTD_ENERGY table as well.

Data Integration and Initial Exploration

CautionInstructor Adjustment

Because I am releasing this a bit behind schedule, students are only required to answer 8 of the following questions. You must have at least 2 inline answers, 2 table answers, and 2 plots among the questions you select. (You are of course welcome to do all of these questions and more!)

EDA - EIA State Electricity Profiles

TipTask 3a: Initial Analysis of SEP Data

Answer the following questions using the EIA_SEP_REPORT data. For each question, answer in the specified format (inline, table, or with a graph)

1. Which 5 states have the most expensive retail electricity? (Graph)
2. Which 5 states have the ‘dirtiest’ electricity mix? (Table)
3. On average, how many pounds of CO2 are emitted per MWh of electricity produced in the US? (Note that you will need to use a suitably weighted average here.) (Inline answer)
4. What are the three rarest primary energy sources in the US? What is the associated cost of electricity associated with each source and in what states are they used? (Table answer)
5. My home state, Texas, has a reputation as being the home of “dirty fossil fuels” while NY has a reputation as a leader in clean energy. How many times cleaner is NY’s energy mix than that of Texas? (Inline answer)

EDA - NTD Energy Usage

TipTask 3b: Explore NTD Service Data

Answer the following questions using the NTD_ENERGY data. For each question, answer in the specified format (inline, table, or with a graph)

1. Which five transit agencies used the most gallons of fuel (all sources) and how did it breakdown by type? (Table)
2. Across all US public transit, visualize the amounts of each type of fuel used (Graph)
3. What is the least commonly used fuel source for US public transit? (Inline)

EDA - NTD Service

TipTask 3c: Explore NTD Service Data

Answer the following questions using the NTD_SERVICE data. For each question, answer in the specified format (inline, table, or with a graph)

1. Which five transit services have the most UPT annually? (Graph)

2. What is the average trip length of a trip on MTA NYC? (Inline)

3. Which transit service in NYC has the longest average trip length? (Inline)

   Hint: You need to be careful with the City column here. Certain MTA services are officially located in “New York City” while others are located in Brooklyn.)

4. Which five states has the fewest total miles travelled by public transit? (Graph)

5. Are all states represented in this data? If no, which ones are missing? The state.name and state.abb objects built into R may be useful here, as well as the anti_join() function.³ (Inline)

Data Integration

Before proceeding, you will want to consider how you will want to join these data files together. In this case, there are sometimes several possible ways to join together a pair of tables. The following mental checklist will help you maximize the probability that you’re using the correct join for your analyses.⁴

NoteSelecting a Join

1. What two pieces of information am I looking to connect? Answer(s): two columns from different tables. (If the columns are already in the same table, you don’t need a join.)

2. What is the common feature(s) I can use to join together these two tables? Answer(s): one or more columns that have corresponding values in each table. It is not required for the columns to have the same name or the exact same set of values in both tables. (If there is not a natural pair of combining columns, you might need to introduce a third table to your analyis.)

3. Join type:

   1. Do I want to guarantee use all of the rows from Table 1, filling in with missing values if no corresponding row is found in Table 2?
   2. Do I want to guarantee use all of the rows from Table 2, filling in with missing values if no corresponding row is found in Table 1?

   If “yes/yes”, you probably want a full_join; if “yes/no”, you probably want a left_join; if “no/yes”, you probably want a right_join; if “no/no”, you probably want an inner_join.

4. Construct a potential join:

   JOIN_TYPE(TABLE_1_NAME, 
             TABLE_2_NAME, 
             join_by(TABLE_1_KEY_COLUMN == TABLE_2_KEY_COLUMN))

   Here, replace all values with the answers from your questions above. (We are not considering non-equality joins here.)

5. Run your join and make sure you get no “many-to-many” or “many-to-one” warnings.

6. Embed your join into your full analysis

For example, if we want to answer the question “Who played guitar in which bands?” using the band_members and band_instruments tables in the dplyr package, the join-design flowchart would go something like this:

1. I want to connect the plays column from band_instruments to the band column of band_members.

2. The common feature (“key”) connecting the band_instruments and band_members table is the name column, which has the same name and consistent values in both tables.

3. 1. I am fine dropping rows from the band_instruments table if I am unable to determine what band someone was a part of.
   2. I am fine dropping rows from the band_members table if I am unable to determine what instrument someone played.

   These suggest I want to use an inner_join.

4. My candidate join is:

   library(dplyr)
   inner_join(band_members, 
              band_instruments, 
              join_by(name == name))

   # A tibble: 2 × 3
     name  band    plays 
     <chr> <chr>   <chr> 
   1 John  Beatles guitar
   2 Paul  Beatles bass  

5. This runs without any warnings or other danger signs, so I’m good to proceed.

6. I can use this join to find one guitarist in my data set with a known band:

   library(dplyr)
   inner_join(band_members, 
              band_instruments, 
              join_by(name == name)) |>
       filter(plays == "guitar") 

   # A tibble: 1 × 3
     name  band    plays 
     <chr> <chr>   <chr> 
   1 John  Beatles guitar

   so the one row in our result is John (Lennon) playing guitar in the Beatles. Sadly, our datasets were incomplete and we lost track of Keith Richards, guitarist in the Rolling Stones.

TipTask 4: Practice Joining Data Files

Work through the join-design steps for the following questions based on the data files loaded above.

1. How many UPTs were recorded in states where the primary energy source was nuclear power?

2. In what metro area were the most gallons of bio-diesel used by public transit agencies?

NB: You are not required to actually include your answers to these questions in your submission. You will demonstrate knowledge of proper joining strategies in next section (Data Analysis) and your final deliverable. This is just a set of ‘warmups’ to help you orient yourself.

Data Analysis

Calculate Total Emissions

Compute the total emissions associated with each Agency + Mode pair.

TipTask 5: Calculate Emissions

You will need to join the three tables (NTD_SERVICE, NTD_ENERGY, and EIA_SEP_REPORT) together. You should create a table with the following format:

• Each row should be an Agency + Mode Pair
• Each row should include the state in which the Agency is located (per the NTD_SERVICE table)
• Each row should include all fuel sources from the NTD_ENERGY table
• Each row should include the EIA estimated CO2 emissions per MWh from the EIA_SEP_REPORT table.

Once this table is created, use a (long-ish) arithmetic formula within a mutate to compute the total emissions for that row. Note that you will need additional data to convert combustion fuels to CO2 emissions; these conversions can be found on the EIA website. (You may hard code these values in your submission. Note that the fuel names used by the EIA do not exactly match those reported by the NTD; you will need to align these manually to best effort.)

Relate Total Emissions and Passenger Service

Now that you have computed the total emissions for each Agency + Mode pair, it is time to normalize these by the actual size of the transit agency. E.g., the NYC Subway almost certainly has higher total emissions than local bus service in Ithaca, NY, but it also serves many more riders, so the per capita or per transit emissions may be lower.

TipTask 6: Normalize Emissions to Transit Usage

Using the total emissions calculated above, compute the emissions:

1. Per UPT; and
2. Per passenger mile

Which agencies are most efficient on these bases? You may want to filter out extremely small agencies and to create “small”, “medium”, and “large” agencies so that you can give multiple awards.

Note that transit usage is reported at the Agency level, so you may need to sum emissions across multiple modes.

Idenitify ‘Greenest’ Transit Agencies

CautionInstructor Adjustment

Because I am releasing this a bit behind schedule, students are only required to construct 2 of the following awards. At least one should be of your own creation (either a “best of” or “wort of” award). You are of course welcome to develop all four award metrics and to identify winners.

Your final press release should still have at least two publication quality visualizations (cf. Task 8 below).

With the above analysis, determine the winners of the GTA IV awards:

1. Greenest Transit Agency
2. Most Emissions Avoided
3. A third award of your creation
4. A “Worst Of” award of your creation

The Greenest Transit Agency award can be determined using your analysis above.

For the “Most Emissions Avoided” award, you should first compute the total emissions if all transit usage were replaced with driving individual vehicles. Then use US CAFE standards to convert miles to gallons of motor fuel used and convert motor fuel to total emissions as per above. Compare this to the actual emissions associated with that agency to determine emissions avoided.

For the third award, determine an additional metric on which a transit agency can be considered to be highly green (e.g., highest percentage of electrification) and then determine the appropriate winner.

Finally, for the fourth award, “name and shame” an agency which does not have particularly ‘green’ operations. As with the third award, you can determine a metric you think is best.

Per discussion above, you may choose to break these down into multiple categories (or honorable mention awards) to recognize smaller agencies.

TipTask 7: Determine Award Winners

Design and implement metrics for the four GTA IV Green Transit Awards and determine winners.

Deliverable: Transit Award “Press Release”

After completing your analysis, prepare a “press release” announcing the winners in each category. For each award given, your press release should include at a minimum:

1. A short description of the way in which the relevant metric was calculated.
2. The winning agency and/or transit mode and the value of their metric.
3. A ‘reference’ value that makes the winner look particularly impressive, e.g. how did the median agency do on that same metric?

TipTask 8: Final Deliverable

Write a press release announcing your winners, as described above.

For at least two of the four awards, create a suitable visualization demonstrating the ‘green-ness’ of the award winners, both in absolute terms and relative to other agencies and/or private modes of transporation.

These should be included in your press release as media supplements that can be used by, e.g., a local news report in their nightly broadcast. As such, these graphics should:

1. Have professional-quality (“publication ready”) formatting;⁵
2. Have low-clutter / high-information density;
3. Be self-contained and accessible to a general reader without significant additional description retired.
4. Include citations to original data sources used

AI Usage Statement

At the end of your report, you must include a description of the extent to which you used Generative AI tools to complete the mini-project. This should be a one paragraph section clearly deliniated using a collapsable Quarto “Callout Note”. Failure to include an AI disclosure will result in an automatic 25% penalty.

E.g.,

NoteAI Usage Statement

No Generative AI tools were used to complete this mini-project.

or

NoteAI Usage Statement

GitHub Co-Pilot Pro was used via RStudio integration while completing this project. No other generative AI tools were used.

or

NoteAI Usage Statement

ChatGPT was used to help write the code in this project, but all non-code text was generated without the use of any Generative AI tools. Additionally, ChatGPT was used to provide additional background information on the topic and to brainstorm ideas for the final open-ended prompt.

Recall that Generative AI may not be used to write or edit any non-code text in this course.

These blocks should be created using the following syntax:

::: {.callout-note title="AI Usage Statement" collapse="true"}

Your text goes here. 

:::

Make sure to use this specific type of callout (.callout-note), title, and collapse="true" setting.

Please contact the instructor if you have any questions about appropriate AI usage in this course.

Extra Credit Opportunities

There are optional Extra Credit Opportunities where extra points can be awarded for specific additional tasks in this mini-project. The amount of the extra credit is typically not proportional to the work required to complete these tasks, but I provide these for students who want to dive deeper into this project and develop additional data analysis skills not covered in the main part of this mini-project.

For this mini-project, no more than 4 total points of extra credit may be awarded. Even with extra credit, your grade on this mini-project cannot exceed 80 points total.

Extra Credit Opportunity #01: Relationship Diagram

Possible Extra Credit: Up to 2 Points

After completing the data import steps, examine the structure of your data sets and create a suitable data relationship diagram. A data relationship diagram is a visual representation of:

1. The data tables available for an analysis
2. The columns (names and types) present in each table
3. The possible join structures that can be used to combine these tables.

Generate a suitable visualization and include it in your final submission. Tools like https://dbdiagram.io or Canva can be useful for creating this visualization, but you may ultimately use whatever tool is most convenient for you (even a photo of an on-paper sketch). In determining the amount of extra credit awarded, peer evaluators will look at both the aesthetics of the visualization and the accuracy of the depicted relationships.

Note that this data is not ‘fully normalized’ so there may be cross-table relationships between one or more columns, particularly when connecting a ‘wide’ and ‘long’ data set. Note also that some of the join keys are a bit more complex than typical of a standard database set up so you may wish to annotate when a relationship more complex than simple quality is required.

Extra Credit Opportunity #02: Hilarity Ensues

Possible Extra Credit: Up to 2 Points

Peer evaluators are encouraged to give up to 2points of extra credit for

1. Press release writing that is particularly funny, punny, or otherwise enjoyable to read; and
2. Award criteria that are particularly creative and insight-generating. When awarding extra credit, peer evaluators should be sure to assess the quality of the insight generated by these additional awards: finding that, e.g., the NYC subway is highly efficient on two different but highly correlated metrics is not a remarkable insight.

---

This work ©2026 by Michael Weylandt is licensed under a Creative Commons BY-NC-SA 4.0 license.

Footnotes

1. This the level of “ChatGPT-level” prose, without obvious flaws but lacking the style and elegance associated with true quality writing.

2. Throughout this section, replace YOUR_GITHUB_ID with your GitHub ID from Mini-Project #00. Note that the automated course infrastructure will be looking for precise formatting, so follow these instructions closely.

3. You do not need to use an anti_join() here. You can achieve the same effect with clever use of the %in% operator.

4. This process isn’t fool-proof, but it should cover most use-cases.

5. Make sure to set fonts and figure dimensions large enough to be legible on a large screen.