{'fraction_non_alphanumeric': 0.04312993854258121, 'fraction_numerical': 0.007206613988879134, 'mean_word_length': 4.85249411260972, 'pattern_counts': {'":': 0, '<': 0, '<?xml version=': 0, '>': 0, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
Method for Compiling Temporally and Spatially Aggregated Data on Hydraulic Fracturing—Treatments and Wells Method for Compiling Temporally and Spatially Aggregated Data on Hydraulic Fracturing—Treatments and Wells

This report provides a step-by-step method for compiling hydraulic fracturing data in the United States (U.S.) from the IHS Markit, 2019, U.S. Well History and Production Relational Database (IHS Markit, 2019). Data on hydraulically fractured wells include their location (geologic province, State, county), well type (oil or gas), orientation (directional, horizontal, or vertical), spud date, completion date and the hydraulic fracturing treatments, treatment fluids types, treatment fluid volumes, additive types, agent types (“proppants”), and proppant amounts injected. This method also describes how to associate each unique well with the hydraulic fracturing treatments to provide an indication of the total amount of all treatment fluids injected into a well for hydraulic fracturing and the volume of each individual treatment fluid type injected.


Introduction
Hydraulic fracturing is a well stimulation technique applied to oil and gas wells to enhance the production of oil and gas from low permeability reservoirs (Coleman, 2009;Montgomery and Smith, 2010). The technique involves injecting treatment fluid(s) mixed with a propping agent or "proppant" (such as sand) into a well bore to fracture the host rock and create pathways for the oil and gas to flow to the well bore (Veatch, 1983a, b;Ground Water Protection Council and ALL Consulting, 2009;Montgomery and Smith, 2010). Treatment fluids contain a base fluid (such as water, gel, or oil) that is amended with various chemicals or additives, depending on the properties of the target hydrocarbon, reservoir, and the desired fracture geometry (Economides and Nolte, 2000;Elbel and Britt, 2000;Gulbis and Hodge, 2000;U.S. Environmental Protection Agency, 2004;Holditch, 2007). Hydraulic fracturing has historically been applied to wells of a vertical, directional, or horizontal orientation (Ground Water Protection Council and ALL Consulting, 2009). Data regarding these aspects of hydraulically fractured wells and their treatments are found in the IHS Markit, 2019, U.S. Well History and Production Relational Database (IHS Markit, 2019). Several processing steps are needed to both extract the pertinent data and link the hydraulically fracturing wells to their associated hydraulic fracturing treatments.

Purpose
The purpose of this report is to provide a step-by-step method for compiling data from the IHS Markit, 2019, U.S. Well History and Production Relational Database (IHS Markit, 2019) in a consistent and repeatable manner to achieve data that describe the quantifiable aspects of hydraulic fracturing. The well data are compiled according to their location (geologic province, State, county), well type (oil or gas) and orientation (directional, horizontal, or vertical), spud date, and completion date. The wells are also associated with hydraulic fracturing treatments, treatment fluids type, treatment fluid volumes, additive types, agent types ("proppants"), and proppant amounts used for hydraulic fracturing as defined by IHS Markit (2019). Original data are proprietary and are not provided here but are available through IHS Markit (2019).

Data on Hydraulically Fractured Oil and Gas Wells
Data are from the IHS Markit (IHS), U.S. Well History and Production Relational Database (see http://www.ihsenergy.com) (IHS Markit, 2019). IHS currently offers two versions of this database, an online version (Enerdeq) and a local Oracle IHS database (PIDM) based on a PIDM data model (referred to as "PIDM" in this report) (IHS Markit, 2019). This report is written for the local Oracle version (based on PIDM Data Model 2.5.3 released February 2012) because it matches standards defined for petroleum industry databases (IHS Markit, 2019). Tabular data include the following: • Tables WELL and WELL_TREATMENT   • Code tables R_FINAL_STATUS, R_ADDITIVE_  TYPE, COUNTY, GEOLOGIC_PROV, PROPPANT_  TYPE, PROVINCE_STATE, TREATMENT_FLUID

User Requirements
The user of the method described herein requires access to the IHS Markit (IHS), U.S. Well History and Production Relational Database (IHS Markit, 2019). The user also must have the ability to connect to an Oracle database directly using Java Database Connectivity (JDBC). The use of this method requires an understanding of programming, and specifically, the ability to compile and run a Java program. The user should also understand relational databases, most notably, the relationships between tables and the structure of the Oracle IHS database (PIDM) (IHS Markit, 2019). Finally, the user should be proficient in Microsoft (MS) Office products such as Access, Excel, and Word.

Naming Conventions
Terms from the PIDM are set in all capital letters; PIDM table names are capitalized and underlined (for example, WELL_TREATMENT); schemas are capitalized, italicized, and underlined (for example, PI_CODES); IHS field names (column headings) are capitalized (for example, TRTM_UNIT); and table entries (values) are capitalized and italicized (for example, GAL) (table 1). Filenames created using this method are in quotation marks (for example, "fracworksheet.xlsx"). MS Access filenames are in bold and in quotes (for example, "master tables.accdb"), table names within MS Access database files are bold and underlined (for example, Master Treatment), query names within MS Access are italicized, bold and underlined (for example, Treatments_w_codes), field names within MS Access tables are all capitalized and bold (for example, UWI), and table entries within MS tables are all capitalized, bold, and italicized (for example, GAS WELL). Also, when creating MS Access files, we use "[vintage year of PIDM]" and "[start date of compilation]" to indicate the year of the PIDM release and date that the data compilation was started, respectively. Since this process can be used for a single year or groups of multiple years, we use "[start year of interest -end year of interest]" when naming tables or queries and use "year(s) of interest" when referring to all the different groupings of years (for example, "for each year(s) of interest, create a new table called Table_Example-[start year of interest-end year of interest]"). This is only a concern from step 5 onward. It should also be noted that users must modify many of the "checks" in the "fracworksheet.xlsx" file if this method is used to compile multiple year groupings.

Process Steps
Step 1: Convert treatment amounts Use the Java program, "ConvertTreatments" (appendix 1) to convert the various treatment units (TRTM_UNIT) including GAL (gallon), QT (quart), BBL (barrel), CF (cubic feet) and MCF (thousand cubic feet) into CUM (cubic meters) as well as to convert proppant amounts (AGENT_UNIT) from TON (tons) into LB (pounds). Convert units for all records within the IHS WELL_TREATMENT table that have a value for either the treatment fluid amount (TRTM_AMOUNT) or the proppant amount (AGENT_AMOUNT). Only convert units that have a conversion factor in the PIDM (see appendix 2 for a list of IHS unit conversion factors) (IHS Markit, 2019); all other units will be ignored.
Step 2: Create files for data storage .accdb" will be referred to as "master tables.accdb".
• The output data could be stored in either a single MS Access file or in multiple MS Access files, depending on size limitations. If multiple files are used, then additional filenames should be consecutively numbered and appended with the "file number of total number of files" (for example, "HydroFRAC-[vintage year of PIDM]-MasterTables-[start date of compilation]-1 of 2.accdb").
• Open a MS Excel file called "HydroFRAC-Template-Worksheet.xlsx" and save it as "HydroFRAC-[vintage year of PIDM]-Worksheet-[start date of compilation].xlsx". Copy the template in appendix 2 into this Excel file. This file will be used to check the counts to assure that data have not been lost as specified throughout this report.
• Create a text file called "README.txt".
• Use this file to record information about the various tables and files.
• List information such as the data source, date of creation, name of person who created the file, methods/ steps used to compile the data, list of field names, and what the data represent including the year(s) of compilation and any other definitions or explanations needed to document the origin, processing steps, methods, and quality.
Step 3: Use Microsoft Access to store data from "converted treatments" database and from the PIDM • In the "fracworksheet.xlsx" file, record the number of records listed in the IHS WELL_TREATMENT and the usgs_treatment tables.
• In the "fracworksheet.xlsx" file, record the number of records by treatment unit (TRTM_UNIT) and the number of records by proppant unit (AGENT_UNIT) (only of the FRAC or REFRAC treatment types) found in the original IHS WELL_TREATMENT table.
• Compare the counts of converted treatment fluid units (TRTM_UNIT) and converted proppant units (AGENT_UNIT). In the "fracworksheet.xlsx" file, explain discrepancies (if any) between original IHS WELL_TREATMENT and usgs_treatment tables. • Records are converted if either the treatment amount (TRTM_AMOUNT) or the proppant amount (AGENT_AMOUNT) has a value.
• Note that the counts for converted records from the usgs_treatment table will not likely match counts for TRTM_UNIT and AGENT UNIT from the IHS WELL_TREATMENT because a record could have a proppant amount and not a treatment fluid amount or vice versa.
Step 4: Create a master treatment table • Record the count of FRAC and REFRAC records from the IHS WELL_TREATMENT table and the usgs_treatment table in the "fracworksheet.xlsx" file.
• It is possible that the count for FRAC and REFRAC records for IHS WELL_TREATMENT and usgs_ treatment tables could be the same but highly unlikely since not all units are converted from WELL_TREATMENT. Furthermore, there are cases where a record can have a proppant amount (AGENT_AMOUNT) but not a treatment amount (TRTM_AMOUNT). It is more likely that the record count for usgs_treatment table will be less than the record count for IHS WELL_TREATMENT. There should never be more FRAC and REFRAC records in the usgs_treatment table than in the  IHS WELL_TREATMENT table because the  usgs_treatment table is created from the IHS  WELL_TREATMENT table. • Create a query called Treatments_w_codes in the "converted treatments.accdb" to link code information from the IHS tables in the PI_CODES schema (R_ADDITIVE_TYPE, R_PROPPANT_TYPE, R_TREATMENT_FLUID, and R_TREATMENT_ TYPE) to the usgs_treatment table to convert the IHS codes to full text. Note that PI_CODES is a schema that holds all the tables that convert "codes" to their actual value, therefore, the PI_CODES table converts "FRAC" to "Fracturing".
• Make sure the joined IHS PI_CODES tables are specified to retain all converted records and only retain IHS PI_CODES records, when available.
• In the "fracworksheet.xlsx" file, record the number of records in the Treatments_w_codes query after linking the IHS PI_CODES.
• Verify that the number of Treatments_w_codes query records match the number of records in the usgs_ treatment table. The number of records in the query should be the exact same as the number in the usgs_ treatment table. This provides the following: • Verifies that records were not inadvertently removed because they did not have a code; • Verifies that codes are connected correctly and that the code name has been added (for example, FRAC [TRTM_TYPE] now has an additional column [TRTM_TYPE_NAME] in this case, "Fracturing"); • Checks that if a code is empty (for example, the ADDITIVE_TYPE is empty) then the entire record is not removed when records are joined.
• If the number of records in the Treatments_w_codes query agrees with the number of records in the usgs_treatment • Record explanations and information regarding the Master Treatment table data in the README text file.
• When linking codes, keep all original records (UWIs from the Unique UWI query) and add only the IHS PI_CODES table field names that match.
• Note that because of a technical issue with MS Access, the user must change the identifier for PI_ SURFACE_LAT to LAT and PI_SURFACE_LONG to LON.
• In the "fracworksheet.xlsx" file, record the number of records in the Unique_UWIs_w_codes query after linking the codes. Make sure that the number of unique UWIs in the Unique_UWIs_w_codes query matches the count of unique UWIs from the Unique UWIs query.
• If the count of unique UWIs in the Unique UWIs query match the count from the Unique_UWIs_w_ codes query, use the Unique_UWIs_w_codes query to create a new query called Master_Well_Table-All.
• If the counts do not match, make sure the Unique UWIs query is correct following step 4 and repeat step 5a.
Step 5b: Compile FRAC/REFRAC onshore wells and check • Using the Master_Well_Table-All query create a temporary query to obtain the count of wells by State using SQL's (Structured Query Language) "Group By" and "Count" functions.
• Since the Master_Well_Table-All query contains all the unique UWIs that had a FRAC or REFRAC treatment, this step should provide a count of the FRAC or REFRAC wells by State.
• In the "fracworksheet.xlsx" file, record the count of UWIs by State.
• Create another query called Master_Well_Table-Land [based on the Master_Well_Table-All query]. Remove all offshore wells by excluding entries with a PROVINCE_STATE value greater than 50.
• The process uses IHS PROVINCE_STATE values to determine if a well is offshore or onshore. We assume onshore wells have a PROVINCE_STATE value that is equal to one of the 50 States in the United States and those wells without value or with a value that is not equal to one of the 50 States in the United States are assumed to be located offshore presumably in waters outside of the continental land mass.
• Create another temporary query based on the Master_Well_Table-All query using the "Group By" and "Count" functions to get the counts by State: • "Group By" PROVINCE_STATE and "Count" UWI.
• The difference between the count by State from the Master_Well_Table-All query and the count by State from Master_Well_Table-Land query is equal to the number of offshore wells.
• In "fracworksheet.xlsx" file, record the total number of wells listed in the Master_Well_Table-Land query.
Check that the number of wells matches the counts from Master_Well_Table-All query by State (not including the offshore State codes).
• If counts do not match, repeat step 5b.
Step 5c: Compile FRAC/REFRAC onshore wells completed during the years of interest and check • Using the Master_Well_Table-Land query, create a temporary query to get the count of wells by State using the "Group By" and "Count" functions.
• Since the Master_Well_Table-Land query contains all the onshore unique UWIs that had a FRAC or REFRAC treatment, this step should provide a count of the FRAC or REFRAC wells by completion year.
• In the "fracworksheet.xlsx" file, record the count of UWIs by COMP_YEAR listed in the temporary Master_Well_Table-Land query. Step 5d: Compile FRAC/REFRAC oil and gas onshore wells completed in the years of interest and check.
• Create a new query/queries for each Master_Well_ • If counts do not match, repeat step 5d.
Step 5e: Compile FRAC/REFRAC, horizonal, vertical, directional, oil and gas onshore wells completed for year(s) of interest. • These counts should match because one is a subset of the other. This is a check to verify that errors were not made when querying only the vertical, directional, and horizontal wells.

• Within each Master_Well_Table-Land-OilGas-[start year of interest-end year of interest
• If counts do not match, repeat step 5e.
Step 5f: Create table(s) of FRAC/REFRAC horizontal, directional, oil and gas onshore wells completed in the year(s) of interest.

• For each Master_Well_Table-Land-OilGas-DHV-[start year of interest-end year of interest]
query, record the number of records in the "fracworksheet.xlsx" file. Table-[start year of interest-end year of interest].

• Use each Master_Well_Table-Land-OilGas-DHV-[start year of interest-end year of interest] query/ queries to create a table(s) called Master Well
• After creating each Master Well • If counts do not match, repeat step 5f.
• Update the README text file with information about the various tables and files, such as data source, date of creation, name of the person who created the file, methods/steps used to compile the data, list of field names, and what the data represent including years of interest and any other definitions or explanations needed to document the origin, processing steps, methods, and (or) quality.
Step 6: Create master table(s) • Link the well information to the treatment information to create a "Master Table". • Check that data were not lost in the compilation.

• Create a query called Master_TableQ-[start year of interest-end year of interest] based on each Master Well
• In the "fracworksheet.xls" file, record the counts of the UWIs listed in the Master Well • If count does not match, repeat step 6.
• If the count of unique wells (UWI) from the • If records counts do not match, repeat step 6.
• Update the README text file with information about the various tables and files, such as data source, date of creation, name of person who created the file, methods/steps used to compile the data, list of field names, and what the data represent including years of interest and any other definitions or explanations needed to document the origin, processing steps, methods, and (or) quality.

Step 7: Check master table(s) counts
• Check that no records were lost between the query and table.
• Open each Master Table-[start year of interestend year of interest] table and go to the last record and record the total number of records in the "fracworksheet.xlsx" file.
• Verify that the count of each Master Table-[

start year of interest-end year of interest] matches the count from the corresponding Master_TableQ-[start year of interest-end year of interest] query.
If this is not the case, then go back to last known correct check.
• Verify and record in "fracworksheet.xlsx" file that each Master • If the record counts do not match, repeat steps 4 to 7.
Step 8: Start the process to compile treatment totals

• For each Master table-[start year of interest-end year of interest], create a temporary query that counts the treatment fluid amount units (TRTM_AMT_ UNIT).
Verify that there is only unit of CUM (cubic meters). Record the counts of records in the "fracworksheet.xlsx" file.
• As mentioned in step 3, if there are TRTM_AMT_ UNIT other than CUM, then a mistake has been made in the first step. The user must start the process from step 1 because the treatment units were not converted properly.
• Some TRTM_AMT_UNIT entries could be blank in cases where the record had a proppant amount but not a treatment amount.
• For each Master • Update the README file with information on data source, date of creation, name of person who created the file, methods/steps used to compile the data, definition of columns and rows, and what the data represent including years of compilation and any other definitions or explanations needed to document the origin, processing steps, methods, and (or) any other notes that would reflect quality.
Step 9: Create master well treatment totals table(s) • Use the "Group By" function within the query, group by well information such that a single record is retrieved for every well. Use the "Sum" function to calculate the sum of the treatment amounts (TRTM_ AMTs) resulting in a single treatment amount per well.

• Create a new query called Unique wells w TRTM_ AMT-[start year of interest-end year of interest] based on the Master
• If the name of the column heading is not changed, the resulting query will name the column heading "SumOfTRTM_AMOUNT". This might cause confusion because the next query also generates a "SumOfTRTM_AMOUNT" if the name is not changed as well.
• It is recommended that the column be renamed "TOTAL_TRTM_AMOUNT".
Record the record count in the "fracworksheet.xlsx" file.

• If the counts from the query/queries Q-Master Wells Treatment Totals-[start year of interest-end year of interest] match the totals from the Unique Wells w TRTM_AMT-[start year of interest-end year of interest] query, then using query Q-Master Well Treatment Totals-[start year of interest-end year of interest] create a new table called Master Well Treatment Totals-[start year of interest-end year of interest]
for each year(s) of interest and record the record count in the "fracworksheet.xlsx" file.
• If the record counts do not match, then repeat step 9.
• Update the README text file to document the data source, date of creation, name of person who created the file, methods/steps used to compile the data, list of field names, and what the data represent including years of compilation and any other definitions or explanations needed to document the origin, processing steps, methods, and factors that could affect the data quality.
Step 10: Create a master well treatment total by fluid table(s) • Include the treatment information (TRTM_FLUID_ TYPE, TRTM_FLUID_TYPE_NAME, and TRTM_AMT).

• In the Q-Well Treatment Totals by Fluid-[start year of interest-end year of interest]
query, compute the total volume of each fluid type injected into each well by applying the "Group By" function to all well information, TRTM_FLUID_TYPE, and TRTM_FLUID_ TYPE_NAME, and apply the "Sum" function to the TRTM_AMT.
• If the name of the column heading is not changed, the resulting query will name the column heading "SumOfTRTM_AMOUNT". This might cause confusion because the previous query also generated a "SumOfTRTM_AMOUNT" if the name was not changed.
• It is recommended that this column be renamed "TOTAL_TRTM_FLUID_AMOUNT".

• Create a new query/queries called Unique Wells Q-Well Treatment Totals by Fluid-[start year of interest-end year of interest] based on the Q-Well Treatment Totals by Fluid-[start year of interest-end
year of interest] query and use the "Group By" function for only the UWI.

• Record the counts for each Q-Well Treatment Totals by Fluid-[start year of interest-end year of interest] and Unique Wells Q-Well Treatment Totals by Fluid-[start year of interest-end year of interest] and check that the Unique Well Q-Well Treatment Totals by Fluid-[start year of interest-end year of interest] count matches the Unique Wells w TRTM_AMT-[start year of interestend year of interest] count from step 9.
• If counts do not match, repeat step 10.

• If counts match, create a new table(s) called Master Well Treatment Totals by Fluid-[start year of interest-end year of interest]
for each year(s) of interest.

• Verify that the counts of records in the Q-Well Treatment Totals by Fluid-[start year of interest-end year of interest] query match the counts of records in the Master Well Treatment Total by Fluid-[start year of interest-end year of interest] table.
• If the counts do not match, then repeat step 10.
Step 11: Remove proprietary data The proprietary information should be removed prior to public release of the data, in accordance with IHS Markit (IHS Markit, 2019) agreements.
• Remove all proprietary information considered proprietary by IHS Markit (IHS Markit, 2019) from all the MS databases (.accdb) and tables as well as from any text files created using this report. For example, proprietary information includes, but is not limited to the following: well identity information such as the UWI (or API), latitude (LAT), and longitude (LON) of the wells.
• Note that this step only applies to the tables created by the user using this method. The original IHS tables are static and should retain the well identity information.
• Final data products derived from this method should be reviewed by IHS Markit prior to release in accordance with IHS Markit agreements (IHS Markit, 2019).

Master Well Table-[start year of interest-end year of interest]
• Method/step used to compile data: Step 5.
• Data represented: Data represents all horizontal, directional, or vertical oil and gas wells that were hydraulically fractured or refractured and completed during the years of interest.

Master Table-[start year of interest-end year of interest]
• Methods/steps used to compile data: Steps 6 and 7.
• Data represented: All hydraulic fracturing or refracturing treatments applied to directional, horizontal, and vertical oil and gas wells completed during the year(s) of interest and associated with a State code. Each row represents a single treatment such that one well could have multiple treatments (more than one row per well).

Master Table w TRTM_AMT-[start year of interest-end year of interest]
• Methods/steps used to compile data: Steps 8 and 9.
• Data represented: Same as Master Table except this  table contains only the records that report a treatment amount for a given treatment type. Each row represents a single treatment and associated treatment amount such that one well could have multiple treatments (more than one row per well).

Master Well Treatment Totals-[start year of interest-end year of interest]
• Methods/steps used to compile data: Steps 8 and 9.
• Data represented: Same as Master Table w TRTM_ AMT except these records contain only a single sum of all treatment amounts applied to a given well, regardless of treatment fluid type. Each row represents a single total treatment amount such that one well could only have one entry (one row per well).

Master Well Treatment Total by Fluid-[start year of interest-end year of interest]
• Data source: IHS PIDM (internal Oracle database) (IHS Markit, 2019).
• Methods/steps used to compile data: Steps 8 and 10.
• Data represented: Same as Master Table w TRTM_ AMT except each record represents the sum of all the treatment amounts of a given treatment type applied to a given well such that one well could have multiple entries (more than one row per well).

Appendix 4. The "fracworksheet.xlsx" File
The following series of tables are used to document the record counts at various steps throughout the data compilation process. Step 3 count of treatment records.

Count
Count of records in the IHS WELL_TREATMENT  Step 3 count of TRTM_UNIT records. Step 3 count of AGENT_UNIT records. Step 4 FRAC and REFRAC treatment record counts.

IHS
Step 4 Record Count of IHS -WELL_TREATMENT (FRAC or REFRAC): Step 5a unique UWI and Master Well Table-All record counts.

Count by HOLE_DIRECTION -Master_Well_Table-Land-OilGas-[start year of interest-end year of interest]
HOLE_DIR_NAME VERTICAL HORIZONTAL DIRECTIONAL PINNATE Total Vertical, Horizontal and Directional Oil and Gas Wells only Table 4.10.
Step 5f record counts for land-based oil and gas horizontal, directional and vertical wells.

Record Count of Master_Well_Table-Land-OilGas-DHV-[start year of interest-end year of interest]:
:: Table:: Record Count of Master Well Table-[start year of interest-end year of interest]: Table 4.11.
Step 6 record counts for building Master Table-[start year of interest-end year of interest].
Step 6 Record Count of Master Well