In [1]:

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

In [2]:

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

In [3]:

    df = pd.read_csv("merged_raw_original.csv")

    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\1736138736.py:1: DtypeWarning: Columns (4,36,38) have mixed types. Specify dtype option on import or set low_memory=False.
      df = pd.read_csv("merged_raw_original.csv")

In [4]:

    ###clean CAS number
    #NP refers to invalid chemical entries (labled as 'N/A'/'unknown'/'not assigned' or similar terms)
    #TS refers to trade secret chemicals (labled as 'proprietary'/'trade secrets'/'confidential' or similar terms)
    #SD refers to system disclosure entries without CAS numbers (labled as 'listed below'/'system disclosure' or similar terms)
    df['CASNumber'] = df['CASNumber'].str.strip()
    df['CASNumber'] = df['CASNumber'].str.lstrip('0')
    df['CASNumber'] = df['CASNumber'].str.strip('*')
    df['CASNumber'] = df['CASNumber'].str.strip('#')
    df['CASNumber'] = df['CASNumber'].str.strip('(')
    df['CASNumber'] = df['CASNumber'].str.strip(')')
    df['CASNumber'] = df['CASNumber'].str.strip('"')
    df['CASNumber'] = df['CASNumber'].str.strip("'")
    df['CASNumber'] = df['CASNumber'].str.strip('?')
    df['CASNumber'] = df['CASNumber'].str.replace('.','-')
    CAS_ODD_dic = pd.read_csv('uniqueCAS.csv', header=None, index_col=0, squeeze=True).to_dict()
    df['CAS_cleaned']= df['CASNumber'].map(CAS_ODD_dic)
    df = df.drop(df[df.CAS_cleaned == 'SD'].index)

    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\192648759.py:14: FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions will *not* be treated as literal strings when regex=True.
      df['CASNumber'] = df['CASNumber'].str.replace('.','-')
    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\192648759.py:15: FutureWarning: The squeeze argument has been deprecated and will be removed in a future version. Append .squeeze("columns") to the call to squeeze.

      CAS_ODD_dic = pd.read_csv('uniqueCAS.csv', header=None, index_col=0, squeeze=True).to_dict()

In [5]:

    ###construct trade secret variables
    df['CAScount'] = df['CAS_cleaned'].groupby(df['UploadKey']).transform('count')
    df['CASisTS'] = np.where(df['CAS_cleaned']== 'TS', True, False)
    df['TScount'] = df['CASisTS'].groupby(df['UploadKey']).transform('sum')
    df['CAScount_uq'] = df.groupby(['UploadKey'])['CAS_cleaned'].transform('nunique')

In [6]:

    #TS_per
    df['TS_perCASall'] = df['TScount']/df['CAScount']

In [7]:

    #CAS_anony
    df['TS_perCASuq'] = df['TScount']/(df['CAScount_uq']-1)

In [8]:

    #TS_conc
    df['HFper_TS'] = df['PercentHFJob']*df['CASisTS']
    df_nowater = df[df['CAS_cleaned']!='7732185']
    HFperTS_well_nowater = df_nowater.groupby(['UploadKey'])['HFper_TS','PercentHFJob'].sum().reset_index()
    HFperTS_well_nowater['HFper_TS_snw'] = HFperTS_well_nowater['HFper_TS']/HFperTS_well_nowater['PercentHFJob']
    HFperTS_well_nowater_keep = HFperTS_well_nowater[['UploadKey','HFper_TS_snw']]

    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\3494932677.py:4: FutureWarning: Indexing with multiple keys (implicitly converted to a tuple of keys) will be deprecated, use a list instead.
      HFperTS_well_nowater = df_nowater.groupby(['UploadKey'])['HFper_TS','PercentHFJob'].sum().reset_index()

In [9]:

    #save TS variables
    df_TS = df[['UploadKey', 'TS_perCASall', 'TS_perCASuq']]
    df_TS.drop_duplicates(inplace=True)
    TS_final = df_TS.merge(HFperTS_well_nowater_keep, on="UploadKey", how="left")
    TS_final.replace([np.inf, -np.inf], np.nan, inplace=True)
    TS_final.dropna(inplace=True)
    TS_final.to_csv('TS_final.csv', index=False, encoding='utf-8-sig')

    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\2265433829.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_TS.drop_duplicates(inplace=True)

In [10]:

    ###prepare chemical-concentration data

In [11]:

    #construct chemical-concentration-date dataset
    df_noNP = df[df.CAS_cleaned!= 'NP']
    df_noNP_agg = df_noNP.groupby(['UploadKey','CAS_cleaned'])['PercentHFJob'].sum().reset_index()
    df_wellinfo = df[['UploadKey','APINumber', 'OperatorName',  'date', 'year', 'month']]
    df_wellinfo.drop_duplicates(inplace=True)
    df_dbscan = df_noNP_agg.merge(df_wellinfo, on='UploadKey', how='left')

    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\82267316.py:5: 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_wellinfo.drop_duplicates(inplace=True)

In [12]:

    #merge FracFocus data with WellDatabase using API number

In [13]:

    df_WDB = pd.read_csv('header_prod.csv')

    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\3437568458.py:1: DtypeWarning: Columns (34,40) have mixed types. Specify dtype option on import or set low_memory=False.
      df_WDB = pd.read_csv('header_prod.csv')

In [14]:

    df_WDB_select = df_WDB[['API','Play','Latitude','Longitude','FirstSixMonthOil','FirstSixMonthBOE']]
    df_dbscan_gis = df_dbscan.merge(df_WDB_select, left_on='APINumber', right_on='API', how='left')
    df_dbscan_gis  = df_dbscan_gis[df_dbscan_gis['Play'].notna()]

In [15]:

    df_dbscan_gis.to_csv('frac_DBSCAN.csv', index=False, encoding='utf-8-sig')

In [16]:

    ###generate control variables

In [17]:

    df_dbscan_gis_well = df_dbscan_gis[['UploadKey','OperatorName','Play','date','year','month','FirstSixMonthOil','FirstSixMonthBOE']]
    df_dbscan_gis_well.drop_duplicates(inplace=True)

    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\676992104.py:2: 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_dbscan_gis_well.drop_duplicates(inplace=True)

In [18]:

    #WELLNUM_new
    df_dbscan_gis_well_byplay = df_dbscan_gis_well.groupby(['OperatorName','year','Play'])['UploadKey'].count().reset_index()
    df_dbscan_gis_well_byplay.rename(columns={'UploadKey':'wellcount_byplay'}, inplace=True)
    df_dbscan_gis_well_byplay['log_wellcount_byplay'] = np.log2(df_dbscan_gis_well_byplay.wellcount_byplay+1)

    #note: WELLNUM_new and WELLNUM_all are log-transformed with base 2, while OGPROD is log-transformed with base e.
    #These variables were constructed at different times in different files, and this is an 'omission' I found after
    #the paper was online. As shown in Table 2 and Figure 3, the main findings remain robust without adding any
    #control variables. The results remain robust regardless of how these controls are transformed (with base 2 or e).

In [19]:

    #WELLNUM_all
    df_dbscan_gis_well_byplay['cumsum_byplay'] = df_dbscan_gis_well_byplay.groupby(['OperatorName','Play']).wellcount_byplay.cumsum()
    df_dbscan_gis_well_byplay['log_cumsum_byplay'] = np.log2(df_dbscan_gis_well_byplay.cumsum_byplay+1)

In [20]:

    #OPEXP
    df_dbscan_gis_well_byplay['firstyear_byplay'] = df_dbscan_gis_well_byplay.groupby(['OperatorName','Play'])['year'].transform('first')
    df_dbscan_gis_well_byplay['opexp_byplay']=df_dbscan_gis_well_byplay['year']-df_dbscan_gis_well_byplay['firstyear_byplay']+1

In [21]:

    #OGPROD
    df_dbscan_gis_well['OGprod'] = (df_dbscan_gis_well['FirstSixMonthOil'] + df_dbscan_gis_well['FirstSixMonthBOE'])/1000
    df_dbscan_gis_well['OGprod_byplay'] = df_dbscan_gis_well.groupby(['OperatorName','Play','year','month'])['OGprod'].transform('mean')
    df_dbscan_gis_well['OGprod_byplay_y'] = df_dbscan_gis_well.groupby(['OperatorName','Play','year'])['OGprod'].transform('mean')
    df_dbscan_gis_well['ln_OGprod_byplay'] = np.log(df_dbscan_gis_well.OGprod_byplay+1)
    df_dbscan_gis_well['ln_OGprod_byplay_y'] = np.log(df_dbscan_gis_well.OGprod_byplay_y+1)
    df_dbscan_gis_well_og = df_dbscan_gis_well[['OperatorName','Play','year','month','ln_OGprod_byplay','ln_OGprod_byplay_y']]
    df_dbscan_gis_well_og.drop_duplicates(inplace=True)

    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\3530519892.py:2: 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_dbscan_gis_well['OGprod'] = (df_dbscan_gis_well['FirstSixMonthOil'] + df_dbscan_gis_well['FirstSixMonthBOE'])/1000
    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\3530519892.py:3: 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_dbscan_gis_well['OGprod_byplay'] = df_dbscan_gis_well.groupby(['OperatorName','Play','year','month'])['OGprod'].transform('mean')
    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\3530519892.py:4: 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_dbscan_gis_well['OGprod_byplay_y'] = df_dbscan_gis_well.groupby(['OperatorName','Play','year'])['OGprod'].transform('mean')
    C:\Users\anson\anaconda3\lib\site-packages\pandas\core\arraylike.py:402: RuntimeWarning: invalid value encountered in log
      result = getattr(ufunc, method)(*inputs, **kwargs)
    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\3530519892.py:5: 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_dbscan_gis_well['ln_OGprod_byplay'] = np.log(df_dbscan_gis_well.OGprod_byplay+1)
    C:\Users\anson\anaconda3\lib\site-packages\pandas\core\arraylike.py:402: RuntimeWarning: invalid value encountered in log
      result = getattr(ufunc, method)(*inputs, **kwargs)
    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\3530519892.py:6: 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_dbscan_gis_well['ln_OGprod_byplay_y'] = np.log(df_dbscan_gis_well.OGprod_byplay_y+1)
    C:\Users\anson\AppData\Local\Temp\ipykernel_6860\3530519892.py:8: 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_dbscan_gis_well_og.drop_duplicates(inplace=True)

In [22]:

    #merge all controls
    df_dbscan_gis_well_c = df_dbscan_gis_well.merge(df_dbscan_gis_well_byplay, on=['OperatorName','Play','year'], how='left')
    df_dbscan_gis_well_c = df_dbscan_gis_well_c.drop(['FirstSixMonthOil', 'FirstSixMonthBOE','OGprod','OGprod_byplay','firstyear_byplay','wellcount_byplay','cumsum_byplay'], axis=1)
    df_dbscan_gis_well_c.to_csv('controls.csv', index=False, encoding='utf-8-sig' )

In [23]:

    ###split chemical-concentration data by shale play (for DBSCAN)

In [24]:

    df_dbscan_gis = df_dbscan_gis.drop(['FirstSixMonthOil', 'FirstSixMonthBOE','API'], axis=1)

In [25]:

    for i, x in df_dbscan_gis.groupby('Play'):
        p = os.path.join(os.getcwd(), "playDBSCAN_{}.csv".format(i.lower()))
        x.to_csv(p, index=False, encoding='utf-8-sig')