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]:

    df = pd.read_csv('fracfocus_welllevel.csv')
    df_singletons = pd.read_csv('singletons.csv')
    singlist = df_singletons['UploadKey'].to_list()
    df_final = df[~df['UploadKey'].isin(singlist)]

In [4]:

    ###within-operator analysis
    #aggregate well level data to operator-shale play level
    df_within_op = df_final[['OperatorName','year','Play','public','ln_OGprod_byplay', 'ln_OGprod_byplay_y',
                      'log_wellcount_byplay', 'log_cumsum_byplay', 'opexp_byplay', 'inn_play']]
    df_within_op.drop_duplicates(inplace=True)
    df_within_op.to_csv('fracfocus_within_op.csv', index=False)

    C:\Users\anson\AppData\Local\Temp\ipykernel_26420\1504151745.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_within_op.drop_duplicates(inplace=True)

In [5]:

    ###between-operator analysis (public operators only)
    #keep public operators and merge financial data
    df_public = df_final[df_final['public']==1]
    df_public['inn_op'] = df_public.groupby(['ticker'])['inn_play'].transform('max')
    df_public['tort_op'] = df_public.groupby(['ticker'])['tort_post'].transform('max')
    df_public_keep = df_public[['year','ticker','inn_op','tort_op']]
    df_public_keep.drop_duplicates(inplace=True)
    df_financial = pd.read_csv('financial.csv')
    df_merge = df_public_keep.merge(df_financial, on=['year','ticker'], how='left')

    C:\Users\anson\AppData\Local\Temp\ipykernel_26420\4146800768.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_public['inn_op'] = df_public.groupby(['ticker'])['inn_play'].transform('max')
    C:\Users\anson\AppData\Local\Temp\ipykernel_26420\4146800768.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_public['tort_op'] = df_public.groupby(['ticker'])['tort_post'].transform('max')
    C:\Users\anson\AppData\Local\Temp\ipykernel_26420\4146800768.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_public_keep.drop_duplicates(inplace=True)
    C:\Users\anson\AppData\Local\Temp\ipykernel_26420\4146800768.py:8: DtypeWarning: Columns (12,16,30,951,957,958,965,969,975) have mixed types. Specify dtype option on import or set low_memory=False.
      df_financial = pd.read_csv('financial.csv')

In [6]:

    #generate financial variables
    df_merge['size'] = np.log(df_merge['at'])
    df_merge['lev'] = (df_merge['dltt']+df_merge['dlc'])/df_merge['seq']
    df_merge['roa'] = df_merge['ni']/df_merge['at']
    df_merge['tobin'] = (df_merge['at']+df_merge['csho']*df_merge['prcc_f']-df_merge['ceq'])/df_merge['at']
    df_merge['cash'] = df_merge['che']/df_merge['at']
    df_merge['capex'] = df_merge['capx']/df_merge['at']
    df_merge['rd_b'] = np.where(df_merge['xrd']>0, 1, 0)
    df_merge['profitmargin'] = df_merge['ni']/df_merge['sale']

In [7]:

    #merge with factiva data
    df_factiva = pd.read_csv('factiva.csv')
    df_between_op = df_merge.merge(df_factiva, on=['year','ticker'], how='left')
    df_between_op['ln_media'] = np.log(df_between_op['media_num'])

In [8]:

    df_between_op_keep = df_between_op[['year','ticker','inn_op','size','lev','roa','tobin',
                                        'cash','capex','rd_b','profitmargin','tort_op','ln_media']]
    df_between_op_keep.to_csv("fracfocus_between_op.csv", index=False)

In [9]:

    ###innovative measure validation
    df_valid = df_final[['year','month','Play','OperatorName','public','ln_OGprod_byplay','inn_play','imi']]
    df_valid.drop_duplicates(inplace=True)
    df_valid.to_csv('inn_validation.csv', index=False)

    C:\Users\anson\AppData\Local\Temp\ipykernel_26420\2211697966.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_valid.drop_duplicates(inplace=True)