"""Functions for pulling FERC Form 1 data out of the PUDL DB."""
import logging
import numpy as np
import pandas as pd
import pudl
from pudl.metadata.fields import apply_pudl_dtypes
[docs]logger = logging.getLogger(__name__)
[docs]def plants_utils_ferc1(pudl_engine):
"""
Build a dataframe of useful FERC Plant & Utility information.
Args:
pudl_engine (sqlalchemy.engine.Engine): Engine for connecting to the
PUDL database.
Returns:
pandas.DataFrame: A DataFrame containing useful FERC Form 1 Plant and
Utility information.
"""
pu_df = pd.merge(
pd.read_sql("plants_ferc1", pudl_engine),
pd.read_sql("utilities_ferc1", pudl_engine),
on="utility_id_ferc1")
return pu_df
[docs]def plants_steam_ferc1(pudl_engine):
"""Select and joins some useful fields from the FERC Form 1 steam table.
Select the FERC Form 1 steam plant table entries, add in the reporting
utility's name, and the PUDL ID for the plant and utility for readability
and integration with other tables that have PUDL IDs.
Also calculates ``capacity_factor`` (based on ``net_generation_mwh`` &
``capacity_mw``)
Args:
pudl_engine (sqlalchemy.engine.Engine): Engine for connecting to the
PUDL database.
Returns:
pandas.DataFrame: A DataFrame containing useful fields from the FERC
Form 1 steam table.
"""
steam_df = (
pd.read_sql("plants_steam_ferc1", pudl_engine)
.merge(plants_utils_ferc1(pudl_engine),
on=['utility_id_ferc1', 'plant_name_ferc1'], how='left')
.assign(
capacity_factor=lambda x:
x.net_generation_mwh / (8760 * x.capacity_mw),
opex_fuel_per_mwh=lambda x: x.opex_fuel / x.net_generation_mwh,
opex_nonfuel=lambda x: x.opex_production_total - x.opex_fuel,
opex_nonfuel_per_mwh=lambda x: np.where(
x.net_generation_mwh > 0,
x.opex_nonfuel / x.net_generation_mwh,
pd.NA)
)
.pipe(
pudl.helpers.organize_cols,
['report_year', 'utility_id_ferc1', 'utility_id_pudl',
'utility_name_ferc1', 'plant_id_pudl', 'plant_id_ferc1',
'plant_name_ferc1']
)
)
return steam_df
[docs]def fuel_ferc1(pudl_engine):
"""Pull a useful dataframe related to FERC Form 1 fuel information.
This function pulls the FERC Form 1 fuel data, and joins in the name of the
reporting utility, as well as the PUDL IDs for that utility and the plant,
allowing integration with other PUDL tables.
Useful derived values include:
* ``fuel_consumed_mmbtu`` (total fuel heat content consumed)
* ``fuel_consumed_total_cost`` (total cost of that fuel)
Args:
pudl_engine (sqlalchemy.engine.Engine): Engine for connecting to the
PUDL database.
Returns:
pandas.DataFrame: A DataFrame containing useful FERC Form 1 fuel
information.
"""
fuel_df = (
pd.read_sql("fuel_ferc1", pudl_engine).
assign(fuel_consumed_mmbtu=lambda x: x["fuel_consumed_units"] * x["fuel_mmbtu_per_unit"],
fuel_consumed_total_cost=lambda x: x["fuel_consumed_units"] * x["fuel_cost_per_unit_burned"]).
merge(plants_utils_ferc1(pudl_engine),
on=['utility_id_ferc1', 'plant_name_ferc1']).
pipe(pudl.helpers.organize_cols, ['report_year',
'utility_id_ferc1',
'utility_id_pudl',
'utility_name_ferc1',
'plant_id_pudl',
'plant_name_ferc1'])
)
return fuel_df
[docs]def fuel_by_plant_ferc1(pudl_engine, thresh=0.5):
"""Summarize FERC fuel data by plant for output.
This is mostly a wrapper around
:func:`pudl.transform.ferc1.fuel_by_plant_ferc1`
which calculates some summary values on a per-plant basis (as indicated
by ``utility_id_ferc1`` and ``plant_name_ferc1``) related to fuel
consumption.
Args:
pudl_engine (sqlalchemy.engine.Engine): Engine for connecting to the
PUDL database.
thresh (float): Minimum fraction of fuel (cost and mmbtu) required in
order for a plant to be assigned a primary fuel. Must be between
0.5 and 1.0. default value is 0.5.
Returns:
pandas.DataFrame: A DataFrame with fuel use summarized by plant.
"""
fbp_df = (
pd.read_sql_table('fuel_ferc1', pudl_engine)
.pipe(pudl.transform.ferc1.fuel_by_plant_ferc1, thresh=thresh)
.merge(plants_utils_ferc1(pudl_engine),
on=['utility_id_ferc1', 'plant_name_ferc1'])
.pipe(pudl.helpers.organize_cols, ['report_year',
'utility_id_ferc1',
'utility_id_pudl',
'utility_name_ferc1',
'plant_id_pudl',
'plant_name_ferc1'])
)
return fbp_df
[docs]def plants_small_ferc1(pudl_engine):
"""Pull a useful dataframe related to the FERC Form 1 small plants."""
plants_small_df = (
pd.read_sql_table("plants_small_ferc1", pudl_engine)
.merge(plants_utils_ferc1(pudl_engine),
on=["utility_id_ferc1", "plant_name_ferc1"], how='left')
.pipe(pudl.helpers.organize_cols, ['report_year',
'utility_id_ferc1',
'utility_id_pudl',
'utility_name_ferc1',
'plant_id_pudl',
'plant_name_ferc1',
"record_id"])
)
return plants_small_df
[docs]def plants_hydro_ferc1(pudl_engine):
"""Pull a useful dataframe related to the FERC Form 1 hydro plants."""
plants_hydro_df = (
pd.read_sql_table("plants_hydro_ferc1", pudl_engine)
.merge(plants_utils_ferc1(pudl_engine),
on=["utility_id_ferc1", "plant_name_ferc1"], how='left')
.assign(capacity_factor=lambda x: (
x.net_generation_mwh / (8760 * x.capacity_mw)))
.pipe(pudl.helpers.organize_cols, ["report_year",
"utility_id_ferc1",
"utility_id_pudl",
"utility_name_ferc1",
"plant_name_ferc1",
"record_id"])
)
return plants_hydro_df
[docs]def plants_pumped_storage_ferc1(pudl_engine):
"""Pull a dataframe of FERC Form 1 Pumped Storage plant data."""
pumped_storage_df = (
pd.read_sql_table("plants_pumped_storage_ferc1", pudl_engine)
.merge(pudl.output.ferc1.plants_utils_ferc1(pudl_engine),
on=["utility_id_ferc1", "plant_name_ferc1"], how='left')
.assign(capacity_factor=lambda x:
x.net_generation_mwh / (8760 * x.capacity_mw))
.pipe(pudl.helpers.organize_cols, ["report_year",
"utility_id_ferc1",
"utility_id_pudl",
"utility_name_ferc1",
"plant_name_ferc1",
"record_id"])
)
return pumped_storage_df
[docs]def purchased_power_ferc1(pudl_engine):
"""Pull a useful dataframe of FERC Form 1 Purchased Power data."""
purchased_power_df = (
pd.read_sql_table("purchased_power_ferc1", pudl_engine)
.merge(pd.read_sql_table("utilities_ferc1", pudl_engine),
on="utility_id_ferc1")
.pipe(pudl.helpers.organize_cols, ["report_year",
"utility_id_ferc1",
"utility_id_pudl",
"utility_name_ferc1",
"seller_name",
"record_id"])
)
return purchased_power_df
[docs]def plant_in_service_ferc1(pudl_engine):
"""Pull a dataframe of FERC Form 1 Electric Plant in Service data."""
pis_df = (
pd.read_sql_table("plant_in_service_ferc1", pudl_engine)
.merge(pd.read_sql_table("utilities_ferc1", pudl_engine),
on="utility_id_ferc1")
.pipe(pudl.helpers.organize_cols, ["report_year",
"utility_id_ferc1",
"utility_id_pudl",
"utility_name_ferc1",
"record_id",
"amount_type"])
)
return pis_df
[docs]def all_plants_ferc1(pudl_engine):
"""Combine the steam, small generators, hydro, and pumped storage tables.
While this table may have many purposes, the main one is to prepare it for
integration with the EIA Master Unit List (MUL). All subtables included in this
output table must have pudl ids. Table prepping involves ensuring that
the individual tables can merge correctly (like columns have the same name)
both with each other and the EIA MUL.
"""
logger.info("loading steam table")
steam_df = plants_steam_ferc1(pudl_engine)
logger.info("loading small gens table")
small_df = plants_small_ferc1(pudl_engine)
logger.info("loading hydro table")
hydro_df = plants_hydro_ferc1(pudl_engine)
logger.info("loading pumped storage table")
pump_df = plants_pumped_storage_ferc1(pudl_engine)
# Prep steam table
logger.info("prepping steam table")
steam_df = (
steam_df.rename(columns={'opex_plants': 'opex_plant'})
.pipe(apply_pudl_dtypes, group="ferc1")
)
# Prep hydro tables (Add this to the meta data later)
logger.info("prepping hydro tables")
hydro_df = hydro_df.rename(columns={'project_num': 'ferc_license_id'})
pump_df = pump_df.rename(columns={'project_num': 'ferc_license_id'})
# Combine all the tables together
logger.info("combining all tables")
all_plants_df = (
pd.concat([steam_df, small_df, hydro_df, pump_df])
.rename(columns={
'fuel_cost': 'total_fuel_cost',
'fuel_mmbtu': 'total_mmbtu',
'opex_fuel_per_mwh': 'fuel_cost_per_mwh',
'primary_fuel_by_mmbtu': 'fuel_type_code_pudl'})
.replace({'': np.nan})
)
return all_plants_df