def createTable(df: pd.DataFrame,
engine: sqlalchemy.engine.base.Engine,
tablename: str,
dbname: str = None,
ifExists: str = "error",
indexList: list = None,
isIndexUnique: bool = True,
dtype: dict = {}) -> None:
"""
===example===
createTable(df_iris, engine, "iris", "db_data",
ifExists="replace")
>>> None
"""
# make query string
if dbname is None:
dbname = engine.url.database
new_names = renameColumns(df.columns.to_series())
col_dtype = [
dtypeParser(col) if name not in dtype else dtype[name]
for name, col in df.iteritems()
]
column_query_part = "\n ,".join(
[f"{name} {dtype}" for name, dtype in zip(new_names, col_dtype)])
if indexList:
idxSeries = pd.Series(indexList).astype(str)
idx_str = ",".join(renameColumns(idxSeries))
q_unique = "unique " if isIndexUnique else ""
index_query_part = q_unique + f"primary index ({idx_str})"
else:
index_query_part = "no primary index"
query = """
create table {dbname}.{tablename} (
{column_query_part}
) {index_query_part}
""".format(tablename=tablename,
dbname=dbname,
column_query_part=column_query_part,
index_query_part=index_query_part)
# execute query
if ifExists == "replace":
dropIfExists(tablename, dbname, engine)
elif ifExists == "error":
if tableIsExist(tablename, dbname, engine):
raise ValueError(f"{tablename}.{dbname} is already exists.")
elif ifExists == "insert":
if tableIsExist(tablename, dbname, engine):
return
engine.execute(query)
print("created table \n" + query)
def dropIfExists(tablename: str, dbname: str,
engine: sqlalchemy.engine.base.Engine) -> None:
"""
===example===
from sqlalchemy import create_engine
engine = create_engine("teradata://*****:*****@hostip:1025")
dropIfExists("iris", "db_data", engine)
droped table db_data.iris
>>> None
"""
if tableIsExist(tablename, dbname, engine):
engine.execute(f"drop table {dbname}.{tablename}")
print(f"droped table {dbname}.{tablename}")
def insert_usage_file_name(file_name: str,
db_engine: sqlalchemy.engine.base.Engine) -> None:
"""
Inserts the name of the file in the table for processed files
Parameters
----------
file_name: str
Name of the file
db_engine: sqlalchemy.engine.base.Engine
Returns
-------
None
"""
_create_usage_table_if_it_does_not_exist(db_engine)
db_engine.execute(f"INSERT INTO {USAGE_TABLE_NAME} VALUES('{file_name}')")
def atb_fixed_var_om_existing(
results: pd.DataFrame,
atb_hr_df: pd.DataFrame,
settings: dict,
pudl_engine: sqlalchemy.engine.base.Engine,
) -> pd.DataFrame:
"""Add fixed and variable O&M for existing power plants
ATB O&M data for new power plants are used as reference values. Fixed and variable
O&M for each technology and heat rate are calculated. Assume that O&M scales with
heat rate from new plants to existing generators. A separate multiplier for fixed
O&M is specified in the settings file.
Parameters
----------
results : DataFrame
Compiled results of clustered power plants with weighted average heat rates.
Note that column names should include "technology", "Heat_rate_MMBTU_per_MWh",
and "region". Technology names should not yet be converted to snake case.
atb_hr_df : DataFrame
Heat rate data from NREL ATB
settings : dict
User-defined parameters from a settings file
Returns
-------
DataFrame
Same as incoming "results" dataframe but with new columns
"Fixed_OM_cost_per_MWyr" and "Var_OM_cost_per_MWh"
"""
logger.info("Adding fixed and variable O&M for existing plants")
techs = settings["eia_atb_tech_map"]
existing_year = settings["atb_existing_year"]
# ATB string is <technology>_<tech_detail>
techs = {eia: atb.split("_") for eia, atb in techs.items()}
df_list = []
grouped_results = results.reset_index().groupby(
["plant_id_eia", "technology"], as_index=False)
for group, _df in grouped_results:
plant_id, eia_tech = group
existing_hr = _df["heat_rate_mmbtu_mwh"].mean()
try:
atb_tech, tech_detail = techs[eia_tech]
except KeyError:
if eia_tech in settings["tech_groups"]:
raise KeyError(
f"{eia_tech} is defined in 'tech_groups' but doesn't have a "
"corresponding ATB technology in 'eia_atb_tech_map'")
else:
raise KeyError(
f"{eia_tech} doesn't have a corresponding ATB technology in "
"'eia_atb_tech_map'")
try:
new_build_hr = (atb_hr_df.query(
"technology==@atb_tech & tech_detail==@tech_detail"
"& basis_year==@existing_year").squeeze().at["heat_rate"])
except ValueError:
# Not all technologies have a heat rate. If they don't, just set both values
# to 1
existing_hr = 1
new_build_hr = 1
try:
s = f"""
select parameter_value
from technology_costs_nrelatb
where
technology == "{atb_tech}"
AND tech_detail == "{tech_detail}"
AND basis_year == "{existing_year}"
AND financial_case == "Market"
AND cost_case == "Mid"
AND atb_year == "{settings['atb_data_year']}"
AND parameter == "variable_o_m_mwh"
"""
atb_var_om_mwh = pudl_engine.execute(s).fetchall()[0][0]
except IndexError:
# logger.warning(f"No variable O&M for {atb_tech}")
atb_var_om_mwh = 0
try:
s = f"""
select parameter_value
from technology_costs_nrelatb
where
technology == "{atb_tech}"
AND tech_detail == "{tech_detail}"
AND basis_year == "{existing_year}"
AND financial_case == "Market"
AND cost_case == "Mid"
AND atb_year == "{settings['atb_data_year']}"
AND parameter == "fixed_o_m_mw"
"""
atb_fixed_om_mw_yr = pudl_engine.execute(s).fetchall()[0][0]
except IndexError:
# logger.warning(f"No fixed O&M for {atb_tech}")
atb_fixed_om_mw_yr = 0
nems_o_m_techs = [
"Combined Cycle",
"Combustion Turbine",
"Coal",
"Steam Turbine",
"Hydroelectric",
"Geothermal",
"Nuclear",
]
if any(t in eia_tech for t in nems_o_m_techs):
# Change CC and CT O&M to EIA NEMS values, which are much higher for CCs and
# lower for CTs than a heat rate & linear mulitpler correction to the ATB
# values.
# Add natural gas steam turbine O&M.
# Also using the new values for coal plants, assuming 40-50 yr age and half
# FGD
# https://www.eia.gov/analysis/studies/powerplants/generationcost/pdf/full_report.pdf
# logger.info(f"Using NEMS values for {eia_tech} fixed/variable O&M")
target_usd_year = settings["target_usd_year"]
simple_o_m = {
"Combined Cycle": {
# "fixed_o_m_mw": inflation_price_adjustment(
# 13.08 * 1000, 2017, target_usd_year
# ),
# "variable_o_m_mwh": inflation_price_adjustment(
# 3.91, 2017, target_usd_year
# )
},
# "Combustion Turbine": {
# # This includes both the Fixed O&M and Capex. Capex includes
# # variable O&M, which is split out in the calculations below.
# "fixed_o_m_mw": inflation_price_adjustment(
# (5.33 + 6.90) * 1000, 2017, target_usd_year
# ),
# "variable_o_m_mwh": 0,
# },
"Natural Gas Steam Turbine": {
# NEMS documenation splits capex and fixed O&M across 2 tables
# "fixed_o_m_mw": inflation_price_adjustment(
# (15.96 + 24.68) * 1000, 2017, target_usd_year
# ),
"variable_o_m_mwh":
inflation_price_adjustment(1.0, 2017, target_usd_year)
},
"Coal": {
# "fixed_o_m_mw": inflation_price_adjustment(
# ((22.2 + 27.88) / 2 + 46.01) * 1000, 2017, target_usd_year
# ),
"variable_o_m_mwh":
inflation_price_adjustment(1.78, 2017, target_usd_year)
},
"Conventional Hydroelectric": {
"fixed_o_m_mw":
inflation_price_adjustment(44.56 * 1000, 2017,
target_usd_year),
"variable_o_m_mwh":
0,
},
"Geothermal": {
"fixed_o_m_mw":
inflation_price_adjustment(198.04 * 1000, 2017,
target_usd_year),
"variable_o_m_mwh":
0,
},
"Pumped Hydro": {
"fixed_o_m_mw":
inflation_price_adjustment((23.63 + 14.83) * 1000, 2017,
target_usd_year),
"variable_o_m_mwh":
0,
},
}
if "Combined Cycle" in eia_tech:
# https://www.eia.gov/analysis/studies/powerplants/generationcost/pdf/full_report.pdf
plant_capacity = _df[settings["capacity_col"]].sum()
assert plant_capacity > 0
if plant_capacity < 500:
fixed = 15.62 * 1000
variable = 4.31
elif 500 <= plant_capacity < 1000:
fixed = 9.27 * 1000
variable = 3.42
else:
fixed = 11.68 * 1000
variable = 3.37
_df["Fixed_OM_cost_per_MWyr"] = inflation_price_adjustment(
fixed, 2017, target_usd_year)
_df["Var_OM_cost_per_MWh"] = inflation_price_adjustment(
variable, 2017, target_usd_year)
if "Combustion Turbine" in eia_tech:
# need to adjust the EIA fixed/variable costs because they have no
# variable cost per MWh for existing CTs but they do have per MWh for
# new build. Assume $11/MWh from new-build and 4% CF:
# (11*8760*0.04/1000)=$3.85/kW-yr. Scale the new-build variable
# (~$11/MWh) by relative heat rate and subtract a /kW-yr value as
# calculated above from the FOM.
# Based on conversation with Jesse J. on Dec 20, 2019.
plant_capacity = _df[settings["capacity_col"]].sum()
op, op_value = (settings.get("atb_modifiers", {}).get(
"ngct", {}).get("Var_OM_cost_per_MWh", (None, None)))
if op:
f = operator.attrgetter(op)
atb_var_om_mwh = f(operator)(atb_var_om_mwh, op_value)
variable = atb_var_om_mwh # * (existing_hr / new_build_hr)
if plant_capacity < 100:
annual_capex = 9.0 * 1000
fixed = annual_capex + 5.96 * 1000
elif 100 <= plant_capacity <= 300:
annual_capex = 6.18 * 1000
fixed = annual_capex + 6.43 * 1000
else:
annual_capex = 6.95 * 1000
fixed = annual_capex + 3.99 * 1000
fixed = fixed - (variable * 8760 * 0.04)
_df["Fixed_OM_cost_per_MWyr"] = inflation_price_adjustment(
fixed, 2017, target_usd_year)
_df["Var_OM_cost_per_MWh"] = inflation_price_adjustment(
variable, 2017, target_usd_year)
if "Natural Gas Steam Turbine" in eia_tech:
# https://www.eia.gov/analysis/studies/powerplants/generationcost/pdf/full_report.pdf
plant_capacity = _df[settings["capacity_col"]].sum()
assert plant_capacity > 0
if plant_capacity < 500:
annual_capex = 18.86 * 1000
fixed = annual_capex + 29.73 * 1000
elif 500 <= plant_capacity < 1000:
annual_capex = 11.57 * 1000
fixed = annual_capex + 17.98 * 1000
else:
annual_capex = 10.82 * 1000
fixed = annual_capex + 14.51 * 1000
_df["Fixed_OM_cost_per_MWyr"] = inflation_price_adjustment(
fixed, 2017, target_usd_year)
_df["Var_OM_cost_per_MWh"] = simple_o_m[
"Natural Gas Steam Turbine"]["variable_o_m_mwh"]
if "Coal" in eia_tech:
plant_capacity = _df[settings["capacity_col"]].sum()
assert plant_capacity > 0
age = settings["model_year"] - _df.operating_date.dt.year
age = age.fillna(age.mean())
age = age.fillna(40)
# https://www.eia.gov/analysis/studies/powerplants/generationcost/pdf/full_report.pdf
annual_capex = (16.53 + (0.126 * age) + (5.68 * 0.5)) * 1000
if plant_capacity < 500:
fixed = 44.21 * 1000
elif 500 <= plant_capacity < 1000:
fixed = 34.02 * 1000
elif 1000 <= plant_capacity < 2000:
fixed = 28.52 * 1000
else:
fixed = 33.27 * 1000
_df["Fixed_OM_cost_per_MWyr"] = inflation_price_adjustment(
fixed + annual_capex, 2017, target_usd_year)
_df["Var_OM_cost_per_MWh"] = simple_o_m["Coal"][
"variable_o_m_mwh"]
if "Hydroelectric" in eia_tech:
_df["Fixed_OM_cost_per_MWyr"] = simple_o_m[
"Conventional Hydroelectric"]["fixed_o_m_mw"]
_df["Var_OM_cost_per_MWh"] = simple_o_m[
"Conventional Hydroelectric"]["variable_o_m_mwh"]
if "Geothermal" in eia_tech:
_df["Fixed_OM_cost_per_MWyr"] = simple_o_m["Geothermal"][
"fixed_o_m_mw"]
_df["Var_OM_cost_per_MWh"] = simple_o_m["Geothermal"][
"variable_o_m_mwh"]
if "Pumped" in eia_tech:
_df["Fixed_OM_cost_per_MWyr"] = simple_o_m["Pumped Hydro"][
"fixed_o_m_mw"]
_df["Var_OM_cost_per_MWh"] = simple_o_m["Pumped Hydro"][
"variable_o_m_mwh"]
if "Nuclear" in eia_tech:
num_units = len(_df)
plant_capacity = _df[settings["capacity_col"]].sum()
# Operating costs for different size/num units in 2016 INL report
# "Economic and Market Challenges Facing the U.S. Nuclear Fleet"
# https://gain.inl.gov/Shared%20Documents/Economics-Nuclear-Fleet.pdf,
# table 1. Average of the two costs are used in each case.
# The costs in that report include fuel and VOM. Assume $0.66/mmbtu
# and $2.32/MWh plus 90% CF (ATB 2020) to get the costs below.
# The INL report doesn't give a dollar year for costs, assume 2015.
if num_units == 1 and plant_capacity < 900:
fixed = 315000
elif num_units == 1 and plant_capacity >= 900:
fixed = 252000
else:
fixed = 177000
# age = (settings["model_year"] - _df.operating_date.dt.year).values
# age = age.fillna(age.mean())
# age = age.fillna(40)
# EIA, 2020, "Assumptions to Annual Energy Outlook, Electricity Market Module,"
# Available: https://www.eia.gov/outlooks/aeo/assumptions/pdf/electricity.pdf
# fixed = np.ones_like(age)
# fixed[age < 30] *= 27 * 1000
# fixed[age >= 30] *= (27+37) * 1000
_df["Fixed_OM_cost_per_MWyr"] = inflation_price_adjustment(
fixed, 2015, target_usd_year)
_df["Var_OM_cost_per_MWh"] = atb_var_om_mwh * (existing_hr /
new_build_hr)
else:
_df["Fixed_OM_cost_per_MWyr"] = atb_fixed_om_mw_yr
_df["Var_OM_cost_per_MWh"] = atb_var_om_mwh * (existing_hr /
new_build_hr)
df_list.append(_df)
mod_results = pd.concat(df_list, ignore_index=True)
# mod_results = mod_results.sort_values(["model_region", "technology", "cluster"])
mod_results.loc[:,
"Fixed_OM_cost_per_MWyr"] = mod_results.loc[:,
"Fixed_OM_cost_per_MWyr"].astype(
int)
mod_results.loc[:,
"Var_OM_cost_per_MWh"] = mod_results.loc[:,
"Var_OM_cost_per_MWh"]
return mod_results
def fetch_atb_costs(
pudl_engine: sqlalchemy.engine.base.Engine,
settings: dict,
offshore_spur_costs: pd.DataFrame = None,
) -> pd.DataFrame:
"""Get NREL ATB power plant cost data from database, filter where applicable.
This function can also remove NREL ATB offshore spur costs if more accurate costs
will be included elsewhere (e.g. as part of total interconnection costs).
Parameters
----------
pudl_engine : sqlalchemy.Engine
A sqlalchemy connection for use by pandas
settings : dict
User-defined parameters from a settings file. Needs to have keys
`atb_data_year`, `atb_new_gen`, and `target_usd_year`. If the key
`atb_financial_case` is not included, the default value will be "Market".
offshore_spur_costs : pd.DataFrame
An optional dataframe with spur costs for offshore wind resources. These costs
are included in ATB for a fixed distance (same for all sites). PowerGenome
interconnection costs for offshore sites include a spur cost calculated
using actual distance from shore.
Returns
-------
pd.DataFrame
Power plant cost data with columns:
['technology', 'cap_recovery_years', 'cost_case', 'financial_case',
'basis_year', 'tech_detail', 'fixed_o_m_mw', 'variable_o_m_mwh', 'capex', 'cf',
'fuel', 'lcoe', 'wacc_nominal']
"""
logger.info("Loading NREL ATB data")
col_names = [
"technology",
"tech_detail",
"cost_case",
"parameter",
"basis_year",
"parameter_value",
"dollar_year",
]
atb_year = settings["atb_data_year"]
fin_case = settings.get("atb_financial_case", "Market")
# Fetch cost data from sqlite and create dataframe. Only get values for techs/cases
# listed in the settings file.
all_rows = []
wacc_rows = []
tech_list = []
techs = settings["atb_new_gen"]
mod_techs = []
if settings.get("modified_atb_new_gen"):
for _, m in settings.get("modified_atb_new_gen").items():
mod_techs.append([
m["atb_technology"], m["atb_tech_detail"], m["atb_cost_case"],
None
])
cost_params = (
"capex_mw",
"fixed_o_m_mw",
"variable_o_m_mwh",
"capex_mwh",
"fixed_o_m_mwh",
)
# add_pv_wacc = True
for tech in techs + mod_techs:
tech, tech_detail, cost_case, _ = tech
# if tech == "UtilityPV":
# add_pv_wacc = False
s = f"""
SELECT technology, tech_detail, cost_case, parameter, basis_year, parameter_value, dollar_year
from technology_costs_nrelatb
where
technology == "{tech}"
AND tech_detail == "{tech_detail}"
AND financial_case == "{fin_case}"
AND cost_case == "{cost_case}"
AND atb_year == {atb_year}
AND parameter IN ({','.join('?'*len(cost_params))})
"""
all_rows.extend(pudl_engine.execute(s, cost_params).fetchall())
if tech not in tech_list:
# ATB2020 summary file provides a single WACC for each technology and a single
# tech detail of "*", so need to fetch this separately from other cost params.
# Only need to fetch once per technology.
wacc_s = f"""
select technology, cost_case, basis_year, parameter_value
from technology_costs_nrelatb
where
technology == "{tech}"
AND financial_case == "{fin_case}"
AND cost_case == "{cost_case}"
AND atb_year == {atb_year}
AND parameter == "wacc_nominal"
"""
wacc_rows.extend(pudl_engine.execute(wacc_s).fetchall())
tech_list.append(tech)
if "Battery" not in tech_list:
df = pd.DataFrame(all_rows, columns=col_names)
wacc_df = pd.DataFrame(
wacc_rows,
columns=["technology", "cost_case", "basis_year", "wacc_nominal"])
else:
# ATB doesn't have a WACC for battery storage. We use UtilityPV WACC as a default
# stand-in -- make sure we have it in case.
s = 'SELECT DISTINCT("technology") from technology_costs_nrelatb WHERE parameter == "wacc_nominal"'
atb_techs = [x[0] for x in pudl_engine.execute(s).fetchall()]
battery_wacc_standin = settings.get("atb_battery_wacc")
battery_tech = [x for x in techs if x[0] == "Battery"][0]
if isinstance(battery_wacc_standin, float):
if battery_wacc_standin > 0.1:
logger.warning(
f"You defined a battery WACC of {battery_wacc_standin}, which seems"
" very high. Check settings parameter `atb_battery_wacc`.")
battery_wacc_rows = [(battery_tech[0], battery_tech[2], year,
battery_wacc_standin)
for year in range(2017, 2051)]
wacc_rows.extend(battery_wacc_rows)
elif battery_wacc_standin in atb_techs:
# if battery_wacc_standin in tech_list:
# pass
# else:
logger.info(
f"Using {battery_wacc_standin} {fin_case} WACC for Battery storage."
)
wacc_s = f"""
select technology, cost_case, basis_year, parameter_value
from technology_costs_nrelatb
where
technology == "{battery_wacc_standin}"
AND financial_case == "{fin_case}"
AND cost_case == "Mid"
AND atb_year == {atb_year}
AND parameter == "wacc_nominal"
"""
b_rows = pudl_engine.execute(wacc_s).fetchall()
battery_wacc_rows = [(battery_tech[0], battery_tech[2], b_row[2],
b_row[3]) for b_row in b_rows]
wacc_rows.extend(battery_wacc_rows)
else:
raise ValueError(
f"The settings key `atb_battery_wacc` value is {battery_wacc_standin}. It "
f"should either be a float or a string from the list {atb_techs}."
)
df = pd.DataFrame(all_rows, columns=col_names)
wacc_df = pd.DataFrame(
wacc_rows,
columns=["technology", "cost_case", "basis_year", "wacc_nominal"])
# Transform from tidy to wide dataframe, which makes it easier to fill generator
# rows with the correct values.
atb_costs = (df.drop_duplicates().set_index([
"technology",
"tech_detail",
"cost_case",
"dollar_year",
"basis_year",
"parameter",
]).unstack(level=-1))
atb_costs.columns = atb_costs.columns.droplevel(0)
atb_costs = (atb_costs.reset_index().merge(
wacc_df, on=["technology", "cost_case",
"basis_year"], how="left").drop_duplicates())
atb_costs = atb_costs.fillna(0)
usd_columns = [
"fixed_o_m_mw",
"fixed_o_m_mwh",
"variable_o_m_mwh",
"capex_mw",
"capex_mwh",
]
for col in usd_columns:
if col not in atb_costs.columns:
atb_costs[col] = 0
atb_target_year = settings["target_usd_year"]
atb_costs[usd_columns] = atb_costs.apply(
lambda row: inflation_price_adjustment(row[usd_columns],
base_year=row["dollar_year"],
target_year=atb_target_year),
axis=1,
)
if any("PV" in tech for tech in tech_list) and atb_year == 2019:
print("Inflating ATB 2019 PV costs from DC to AC")
atb_costs.loc[atb_costs["technology"].str.contains("PV"),
["capex_mw", "fixed_o_m_mw", "variable_o_m_mwh"],
] *= settings.get("pv_ac_dc_ratio", 1.3)
elif atb_year > 2019:
logger.info(
"PV costs are already in AC units, not inflating the cost.")
if offshore_spur_costs is not None and "OffShoreWind" in atb_costs[
"technology"]:
idx_cols = ["technology", "tech_detail", "cost_case", "basis_year"]
offshore_spur_costs = offshore_spur_costs.set_index(idx_cols)
atb_costs = atb_costs.set_index(idx_cols)
atb_costs.loc[idx["OffShoreWind", :, :, :], "capex_mw"] = (
atb_costs.loc[idx["OffShoreWind", :, :, :], "capex_mw"] # .values
- offshore_spur_costs.loc[idx["OffShoreWind", :, :, :],
"capex_mw"] # .values
)
atb_costs = atb_costs.reset_index()
return atb_costs
def _create_usage_table_if_it_does_not_exist(
db_engine: sqlalchemy.engine.base.Engine) -> None:
if _table_exist(USAGE_TABLE_NAME, db_engine) is False:
db_engine.execute(f"""CREATE TABLE {USAGE_TABLE_NAME} (
FILE_NAME TEXT PRIMARY KEY
);""")
