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


###import well data
df_frac = pd.read_csv('fracfocus_nomonitor.csv')


###merge well data with monitor data (executed in QGIS)
#import well data to QGIS
#import river monitor, shalent monitor, and well monitor data to QGIS
#join well data with monitor data (one to many, set maximum neighbor as 9999)
#for river monitor - set maximum distance as 2.5km(0.0225°)/5km(0.045°)/10km(0.09°)
#for well monitor - set maximum distance as 500m(0.0045°)
#for shalenet monitor - set maximum distance as 2.5km(0.0225°)
#export joined layers as csv
df_river = pd.read_csv('river_joined.csv')
df_river_5km = pd.read_csv('river_joined_5km.csv')
df_river_10km = pd.read_csv('river_joined_10km.csv')
df_well = pd.read_csv('well_joined.csv')
df_shalenet = pd.read_csv('shalenet_joined.csv')


###merge STORET sampling data with the well data and filter active monitors
#import sampling data from the Water Quality Portal
df_samp_storet = pd.read_csv('frac_monitor_sampling.csv')
df_samp_storet['ActivityStartDate'] = pd.to_datetime(df_samp_storet['ActivityStartDate'])
df_samp_storet_keep = df_samp_storet[['MonitoringLocationIdentifier','ActivityStartDate']]

df_river_keep = df_river[['UploadKey','date','MonitoringLocationIdentifier']]
df_river_notna = df_river_keep[~df_river_keep['MonitoringLocationIdentifier'].isna()]
df_river_list = df_river_notna['MonitoringLocationIdentifier'].to_list()
df_river_samp = df_samp_storet_keep[df_samp_storet_keep['MonitoringLocationIdentifier'].isin(df_river_list)]

df_river_5km_keep = df_river_5km[['UploadKey','date','MonitoringLocationIdentifier']]
df_river_notna_5km = df_river_5km_keep[~df_river_5km_keep['MonitoringLocationIdentifier'].isna()]
df_river_list_5km = df_river_notna_5km['MonitoringLocationIdentifier'].to_list()
df_river_samp_5km = df_samp_storet_keep[df_samp_storet_keep['MonitoringLocationIdentifier'].isin(df_river_list_5km)]

df_river_10km_keep = df_river_10km[['UploadKey','date','MonitoringLocationIdentifier']]
df_river_notna_10km = df_river_10km_keep[~df_river_10km_keep['MonitoringLocationIdentifier'].isna()]
df_river_list_10km = df_river_notna_10km['MonitoringLocationIdentifier'].to_list()
df_river_samp_10km = df_samp_storet_keep[df_samp_storet_keep['MonitoringLocationIdentifier'].isin(df_river_list_10km)]

df_well_keep = df_well[['UploadKey','date','MonitoringLocationIdentifier']]
df_well_notna = df_well_keep[~df_well_keep['MonitoringLocationIdentifier'].isna()]
df_well_list = df_well_notna['MonitoringLocationIdentifier'].to_list()
df_well_samp = df_samp_storet_keep[df_samp_storet_keep['MonitoringLocationIdentifier'].isin(df_well_list)]

#river monitors (2.5km)
df_river_merge = df_river_notna.merge(df_river_samp, on='MonitoringLocationIdentifier', how='left')
df_river_merge['date'] = pd.to_datetime(df_river_merge['date'])
df_river_merge['date_interval'] = df_river_merge['date']-df_river_merge['ActivityStartDate']
df_river_merge['date_interval']  = (df_river_merge['date_interval']).dt.days
df_river_merge_active = df_river_merge[(df_river_merge['date_interval']<=365) & (df_river_merge['date_interval']>=0)]
df_river_merge_active['river_base'] = 1
df_river_merge_active_keep = df_river_merge_active[['UploadKey','river_base']]
df_river_merge_active_keep.drop_duplicates(inplace=True)
df_frac_river = df_frac.merge(df_river_merge_active_keep, on='UploadKey', how='left')
df_frac_river['river_base'].fillna(0, inplace=True)
df_frac_river['watch_river_base'] = df_frac_river.groupby(['OperatorName', 'Play'])['river_base'].transform('max')

#river monitors (5km)
df_river_merge_5km = df_river_notna_5km.merge(df_river_samp_5km, on='MonitoringLocationIdentifier', how='left')
df_river_merge_5km['date'] = pd.to_datetime(df_river_merge_5km['date'])
df_river_merge_5km['date_interval'] = df_river_merge_5km['date']-df_river_merge_5km['ActivityStartDate']
df_river_merge_5km['date_interval']  = (df_river_merge_5km['date_interval']).dt.days
df_river_merge_active_5km = df_river_merge_5km[(df_river_merge_5km['date_interval']<=365) & (df_river_merge_5km['date_interval']>=0)]
df_river_merge_active_5km['river_5km'] = 1
df_river_merge_active_keep_5km = df_river_merge_active_5km[['UploadKey','river_5km']]
df_river_merge_active_keep_5km.drop_duplicates(inplace=True)
df_frac_river_5km = df_frac_river.merge(df_river_merge_active_keep_5km, on='UploadKey', how='left')
df_frac_river_5km['river_5km'].fillna(0, inplace=True)
df_frac_river_5km['watch_river_5km'] = df_frac_river_5km.groupby(['OperatorName', 'Play'])['river_5km'].transform('max')

#river monitors (10km)
df_river_merge_10km = df_river_notna_10km.merge(df_river_samp_10km, on='MonitoringLocationIdentifier', how='left')
df_river_merge_10km['date'] = pd.to_datetime(df_river_merge_10km['date'])
df_river_merge_10km['date_interval'] = df_river_merge_10km['date']-df_river_merge_10km['ActivityStartDate']
df_river_merge_10km['date_interval']  = (df_river_merge_10km['date_interval']).dt.days
df_river_merge_active_10km = df_river_merge_10km[(df_river_merge_10km['date_interval']<=365) & (df_river_merge_10km['date_interval']>=0)]
df_river_merge_active_10km['river_10km'] = 1
df_river_merge_active_keep_10km = df_river_merge_active_10km[['UploadKey','river_10km']]
df_river_merge_active_keep_10km.drop_duplicates(inplace=True)
df_frac_river_10km = df_frac_river_5km.merge(df_river_merge_active_keep_10km, on='UploadKey', how='left')
df_frac_river_10km['river_10km'].fillna(0, inplace=True)
df_frac_river_10km['watch_river_10km'] = df_frac_river_10km.groupby(['OperatorName', 'Play'])['river_10km'].transform('max')

#well monitors
df_well_merge = df_well_notna.merge(df_well_samp, on='MonitoringLocationIdentifier', how='left')
df_well_merge['date'] = pd.to_datetime(df_well_merge['date'])
df_well_merge['date_interval'] = df_well_merge['date']-df_well_merge['ActivityStartDate']
df_well_merge['date_interval']  = (df_well_merge['date_interval']).dt.days
df_well_merge_active = df_well_merge[(df_well_merge['date_interval']<=365) & (df_well_merge['date_interval']>=0)]
df_well_merge_active['well'] = 1
df_well_merge_active_keep = df_well_merge_active[['UploadKey','well']]
df_well_merge_active_keep.drop_duplicates(inplace=True)
df_frac_river_well = df_frac_river_10km.merge(df_well_merge_active_keep, on='UploadKey', how='left')
df_frac_river_well['well'].fillna(0, inplace=True)
df_frac_river_well['watch_well'] = df_frac_river_well.groupby(['OperatorName', 'Play'])['well'].transform('max')


###merge shalent sampling data with the well data and filter active monitors
#import shalenet sampling data
df_shalenet_keep = df_shalenet[['UploadKey','date','SiteID']]
df_shalenet_notna = df_shalenet_keep[~df_shalenet_keep['SiteID'].isna()]
df_shalenet_list = df_shalenet_notna['SiteID'].to_list()

df_samp_shale = pd.read_csv('shalenet_raw.csv')
df_samp_shale['LocalDateTime'] = pd.to_datetime(df_samp_shale['LocalDateTime'])
df_samp_shale_keep = df_samp_shale[['LocalDateTime','SiteID']]
df_shalenet_samp = df_samp_shale_keep[df_samp_shale_keep['SiteID'].isin(df_shalenet_list)]

#shalenet monitors
df_shalenet_merge = df_shalenet_notna.merge(df_shalenet_samp, on='SiteID', how='left')
df_shalenet_merge['date'] = pd.to_datetime(df_shalenet_merge['date'])
df_shalenet_merge['date_interval'] = df_shalenet_merge['date']-df_shalenet_merge['LocalDateTime']
df_shalenet_merge['date_interval']  = (df_shalenet_merge['date_interval']).dt.days
df_shalenet_merge_active = df_shalenet_merge[(df_shalenet_merge['date_interval']<=365) & (df_shalenet_merge['date_interval']>=0)]
df_shalenet_merge_active['shalenet'] = 1
df_shalenet_merge_active_keep = df_shalenet_merge_active[['UploadKey','shalenet']]
df_shalenet_merge_active_keep.drop_duplicates(inplace=True)
df_frac_all = df_frac_river_well.merge(df_shalenet_merge_active_keep, on='UploadKey', how='left')
df_frac_all['shalenet'].fillna(0, inplace=True)
df_frac_all['watch_shalenet'] = df_frac_all.groupby(['OperatorName', 'Play'])['shalenet'].transform('max')

df_frac_all.to_csv('fracfocus_monitor.csv', index=False)

