Fix FERC714 validation failure

[cmgosnell]

There are 90 missing records post #2999. In my understanding this stems from the processing within annualized_respondents_ferc714 and is filtering down into summarized_demand_ferc714 (which is a db table and output table being min/max row checked). It seems there are 6 respondents that are missing from the new table. I pulled the last-passed nightly build next to my local db:

>>> tbl_name = "summarized_demand_ferc714"
>>> tbl= pd.read_sql(tbl_name, pudl_engine)
>>> tbl_night = pd.read_sql_table(tbl_name, pudl_engine_night)
>>> pks = pudl.metadata.classes.Resource.from_id(
...    tbl_name
... ).schema.primary_key
>>> tbl = tbl.set_index(pks)
>>> tbl_night = tbl_night.set_index(pks)
>>> assert(tbl.index.difference(tbl_night.index).empty)
>>> missing.reset_index().respondent_id_ferc714.value_counts()

respondent_id_ferc714
153    15
155    15
204    15
229    15
288    15
291    15
Name: count, dtype: int64

@rousik you did some digging into this as well and if you have a similar view but maybe found a more broad issue with this change

[rousik]

@cmgosnell yeah, I found that and reported on my findings on slack (still not sure what comms channel to use when) here: https://catalystcooperative.slack.com/archives/C02BSMJJVR8/p1699336003657749. I have dug into the data that feeds into this table and found out that report_date was up to recently calculated very incorrectly, so report_date year did not match the year of utc_datetime in about 80% of the time. With the recent change, we have eliminated null report_date values and aligned most of the date year with utc_datetime (0.07% discrepancy, likely around end of year). I suspect that perhaps the loss is due to shifting of the rows between the years?

[rousik]

After further digging, I can see that the only piece of information that flows from demand_hourly_pa_ferc714 are distinct report_date non-null values and those have not changed as a result of my change. I suspect that the changes in here are likely due to changes of the other inputs, namely one of these:

    respondent_id_ferc714: pd.DataFrame,
    denorm_utilities_eia: pd.DataFrame,
    service_territory_eia861: pd.DataFrame,
    balancing_authority_eia861: pd.DataFrame,

[cmgosnell]

Okay @rousik well I think I've isolated at which point in the processing for demand_hourly_pa_ferc714 these 6 now-missing respondents are dropped. These respondents don't show up in georeferenced_counties_ferc714. So when we are doing an inner merge here we are loosing these guys.

why they showed up before is still a bit of a mystery to me...

[cmgosnell]

okay these records indeed don't have fipsified_respondents_ferc714 and in the old version they had no actual fips codes and we think that is okay! I am going to convert this inner merge to a left merge so they annualized data shows up. but fix the row could for the fipsified respondents.

(also i'm going to add in a validation test re the demand_hourly_pa_ferc714 no more than .1% of the years in the datetime column should be off from the report_date column)

[jdangerx]

@cmgosnell is this closeable?

[zaneselvans]

@cmgosnell I'm closing since this seems to be working... let us know if there's still something here to address.