Review inconsistent field attributes for fields of the same name

[ezwelty]

A review of the field metadata in src/pudl/package_data/meta/datapkg/datapackage.json reveals the following inconsistencies in constraints.enum and description for fields of the same name across different resources.

Does additional field metadata exist elsewhere than src/pudl/package_data/meta/datapkg/datapackage.json?

Our task is to choose one standard attribute value where possible, rename the field in some resources as needed, and determine whether field metadata can be universal or needs to be customizable by resource (e.g. service_territory_eia861, respondent_id_ferc714).

Fields are removed from the tables below as they are resolved:

field	constraints.enum	resources
state	AL, AR, AZ, CA, CO, CT, DC, DE, FL, GA, IA, ID, IL, IN, KS, KY, LA, MA, MD, ME, MI, MN, MO, MS, MT, NC, ND, NE, NH, NJ, NM, NV, NY, OH, OK, OR, PA, RI, SC, SD, TN, TX, UT, VA, VT, WA, WI, WV, WY	hourly_emissions_epacems
	AUS, COL, CAN, IDN, POL, RUS, GBR, VEN, other_country, unknown, AK, AL, AR, AS, AZ, CA, CO, CT, DC, DE, FL, GA, GU, HI, IA, ID, IL, IN, KS, KY, LA, MA, MD, ME, MI, MN, MO, MP, MS, MT, NA, NC, ND, NE, NH, NJ, NM, NV, NY, OH, OK, OR, PA, PR, RI, SC, SD, TN, TX, UT, VA, VI, VT, WA, WI, WV, WY	coalmine_eia923

field	description	resources
avg_num_employees	Average number of employees.	plants_hydro_ferc1, plants_pumped_storage_ferc1
	Average number of plant employees during report year.	plants_steam_ferc1
boiler_id	Boiler identification code. Alphanumeric.	boiler_fuel_eia923
	EIA-assigned boiler identification code.	boiler_generator_assn_eia860
	The EIA-assigned boiler identification code. Alphanumeric.	boilers_entity_eia
capacity_mw	Name plate capacity in megawatts.	plants_small_ferc1
	The highest value on the generator nameplate in megawatts rounded to the nearest tenth.	generators_eia860
	Total installed (nameplate) capacity, in megawatts.	plants_hydro_ferc1, plants_pumped_storage_ferc1
	Total installed plant capacity in MW.	plants_steam_ferc1
capex_per_mw	Capital expenses per MW of installed plant capacity.	plants_steam_ferc1
	Cost of plant per megawatt of installed (nameplate) capacity. Nominal USD.	plants_hydro_ferc1, plants_pumped_storage_ferc1
	Plant costs (including asset retirement costs) per megawatt. Nominal USD.	plants_small_ferc1
city	Name of the city in which operator/owner is located	utilities_eia860
	The plant's city.	plants_entity_eia
construction_year	Four digit year of the plant's original construction.	plants_hydro_ferc1, plants_pumped_storage_ferc1
	Original year of plant construction.	plants_small_ferc1
	Year the plant's oldest still operational unit was built.	plants_steam_ferc1
description	Description of the FERC depreciation account, as listed on FERC Form 1, Page 219.	ferc_depreciation_lines
	Long description of the FERC Account.	ferc_accounts
fuel_cost_per_mmbtu	All costs incurred in the purchase and delivery of the fuel to the plant in cents per million Btu(MMBtu) to the nearest 0.1 cent.	fuel_receipts_costs_eia923
	Average cost of fuel consumed in the report year, in nominal USD per mmBTU of fuel heat content.	fuel_ferc1
	Average fuel cost per mmBTU (if applicable). Nominal USD.	plants_small_ferc1
fuel_mmbtu_per_unit	Average heat content of fuel consumed in the report year, in mmBTU per reported fuel unit.	fuel_ferc1
	Heat content of the fuel in millions of Btus per physical unit.	generation_fuel_eia923, boiler_fuel_eia923
fuel_type	Kind of fuel. Originally reported to FERC as a freeform string. Assigned a canonical value by PUDL based on our best guess.	plants_small_ferc1
	The fuel code reported to EIA. Two or three letter alphanumeric.	generation_fuel_eia923
fuel_type_code_pudl	PUDL assigned code indicating the general fuel type.	fuel_ferc1
	Standardized fuel codes in PUDL.	generators_eia860, fuel_receipts_costs_eia923, generation_fuel_eia923, boiler_fuel_eia923
generator_id	EIA-assigned generator identification code.	boiler_generator_assn_eia860
	Generator identification code. Often numeric, but sometimes includes letters. It's a string!	generation_eia923
	Generator identification number	generators_entity_eia
	Generator identification number.	generators_eia860, ownership_eia860
installation_year	Four digit year in which the last unit was installed.	plants_hydro_ferc1, plants_pumped_storage_ferc1
	Year the plant's most recently built unit was installed.	plants_steam_ferc1
line_id	A human readable string uniquely identifying the FERC depreciation account. Used in lieu of the actual line number, as those numbers are not guaranteed to be consistent from year to year.	ferc_depreciation_lines
	Line numbers, and corresponding FERC account number from FERC Form 1, page 2019, Accumulated Provision for Depreciation of Electric Utility Plant (Account 108).	accumulated_depreciation_ferc1
net_generation_mwh	Net generation (exclusive of plant use) in MWh during report year.	plants_steam_ferc1
	Net generation excluding plant use, in megawatt-hours.	plants_small_ferc1
	Net generation for specified period in megawatthours (MWh).	generation_eia923
	Net generation, exclusive of plant use, in megawatt hours.	plants_hydro_ferc1, plants_pumped_storage_ferc1
	Net generation, year to date in megawatthours (MWh). This is total electrical output net of station service. In the case of combined heat and power plants, this value is intended to include internal consumption of electricity for the purposes of a production process, as well as power put on the grid.	generation_fuel_eia923
peak_demand_mw	Net peak demand experienced by the plant in MW in report year.	plants_steam_ferc1
	Net peak demand for 60 minutes. Note: in some cases peak demand for other time periods may have been reported instead, if hourly peak demand was unavailable.	plants_small_ferc1
	Net peak demand on the plant (60-minute integration), in megawatts.	plants_hydro_ferc1, plants_pumped_storage_ferc1
plant_capability_mw	Net continuous plant capability in MW	plants_steam_ferc1
	Net plant capability in megawatts.	plants_pumped_storage_ferc1
plant_hours_connected_while_generating	Hours the plant was connected to load while generating.	plants_hydro_ferc1, plants_pumped_storage_ferc1
	Total number hours the plant was generated and connected to load during report year.	plants_steam_ferc1
plant_name_ferc1	Name of the plant, as reported to FERC. This is a freeform string, not guaranteed to be consistent across references to the same plant.	fuel_ferc1, plants_ferc1, plants_hydro_ferc1, plants_pumped_storage_ferc1, plants_steam_ferc1
	PUDL assigned simplified plant name.	plants_small_ferc1
plant_type	Kind of plant (Run-of-River or Storage).	plants_hydro_ferc1
	PUDL assigned plant type. This is a best guess based on the fuel type, plant name, and other attributes.	plants_small_ferc1
	Simplified plant type, categorized by PUDL based on our best guess of what was intended based on freeform string reported to FERC. Unidentifiable types are null.	plants_steam_ferc1
prime_mover_code	Code for the type of prime mover (e.g. CT, CG)	boilers_entity_eia
	EIA assigned code for the prime mover (i.e. the engine, turbine, water wheel, or similar machine that drives an electric generator)	generators_entity_eia
	Type of prime mover.	generation_fuel_eia923
state	Plant state. Two letter US state and territory abbreviations.	plants_entity_eia
	State of the operator/owner	utilities_eia860
	State the plant is located in.	hourly_emissions_epacems
	Two letter US state abbreviations and three letter ISO-3166-1 country codes for international mines.	coalmine_eia923
street_address	Plant street address	plants_entity_eia
	Street address of the operator/owner	utilities_eia860
utility_id_eia	EIA-assigned identification number for the company that is responsible for the day-to-day operations of the generator.	generators_eia860, ownership_eia860, plants_eia860, utilities_eia860
	The EIA Utility Identification number.	utilities_eia, utilities_entity_eia
zip_code	Plant street address	plants_entity_eia
	Zip code of the operator/owner	utilities_eia860

[zaneselvans]

I almost certainly typed most of these back in the day.

• state will need to be reorganized in some cases I think, and the enums may need to be customized by field? For the US political subdivisions that have a 2 letter abbreviation, maybe we call these columns something like us_state_territory? and that could include DC, Guam, Virgin Islands, Puerto Rico, etc. For coalmine_eia923 I think we probably want to split it into two columns, one which the 3-letter ISO country abbreviation, and another which is the US state or territory abbreviation. For the purposes of column value constraints, I suspect that we will be okay with having all of the 2-letter abbreviations of US political subdivisions. However there are some iteration cases (e.g. in the CEMS) where we want only to iterate over a subset of those abbreviations and that subset will need to be defined elsewhere. Oh also I think there are some places where Canadian provinces have snuck in, and at this point I've probably replaced them all with CAN, but if we have state-or-territory and also country... maybe we need to think about this more...
• I suspect that the multiply defined columns from ferc1 mostly all refer to the same thing, but they may be written differently on the different pages of the blank PDF, which is where these descriptions would have come from. We will need to review and make sure they actually mean the same thing between say, the steam and small plants tables, but I think in most cases they will.
• city really is referring to two different things: where a plant is, vs. the city where a utility's offices / mailing address are. So these should maybe get renamed like... plant_city and utility_city or something like that?
• All the "nominal USD" comments can probably be removed, since all of the costs are in USD, and they're all nominal (not inflation adjusted)
• fuel_type is fraught. There are many, many different categorizations. They should probably all indicate where they came from in the column name. E.g. fuel_type_eia or fuel_type_ferc1 or fuel_type_pudl
• generator_id really is all the same, but it should probably become generator_id_eia and all the descriptions should be the same and they are hella not numbers. definitely strings. But lots of numbers. And this fucks up the data types all the time when you only read a subset of them.
• Not sure what is going on with line_id -- looks weird.
• mine_id_pudl should definitely be the same. Probably should combine the two -- yes it's a surrogate key that we made, and also it identifies a mine.
• plant_*_ferc1 there's some now abandoned table cleanup that was being applied to the small plants table, which we may need to remediate.
• plant_type has different valid values in different plant tables. Might need to make more specific names, or combine all of the valid values across the tables into a single enum and a single more general plant type description.
• prime_mover_code is all the same I think. We have also talked about replacing many of the inscrutable short codes with longer snake_case descriptions that are actually readable, and using an ENUM to constrain them.
• state descriptions are referring to different things. Probably need to rename some columns utility/operator state vs. plant state
• street_address ditto city and state.
• I think utility_id_eia should all be the same, though only one of those descriptions would make sense in all contexts. There are cases in which there's an owner vs. operator distinction, and "utility" is kind of the generic name we've used when it doesn't matter.
• zip_code also needs to differentiate plant vs. corporate entity.

[cmgosnell]

After reading through all of these, I agree w/ a lot of what zane said. Outside of the enums the columns in here that seem like they need to be broken out are:

• street_address
• zip_code
• state
• fuel_type

I have apprehension about breaking out fuel_type into fuel_type_eia and fuel_type_ferc1 because we have conveniently squished them to be comparable/merge-able columns, but they are generated in very different ways.

[ezwelty]

All the unresolved fields listed above have been added to pudl.metadata.fields with only a name and a type. You can fill in their description and constraints.enum (or rename them) as you decide how to resolve them. As needed, you can override the default field metadata for a particular resource in the resource's metadata in pudl.metadata.resources:

'resource': {
    'schema': {
        'fields': ['default_field', {'name': 'custom_field', 'description': 'Custom description'}]
    }
}