<jupyter_start><jupyter_text>Introduction version 3: moved to O&G\OH directory structure, so had to change directory pointers. This notebook contains the code to transform the Excel files available through ODNR () to a dataset of the format needed for planned analysis.Currently, ODNR produces quarterly files with production numbers of oil (bbl), gas (mcf) and brine (bbl). Data for 2011 and 2012 (the first years of the data) have only annual numbers.This notebook is version 2 and will create separate sets of wells that first start producing in the same time period (usually quarter, except 2011-12).Data produced using the earlier version ('ODNR Production data.ipynb') were used by <NAME> in his article for the FracTracker web site  . This version uses the same data, but formats it in a way for wider uses.   Imports and directory definitions<jupyter_code>import matplotlib.pyplot as plt
%matplotlib inline
import numpy as np
import pandas as pd
import os

   
# set datadir to the directory that holds the files on YOUR computer
datadir = './sources/'
outdir = './out/'
# this is the directory that contains the odnr excel files.  Note that the must be remaned to the format described 
#   in the next section
indir = datadir+'odnr_production_files/'

### ODNR cumulative permit files.
utica = datadir+'Utica_052519.xlsx'
marcellus = datadir+'Marcellus_052519.xlsx'


# for viewing of data at interim points 
tempfn = outdir+'temp.csv'
tempexcel = outdir+'temp.xlsx'

# output name suffix
outsuff = '_production_by_start_period.xlsx'<jupyter_output><empty_output><jupyter_text>A function we will need to save data in separate worksheets<jupyter_code># used to save the final data into several worksheets of the same excel file.
def save_xls(dict_df, xlsx_path):
    writer = pd.ExcelWriter(xlsx_path,engine='xlsxwriter')
    for key in dict_df:
        dict_df[key].to_excel(writer,sheet_name= f'{key}')
    writer.save()<jupyter_output><empty_output><jupyter_text>Get metadata from ODNR permit sheets<jupyter_code>####  Read in the ODNR excel sheets for the meta-data for each well.

uti = pd.read_excel(utica,header=4,dtype={'API #':'category'})
mar = pd.read_excel(marcellus,header=4,dtype={'API #':'category'})
permit = pd.concat([uti,mar])

# rename a few things
permit.columns = ['pdate','county','township','APINumber','Status','Operator',
                  'WellName_Number','SurfLat','SurfLon','EndLat','EndLon']

permit = permit.dropna(subset=['APINumber'])
#print(len(permit))
permit = permit.drop_duplicates(subset=['APINumber'],keep='last') # the permit file has a few duplicates, keep recent
print(f'Total number of unique APINumbers in permit file: {len(permit)}')

# keep only those fields that are of interest in final production sheets
permit = permit.filter(['APINumber','Operator','WellName_Number',
                        'SurfLat','SurfLon','EndLat','EndLon'])
permit.head()<jupyter_output>Total number of unique APINumbers in permit file: 3159<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 as : 'YEAR_Q.extension'.  Keep the same extension as the downloaded file (xls or xslx).  This program will use the Year and Q info to label the records in the 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','Owner','County','Twp','WellName','WellNum','Oil','Gas','Brine','Days']
colnames2 = ['Yr','Q','Owner','County','APINumber','Twp','WellName-WellNum','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 create four quarters out of the yearly data.
        d.columns = colnames2
        #d['period'] = pd.to_period(f)
        d['Year'] = f[:4]
        d['Quarter'] = 'X' # for these years, quarterly data is not given.  Flag with a 'X' quarter.
        d['period'] = f[:4]+'QX' # mark these as non-quarter years
    else:
        # The quarterly data...
        d.columns = colnames1
        d['Year'] = f[:4]
        d['Quarter'] = f[5]
        d['period'] = f[:6].replace('_','Q')

    # now convert all oil,gas and brine measures by #/days to adjust for variable 
    #   production days in each reporting period
    d['Gas'] = d.Gas/d.Days
    d['Oil'] = d.Oil/d.Days
    d['Brine'] = d.Brine/d.Days
    d = d.filter(['APINumber','Year','Quarter','Owner','Oil','Gas','Brine','Days','period'])
    
    d.APINumber = d.APINumber.fillna(0).astype('int64').astype(str)
    #print(d.APINumber.describe())
    dlst.append(d)
df_all = pd.concat(dlst,sort=True)
df_all.head(20)<jupyter_output><empty_output><jupyter_text>**Note that the data in the df_all dataframe are already recalculated to per/day values.**  Gas records processing (an illustration)this is an illustration of what is done to Gas,Oil and Brine all at once below.   Pivot for columns as API, rows as time periodsIn some reporting quarters, some wells are reported more than once.  For these, we take the sum for a given well as the total production for that period. We then pivot the dataset so that each well is a column.<jupyter_code>gas = df_all[['APINumber','period','Gas']]
gasgb = gas.groupby(['period','APINumber'],as_index=False)['Gas'].sum()
Gas_pivot =gasgb.pivot(index='period',columns='APINumber')
Gas_pivot.columns = Gas_pivot.columns.get_level_values(1) # snags the API# as the column name
Gas_pivot.fillna(0,inplace=True) # if a well had no record in a time period, make it '0' instead of NaN.
Gas_pivot.drop('0',axis=1,inplace=True) # drop the column named '0' - it is an artifact.

# plot some curves, ignore start time
Gas_pivot.plot(y=Gas_pivot.columns[:300],use_index=True,legend=False,linewidth=0.5,color='gray')<jupyter_output><empty_output><jupyter_text>find the first non-zero data point in the timeseries<jupyter_code>id_lst = list(Gas_pivot.index)

def id_first_nonzero(ser):
    # returns the index of the first non-zero component (that is, when well comes on line)
    lst = np.nonzero(ser)
    #print(len(lst),lst)
    if len(lst[0])==0:
        return 'all_zero'
    return id_lst[lst[0][0]] # use the first value in the non-zero list to point into the period index

#### test function -----------
ser = pd.Series([0,0,0,1])
id_first_nonzero(ser)
##### --------------------
strt = pd.DataFrame(Gas_pivot.apply(id_first_nonzero),columns=['start_period']) 
strt.tail()<jupyter_output>C:\Users\BMansfield\Anaconda3\envs\datawork\lib\site-packages\numpy\core\fromnumeric.py:51: FutureWarning: Series.nonzero() is deprecated and will be removed in a future version.Use Series.to_numpy().nonzero() instead
  return getattr(obj, method)(*args, **kwds)<jupyter_text>merge start period with production data<jupyter_code># merge gas data with column that points to first non-zero data
mg = pd.merge(strt,Gas_pivot.T,on='APINumber',how='outer',validate='1:1')
mg.head(10)<jupyter_output><empty_output><jupyter_text>make some plots to verify separation by period<jupyter_code>def plotperiod(per='2011QX'):
    tdf = mg[mg.start_period==per]
    #tdf.info()
    tdf= tdf.drop(['start_period'],axis=1)
    #tdf.info()
    #tdf =tdf.set_index('APINumber')
    t = tdf.T
    t.plot(y=t.columns,use_index=True,legend=False,linewidth=0.5,color='gray',title=f'Starting {per}')
plotperiod(per='2011QX')
plotperiod(per='2013Q1')
plotperiod(per='2016Q1')
plotperiod(per='2017Q1')<jupyter_output><empty_output><jupyter_text>merge with permit data to get location and ownership of each well<jupyter_code># now merge with some of the well identifiers pulled in from the permit dataset.
mg = pd.merge(permit,mg,on='APINumber',validate='1:1',indicator=True,how='outer')
mg = mg[mg._merge != 'left_only']  # drop the permit rows that don't have corresponding production; keep non-meta rows
mg = mg.drop(['_merge'],axis=1)
print(len(mg))
mg.to_csv(tempfn)<jupyter_output>2233<jupyter_text>finally, save in excel file with separate sheets<jupyter_code>fsdict = {} # dict to hold set parsed by first_nonzero
fslst = sorted(list(strt.start_period.unique()))
for fs in fslst:
    fsdict[fs] = mg[mg.start_period == fs]
save_xls(fsdict,tempexcel)<jupyter_output><empty_output><jupyter_text>Process Gas, Oil and Brine with functionNow let's do it for real.  Process each of the production types using a function that just passes the name of each...  Define the functions that will do the processing<jupyter_code># used to find the id of the first
def id_first_nonzero(ser):
    # returns the index of the first non-zero component (that is, when well comes on line)
    lst = np.nonzero(ser)
    #print(len(lst),lst)
    if len(lst[0])==0:
        return 'all_zero'
    return id_lst[lst[0][0]] # use the first value in the non-zero list to point into the period index

def processAll():
    for pn in ['Gas','Oil','Brine']:
        temp = processProduct(pn)

def processProduct(pname='Gas'):
    product = df_all[['APINumber','period',pname]]  # make small dataset with just 3 columns
    productgb = product.groupby(['period','APINumber'],as_index=False)[pname].sum()
    Pivot =productgb.pivot(index='period',columns='APINumber')
    Pivot.columns = Pivot.columns.get_level_values(1) # snags the API# as the column name
    Pivot.fillna(0,inplace=True) # if a well had no record in a time period, make it '0' instead of NaN.
    Pivot.drop('0',axis=1,inplace=True)# drop the column named '0' - it is an artifact.

    id_lst = list(Pivot.index)
    strt = pd.DataFrame(Pivot.apply(id_first_nonzero),columns=['start_period']) 

    # merge production data with column that points to first non-zero data
    mg = pd.merge(strt,Pivot.T,on='APINumber',how='outer',validate='1:1')

    # now merge with some of the well identifiers pulled in from the permit dataset.
    mg = pd.merge(permit,mg,on='APINumber',validate='1:1',indicator=True,how='outer')
    mg = mg[mg._merge != 'left_only']  # drop the permit rows that don't have corresponding production; keep non-meta rows
    mg = mg.drop(['_merge'],axis=1)

    fsdict = {} # dict to hold set parsed by first_nonzero
    fslst = sorted(list(strt.start_period.unique()))
    for fs in fslst:
        fsdict[fs] = mg[mg.start_period == fs]
    save_xls(fsdict,f'{outdir}{pname}{outsuff}')

    return fsdict<jupyter_output><empty_output><jupyter_text>Finally, make all three files<jupyter_code>processAll()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1269901083372586, 'fraction_numerical': 0.0131888836552049, 'mean_word_length': 4.849035812672176, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 0, '>': 47, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '612428', 'license': 'CC0-1.0', 'n_tokens_mistral': 3612, 'n_tokens_neox': 3457, 'n_words': 1048, 'path': 'ODNR Production data v3.ipynb', 'repo_name': 'gwallison/ohio_production_to_FT_format', 'size': '10617'}