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.

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 that will want to get reorganized, probably changing the PK of the table. Organization should probably mirror what we do for transmission data since the breakouts are similar in nature.

Part C: core_phmsa__yearly_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

Beta Give feedback

1. Add keywords to Zenodo archive to fix dataset validation issue #2850
   datastore zenodo +2
   
2. Add multi-year partitions to PHMSA archiver pudl-archiver#199
   phmsagas +1
   
3. Change PHMSA partitions to use year instead of years pudl-archiver#252
   metadata phmsagas +2
   
4. When PHMSA data has _present in file name, make partitions actually reflect years of data available correctly. pudl-archiver#253
   metadata phmsagas +2
   

Extraction into raw assets

Beta Give feedback

1. Extract raw PHMSA distribution and start of transmission data (Table A-D, H, I) #2932
   new-data phmsa +2
   
2. Extract raw PHMSA transmission data for Tables F-G #3241
   new-data phmsa +2
   
3. Map columns for transmission J #3243
   new-data phmsa +2
   
4. Map columns for transmission K #3248
   new-data phmsa +2
   
5. Map columns for PHMSA transmission part L #3253
   new-data phmsa +2
   
6. Map columns for PHMSA transmission part M #3263
   new-data phmsa +2
   
7. Map columns for PHMSA transmission part N-O #3259
   new-data phmsa +2
   
8. Map columns for PHMSA transmission part P #3278
   new-data phmsa +2
   
9. Map columns for PHMSA transmission part Q #3275
   new-data phmsa +2
   
10. Map columns for PHMSA transmission part R #3268
    new-data phmsa +2
    
11. Map columns for PHMSA transmission part S #3261
    new-data phmsa +2
    
12. Map columns for PHMSA transmission part T #3265
    new-data phmsa +2
    
13. Clean up and standardize column names #3277
    new-data phmsa +2
    
14. Extract PHMSA distribution and transmission data from 1970-1989 #3290
    new-data phmsa +2

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 Clean up and standardize column names #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

Beta Give feedback

1. Distribution data
2. Transmission A-D
3. Transmission F-G
4. Transmission H
5. Transmission I
6. Transmission J
7. Transmission K
8. Transmission L
9. Transmission M
10. Transmission N-O
11. Transmission P
12. Transmission Q
13. Transmission R
14. Transmission S
15. 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!