<jupyter_start><jupyter_text>Introduction This notebook contains the code to transform the Excel files available through ODNR () to a dataset in the same format as Ted's 2016 Production spreadsheet (from a shapefile).The data are pretty much copied straight over into separate columns for each ODNR file, but aligned by API number.See the ODNR Production notebook for an alternate assembly of the data.  Steps to update data set - Make a copy of this jupyter notebook and make changes to the copy (and keep original separate).- Go to (http://oilandgas.ohiodnr.gov/shale) and download the Utica and Marcellus Cumulative Permitting Activity files (the xls versions). Save them in the sources directory.  In the imports code below, change the names of the two files to reflect the downloads. - Go to the (http://oilandgas.ohiodnr.gov/production) and download all the QUARTERLY data that has been added since the last update.  (Look in sources/odnr_production_files directory on your computer to see what you already have.)  The new files have to be moved to that directory and renamed in the format "YYYY_Q.xls" just like the other files.- Go to the "Fetch the Data; concat" section below and add the new filenames to the 'fns' list.- Finally, in the Imports section just below, change the name of 'prodcsv' to reflect the last quarter in the new data.- That should be it: run this notebook and the output file will show up in the output directory.  Imports<jupyter_code>#
import matplotlib.pyplot as plt
%matplotlib inline
import numpy as np
import pandas as pd
import os
import seaborn as sns

# set datadir to the directory that holds the downloaded files
datadir = './sources/'
outdir = './out/'
indir = datadir+'odnr_production_files/' # subdirectory for the production files

### ODNR cumulative data files.
utica = datadir+'Utica_110219.xlsx'
marcellus = datadir+'Marcellus_110219.xlsx'

prodcsv = outdir+'production_to_2019Q2.csv'
temp = outdir+'temp.csv'
temp2 = outdir+'temp2.csv'

df_allfn = outdir+'df_all.pkl'<jupyter_output><empty_output><jupyter_text>Fetch the baseline/permit dataset The baseline file is used for the metadata such as lat/lon, owner, etc.  It comes from two files on the ODNR page:<jupyter_code>####  Read in the ODNR excel sheets

uti = pd.read_excel(utica,header=4,dtype={'API #':'category'})
mar = pd.read_excel(marcellus,header=4,dtype={'API #':'category'})
baseline = pd.concat([uti,mar])
baseline = baseline.dropna()
# rename a few things
baseline.columns = ['PermitDate','County','Township','API','Status','Operator','WellName_Number','SurfLat','SurfLon','EndLat','EndLon']

# print the number of APIs
print(f'Total length = {len(baseline)}')
print(f'Unique API   = {len(baseline["API"].unique())}')
baseline[baseline.API.duplicated()]<jupyter_output>Total length = 3275
Unique API   = 3272<jupyter_text>Filling in the gaps of the metadata Unfortunately, currently (11/2019) the ODNR permit dataset does not include all wells that are listed in the production dataset.  Therefore, we will pull in metadata directly from the production set (even though it is not as complete) and add it to the baseline set for those wells missing from the baseline.First, we will pull in all the production data and concat to a single dataframe.<jupyter_code>#  The files from 2011 and 2012 are a different format and order so we need different sets of column names.

colnames1 = ['APINumber','Yr','Q','Operator','County','Township','WellName','WellNum','Oil','Gas','Brine','Days']
colnames2 = ['Yr','Q','Operator','County','APINumber','Township','WellName','Oil','Gas','Brine','Days','FirstDate']

dlst = []
filelist = os.listdir(indir)

for f in filelist:
    d = pd.read_excel(indir+f)
    if f[:4] in ['2011','2012']:
        # Need to treat these years differently because they are annual data and slightly diff format
        d.columns = colnames2
        d['Year'] = f[:4]
        d['Quarter'] = '1' 
        d['period'] = f[:4]+'Q1'
    else:
        # The quarterly data doesn't need as much...
        d.columns = colnames1
        d['Year'] = f[:4]
        d['Quarter'] = f[5]
        d['period'] = f[:6].replace('_','Q')
    d = d.filter(['APINumber','Year','Quarter','Operator','County','Township','Wellname','Oil','Gas','Brine','Days','period'])
    
    d.APINumber = d.APINumber.fillna(0).astype('int64').astype(str)
    dlst.append(d)
df_all = pd.concat(dlst,sort=True,ignore_index=True)
df_all.to_pickle(df_allfn)
df_all.head(20)
df_all = pd.read_pickle(df_allfn)

API = pd.DataFrame(df_all['APINumber'].unique(),columns=['API'])
mg = pd.merge(baseline,API,left_on='API',right_on='API',how='outer',indicator=True)

# the following df has the API of the not-baseline-included wells.
ext_API = pd.DataFrame(mg[mg['_merge']=='right_only']).filter(['API'],axis=1)

#now let's get the metadata from df_all
df_tmp = df_all.groupby('APINumber',as_index=False)['Operator','County','Township'].last()
meta = pd.merge(df_tmp,ext_API,left_on='APINumber',right_on='API',how='inner')
meta = meta.filter(['API','Operator','County','Township']) #.dropna()
meta
#Fill in with NaN 
bl = pd.concat([baseline,meta],sort=False,ignore_index=True)
bl.fillna(np.NaN,inplace=True)
bl.tail(10)<jupyter_output><empty_output><jupyter_text>Fetch the data; concat Because the data in the files from ODNR is not exactly consistent, We must name the files by hand and use the name of those files as info for the resulting dataframe.  Specifically, name files downloaded : 'YEAR_Q.extension'.  Keep the same extension as the downloaded file (xls or xslx). Add the name to the "fns" variable below.  This program will use the Year and Q info to label the records in the dataframe.  Note that files for years 2011 and 2012 contain annual data only.<jupyter_code>blcop = bl.copy()
fns = ['2011.xls','2012.xls',
      '2013_1.xlsx','2013_2.xlsx','2013_3.xlsx','2013_4.xlsx',
      '2014_1.xls','2014_2.xls','2014_3.xls','2014_4.xls',
      '2015_1.xls','2015_2.xls','2015_3.xls','2015_4.xls',
      '2016_1.xls','2016_2.xls','2016_3.xls','2016_4.xls',
      '2017_1.xls','2017_2.xls','2017_3.xls','2017_4.xls',
      '2018_1.xls','2018_2.xls','2018_3.xls','2018_4.xls',
      '2019_1.xls','2019_2.xls']

#  The files from 2011 and 2012 are a different format and order so we need different sets of column names.

colnames1 = ['APINumber','Yr','Q','Owner','County','Twp','WellName','WellNum','Oil','Gas','Brine','Days']
colnames2 = ['Yr','Q','Owner','County','APINumber','Twp','WellName-WellNum','Oil','Gas','Brine','Days','FirstDate']

def update_col(indx,cols):
    # add the index to the name of each column (except the first (API))
    new = [cols[0]]
    for c in cols[1:]: # skip the first value, it is the API number
        new.append(c+str(indx))
    return new


for i,f in enumerate(fns):
    d = pd.read_excel(indir+f) #,dtype={'Permit Number':'category'})

    if f[:4] in ['2011','2012']:  # 2011 and 2012 are odd and have to be treated separately
        d.columns = colnames2
        d['Year'] = f[:4]
        kcols = ['APINumber','Year','Oil','Gas','Brine','Days']
        d = d.filter(kcols)
        if f[:4]=='2012':  #need to add '1'
            kcols = update_col(i,kcols)
            d.columns = kcols
        d.APINumber = d.APINumber.fillna(0).astype('int64').astype(str)

    else:  # all the other files
        d.columns = colnames1
        d['Year'] = f[:4]
        d['Quarter'] = f[5]
        kcols = ['APINumber','Year','Quarter','Oil','Gas','Brine','Days']
        d.APINumber = d.APINumber.fillna(0).astype('int64').astype(str)
        # some quarters have more than one record for an API - looks like the owner channges hands
        # mid-quarter.  Summing the duplicates (incl # days) solves the issue.
        d = d.groupby(['APINumber','Year','Quarter'],as_index=False)['Oil','Gas','Brine','Days'].sum()
        d = d.filter(kcols)
        kcols = update_col(i,kcols)
        d.columns = kcols

    blcop = blcop.merge(d,how='outer',left_on='API',right_on='APINumber')
    blcop = blcop.drop('APINumber',axis=1)<jupyter_output><empty_output><jupyter_text>Summary columns<jupyter_code>summ = df_all.groupby('APINumber',as_index=False)['Oil','Gas','Brine','Days'].sum()
summ.columns = ['APINumber','TotalOil','TotalGas','TotalBrine','TotalDays']
blcop = blcop.merge(summ,how='inner',left_on='API',right_on='APINumber')
blcop = blcop.drop('APINumber',axis=1)

blcop['OilPerDay'] = blcop.TotalOil/blcop.TotalDays
blcop['GasPerDay'] = blcop.TotalGas/blcop.TotalDays
blcop['BrinePerDay'] = blcop.TotalBrine/blcop.TotalDays

#print(blcop[blcop.API.duplicated()])
blcop = blcop.drop_duplicates()

# gpk = pd.read_csv(PeakStats)
# blcop = blcop.merge(gpk,left_index=True,right_index=True,how='inner')
# blcop = blcop.drop('Unnamed: 0',axis=1)
blcop.to_csv(prodcsv)
blcop.head(20)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14584747680470694, 'fraction_numerical': 0.030889341479972843, 'mean_word_length': 4.85364238410596, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '202048', 'license': 'CC0-1.0', 'n_tokens_mistral': 3201, 'n_tokens_neox': 2952, 'n_words': 822, 'path': 'OH Production to FT format-Version Fall 2019.ipynb', 'repo_name': 'gwallison/ohio_production_to_FT_format', 'size': '8838'}