Transform `f1_elctrc_erg_acct` xbrl + dbf

[aesharpe]

Table Notes

elec_energy_acct_dbf = pd.read_sql("f1_elctrc_erg_acct", ferc1_engine)
elec_energy_acct_xbrl = pd.read_sql("electric_energy_account_401a_duration", ferc1_xbrl_engine)

• This table is an RMI priority.
• Seems to be more about where power is coming from and going to, both generation and system purchases.
• An electricity, not a money accounting table.
• DBF data only has 2 columns: energy source and energy disposition (in MWh). At least one of them is always NA, so they're mutually exclusive.
• Because data exists in disjoint rows, should this really be a single column with different sign conventions? Is it two tables? Are there pairs of rows that correlate with each other?
• DBF row labels will not uniquely identify the various quantities alone, since they are only unique within their nested categories. See this query for examples.
• XBRL data is entirely duration -- nothing in the instant table.
• There appear to be 2 full years of data in the duration table. This year and last year. What to do?
• XBRL data has several columns which come in pairs: received/delivered, purchased/sales.
• XBRL also has some net energy columns e.g. interchange / exchange.
• XBRL looks like it has some totals, and some values that should only be negative (transmission losses) or positive (generation). Potential for sign errors / ambiguity.
• This is a somewhat different use case from the Electric Plant In Service table (See #1807), but there's row-to-column reshaping and hierarchical categories so it seems like structurally it may end up being very similar.
• From 1994 to 2020 there were zero cases in which a row number changed its meaning.
• The DBF data has a "purchased energy" category which doesn't directly correspond to the XBRL categories -- in 2021 this appears to have been split into two new categories: megawatt_hours_purchased_other_than_storage and megawatt_hours_purchased_for_energy_storage. A new category energy_stored was also added. How should we handle these cases where there's not a direct correspondence between the two data sources? I'm inclined to just let them not align, and manage that kind of thing in the transformations or aggregations later.
• This will present a particular problem when there's a category that has changed row numbers over the years in the DBF data that doesn't have a corresponding XBRL column -- we'll need to map to another non-XBRL value in order to align the old DBF data internally.
• There are "net" calculations in this table, which might be different in structure than the totals / subtotals that we've seen in the Electric Plan In Service table.

[jrea-rmi]

I have both xbrl and dbf transform steps into the tidy format we're using in the Hub here: https://github.com/rmi-electricity/utility-transition-hub/blob/f8fc3c49caf2b2d14238468b91770ecf94183704/hub/energy_sources_disposition.py

You can look at the final output by running

from hub.energy_sources_disposition import EnergySourcesDisp
energy_sources_f1 = EnergySourcesDisp()._create_energy_sources_f1()
disposition_f1 = EnergySourcesDisp()._create_disposition_f1()

The part my transformation/combination is missing is conversion from old respondent_id to new entity_id. I didn't see that table in ferc1_xbrl.sqlite, does it exist elsewhere?

[zaneselvans]

Hey @jrea-rmi maybe this has already become clear, but the mapping between the old respondent_id and the new entity_id can't happen in the context of extracting the XBRL into an SQLite DB, because the old IDs aren't in there. It's been integrated into the PUDL ID mappings in the PUDL DB. I imagine @cmgosnell has already chatted with you about this since she did most of that work.

[jrea-rmi]

@zaneselvans yes, I've chatted with @cmgosnell about it, and am expecting the final transformed/combined PUDL version of FERC tables to use utility_id_ferc1 as an identifier with another mapping table(s) that connect utility_id_ferc1 to respondent_id and entity_id.

Those mappings exist in the PUDL DB where? Not in datasette yet, right?