EIA to FERC1 Entity Matching Example

record-linkage-example-eia.csv

record-linkage-example-ferc1.csv

record-linkage-example-matches.csv

This is an example of the kinds of records we're matching between the FERC 1 and EIA datasets.

• Most of the columns are numeric / quantitative, with the exception of plant_name which is a string feature that we've been using the jaro-winkler distance to compare, and fuel_type_code_pudl which is a categorical column.
• We are primarily trying to ensure that every record from the FERC 1 dataset is associated with the best available match from the EIA dataset. * The FERC 1 records are the "real" records -- data actually reported to FERC -- while the EIA records are generally aggregations of several individual generator records that may have been scaled according to what portion of the generator is owned by an individual utility. We do a bunch of work to try and generate any possible aggregation of EIA records that might potentially show up as something that's reported in a singlle FERC 1 record, so we expect most of them not to end up finding a good match on the FERC side.
• We currently use (plant_id_pudl, utility_id_pudl, report_date) as blocking IDs to minimize the number of records that need to be compared to each other.
• One issue with the current system is that the Python record linkage toolkit (built on top of scikit-learn) returns all matches that have a probability of > 50%, but we ultimately need to choose a single best match.
• There are a lot of NULL values especially on the EIA side, because when the records being aggregated don't share a consistent categorical attribute, or if any one of the records has an NA value, the whole aggregated record ends up with an NA value.
• The plant_name_new column is a composition of the plant's reported EIA name (in this case Barry) and strings that describe the portion of the plant that has been aggregated to create the record, since many times the FERC records will include that kind of information in the plant_name string (e.g. "Barry Combined Cycle" or "Barry CC" or "Barry Unit 2").
• Within a given plant-utility-year grouping, it is normally the case that all of the EIA generators (the smallest division of a plant) will end up being associated with a FERC plant, and that each of them will only be used once (though often as part of some larger aggregation of generators). When this doesn't happen, it's typically because there are some very small generators that aren't required to report to FERC. And then occasionally the same utility will report both the entire plant, and also its ownership share. In which case some generators would end up getting used more than once.
• While we use the report_year as a blocking ID right now, there's a lot of continuity between years, and when doing the mapping by hand it's often useful to look at the whole time series for a particular plant, or sub-plant aggregation, especially when there's a gap in the data for some of the columns. Not sure if that's something that's best taken advantage of in the entity matching process, or if it means we should be filling in some of the NA values before we get this far.
• There are a few instances in which non-standard aggregations are necessary to match the records as they are reported in FERC. E.g. two separate units that were installed in the same year might be reported together in FERC all the time (as with the Comanche 1 & 2 units in Pueblo, CO) even though they are distinct generators, units, etc. Currently these are being overridden by hand after the fact but it would be nice if this kind of thing could be worked into the process more uniformly.
• In the example-matches the plant_part column indicates what level of aggregation the record came from. In the full table there are also long unique IDs that convey what collection of individual generator records and ownership slices went into creating the aggregated records so that can all be disentangled later.