The pudl.init_db() subroutine is getting too long and has become unreadable. Separate various logical pieces of it into functions that define particular portions of the process, including:

• importing the static tables/lists
• generating and importing the "glue" tables.
• the import & cleanup for each of the ferc1 tables.

This will make it easier to hand off & test the individual tasks of importing new ferc1 tables.

The constraints set on pulling the plants for the id mapping (ex. excluding anything under 5 MW) excluded some fields like common plants, which have expenses associated with them, but no capacity. Not a top priority by any means, but eventually we should go back through these constraints to see what we missed.

Example: for the f1_hydro table, we lost respondent_id, plant_names:
122, 'Common Hydro Plant'; 70, 'Common Facilities'

Periodically FERC updates their uniform chart of accounts for electric plant (e.g. they just recently added accounts for electricity storage devices, yay!). When this happens, the FERC Form 1 line numbers that correspond to a given FERC account may change. If that happens, all of our parsing of the f1_plant_in_srvce data will get messed up.

Somebody needs to go back and spot check some past years (between 2004 and 2015) to figure out if/when the lines-to-accounts mapping has changed in the past, and we'll need to generate a new mapping for each different set of lines/accounts. This could be done with old blank copies of Form 1 (maybe we can ask FERC?) or from the archived versions of the form that we got generated for PSCo. The page(s) we're interested in are FERC Form 1 pp 204-207.

For reference, the line-to-account mapping for 2015 is defined in a DataFrame named: pudl.constants.ferc_electric_plant_accounts

There's a respondent-wide depreciation value reported to FERC, but it's not in the Plant In Service table. Uday/CPI want that number, so we need to figure out which FERC1 table it's in, and make sure we pull that table into the FERC1 and then PUDL DBs.

The small plants data need to be ingested into PUDL, along the same lines as f1_steam, f1_fuel, etc. Define the ORM class in models_ferc1.py and define the ingest function in pudl.py.

Many of the database tables we are creating have dozens of columns. We need readable, memorable, tab-completable convention for naming them. This could be based on the table or data source they are being imported from, and also the nature of the data within them.

We need a table that indicates which plants are associated with what utilities. For now it can just contain two columns: utility_id and plant_id, both of which are primary keys, and foreign keys. The same table might later also store the ownership percentages.

The f1_steam table in the ferc1 database has at least two freeform fields that need to be cleaned up. They are type_const and plant_kind. Export a list of all unique strings found in those two fields, from all of the data we can import into the database simultaneously -- years 2004-2015. Using whatever information you can find about what those fields are supposed to describe (e.g. the blank FERC Form 1 document, and the instructions for filling it out) categorize the strings into a few meaningful categories, using the ferc1_fuel_strings and ferc1_fuel_unit_strings dictionary-of-lists in constants.py as a model. Look at whatever other fields you need to within the f1_steam table for context on what is meant by the type_const and plant_kind fields. This issue is complete when there are ferc1_type_const and ferc1_plant_kind dictionaries in constants.py that can be used to clean up these columns.

Get at least the f1_fuel data automatically flowing into the PUDL database when init_db() is run. This will require cleaning up any data fields which are freeform strings (fuel, fuel_unit), deciding which records are so bad that they can't be imported, and ensuring that the necessary foreign key tables are consistent with the actual contents of the f1_fuel table.

The ferc1 module can now pull in data from the old DBF files from 2004-2015, however, the database can only hold one year's worth of data at a time, because the non-data tables (like f1_respondents) don't have a year field, and so entries for the same respondents from different years collide in the database -- you can't add respondent_id 134 to the table more than once. There needs to be logic incorporated that allows the union of all respondent_id's and respondent_names to be added to f1_respondent, and something similar for other non-data tables.

Right now there are some hard coded paths referring to data files within the project. Mostly they currently assume a Unix/OSX type filesystem. They should be made platform agnostic, using the os.path module.

Making sure that there’s a mapping for the older tables that have different column names

Rather than hard-coding the year 2015 into the database ingestion, the user should be able to specify what year's data they want to pull in (or potentially which years they want to pull in?) and have that data get pulled. This will almost certainly involve some more grungy data cleaning, as new and exciting freeform strings describing fuels etc. will appear in previous years' data.

There are many static tables (e.g. abbreviations of NERC regions) that we should have available as foreign keys within the PUDL Database. They are summarized in the back of the EIA923 spreadsheet. Because they're simple, they'll also provide a good practice run for defining tables using the ORM, and getting those tables populated when init_db() runs.

The f1_plant_in_srvce table contains all the FERC account balance information -- nearly ~70 separate individual accounts in all -- on a respondent-by-respondent basis. We need to slurp it up!

• milestones,
• weekly goals,
• issues;
• put goals for the week in there
• send that to team

Conservative estimate preferred by Alana
-Breaking up by task
-End of year - more heavy there - running payroll, team management with meetings, running invoices, running invoices

• Beginning of year - heavier with set up

Before monday 2/27, do a close read of the draft bylaws, and make any last comments before we send them to Bill and/or Jason for final drafting changes. Put edits in the "remaining questions" Google doc: https://docs.google.com/document/d/1cu_-FTBZ3MXM0mopGPq6ubXjG8aGHNMU6xxje9Jh_-8/edit#

The constants.py module is accumulating a lot of values, which pertain to different parts of the project, some of which contain the same kind of information but from different sources. We need a well defined naming convention so that we don't end up using the wrong values.

Right now there's a hard-coded list of DBF files which should be pulled into the ferc1 database clone buried within ferc1.init_db. This list should instead be an input into the function. A default list of DBF files/tables to import can be stored in the constants module.

RMFU offered to help us write our bylaws for $100/hr w/ a $750 cap. Once we've adopted the bylaws... we should really pay them.

Alongside the f1_plant_in_srvce table which describes the balances and changes in the electric plant in service, we need to ingest information about utility-wide depreciation, which comes from p. 219 of FERC Form 1, and is stored in table f1_accumdepr_prvsn. We need to define a PUDL DB table for this data, and write an ingest function. It appears similar in structure to f1_plant_in_srvce, which means we'll need to do a line number -> meaningful description dictionary/data table too, and hope that they don't change from year to year.

Create the necessary tables in the PUDL DB to hold both FERC Form 1 annual fuel information (from the f1_fuel table) and EIA923 monthly fuel deliveries. Demonstrate the ability to populate these tables with information from FERC Form 1 and EIA923.

to do for Monday 3/6

Many FERC1 data tables are now successfully importing into PUDL, but only for the year 2015. Once we have the glue tables updated with relationships between plants and utilities for the years 2004-2015 (See Issue #26 ), we'll need to revisit the imports of all of the other years of these older tables, and make sure they still work.

The ferc1 database has a table called f1_pumped_storage which stores attributes of various pumped hydro storage plants around the US. It's the simplest of the tables describing plants. Define a table to receive this data using the ORM in models_ferc1.py. Look at the FuelFERC1 class as a template.

Then create a function in pudl.py that pulls the f1_pumped_hydro data out of the ferc1 database into a DataFrame using pd.read_sql(), cleans it up as necessary for insertion into the PUDL DB, and adds it using DataFrame.to_sql(). Look at how the same work is done by for importing the f1_fuel data as an example.

When this issue is complete, pudl.init_db() should successfully import the pumped_hydro data.

As we finally approach having our initially desired data pulled into PUDL from FERC1 & EIA923 we need to have a full list of the issues required to do an initial alpha release (yay!) of PUDL that can be used to do interesting things in the world... like make some plots for potential funders to ogle.

Get the f1_hydro table imported into PUDL from FERC1 a process similar to that for the f1_steam and f1_pumped_storage to define the database table using the ORM in models_ferc1.py and a corresponding ingest function within pudl.py

Create schemata for the PUDL database tables which we will import the EIA923 data into, using the ORM syntax.

We need a readable, easily rememberable convention for the names of the classes which we are constructing with the SQLAlchemy ORM. This should determine the name of the class, and the name of the DB table which objects of this class will be records within.

We can pull in FERC Form 1 data from 2004 onward. However, the id mapping "glue" tables that we have right now only encompass information from the 2015 FERC database. If we want to be able to work with multiple years of FERC data in the PUDL database, we need glue for those other years. Thus, the id mapping exercise needs to be expanded to pull in new associations between plants & utilities from prior FERC years.

If @cmgosnell & @swinter2011 can indicate which fields they need from the various plants tables to do the mapping, @zaneselvans can pull them from the 2004-2015 Form 1 DB for matching. If we can come up with a common set of columns for all the different types of plants, we could do it all in one go maybe?

• create initial categories
• create member accounts
• create stock ledger
• run the above by the accountant
• anything else needed?

After the fuel and fuel_unit strings are cleaned up in the f1_fuel import, any record which contains any NA values is dropped, before the DataFrame is pulled into the PUDL DB. In some cases, this means we lose some data. E.g. some utilities only report their mmbtu/kWh numbers on a separate "Total" line. We should check and see whether there's a less destructive way we can deal with these leftover records.

check to see if all metadata-type variables in models_eia923 have an associated dictionary in constants.py; once there are dictionaries create classes in models.py;
for new static constants added in models.py, add to ingest_static_tables function in pudl.py.

• availability
• invoicing and payment
• getting time in by end of month

Using a process similar to that for f1_fuel, f1_steam, f1_hydro, etc. define a DB table for the f1_purchased_pwr table using the ORM in models_ferc1, and flesh out the ingest function in pudl.py.