How can I access FERC Form 1 utility monthly system peaks (Schedule 401)?

[zaneselvans]

A user question via email, imported here for wider reference value:

I'm interested in using PUDL to better understand and perform some analyses using FERC Form 1 reporting. The extensive work Catalyst has done to ingest and clean this dataset has been very helpful in my work.

One thing I'm having trouble extracting from PUDL is utility reporting on system peaks from FERC Form 1. My understanding is that utilities report monthly system peaks on page 401a, yet I don't see datasets in PUDL that have this information. Perhaps I'm looking in the wrong place? Any guidance would be greatly appreciated.

[zaneselvans]

Part of FERC Form 1 Schedule 401 is available in the main PUDL database. The table names include schedXXX where XXX is the schedule number. The best place to go to find what is available in the PUDL DB is the PUDL Data Dictionary. Currently there are two tables derived from Schedule 401a -- energy sources and energy dispositions. The Data Dictionary also has links to browse the tables in Datasette (where you can also export them as CSVs), or download them as Parquet files.

However, looking at the most recent blank FERC Form 1 that we've archived it looks like the monthly system peaks are reported on Page 401b, which has not yet been integrated into the main PUDL DB. There are 100+ FERC Form 1 tables and they each need some manual work to organize and reconcile the older DBF data with the newer XBRL data, so we only bring them in as there's demand for them. If you need that specific data from page 401b there are a couple of options:

• Hire us to integrate it into PUDL, in which case it would be available for all historical years, and would get updated each year as part of our normal FERC Form 1 updates. We have a lot of infrastructure for this already, and this looks like it's probably a well structured table, so I doubt it would be a ton of work.
• Try and extract the data yourself. We publish all the unprocessed FERC data as SQLite databases (converted from DBF and XBRL). You can download the SQLite DBs using the links on the Data Access in our docs (DBF, XBRL). You can get a preview of those tables through Datasette (or download CSVs): f1_mthly_peak_otpt (DBF, 1994-2020) and monthly_peaks_and_output_401b_duration (XBRL, 2021-2023) -- reconciling them with each other will probably be annoying, and you'll need to merge in the respondent ID tables in order to get the human readable utility names.

Alternatively, depending what kind of analysis you're doing, the FERC-714 planning area hourly demand data might also work? Many of the planning areas are utilities, and the FERC Form 1 operational data can be a little sketchy. The out_ferc714__hourly_planning_area_demand table is probably the one to look at for the data, and the out_ferc714__summarized_demand table can provide human readable names and some other summary information on an annual basis that might be helpful.

Note that the hourly tables are only available as Parquet files (not in Datasette) due to their size. The Data Dictionary has a link to download the Parquet. If you're not familiar with that format, the TAD Viewer will let you open up both SQLite DBs and Parquet files to browse and filter them graphically.

[clyman23]

(Following up here since this was my email)

Another approach could be to pull in EIA 861 data on peak demand (reported as annual summer and winter peak). This requires mapping utility IDs in FERC to utility IDs in EIA. I'm attempting to do this for some other EIA data already. I notice PUDL has core_pudl__assn_eia_pudl_utilities and core_pudl__assn_ferc1_pudl_utilities, which could be used to relate EIA and FERC reporting. Is this the recommended approach? Where could I find the methodology for generating these tables?

Thank you

[zaneselvans]

Hey @clyman23, the FERC to EIA utility ID mapping is manual. The instructions that we've written up to tell ourselves how to do it are here. There's also a manual plant mapping, but that gets much more complex because of the wildly different ways that FERC and EIA think about "plants"

Those are the right two association tables to use to understand which FERC Form 1 & EIA utility IDs correspond to each other, with the slight complication that the old DBF and new XBRL data from FERC use different sets of IDs, so there's an intermediate ID that links them together, defined in these association tables:

• core_pudl__assn_ferc1_dbf_pudl_utilities
• core_pudl__assn_ferc1_xbrl_pudl_utilities

The easiest way to bring all these utility IDs together is probably to join the DBF/XBRL tables above on utility_id_ferc1 and then join the result with core_pudl__assn_ferc1_pudl_utilities on utility_id_ferc1 and then join the results with core_pudl_assn__eia_pudl_utilities on utility_id_pudl

A ton of EIA-861 data is also in PUDL, but it's not quite as fully integrated as the EIA-860/923. It looks like summer/winter peak demand is reported in core_eia861__yearly_operational_data_misc

The Utility IDs that we map to FERC are only the ones found in the EIA-860 and EIA-923, so there will be some Utilities that show up in EIA-861 that don't have an associated FERC 1 utility ID, but I think these utilities will mostly be the ones that don't operate power plants, and just act as retail intermediaries.

The Utilities found in FERC 1 are also less expansive than in the EIA data -- e.g. munis and co-ops don't report Form 1 -- so there will definitely be some utilities without a match.

What other FERC Form 1 data are you trying to link this information back to?

[clyman23]

Thanks, @zaneselvans . My current process involves taking core_ferc1__yearly_operating_expenses_sched320 and getting the utility name and PUDL ID for each entry in the dataset using core_pudl__assn_ferc1_pudl_utilities. Does the DBF/XBRL issue mean that I should be using different mappings for different years in the Schedule 320 data? I was assuming that core_ferc1__yearly_operating_expenses_sched320 had already performed this step to relate data across years.

Understood on the nuance of mapping utility IDs from EIA 860/923 versus 861. I think you're correct that it won't be a major issue, but I'll keep that in mind.

In all, I'm attempting to link spending data (FERC Form 1) with sales (FERC Form 1 and EIA 861, I believe, but I would defer to just one dataset for simplicity. I forget exactly the differences, but I digress) and peak load (FERC Form 1 and EIA 861, but, as we've said here, dealing with EIA 861 would certainly be easier right now). I also want to understand these findings geospatially; my plan is to use reported service territory data (EIA 861, which I see PUDL has!) for this, rather than other maps out there (for example, this map from HIFLD). I would like to analyze this data over time, recognizing of course that I'll need to include checks in my analysis for things like mergers, acquisitions, and major service territory changes.