Write transform function to clean and normalize FERC 714 XBRL hourly historic load table

[aesharpe]

Tasks

Preview Give feedback

1. decide if/how to use pieces of the form 1 transforms (Extend FERC 714 forecasted and hourly load tables to 2023 by integrating XBRL tables and combining with existing CSV tables #3809 (comment))
2. pull relevant methods out of transform.ferc1/classes into more accessible functions
3. pull both csv and xbrl files into one asset
4. pull csv-based transform code out of existing transform function into their own modular functions
5. prep duration_xbrl: (convert the yearly reported records)
6. prep instant_xbrl: nada
7. prep xbrl: SO MUCH TIME SERIES CLEANING. objective: complete, continuous, non-duplicative datetime64[ns]
8. document small number of gaps in xbrl (mostly from daylight savings time hour)
9. prep csv: migrate csv-only relevant code into csv step
10. map_respondent_id_ferc714: csv/xbrl -> PUDL id (first phase done in Synchronize FERC 714 XBRL and CSV IDs and combine into one table #3846)
11. concatenate CSV + XBRL
12. deduplicate time series
13. map new bad utc offset codes
14. plot all respondents and do major spot fixing/timezone fixing
15. [maybe] rename this table from out_ to core_?
16. add the respondent_id_ferc714_csv and respondent_id_ferc714_xbrl into the table schema
17. add the Synchronize FERC 714 XBRL and CSV IDs and combine into one table #3846 little glue respondent id tables into db
18. back/forward fill the source respondent ids
19. add any additional info about the table into the metadata
20. sort the table before loading to make it most efficient for parquet
21. release notes
22. run all of the downstream analysis outputs

out of scope here -> issue or pr or otherwise

• make the extract loading wayyyy faster or skip it for this hourly table -> dealing w/ this in Migrate where/when we filter for the freshest XBRL data #3861

[cmgosnell]

should we add the respondent_id_ferc714_csv and respondent_id_ferc714_xbrl into the table schema?

reasons to have the source ids in the table

because this hourly table is a parquet table not a db table it doesn’t really have foreign key relationships to these soon to be small glue tables the stores the map between the pudl-derived ids and these new ids. i assume most users will primarily be familiar with or searching for a utility with one of these _{source} native IDs. usually we merge these things back into the tables during the out_ de-normalization phase but we don't do that to this table.

reasons to not have the source ids in the table

but adding these two columns into an already very large table is not the tidiest. one will always be null (null xbrl id in csv years and vice versa)... although we could fill in the values for their respective other source years.

[cmgosnell]

re plotting the demand timeseries, here are a few things I did. Nothing hugely bad jumped out at me tbh.

• plot full timeseries for all respondents - done with new pudl-derived respondent ids to see if there were weird disjointed things that would maybe indicate we mapped the ID's incorrectly. i was also looking for new weird big missing bits or new trends that were real different from the past. I did find a good handful of big gaps and periods where the demands was suddenly higher or lower in the CSV years but i'm assuming that's oos for this work
• plot 2019-now for all respondents: looking for similar oddities about the
• plot one month at a time. i tried a few months separately all from the new data. mostly for this one i was looking for phase shifts that could indicate that we got the timezone wrong. believe it or not nothing jumped out at me. I was mostly looking for which hour the peaks happened... this felt very unscientific I bet we could find some other way to do this more

a few fully unblocking imo respondent_id_ferc714's just to note for maybe later cleaning:

fully_empty = [42,47, 48,175,180,31, 97] # these guys looked always 0 or null
big_one_time_spikes = [19,161, 77] # mostly in csv years but these looked anomalous 
long_big_spikes = [16] # one ~year long spike back in the csv years

this is my little code snippet that i used for plot things.. this is the most complicated one so you can remove specific pieces of the mask to expand it

for rid in df.respondent_id_ferc714.unique():
    recent = df[(df.respondent_id_ferc714 == rid) & (df.datetime_utc.dt.month == 5) & (df.datetime_utc.dt.year == 2022)]
    if not recent.empty or recent.demand_mwh.notnull().any():
        recent.sort_values("datetime_utc").plot("datetime_utc", "demand_mwh", title=f"Respondent ID {rid}")

        plt.show()

some examples

these are not super helpful on their own but you get the gist
all time:

recent years:

one month: