Landfill Environmental Justice Pipeline

Unify three EPA datasets to map landfill locations with community demographics.

Introduction

30 Second Summary

Landfills don't end up in random locations. Proving which communities bear the burden means connecting federal datasets that were never designed to talk to each other.

In this project, you will build a Python data pipeline in Google Colab that takes three disconnected EPA databases and joins them into one analysis-ready file. Each row pairs a U.S. landfill with the demographics of the community living within 5 miles of it.

What You'll Build

Run your finished notebook top to bottom and hand someone a single file that answers the question: who lives near America's landfills?

By the end of this project, you'll have:

  • A unified dataset where you can look up any U.S. landfill and see the racial makeup, income levels, education, and age distribution of the surrounding community.
  • A clear breakdown of how each landfill was matched: by official ID through EPA's Facility Registry Service or by GPS coordinate proximity, with a count of records that could not be matched at all.
  • An analysis-ready CSV structured for direct import into Tableau to power an environmental justice dashboard.
  • Secret Mission: an optional challenge to push your data analysis skills further.

Are there any prerequisites?

This project assumes basic familiarity with Python, SQL, and data analysis concepts. You also need a Google account to use Google Colab, which is free and runs entirely in your browser.

Before We Start

Before any code runs, let's lock in what you're about to build and why it matters.

Set Up Your Colab Workspace and Load the Data

Right now, three EPA datasets sit on your desktop in different file formats with no connection between them. Before any analysis can happen, you need them all in a single workspace where Python and SQL can reach them.

In this step, you will create a Google Colab notebook, connect it to your Google Drive, and load each dataset into pandas so you can start working with the data.

In this step, get ready to:
  • Create a Google Colab notebook and connect it to Google Drive.
  • Upload the four EPA dataset files to your Drive.
  • Load each dataset into a pandas DataFrame and confirm the data looks right.
Create a new Google Colab notebook and mount Google Drive

Google Colab is a free, browser-based Python notebook environment. Each notebook is made up of code cells that you run one at a time.

Name your notebook

Click on the default title at the top left (it usually says "Untitled") and rename it to something descriptive like Landfill EJ Pipeline.

  • Paste the following code into the first cell and run it by pressing Shift+Enter:
# --- Cell 1: Mount Google Drive and Import Libraries ---
from google.colab import drive
drive.mount('/content/drive')

import pandas as pd
import sqlite3
from math import radians, cos, sin, asin, sqrt

print("Libraries loaded successfully.")

What does this code do?

  • drive.mount('/content/drive') connects your Colab notebook to your Google Drive so the notebook can read files stored there.
  • pandas is the library you will use to read, clean, and manipulate each dataset.
  • sqlite3 is Python's built-in SQL database engine. You will use it in Step 3 to join tables together.
  • The math imports (radians, cos, sin, asin, sqrt) are for a distance calculation you will build in Step 4.

When you run this cell, Colab will ask you to authorize Google Drive access.

  • A pop-up will appear asking you to permit this notebook to access your Google Drive files.
  • Click Allow and grant all the requested permissions.

You should see "Mounted at /content/drive" followed by "Libraries loaded successfully." printed below the cell.

Drive not mounting?

  • Make sure you are signed into the same Google account in both Colab and Google Drive. Colab requires the same account for both.
  • When the permissions pop-up appears, grant all requested permissions. Selecting only some permissions can cause the mount to fail.
  • If you see a browser pop-up blocker warning, allow pop-ups for colab.research.google.com and try running the cell again.
  • Help me mount Google Drive in my Colab notebook.

Now create the project folder where you will store all the data files.

  • Open Google Drive in a new browser tab by going to drive.google.com.
  • Click New in the top left corner, then click New folder.
  • Name the folder EPA_Landfill_Data and click Create.

You should see EPA_Landfill_Data listed in your Google Drive.

Upload your data files to Google Drive

You downloaded three items to your desktop: the LMOP Excel file, the FRS ZIP, and the ECHO ZIP. You need to extract the CSVs from the two ZIP files and then upload everything to your new Drive folder.

  • On your desktop, find the FRS ZIP file and extract it. Locate FRS_PROGRAM_LINKS.csv and FRS_FACILITIES.csv inside the extracted folder.
  • Find the ECHO ZIP file on your desktop and extract it. Locate ECHO_DEMOGRAPHICS.csv inside the extracted folder.
  • In Google Drive, double-click the EPA_Landfill_Data folder to open it.
  • Drag and drop all four files into this folder: the LMOP Excel file, FRS_PROGRAM_LINKS.csv, FRS_FACILITIES.csv, and ECHO_DEMOGRAPHICS.csv.

Why does the upload take a while?

The FRS and ECHO files are large. The FRS CSV files come from a 318 MB ZIP, and the ECHO demographics file comes from a 567 MB ZIP. Uploading to Google Drive may take a few minutes depending on your internet connection.

  • Wait for all uploads to finish. You should see four files listed inside the EPA_Landfill_Data folder with no spinning upload indicators.
Load each dataset into a DataFrame

With the files in Drive and your notebook connected, you can now read each file into a pandas DataFrame. Each dataset goes into its own cell so you can see the output as you go.

  • Switch back to your Colab notebook tab.
  • Create a new code cell by clicking + Code in the top left toolbar, then paste this code and run it:
# --- Cell 2: Define File Paths ---
DATA_PATH = '/content/drive/MyDrive/EPA_Landfill_Data/'

LMOP_FILE = 'Landfill-level data only (September 2024).xlsx'
FRS_LINKS_FILE = 'FRS_PROGRAM_LINKS.csv'
FRS_FACILITIES_FILE = 'FRS_FACILITIES.csv'
ECHO_FILE = 'ECHO_DEMOGRAPHICS.csv'

What does this cell define?

DATA_PATH points to the Google Drive folder you just created. The four file name variables match the exact filenames of your uploaded files.

If your LMOP Excel file has a slightly different name (the download name can vary), update the LMOP_FILE value to match your exact filename.

This cell defines variables only, so it produces no output. The next cell uses these paths to load data.

  • Create a new code cell, paste this code, and run it to load the LMOP landfill data:
# --- Cell 3: Load LMOP Landfill Data ---
lmop_df = pd.read_excel(DATA_PATH + LMOP_FILE)
print(f"LMOP Landfills loaded: {len(lmop_df)} rows, {len(lmop_df.columns)} columns")
print(f"\nColumn names:\n{list(lmop_df.columns)}")
print(f"\nFirst 3 rows:")
print(lmop_df.head(3))

What does this code do?

  • pd.read_excel() reads the LMOP Excel file into a DataFrame called lmop_df.
  • The print statements show you how many rows and columns were loaded, the exact column names in the file, and a preview of the first three rows.
  • LMOP covers more than 2,600 municipal solid waste landfills across the U.S.

You should see a row count, a list of column names, and a preview of the first three rows printed below the cell.

Seeing a FileNotFoundError?

  • Double-check that the file is inside the EPA_Landfill_Data folder in Google Drive, not in the root of My Drive.
  • Make sure the LMOP_FILE variable in Cell 2 matches your downloaded filename exactly, including spaces and parentheses.
  • If Drive was not mounted successfully, scroll up to Cell 1 and re-run it.
  • Help me fix a FileNotFoundError when loading my LMOP data in Colab.
  • Create a new code cell, paste this code, and run it to load the FRS Program Links:
# --- Cell 4: Load FRS Program Links ---
frs_links_df = pd.read_csv(
    DATA_PATH + FRS_LINKS_FILE,
    dtype=str,
    low_memory=False
)
print(f"FRS Program Links loaded: {len(frs_links_df)} rows")
print(f"\nColumn names:\n{list(frs_links_df.columns)}")

Why dtype=str?

The dtype=str parameter tells pandas to read every column as text instead of guessing data types. Without it, pandas would read ID columns like REGISTRY_ID as numbers, which strips leading zeros and breaks joins later.

You should see the row count and column names for FRS Program Links. This is the bridge table that connects landfill IDs to the demographics database.

  • Create a new code cell, paste this code, and run it to load FRS Facilities:
# --- Cell 5: Load FRS Facilities ---
frs_facilities_df = pd.read_csv(
    DATA_PATH + FRS_FACILITIES_FILE,
    dtype=str,
    low_memory=False
)
print(f"FRS Facilities loaded: {len(frs_facilities_df)} rows")
print(f"\nColumn names:\n{list(frs_facilities_df.columns)}")

You should see the row count and column names for FRS Facilities. This table contains GPS coordinates you will use for coordinate matching in Step 4.

  • Before you run the last dataset, take a guess: do you think the ECHO Demographics file will have more or fewer rows than FRS Program Links?
  • Create a new code cell, paste this code, and run it to find out:
# --- Cell 6: Load ECHO Demographics ---
echo_df = pd.read_csv(
    DATA_PATH + ECHO_FILE,
    dtype=str,
    low_memory=False
)
print(f"ECHO Demographics loaded: {len(echo_df)} rows")
print(f"\nColumn names:\n{list(echo_df.columns)}")

You should see the row count and column names for ECHO Demographics. This dataset contains demographic data at three different radii (1, 3, and 5 miles) for each EPA-tracked facility.

CSV cells taking a long time to run?

  • These are large files. FRS Program Links and FRS Facilities can each take 30-60 seconds to load depending on Colab's connection speed to your Drive.
  • If a cell seems stuck for more than two minutes, click Runtime in the top menu, then Restart session, and re-run all cells from the top.
  • My Colab cells are running slowly when loading CSV files from Drive. What can I try?

✔️ Awesome, I've got everything!

Great. Double-check you have saved your notebook.

ⓧ I'd like to double check the full code

Your notebook should have 6 cells in this order. Scroll through and confirm each one is present with its output:

  • Cell 1: Mount Google Drive and import libraries. Output shows "Mounted at /content/drive" and "Libraries loaded successfully."
  • Cell 2: Define file paths. No output (this is expected).
  • Cell 3: Load LMOP landfill data. Output shows row count, column names, and first 3 rows.
  • Cell 4: Load FRS Program Links. Output shows row count and column names.
  • Cell 5: Load FRS Facilities. Output shows row count and column names.
  • Cell 6: Load ECHO Demographics. Output shows row count and column names.

All four datasets are loaded and ready to work with. Next up, you will clean and filter the raw data so it is small enough and consistent enough to join together.

Clean and Filter the Raw Data

Your four datasets are loaded, and you can see their shapes and column names. That is a great start, but these raw files are far too large and inconsistent to join directly.

FRS Program Links has millions of rows covering every EPA program, but you only need the greenhouse gas rows. ECHO Demographics stores three rows per facility (one per radius), but you only need the 5-mile rows. ID columns stored as numbers will silently lose leading zeros, breaking every join before it starts.

In this step, get ready to:
  • Identify and store the LMOP column names you will reference throughout the pipeline.
  • Filter the FRS and ECHO datasets down to only the rows relevant to your analysis.
  • Check for missing values in the columns the join depends on.
Confirm the LMOP column names

When you loaded the LMOP file in Step 1, the printed column names showed you the exact headers in your download. These headers are the keys you will reference in every later cell.

Storing them in variables means you only need to update one place if the names in your file differ slightly from the ones in this project.

  • Scroll up to the output of Cell 3 in your notebook and review the printed column names for lmop_df.
  • Confirm that the columns for GHGRP ID, Latitude, Longitude, Landfill Name, State, Owner/Operator, and Current Landfill Status match the names below.
  • Create a new code cell (Cell 7) and set up the column name variables by pasting the following code:
# --- Cell 7: Set LMOP column name variables ---
# IMPORTANT: After running Cells 3-6, check the printed column names.
# Update these variables to match the exact headers in YOUR LMOP file.
GHGRP_COL = 'GHGRP ID'
LAT_COL = 'Latitude'
LON_COL = 'Longitude'
NAME_COL = 'Landfill Name'
STATE_COL = 'State'
OWNER_COL = 'Owner/Operator'
STATUS_COL = 'Current Landfill Status'

What does this code do?

Each variable stores the exact column header string from the LMOP Excel file. Instead of typing 'GHGRP ID' every time you reference that column, you use GHGRP_COL.

If your downloaded file uses a slightly different header (like 'GHGRP_ID' instead of 'GHGRP ID'), you only need to change it here once.

  • Run Cell 7. No output appears because this cell only sets variables. The next cell uses them to clean the data.

Seeing a NameError later?

If a later cell raises a KeyError on a column name, the header in your LMOP file does not match the variable here. Scroll back to Cell 3's output, copy the exact column name, and update Cell 7.

Help me fix a KeyError on my LMOP column names

Standardize IDs and filter the datasets

The GHGRP ID column in the LMOP file was loaded as a number by default. That means any ID with a leading zero (like 0012345) was silently truncated to 12345. Converting it to a string and cleaning up blank values prevents mismatches later.

  • Create a new code cell (Cell 8) and standardize the LMOP ID and coordinate columns by pasting the following code:
# --- Cell 8: Standardize ID columns ---
lmop_df[GHGRP_COL] = lmop_df[GHGRP_COL].astype(str).str.strip()
lmop_df.loc[lmop_df[GHGRP_COL].isin(['nan', 'None', '']), GHGRP_COL] = None

lmop_df[LAT_COL] = pd.to_numeric(lmop_df[LAT_COL], errors='coerce')
lmop_df[LON_COL] = pd.to_numeric(lmop_df[LON_COL], errors='coerce')

has_ghgrp = lmop_df[GHGRP_COL].notna().sum()
no_ghgrp = lmop_df[GHGRP_COL].isna().sum()
print(f"Landfills WITH GHGRP ID: {has_ghgrp}")
print(f"Landfills WITHOUT GHGRP ID: {no_ghgrp}")

What does this code do?

  • .astype(str).str.strip() converts the GHGRP ID column to a string and removes any whitespace, so IDs with leading zeros stay intact.
  • The .isin(['nan', 'None', '']) line catches all the ways pandas represents missing data after a string conversion and replaces them with Python's None.
  • pd.to_numeric(errors='coerce') converts latitude and longitude to numbers, turning anything non-numeric into NaN instead of crashing.
  • The final four lines count how many landfills have a GHGRP ID vs. how many do not. This split previews the matching challenge you will tackle in Step 4.
  • Run Cell 8. You should see two lines printed showing the count of landfills with and without a GHGRP ID.

Notice that a significant number of landfills have no GHGRP ID at all. Those landfills cannot be matched through the primary ID join. You will rescue them with coordinate matching in Step 4.

Seeing all zeros for both counts?

Make sure Cell 7 ran successfully before Cell 8. Cell 8 references GHGRP_COL, which is defined in Cell 7.

If your GHGRP column name does not match 'GHGRP ID', update Cell 7 with the exact name from your Cell 3 output, then re-run both cells.

Help me debug my GHGRP ID counts

Next, the FRS Program Links file covers every EPA program. You only need the rows for the E-GGRT program (Electronic Greenhouse Gas Reporting Tool), which is the system that assigns the GHGRP IDs your LMOP file references.

  • Create a new code cell (Cell 9) and filter the FRS Program Links by pasting the following code:
# --- Cell 9: Filter FRS Program Links to E-GGRT only ---
frs_links_filtered = frs_links_df[
    frs_links_df['PGM_SYS_ACRNM'] == 'E-GGRT'
].copy()
print(f"FRS Program Links (all programs): {len(frs_links_df)} rows")
print(f"FRS Program Links (E-GGRT only): {len(frs_links_filtered)} rows")

What does this filter do?

The PGM_SYS_ACRNM column identifies which EPA program each row belongs to. Filtering to 'E-GGRT' keeps only the rows that map GHGRP facility IDs to FRS registry IDs.

This filter is what makes the three-table join possible. Each E-GGRT row connects a PGM_SYS_ID (the GHGRP ID from the landfill file) to a REGISTRY_ID (the ID the demographics file uses).

  • Run Cell 9. You should see the original row count (millions) alongside the filtered count (dramatically smaller).

Filtered count is zero?

Check that 'E-GGRT' is spelled exactly as shown, including the hyphen and uppercase letters. The filter is case-sensitive.

Help me figure out why my FRS filter returned zero rows

Now for the ECHO Demographics file. It stores three rows per facility: one each for a 1-mile, 3-mile, and 5-mile radius around the site. You only need the 5-mile radius data, which captures the full community impact zone.

  • Create a new code cell (Cell 10) and filter the ECHO demographics by pasting the following code:
# --- Cell 10: Filter ECHO Demographics to 5-mile radius only ---
echo_filtered = echo_df[echo_df['RADIUS_OF_AREA'] == '5'].copy()
print(f"ECHO Demographics (all radii): {len(echo_df)} rows")
print(f"ECHO Demographics (5-mile only): {len(echo_filtered)} rows")

demo_numeric_cols = [
    'ACS_POPULATION', 'MINORITY_POPULATION', 'LOWINCOME',
    'PERSONS_BELOW_POVERTY_LEVEL', 'WHITE_POPULATION',
    'AFRICAN_AMERICAN_POPULATION', 'HISPANIC_ORIGIN_POPULATION',
    'ASIAN_POP', 'AMERICAN_INDIAN_POPULATION',
    'OTHER_MULTIRACIAL_POPULATION', 'HOUSEHOLDS',
    'HOUSEHOLDS_PUBLIC_ASSISTANCE', 'EDUCATION_BASIS',
    'LESS_THAN_9TH_GRADE_POPULATION', 'GRADES_9_12_POPULATION',
    'HIGH_SCHOOL_DIPLOMA_POPULATION', 'SOME_COLLEGE_POPULATION',
    'BS_BA_OR_MORE_POPULATION', 'CHILD_5_0_POPULATION',
    'MINORS_17_0_POPULATION', 'ADULTS_18_PLUS_POPULATION',
    'SENIORS_65_PLUS_POPULATION', 'POPULATION_DENSITY'
]
for col in demo_numeric_cols:
    if col in echo_filtered.columns:
        echo_filtered[col] = pd.to_numeric(echo_filtered[col], errors='coerce')

What does this code do?

  • The RADIUS_OF_AREA == '5' filter keeps only the 5-mile radius rows. This cuts the dataset to roughly one-third of its original size.
  • Because the CSV was loaded with dtype=str in Step 1, every column is a string right now. The loop converts 23 demographic columns (population, income, race, education, age) to numeric types so you can do math with them later.

Why a 5-mile radius?

Full Circle Future's Waste Impact Tracker methodology states that each landfill was analyzed using data from census tracts within a 5-mile radius, "a boundary chosen based on impacted community input." Using the same radius aligns your pipeline with published environmental justice research.

  • Run Cell 10. You should see the original ECHO row count and the filtered 5-mile count (roughly one-third of the original).

Filtered count is the same as the original?

The RADIUS_OF_AREA column is a string because the CSV was loaded with dtype=str. Make sure the filter compares to the string '5' (with quotes), not the integer 5.

Help me debug my ECHO Demographics filter

Check for missing values in the join columns

Before attempting any join, you need to know where gaps exist. A missing GHGRP ID means the primary join cannot match that landfill. Missing coordinates mean the fallback match cannot rescue it either.

  • Create a new code cell (Cell 11) and check for missing values by pasting the following code:
# --- Cell 11: Check missing values in critical columns ---
print("Missing values in critical join columns:")
print(f"  LMOP - {GHGRP_COL}: {lmop_df[GHGRP_COL].isna().sum()} of {len(lmop_df)}")
print(f"  LMOP - {LAT_COL}: {lmop_df[LAT_COL].isna().sum()} of {len(lmop_df)}")
print(f"  LMOP - {LON_COL}: {lmop_df[LON_COL].isna().sum()} of {len(lmop_df)}")
print(f"  FRS Links - PGM_SYS_ID: {frs_links_filtered['PGM_SYS_ID'].isna().sum()} of {len(frs_links_filtered)}")
print(f"  FRS Links - REGISTRY_ID: {frs_links_filtered['REGISTRY_ID'].isna().sum()} of {len(frs_links_filtered)}")
print(f"  ECHO - REGISTRY_ID: {echo_filtered['REGISTRY_ID'].isna().sum()} of {len(echo_filtered)}")

What does this code do?

Each line checks one critical column for missing values and prints the count alongside the total rows. This gives you a quick data quality report before you attempt the joins.

  • Missing GHGRP_COL values are landfills that the primary join in Step 3 cannot reach.
  • Missing latitude or longitude values are landfills that the coordinate fallback in Step 4 cannot rescue either.
  • Missing PGM_SYS_ID or REGISTRY_ID values in FRS or ECHO would break the bridge between datasets.

Before you run this cell, take a moment to predict: do you think more landfills will be missing a GHGRP ID, or missing coordinates?

  • Run Cell 11 and compare the output to your prediction.

You should see that many landfills are missing a GHGRP ID (matching the count from Cell 8), but very few are missing coordinates. This confirms that the coordinate fallback strategy in Step 4 has a strong foundation to work with.

Seeing a NameError on frs_links_filtered?

This means Cell 9 did not run successfully. Go back and re-run Cell 9 first, then re-run Cell 11.

Help me fix a NameError in my missing values check

✔️ Awesome, I've got everything!

Great. Double check that you have saved your notebook and that Cells 7 through 11 all ran without errors.

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm each of the following cells is present and in order:

  • Cells 1-6 from Step 1 (Drive mount, file paths, four data loads).
  • Cell 7 sets seven column name variables (GHGRP_COL, LAT_COL, LON_COL, etc.).
  • Cell 8 standardizes IDs to strings and prints landfill counts with and without GHGRP IDs.
  • Cell 9 filters FRS Program Links to E-GGRT rows only.
  • Cell 10 filters ECHO Demographics to 5-mile radius and converts demographic columns to numeric.
  • Cell 11 prints missing value counts for all critical join columns.

Your raw datasets are now clean, filtered, and ready for joining. Next up, you will build the primary three-table SQL join in SQLite that bridges the GHGRP ID in the landfill file to the REGISTRY_ID in the demographics file.

Build the Primary Join in SQLite

You cleaned and filtered all three datasets in the last step. The landfill data is ready, the FRS links are scoped to greenhouse gas records, and the demographics are limited to 5-mile radius rows.

Here is the core problem: the GHGRP ID in your landfill data and the REGISTRY_ID in the demographics data are completely different numbering systems. They were never designed to connect to each other. EPA's Facility Registry Service is the only bridge between them. In this step, you'll load all three tables into SQLite and chain them together with SQL joins.

In this step, get ready to:
  • Load your cleaned DataFrames into a SQLite in-memory database.
  • Write a three-table SQL join that bridges GHGRP IDs to REGISTRY_IDs through FRS.
  • Identify which landfills could not be matched by ID.
Load the cleaned data into SQLite

SQLite is a lightweight database engine built into Python. By loading your DataFrames into SQLite tables, you can write readable SQL queries to join them.

  • Create a new cell in your notebook (Cell 12) and paste the following code to load all three DataFrames into an in-memory database:
# --- Cell 12: Load DataFrames into SQLite ---
conn = sqlite3.connect(':memory:')

# Copy each DataFrame into a named SQLite table
lmop_df.to_sql('lmop', conn, index=False, if_exists='replace')
frs_links_filtered.to_sql('frs_links', conn, index=False, if_exists='replace')
echo_filtered.to_sql('echo_demo', conn, index=False, if_exists='replace')

# Confirm all tables loaded with row counts
print("All tables loaded into SQLite.")
print(f"  lmop: {pd.read_sql('SELECT COUNT(*) as cnt FROM lmop', conn).iloc[0]['cnt']} rows")
print(f"  frs_links: {pd.read_sql('SELECT COUNT(*) as cnt FROM frs_links', conn).iloc[0]['cnt']} rows")
print(f"  echo_demo: {pd.read_sql('SELECT COUNT(*) as cnt FROM echo_demo', conn).iloc[0]['cnt']} rows")

What does this code do?

  • sqlite3.connect(':memory:') creates a database that lives entirely in RAM. No files to manage, no setup required.
  • .to_sql() copies each DataFrame into a named table inside the database.
  • The three SELECT COUNT(*) queries confirm every row transferred correctly.
  • Run the cell. You should see all three tables loaded with row counts matching what you saw at the end of Step 2.

Why use SQL instead of pandas merges?

You could chain pandas .merge() calls to achieve the same result. SQL reads more naturally for multi-table joins, especially when explaining your logic to someone else or in an interview.

A three-table join in SQL is one readable query. The same join in pandas is three chained method calls that are harder to scan at a glance.

Seeing an OperationalError?

  • Make sure you ran all the cells from Steps 1 and 2 first. The variables lmop_df, frs_links_filtered, and echo_filtered must already exist in memory.
  • If you restarted the Colab runtime, use **Runtime**, then **Run all** to re-execute every cell from the top.

Still stuck? Ask for help.

Write and run the three-table join

The join chains three tables together: LMOP landfills connect to FRS Program Links on the GHGRP ID, then FRS connects to ECHO Demographics on REGISTRY_ID. Only landfills that appear in all three tables survive the join.

  • Create a new cell (Cell 13) and define the join query by pasting this code:
# --- Cell 13: Run the primary three-table join ---
primary_join_query = f"""
SELECT
    l.*,
    e.REGISTRY_ID,
    e.ACS_POPULATION,
    e.MINORITY_POPULATION,
    e.LOWINCOME,
    e.PERSONS_BELOW_POVERTY_LEVEL,
    e.WHITE_POPULATION,
    e.AFRICAN_AMERICAN_POPULATION,
    e.HISPANIC_ORIGIN_POPULATION,
    e.ASIAN_POP,
    e.AMERICAN_INDIAN_POPULATION,
    e.OTHER_MULTIRACIAL_POPULATION,
    e.HOUSEHOLDS,
    e.HOUSEHOLDS_PUBLIC_ASSISTANCE,
    e.EDUCATION_BASIS,
    e.LESS_THAN_9TH_GRADE_POPULATION,
    e.GRADES_9_12_POPULATION,
    e.HIGH_SCHOOL_DIPLOMA_POPULATION,
    e.SOME_COLLEGE_POPULATION,
    e.BS_BA_OR_MORE_POPULATION,
    e.CHILD_5_0_POPULATION,
    e.MINORS_17_0_POPULATION,
    e.ADULTS_18_PLUS_POPULATION,
    e.SENIORS_65_PLUS_POPULATION,
    e.POPULATION_DENSITY
FROM lmop l
INNER JOIN frs_links f
    ON l."{GHGRP_COL}" = f.PGM_SYS_ID
INNER JOIN echo_demo e
    ON f.REGISTRY_ID = e.REGISTRY_ID
WHERE l."{GHGRP_COL}" IS NOT NULL
    AND l."{GHGRP_COL}" != ''
"""

What does this query do?

  • l.* selects every column from the LMOP landfill table (name, location, status, owner, etc.).
  • The long list of e. columns pulls specific demographic fields from ECHO (population, racial breakdown, income, education, age).
  • The first INNER JOIN connects each landfill to its FRS record by matching GHGRP ID to PGM_SYS_ID.
  • The second INNER JOIN connects the FRS record to ECHO demographics by matching REGISTRY_ID on both sides.
  • The WHERE clause filters out landfills with no GHGRP ID, since they cannot participate in this join.
  • Below the query in the same cell, add this code to execute the query and preview the results:
# Execute the join and preview a matched landfill
matched_df = pd.read_sql(primary_join_query, conn)
print(f"\nPrimary join results: {len(matched_df)} landfills matched")
print(f"\nSample matched row:")
if len(matched_df) > 0:
    sample = matched_df.iloc[0]
    print(f"  Landfill: {sample[NAME_COL]}")
    print(f"  State: {sample[STATE_COL]}")
    print(f"  Population within 5 miles: {sample['ACS_POPULATION']:,.0f}")
    print(f"  People of Color: {sample['MINORITY_POPULATION']:,.0f}")
    print(f"  Low Income: {sample['LOWINCOME']:,.0f}")

What does this code do?

  • pd.read_sql() sends your SQL query to the SQLite database and returns the results as a pandas DataFrame.
  • The sample row shows a real landfill with its demographic data side by side. This is the first time location and community data are paired together.
  • Run the cell. You should see the count of matched landfills and a sample row showing a landfill name alongside population, people of color, and low income numbers.

Seeing 0 matched landfills?

  • Check that your Cell 8 (standardize ID columns) ran correctly. If the GHGRP IDs still have decimal points (like 1234567.0), the join will fail because PGM_SYS_ID in FRS stores them as clean strings without decimals.
  • Confirm that frs_links_filtered is not empty. Run len(frs_links_filtered) in a scratch cell. If it shows 0, re-run Cell 9 (the E-GGRT filter).

Need help debugging your join?

Identify the unmatched landfills

The primary join only connects landfills that have a GHGRP ID and whose ID appears in FRS. Landfills without a GHGRP ID were excluded entirely. You need to know exactly how many fell through the cracks.

  • Create a new cell (Cell 14) and paste the following code to separate matched landfills from unmatched ones:
# --- Cell 14: Identify unmatched landfills ---
# Collect the GHGRP IDs that successfully matched
matched_ghgrp_ids = set(matched_df[GHGRP_COL].dropna().unique())

# Unmatched = no GHGRP ID at all, or GHGRP ID not found in results
unmatched_df = lmop_df[
    (lmop_df[GHGRP_COL].isna()) |
    (~lmop_df[GHGRP_COL].isin(matched_ghgrp_ids))
].copy()

print(f"\nUnmatched landfills: {len(unmatched_df)}")
print(f"Match rate so far: {len(matched_df)}/{len(lmop_df)} ({len(matched_df)/len(lmop_df)*100:.1f}%)")
print(f"Still need to match: {len(unmatched_df)} landfills")

What does this code do?

  • matched_ghgrp_ids collects every GHGRP ID that made it through the join. This is the "success list."
  • The filter catches two groups: landfills with no GHGRP ID at all (.isna()), and landfills whose GHGRP ID did not appear in FRS (~.isin()).
  • unmatched_df is the DataFrame of landfills that still have no demographic data. These are the records the next step will try to rescue.

Before you run this cell, think about what fraction of the 2,600+ landfills you expect to be matched. Remember, back in Cell 8 you saw how many landfills have a GHGRP ID and how many do not.

  • Run the cell.

You should see two numbers: the count and percentage of landfills that matched, and the count still waiting to be matched. That unmatched count is not an error. It is the gap that Step 4 solves with coordinate-based matching.

Match rate looks too low?

  • A match rate below 10% likely means the GHGRP IDs are not formatted consistently. In a scratch cell, compare a few values: run lmop_df[GHGRP_COL].dropna().head() and frs_links_filtered['PGM_SYS_ID'].head(). Look for differences like leading zeros or trailing spaces.
  • If the unmatched count is higher than expected but not drastically wrong, that is normal. Many landfills simply do not report under the Greenhouse Gas Reporting Program.

Need help interpreting your match rate?

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook.

ⓧ I'd like to double check the full code

Scroll up through your notebook and confirm these cells are present in order, with each one producing the expected output:

  • Cells 1-6: Mount Drive, define paths, load all four files into DataFrames.
  • Cells 7-8: Set column name variables, standardize IDs to strings, print GHGRP ID counts.
  • Cells 9-11: Filter FRS to E-GGRT, filter ECHO to 5-mile radius, check missing values.
  • Cell 12: Load DataFrames into SQLite. Output shows three tables with row counts.
  • Cell 13: Three-table join query and execution. Output shows matched count and a sample row with landfill name, population, people of color, and low income.
  • Cell 14: Identify unmatched landfills. Output shows unmatched count, match rate percentage, and remaining count.

Your primary join just connected landfill locations to community demographics for every landfill with a GHGRP ID. But hundreds of landfills are still missing. Next up, you'll rescue those unmatched records using GPS coordinates instead of IDs.

Rescue Unmatched Landfills with Coordinate Matching

In the last step, you built a three-table join that connected landfills to community demographics through their GHGRP IDs. The good news is it worked. The bad news is that hundreds of landfills have no GHGRP ID at all, because they don't report greenhouse gas emissions.

These are often smaller, older landfills that may sit near underserved communities. Dropping them would create a blind spot in the environmental justice analysis. In this step, you'll rescue those landfills by matching their GPS coordinates to the nearest FRS facility, then pulling demographics for the match.

In this step, get ready to:
  • Write and test a Haversine distance function for calculating GPS distances in miles.
  • Match unmatched landfills to FRS facilities by coordinate proximity within a 1-mile threshold.
  • Join coordinate-matched landfills to ECHO Demographics and count the results.
Write the Haversine distance function

Both the LMOP landfill file and FRS Facilities contain GPS coordinates (latitude and longitude). The Haversine formula converts the difference between two coordinate pairs into a real-world distance in miles, accounting for the curvature of the Earth.

  • Create a new code cell in your notebook and define the Haversine function by pasting this code:
# --- Cell 15: Haversine distance function ---
def haversine_miles(lat1, lon1, lat2, lon2):
    """Calculate the distance in miles between two GPS coordinate pairs."""
    lat1, lon1, lat2, lon2 = map(radians, [lat1, lon1, lat2, lon2])
    dlat = lat2 - lat1
    dlon = lon2 - lon1
    a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2
    c = 2 * asin(sqrt(a))
    # Earth's mean radius in miles
    miles = 3956 * c
    return miles

# Test with known coordinates: NYC City Hall to Times Square
test_dist = haversine_miles(40.7128, -74.0060, 40.7580, -73.9855)
print(f"Test: NYC City Hall to Times Square = {test_dist:.2f} miles")

What does this code do?

The haversine_miles function takes two GPS coordinate pairs and returns the straight-line distance between them in miles. It converts degrees to radians, applies the Haversine formula to account for Earth's curvature, then multiplies by 3,956 (Earth's mean radius in miles).

The test call uses the coordinates of NYC City Hall and Times Square, a well-known distance you can verify. The radians, cos, sin, asin, and sqrt functions were already imported from Python's math module in Cell 1.

  • Run the cell. You should see a distance of approximately 3.35 miles printed.

Seeing a NameError for radians or sqrt?

Make sure you ran Cell 1 earlier in this session. The from math import radians, cos, sin, asin, sqrt line in Cell 1 imports the math functions this cell depends on.

If your Colab runtime restarted at any point, you need to re-run all previous cells from Cell 1 onward.

Why isn't my Haversine function working in Google Colab?

Match unmatched landfills by coordinate proximity

The FRS Facilities file was loaded as all strings back in Cell 5. Before you can compare coordinates, you need to convert the latitude and longitude columns to numbers and filter out any facilities that are missing coordinates.

  • Create a new cell and prepare the FRS Facilities data by pasting this code:
# --- Cell 16: Prepare FRS Facilities for coordinate matching ---
# Convert coordinate columns from strings to numbers
frs_facilities_df['LATITUDE_MEASURE'] = pd.to_numeric(
    frs_facilities_df['LATITUDE_MEASURE'], errors='coerce'
)
frs_facilities_df['LONGITUDE_MEASURE'] = pd.to_numeric(
    frs_facilities_df['LONGITUDE_MEASURE'], errors='coerce'
)

# Keep only facilities that have valid coordinates
frs_with_coords = frs_facilities_df.dropna(
    subset=['LATITUDE_MEASURE', 'LONGITUDE_MEASURE']
).copy()
print(f"FRS facilities with coordinates: {len(frs_with_coords)}")

What does this code do?

The LATITUDE_MEASURE and LONGITUDE_MEASURE columns were loaded as strings (because you used dtype=str in Cell 5). Converting them to numeric lets you do math on them with the Haversine function.

The dropna filter creates frs_with_coords, a cleaned subset of FRS that only includes facilities with valid GPS data.

  • Run the cell. You should see a count of FRS facilities with valid coordinates printed.

Now for the core matching logic. For each unmatched landfill, the strategy is to filter FRS Facilities down to facilities in the same state, calculate the Haversine distance to every candidate, and keep the closest one if it falls within 1 mile.

Why filter by state first?

FRS Facilities contains hundreds of thousands of records. Calculating the Haversine distance to every single one for every unmatched landfill would take hours. Filtering to the same state reduces the search space from hundreds of thousands to just a few thousand candidates per landfill.

Why a 1-mile threshold?

EPA notes that LMOP coordinates are not intended to be exactly precise. Some map to a landfill's center while others map to its entrance. A 1-mile threshold is wide enough to account for this imprecision but tight enough to avoid false matches with unrelated facilities.

Before you run this, think about what you expect. How many of the unmatched landfills do you think will have an FRS facility within 1 mile of their GPS coordinates?

  • Create a new cell and start building the coordinate matching loop by pasting this code:
# --- Cell 17: Match unmatched landfills by coordinates ---
MAX_DISTANCE_MILES = 1.0
coord_matches = []
no_match_count = 0

for idx, landfill in unmatched_df.iterrows():
    lat = landfill[LAT_COL]
    lon = landfill[LON_COL]
    state = landfill[STATE_COL]

    if pd.isna(lat) or pd.isna(lon):
        no_match_count += 1
        continue

    state_facilities = frs_with_coords[
        frs_with_coords['FAC_STATE'] == state
    ]

    if len(state_facilities) == 0:
        no_match_count += 1
        continue

    distances = state_facilities.apply(
        lambda row: haversine_miles(
            lat, lon,
            row['LATITUDE_MEASURE'],
            row['LONGITUDE_MEASURE']
        ),
        axis=1
    )

    min_idx = distances.idxmin()
    min_dist = distances[min_idx]

    if min_dist <= MAX_DISTANCE_MILES:
        coord_matches.append({
            'landfill_index': idx,
            'REGISTRY_ID': state_facilities.loc[min_idx, 'REGISTRY_ID'],
            'match_distance_miles': min_dist,
            'frs_facility_name': state_facilities.loc[min_idx, 'FAC_NAME']
        })
    else:
        no_match_count += 1
  • In the same cell, below the loop, add this code to summarize the results:
coord_matches_df = pd.DataFrame(coord_matches)
print(f"\nCoordinate matches found: {len(coord_matches_df)}")
print(f"Could not match: {no_match_count}")
if len(coord_matches_df) > 0:
    print(f"Average match distance: {coord_matches_df['match_distance_miles'].mean():.3f} miles")
    print(f"Max match distance: {coord_matches_df['match_distance_miles'].max():.3f} miles")

What does this code do?

The loop iterates over every landfill in unmatched_df. For each one, it skips landfills without GPS coordinates, filters frs_with_coords to the same state using FAC_STATE, and calculates the Haversine distance to every facility in that state.

If the closest facility is within MAX_DISTANCE_MILES (1.0 mile), the match is stored with the facility's REGISTRY_ID and the match distance. That REGISTRY_ID is the key you need to look up demographics in ECHO.

The summary at the end prints how many landfills were matched, how many could not be matched, and the average and maximum match distances so you can gauge the quality of the matches.

  • Run the cell. This may take a few minutes because the loop calculates distances against thousands of facilities per landfill.

When the cell finishes, you should see the count of coordinate matches found, the count of landfills that could not be matched, and the average and maximum match distances in miles.

Cell is taking a very long time?

This cell iterates over hundreds of landfills and compares each one against thousands of FRS facilities in the same state. It can take several minutes to complete. If the cell spinner is still spinning, the loop is still running.

If it runs for more than 10 minutes, check that Cell 16 ran successfully and that frs_with_coords is not empty. An empty DataFrame would mean every iteration fails the state filter check.

Why is my coordinate matching loop running slowly in Colab?

Join coordinate-matched landfills to demographics

You now have a list of REGISTRY_ID values for the coordinate-matched landfills. The final piece is joining those IDs to ECHO Demographics to pull community data for these newly matched landfills.

Before you run this, do you think every coordinate-matched landfill will also have a matching row in ECHO Demographics? Or might some fall through the cracks again?

  • Create a new cell and join the coordinate-matched landfills to demographics by pasting this code:
# --- Cell 18: Join coordinate-matched landfills to demographics ---
if len(coord_matches_df) > 0:
    # Build a DataFrame of coordinate-matched landfills with their new REGISTRY_IDs
    coord_landfills = unmatched_df.loc[coord_matches_df['landfill_index']].copy()
    coord_landfills['REGISTRY_ID'] = coord_matches_df['REGISTRY_ID'].values

    # Load into SQLite for the demographics join
    coord_landfills.to_sql('coord_landfills', conn, index=False, if_exists='replace')

    coord_join_query = """
    SELECT
        cl.*,
        e.ACS_POPULATION,
        e.MINORITY_POPULATION,
        e.LOWINCOME,
        e.PERSONS_BELOW_POVERTY_LEVEL,
        e.WHITE_POPULATION,
        e.AFRICAN_AMERICAN_POPULATION,
        e.HISPANIC_ORIGIN_POPULATION,
        e.ASIAN_POP,
        e.AMERICAN_INDIAN_POPULATION,
        e.OTHER_MULTIRACIAL_POPULATION,
        e.HOUSEHOLDS,
        e.HOUSEHOLDS_PUBLIC_ASSISTANCE,
        e.EDUCATION_BASIS,
        e.LESS_THAN_9TH_GRADE_POPULATION,
        e.GRADES_9_12_POPULATION,
        e.HIGH_SCHOOL_DIPLOMA_POPULATION,
        e.SOME_COLLEGE_POPULATION,
        e.BS_BA_OR_MORE_POPULATION,
        e.CHILD_5_0_POPULATION,
        e.MINORS_17_0_POPULATION,
        e.ADULTS_18_PLUS_POPULATION,
        e.SENIORS_65_PLUS_POPULATION,
        e.POPULATION_DENSITY
    FROM coord_landfills cl
    INNER JOIN echo_demo e
        ON cl.REGISTRY_ID = e.REGISTRY_ID
    """

    coord_matched_df = pd.read_sql(coord_join_query, conn)
    print(f"Coordinate-matched landfills with demographics: {len(coord_matched_df)}")
else:
    coord_matched_df = pd.DataFrame()
    print("No coordinate matches to join.")

What does this code do?

First, it builds coord_landfills by looking up the original landfill rows from unmatched_df using the index positions stored in coord_matches_df, then attaches the REGISTRY_ID that was found through coordinate matching.

Then it loads this DataFrame into SQLite as the coord_landfills table and joins it to echo_demo on REGISTRY_ID. The SQL query selects the same demographic columns you used in the primary join. The else branch handles the edge case where no coordinate matches were found.

  • Run the cell. You should see a count of how many coordinate-matched landfills successfully joined to demographics.

This count may be slightly lower than the coordinate match count from Cell 17. Some facilities in FRS may not have a corresponding row in ECHO Demographics, so the INNER JOIN drops them.

Seeing 0 coordinate-matched landfills?

Check that Cell 17 produced coordinate matches. If coord_matches_df is empty, the code takes the else branch and skips the join entirely.

Also verify that the SQLite connection conn is still open. If your Colab runtime restarted, you need to re-run all cells from Cell 1 onward, because the in-memory SQLite database is lost on restart.

Why am I getting 0 coordinate-matched landfills with demographics?

✔️ Awesome, I've got everything!

Great. Confirm you have saved your notebook, then move on to the next step.

ⓧ I'd like to double check the full code

Scroll through your notebook and confirm these four cells are present in order, each in its own code cell:

  • Cell 15: haversine_miles function definition and the test call with NYC coordinates.
  • Cell 16: Converts LATITUDE_MEASURE and LONGITUDE_MEASURE to numeric and creates frs_with_coords.
  • Cell 17: The for loop over unmatched_df with state filtering, Haversine distances, and the 1-mile threshold. Ends with coord_matches_df summary prints.
  • Cell 18: Builds coord_landfills, loads it into SQLite, and joins to echo_demo on REGISTRY_ID to produce coord_matched_df.

Make sure all four cells ran successfully and each printed output without errors.

You now have two sets of results: landfills matched by GHGRP ID in matched_df and landfills matched by coordinates in coord_matched_df. Next up, you'll merge them into one final CSV that is ready for Tableau.

Combine Results and Export the Final CSV

Your coordinate matching strategy rescued landfills that the ID-based join could not reach. You now have two separate DataFrames of results sitting side by side in memory.

Anyone looking at your output needs to know which matching method produced each row. In this step, you will tag each result set, merge them into one DataFrame, and export the final CSV to Google Drive for import into Tableau.

In this step, get ready to:
  • Add a Match_Method column to both result sets and concatenate them into a single DataFrame.
  • Export the combined DataFrame as a CSV to Google Drive and confirm the column list.
Tag and combine both result sets

Right now, matched_df holds landfills joined by GHGRP ID, and coord_matched_df holds landfills joined by GPS coordinate proximity. Before merging them, each row needs a label recording which method produced the match.

Before you run the next cell, think back to your results from Steps 3 and 4. What do you expect the total match count and overall match rate to be when both sets are combined?

  • Tag both DataFrames with a Match_Method column, concatenate them, and print the final summary by pasting this code into a new cell:
# --- Cell 19: Merge both result sets ---
matched_df['Match_Method'] = 'GHGRP_ID'
if len(coord_matched_df) > 0:
    coord_matched_df['Match_Method'] = 'Coordinate'

final_df = pd.concat([matched_df, coord_matched_df], ignore_index=True)

print(f"\n{'='*50}")
print(f"FINAL RESULTS")
print(f"{'='*50}")
print(f"Total landfills with demographics: {len(final_df)}")
print(f"  Matched by GHGRP ID: {len(matched_df)}")
print(f"  Matched by coordinates: {len(coord_matched_df)}")
print(f"  Total in LMOP: {len(lmop_df)}")
print(f"  Overall match rate: {len(final_df)/len(lmop_df)*100:.1f}%")
print(f"  Unmatched: {len(lmop_df) - len(final_df)}")

What does this code do?

  • matched_df['Match_Method'] = 'GHGRP_ID' adds a new column to every row that was joined through the FRS Program Links bridge. This labels them as ID-based matches.
  • The if len(coord_matched_df) > 0 guard prevents an error if no coordinate matches were found. When matches exist, they get labeled Coordinate.
  • pd.concat stacks both DataFrames vertically into one unified final_df. The ignore_index=True resets the row numbers so they run sequentially.
  • The print block gives you the full pipeline scorecard: total matched, breakdown by method, overall match rate, and unmatched count.

You should see a FINAL RESULTS summary showing the total matched landfills, the breakdown by GHGRP ID vs. coordinate, the overall match rate as a percentage, and how many landfills remain unmatched.

How did your prediction compare?

The combined match rate should be higher than what you saw at the end of Step 3, because the coordinate fallback rescued landfills the ID-based join missed. The unmatched count represents landfills that had no GHGRP ID and no FRS facility within 1 mile of their GPS coordinates.

Seeing 0 total landfills?

  • Make sure you ran all previous cells in order. If you restarted the Colab runtime, you need to re-run every cell from Cell 1 onward.
  • Check that matched_df is not empty by running len(matched_df) in a new cell. If it shows 0, re-run Cells 12 through 14.

Why is my final results summary empty?

Export the final CSV to Google Drive
  • Save the combined DataFrame as a CSV file and close the SQLite connection by pasting this code into a new cell:
# --- Cell 20: Export to CSV ---
output_path = DATA_PATH + 'landfill_ej_analysis.csv'
final_df.to_csv(output_path, index=False)
print(f"\nExported to: {output_path}")
print(f"File contains {len(final_df)} rows and {len(final_df.columns)} columns")
print(f"\nColumn list:")
for col in final_df.columns:
    print(f"  - {col}")

conn.close()
print("\nPipeline complete. CSV is ready for Tableau.")

What does this code do?

  • output_path builds the full file path by combining your DATA_PATH variable with the filename landfill_ej_analysis.csv.
  • to_csv(output_path, index=False) writes the DataFrame to a CSV file on your Google Drive. The index=False prevents pandas from adding an extra row-number column.
  • The column list printout confirms that the file contains both landfill fields (name, state, coordinates) and demographic fields (population, income, race) side by side.
  • conn.close() releases the in-memory SQLite database. The data now lives in the CSV file.

You should see the export path printed, followed by the row and column counts, a full list of every column name, and the message Pipeline complete. CSV is ready for Tableau.

Seeing a file not found or permission error?

  • Make sure your Google Drive is still mounted. If the runtime disconnected, re-run Cell 1 to remount it.
  • Confirm that the folder EPA_Landfill_Data still exists in your Google Drive. If you renamed or moved it, update DATA_PATH in Cell 2 to match.

Why can't my notebook write the CSV to Google Drive?

✔️ Awesome, I've got everything!

Great. Confirm you have saved your notebook so all 20 cells are preserved.

ⓧ I'd like to double check the full code

Your full notebook now has 20 cells. Scroll up through your notebook and confirm each cell is present in order, from Cell 1 (mount Drive and import libraries) through Cell 20 (export to CSV). The two cells you added in this step are:

  • Cell 19: Merge both result sets. Adds Match_Method to each DataFrame, concatenates into final_df, and prints the final results summary.
  • Cell 20: Export to CSV. Writes landfill_ej_analysis.csv to Google Drive, prints the column list, and closes the SQLite connection.

What did you just build?

Your final CSV pairs every matchable U.S. landfill from the LMOP database with the demographics of the community living within 5 miles of it. Each row contains the landfill's name, location, and status alongside population counts, racial breakdown, income levels, and education statistics from the American Community Survey.

The Match_Method column makes the data transparent. Anyone analyzing the CSV can see exactly which records were linked by official ID and which were linked by coordinate proximity.

Secret mission

Validate Your Findings Against EPA's Published Statistics

EPA reported that approximately 902 LMOP landfills, about 35%, sit near communities exceeding the 80th percentile for environmental justice demographic indicators. Can your pipeline validate that finding using a different methodology and newer data? Add an EJ flag column and compare your results to the published statistics.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely in Google Colab with files stored on Google Drive, so there are no ongoing costs. Decide whether to keep your resources for the Tableau dashboard project (Project 2), or delete them entirely.

Resources you used:

  • Google Colab notebook containing the pipeline code.
  • EPA_Landfill_Data folder on Google Drive containing uploaded EPA datasets and the output landfill_ej_analysis.csv.
  • Original EPA dataset files downloaded to your desktop (LMOP xlsx, FRS ZIP, ECHO Demographics ZIP).

Keep everything running

No action needed. Choose this if you plan to continue to Project 2 (building the Tableau dashboard) or want to revisit your analysis later.

  • Your Colab notebook stays in Google Drive under Colab Notebooks and can be reopened anytime.
  • Your EPA_Landfill_Data folder and all its files remain on Google Drive at no cost (within your Google Drive storage quota).
  • The original ZIP and Excel files on your desktop can stay for reference or be deleted to free up local disk space.

Pause - I'll come back to this later

Free up local disk space while keeping your Google Drive files for later. There are no running processes to stop since Colab sessions shut down automatically after inactivity.

  • Delete the original EPA ZIP and Excel files from your desktop. The uploaded copies on Google Drive are all you need to resume.
  • Your Colab notebook and EPA_Landfill_Data folder stay on Google Drive. When you return, open the notebook and re-run the cells from the top.

Delete - I don't want to use this again

Remove all project files from both your desktop and Google Drive.

  • Delete the original EPA files from your desktop. On macOS, drag the LMOP Excel file, the FRS ZIP, and the ECHO Demographics ZIP to the Trash, then empty the Trash. On Windows, select the files, press Delete, then empty the Recycle Bin.
  • Delete the extracted CSV files from your desktop if you still have them (FRS_PROGRAM_LINKS.csv, FRS_FACILITIES.csv, ECHO_DEMOGRAPHICS.csv).
  • Delete the Google Drive folder. Open Google Drive in your browser, right-click the EPA_Landfill_Data folder, and select Move to trash.
  • Delete the Colab notebook. In Google Drive, open the Colab Notebooks folder, right-click your pipeline notebook, and select Move to trash.
  • Empty your Google Drive trash. Click Trash in the left sidebar, then click Empty trash to permanently free up the storage space.

Nice Work!

Nice Work!

Nice work! You just built a data pipeline that takes three disconnected EPA datasets and unifies them into a single file ready for environmental justice analysis.

You've learned how to:

  • Built a three-table SQL join in SQLite that bridges incompatible federal identifier systems through EPA's Facility Registry Service, connecting landfill records to surrounding community demographics.
  • Implemented a coordinate-based fallback using the Haversine formula to rescue landfills that could not be matched by ID alone, closing gaps in the dataset that would have created blind spots in the analysis.
  • Produced a single analysis-ready CSV pairing every matchable U.S. landfill with the racial makeup, income levels, and age distribution of the communities living within 5 miles.
  • Completed an optional Secret Mission to push your data analysis skills further.

Ready to quiz yourself?