Link historical hourly demand to geography

[zaneselvans]

The main underlying task in this project is to link historical hourly electricity demand to the geographic region in which that demand occurred. Unfortunately, this isn't very straightforward, but we have a bunch of building blocks to work with:

• Utility service territory geometries from HIFLD linking an EIA utility ID to a geographic region. These appear to only exist for 2018, but have a lot of spatial detail.
• Electricity Balancing Area geometries from HIFLD, linking the EIA utility ID of the utility operating the balancing area to the geometry. HIFLD calls these "Control areas". Again this geospatial information only appears to pertain to 2018.
• Electricity Planning Area geometries from HIFLD, linking the EIA utility ID of the utility responsible for the planning area to the geometry. These planning areas appear to correspond to the hourly demand data that is reported in FERC 714, but again it only seems to be relevant for 2018.
• A mapping from EIA utility ID to Counties served via a set of (state abbreviation, county name) pairs, which should be mappable to a county FIPS code, indicating in which counties that utility has equipment for delivering electricity to end users. Each county may show up in the list for more than one utility, if more than one utility delivers power in that county. This data comes from the EIA Form 861, and appears to be available going back at least as far as the FERC 714 hourly data (2006). This would seem to be most analogous to the present-day utility service territory geometries available from HIFLD, but probably less granular, since it's county by county, with some duplicity in the listed counties.
• A mapping between FERC 714 Planning Area respondent ID, and the name of each of the utilities which make up that Planning Area. This data appears to be available as far back as the FERC 714 data goes (2006-2018). In theory this should allow us to reconstruct the historical geographies of the planning areas.
• A mapping between County FIPS ID and County Geometry This should be easily accessible within the US Census datasets.
• A mapping between State and County name, and County FIPS ID Hopefully these names are standardized and we can just join on them. But probably they aren't.
• EIA Plant ID to Balancing Authority Code in the EIA 860 Plants table.
• Balancing Authority Utility ID to Balancing Authority Code in the EIA 861 Balancing_Authority.xlsx files.
• Auffhammer et al (2017) apparently mapped counties to planning areas, but their process is not sufficiently detailed enough to replicate.

[zaneselvans]

The methodology outlined by Auffhammer et al. (2017) seems promising, but the publication seems to be missing some details. They are trying to link the entities reporting load data (in FERC 714 by planning area) to the utilities listed in EIA 861, which are associated with particular states + counties.

They start with the list of all utilities reporting hourly demand, and exclude the following:

• Respondents whose reporting was completely subsumed by any of the following major ISOs: Elec- tricity Reliability Council of Texas (ERCOT), ISO New England (ISO-NE), PJM, and NYISO. We obtained sub-regional load data from each of the ISOs separately
• Respondents outside of the contiguous United States
• Respondents with <0 kWh total reported load
• Respondents whose entries were internal tests
• Respondents whose data duplicated other FERC-714 respondents

Question: How do they know whose data is is duplicated by other FERC-714 respondents?

For many of the respondents that appear in the FERC data, FERC provides an EIA utility ID. This ID can be used to directly look up the counties served by that utility in the EIA 861.

Question: How do we know it is valid to assume a utility's service territory (recorded in EIA 861) is the same as the "planning area" which that utility administers?

A smaller number of the EIA Utility IDs listed in the FERC 714 data did not map directly to a utility with an indicated service territory, but did map to a utility listed as operating a balancing authority, and (apparently?) EIA 861 provides a mapping of individual utilities with retail service territories to the corresponding utility entity that is the balancing authority, allowing the individual constituent utilities listed in the FERC 714 planning area descriptions to be indirectly linked to a set of counties. (Note that there is substantial overlap in the areas of these balancing authorities, and the individual utility service areas identified in the step above.)

Looking into the historical EIA 861 data, File1.xls starting in 1999, and later on file1_cao_YYYY.xls and finally in 2012 balancing_authority_YYYY.xlsx include mappings of individual EIA utility IDs to the EIA utility IDs of the entities operating their balancing area. However, from 2013 onward, the Balancing Authority code is no longer a utility ID, and instead becomes a string abbreviation. So far as I can tell this string abbreviation doesn't show up anywhere else in the EIA 861 data, but it is associated with individual plants in the EIA 860 data -- so it seems like there was some reorganization of this data on the EIA side, giving each EIA plant a BA instead of each little utility a BA. Which I guess probably makes more sense.

Question: Given the substantial overlap in these territories, what's the right way to allocate demand? What makes us think that the same demand isn't being reported multiple times by different entities

For the remaining utilities reporting hourly demand in FERC 714, which were not mapped to utilities and the counties they serve through the above processes, the authors manually mapped the names of the utilities making up the reporting planning area to individual utilities reported in EIA 861 based on their names alone.

[gschivley]

I’ll try to get my notebook of an initial mapping up later today or tomorrow. I also reached out to Max Auffhammer and he is willing to answer specific questions. A few things I’m running up against so far:

• Some respondents don’t have clear matches to utilities or BAs using either the eia_code or string matching. One example is Southern Power Company (iirc, on my phone right now).
• Some respondents like pacificorp are huge. Their sales in each state can be constrained by 861 but is there any way to allocate or disaggregate hourly profiles that accounts for different customer types/use patterns?

[zaneselvans]

@gschivley My understanding is that in the Auffhammer paper, they were trying to figure out "What location is associated with this demand?" as opposed to "What demand is associated with this location." -- does that seem right to you? Figuring out what all demand is associated with a county seems harder, since you want to try and avoid double counting, or missing demand. It seemed like in their maps there were a lot of counties that showed up associated with more than one of the respondents.

[zaneselvans]

Potential Demand Mapping Method:

Assumptions:

• We have a clean mapping from State + County Name to FIPS County Code
• Utility Service Territory as indicated by the list of Counties in which an EIA Utility operates, reported in the EIA 861 data is also reflective of the geography of the Planning Area associated with that utility, for which FERC collects hourly demand data.
• Constituent utilities making up a balancing authority (as indicated in EIA 861 data) cover the same geographic area as the constituent utilities making up the corresponding Planning Area, at the resolution of individual counties.

Process:

For each FERC 714 Respondent that reports hourly demand, we must identify the set of County FIPS IDs associated with that respondent.

• Create an explicit mapping of State + County Name to County FIPS ID, and merge it with the Service Territory data from EIA 861.
• For each FERC 714 respondent that reports hourly demand, do one of the following:
  • If the respondent_id to eia_code mapping exists, and indicates a utility which is listed in the EIA 861 Service Territory table, then use those counties to generate the geometry of the planning area for that respondent ID.
  • If the respondent_id to eia_code mapping exists, and indicates a utility which does not show up in the EIA 861 Service Territory table, but which does appear in the EIA 861 Balancing Authority table (for years 2006-2012), use the BA table to generate a list of constituent utilities whose associated counties can be looked up in the Service Territory table.
  • If the respondent_id to eia_code mapping does not exist, or indicates a BA for years 2013 and later (which means it doesn't cleanly indicate a set of constituent utilities we can map directly to the Service Territory table), then use the utility names in the FERC 714 data, associated with that planning area, to identify the most likely set of associated utilities in the EIA 861 Service Territories table.

Sanity Checks / Success Metrics:

• What fraction of the overall reported demand in FERC 714 has been attributed to a county?
• What fraction of overall counties are associated with a single respondent vs. multiple respondents?
• What fraction of the overall US population (at the county level) has been associated with at least one FERC 714 respondent?
• When the FERC 714 demand is aggregated to the state level, at monthly time resolution, how well does it correlate with the monthly total electricity demand / consumption reported to EIA across the years?
• How closely does the county-by-county attribution of demand in 2018 correlate with the attribution we get if we use the HIFLD planning area geometries for 2018?

[gschivley]

I finally cleaned up (a little) the work I've done and posted it as a notebook. Can be downloaded or run through colab/mybinder.