Analyze U.S. Power Plants with Python
Load real government data to visualize U.S. energy infrastructure with pandas.
Introduction
30 Second Summary
Every time you flip a light switch, electricity flows from one of over 25,000 generators spread across the country. Some burn natural gas, others capture sunlight, and a few split atoms. The mix is shifting fast.
In this project, you will analyze real U.S. government data to uncover how America generates its electricity. You will load, clean, and visualize the official power plant dataset that energy analysts and policymakers actually use.
What You'll Build
A Jupyter Notebook that explores the U.S. Energy Information Administration's Form EIA-860 dataset and produces four publication-ready charts telling the story of U.S. energy infrastructure.
By the end of this project, you'll have:
- A bar chart showing total electricity generation capacity broken down by fuel type.
- A horizontal bar chart of the top 10 states by installed capacity, plus a stacked breakdown of each top state's energy mix.
- A timeline chart revealing how solar and wind installations have surged in recent years.
- Secret Mission: Build a Renewable Energy Scorecard comparing states by their percentage of renewable capacity.
Are there any prerequisites?
You need basic Python knowledge (variables, loops, lists). No prior pandas or data analysis experience is required.
Load and Explore the Generator Data
Your notebook is set up and ready to go. Now it is time to load real data into it.
The EIA-860 Excel file contains thousands of rows, one for each power plant generator in the United States. In this step, you will load that data into a pandas DataFrame and explore its structure to understand what you are working with.
In this step, get ready to:
- Load the generator Excel file into a pandas DataFrame.
- Inspect the data's shape, columns, and first few rows.
- Identify the key columns you will use for analysis and check for missing values.
Load the Excel file into a DataFrame
The EIA-860 Excel file has a quirk. The very first row is a description header, not the actual column names. You need to tell pandas to skip that row so it reads the real column headers correctly.
- In your notebook, create a new cell below your import cell and add this code to load the generator data:
# %% Load the data
df = pd.read_excel(
'3_1_GeneratorY2023.xlsx',
sheet_name='Operable',
skiprows=1
)What does this code do?
pd.read_excel() reads an Excel file into a DataFrame, which is pandas' table-like data structure.
- sheet_name='Operable' tells pandas to read the sheet containing currently operating generators (not proposed or retired ones).
- skiprows=1 skips the first row, which is a description header that would confuse the column names.
- Run the cell by pressing Shift+Enter.
The cell may take a few seconds to execute because it is loading a large Excel file. You should see no output or errors when it finishes.
Seeing a FileNotFoundError?
- Make sure 3_1_GeneratorY2023.xlsx is in the same folder as your notebook file. If you extracted the ZIP elsewhere, move the Excel file next to your notebook.
- Check the filename matches exactly, including capitalization and the year.
Help me fix my FileNotFoundError when loading the EIA-860 Excel file
Explore the data's shape and structure
Before diving into analysis, you need to understand the basic shape of your data. How many rows and columns are there? What are the column names?
- Create a new cell below and add this code to print a summary of the DataFrame:
# %% Explore the data
print(f"Dataset shape: {df.shape}")
print(f"\nColumn names:\n{list(df.columns)}")
print(f"\nFirst few rows:")
df.head()What does this code do?
- df.shape returns the number of rows and columns as a tuple (rows, columns).
- df.columns lists all column names so you can see what data fields are available.
- df.head() shows the first 5 rows so you can see actual data values.
- Run the cell with Shift+Enter.
You should see the dataset has over 25,000 rows and dozens of columns. The table preview shows columns like Plant Code, Plant Name, State, and various technical specs for each generator.
What is a DataFrame?
A DataFrame is pandas' version of a spreadsheet. It stores data in rows and columns, just like Excel. Each row is one generator, and each column is a piece of information about that generator (its state, capacity, fuel type, etc.).
Seeing garbled column names or an empty DataFrame?
- If column names look like data values, you may have forgotten skiprows=1 in your read_excel call. Go back and add it.
- If the DataFrame is empty, double-check that sheet_name='Operable' is spelled exactly right (capital O).
Help me debug my DataFrame loading issue
Identify and inspect the key columns
The dataset has dozens of columns, but you only need a handful for your analysis. The four most important columns tell you where each generator is, how much electricity it can produce, what fuel it uses, and when it was built.
- Create a new cell and add this code to inspect the key columns and check for missing values:
# %% Check key columns
key_columns = ['Plant State', 'Nameplate Capacity (MW)', 'Energy Source 1', 'Operating Year']
print(df[key_columns].head(10))
print(f"\nMissing values in key columns:")
print(df[key_columns].isnull().sum())What does this code do?
- key_columns is a list of the four column names you will use throughout this project.
- df[key_columns].head(10) shows the first 10 rows of just those four columns, so you can verify they contain what you expect.
- .isnull().sum() counts missing values in each column. Knowing where data is missing helps you decide what to clean in the next step.
- Run the cell with Shift+Enter.
You should see a table with state codes (like TX, CA), capacity numbers in megawatts, energy source codes (like NG, SUN, WND), and operating years. Below that, you will see the count of missing values for each column.
What are these energy source codes?
The EIA uses standardized abbreviation codes for fuel types. For example, NG means Natural Gas, SUN means Solar, and WND means Wind.
These codes are not very readable in charts. In the next step, you will map them to human-friendly names like "Natural Gas" and "Solar".
Seeing a KeyError on a column name?
- Column names must match exactly, including spaces and capitalization. Copy the column names from the code above rather than typing them manually.
- If you see a different column name in your df.columns output, your version of the file may have slightly different headers. Use the exact name from your output.
Help me fix my KeyError when accessing columns in the EIA-860 DataFrame
✔️ Awesome, I've got everything!
Great. Make sure you have saved your notebook before moving on.
ⓧ I'd like to double check the full code
Here is the full notebook code up to this point. Make sure your cells match this:
# %% [markdown]
# # U.S. Power Plant Infrastructure Analysis
# Analyzing the EIA-860 dataset to understand America's energy infrastructure.
# %% Import libraries
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
# Set plot style
sns.set_style("whitegrid")
plt.rcParams['figure.figsize'] = (12, 6)
# %% Load the data
df = pd.read_excel(
'3_1_GeneratorY2023.xlsx',
sheet_name='Operable',
skiprows=1
)
# %% Explore the data
print(f"Dataset shape: {df.shape}")
print(f"\nColumn names:\n{list(df.columns)}")
print(f"\nFirst few rows:")
df.head()
# %% Check key columns
key_columns = ['Plant State', 'Nameplate Capacity (MW)', 'Energy Source 1', 'Operating Year']
print(df[key_columns].head(10))
print(f"\nMissing values in key columns:")
print(df[key_columns].isnull().sum())You now have a clear picture of your dataset's structure. You know the four columns that matter most and where the gaps are. Next up, you will clean the data and translate those cryptic energy source codes into names that actually make sense on a chart.
Clean the Data and Map Energy Source Codes
In the previous step, you loaded the EIA-860 dataset and explored its structure. You identified the key columns and spotted missing values.
Raw data is rarely ready for analysis. Before you can build meaningful visualizations, you need to remove incomplete records and translate the EIA's cryptic energy source codes into names that actually make sense on a chart.
In this step, get ready to:
- Drop rows with missing capacity data so calculations are accurate.
- Create a mapping dictionary that translates EIA energy source codes to human-readable fuel type names.
- Add a new Fuel Type column to the DataFrame using the mapping.
Drop rows with missing capacity values
Generators with missing capacity values would skew your totals and averages. Removing them ensures every row contributes real data to your analysis.
- Create a new cell in your notebook below the existing cells, then add this code:
# %% Clean the data
df = df.dropna(subset=['Nameplate Capacity (MW)'])What does this code do?
The .dropna(subset=['Nameplate Capacity (MW)']) method removes any row where the capacity column is empty. The subset parameter targets only that one column, keeping rows that are missing other values intact.
- Run the cell by pressing Shift+Enter.
No output appears, which is expected. The DataFrame now has fewer rows because generators with missing capacity data have been silently removed.
Seeing a KeyError?
Make sure the column name is exactly Nameplate Capacity (MW) with the parentheses and spaces. Column names in this dataset are case-sensitive and include special characters.
Also confirm you ran all previous cells first. If you restarted your notebook kernel, run every cell from the top to reload the data.
Help me debug a KeyError when using dropna on my DataFrame.
Map energy source codes to human-readable names
The EIA uses short codes like NG, SUN, and BIT to identify energy sources. These are useful for compact storage but meaningless on a chart.
You will create a dictionary that translates each code into a readable category like Natural Gas, Solar, or Coal. Some fuels have multiple codes that map to the same category (for example, BIT, SUB, and LIG are all subtypes of coal).
- Create a new cell below the cleaning cell, then add this code to define the mapping dictionary and apply it:
# %% Map energy source codes to readable names
energy_source_map = {
'NG': 'Natural Gas',
'SUN': 'Solar',
'WND': 'Wind',
'WAT': 'Conventional Hydroelectric',
'BIT': 'Coal',
'SUB': 'Coal',
'LIG': 'Coal',
'RC': 'Coal',
'NUC': 'Nuclear',
'DFO': 'Petroleum',
'RFO': 'Petroleum',
'KER': 'Petroleum',
'JF': 'Petroleum',
'WO': 'Petroleum',
'PC': 'Petroleum',
'WDS': 'Biomass',
'WDL': 'Biomass',
'AB': 'Biomass',
'MSW': 'Biomass',
'OBS': 'Biomass',
'BLQ': 'Biomass',
'LFG': 'Biomass',
'OBG': 'Biomass',
'SLW': 'Biomass',
'GEO': 'Geothermal',
'MWH': 'Energy Storage',
'PUR': 'Purchased Steam',
'WH': 'Waste Heat',
'OTH': 'Other',
}
df['Fuel Type'] = df['Energy Source 1'].map(energy_source_map).fillna('Other')What does this code do?
The energy_source_map dictionary maps every EIA energy source code to a human-readable fuel type category. Multiple codes can point to the same category (like four coal subtypes all mapping to "Coal").
The .map() method looks up each value in the Energy Source 1 column and replaces it with the corresponding dictionary value. The .fillna('Other') catches any codes not in the dictionary and labels them as "Other" instead of leaving them blank.
- Run the cell by pressing Shift+Enter.
No output appears. Your DataFrame now has a new Fuel Type column that translates cryptic codes into readable names like Natural Gas, Solar, and Wind. You will use this column for every chart in the next steps.
Seeing NaN values in Fuel Type?
If you see many NaN values in your new column, the .fillna('Other') should handle them. If it is not working, make sure the entire cell ran without errors. Check that the last line df['Fuel Type'] = ... is included in the same cell as the dictionary.
If you see a NameError for df, you need to re-run the earlier cells that load and clean the data first.
Help me fix my energy source mapping in pandas.
Why group multiple codes into one category?
The EIA tracks fuel types at a very granular level. For example, coal has separate codes for bituminous (BIT), subbituminous (SUB), lignite (LIG), and refined coal (RC). For a high-level infrastructure analysis, these distinctions are noise.
Grouping them into a single "Coal" category gives you cleaner charts with fewer bars. You can always go back to the raw codes if you need finer detail later.
✔️ Awesome, I've got everything!
Great. Double check you have saved your notebook file before moving on.
ⓧ I'd like to double check the full code
Here is the full notebook code up to this point. Compare your cells against this:
# %% [markdown]
# # U.S. Power Plant Infrastructure Analysis
# Analyzing the EIA-860 dataset to understand America's energy infrastructure.
# %% Import libraries
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
# Set plot style
sns.set_style("whitegrid")
plt.rcParams['figure.figsize'] = (12, 6)
# %% Load the data
df = pd.read_excel(
'3_1_GeneratorY2023.xlsx',
sheet_name='Operable',
skiprows=1
)
# %% Explore the data
print(f"Dataset shape: {df.shape}")
print(f"\nColumn names:\n{list(df.columns)}")
print(f"\nFirst few rows:")
df.head()
# %% Check key columns
key_columns = ['Plant State', 'Nameplate Capacity (MW)', 'Energy Source 1', 'Operating Year']
print(df[key_columns].head(10))
print(f"\nMissing values in key columns:")
print(df[key_columns].isnull().sum())
# %% Clean the data
df = df.dropna(subset=['Nameplate Capacity (MW)'])
# %% Map energy source codes to readable names
energy_source_map = {
'NG': 'Natural Gas',
'SUN': 'Solar',
'WND': 'Wind',
'WAT': 'Conventional Hydroelectric',
'BIT': 'Coal',
'SUB': 'Coal',
'LIG': 'Coal',
'RC': 'Coal',
'NUC': 'Nuclear',
'DFO': 'Petroleum',
'RFO': 'Petroleum',
'KER': 'Petroleum',
'JF': 'Petroleum',
'WO': 'Petroleum',
'PC': 'Petroleum',
'WDS': 'Biomass',
'WDL': 'Biomass',
'AB': 'Biomass',
'MSW': 'Biomass',
'OBS': 'Biomass',
'BLQ': 'Biomass',
'LFG': 'Biomass',
'OBG': 'Biomass',
'SLW': 'Biomass',
'GEO': 'Geothermal',
'MWH': 'Energy Storage',
'PUR': 'Purchased Steam',
'WH': 'Waste Heat',
'OTH': 'Other',
}
df['Fuel Type'] = df['Energy Source 1'].map(energy_source_map).fillna('Other')Your data is now clean and labeled with readable fuel type names. Next up, you will use these labels to build your first chart showing total U.S. generation capacity broken down by fuel type.
Visualize Capacity by Fuel Type
In the previous step, you cleaned the dataset and mapped cryptic energy source codes into readable fuel type names. Your DataFrame is now ready to answer real questions about U.S. energy infrastructure.
The first question worth answering: which fuel types generate the most electricity capacity in America? To find out, you will aggregate capacity across thousands of generators and plot the results as a bar chart.
In this step, get ready to:
- Use groupby to sum total capacity for each fuel type.
- Create a bar chart showing capacity by fuel type, sorted from largest to smallest.
- Format the chart with labels, a title, and rotated x-axis text.
Build a bar chart of capacity by fuel type
The groupby method in pandas splits your DataFrame into groups based on a column, then applies an aggregation (like sum()) to each group. This is how you go from 25,000+ individual generator rows to a single total per fuel type.
- In your notebook, create a new cell below your existing code and paste the following:
# %% Visualization 1: Total capacity by fuel type
capacity_by_fuel = df.groupby('Fuel Type')['Nameplate Capacity (MW)'].sum().sort_values(ascending=False)
fig, ax = plt.subplots(figsize=(12, 6))
capacity_by_fuel.plot(kind='bar', ax=ax, color='steelblue', edgecolor='black')
ax.set_title('U.S. Electricity Generation Capacity by Fuel Type', fontsize=14, fontweight='bold')
ax.set_xlabel('Fuel Type', fontsize=12)
ax.set_ylabel('Nameplate Capacity (MW)', fontsize=12)
ax.set_xticklabels(ax.get_xticklabels(), rotation=45, ha='right')
plt.tight_layout()
plt.savefig('capacity_by_fuel_type.png', dpi=150, bbox_inches='tight')
plt.show()What does this code do?
This cell does three things: aggregates the data, builds the chart, and saves it as an image.
- df.groupby('Fuel Type')['Nameplate Capacity (MW)'].sum() groups every generator by its fuel type and sums the capacity within each group. The .sort_values(ascending=False) puts the largest fuel type first.
- plt.subplots() creates a figure and axes object. The .plot(kind='bar') call on the pandas Series draws a vertical bar chart directly onto those axes.
- ax.set_xticklabels(..., rotation=45, ha='right') tilts the fuel type labels so they don't overlap each other.
- plt.savefig() exports the chart as a PNG image at 150 dpi. plt.show() displays it inline in your notebook.
- Run the cell by pressing Shift+Enter.
- You should see a bar chart appear below the cell with Natural Gas as the tallest bar, followed by other fuel types in descending order.
What should I see?
Your chart should show Natural Gas dominating U.S. generation capacity, with Coal and Wind in second and third. The x-axis labels are tilted at 45 degrees for readability, and the bars are steel blue with black edges.
Chart not appearing?
- Make sure you ran all previous cells first. The df variable with the Fuel Type column must already exist in memory.
- If you see a NameError: name 'df' is not defined error, restart your kernel and run all cells from top to bottom using **Run All**.
- If the chart appears but looks blank, double check that the Fuel Type column has values (not all NaN). Run df['Fuel Type'].value_counts() in a new cell to check.
Why isn't my bar chart showing up in Jupyter?
✔️ Awesome, I've got everything!
Great. Double check you have saved your notebook before moving on.
ⓧ I'd like to double check the full code
Here is the full notebook code up to this point:
# %% [markdown]
# # U.S. Power Plant Infrastructure Analysis
# Analyzing the EIA-860 dataset to understand America's energy infrastructure.
# %% Import libraries
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
# Set plot style
sns.set_style("whitegrid")
plt.rcParams['figure.figsize'] = (12, 6)
# %% Load the data
df = pd.read_excel(
'3_1_GeneratorY2023.xlsx',
sheet_name='Operable',
skiprows=1
)
# %% Explore the data
print(f"Dataset shape: {df.shape}")
print(f"\nColumn names:\n{list(df.columns)}")
print(f"\nFirst few rows:")
df.head()
# %% Check key columns
key_columns = ['Plant State', 'Nameplate Capacity (MW)', 'Energy Source 1', 'Operating Year']
print(df[key_columns].head(10))
print(f"\nMissing values in key columns:")
print(df[key_columns].isnull().sum())
# %% Clean the data
df = df.dropna(subset=['Nameplate Capacity (MW)'])
# %% Map energy source codes to readable names
energy_source_map = {
'NG': 'Natural Gas',
'SUN': 'Solar',
'WND': 'Wind',
'WAT': 'Conventional Hydroelectric',
'BIT': 'Coal',
'SUB': 'Coal',
'LIG': 'Coal',
'RC': 'Coal',
'NUC': 'Nuclear',
'DFO': 'Petroleum',
'RFO': 'Petroleum',
'KER': 'Petroleum',
'JF': 'Petroleum',
'WO': 'Petroleum',
'PC': 'Petroleum',
'WDS': 'Biomass',
'WDL': 'Biomass',
'AB': 'Biomass',
'MSW': 'Biomass',
'OBS': 'Biomass',
'BLQ': 'Biomass',
'LFG': 'Biomass',
'OBG': 'Biomass',
'SLW': 'Biomass',
'GEO': 'Geothermal',
'MWH': 'Energy Storage',
'PUR': 'Purchased Steam',
'WH': 'Waste Heat',
'OTH': 'Other',
}
df['Fuel Type'] = df['Energy Source 1'].map(energy_source_map).fillna('Other')
# %% Visualization 1: Total capacity by fuel type
capacity_by_fuel = df.groupby('Fuel Type')['Nameplate Capacity (MW)'].sum().sort_values(ascending=False)
fig, ax = plt.subplots(figsize=(12, 6))
capacity_by_fuel.plot(kind='bar', ax=ax, color='steelblue', edgecolor='black')
ax.set_title('U.S. Electricity Generation Capacity by Fuel Type', fontsize=14, fontweight='bold')
ax.set_xlabel('Fuel Type', fontsize=12)
ax.set_ylabel('Nameplate Capacity (MW)', fontsize=12)
ax.set_xticklabels(ax.get_xticklabels(), rotation=45, ha='right')
plt.tight_layout()
plt.savefig('capacity_by_fuel_type.png', dpi=150, bbox_inches='tight')
plt.show()You just turned 25,000+ rows of raw generator data into a clear visual story. Next up, you will dig into which states have the most generation capacity and explore how the energy mix has shifted over time.
Analyze Top States and Build a Timeline
Your fuel type bar chart tells you what powers the U.S. grid. Now the question is: where is all that capacity located, and when was it built?
In this step, you will drill into state-level data to find which states have the most generation capacity. You will also build a timeline that reveals how the energy mix has shifted over the last 50 years.
In this step, get ready to:
- Create a horizontal bar chart of the top 10 states by installed capacity.
- Build a stacked bar chart showing each top state's energy mix.
- Plot a timeline of annual capacity additions to reveal the energy transition.
Find the top 10 states by generation capacity
The Plant State column contains the two-letter state code for each generator. By grouping on this column and summing capacity, you can rank every state by its total installed power.
- Create a new cell in your notebook and add this code to group by state, find the top 10, and plot a horizontal bar chart:
# %% Visualization 2: Top 10 states by capacity
capacity_by_state = df.groupby('Plant State')['Nameplate Capacity (MW)'].sum().sort_values(ascending=False)
top_10_states = capacity_by_state.head(10)
fig, ax = plt.subplots(figsize=(10, 6))
top_10_states.sort_values().plot(kind='barh', ax=ax, color='darkorange', edgecolor='black')
ax.set_title('Top 10 States by Installed Generation Capacity', fontsize=14, fontweight='bold')
ax.set_xlabel('Nameplate Capacity (MW)', fontsize=12)
ax.set_ylabel('State', fontsize=12)
plt.tight_layout()
plt.savefig('top_10_states.png', dpi=150, bbox_inches='tight')
plt.show()What does this code do?
capacity_by_state groups all generators by their state, sums the megawatt capacity for each state, and sorts from largest to smallest.
top_10_states.sort_values() re-sorts ascending before plotting so the longest bar appears at the top of the horizontal chart. This makes the chart read naturally from top to bottom.
- Run the cell.
- You should see a horizontal bar chart with state abbreviations on the y-axis and capacity on the x-axis. Texas (TX) should appear at the top with the longest bar.
Chart looks empty or states are missing?
- Make sure you ran the earlier cells that clean the data and create the Fuel Type column. The cell references the cleaned df variable.
- Check that you typed Plant State with a capital P and S, and the exact spacing shown above.
Help me debug my state capacity chart
Break down the energy mix of the top 5 states
Knowing which states have the most capacity is useful, but it does not tell you how that capacity is distributed across fuel types. A stacked bar chart shows each state's total capacity broken into colored segments for each fuel.
- Create a new cell and add this code to filter the top 5 states and plot their energy mix as a stacked bar chart:
# %% Visualization 3: Energy mix of top 5 states
top_5_states = capacity_by_state.head(5).index.tolist()
top_5_data = df[df['Plant State'].isin(top_5_states)]
state_fuel_mix = top_5_data.groupby(['Plant State', 'Fuel Type'])['Nameplate Capacity (MW)'].sum().unstack(fill_value=0)
fig, ax = plt.subplots(figsize=(12, 6))
state_fuel_mix.plot(kind='bar', stacked=True, ax=ax, colormap='tab10')
ax.set_title('Energy Mix of Top 5 States by Capacity', fontsize=14, fontweight='bold')
ax.set_xlabel('State', fontsize=12)
ax.set_ylabel('Nameplate Capacity (MW)', fontsize=12)
ax.legend(title='Fuel Type', bbox_to_anchor=(1.05, 1), loc='upper left')
ax.set_xticklabels(ax.get_xticklabels(), rotation=0)
plt.tight_layout()
plt.savefig('top_5_states_mix.png', dpi=150, bbox_inches='tight')
plt.show()What does this code do?
- capacity_by_state.head(5).index.tolist() grabs the state abbreviations for the top 5 states you already calculated.
- .groupby(['Plant State', 'Fuel Type']).sum().unstack() creates a pivot table with states as rows and fuel types as columns. This is the shape matplotlib needs for a stacked bar chart.
- bbox_to_anchor=(1.05, 1) moves the legend outside the chart so it does not overlap the bars.
- Run the cell.
- You should see a stacked bar chart with 5 state abbreviations on the x-axis. Each bar is divided into colored segments representing different fuel types, with a legend on the right.
Legend is overlapping or bars look wrong?
- If the legend overlaps the chart, make sure you have the plt.tight_layout() call after setting the legend position.
- If you see a NameError for capacity_by_state, go back and run the top 10 states cell first. This cell depends on that variable.
Help me fix my stacked bar chart
Build a timeline of capacity additions
The Operating Year column records when each generator began commercial operation. By plotting how much capacity was added each year by fuel type, you can see the energy transition unfold over time.
- Create a new cell and add this code to filter generators built after 1970 and plot annual capacity additions as a line chart:
# %% Visualization 4: Capacity additions over time
df_timeline = df[df['Operating Year'] >= 1970].copy()
df_timeline['Operating Year'] = df_timeline['Operating Year'].astype(int)
major_fuels = ['Natural Gas', 'Coal', 'Solar', 'Wind', 'Nuclear']
df_timeline_major = df_timeline[df_timeline['Fuel Type'].isin(major_fuels)]
additions_by_year = df_timeline_major.groupby(['Operating Year', 'Fuel Type'])['Nameplate Capacity (MW)'].sum().unstack(fill_value=0)
fig, ax = plt.subplots(figsize=(14, 6))
for fuel in major_fuels:
if fuel in additions_by_year.columns:
ax.plot(additions_by_year.index, additions_by_year[fuel], label=fuel, linewidth=2)
ax.set_title('Annual Capacity Additions by Fuel Type (1970-Present)', fontsize=14, fontweight='bold')
ax.set_xlabel('Year', fontsize=12)
ax.set_ylabel('Capacity Added (MW)', fontsize=12)
ax.legend(title='Fuel Type', fontsize=10)
plt.tight_layout()
plt.savefig('capacity_additions_timeline.png', dpi=150, bbox_inches='tight')
plt.show()
print("\nAnalysis complete! All charts saved.")What does this code do?
- df[df['Operating Year'] >= 1970].copy() filters to generators built from 1970 onward. The EIA data is most consistent from this period.
- major_fuels limits the chart to five key fuel types so the lines are readable. Including every fuel type would make a cluttered mess.
- The for loop plots each fuel as its own line, giving you a clear comparison of how each energy source grew or shrank year by year.
- Run the cell.
- You should see a line chart with five colored lines. Look for the Solar and Wind lines surging upward in recent years while Coal drops off after the 2000s.
Lines are flat or missing?
- If a line does not appear, check that your Fuel Type mapping in the earlier cell correctly maps to the exact strings in the major_fuels list (e.g. 'Natural Gas', not 'natural gas').
- If you see a TypeError about comparing integers and floats, the .astype(int) line handles this. Make sure it is present after the filtering line.
Help me debug my timeline chart
What story does this timeline tell?
The timeline reveals the U.S. energy transition in action. Coal construction peaked in the 1970s-80s and dropped to nearly zero after 2010. Natural Gas surged in the late 1990s and 2000s.
Solar and Wind barely existed before 2005 but now dominate new capacity additions. This is the same dataset that policymakers and energy analysts use to track these shifts.
✔️ Awesome, I've got everything!
Great. Double check you have saved your notebook file.
ⓧ I'd like to double check the full code
Here is the complete notebook up to this point. Compare it against your file to spot any differences.
# %% [markdown]
# # U.S. Power Plant Infrastructure Analysis
# Analyzing the EIA-860 dataset to understand America's energy infrastructure.
# %% Import libraries
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
# Set plot style
sns.set_style("whitegrid")
plt.rcParams['figure.figsize'] = (12, 6)
# %% Load the data
df = pd.read_excel(
'3_1_GeneratorY2023.xlsx',
sheet_name='Operable',
skiprows=1
)
# %% Explore the data
print(f"Dataset shape: {df.shape}")
print(f"\nColumn names:\n{list(df.columns)}")
print(f"\nFirst few rows:")
df.head()
# %% Check key columns
key_columns = ['Plant State', 'Nameplate Capacity (MW)', 'Energy Source 1', 'Operating Year']
print(df[key_columns].head(10))
print(f"\nMissing values in key columns:")
print(df[key_columns].isnull().sum())
# %% Clean the data
df = df.dropna(subset=['Nameplate Capacity (MW)'])
# %% Map energy source codes to readable names
energy_source_map = {
'NG': 'Natural Gas',
'SUN': 'Solar',
'WND': 'Wind',
'WAT': 'Conventional Hydroelectric',
'BIT': 'Coal',
'SUB': 'Coal',
'LIG': 'Coal',
'RC': 'Coal',
'NUC': 'Nuclear',
'DFO': 'Petroleum',
'RFO': 'Petroleum',
'KER': 'Petroleum',
'JF': 'Petroleum',
'WO': 'Petroleum',
'PC': 'Petroleum',
'WDS': 'Biomass',
'WDL': 'Biomass',
'AB': 'Biomass',
'MSW': 'Biomass',
'OBS': 'Biomass',
'BLQ': 'Biomass',
'LFG': 'Biomass',
'OBG': 'Biomass',
'SLW': 'Biomass',
'GEO': 'Geothermal',
'MWH': 'Energy Storage',
'PUR': 'Purchased Steam',
'WH': 'Waste Heat',
'OTH': 'Other',
}
df['Fuel Type'] = df['Energy Source 1'].map(energy_source_map).fillna('Other')
# %% Visualization 1: Total capacity by fuel type
capacity_by_fuel = df.groupby('Fuel Type')['Nameplate Capacity (MW)'].sum().sort_values(ascending=False)
fig, ax = plt.subplots(figsize=(12, 6))
capacity_by_fuel.plot(kind='bar', ax=ax, color='steelblue', edgecolor='black')
ax.set_title('U.S. Electricity Generation Capacity by Fuel Type', fontsize=14, fontweight='bold')
ax.set_xlabel('Fuel Type', fontsize=12)
ax.set_ylabel('Nameplate Capacity (MW)', fontsize=12)
ax.set_xticklabels(ax.get_xticklabels(), rotation=45, ha='right')
plt.tight_layout()
plt.savefig('capacity_by_fuel_type.png', dpi=150, bbox_inches='tight')
plt.show()
# %% Visualization 2: Top 10 states by capacity
capacity_by_state = df.groupby('Plant State')['Nameplate Capacity (MW)'].sum().sort_values(ascending=False)
top_10_states = capacity_by_state.head(10)
fig, ax = plt.subplots(figsize=(10, 6))
top_10_states.sort_values().plot(kind='barh', ax=ax, color='darkorange', edgecolor='black')
ax.set_title('Top 10 States by Installed Generation Capacity', fontsize=14, fontweight='bold')
ax.set_xlabel('Nameplate Capacity (MW)', fontsize=12)
ax.set_ylabel('State', fontsize=12)
plt.tight_layout()
plt.savefig('top_10_states.png', dpi=150, bbox_inches='tight')
plt.show()
# %% Visualization 3: Energy mix of top 5 states
top_5_states = capacity_by_state.head(5).index.tolist()
top_5_data = df[df['Plant State'].isin(top_5_states)]
state_fuel_mix = top_5_data.groupby(['Plant State', 'Fuel Type'])['Nameplate Capacity (MW)'].sum().unstack(fill_value=0)
fig, ax = plt.subplots(figsize=(12, 6))
state_fuel_mix.plot(kind='bar', stacked=True, ax=ax, colormap='tab10')
ax.set_title('Energy Mix of Top 5 States by Capacity', fontsize=14, fontweight='bold')
ax.set_xlabel('State', fontsize=12)
ax.set_ylabel('Nameplate Capacity (MW)', fontsize=12)
ax.legend(title='Fuel Type', bbox_to_anchor=(1.05, 1), loc='upper left')
ax.set_xticklabels(ax.get_xticklabels(), rotation=0)
plt.tight_layout()
plt.savefig('top_5_states_mix.png', dpi=150, bbox_inches='tight')
plt.show()
# %% Visualization 4: Capacity additions over time
df_timeline = df[df['Operating Year'] >= 1970].copy()
df_timeline['Operating Year'] = df_timeline['Operating Year'].astype(int)
major_fuels = ['Natural Gas', 'Coal', 'Solar', 'Wind', 'Nuclear']
df_timeline_major = df_timeline[df_timeline['Fuel Type'].isin(major_fuels)]
additions_by_year = df_timeline_major.groupby(['Operating Year', 'Fuel Type'])['Nameplate Capacity (MW)'].sum().unstack(fill_value=0)
fig, ax = plt.subplots(figsize=(14, 6))
for fuel in major_fuels:
if fuel in additions_by_year.columns:
ax.plot(additions_by_year.index, additions_by_year[fuel], label=fuel, linewidth=2)
ax.set_title('Annual Capacity Additions by Fuel Type (1970-Present)', fontsize=14, fontweight='bold')
ax.set_xlabel('Year', fontsize=12)
ax.set_ylabel('Capacity Added (MW)', fontsize=12)
ax.legend(title='Fuel Type', fontsize=10)
plt.tight_layout()
plt.savefig('capacity_additions_timeline.png', dpi=150, bbox_inches='tight')
plt.show()
print("\nAnalysis complete! All charts saved.")Secret mission
Build a Renewable Energy Scorecard
You've analyzed U.S. energy infrastructure by fuel type, state, and timeline. But which states are actually leading the clean energy transition? Build a scorecard that ranks states by their percentage of renewable generation capacity.
Clean Up Your Resources
Clean Up Your Resources
This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your files for future reference, or delete them to free up disk space.
Resources you used:
- Python packages: pandas, matplotlib, seaborn, openpyxl.
- EIA-860 dataset ZIP file and extracted Excel files.
- Jupyter Notebook (energy_analysis.ipynb).
- Generated chart PNG files (capacity_by_fuel_type.png, top_10_states.png, top_5_states_mix.png, capacity_additions_timeline.png).
Keep everything running
No action needed. Choose this if you want to keep exploring the data or building on your analysis.
- Your notebook and data files stay on your Desktop.
- Your installed Python packages remain available for future projects.
Pause - I'll come back to this later
Free up disk space by removing the large dataset files while keeping your notebook for reference.
- Delete the EIA-860 ZIP file and extracted Excel files from your project folder. These can be re-downloaded from the EIA website any time.
- Keep your Jupyter Notebook and generated chart images. These are small and preserve your work.
Delete - I don't want to use this again
Remove all project files and start fresh.
- Delete the entire project folder from your Desktop.
- Optionally uninstall the Python packages by running:
pip uninstall pandas matplotlib seaborn openpyxl -yKeep the packages
pandas, matplotlib, and seaborn are widely used in data analysis projects. You may want to keep them installed even if you delete this project's files.
Nice Work!
Nice Work!
You just analyzed the entire U.S. power generation fleet using real government data. From a raw Excel file with over 25,000 records, you built a complete data analysis notebook that tells the story of America's energy infrastructure.
You've learned how to:
- Load, clean, and transform real-world government data using pandas and the EIA-860 dataset.
- Create bar charts and line charts with matplotlib to visualize capacity by fuel type, top states, and energy mix.
- Use groupby aggregation and data filtering to build a timeline revealing the shift from coal to renewables.
- Built a Renewable Energy Scorecard ranking states by their percentage of clean energy capacity.
Ready to quiz yourself?