Clarification on the utility_id_ferc1 variable source in out_ferc1__yearly_utility_plant_summary_sched200 (and other Form 1 tables!)

[myozwiak]

Thanks for all the awesome work Catalyst does on the FERC Form 1 data.

I had a quick question about the utility_id_ferc1 variable in the new parquette tables for Form 1. I'm working specifically with Schedule 204 (out_ferc1__yearly_plant_in_service_sched204).

I've worked with the raw Form 1 data in the past, using an old PC and FoxPro to export the data to text files, as well as the older PUDL datasette tables (last accessed in early 2025).

Updating some code today to work with the new parquette files, I noticed the id's in the current tables don't seem to align with the raw FERC data or the prior datasette csv's. For example:

1. Alabama Power: I have a FERC ID of 2; the out_ferc1__yearly_plant_in_service_sched204 table shows an utility_id_ferc1 of 294.
2. Florida Power and Light: I have a FERC ID of 56; the out_ferc1__yearly_plant_in_service_sched204 table shows an utility_id_ferc1 of 247.

I cross-checked a few investment values against PDF's of both utilities' filings and the values appear to align, so I think this is simply about the utility_id_ferc1 variable.

Question: Would you be able to clarify if the utility_id_ferc1 variable has been modified, relative to the raw filings? (Or, perhaps FERC changed its nomenclature?)

To illustrate the discrepancy, I'm attaching the set of names and respondent id's I exported in 2022, for the 2020 filing, from the raw FERC Form 1 data: f1_respondent_id20.txt. The id's here also align with the summary table at UT Austin: https://energy.utexas.edu/policy/fce/calculators.

Thank you so much!

[zaneselvans]

Hey @myozwiak sorry for the confusion here, and glad our FERC data has been useful! Please don't be shy about letting us know how it can be more useful, or if there additional tables that you'd like to see fully integrated int PUDL, or any weird data issues you find.

utility_id_ferc1 is an ID assigned within PUDL which unifies the two different epochs of FERC Form 1 reporting. In the old DBF data that you've worked with in the past, each respondent had an integer ID. In the new XBRL based Form 1 reporting, each respondent has a different ID that's a string and looks like C000528. We've manually matched the old and new IDs and assigned the utility_id_ferc1 so that a single ID can be used to refer to that utility across the whole 30+ years of data. That utility_id_ferc1 is then used to manually map FERC and EIA respondents to a utility_id_pudl

The originally reported FERC 1 utility IDs are named utility_id_ferc1_dbf (the integers you're familiar with) and utility_id_ferc1_xbrl (the new strings). If you search the PUDL Data Dictionary for core_pudl__assn_ferc1 you'll find all of the association tables (crosswalks) that relate these IDs to each other, and you can merge the DBF and XBRL IDs into the plant in service table on utility_id_ferc1 to get them all there together.

Though really, for that denormalized output table (and all the others), I can see a good argument for us merging the DBF and XBRL ids in across the board so they're always available if people are already familiar with them or want to merge in other data that's using the old IDs.

[zaneselvans]

We have some developer docs on the PUDL ID mapping process (connecting FERC 1 & EIA) but we should really add a section about these mapping processes to our new top-level user-facing Methodology section.

[zaneselvans]

After chatting with @cmgosnell I've created #4365 to track adding these IDs into all the FERC 1 output tables.

[myozwiak]

Sweet. Super clear and so helpful. Thanks so much, @zaneselvans!

[zaneselvans]

Oh also, be aware that neither the utility_id_ferc1 or utility_id_pudl is guaranteed to be stable across different releases of PUDL, since there's an algorithmic component to their assignment. But within a given build they'll always refer to a single entity.

[myozwiak]

See discussion above!