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

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

In [4]:

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

    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\3936702248.py:9: DtypeWarning: Columns (41,46,50) have mixed types. Specify dtype option on import or set low_memory=False.
      df_river = pd.read_csv('river_joined.csv')
    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\3936702248.py:10: DtypeWarning: Columns (41,46,50) have mixed types. Specify dtype option on import or set low_memory=False.
      df_river_5km = pd.read_csv('river_joined_5km.csv')
    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\3936702248.py:11: DtypeWarning: Columns (41,46,50) have mixed types. Specify dtype option on import or set low_memory=False.
      df_river_10km = pd.read_csv('river_joined_10km.csv')
    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\3936702248.py:12: DtypeWarning: Columns (36,37,38,39,40,41,50,51,52,53,55,57,58,59,60,63,64,65,68,70,71,72) have mixed types. Specify dtype option on import or set low_memory=False.
      df_well = pd.read_csv('well_joined.csv')
    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\3936702248.py:13: DtypeWarning: Columns (35,37) have mixed types. Specify dtype option on import or set low_memory=False.
      df_shalenet = pd.read_csv('shalenet_joined.csv')

In [5]:

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

    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\462377535.py:3: DtypeWarning: Columns (5,9,10,11,12,14,15,17,19,21,23,24,25,26,31,32,33,34,35,36,37,40,42,44,46,48,53,60,61,62,63,65,66,67,68,69,70,71,72,73,74) have mixed types. Specify dtype option on import or set low_memory=False.
      df_samp_storet = pd.read_csv('frac_monitor_sampling.csv')

In [6]:

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

    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\3281791290.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_river_merge_active['river_base'] = 1
    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\3281791290.py:9: 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_river_merge_active_keep.drop_duplicates(inplace=True)

In [7]:

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

    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\824476316.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_river_merge_active_5km['river_5km'] = 1
    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\824476316.py:9: 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_river_merge_active_keep_5km.drop_duplicates(inplace=True)

In [8]:

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

    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\813538697.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_river_merge_active_10km['river_10km'] = 1
    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\813538697.py:9: 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_river_merge_active_keep_10km.drop_duplicates(inplace=True)

In [9]:

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

    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\3840093633.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_well_merge_active['well'] = 1
    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\3840093633.py:9: 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_well_merge_active_keep.drop_duplicates(inplace=True)

In [10]:

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

    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\506370164.py:7: DtypeWarning: Columns (18,35) have mixed types. Specify dtype option on import or set low_memory=False.
      df_samp_shale = pd.read_csv('shalenet_raw.csv')

In [11]:

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

    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\400180270.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_shalenet_merge_active['shalenet'] = 1
    C:\Users\anson\AppData\Local\Temp\ipykernel_26836\400180270.py:9: 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_shalenet_merge_active_keep.drop_duplicates(inplace=True)

In [12]:

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