Integrate PHMSA transmission and distribution data

[e-belfer]

Scope of PR:

Produce PHMSA assets in dagster from the transmission and distribution tables, mirroring the format of the most recent year's form.

Design Notes

Transmission Data (1970-present)

Table design

All form parts are based on the 2021 form. Form part letters change over time, so this will require pairing older fields from .xlsx files to their correct tables.

Parts A-D: core_phmsa__yearly_transmission_summary_by_commodity

• Priority: high
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER
• The summary table: one row per commodity, lists summarized miles of pipe, volume, and onshore/offshore miles of pipe by category.
• Fields B and D are actually summaries from other fields, and could be compared directly to them as an additional step.
• Part C is instructed to only be completed one time in all forms. This field could be filled in by operator ID and year to all relevant reports it covers.

Parts F-G: core_phmsa__yearly_inspections_and_assessments

• Priority: high
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should have either information on interstate inspections or information on inspections in a particular state (as determined by the INTER_INTRA column
• These fields don't exist prior to 2010.

Part H: core_phmsa__yearly_miles_of_transmission_pipe_by_nps

• Priority: medium
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.
• Could maybe be combined with Part I.

Part I: core_phmsa__yearly_miles_of_gathering_pipe_by_nps

• Priority: medium
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.
• Could maybe be combined with Part H.
• Additional columns (e.g., PARTIONCADDITIONAL) are strings that will require parsing and reformatting to be of actual use.

Part J: core_phmsa__yearly_miles_of_pipe_by_decade_installed

• Priority: high
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.
• Could maybe get scavenged into a harvested table across years, since this information should largely be identical year to year.

Part K: core_phmsa__yearly_miles_of_transmission_pipe_by_specified_minimum_yield_strength

• Priority: ??
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.

Part L: core_phmsa__yearly_miles_of_pipe_by_class_location

• Priority: ??
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.
• Feeds into Part B.

Part M: core_phmsa__yearly_failures_leaks_repairs

• Priority: High
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.

Part P: core_phmsa__yearly_miles_of_pipe_by_material

• Priority: ??
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.
• Feeds into Part D.

Part Q: core_phmsa__yearly_gas_transmission_miles_by_maop_determination_method

• Priority: ??
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.

Part R: core_phmsa__yearly_gas_transmission_miles_by_pt_range_and_internal_inspection

• Priority: ??
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.

Part S: core_phmsa__yearly_transmission_materials_verification

• Priority: ??
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.

Part T: core_phmsa__yearly_transmission_hca_miles_by_determination_method_and_risk_model

• Priority: ??
• Primary key: OPERATOR_ID, REPORT_YEAR, REPORT_NUMBER, INTER_INTRA, STATE_NAME
• Each row should correspond to interstate pipelines within one state, or intrastate pipelines within one state.
• This table has a bunch of different categoricals that will want to get reorganized.

Misc.

• Parts O + N are the preparer's signature, email, telephone etc. (N), and the senior executive officers who signed off on the form (O). Not sure if we want to include these at all in our DB, alternately they could get appended to Parts A-E as part of the summary information on the report.
• Some fields are not in the form but are added to the dataset, they're generally noted in the bottom of the PDF or in a separate file for each folder.

Distribution Data (1970-present)

Table design

• All form parts are based on the 2021 form. Form part letters change over time, so this will require pairing older fields from .xlsx files to their correct tables. The original files (CSV and Excel) are not split by form part.
• Each report corresponds to 1 state and one commodity group.

Part A: Operator Information

• Essentially just the operator ID, name, address, and info on the state and commodity pertinent to the report.

core_phmsagas__yearly_distribution_main_and_services

Part B: System Description

1. Summary stats on miles and services by material.
2. Miles of main by material and size of pipe.
3. Miles of services by material and size of pipe.
4. Miles of main and services by decade of installation.
   • Each part is a table that has a bunch of different categoricals (e.g. pipe size) that will want to get tidied, changing the PK of the table.

core_phmsa__yearly_distribution_leaks_and_repairs

Part C: Leaks and Repairs

• Table is organized by cause of leak and type of pipe. There are two additional fields at the bottom for "known leaks scheduled for repairs" and "leaks involving mechanical joint failure"

Part D-I

• Each field occurs once per report, these should probably be kept in one table with Part A.
• Part D, Excavation Damage: 6 fields of summary stats, should probably be collapsed into another table.
• Part E, EFV and Service Valve Data: 4 fields of summary stats, should probably be collapsed into another table.
• Part F: Total number of leaks on federal lands scheduled for repair or repaired.
• Part G: %age of unaccounted gas.
• Part H: Additional Info: a Notes field, discusses corrections, changes in calculations, and other ambiguities.
• Part I: Preparer's info: Email, initial or supplemental report, contact info for preparer.

### Adapt infrastructure to handle PHMSA partitions
- [ ] https://github.com/catalyst-cooperative/pudl/issues/2850
- [ ] https://github.com/catalyst-cooperative/pudl-archiver/issues/199
- [ ] https://github.com/catalyst-cooperative/pudl-archiver/pull/252
- [ ] https://github.com/catalyst-cooperative/pudl-archiver/pull/253

### Extraction into raw assets
- [ ] https://github.com/catalyst-cooperative/pudl/pull/2932
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3241
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3243
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3248
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3253
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3263
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3259
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3278
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3275
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3268
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3261
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3265
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3277
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3290

After raw assets are extracted, we will have to define a core set of transformations for the PHMSA data. For each table, this could include: defining all columns with a datatype, transforming columns into categoricals to reduce the width of the table, defining primary keys, standardizing NAs.

Known cleaning steps:

• Convert 2-digit report_year into 4-digit years (pre 2000).
• Standardize report_state to use either shorthand or full state name
• Ideally, adapt existing wide_to_tidy infrastructure to drastically collapse tables using categoricals (e.g., a column for "location" that includes onshore, offshore, total rather than 3x the columns).
• Standardize the multiple treatments of time noted in #3277 (filing date, data date, revision date)
• Handle different aggregations of reporting over time for each form section (e.g., 1 form per state, one form per system)
• Where granularity increases over time (e.g. onshore becomes onshore types A, B, C), aggregate these increasingly disaggregated columns back to have comparable totals over time.
• Deal with extremely varying telephone formats
• Standardize use of office vs. HQ addresses over time, and do general address cleaning

### First round of cleaning into core assets
- [ ] https://github.com/catalyst-cooperative/pudl/issues/3770
- [ ] Transmission A-D
- [ ] Transmission F-G
- [ ] Transmission H
- [ ] Transmission I
- [ ] Transmission J
- [ ] Transmission K
- [ ] Transmission L
- [ ] Transmission M
- [ ] Transmission N-O
- [ ] Transmission P
- [ ] Transmission Q
- [ ] Transmission R
- [ ] Transmission S
- [ ] Transmission T

[e-belfer]

2017-2021 Excel and CSV comparison:

• Column names are identical.
• Excel files have day month year and time of report date, filing date and last data update columns, CSVs only have day month and year.
• Minor rounding differences between values (e.g. one row out of 1300).
• Otherwise no major differences found between data sources, good!