Bottlenecks in the U.S. Energy Transition: An Analysis of Interconnection Queue Withdrawal Patterns

Project Prompt

Dataset(s) to be used:
LBNL Interconnection Queue Data (2024 update)
Source: https://emp.lbl.gov/queues

Analysis question:
Where in the United States are renewable energy projects experiencing the longest interconnection queue delays, and how do county-level queue wait times relate to withdrawal rates?

Columns that will (likely) be used:

• q_date — date a project entered the interconnection queue

• wd_date — withdrawal date (if applicable)

• on_date — commercial operation date (if applicable)

• region — ISO/RTO or balancing authority

• fips_codes — county identifier used for aggregation

• mw1 — project capacity (used indirectly for understanding project scale)

• q_id — unique project identifier

• project_type / type_clean — technology descriptors (not used in final analysis but part of exploratory review)

• Derived fields:

  • withdrawn_or_not (0/1)

  • queue_duration_days (difference in days between queue entry and withdrawal/operation)

  • median_queue_days (county-level aggregation)

  • withdrawal_rate (county-level aggregation)

(If using multiple datasets)
County boundaries GeoJSON (for mapping):

• Dataset 1: county_stats — contains GEOID

• Dataset 2: Plotly U.S. Counties GeoJSON — uses id

• Join key: county_stats["GEOID"] ↔ GeoJSON "id"

Hypothesis:
U.S. interconnection delays and withdrawal rates are not evenly distributed geographically.
I expect to find that:

1. Certain regions—perhaps areas with high renewable development interest or constrained transmission—exhibit significantly longer queue wait times.

2. Counties with longer typical queue waits will also show higher withdrawal rates, suggesting that prolonged interconnection studies, negotiations, or transmission bottlenecks contribute to project attrition.

3. ISO-level summaries will reveal structural differences, with some grid operators (e.g., PJM, CAISO) showing meaningfully higher median waiting times.

Introduction to the Project:

With rising electrcity demand and prices due to the data center buildout, electrification, and a rise in domestic manufacturing, the U.S. energy transition increasingly depends on how quickly new renewable energy projects can connect to the electrical grid. When I worked in climate policy in Congress, the length of time that projects spend waiting in the interconnection queue was a frequent concern that I heard from energy developers. But moving at the pace of politics, I was never able to dive deeper into the data to understand the granularities of what this looks like in particular localities and project types. Consequently, I was eager to use my Data Science for Policy final project to better understand U.S. electricity sector energy transition bottlenecks.

In this project, I explore three questions: Where in the United States is the energy transition most stalled? Which states and counties have the highest withdrawal rates for renewable energy projects? Which project types are more likely to be successful in connecting to the grid?

To answer these, I merge: The LBNL Interconnection Queue dataset and county shapefiles (Census TIGER/Line)

And I use Plotly to create interactive choropleths showing where the transition is stuck, to give a geographically grounded view of grid bottlenecks stalling U.S. decarbonization progress.

1. Set up libraries and plotting

In this cell, I load the core Python libraries I’ll use throughout the analysis:

• pandas for working with tabular data (DataFrames)

• numpy for numerical operations and array manipulation

• plotly.express for creating interactive charts and maps

• plotly.io to control how Plotly renders graphics in this notebook

I then tell Plotly to render plots inside the notebook by setting:

pio.renderers.default = "notebook"

import pandas as pd
import numpy as np
import plotly.express as px
import plotly.io as pio

pio.renderers.default = "notebook"

2. Load the LBNL Interconnection Queue dataset and inspect structure

Here I load the main dataset that powers the entire project: the LBNL Interconnection Queue file. The LBNL Interconnection Queue Dataset is comprised of data collected from interconnection queues for 7 ISOs/ RTOs and 44 non-ISO balancing areas (including utilities and Power Marketing Administrations), which collectively represent >95% of currently installed U.S. electric generating capacity

This dataset includes projects that connect to the bulk-power system, not distribution-connected or behind-the-meter, includes projects in queues through the end of 2023, and substantial data cleaning, standardization, and QA/QC has already been conducted by Berkeley Lab analyst team.

The full sample includes:

• 4,155 “operational” projects (~470.4 GW)

• 11,597 “active” projects (~2,598 GW)

• 325 “suspended” projects (~54.9 GW)

• 17,873 “withdrawn” projects (~3,097 GW)

(Source: https://emp.lbl.gov/sites/default/files/2024-04/Queued Up 2024 Edition_R2.pdf)

I imported it into python and inspected the dataset using the below code. I had to skip the first row, which had unecessary headers, and turn off low memory to handle multiple data types.

queue = pd.read_csv("LBNL_Ix_Queue_Data_File_thru2024_v2.csv", skiprows=1, 
                    low_memory = False)
queue.info()
queue.describe
queue.head()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 36441 entries, 0 to 36440
Data columns (total 31 columns):
 #   Column              Non-Null Count  Dtype  
---  ------              --------------  -----  
 0   q_id                36440 non-null  object 
 1   q_status            36441 non-null  object 
 2   q_date              35834 non-null  float64
 3   prop_date           29029 non-null  float64
 4   on_date             2957 non-null   float64
 5   wd_date             10138 non-null  float64
 6   ia_date             2440 non-null   float64
 7   IA_status_raw       24309 non-null  object 
 8   IA_status_clean     30984 non-null  object 
 9   county              33986 non-null  object 
 10  state               36334 non-null  object 
 11  county_state_pairs  33481 non-null  object 
 12  fips_codes          33257 non-null  float64
 13  poi_name            33611 non-null  object 
 14  region              36441 non-null  object 
 15  project_name        12184 non-null  object 
 16  utility             30226 non-null  object 
 17  entity              36441 non-null  object 
 18  developer           6729 non-null   object 
 19  cluster             9505 non-null   object 
 20  service             27367 non-null  object 
 21  project_type        33662 non-null  object 
 22  type1               36441 non-null  object 
 23  type2               4233 non-null   object 
 24  type3               99 non-null     object 
 25  mw1                 36441 non-null  float64
 26  mw2                 1156 non-null   float64
 27  mw3                 45 non-null     float64
 28  type_clean          36441 non-null  object 
 29  q_year              35834 non-null  float64
 30  prop_year           29029 non-null  float64
dtypes: float64(11), object(20)
memory usage: 8.6+ MB

	q_id	q_status	q_date	prop_date	on_date	wd_date	ia_date	IA_status_raw	IA_status_clean	county	...	project_type	type1	type2	type3	mw1	mw2	mw3	type_clean	q_year	prop_year
0	not assigned	withdrawn	43511.0	NaN	NaN	NaN	NaN	Withdrawn	Withdrawn	Coconino	...	Generation	Solar	NaN	NaN	20.0	NaN	NaN	Solar	2019.0	NaN
1	Q007 - 061	operational	38497.0	39539.0	NaN	NaN	NaN	In-Service	Operational	Navajo	...	Generation	Other	NaN	NaN	24.0	NaN	NaN	Other	2005.0	2008.0
2	Q044a.	withdrawn	39597.0	40909.0	NaN	NaN	NaN	Withdrawn	Withdrawn	Yuma	...	Generation	Solar	NaN	NaN	900.0	NaN	NaN	Solar	2008.0	2012.0
3	Q044b.	withdrawn	39597.0	41760.0	NaN	NaN	NaN	Withdrawn	Withdrawn	Maricopa	...	Generation	Solar	NaN	NaN	300.0	NaN	NaN	Solar	2008.0	2014.0
4	Q1	withdrawn	38041.0	42217.0	NaN	NaN	NaN	Withdrawn	Withdrawn	San Juan	...	Generation	Coal	NaN	NaN	700.0	NaN	NaN	Coal	2004.0	2015.0

5 rows × 31 columns

3. List all column names to understand available variables

I learned that the datasets has 36, 441 rows and 31 columns, and was stored as a combination of floats and objects. Each row represents one project in the interconnection queue, and includes data like the interconnection queue number, queue entry date, wtihdrawal or online connection date, geographic attributes, developer, utility, etc.

I wanted to inspect the column names further to determine which I was interested in using for the analysis, as well as to check for naming inconsistencies, so I printed out a list of the names of all columns in the dataset.

queue.columns.tolist()

['q_id',
 'q_status',
 'q_date',
 'prop_date',
 'on_date',
 'wd_date',
 'ia_date',
 'IA_status_raw',
 'IA_status_clean',
 'county',
 'state',
 'county_state_pairs',
 'fips_codes',
 'poi_name',
 'region',
 'project_name',
 'utility',
 'entity',
 'developer',
 'cluster',
 'service',
 'project_type',
 'type1',
 'type2',
 'type3',
 'mw1',
 'mw2',
 'mw3',
 'type_clean',
 'q_year',
 'prop_year']

4. Clean and standardize column names

Here I cleaned up the column names to make them easier to work with in code, removing leading or trailing spaces in the column names and converting each naame to lowercase.

queue.columns = queue.columns.str.strip()
queue.columns = queue.columns.str.lower()

5. Inspect the data types of the date columns

In this cell, I focused on the columns that represent key project dates. I wanted to understand what data type Python had encoded each as, and in what form they appeared, so that I could understand what I needed to do to convert them to datetime.

date_cols = ["q_date", "prop_date", "on_date", "wd_date", "ia_date"]

results = []
for col in date_cols:
    results.append(queue[col].dtype)
results

queue["prop_date"].head()

0        NaN
1    39539.0
2    40909.0
3    41760.0
4    42217.0
Name: prop_date, dtype: float64

6. Convert Excel-style serial dates into real calendar dates

I was pretty confused when the first five rows in one of the date columns had values like “39539.0” and “40909.0”, but I learned through som Googling that such data are Excel-style serial dates, representing days since a base date (usually Jan 1, 1900, or Dec 30, 1899); you convert them by adding this number to the base date.

So, excel_origin = “1899-12-30” sets the base date so that Excel day “1” corresponds to January 1, 1900

The for loop tells pandas to treat the values in each date column as “days since 1899-12-30” and convert them into a proper calendar date. The last line confirms that one of the date columns is now readable by humans in YYYY-MM-DD form.

Source: had help from Google Gemini to figure out this fix

excel_origin = "1899-12-30"
for col in date_cols:
    queue[col] = pd.to_datetime(queue[col], origin=excel_origin, unit="D")

queue["prop_date"].head()

0          NaT
1   2008-04-01
2   2012-01-01
3   2014-05-01
4   2015-08-01
Name: prop_date, dtype: datetime64[ns]

7. Create an variable indicating whether a project was withdrawn

This cell creates a binary variable that flags whether each project ultimately withdrew from the queue. I learned that there were ~12K non NaN rows in this column and that of these, 9990 projects were withdrawn. In retrospect, there would have been more useful rows had I used q_status for this.

queue["wd_date"].shape
queue["withdrawn_or_not"] = queue["wd_date"].notna().astype(int)
queue["withdrawn_or_not"].value_counts()

withdrawn_or_not
0    26303
1    10138
Name: count, dtype: int64

8. Compute how long each project spent in the queue

Next, I calculate the number of days each project spent in the interconnection queue before it either withdrew or came online.

If a project had a withdrawal date, I used that as the end of the queue period. If not, I used the on_date (commercial operation date) as the end date. Subtracting q_date from this end date yielded a difference of days, extracted by using dt.days.

So queue_duration_days represents the waiting time in days between entering the queue and reaching an outcome.

When I ran summary stats on the new column, I found some odd values including very high queue durations (including a max of 19,722 days) and negative queue durations ( a min of -35,018 days) that I wanted to investigate further to ensure the operation had worked.

queue["queue_duration_days"] = (
    queue["wd_date"].fillna(queue["on_date"]) - queue["q_date"]
).dt.days

queue["queue_duration_days"].value_counts()
queue["queue_duration_days"].describe()

count    12235.000000
mean      1055.656723
std       2069.432832
min     -35018.000000
25%        261.000000
50%        666.000000
75%       1361.500000
max      19722.000000
Name: queue_duration_days, dtype: float64

9. Identify extreme queue-time outliers using the IQR method

To understand whether there were significant outliers, I plugged the IQR formula that we learned in Quant 1 into a function to flag outliers. I learned that by this definition there was only one outlier in the queue duration days column (likely the min value).

def find_outliers_iqr(queue, queue_duration_days):
    Q1 = queue["queue_duration_days"].quantile(0.25)
    Q3 = queue["queue_duration_days"].quantile(0.75)
    IQR = Q3 - Q1
    lower_bound = Q1 - 1.5 * IQR
    upper_bound = Q3 + 1.5 * IQR
    outliers = [(queue["queue_duration_days"] < lower_bound) | (queue["queue_duration_days"] > upper_bound)]
    return outliers

outliers = find_outliers_iqr(queue, "queue_duration_days")
pd.Series(outliers).shape

(1,)

10. Check for impossible negative queue durations

Disturbed by the presence of a negative interconnection queue length outcome, I did some investigation online and found that this does happen sometimes in raw interconnection queue data for legitimate reasons, including a project having entered an incorrect data, a placeholder date, updating one date but not the other, etc.

I determined that because I only had < 75 negative values out of tens of thousands of queue entries, it was not a systemic problem, and I should remove the entire rows that had the rogue negative values to prevent downstream distortions (in medians, means, regressions, etc.) and preserve the rest of the dataset. I then checked and there were no negative entries after running that code (returned an empty series).

queue["queue_duration_days"].loc[queue["queue_duration_days"] < 0]

695       -127.0
1727      -117.0
3204        -5.0
3606       -68.0
5597     -5670.0
          ...   
32750   -15026.0
32760    -2217.0
32776   -10735.0
32785   -11470.0
32832   -11470.0
Name: queue_duration_days, Length: 74, dtype: float64

queue = queue[queue["queue_duration_days"] >= 0]

queue["queue_duration_days"].loc[queue["queue_duration_days"] < 0]

Series([], Name: queue_duration_days, dtype: float64)

11. Clean and standardize county FIPS codes

To aggregate and map results at the county level, I needed clean 5-digit FIPS codes. This is because TIGER/Line county shapefiles use a column called “GEOID” which is always 5 characters long, so they need to match to merge later. I ensured the FIPS column is stored as a string (not int or float), and tried to force every FIPS code to be exactly 5 characters long, padded with leading zeros if needed.

queue["fips_codes"] = (
    pd.to_numeric(queue["fips_codes"], errors="coerce")
    .astype(str)
    .str.zfill(5)
    
)

I then discovered that the fips_codes still had zeroes after a decimal point.

queue["fips_codes"].dtypes
queue["fips_codes"].head()

665    53043.0
666    16049.0
674    53043.0
677    53023.0
679    53023.0
Name: fips_codes, dtype: object

12. Aggregate project-level data to the county level

Here I collapse all the project-level records into county-level summary statistics using the cleaned FIPS codes.

This creates a separate group for every county in the U.S. that appears in the dataset, and computes summary statistics on each county, including:

• the percent of projects in a county that withdrew

• median number of days projects spent in the queue before withdrawing or coming online

• sum of the MW capacity of all projects in a given county

• which can show where significant amounts of proposed generation are concentrated

Then, .reset_index turns the grouped index back into a regular column named fips_codes.

Finally, Shapefiles (TIGER/Line) use “GEOID” as the column containing county FIPS strings, which I need for plotly choropleth maps and county-level analysis.

county_stats = (
    queue.groupby("fips_codes")
    .agg(
        withdrawal_rate=("withdrawn_or_not", "mean"),
        median_queue_days=("queue_duration_days", "median"),
        total_capacity_mw=("mw1", "sum"),
        project_count=("q_id", "count"),
    )
    .reset_index()
    .rename(columns={"fips_codes": "GEOID"})
)

print(county_stats.head())

     GEOID  withdrawal_rate  median_queue_days  total_capacity_mw  \
0    00nan         0.717415             1724.0      230827.534006   
1  10001.0         0.782609              644.0        1093.160000   
2  10003.0         0.489362              558.0        4720.250000   
3  10005.0         0.927273              692.0       10418.860000   
4   1001.0         1.000000              177.0        1408.000000   

   project_count  
0            913  
1             23  
2             47  
3             55  
4              8  

13. Check for counties with missing or malformed GEOID values

The below cell checks for missing FIPS, and the output was a row of the headers with no entries, which was mysterious.

I wanted to understand why the command to make the GEOIDs into 5 digit strings with no decimal points wasn’t working. This cell checks for GEOIDs that don’t have exactly 5 characters and found 1493 rows with lagging zeroes after a decimal point or other issues.

county_stats[county_stats["GEOID"].isna()]

	GEOID	withdrawal_rate	median_queue_days	total_capacity_mw	project_count

county_stats[county_stats["GEOID"].str.len() != 5]

	GEOID	withdrawal_rate	median_queue_days	total_capacity_mw	project_count
1	10001.0	0.782609	644.0	1093.160000	23
2	10003.0	0.489362	558.0	4720.250000	47
3	10005.0	0.927273	692.0	10418.860000	55
4	1001.0	1.000000	177.0	1408.000000	8
5	1003.0	1.000000	1057.0	2455.000000	11
...	...	...	...	...	...
1488	9007.0	1.000000	709.5	1559.085007	8
1489	9009.0	1.000000	473.5	2793.620014	18
1490	9011.0	1.000000	356.5	984.080001	12
1491	9013.0	1.000000	43.0	10.000000	1
1492	9015.0	1.000000	734.0	3875.010000	19

1492 rows × 5 columns

14. Convert malformed GEOIDs to numeric form to detect invalid values

Because I was having trouble converting the FIPS codes to five digits and finding entries with 2 FIPS codes in one cell, this step attempted to coerce the GEOID field into a numeric type so that any non-numeric or malformed entries became NaN and were excluded.

county_stats[“GEOID”].str.len().value_counts() returned:

5 1434

10 35

9 16

15 6

19 1

Name: count, dtype: int64

I saved all the rows with >5 characters as “bad_geoids” and inspected them. This helped me discover that I had a mix of valid 5-digit county FIPS codes AND some “GEOID” values that were much longer, and it turned out they were strings of multiple FIPS codes concatenated together.

Example: 1700917083 → “17009” + “17083” which are both real Illinois counties: 17009 = Brown County, IL 17083 = Jersey County, IL

It seems that the raw queue dataset may have sometimes listed two or more counties for one interconnection request.

# Convert to numeric safely
county_stats["GEOID"] = pd.to_numeric(county_stats["GEOID"], errors="coerce")

# Remove invalid entries
county_stats = county_stats[county_stats["GEOID"].notna()]

# Convert to string FIPS
county_stats["GEOID"] = county_stats["GEOID"].astype(int).astype(str).str.zfill(5)

county_stats["GEOID"].str.len().value_counts()

GEOID
5     1434
10      35
9       16
15       6
19       1
Name: count, dtype: int64

county_stats["GEOID"].str.len().unique()

array([ 5, 10, 15,  9, 19])

bad_geoids = county_stats[county_stats["GEOID"].str.len() != 5]
bad_geoids.head()

	GEOID	withdrawal_rate	median_queue_days	total_capacity_mw	project_count
119	1309313261	1.0	1993.0	100.0	1
219	1700917083	1.0	665.0	100.0	1
220	1700917099	1.0	234.0	100.0	1
226	1702917045	1.0	372.0	250.0	1
231	1703917113	1.0	375.0	99.0	1

15. Dropping Multi-county rows

For ease, I decided to drop the relatively few rows that had multiple GEOIDs. This code keeps only rows where the GEOID is exactly 5 characters long — the correct length for U.S. county FIPS codes. It dropped:

the 10-digit concatenated FIPS values the 9-digit weird malformed values the 15-digit triple-county rows the single 19-digit row

Leaving me with a clean dataset of county-level statistics. The next line verified the result by counting how many rows of each GEOID length still exist. Output was, thankfully:

GEOID 5 1434 Name: count, dtype: int64

county_stats = county_stats[county_stats["GEOID"].str.len() == 5]
county_stats["GEOID"].str.len().value_counts()

GEOID
5    1434
Name: count, dtype: int64

16. Load the U.S. county GeoJSON used for choropleth mapping

This step loads the official Plotly county boundaries directly from an online GitHub source, parses the file into a python dictionary, and shows the top-level JSON keys, which were ‘type’ and ‘features.’

This GeoJSON contains: a polygon for each U.S. county the identifier “id” for each county, which matches a 5-digit FIPS code, and is what I used as the join key because it makes it directly compatible with Plotly’s choropleth functions.

import urllib.request
import json

url = "https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json"
with urllib.request.urlopen(url) as response:
    counties_geojson = json.load(response)


list(counties_geojson.keys())

['type', 'features']

Start of Mapping and Anaylsis:

To understand where the U.S. energy transition is stalling, I visualize three county-level outcomes derived from the LBNL Interconnection Queue dataset:

• Withdrawal Rate — the percentage of projects in a county that withdraw from the queue.

• Queue Duration — the median number of days projects spend waiting before withdrawal or commercial operation.

• Total Proposed Capacity (MW) — the total generating capacity proposed in each county.

Each metric is aggregated to the county level and mapped using U.S. county boundaries from a public Plotly GeoJSON dataset. This section checks the merge, renders maps, and interprets the spatial patterns.

17. Visualize county-level withdrawal rates as a choropleth map

This cell creates an interactive U.S. map showing the share of projects withdrawn in each county.

• locations=”GEOID” tells Plotly which county each row refers to.

• featureidkey=”id” tells Plotly to match each row to the “id” field inside the GeoJSON.

• color=”withdrawal_rate” shades counties by the proportion of withdrawn projects.

• The “OrRd” scale emphasizes higher values using darker reds.

• update_geos(fitbounds=”locations”) zooms the map to U.S. counties that appear in the dataset.

This visualization highlights geographic patterns in interconnection queue withdrawals — areas where projects are disproportionately failing to connect. From the map, California and the Northeast seem to have consistently high withdrawal rates, and Texas has lower withdrawal rates.

fig = px.choropleth(
    county_stats,
    geojson=counties_geojson,
    locations="GEOID",  
    color="withdrawal_rate",
    color_continuous_scale="OrRd",
    scope="usa",
    labels={"withdrawal_rate": "Withdrawal Rate"},
    title="County-Level Renewable Energy Project Withdrawal Rates",
)

fig.update_geos(fitbounds="locations", visible=False)
fig.show()

18. Create a median queue duration map

When I tried a median queue duration map with, it appeared that this variable was highly right skewed; most counties have moderate wait times, but a small number have extremely long delays (3000–7000+ days). This skew was making the map appear homogenous, and I realized I would need to try a log scale or another approach to see the granularities between counties.

I tried log-transformed median queue durations across U.S. counties, and had a similar, uniform result.

fig = px.choropleth(
    county_stats,
    geojson=counties_geojson,
    locations="GEOID",
    featureidkey="id",
    color="median_queue_days",
    color_continuous_scale="Viridis",
    scope="usa",
    title="Median Queue Duration",
)

fig.update_geos(fitbounds="locations", visible=False)
fig.show()

county_stats["log_queue_days"] = np.log1p(county_stats["median_queue_days"])
fig = px.choropleth(
    county_stats,
    geojson=counties_geojson,
    locations="GEOID",
    featureidkey="id",
    color="log_queue_days",
    color_continuous_scale="Viridis",
    scope="usa",
    labels={"log_queue_days": "Log(Queue Days + 1)"},
    title="Median Queue Duration (Log Scale)",
)

fig.update_geos(fitbounds="locations", visible=False)
fig.show()

19. Visualize winsorized median queue durations to highlight variation

Through some googling, I found out that queue durations are extremely heavy-tailed: most counties have moderate values, while a few have extremely high ones.

A raw choropleth compresses almost all counties into the lowest color band.

Even a log scale left the bottom 90% visually indistinguishable. By clipping values at the 95th percentile — a method known as winsorization — the map:

• highlights meaningful variation across most counties

• avoids distortion from extreme outliers

• retains interpretability (units remain in days and MW)

The resulting maps better show regional clustering that was invisible before winsorization.

Conversely to the withdrawal map, queue durations appear to be shorter in the Northeast, California, and Southeast, perhaps because those jurisdictions incentivize developers to submit multiple requests for the same project due to uncertainty. That volatility leads to a high withdrawal rate and lower wait time per project.

import numpy as np

# Compute high-end cutoffs
q95_q = county_stats["median_queue_days"].quantile(0.95)
q99_q = county_stats["median_queue_days"].quantile(0.99)

q95_mw = county_stats["total_capacity_mw"].quantile(0.95)
q99_mw = county_stats["total_capacity_mw"].quantile(0.99)

q95_q, q99_q, q95_mw, q99_mw

(np.float64(1760.4499999999994),
 np.float64(4970.970000000152),
 np.float64(3915.579996034998),
 np.float64(10471.615800000007))

county_stats["queue_days_winsor"] = np.minimum(county_stats["median_queue_days"], q95_q)

county_stats["capacity_winsor"] = np.minimum(county_stats["total_capacity_mw"], q95_mw)

fig = px.choropleth(
    county_stats,
    geojson=counties_geojson,
    locations="GEOID",
    featureidkey="id",
    color="queue_days_winsor",
    color_continuous_scale="Viridis",
    scope="usa",
    labels={"queue_days_winsor": "Median Queue Days (Winsorized)"},
    title="Median Queue Duration (95th Percentile Winsorized)",
)

fig.update_geos(fitbounds="locations", visible=False)
fig.update_layout(margin={"r": 0, "t": 50, "l": 0, "b": 0})
fig.show()

iso_wait = (
    queue.groupby("region")
    .agg(
        median_wait_days=("queue_duration_days", "median"),
        mean_wait_days=("queue_duration_days", "mean"),
        project_count=("q_id", "count"),
    )
    .reset_index()
    .sort_values("median_wait_days", ascending=False)
)

iso_wait

	region	median_wait_days	mean_wait_days	project_count
4	NYISO	19661.0	19661.000000	1
6	SPP	16046.5	15815.500000	4
1	ERCOT	1417.0	2077.748344	1359
5	PJM	767.0	976.164943	5899
8	West	690.0	1635.351351	518
3	MISO	571.0	632.771637	959
2	ISO-NE	426.5	862.427052	658
0	CAISO	380.0	828.192621	1843
7	Southeast	325.0	1310.672826	920

Conclusion

This analysis set out to understand where the U.S. energy transition is stalling by examining two measurable outcomes in the LBNL Interconnection Queue dataset: how long projects spend waiting in the queue and how often they ultimately withdraw. By converting project-level timelines into county-level and ISO-level metrics, and by visualizing these patterns geographically, several clear themes emerged.

1. Queue delays vary across geography.
Even after cleaning extreme outliers, median wait times still differ by thousands of days between counties. When mapped, these delays cluster in distinct regions rather than appearing randomly distributed. This suggests that structural, place-based factors—such as transmission congestion, understaffed study processes, or local siting challenges—shape how long projects remain in the queue.

2. Withdrawal rates also show geographic clustering.
Counties with persistent delays strangely seemed to have lower withdrawal rates, complicating my hypothesis that prolonged waiting contributes to project attrition. In retrospect, I wonder if this was an issue with my data analysis. In future projects, I would want to explore using one of the string columns documenting project status, including operation and withdrawal, rather than subtracting the dates and converting them to datetime. In other maps online, ERCOT has the lowest wait times, making me think I had some errors.

3. Transformations were essential to reveal true patterns.
Both queue times and withdrawal rates displayed heavy-tailed distributions. Using log-transformed variables and winsorized values allowed the underlying spatial variation to become visible. Without these transformations, extreme outliers obscure meaningful differences among the majority of counties.

Overall, the hypotheses were unevenly supported:
Counties and ISOs with long queue times don’t always exhibit higher withdrawal rates, though the data shows significant regional clustering for both, suggesting uneven renewables buildout, RTO/ISO differences, state and local policy differences, and transmission bottlenecks. Regardless, it is clear from the datat that the interconnection queue is not functioning uniformly across the United States.

Future research could integrate power flow data, project type (e.g., solar, wind, storage), or transmission upgrade timelines to better explain why certain regions struggle more than others. However, even with the available data, it is clear that interconnection is a major bottleneck in the clean energy transition—and that its impacts are far from evenly distributed.