<jupyter_start><jupyter_text>Pre-Processing CSV data for MapAggC General Approach:1) Read in csv sheet of data.  Export fields into new dataframe.2) Remove bad rows and elements.3) Fix date datatype.4) For ReportingUnit df, include ID value for easy match to shp file in ArcGIS (see below).8) Export completed df as processed csZv. Files Needed1) dontopen_RU.csv, 2) dontopen_AggLJAll.csv<jupyter_code>#Needed Libararies
import os
import numpy as np
import pandas as pd
from datetime import datetime
pd.set_option('display.max_columns', 999)  # How to display all columns of a Pandas DataFrame in Jupyter Notebook
pd.options.display.max_rows  # Increases the length of a printed string from a DataFrame
pd.set_option('display.max_colwidth', -1)<jupyter_output>C:\Users\rjame\Anaconda3\lib\site-packages\ipykernel_launcher.py:8: FutureWarning: Passing a negative integer is deprecated in version 1.0 and will not be supported in future version. Instead, use None to not limit the column width.<jupyter_text>ReportingUnits Table and Export General NotesWe drop the geometry field at this time.  API returns a encrypted version of it, which we can't work with at this time.  Have to instead match ReportingUnit_dim table to several separate shp files based on Unit Type (e.g., county, HUC8, custom, etc).  Have to build an ID value for this to work.  Each Unit Type file ID and match approach is different, see below. ReportingUnit csv to ArcGIS shp file notes.Creating ID to link p-csv to shp file works for every ReportUnitType but HUC8, as the ArcGIS shp we use doesn't incldue name in its data.  Have to cheat, and for all HUC8 types, the format should be "2_" + !HUC_8! + "_" + !StateNum!.  For the time, this was easier to do by hand than by code.Wyoming also includes "... River Planning Basin" in its REportingUnitName.  Easier just to remove by hand.  Changed one WY shp file entry to Snake-Salt, from Salt-Snake.California custom DAUCO also worked better using ReportingUnitNativeID.AZ custom AM alsow worked better using ReportingUnitNative ID.  Shp didn't include a StateNum field, I just added it in.USBR did work okay with ReportingUnitName.  Did have to update the shapefile with StateNum field = 100.<jupyter_code>#Working Directory and Input File
workingDir = "C:/Users/rjame/Documents/WSWC Documents/Portal Creation Research"
os.chdir(workingDir)
RU_Input = "dontopen_RU.csv"
df_RU = pd.read_csv(RU_Input)
df_RU
#removeing bad rows of df because they are null for some odd reason
df_RU = df_RU[(df_RU.ReportingUnitUUID != 'test')]

#Dropping fields we don't need.
df_RU = df_RU.drop(['Geometry', 'ReportingUnitUpdateDate', 'ReportingUnitProductVersion'], axis=1)

#Fixing Index
df_RU = df_RU.reset_index()
df_RU = df_RU.drop(columns=['index'])

df_RU
# #Creating Linking Element with Shape File.
# #Format Ex: "1_" + !NAME! + "_" + !StateNum!

# stateNumDic = {
#     "UT" : "46",
#     "NM" : "35",
#     "NE" : "41",
#     "CO" : "41",
#     "WY" : "47",
#     "CA" : "49",
#     "AZ" : "48",
#     "TX" : "37",
#     "US" : "100"
# }

# def createTypeNameNum(colrowValueA, colrowValueB, colrowValueC):
#     StringA = colrowValueA.strip()  # ReportingUnitTypeCV
#     StringB = colrowValueB.strip()  # ReportingUnitName
#     StringC = colrowValueC.strip()  # state for StateNumDic
    
#     outStringA = ""
    
#     if StringA == "County":
#         outStringA = "1"
#     if StringA == "HUC8":
#         outStringA = "2"
#     if StringA == "Basin":
#         outStringA = "3"
#     if StringA == "Planning Area":
#         outStringA = "4"
#     if StringA == "Active Management Area":
#         outStringA = "5"
#     if StringA == "Tributary":
#         outStringA = "6"
#     if StringA == "Subarea":
#         outStringA = "7"
#     if StringA == "Hydrologic Region":
#         outStringA = "8"
    
#     outList = outStringA + "_" + StringB + "_" + stateNumDic[StringC]
#     return outList

# df_RU['TypeNameNum'] = df_RU.apply(lambda row: createTypeNameNum(row['ReportingUnitTypeCV'], row['ReportingUnitName'], row['StateCV']), axis=1)
# df_RU
# #Creating Linking Element with Shape File.
# #Format Ex: "1_" + !ID! + "_" + !StateNum!

# stateNumDic = {
#     "UT" : "46",
#     "NM" : "35",
#     "NE" : "41",
#     "CO" : "42",
#     "WY" : "47",
#     "CA" : "49",
#     "AZ" : "48",
#     "TX" : "37",
#     "US" : "100"
# }


# def createTypeNameNum(colrowValueA, colrowValueB, colrowValueC):
#     StringA = colrowValueA.strip()  # ReportingUnitTypeCV
#     StringB = colrowValueB.strip()  # ReportingUnitNativeID
#     StringC = colrowValueC.strip()  # state for StateNumDic
    
#     outStringA = ""
    
#     if StringA == "County":
#         outStringA = "1"
#     if StringA == "HUC8":
#         outStringA = "2"
#     if StringA == "Basin":
#         outStringA = "3"
#     if StringA == "Planning Area":
#         outStringA = "4"
#     if StringA == "Active Management Area":
#         outStringA = "5"
#     if StringA == "Tributary":
#         outStringA = "6"
#     if StringA == "Subarea":
#         outStringA = "7"
#     if StringA == "Hydrologic Region":
#         outStringA = "8"
    
#     outList = outStringA + "_" + StringB + "_" + stateNumDic[StringC]
#     return outList

# df_RU['TypeIDNum'] = df_RU.apply(lambda row: createTypeNameNum(row['ReportingUnitTypeCV'], row['ReportingUnitNativeID'], row['StateCV']), axis=1)
# df_RU
#The Ouput
df_RU.to_csv('Pagg_ReportingUnit.csv', index=False)<jupyter_output><empty_output><jupyter_text>AggregatedAmounts_withOrg<jupyter_code>AAwO_Input = "dontopen_AggLJAll.csv"
df_AAwO = pd.read_csv(AAwO_Input)
df_AAwO.head(5)
date_Input = "dontopen_Date_dim.csv"
df_date = pd.read_csv(date_Input)
df_date.head(5)
#Retreiving TimeframeStart from Dates_dim Table
TimeframeStartdict = pd.Series(df_date.Date.values, index = df_date.DateID).to_dict()

# For creating TimeframeStart
def retrieveTimeframeStart(colrowValue):
    if colrowValue == '' or pd.isnull(colrowValue):
        outList = ''
    else:
        val1 = colrowValue
        try:
            outList = TimeframeStartdict[val1]
        except:
            outList = colrowValue
    return outList

df_AAwO['TimeframeStart'] = df_AAwO.apply(lambda row: retrieveTimeframeStart(row['TimeframeStartID']), axis=1)
df_AAwO.head(5)
#Retreiving TimeframeEnd from Dates_dim Table
TimeframeEnddict = pd.Series(df_date.Date.values, index = df_date.DateID).to_dict()

# For creating TimeframeEnd
def retrieveTimeframeEnd(colrowValue):
    if colrowValue == '' or pd.isnull(colrowValue):
        outList = ''
    else:
        val1 = colrowValue
        try:
            outList = TimeframeEnddict[val1]
        except:
            outList = colrowValue
    return outList

df_AAwO['TimeframeEnd'] = df_AAwO.apply(lambda row: retrieveTimeframeEnd(row['TimeframeEndID']), axis=1)
df_AAwO.head(5)
#Dropping fields we don't need.
df_AAwO = df_AAwO.drop(['TimeframeStartID', 'TimeframeEndID'], axis=1)
df_AAwO.head(5)
df_AAwO['BeneficialUseCV'].fillna('Unspecified', inplace=True)
df_AAwO
#The Ouput
df_AAwO.to_csv('Pagg_AggregatedAmountsAll.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11961856682092273, 'fraction_numerical': 0.013462347496844763, 'mean_word_length': 3.735723771580345, 'pattern_counts': {'":': 16, '<': 12, '<?xml version=': 0, '>': 12, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '307021', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 2576, 'n_tokens_neox': 2465, 'n_words': 674, 'path': 'App 2_AggregatedPolygonData/MapAggPoly_ver2d/bonus code/Pre Processing MapAggD Data.ipynb', 'repo_name': 'WSWCWaterDataExchange/WaDE2.0Apps', 'size': '7131'}