<jupyter_start><jupyter_text>Creating single csv export that has a 1-1 many relationship with allocation and site.<jupyter_code>#Needed Libaraies
import os
import numpy as np
import pandas as pd
#Setting work directory, reading inputs, creating dataframe
workingDir = "C:/Users/rjame/Documents/WSWC Documents/Portal Creation Research"
os.chdir(workingDir)
fileInput = "dontopen_WaDEQAQuery_All.csv"
#fileInput = "dontopen_WaDEQAQuery_CO.csv"
#fileInput = "dontopen_WaDEQAQuery_UT.csv"
#fileInput = "dontopen_WaDEQAQuery_AZ.csv"
#fileInput = "dontopen_WaDEQAQuery_NM.csv"
df = pd.read_csv(fileInput)
df
df.dtypes
#removeing bad rows of df because they are null for some odd reason
df = df[(df.WaterSourceName != 'test')]
#changing data type of 'Date' to datatype, then changing format of 'Date' to yyyy-mm-dd
df['Date'] = pd.to_datetime(df['Date'], errors = 'coerce')
df['Date'] = pd.to_datetime(df["Date"].dt.strftime('%m/%d/%Ym'))
WSWCBenUseDict = {
"Irrigation" : "Agricultural",
"Agriculture" : "Agricultural",
"Irrigation Storage" : "Agricultural",
"Agriculture other than irrigation" : "Agricultural",
"Commercial" : "Commercial",
"Commercial From Storage" : "Commercial",
"Commercial Storage" : "Commercial",
"72-12-1 Sanitary in conjunction with a commercial use" : "Commercial",
"Domestic" : "Domestic",
"72-12-1 domestic and livestock watering" : "Domestic",
"72-12-1 domestic one household" : "Domestic",
"72-12-1 multiple domestic households" : "Domestic",
"Mobile home parks" : "Domestic",
"Subdivision" : "Domestic",
"Household Use Only" : "Domestic",
"Domestic From Storage" : "Domestic",
"Domestic Storage" : "Domestic",
"Public utility" : "Domestic",
"Minimum Streamflow" : "Environmental",
"Ground Water Recharge" : "Environmental",
"Ground Water Recharge From Storage" : "Environmental",
"Ground Water Recharge Storage" : "Environmental",
"Lake Level Maintenance" : "Environmental",
"Minimum Stream Flow" : "Environmental",
"Mitigation" : "Environmental",
"72-12-1 Prospecting or development of natural resource" : "Environmental",
"Pollution control well" : "Environmental",
"Pollution Abatement" : "Environmental",
"Fire" : "Fire",
"Fire Protection" : "Fire",
"Fire Protection From Storage" : "Fire",
"Fire Protection Storage" : "Fire",
"Flood Control Storage" : "Flood Control",
"Flood Control" : "Flood Control",
"Industrial" : "Industrial",
"Cemetery" : "Industrial",
"Construction" : "Industrial",
"Dairy operation" : "Industrial",
"Domestic construction" : "Industrial",
"Highway construction" : "Industrial",
"Oil production" : "Industrial",
"Poultry and egg operation" : "Industrial",
"Industrial From Storage" : "Industrial",
"Industrial Storage" : "Industrial",
"Irrigation From Storage" : "Industrial",
"72-12-1 Construction of public works" : "Industrial",
"Petroleum processing plant" : "Industrial",
"Secondary recovery of oil" : "Industrial",
"Stockwater" : "Livestock",
"Stockwater From Storage" : "Livestock",
"Stockwater Storage" : "Livestock",
"72-12-1 livestock watering" : "Livestock",
"Stock" : "Livestock",
"Stockwatering" : "Livestock",
"Mining" : "Mining",
"Mining From Storage" : "Mining",
"Mining Storage" : "Mining",
"Mining or milling or oil" : "Mining",
"Municipal" : "Municipal",
"Municipal From Storage" : "Municipal",
"Municipal Storage" : "Municipal",
"Municipal - city or county supplied water" : "Municipal",
"Geothermal" : "Power",
"Power From Storage" : "Power",
"Power Storage" : "Power",
"Geothermal boreholes" : "Power",
"Power" : "Power",
"Power Generation" : "Power",
"Recharge" : "Recharge",
"Recreation From Storage" : "Recreation",
"Recreation Storage" : "Recreation",
"Recreation" : "Recreation",
"Snow Making" : "Snow Making",
"Storage" : "Storage",
"Fishery" : "Wildlife",
"Fish Habitat" : "Wildlife",
"Fish Habitat Storage" : "Wildlife",
"Fish Propagation" : "Wildlife",
"Fish Propagation From Storage" : "Wildlife",
"Fish Propagation Storage" : "Wildlife",
"Wildlife From Storage" : "Wildlife",
"Wildlife Storage" : "Wildlife",
"Wildlife" : "Wildlife",
"Fish And Wildlife" : "Wildlife",
"Fish and game propogation" : "Wildlife"}


def assignWSWCBU(colrowValue):
    if colrowValue == '' or pd.isnull(colrowValue):
        outList = "Other"
    else:
        String1 = colrowValue  # remove whitespace chars
        try:
            outList = WSWCBenUseDict[String1]
        except:
            outList = "Other"
    return outList

df['WSWCBeneficialUse'] = df.apply(lambda row: assignWSWCBU(row['BeneficialUseCV']), axis=1)
#Removing NaN, and missing (999) values from AllocaitonAmount, AllocaitonMaxium, IrrigatedAcerage.
df['AllocationAmount'] = df['AllocationAmount'].fillna(0)
df['AllocationAmount'] = df['AllocationAmount'].replace(999, 0)

df['AllocationMaximum'] = df['AllocationMaximum'].fillna(0)
df['AllocationMaximum'] = df['AllocationMaximum'].replace(999, 0)

df['IrrigatedAcreage'] = df['IrrigatedAcreage'].fillna(0)
df['IrrigatedAcreage'] = df['IrrigatedAcreage'].replace(999, 0)
#Outbound DataFrame
#outdf = pd.DataFrame(index = df.index)
outdf = pd.DataFrame()

# ###################
# #Temporary, tyring to get this dataset to work in Tableau.  Need a unique ID for each site.
# outdf['UniqueID'] = df['State'].astype(str) + "_" + df['AllocationNativeID'].astype(str)
# ###################

outdf['WSWC AllocationAmountID'] = df['AllocationAmountID'].astype(float) 
outdf['State ID'] = df['State'].astype(str) 
outdf['SiteUUID'] = df['SiteUUID'].astype(str) 
outdf['State Allocation Native ID'] = df['AllocationNativeID'].astype(str) 
outdf['Allowed Allocation Flow (CFS)'] = df['AllocationAmount'].astype(float) 
outdf['Allowed Allocation Volume (AF)'] = df['IrrigatedAcreage'].astype(float) 
outdf['Allocation Max Limit Volume (AF)'] = df['AllocationMaximum'].astype(float) 
outdf['Priority Date'] = df['Date']
outdf['WSWC Identified Beneficial Use'] = df['WSWCBeneficialUse'].astype(str) 
outdf['State Defined Beneficial Use'] = df['BeneficialUseCV'].astype(str) 
outdf['Water Source Name'] = df['WaterSourceName'].astype(str) 
outdf['POD Latitude'] = df['Latitude'].astype(float) 
outdf['POD Longitude'] = df['Longitude'].astype(float) 

outdf.reset_index()
#Exporting Outbound DataFrame to working csv file.
outdf.to_csv('P_WaDEQA_All_v2.csv', index=False)  # The output<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16205910390848427, 'fraction_numerical': 0.009374006990784875, 'mean_word_length': 6.502979737783075, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '158152', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 2253, 'n_tokens_neox': 2111, 'n_words': 536, 'path': 'App 1_WaterRightPointData/MapPODSite_ver3/bonus code/.ipynb_checkpoints/PreProcessData_Allocationl-checkpoint.ipynb', 'repo_name': 'WSWCWaterDataExchange/WaDE2.0Apps', 'size': '6294'}