FERC1 DataBase

[yifei-liu-yl]

Dear Catalyst community,

I attempted to access FERC1 data on PUDL and located the FERC Form 1 Data Dictionary (https://data.catalyst.coop/ferc1). As I understand, FERC began gathering this data in 1994. However, not all the tables on Datasette date back to that year. Some tables appear to have started in 2004 or 2006, which has left me confused and unsure if I am on the correct webpage and looking at the full data tables. Can anyone please provide insight on this?

Many thanks in advance!

Best,
Violet

[cmgosnell]

Hi Violet! This is a great question! You are in the right place if you want to look into the raw FERC Form 1 data. It does look like there are 19 tables that didn't start being reported until after 1994. All of them seem have started being published in the 2002-2009 range (see below for the list of tables).

some additional FERC1 context

FERC changed their data format in 2021. So If you want to see anything from 2021 (and on once they report additional years) you'll have to look at: https://data.catalyst.coop/ferc1_xbrl. But fair warning, it is a very different format/structure.

We've done a fair amount of work to clean and standardize these two formats with 21 FERC1 tables. You can find them in: https://data.catalyst.coop/pudl (they are all the tables with _ferc1 suffix).

how I answered this question (just in case you are curious! if not feel free to ignore this)

The f1_row_lit_tbl table is a helpful map table of all of the descriptive names that correspond to row_numbercolumns reported across many of the tables. It includes the date that the table was first reported and any changes to the row_number over time. So I grabbed that table out of the db and just determined the earliest year.

import pudl
import pandas as pd
import sqlalchemy as sa

pudl_settings = pudl.workspace.setup.get_defaults()
ferc1_engine = sa.create_engine(pudl_settings["ferc1_db"])
row_literal = pd.read_sql("f1_row_lit_tbl", ferc1_engine)
earliest_report_date_per_table = row_literal.groupby(["sched_table_name"])[["report_year"]].min()
earliest_report_date_per_table[earliest_report_date_per_table.report_year != 1994]

[bendnorman]

Hi @yifeivioletliu! Did this work for you?

[yifei-liu-yl]

Thank you so much for answering my question. This is very helpful! The f1_row_lit_tbl table sounds very useful in terms of figuring out the data.