<jupyter_start><jupyter_text>Integrating New FERC Form 1 and EIA Data ReleasesThis notebook generates lists of new plants and utilities that need to be assigned PUDL IDs. It helps with the process of integrating new data each fall when the agencies make their new annual release for the previous year. Prerequisites:* All available EIA 860/923 years must be loaded into your PUDL DB.* This includes the **new** year of data to be integrated.* This means the spreadsheet tab maps need to be updated.* Some minor EIA data wrangling may also be required.* All years of FERC Form 1 data must be loaded into your FERC 1 DB.* This includes the **new** year of data to be integrated. Outputs:* `unmapped_utilities_ferc1.csv`: Respondent IDs and respondent names of utilities which appear in the FERC Form 1 DB, but which do **not** appear in the PUDL ID mapping spreadsheet.* `unmapped_plants_ferc1.csv`: Plant names, respondent names, and respondent IDs associated with plants that appear in the FERC Form 1 DB, but which do **not** appear in the PUDL ID Mapping spreadsheet.* `unmapped_utilities_eia.csv`: EIA Utility IDs and names of utilities which appear in the PUDL DB, but which do **not** appear in the PUDL ID mapping spreadsheet.* `unmapped_plants_eia.csv`: EIA Plant IDs and Plant Names of plants which appear in the PUDL DB, but which do **not** appear in the PUDL ID mapping spreadsheet.  The Utility ID and Name for the primary plant operator, as well as the aggregate plant capacity and the state the plant is located in are also proved to aid in PUDL ID mapping.* `lost_utilities_eia.csv`: The Utility IDs and Names of utilities which appear in the PUDL ID mapping spreadsheet but which do **not** appear in the PUDL DB. Likely because EIA revised previous years of data, and removed those utilities, after we had mapped them.* `lost_plants_eia.csv`: The Plant IDs and Names of plants which appear in the PUDL ID mapping spreadsheet but which do **not** appear in the PUDL DB. Likely because EIA revised previous years of data, and removed those plants, after we had mapped them.<jupyter_code>%load_ext autoreload
%autoreload 2
import sqlalchemy as sa
import pandas as pd
import pudl
import re
from pathlib import Path
pudl_settings = pudl.workspace.setup.get_defaults()
pudl_settings<jupyter_output><empty_output><jupyter_text>Setup:* Create FERC1/PUDL database connections* Set the scope of the FERC Form 1 search (which years to check)<jupyter_code>ferc1_engine = sa.create_engine(pudl_settings["ferc1_db"])
pudl_engine = sa.create_engine(pudl_settings["pudl_db"])
ferc1_years = pudl.constants.data_years["ferc1"]
print("Searching for new FERC 1 plants, utilities and strings in the following years:")
print(ferc1_years)
with pudl_engine.connect() as conn:
    eia_years = pd.read_sql("select distinct(report_date) from plants_eia860", conn)

print(f"EIA Years in db: {(eia_years)}")<jupyter_output>EIA Years in db:    report_date
0   2019-01-01
1   2018-01-01
2   2017-01-01
3   2016-01-01
4   2015-01-01
5   2014-01-01
6   2013-01-01
7   2012-01-01
8   2011-01-01
9   2010-01-01
10  2009-01-01
11  2008-01-01
12  2007-01-01
13  2006-01-01
14  2005-01-01
15  2004-01-01
16  2003-01-01
17  2002-01-01
18  2001-01-01<jupyter_text>Unmapped FERC Form 1 Plants<jupyter_code>unmapped_plants_ferc1 = pudl.glue.ferc1_eia.get_unmapped_plants_ferc1(pudl_settings, years=ferc1_years)
n_ferc1_unmapped_plants = len(unmapped_plants_ferc1)
print(f"{n_ferc1_unmapped_plants} unmapped FERC 1 plants found in {min(ferc1_years)}-{max(ferc1_years)}.")
outfile = Path("unmapped_plants_ferc1.csv")
print(f"Writing {n_ferc1_unmapped_plants} out to {outfile}")
unmapped_plants_ferc1.to_csv(outfile, index=False)
unmapped_plants_ferc1<jupyter_output>0 unmapped FERC 1 plants found in 1994-2019.
Writing 0 out to unmapped_plants_ferc1.csv<jupyter_text>Unmapped FERC Form 1 Utilities / Respondents* **Note:** Frequently there are zero of these.<jupyter_code>unmapped_utils_ferc1 = pudl.glue.ferc1_eia.get_unmapped_utils_ferc1(ferc1_engine)
n_ferc1_unmapped_utils = len(unmapped_utils_ferc1)
print(f"{n_ferc1_unmapped_utils} unmapped FERC 1 utilities found in {min(ferc1_years)}-{max(ferc1_years)}.")
outfile = Path("unmapped_utilities_ferc1.csv")
print(f"Writing {n_ferc1_unmapped_utils} out to {outfile}")
unmapped_utils_ferc1.to_csv(outfile, index=False)
unmapped_utils_ferc1<jupyter_output>0 unmapped FERC 1 utilities found in 1994-2019.
Writing 0 out to unmapped_utilities_ferc1.csv<jupyter_text>Unmapped EIA Plants* **Note:** Some unmapped EIA plants do not have Utilities associated with them.* Many of these plants are too small to warrant mapping, and so capacity is included as a potential filter.* Also note that the first and last few plants in the output dataframe have a bunch of NA values... which can be confusing.<jupyter_code>unmapped_plants_eia = pudl.glue.ferc1_eia.get_unmapped_plants_eia(pudl_engine)
print(f"Found {len(unmapped_plants_eia)} unmapped EIA plants.")
outfile = Path("unmapped_plants_eia.csv")
unmapped_plants_eia.to_csv(outfile)
unmapped_plants_eia<jupyter_output>Found 253 unmapped EIA plants.<jupyter_text>Lost EIA Plants* There shouldn't be very many of these... if it's more than a few hundred (out of the ~10,000 EIA plants) then something may be wrong.<jupyter_code>lost_plants_eia = pudl.glue.ferc1_eia.get_lost_plants_eia(pudl_engine)
print(f"Found {len(lost_plants_eia)} lost EIA plants.")
outfile = Path("lost_plants_eia.csv")
outfile.unlink(missing_ok=True)
outfile.touch()
lost_plants_eia.to_csv(outfile)
lost_plants_eia.sample(min([10, len(lost_plants_eia)]))<jupyter_output>Found 3 lost EIA plants.<jupyter_text>Unmapped EIA Utilities* Especially with the advent of many small distributed generators, there are often just as many new utilities as there are new plants.<jupyter_code>unmapped_utils_eia = pudl.glue.ferc1_eia.get_unmapped_utils_eia(pudl_engine)
print(f"Found {len(unmapped_utils_eia)} unmapped EIA utilities.")
outfile = Path("all_unmapped_utilities_eia.csv")
unmapped_utils_eia.to_csv(outfile)

miss_utils = pudl.glue.ferc1_eia.get_unmapped_utils_with_plants_eia(pudl_engine)
print(f"Found {len(miss_utils)} unmapped utilities with plants/ownership.")
outfile = Path("planted_unmapped_utilities_eia.csv")
miss_utils.to_csv(outfile)

unmapped_utils_eia.head(10)
miss_utils.head(10)<jupyter_output><empty_output><jupyter_text>Another Kind of Unmapped EIA Utilities* This cell looks *only* for the EIA utilities that show up somewhere in the EIA 923 data, but still don't have a `utility_id_pudl` value assigned to them.<jupyter_code>pudl_raw = pudl.output.pudltabl.PudlTabl(pudl_engine, freq=None)
frc_eia923 = pudl_raw.frc_eia923()
gf_eia923 = pudl_raw.gf_eia923()
gen_eia923 = pudl_raw.gen_eia923()
bf_eia923 = pudl_raw.bf_eia923()

missing_frc = frc_eia923[frc_eia923.utility_id_pudl.isna()][["utility_id_eia", "utility_name_eia"]]
missing_gf = gf_eia923[gf_eia923.utility_id_pudl.isna()][["utility_id_eia", "utility_name_eia"]]
missing_bf = bf_eia923[bf_eia923.utility_id_pudl.isna()][["utility_id_eia", "utility_name_eia"]]
missing_gens = gen_eia923[gen_eia923.utility_id_pudl.isna()][["utility_id_eia", "utility_name_eia"]]

missing_utils = (
    pd.concat([missing_frc, missing_bf, missing_gf, missing_gens])
    .drop_duplicates(subset="utility_id_eia")
    .set_index("utility_id_eia")
)

print(f"Found {len(missing_utils)} utilities with EIA 923 data but no PUDL Utility ID.")
outfile = Path("dataful_unmapped_utilities_eia.csv")
missing_utils.to_csv(outfile)
missing_utils.sample(min(len(missing_utils), 10))
missing_utils.iloc[50:100]<jupyter_output><empty_output><jupyter_text>Lost EIA Utilities* Again, there shouldn't be **too** many of these. If it's thousands, not hundreds, dig deeper.<jupyter_code>lost_utils_eia = pudl.glue.ferc1_eia.get_lost_utils_eia(pudl_engine)
print(f"Found {len(lost_utils_eia)} lost EIA utilities.")
outfile = Path("lost_utilities_eia.csv")
lost_utils_eia.to_csv(outfile)<jupyter_output><empty_output><jupyter_text>Cleaning other FERC Form 1 Plant Tables* There are several additional FERC Form 1 tables which contain plant data.* These include small plants, hydro, and pumped storage.* Thus far we have not done much concerted work cleaning up / categorizing these plants, though they do get PUDL IDs.* The following cell pulls the small plants (`f1_gnrt_plant`) table with some fields that would be useful for categorization.* This is just a prototype/outline/suggestion...<jupyter_code>small_plants_ferc1 = (
    pd.read_sql(
        f"""SELECT f1_gnrt_plant.report_year,\
                   f1_gnrt_plant.respondent_id,\
                   f1_gnrt_plant.row_number,\
                   f1_gnrt_plant.spplmnt_num,\
                   f1_gnrt_plant.plant_name,\
                   f1_gnrt_plant.capacity_rating,\
                   f1_gnrt_plant.kind_of_fuel, \
                   f1_respondent_id.respondent_name\
            FROM f1_gnrt_plant, f1_respondent_id \
            WHERE report_year>={min(ferc1_years)}
            AND report_year<={max(ferc1_years)}
            AND f1_respondent_id.respondent_id=f1_gnrt_plant.respondent_id;""", ferc1_engine).
    assign(record_number=lambda x: x["row_number"] + 46*x["spplmnt_num"]).
    drop(["row_number", "spplmnt_num"], axis="columns").
    pipe(pudl.helpers.simplify_strings, columns=["plant_name", "kind_of_fuel", "respondent_name"]).
    rename(columns={"capacity_rating": "capacity_mw"}).
    loc[:,["report_year", "respondent_id", "respondent_name", "record_number", "plant_name", "capacity_mw", "kind_of_fuel"]].
    sort_values(["report_year", "respondent_id", "record_number"])
)
n_small_plants_ferc1 = len(small_plants_ferc1)
outfile = Path("f1_gnrt_plant.csv")
print(f"Writing {n_small_plants_ferc1} small plant records out to {outfile}")
small_plants_ferc1.to_csv(outfile, index=False)
small_plants_ferc1<jupyter_output><empty_output>