In [1]:

    import csv
    import pandas as pd
    import os
    import glob
    import numpy as np

In [2]:

    os.getcwd()
    os.chdir(r'C:\Users\anson\Dropbox\Dissertation\Fracfocus\FracFocusCSV')

In [3]:

    ###combine all novel wells
    path = r'C:\Users\anson\Dropbox\Dissertation\Fracfocus\FracFocusCSV\playTS\noisekey'
    all_files = glob.glob(os.path.join(path, "*"))

    df_novelkey = pd.concat([pd.read_csv(fp).assign(cluster=os.path.basename(fp).split('.')[0]) for fp in all_files])
    df_novelkey['dbscan1k'] = 1
    df_novelkey.rename(columns={"Uploadkey_noise": "UploadKey"}, inplace=True)
    df_novelkey_keep = df_novelkey[['UploadKey','dbscan1k']]

In [4]:

    ###combine full well clusters (without iteration)
    path = r'C:\Users\anson\Dropbox\Dissertation\Fracfocus\FracFocusCSV\playTS\cluster_1k'
    all_files = glob.glob(os.path.join(path, "*"))

    df_wellclusters = pd.concat([pd.read_csv(fp).assign(cluster=os.path.basename(fp).split('.')[0]) for fp in all_files])
    df_wellclusters_keep = df_wellclusters[['UploadKey','cluster','date','year','month','Play']]
    df_wellclusters_keep.drop_duplicates(inplace=True)

    C:\Users\anson\AppData\Local\Temp\ipykernel_27072\2098424101.py:5: DtypeWarning: Columns (16) have mixed types. Specify dtype option on import or set low_memory=False.
      df_wellclusters = pd.concat([pd.read_csv(fp).assign(cluster=os.path.basename(fp).split('.')[0]) for fp in all_files])
    C:\Users\anson\AppData\Local\Temp\ipykernel_27072\2098424101.py:5: DtypeWarning: Columns (12) have mixed types. Specify dtype option on import or set low_memory=False.
      df_wellclusters = pd.concat([pd.read_csv(fp).assign(cluster=os.path.basename(fp).split('.')[0]) for fp in all_files])
    C:\Users\anson\AppData\Local\Temp\ipykernel_27072\2098424101.py:7: SettingWithCopyWarning:
    A value is trying to be set on a copy of a slice from a DataFrame

    See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
      df_wellclusters_keep.drop_duplicates(inplace=True)

In [5]:

    df_full = df_wellclusters_keep.merge(df_novelkey_keep, on="UploadKey", how="left")
    df_full['dbscan1k'] = df_full['dbscan1k'].replace(np.nan, 0)

In [6]:

    ###merge with state mandatory disclosure dates and keep wells drilled after the mandatory reporting dates

In [7]:

    df_raw = pd.read_csv(r'C:\Users\anson\Dropbox\Dissertation\Fracfocus\FracFocusCSV\Fracfocus_raw\merged_raw_original.csv')
    df_state = df_raw[['UploadKey','StateNumber','Latitude','Longitude']]
    df_state.drop_duplicates(inplace=True)
    df_full_state = df_full.merge(df_state, on="UploadKey", how="left")

    C:\Users\anson\AppData\Local\Temp\ipykernel_27072\188804597.py:1: DtypeWarning: Columns (4,36,38) have mixed types. Specify dtype option on import or set low_memory=False.
      df_raw = pd.read_csv(r'C:\Users\anson\Dropbox\Dissertation\Fracfocus\FracFocusCSV\Fracfocus_raw\merged_raw_original.csv')
    C:\Users\anson\AppData\Local\Temp\ipykernel_27072\188804597.py:3: SettingWithCopyWarning:
    A value is trying to be set on a copy of a slice from a DataFrame

    See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
      df_state.drop_duplicates(inplace=True)

In [8]:

    df_mandate = pd.read_csv("state_man.csv")
    df_mandate['man_date'] = pd.to_datetime(df_mandate[['year', 'month', 'day']])
    df_mandate_keep = df_mandate[['StateNumber','man_date']]
    df_full_state_man = df_full_state.merge(df_mandate_keep, on="StateNumber", how="left")
    df_full_state_man['date']= pd.to_datetime(df_full_state_man['date'])
    df_man = df_full_state_man[df_full_state_man['date'] > df_full_state_man['man_date']]

In [9]:

    ###merge with TS variables and control variables
    df_TS = pd.read_csv("TS_final.csv")
    df_control = pd.read_csv("controls.csv")
    df_control_keep = df_control[['UploadKey','OperatorName','ln_OGprod_byplay','ln_OGprod_byplay_y','log_wellcount_byplay','log_cumsum_byplay','opexp_byplay']]
    df_man_TS = df_man.merge(df_TS, on="UploadKey", how="left")
    df_man_TS_c = df_man_TS.merge(df_control_keep, on='UploadKey', how="left")

In [10]:

    ###generate innovative operator variables (operator-shale play level)
    df_man_TS_c['inn_play'] = df_man_TS_c.groupby(['OperatorName','Play'])['dbscan1k'].transform('max')

In [11]:

    #identify noninnovative wells that changed chemical clusters to prior disclosed ones

In [12]:

    df_novelcluster = df_man_TS_c[df_man_TS_c['dbscan1k']==1]
    inn_cluster = df_novelcluster['cluster'].to_list()
    df_man_TS_c['imi'] = np.where((df_man_TS_c['inn_play']==0) & (df_man_TS_c['cluster'].isin(inn_cluster)), 1, 0)

In [13]:

    df_man_TS_c.dropna(inplace=True)

In [14]:

    ###cross-sectional variables

In [15]:

    #disct_strict
    df_disc = pd.read_csv('disc_strict.csv')
    df_man_TS_c1 = df_man_TS_c.merge(df_disc, on='StateNumber', how='left')

In [16]:

    #budget_high
    #split by state-year median (24192.67)
    df_budget = pd.read_csv('budget_staff_gdp.csv')
    df_man_TS_c2 = df_man_TS_c1.merge(df_budget, on=['StateNumber','year'], how='left')
    df_man_TS_c2['spend_oggdp'] = df_man_TS_c2['spend']/df_man_TS_c2['oggdp']
    df_man_TS_c2['budget_high'] = np.where(df_man_TS_c2['spend_oggdp']>=24192.67, 1, 0)

In [17]:

    #urban
    #import the 2019 US nation TIGER/Line Shapefile to QGIS
    #import well data to QGIS
    #intersect the well points and the MMSA polygons
    #save the joined layer as csv
    df_urban = pd.read_csv('urban_joined.csv')
    df_urban['urban'] = np.where(df_urban['NAME'].isnull(), 0, 1)
    df_urban_keep = df_urban[['UploadKey','urban']]
    df_man_TS_c3 = df_man_TS_c2.merge(df_urban_keep, on='UploadKey', how='left')

    C:\Users\anson\AppData\Local\Temp\ipykernel_27072\831165391.py:6: DtypeWarning: Columns (6,7,8,10) have mixed types. Specify dtype option on import or set low_memory=False.
      df_urban = pd.read_csv('urban_joined.csv')

In [18]:

    #tort_post
    df_tort = pd.read_csv('tort_filedate.csv')
    df_tort['tort_filedate'] = pd.to_datetime(df_tort[['year', 'month', 'day']])
    df_tort_keep = df_tort[['OperatorName','StateNumber','tort_filedate']]
    df_man_TS_c4_temp = df_man_TS_c3.merge(df_tort_keep, on=['OperatorName','StateNumber'], how='left')
    df_man_TS_c4_tort = df_man_TS_c4_temp.loc[~df_man_TS_c4_temp.tort_filedate.isnull(), :]
    df_man_TS_c4_tort['tort_post'] = np.where(df_man_TS_c4_tort['tort_filedate']<=df_man_TS_c4_tort['date'], 1, 0)
    df_man_TS_c4_tort_keep = df_man_TS_c4_tort[['UploadKey', 'tort_post']]
    df_man_TS_c4 = df_man_TS_c3.merge(df_man_TS_c4_tort_keep, on='UploadKey', how='left')
    df_man_TS_c4['tort_post'].fillna(0, inplace = True)

    C:\Users\anson\AppData\Local\Temp\ipykernel_27072\4249358836.py:7: SettingWithCopyWarning:
    A value is trying to be set on a copy of a slice from a DataFrame.
    Try using .loc[row_indexer,col_indexer] = value instead

    See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
      df_man_TS_c4_tort['tort_post'] = np.where(df_man_TS_c4_tort['tort_filedate']<=df_man_TS_c4_tort['date'], 1, 0)

In [19]:

    ###operators with available stock price
    df_pubop = pd.read_csv('operator_match.csv')
    df_merge = df_man_TS_c4.merge(df_pubop, on='OperatorName', how='left')
    df_merge['public'] = np.where(df_merge['ticker'].isnull(), 0, 1)

In [20]:

    df_merge.to_csv('fracfocus_nomonitor.csv', index=False)