Best way to relate utilities and plants

[bendnorman]

What is the best way to relate utilities and plants? I figured the utilities_entity_eia and plants_eia860 can be joined together on the utility_id_eia columns.

1. Should we be pointing users to pu_eia860 output table instead of having them merge the normalized tables.
2. When should users be using utility_plant_assn? Is this only used for associating FERC and EIA tables?

[zaneselvans]

This isn't a lot of context to work with. Are you talking about just within the EIA data? What aspect of the plants and utilities do you want to bring together?

In EIA, utilities have a couple of different kinds of relationships with plants:

• plant operator -- this is the utility_id_eia that appears in the plants_eia860 table.
• generator owner -- generators are associated with plants, and the ownership_eia860 table identifies their owners using utility_id_eia though I think there it may be called owner_id_eia.
• transmission owner -- in the plants_eia860 table there's also a transmission_distribution_owner_id which is a utility_id_eia and indicates what T&D utility owns the line that the plant is connected to.

The pu_eia860 output table only grabs a handful of plant & utility fields that we thought would be useful to include across the board in all the output tables that include plant IDs. To get all of the available fields you could do something like this:

plants_entity_eia = pd.read_sql("plants_entity_eia", pudl_engine).convert_dtypes(convert_floating=False)
plants_eia860 = pd.read_sql("plants_eia860", pudl_engine).convert_dtypes(convert_floating=False)
plants_eia = pd.merge(plants_entity_eia, plants_eia860, on="plant_id_eia")

utilities_entity_eia = pd.read_sql("utilities_entity_eia", pudl_engine).convert_dtypes(convert_floating=False)
utilities_eia860 = pd.read_sql("utilities_eia860", pudl_engine).convert_dtypes(convert_floating=False)
utilities_eia = pd.merge(utilities_entity_eia, utilities_eia860, on="utility_id_eia")

plants_utils_eia = pd.merge(plants_eia, utilities_eia, on="utility_id_eia")

(though without additional work you'll get column name collisions and have some _x and _y columns)

The utility_plant_assn table associates PUDL plant and utility IDs. The PUDL plant and utility IDs are almost entirely used to connect FERC and EIA data about plants and utilities.

[bendnorman]

Got it, thank you!

I think we should try to include this type of information about table relationships in our documentation.

[zaneselvans]

I think most of this information is conveyed by the foreign key relationships & column descriptions. It would be nice if we could somehow display the FKs in the data dictionaries.