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

[clyman23]

Understood, thank you! It wasn't clear to me if utility_id_ferc1 was defined by FERC of by PUDL, and I think that was causing some confusion on my end when it came to relating records between years and between XBRL / DBF.

Great to know that I can just use the utility_id_ferc1 for any of the core_ferc1__yearly_* tables.

[cmgosnell]

Happy to help and again sorry it took so long to reply! But yes its the utility_id_ferc1 is:

PUDL-assigned utility ID, identifying a FERC1 utility. This is an auto-incremented ID and is not expected to be stable from year to year.

whereas the utility_id_ferc1_xbrl and utility_id_ferc1_dbf come from FERC directly. We should probably update that description to include its purpose of linking the XBRL and DBF ids.

Thanks for the inquiry and I hope your able to get at the info your looking for!

[clyman23]

Hi @cmgosnell , could you please clarify one thing for me about the fact that the utility_id_ferc1 is not stable year to year: does this mean I should not rely on utility_id_ferc1 to analyze changes to utilities from year-to-year? For example, I'm using core_ferc1__yearly_plant_in_service_sched204 to analyze changes over time to utility spending (let's say I'm curious about account "x"). The only ID in this table is utility_id_ferc1. If I were to query, say, utility_id_ferc1==2, I would get something like:

utility_id_ferc1	report_year	ferc_account_label	starting_balance	...
2	1994	x	1.1	...
2	1995	x	1.3	...
2	1996	x	0.95	...
2	1997	x	1.27	...

How would I verify that it's the same utility for each year that is in that query if utility_id_ferc1 is not stable from year to year?

Or, am I misunderstanding "... is not expected to be stable from year to year"? Does that mean that utility_id_ferc1 may change with each PUDL release?

Thank you for the clarification.

[zaneselvans]

@clyman23 you're right, that wording unclear. It's the latter -- within a given release of PUDL the utility_id_ferc1 consistently refers to one utility throughout the database. But it's algorithmically assigned in our data processing and if the input data changes, which utility gets what ID might turn out different. So for instance it would be dangerous to hard code a list of those IDs in an analysis and use it across multiple releases of PUDL.

[clyman23]

Understood, thank you for explaining!