<jupyter_start><jupyter_text>Updating E.Coli data for CRWA's visualization  Approach:1. There is a google spreadsheet that is connected to ArcGis which is used as the main database. This database will be updated with new data through this code.2. The new data will be pulled through an Access Query. The file must be named the same as in the code.  3. By running this code the current data on google spreadsheet will be cleared and replaced with the updated & formated data. 4. ArcGis should then be updated with the new data. 5. Data will also be archived in Google Drive. The downloadble data file will also be created.  Steps to run code:1.   Export data from Access as csv and store in local folder. Make sure the file is named "DataVizPipeline.xlsx". 2.   In runtime column press "Run all"3.   Scroll down and follow code:  1. In the allowing access to Google Drive / Google Sheet section, click on the generated link which will open a new tab. In the new tab select the google account and click "allow". Then copy the verification code and paste into intial section of this notebook.   2. In the file upload section, click on "choose files" and select the newly exported access data.    Notes:* Make sure that there are the following spreadsheets already created in the google dirve:  * E.Coli Database for ArcGis  * data_site* Make sure the outputs are pointed to correct location:  * i.e. Folder for archiving data is set to "E.Coli Data" initally but can be changed to any other location. *   Initializing Libraries<jupyter_code>#Installing Reverse Geocoder for coordinates mapping
!pip install reverse_geocoder
# Importing libraries
import pandas as pd
import numpy as np
from datetime import date

from oauth2client.client import GoogleCredentials
from google.colab import files
from google.colab import auth
from google.colab import drive
import io

import reverse_geocoder as rg

import gspread
from gspread_dataframe import set_with_dataframe

import warnings
warnings.filterwarnings('ignore')<jupyter_output><empty_output><jupyter_text>Allowing Access to Google Drive / Google Sheet<jupyter_code># Allowing access to Google Drive / Google Sheet
# Follow instructions to allow access: copy the code from new tab into box below and press enter to confirm. Will need to perform this action each time it is prompted.
auth.authenticate_user()<jupyter_output><empty_output><jupyter_text>File Upload<jupyter_code># Upload access data into google colab
uploaded = files.upload()
# Importing data into dataframe to be manipulated 
df = pd.read_excel(io.BytesIO(uploaded['DataVizPipeline.xlsx']))

# Alternative code to import CSV
# df = pd.read_csv(io.BytesIO(uploaded['DataVizPipeline.csv']))

# Check
df.head()<jupyter_output><empty_output><jupyter_text>Data Manipulation<jupyter_code># Formating/processing data into usuable/correct format

# Selecting columns to be used
df_proc = df[['Date_Collected', 'Component_Name', 'Results.Site_ID', 'Site_Name', 'Latitude_DD', 'Longitude_DD', 'Reporting_Result', 'Unit_Abbreviation']]

# Renaming the Site ID column
df_proc.rename(columns = {'Results.Site_ID':'Site_ID'}, inplace = True)
#Finding number of unique values within the column to see if there is erronious data
for i in df_proc.columns:
    print(i)
    print(df_proc[i].nunique())
# Finding unique values within column 
print(df_proc['Component_Name'].unique())
print(df_proc['Unit_Abbreviation'].unique())<jupyter_output>['Escherichia coli' 'Fecal coliform']
['cfu/100ml' 'MPN/100ml']<jupyter_text>Note: Component Name and Unit Abbreviation should all be the same.<jupyter_code># Renaming Fecal coliform to match with Escherichia coli
df_proc.loc[df_proc['Component_Name'] == 'Fecal coliform', 'Component_Name'] = 'Escherichia coli'

# Renaming unit abbreviation to match
df_proc.loc[df_proc['Unit_Abbreviation'] == 'MPN/100ml', 'Unit_Abbreviation'] = 'cfu/100ml'

# Check
for i in df_proc.columns:
    print(i)
    print(df_proc[i].nunique())
# Check
df_proc.head()
# Get coordinates of the sites
coordinates = []
for row in df_proc.pivot_table(index=['Latitude_DD', 'Longitude_DD']).index:
    coordinates.append(row)
coordinates = set(coordinates)

# Get town names based on coordinates
towns = []
for coor in coordinates: # This takes about 30 seconds
    towns.append(rg.search(coor)[0]['name'])
# Make a dataframe with Latitude, Longitude and Town names
df_town = pd.DataFrame(data = coordinates, columns=['Latitude_DD', 'Longitude_DD'])

# Renaming column
df_town['Town'] = towns

# Check 
df_town.head()
# Adding town locations to dataset
df_final = df_proc.merge(df_town, on=['Latitude_DD', 'Longitude_DD'], how='left')

# Check 
df_final.head()
#Checking if dataset contains null values
df_final.isnull().sum()
# Checking number of rows before deleting
print('Number of rows before deleting: {}'.format(df_final.shape))

# Deleting any rows with empty values
df_final = df_final.dropna(how='any', axis = 0)

# Checking number of after deleting
print('Number of rows before deleting: {}'.format(df_final.shape))

#Checking if dataset contains null values
print('')
df_final.isnull().sum()
#Adding safety column based on reporting result column
df_final['Safety'] = df_final['Reporting_Result']
df_final['Safety'] = pd.cut(df_final['Safety'], [-1, 235, 1260, 1000000], labels=['Safe', 'No Swimming', 'Not safe for activities'])

# note: leave the labels as they are, because they are, as is, the basis for the color coding in the ArcGIS base map.
#       we then manually changed the legend of the web map to be more precise and be what is displayed on the final dashboard

# Check
df_final.head()<jupyter_output><empty_output><jupyter_text>Clearing Old Data and Importing New Data<jupyter_code># Estabilishing credentials to interact with Google drive & Google Sheet
gc = gspread.authorize(GoogleCredentials.get_application_default())<jupyter_output><empty_output><jupyter_text>Note: There must be a spreadsheet in google drive named "E.Coli Database for ArcGis"<jupyter_code># Defining worksheet 
sheet = gc.open('E.Coli Database for ArcGis').sheet1
# Clear all contents in worksheet
sheet.clear()
# Importing final data frame into google sheets
set_with_dataframe(sheet, df_final)<jupyter_output><empty_output><jupyter_text>Creating Data_site Sheet<jupyter_code># Selecting columns to be used to create data_site sheet
df_site = df_final[['Town', 'Site_ID', 'Site_Name', 'Latitude_DD', 'Longitude_DD']]

# Check 
df_site.head()
# Checking number of before deleting
print('Number of rows before deleting: {}'.format(df_site.shape))

# Deleting any duplicates (keeping unique rows)
df_site_final = df_site.drop_duplicates()

# Checking number of after deleting
print('Number of rows after deleting: {}'.format(df_site_final.shape))<jupyter_output>Number of rows before deleting: (4017, 5)
Number of rows after deleting: (39, 5)<jupyter_text>Creating Data_Site File<jupyter_code># Estabilishing credentials to interact with Google drive & Google Sheet
gc = gspread.authorize(GoogleCredentials.get_application_default())<jupyter_output><empty_output><jupyter_text>Note: There must be a spreadsheet in google drive named "data_site"<jupyter_code># Defining worksheet 
sheet = gc.open('data_site').sheet1
# Clear all contents in worksheet
sheet.clear()
# Importing final data frame into google sheets
set_with_dataframe(sheet, df_site_final)<jupyter_output><empty_output><jupyter_text>Archiving Dataset to Google Drive<jupyter_code># Follow instructions to allow access: copy the code ffrom new tab into box below and press enter to confirm. 
drive.mount('drive')
# Naming csv file to current date
today = date.today()
file_name = f'{today}_e_coli_data.csv'

# Uploading final dataframe to google drive as referrence
upload_df = df_final.to_csv(file_name)
!cp  $file_name "drive/My Drive/E. Coli Data/"<jupyter_output><empty_output><jupyter_text>Creating Download File<jupyter_code># Naming csv file to current date
download_file_name = 'e_coli_data.csv'

# Uploading final dataframe to google drive as referrence
upload_df = df_final.to_csv(download_file_name)
!cp  $download_file_name "drive/My Drive/"<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08842285994603875, 'fraction_numerical': 0.006377238165317636, 'mean_word_length': 5.922750424448218, 'pattern_counts': {'":': 0, '<': 47, '<?xml version=': 0, '>': 47, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '385093', 'license': 'MIT', 'n_tokens_mistral': 2584, 'n_tokens_neox': 2461, 'n_words': 891, 'path': 'projects/crwa/notebooks/Sending_Access_Data_to_Google_Sheets.ipynb', 'repo_name': 'kkliu5/safe-water', 'size': '8154'}