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

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


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


###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)


###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')

#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']

#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'])

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)


###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)

