Difference between revisions of "Pennsylvania Oil and Gas Data Sources"

Revision as of 08:20, 23 May 2015

May 2015 Production Update

Updating Production data. Extra column for WELL_API had to be added, using Well Permit #. Excel formula ="37-"&B2 was used to produce WELL_API values. Well Permit # renamed to WELL_API_COUNTY_ID. Extra columns WELL_COUNTRY set to "United States" and WELL_STATE_CODE set to "PA".

Needed to specify that lines were delimited by \n instead of auto to get around PHP "invalid comma count error." There was a typo in one cell for a sheet that had a "\" character, which produced another invalid column count error. Also renamed all columns in CSV to columns in SQL db as follows:

WELL_API,WELL_API_COUNTY_ID,PERIOD_ID,PRODUCTION_INDICATOR,WELL_STATUS,FARM_NAME,WELL_ID,SPUD_DATE,GAS_QUANTITY_MCF,GAS_PRODUCTION_DAYS,CONDENSATE_QUANTITY_BBL,CONDENSATE_PRODUCTION_DAYS,OIL_QUANTITY_BBL,OIL_PRODUCTION_DAYS,AVERAGED,OPERATOR_NAME,operator_id,WELL_COUNTY,WELL_MUNICIPALITY,LATITUDE_DECIMAL,LONGITUDE_DECIMAL,UNCONVENTIONAL,CONFIGURATION,HOME_USE,REPORTING_PERIOD,COMMENT_REASON,COMMENT_TEXT,WELL_COUNTRY,WELL_STATE_CODE

which needs to be entered on the import screen for PHPmyAdmin to upload correctly for column headers.

This has been more work than it should have been. There is a CSV module in python if we elect to go for full automation, so this could possibly be done automatically.

In addition to removing the \ character from CSVs, they must also be processed to remove the quotation character " to allow for proper importation. Older yearly CSV files (non-monthly) were split using a CSV split utility in order to meet MySQL's 2048KiB filesize limit

@@ Line 3: / Line 3: @@
 ==May 2015 Production Update==
+Updating Production data. Extra column for WELL_API had to be added, using Well Permit #. Excel formula ="37-"&B2 was used to produce WELL_API values. Well Permit # renamed to WELL_API_COUNTY_ID. Extra columns WELL_COUNTRY set to "United States" and WELL_STATE_CODE set to "PA".
-Data within google drive appears to be uploaded to PA for waste and Production, assuming the data we are interested in is located within the PADEP folder within PA.
+Needed to specify that lines were delimited by \n instead of auto to get around PHP "invalid comma count error." There was a typo in one cell for a sheet that had a "\" character, which produced another invalid column count error. Also renamed all columns in CSV to columns in SQL db as follows:
-However - I suspect that the data was not quite inserted correctly. It appears that the WELL_API_COUNTY_ID on both was not inserted (under the CSVs, they are called Well Permit #).
-I think we could correct this, although I don't think we necessarily need to.
-That said, there is still data on the PA DEP website that needs to be uploaded.
-Updating production information. Extra column for WELL_API had to be added, using Well Permit #. Excel formula ="37-"&B2 was used to produce WELL_API values. Well Permit # renamed to WELL_API_COUNTY_ID. Extra columns WELL_COUNTRY set to "United States" and WELL_STATE_CODE set to "PA".
-Needed to specify that lines were delimited by \n instead of auto to get around PHP "invalid comma count error". There was a typo in one cell for a sheet that had a "\" character, which produced another invalid column count error. Also renamed all columns in CSV to columns in SQL db as follows:
 WELL_API,WELL_API_COUNTY_ID,PERIOD_ID,PRODUCTION_INDICATOR,WELL_STATUS,FARM_NAME,WELL_ID,SPUD_DATE,GAS_QUANTITY_MCF,GAS_PRODUCTION_DAYS,CONDENSATE_QUANTITY_BBL,CONDENSATE_PRODUCTION_DAYS,OIL_QUANTITY_BBL,OIL_PRODUCTION_DAYS,AVERAGED,OPERATOR_NAME,operator_id,WELL_COUNTY,WELL_MUNICIPALITY,LATITUDE_DECIMAL,LONGITUDE_DECIMAL,UNCONVENTIONAL,CONFIGURATION,HOME_USE,REPORTING_PERIOD,COMMENT_REASON,COMMENT_TEXT,WELL_COUNTRY,WELL_STATE_CODE
@@ Line 24: / Line 13: @@
 This has been more work than it should have been. There is a CSV module in python if we elect to go for full automation, so this could possibly be done automatically.
+In addition to removing the \ character from CSVs, they must also be processed to remove the quotation character " to allow for proper importation. Older yearly CSV files (non-monthly) were split using a CSV split utility in order to meet MySQL's 2048KiB filesize limit
-In addition to removing the \ character from CSVs, the must also be processed to remove the quotation character " to allow for proper importation. Older yearly CSV files (non-monthly) were split using a CSV split utility in order to meet MySQL's 2048KiB filesize limit

Track changes made to this page