Write transform method for EIA 861 Balancing Authority table

[zaneselvans]

We need the EIA 861 Balancing Authority table to do the GridLab project allocating demand geographically, but it's also part of the Sloan grant.

This table had one form up through 2012, and another form from 2013 to the present (with 2013 being a bit of a mix). The earlier version indicated which utilities were part of what balancing authorities -- with many entries for each balancing authority, pertaining to each balanced authority. The latter version doesn't include managed utilities, and instead has a collection of Balancing Authority codes, and an indication of which states each balancing authority is operating in. Consequently, the early tables have +3000 records per year, while the latter ones only have about 200. Depending on how consistent these attributes are over time, it may be possible to use the latter data to fill in which states the earlier balancing authority records are associated with, and the latter data to fill in which utilities and BA codes the earlier records are associated with. The only consistently reported values across the entire dataset are the Balancing Authority IDs and Names.

So it seems like the natural primary key here is the Balancing Authority ID, and we need to compile the associated states and BA codes from all of the years (though, it's not 100% clear to me how consistent these mappings should be from year to year).

There's also some confusing overlap between the BA IDs and Utility IDs -- some entities act as both, and some ID's show up in both sets associated with the same entity... while other IDs show up in both sets associated with different entities apparently, which is, ugh. Confusing. Just looking at the sets of IDs (from 2010-2018 for the balancing authority table, and 2009-2018 for the EIA 860 Plants table output, we see:

• Total # of BA IDs: 175
• Total # of Util IDs: 12017
• BA IDs that are not Util IDs: 18
• Util IDs that are not BA IDs: 11860
• BA IDs that are also Util IDs: 157

So 99% of Utility IDs are not BA IDs, and about 10% of BA IDs are apparently not Utility IDs, and we need to take a harder look at the IDs which show up both as BA IDs and Util IDs to understand whether they are meant to represent the same entities in general, or not, and why the same entity (like PacifiCorp) has two IDs -- one as a Util and one as a BA, and why some IDs are associated with totally different entities when used as a BA ID vs. being used as a Util ID.

Problem IDs

These IDs show up as both BA IDs and Util IDs, but may be related to different entities, at least based on their names (ID: BA Name / Util Name):

• 189: PowerSouth Energy Co-op / Alabama Electric Co-op
• 12341: MISO / MidAmerican Energy
• 99999: MISO / "Other"
• 6455: Has like 10 different BA Names, but never a Utility Name
• 3046: Similarly, Progress Energy Carolinas or Duke, but never a Utility Name.
• 3253: Sometimes BA is CIPS/ Ameren, sometimes MISO
• 22500: BA is Westar or Western Resources Inc / Util is always Westar
• 17698: BA is Southwestern Electric Power Co or Central and Southwest / Util is always SWEPCo.
• 13434: ISO New England and New England Power Exchange are... the same thing?
• Sunflower Electric Power Corp shows up under 20391 and 18315
• 28503: Seven different BA Names, not sure if they're really the same thing, all mapping to USDOE-WAPA-Loveland-Ft. Collins Utility.
• 25471: 4 different WAPA BA names, all mapped to USDOE-WAPA-Upper Missouri-West Utility
• 2777: Are Louisiana Generating and Cajun Electric Power Co-op the same thing?
• 3542: Are Cincinnati Gas & Electric and Duke Energy Ohio the same thing?
• 5490: Are Duke Energy Corp and DTE Crete Operations the same thing?
• 15474: Another instance of "Central and Southwest" BA now with PS Co of OK. Also AEP using this ID in Oklahoma context as agent of... someone.
• 14610: Florida Municipal Power Pool / Orlando Utilities Comm
• 9996: Board of Public Utilities / City of Kansas City (KS)
• 13143: City of Muscatine / Board of Water, Electric & Communications
• 99996: Central and Southwest / "Several Municipals and Co-ops" (really? wtf?)
• 16534: BA of NorCal / SMUD
• 12698: Aquila Inc. / KCP&L Greater Missouri Ops
• 44372: TXU Electric Delivery Co / Oncor Electric Delivery Co
• 56669: MISO again, even though it has shown up as other BA IDs as well.
• 58790: Gridforce + Constellation Energy / Copper Mountain Solar 3
• 58791: NaturEner Power Watch / Pristine Sun LLC
• 58786: Duke Energy Progress or Carolinas / Sol Orchard San Diego? Really?
• 59504: Southwest Power Pool / Kirkwood Community College (this seems clearly wacked)
• 56545: Gridforce South / Pattern Operators LP

[zaneselvans]

It almost seems like the BA and Utility IDs really are totally differet sets of IDs that are not required to refer to the same entity, but that EIA has gone out of their way to preferentially use the same numerical ID to represent the same entity when they appear in both sets of IDs, which is hella confusing.

[zaneselvans]

The structure of the reporting entities seems to be something like:

• ISO/RTO → BA → Utility → Plant
• ISOs can also BAs -- and in fact can control several different BAs with different BA IDs.
• BAs can also be Utilities, but the set of IDs used for BAs and Utilities are functionally distinct.
• A BA will often encompass electricity from multiple Utilities.
• A Utility will most commonly only operate inside a single BA, but sometimes 2-3 different BAs.
• Up through 2012, each BA has a list of Utilities associated with it, reported in the balancing_authority_eia861 table[
• In 2013 and later, instead of associating individual utilities with each BA, most but not all Plants (from EIA 860) are associated with an individual BA (by BA Code). These codes only started getting reported in the balancing_authority_eia861 table as of 2013, but I think we've associated those BA codes with plants for earlier years in the ID harvesting and infill process. Not sure if the NA values in here are because there were inconsistent BA values reported for individual plants (and so we Nulled it out) or if there really were some plants that never had a BA associated with them (maybe plants that only appear in the pre-2013 data?).
• Each Plant (in EIA 860) does have a Utility associated with it (more than one really, the operators, but also the owners).

[zaneselvans]

In the context of integration with the FERC 714 data, if we have a Planning Area which is associated with a Utility, it seems straightforward to look up the collection of counties associated with that utility in service_territory_eia861 If we have a Planning Area that maps to a Balancing Authority, we can look up the utilities which were associated with that BA in 2010-2012, but not later, unless we want to try and figure out how to use the BA Code and the list of plants that are associated with that code to infer a set of utilities, whose service territories we can then go look up.

Given that both Utilities and BAs and their IDs are being reported in the FERC 714 Planning Area table, how will we know whether to try and look that ID up in the Balancing Authority vs. the Utility tables? Especially given that the ID is often the same in both? If the ID shows up as a utility with enumerated counties defining its service territory, can we be sure that there aren't other utilities who should also be included as part of the planning area, if that same utility shows up in the Balancing Authority table? Which should take precedence? If we go with BA, will we double count? If we go with Utility only, will we miss service territory? See also: #597 #601

[zaneselvans]

Notes from chat with @cmgosnell:

• Treat BA and Util IDs as totally separate namespaces.
• BA+State mappings do correspond to a Utility ID... but only as they appear in the Sales and other data tables -- so we could potentially harvest those releationships from the other tables, even though it's not explicitly reported in isolation.
• To collect all of these pairings of county and BA code, we can drop duplicates on the various EIA 861 data tables.
• So we're going to create a new entity table (Balancing Authorities) and a new glue table (connecting balancing authorities to utilities).