<jupyter_start><jupyter_text>This jupyter notebook contains python scripting used in ArcGIS Pro to prioritize budget needs for water system improvementsInputs include the 1. a personal geodatabase copy of the water network pipe feature class for size, material, install date, wrapping,2. a personal geodatabase copy of the main break data feature class for current existing pipes, without breaks on pipes that have been replaced3. reference data includes roads, soils, could also include bridges, culverts, hydraulic model, planning & zoning, etcPipe categories are statistically evaluated to determine a likelihood of break ranking based on the input factorsThose rank scores are then adjusted to consider actual existing broken and repaired locations that would benefit from replacement.Replacement costs can be compared with a statistically ranked value based on the quality of improvement to get the most benefit per dollar in scheduling routine maintenance of the system by replacing smaller, older, and failure prone pipes.Based on the score a project year and rough cost estimates can be assigned for the CIP.The maps and graphs are included in a related arcgis pro project that is also the default workspace for the following python script.https://www.waterrf.org/system/files/resource/2019-05/4332_1.pdfcreated by <NAME> on 7/16/2021<jupyter_code>collectorwatermains = r'https://gis2.lawrenceks.org/arcgis/rest/services/Utilities_Dept/WaterCollector/FeatureServer/11'
#this cell will set variables referred to throughout the rest of this script for asset management calculations

#as of about Late october, there is now a service available for breaks and leaks

watermains = "Water_Main_2021_12_3"
#BreakData = r"C:\Users\kgonterwitz\OneDrive - City of Lawrence KS\Documents\ArcGIS\Projects\WaterAssets\WaterAssets.gdb\Main_breaks_Not_Repaired_2021_5"

BreakData = r'https://gisweb.lawrenceks.org/hosting/rest/services/Water/Water_Breaks_and_Leaks/FeatureServer'

#for the map analysis add the features from above and remove them from the group layer, so the layer name in TOC is this:
BreakData = "Water Main Breaks"

Soils= r"C:\Users\kgonterwitz\OneDrive - City of Lawrence KS\Documents\ArcGIS\Projects\WaterAssets\WaterAssets.gdb\Soils_SSURGO"


#initiate new analysis geodatabase and copy latest updated version of water main data
projdb = r"C:\Users\kgonterwitz\OneDrive - City of Lawrence KS\Documents\ArcGIS\Projects\WaterAssets\WaterAssets_2021_12_03.gdb"


#Export Water Mains from hosted feature service layer to analysis geodatabase
#this will also reproject the analysis data to KRCS Zone 11

assetwhereclause = "WATERTYPE IN ('Potable') And OPERATIONAREA NOT IN ('HINU', 'KU', 'OTH', 'UNK', 'WTP') And DIAMETER <> 0"
wmains = ("https://gis2.lawrenceks.org/arcgis/rest/services/Utilities_Dept/WaterCollector/FeatureServer/11")
Zone11 = 'PROJCS["NAD_1983_2011_KS_RCS_Zone_11",GEOGCS["GCS_NAD_1983_2011",DATUM["D_NAD_1983_2011",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",11500000.0],PARAMETER["False_Northing",600000.0],PARAMETER["Central_Meridian",-95.25],PARAMETER["Standard_Parallel_1",39.1],PARAMETER["Scale_Factor",1.000033],PARAMETER["Latitude_Of_Origin",39.1],UNIT["Foot_US",0.3048006096012192]]'
with arcpy.EnvManager(scratchWorkspace=projdb, outputCoordinateSystem=Zone11, workspace=projdb):
    arcpy.conversion.FeatureClassToFeatureClass(wmains, projdb, watermains, assetwhereclause, '#', '')
#need to correct a few material types

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "MATERIAL = ' di'", None)
arcpy.management.CalculateField(watermains, "MATERIAL", '"DI"', "PYTHON3", '', "TEXT")

print(str(arcpy.management.GetCount(watermains)) + " DI pipes Corrected")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "MATERIAL = ' pvc'", None)
arcpy.management.CalculateField(watermains, "MATERIAL", '"PVC"', "PYTHON3", '', "TEXT")

print(str(arcpy.management.GetCount(watermains)) + " PVC pipes Corrected")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "MATERIAL LIKE '%sdr%'", None)
arcpy.management.CalculateField(watermains, "MATERIAL", '"PVC"', "PYTHON3", '', "TEXT")
print(str(arcpy.management.GetCount(watermains)) + " PVC pipes Corrected type 2")

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION")

#create a truly unique pipe identifier

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION")
arcpy.management.AddField(watermains, "Unique_AssetID", "TEXT", None, None, 50, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateField(watermains, "Unique_AssetID", 'str(!FACILITYID!) +"-"+ str(!OBJECTID!)', "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")
arcpy.management.AddIndex(watermains, "Unique_AssetID", "AssetID_U2", "UNIQUE", "ASCENDING")


#classify pipe diameters for pipes 12" and less, including greater than 12"
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "DIAMETER > 12", None)
arcpy.management.CalculateField(watermains, "PipeDiameterScore", "6", "PYTHON3", '', "LONG")
print(str(arcpy.management.GetCount(watermains)) + " Greater Than 12 in")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "DIAMETER = 12", None)
arcpy.management.CalculateField(watermains, "PipeDiameterScore", "5", "PYTHON3", '', "LONG")
print(str(arcpy.management.GetCount(watermains)) + " 12 in")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "DIAMETER = 10", None)
arcpy.management.CalculateField(watermains, "PipeDiameterScore", "4", "PYTHON3", '', "LONG")
print(str(arcpy.management.GetCount(watermains)) + " 10 in")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "DIAMETER = 8", None)
arcpy.management.CalculateField(watermains, "PipeDiameterScore", "3", "PYTHON3", '', "LONG")
print(str(arcpy.management.GetCount(watermains)) + " 8 in")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "DIAMETER = 6", None)
arcpy.management.CalculateField(watermains, "PipeDiameterScore", "2", "PYTHON3", '', "LONG")
print(str(arcpy.management.GetCount(watermains)) + " 6 in")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "DIAMETER = 4", None)
arcpy.management.CalculateField(watermains, "PipeDiameterScore", "1", "PYTHON3", '', "LONG")
print(str(arcpy.management.GetCount(watermains)) + " 4 in")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore is null", None)
arcpy.management.CalculateField(watermains, "PipeDiameterScore", "0", "PYTHON3", '', "LONG")
print(str(arcpy.management.GetCount(watermains)) + " other diameter")
#Calculte Life Expectancy based on material and pipe diameter classification
arcpy.management.AddField(watermains, "ExpectedLife", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 0 And MATERIAL = 'CAS'", None)
arcpy.management.CalculateField(watermains, "ExpectedLife", "100", "PYTHON3", '', "TEXT")

print(str(arcpy.management.GetCount(watermains)) + " 0 in Cast Iron - 100 yr")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 1 And MATERIAL = 'CAS'", None)
print(str(arcpy.management.GetCount(watermains)) + " 4 in Cast Iron - 95 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "95", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 2 And MATERIAL = 'CAS'", None)
print(str(arcpy.management.GetCount(watermains)) + " 6 in Cast Iron - 85 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "95", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 3 And MATERIAL = 'CAS'", None)
print(str(arcpy.management.GetCount(watermains)) + " 8 in Cast Iron - 90 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "90", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 4 And MATERIAL = 'CAS'", None)
print(str(arcpy.management.GetCount(watermains)) + " 10 in Cast Iron - 80 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "80", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 5 And MATERIAL = 'CAS'", None)
print(str(arcpy.management.GetCount(watermains)) + " 12 in Cast Iron - 75 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "75", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 6 And MATERIAL = 'CAS'", None)
print(str(arcpy.management.GetCount(watermains)) + " Large Cast Iron - 70 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "70", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 6 And MATERIAL = 'RCP'", None)
print(str(arcpy.management.GetCount(watermains)) + " Large RCP - 100 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "100", "PYTHON3", '', "TEXT")


arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 6 And MATERIAL = 'SP'", None)
print(str(arcpy.management.GetCount(watermains)) + " Large Steel - 70 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "70", "PYTHON3", '', "TEXT")


arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 0 And MATERIAL = 'COP'", None)
print(str(arcpy.management.GetCount(watermains)) + " 0 in Copper - 70 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "70", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 3 And MATERIAL = 'COP'", None)
print(str(arcpy.management.GetCount(watermains)) + " 6 in Copper - 60 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "60", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 5 And MATERIAL = 'COP'", None)
print(str(arcpy.management.GetCount(watermains)) + " 12 in Copper - 50 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "50", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 0 And MATERIAL = 'DIP' AND EXTCOVERING is null", None)
print(str(arcpy.management.GetCount(watermains)) + " 0 in DI - 60 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "60", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 1 And MATERIAL = 'DIP'AND EXTCOVERING is null", None)
print(str(arcpy.management.GetCount(watermains)) + " 4 in DI - 55 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "55", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 2 And MATERIAL = 'DIP'AND EXTCOVERING is null", None)
print(str(arcpy.management.GetCount(watermains)) + " 6 in DI - 50- yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "50", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 3 And MATERIAL = 'DIP'AND EXTCOVERING is null", None)
print(str(arcpy.management.GetCount(watermains)) + " 8 in DI - 40 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "40", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 4 And MATERIAL = 'DIP'AND EXTCOVERING is null", None)
print(str(arcpy.management.GetCount(watermains)) + " 10 in DI - 30 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "30", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 5 And MATERIAL = 'DIP'AND EXTCOVERING is null", None)
print(str(arcpy.management.GetCount(watermains)) + " 12 in DI - 20 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "20", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 6 And MATERIAL = 'DIP'AND EXTCOVERING is null", None)
print(str(arcpy.management.GetCount(watermains)) + " Large DI - 20 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "20", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 0 And MATERIAL = 'DIP' AND EXTCOVERING is not null", None)
print(str(arcpy.management.GetCount(watermains)) + " 0 in wrapped DI poly- 80 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "80", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 1 And MATERIAL = 'DIP'AND EXTCOVERING is not null", None)
print(str(arcpy.management.GetCount(watermains)) + " 4 in wrapped DI poly- 80 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "80", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 2 And MATERIAL = 'DIP'AND EXTCOVERING is not null", None)
print(str(arcpy.management.GetCount(watermains)) + " 6 in DI poly- 75 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "75", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 3 And MATERIAL = 'DIP'AND EXTCOVERING is not null", None)
print(str(arcpy.management.GetCount(watermains)) + " 8 in DI wrapped poly- 70 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "70", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 4 And MATERIAL = 'DIP'AND EXTCOVERING is not null", None)
print(str(arcpy.management.GetCount(watermains)) + " 10 in DI wrapped poly - 60 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "60", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 5 And MATERIAL = 'DIP'AND EXTCOVERING is not null", None)
print(str(arcpy.management.GetCount(watermains)) + " 12 in wrapped DI poly - 50 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "50", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 6 And MATERIAL = 'DIP'AND EXTCOVERING is not null", None)
print(str(arcpy.management.GetCount(watermains)) + " Large wrapped DI poly - 50 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "50", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 0 And MATERIAL = 'PVC'", None)
print(str(arcpy.management.GetCount(watermains)) + " 0 in PVC - 90 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "90", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 1 And MATERIAL = 'PVC'", None)
print(str(arcpy.management.GetCount(watermains)) + " 4 in PVC - 85 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "85", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 2 And MATERIAL = 'PVC'", None)
print(str(arcpy.management.GetCount(watermains)) + " 6 in PVC - 80 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "80", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 3 And MATERIAL = 'PVC'", None)
print(str(arcpy.management.GetCount(watermains)) + " 8 in PVC - 70 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "70", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 4 And MATERIAL = 'PVC'", None)
print(str(arcpy.management.GetCount(watermains)) + " 10 in PVC - 65 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "65", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 5 And MATERIAL = 'PVC'", None)
print(str(arcpy.management.GetCount(watermains)) + " 12 in PVC - 60 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "60", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 6 And MATERIAL = 'PVC'", None)
print(str(arcpy.management.GetCount(watermains)) + " 12 in PVC - 55 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "55", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 1 And MATERIAL = 'TTE'", None)
print(str(arcpy.management.GetCount(watermains)) + " 4 in TTE - 80 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "80", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 2 And MATERIAL = 'TTE'", None)
print(str(arcpy.management.GetCount(watermains)) + " 6 in TTE - 75 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "75", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 3 And MATERIAL = 'TTE'", None)
print(str(arcpy.management.GetCount(watermains)) + " 8 in TTE - 70 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "70", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 4 And MATERIAL = 'TTE'", None)
print(str(arcpy.management.GetCount(watermains)) + " 10 in TTE - 60 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "60", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 5 And MATERIAL = 'TTE'", None)
print(str(arcpy.management.GetCount(watermains)) + " 12 in TTE - 50 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "50", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "PipeDiameterScore = 6 And MATERIAL = 'TTE'", None)
print(str(arcpy.management.GetCount(watermains)) + " Large TTE - 45 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "45", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "ExpectedLife is null AND WATERTYPE = 'Potable'", None)
print(str(arcpy.management.GetCount(watermains)) + " unknown combo- 33 yr")
arcpy.management.CalculateField(watermains, "ExpectedLife", "33", "PYTHON3", '', "TEXT")
#evaluate install dates that are null, there are about 17 records, most of them are 8" pvc
#some might be recent edits 
#most are non-city infrastructure, like KU or HINU
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE IS NULL", None)
arcpy.management.CalculateField(watermains, "INSTALLDATE", '"1/1/1999"', "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")


#determine remaining life in years 
arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION")

arcpy.management.AddField(watermains, "LifeRemaining", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "ExpectedLife IS NOT NULL And INSTALLDATE IS NOT NULL", None)
arcpy.management.CalculateField(watermains, "LifeRemaining", "!ExpectedLife!-(2021-int(str(!INSTALLDATE!)[:4]))", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "LifeRemaining < 1 OR LifeRemaining is null", None)
arcpy.management.CalculateField(watermains, "LifeRemaining", "0", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION")
#calculate the percent of remaining life left for each pipe segment in GIS

arcpy.management.AddField(watermains, "PercentLifeRemaining", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
#arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "LifeRemaining > 0", None)
arcpy.management.CalculateField(watermains, "PercentLifeRemaining", "!LifeRemaining!/!ExpectedLife! * 100", "PYTHON3", '', "TEXT")

#add a field converting shape length feet to miles for total summary

arcpy.management.CalculateField(watermains, "MilesOfPipe", "!Shape_Length!/5280", "PYTHON3", '', "DOUBLE", "NO_ENFORCE_DOMAINS")
#exploring pandas data frames, pivot tables, and grouping material and diameter summary lengths
#this result would be useful for data quality control 


import pandas as pd
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "WATERTYPE = 'Potable' And MAINTBY NOT IN (-1)", None)

#look at total pipe length in feet by material with 10 or less years life remaining

#arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "LifeRemaining <11", None)

#Look at all values to check 
arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION")

fc = watermains

fields = ['MATERIAL','MilesOfPipe', 'DIAMETER'] #Change to match your input

df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(fc,fields),columns=fields)
table = pd.pivot_table(df, values='MilesOfPipe', index=['MATERIAL'], columns=['DIAMETER'], aggfunc=sum, margins = True)

#print(table)
#display(table)

from IPython.display import display, HTML

display(HTML(table.to_html()))
arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION")
#calculate the LOB Area statistics - consider pipes by material, coverings, and install period in decades
#calculate the install era - decade

arcpy.management.AddField(watermains, "Install_ERA", "TEXT", None, None, 12, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1900-01-01 00:00:00'", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1850-1899"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1910-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1900-1909"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1920-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1910-1919"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1930-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1920-1929"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1940-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1930-1939"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1950-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1940-1949"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1960-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1950-1959"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1970-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1960-1969"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1980-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1970-1979"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '1990-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1980-1989"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '2000-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"1990-1999"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '2010-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"2000-2009"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '2020-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"2010-2019"', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "INSTALLDATE < timestamp '2030-01-01 00:00:00' And Install_ERA IS NULL", None)
arcpy.management.CalculateField(watermains, "Install_ERA", '"2020-2029"', "PYTHON3", '', "TEXT")


arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION")

#exploring pandas data frames, pivot tables, and grouping material and diameter summary lengths
#this result would be useful for data quality control 


import pandas as pd
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "WATERTYPE = 'Potable'", None)

fc = watermains

fields = ['MATERIAL', 'Install_Era', 'MilesOfPipe'] #Change to match your input

df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(fc,fields),columns=fields)

table = pd.pivot_table(df, values='MilesOfPipe', index=['MATERIAL'], columns=['Install_Era'], aggfunc=sum, margins = True, dropna = True)
#print(table)
#display(table)

from IPython.display import display, HTML

display(HTML(table.to_html()))
#score the pipes based on corrosive soils - add the soils layer to the analysis map
#soils dont appear to contribute to higher LOB, we should stop doing this.  
#it can still be added to continue to test sensitivity to corrosive soils but dont use this grouping for LOB
#The SSURGO Soils layer has been added to the portal, it has to be added to the map named Soils_SSURGO, or I can change this code to use the feature service

soils = r'https://gisweb.lawrenceks.org/hosting/rest/services/Hosted/Douglas_County_Soils/FeatureServer'

arcpy.management.AddField(watermains, "SoilCorrosionValue", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.management.SelectLayerByAttribute("Soils_SSURGO", "NEW_SELECTION", "steelcorr = 1", None)
arcpy.management.SelectLayerByLocation(watermains, "HAVE_THEIR_CENTER_IN", "Soils_SSURGO", None, "NEW_SELECTION", "NOT_INVERT")

arcpy.management.SelectLayerByAttribute("Soils_SSURGO", "NEW_SELECTION", "steelcorr = 2", None)
arcpy.management.SelectLayerByLocation(watermains, "HAVE_THEIR_CENTER_IN", "Soils_SSURGO", None, "NEW_SELECTION", "NOT_INVERT")
arcpy.management.CalculateField(watermains, "SoilCorrosionValue", "3", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute("Soils_SSURGO", "NEW_SELECTION", "steelcorr = 3", None)
arcpy.management.SelectLayerByLocation(watermains, "HAVE_THEIR_CENTER_IN", "Soils_SSURGO", None, "NEW_SELECTION", "NOT_INVERT")
arcpy.management.CalculateField(watermains, "SoilCorrosionValue", "5", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "SoilCorrosionValue is null", None)
arcpy.management.CalculateField(watermains, "SoilCorrosionValue", "0", "PYTHON3", '', "TEXT")


arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)
arcpy.management.SelectLayerByAttribute("Soils_SSURGO", "CLEAR_SELECTION", '', None)
#Score the pipes based on the proximity to Highway Functional Classification
#these values exist in the excel table but do not seem to be used in the analysis and are not referenced in the report
#this is used to heighten risk of breaks for pipes in the right of way and impacts to traffic and pavement

roadcenterlines = r'https://services.arcgis.com/8O9UlSTnqjKptoda/arcgis/rest/services/RoadCenterline/FeatureServer'

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)
arcpy.management.SelectLayerByAttribute("Road Centerline", "CLEAR_SELECTION", '', None)

arcpy.management.AddField(watermains, "StreetClassValue", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.SelectLayerByAttribute("Road Centerline", "NEW_SELECTION", "FUNCTCLASS = 'PRINCIPAL ARTERIAL'", None)
arcpy.management.SelectLayerByLocation(watermains, "WITHIN_A_DISTANCE", "Road Centerline", "60 Feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.management.CalculateField(watermains, "StreetClassValue", "5", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute("Road Centerline", "NEW_SELECTION", "FUNCTCLASS in ('MINOR ARTERIAL', 'MAJOR COLLECTOR')", None)
arcpy.management.SelectLayerByLocation(watermains, "WITHIN_A_DISTANCE", "Road Centerline", "40 Feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.management.SelectLayerByAttribute(watermains, "REMOVE_FROM_SELECTION", "StreetClassValue IS NOT NULL", None)
arcpy.management.CalculateField(watermains, "StreetClassValue", "4", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute("Road Centerline", "NEW_SELECTION", "FUNCTCLASS in ('COLLECTOR', 'MINOR COLLECTOR')", None)
arcpy.management.SelectLayerByLocation(watermains, "WITHIN_A_DISTANCE", "Road Centerline", "40 Feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.management.SelectLayerByAttribute(watermains, "REMOVE_FROM_SELECTION", "StreetClassValue IS NOT NULL", None)
arcpy.management.CalculateField(watermains, "StreetClassValue", "3", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute("Road Centerline", "NEW_SELECTION", "FUNCTCLASS in ('STREET', 'OTHER RAMP')", None)
arcpy.management.SelectLayerByLocation(watermains, "WITHIN_A_DISTANCE", "Road Centerline", "40 Feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.management.SelectLayerByAttribute(watermains, "REMOVE_FROM_SELECTION", "StreetClassValue IS NOT NULL", None)
arcpy.management.CalculateField(watermains, "StreetClassValue", "2", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute("Road Centerline", "NEW_SELECTION", "FUNCTCLASS in ('PRIVATE', 'ALLEY')", None)
arcpy.management.SelectLayerByLocation(watermains, "WITHIN_A_DISTANCE", "Road Centerline", "40 Feet", "NEW_SELECTION", "NOT_INVERT")
arcpy.management.SelectLayerByAttribute(watermains, "REMOVE_FROM_SELECTION", "StreetClassValue IS NOT NULL", None)
arcpy.management.CalculateField(watermains, "StreetClassValue", "1", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "StreetClassValue IS NULL", None)
arcpy.management.CalculateField(watermains, "StreetClassValue", "0", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)
arcpy.management.SelectLayerByAttribute("Road Centerline", "CLEAR_SELECTION", '', None)
arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION")
#Assetic Life Variation and LOB variaion Concatenations SANS Corrosion values
#not very many records have a polywrap or any type of external covering value

arcpy.management.AddField(watermains, "LOB_Variation", "TEXT", None, None, 26, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateField(watermains, "LOB_Variation", 'str(!MATERIAL!)+"-"+str(int(!DIAMETER!))+"-"+str(!Install_ERA!)', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "EXTCOVERING IS NOT NULL", None)
arcpy.management.CalculateField(watermains, "LOB_Variation", 'str(!MATERIAL!)+"-Poly-"+str(int(!DIAMETER!))+"-"+str(!Install_ERA!)', "PYTHON3", '', "TEXT")

#arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "LOB_Variation IS NULL", None)
#arcpy.management.CalculateField(watermains, "LOB_Variation", 'str(!MATERIAL!)+"-Poly-"+str(int(!DIAMETER!))+"-?-"+str(!SoilCorrosionValue!)', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)


#Assetic Life Variation and LOB variaion Concatenations with soil corrosion included - dont do this any more
#not very many records have a polywrap or any type of external covering value

'''
arcpy.management.AddField(watermains, "LOB_Variation", "TEXT", None, None, 26, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateField(watermains, "LOB_Variation", 'str(!MATERIAL!)+"-"+str(int(!DIAMETER!))+"-"+str(!Install_ERA!)+"-"+str(!SoilCorrosionValue!)', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "EXTCOVERING IS NOT NULL", None)
arcpy.management.CalculateField(watermains, "LOB_Variation", 'str(!MATERIAL!)+"-Poly-"+str(int(!DIAMETER!))+"-"+str(!Install_ERA!)+"-"+str(!SoilCorrosionValue!)', "PYTHON3", '', "TEXT")

#arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "LOB_Variation IS NULL", None)
#arcpy.management.CalculateField(watermains, "LOB_Variation", 'str(!MATERIAL!)+"-Poly-"+str(int(!DIAMETER!))+"-?-"+str(!SoilCorrosionValue!)', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)

'''
# I dont think life variation ever gets used, but it is referenced in the assetic report

#arcpy.management.AddField(watermains, "Life_Variation", "TEXT", None, None, 16, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.management.CalculateField(watermains, "Life_Variation", 'str(!MATERIAL!)+"-"+str(int(!DIAMETER!))+"-"+str(!SoilCorrosionValue!)', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "EXTCOVERING IS NOT NULL", None)
arcpy.management.CalculateField(watermains, "Life_Variation", 'str(!MATERIAL!)+"-Poly-"+str(!SoilCorrosionValue!)', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "LOB_Variation IS NULL", None)
arcpy.management.CalculateField(watermains, "Life_Variation", 'str(!MATERIAL!)+"-"+str(int(!DIAMETER!))+"-?-"+str(!SoilCorrosionValue!)', "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)

#join mainbreak data to the water mains
#dissolve water mains by the calculated properties summing the waterlines that have associated main break records
#this does not tally the count of breaks per main, it just flags the closest main to the main break point from lucity 
#the count of mains that have broken can be used later to adjust the LOB
#the main break data includes many appearant duplicate records that may skew the results - 
#the main breaks per 100 feet are not counting actual main breaks, but for predicting performnance of similar pipe diameters, materials, and ages pipes for breakage statistics.  
#If one pipe has had about 5 or more repairs that not typical, for our system, of all the similar pipes of that same diameter, material, and install era.
#that pipe with 5 repairs probably needs replaced though, and that will be accounted for in a LOB_ADJ and will rank high in the final prioritization

BreakData = "Water Main Breaks"

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)

arcpy.analysis.SpatialJoin(watermains, BreakData, projdb+r"/"+watermains+"_BreakJoin", "JOIN_ONE_TO_ONE", "KEEP_ALL", '#', "CLOSEST", "100 Feet", "BreakDataDistance")

arcpy.management.Dissolve(watermains+"_BreakJoin", projdb+r"/"+watermains+"_Break_Stats", "Install_ERA;DIAMETER;MATERIAL;LOB_Variation", "Join_Count SUM;OBJECTID COUNT", "MULTI_PART", "DISSOLVE_LINES")

arcpy.analysis.Statistics(watermains+"_Break_Stats", projdb+r"/"+watermains+"_Check_Breaks", "SUM_Join_Count SUM", None)
arcpy.analysis.Statistics(watermains+"_Break_Stats", projdb+r"/"+watermains+"_Check_Length", "Shape_Length SUM", None)
arcpy.management.CalculateField(watermains+"_Check_Length", "TotalLengthMiles", "!SUM_Shape_Length!/5280", "PYTHON3", '', "Double")

#calculate breaks per 100 feet
#select segments that are less than 400 feet and set the breaks to an "average" so short lengths/small sample combinations arent overweight

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "BreaksPer100ft", "!SUM_Join_Count!/(!Shape_Length!/100)", "PYTHON3", '', "DOUBLE")
arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "Shape_Length < 400", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "BreaksPer100ft", "0.1", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "CLEAR_SELECTION", '', None)

#calculate likelihood of breaks integer value ranges
#a number of ways exist to do this, 
#use the symbology mapping in ArcPro Mapping can help statiscically evaluate the range values to use in a given analysis period and budget forecast
#The values here use are based on the jenks breaks rounded nicely
#these break values are similar to the predictor prep excel sheet
#with this code each pipe that has breaks has one break counted as noted above
#it is not weighted more heavily by individual pipes with multiple breaks - that is done on the LOB ADJ field

arcpy.management.AddField(watermains+"_Break_Stats", "LOB", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft < 0.015", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "0", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.015 AND BreaksPer100ft < 0.05", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "1", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.05 AND BreaksPer100ft < 0.1", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "2", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.1 AND BreaksPer100ft < 0.15", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "3", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.15 AND BreaksPer100ft < 0.2", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "4", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.2 AND BreaksPer100ft <= 0.5", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "5", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >0.5", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "6", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "CLEAR_SELECTION", '', None)


#calculate likelihood of breaks integer value ranges
#a number of ways exist to do this, 
#use the symbology mapping in ArcPro Mapping can help statiscically evaluate the range values to use in a given analysis period and budget forecast
#The values here use are based on the jenks breaks rounded nicely
#these break values have been adjusted based on materials corrections
#with this code each pipe that has breaks has one break counted as noted above
#it is not weighted more heavily by individual pipes with multiple breaks - that is done on the LOB ADJ field

#arcpy.management.AddField("Water_Main_Break_Stats", "LOB", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft < 0.0125", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "0", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.0125 AND BreaksPer100ft < 0.04", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "1", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.04 AND BreaksPer100ft < 0.06", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "2", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.06 AND BreaksPer100ft < 0.09", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "3", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.09 AND BreaksPer100ft < 0.15", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "4", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >= 0.15 AND BreaksPer100ft <= 0.5", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "5", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "NEW_SELECTION", "BreaksPer100ft >0.5", None)
arcpy.management.CalculateField(watermains+"_Break_Stats", "LOB", "6", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains+"_Break_Stats", "CLEAR_SELECTION", '', None)


#the likelihood of break has been statistically set for each group of pipes,
#now the summary pipe information can be joined back to the individual pipe segments for prioritization

arcpy.management.AddField(watermains, "LOB", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.AddJoin(watermains, "LOB_Variation", watermains+"_Break_Stats", "LOB_Variation", "KEEP_ALL")
arcpy.management.CalculateField(watermains, "LOB", "!"+watermains+"_Break_Stats.LOB!", "PYTHON3", '', "TEXT")
arcpy.management.RemoveJoin(watermains, watermains+"_Break_Stats")

#join main data to the closest water mains to obtain more details about the counts of breaks per water main
#the main break data contains some appearant duplicates so this needs to be re-run with recent and clean break data for accurate results
#the break results should also be filtered temporally, so that breaks dont appear to occur on pipes that have been replaced 
#add the break count per pipe to the pipe table and set the LOB Adj
#12/3/2021 field names for analysis adjusted to account for new feature service layer of breaks
#query added to remove oudated break
# note that the new break data has a field called ASSET_ID that is the non-unique facility ID
# to avoid confusion over this it the unique asset ID field has been renamed 

arcpy.management.AddField(watermains, "BreakCount", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.analysis.SpatialJoin(BreakData, watermains, projdb+r"/"+watermains+"_Join", "JOIN_ONE_TO_ONE", "KEEP_ALL", '#', "CLOSEST", "100 Feet", "NearestMainDist")
#at this point with the new feature layer the breaks data doesnt know when or if the pipe has been replaced.  

#this selection and deletion will remove those breaks from the dataset that occured before a pipe was replaced
arcpy.management.SelectLayerByAttribute(watermains+"_Join", "NEW_SELECTION", "BreakReportedDate < INSTALLDATE" , None)
arcpy.management.DeleteFeatures(watermains+"_Join")


arcpy.management.Dissolve(watermains+"_Join", projdb+r"\BreaksPerPipe", "Unique_AssetID", "Join_Count SUM;BreakReportedDate MIN;BreakReportedDate MAX;BreakReason FIRST;BreakReason LAST", "MULTI_PART", "DISSOLVE_LINES")
arcpy.management.AddJoin(watermains, "Unique_AssetID", "BreaksPerPipe", "Unique_AssetID", "KEEP_ALL")
arcpy.management.CalculateField(watermains, "BreakCount", "!BreaksPerPipe.SUM_Join_Count!", "PYTHON3", '', "TEXT")

arcpy.management.RemoveJoin(watermains, "BreaksPerPipe")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "BreakCount < 1 or BreakCount is null", None)
arcpy.management.CalculateField(watermains, "BreakCount", "0", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)
#adjust break data for querification
#the break join now brings together the fields to compare breaks and pipes for dates, materials and diameters
#interrogate this data closer to attempt to validate the correct break data joins
#based on initial diameter analysis it was pretty good

#score the LOB ADJ field

arcpy.management.AddField(watermains, "LOB_ADJ", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "BreakCount < 1 or BreakCount is null", None)
arcpy.management.CalculateField(watermains, "LOB_ADJ", "0", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "BreakCount = 1", None)
arcpy.management.CalculateField(watermains, "LOB_ADJ", "1", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "BreakCount = 2", None)
arcpy.management.CalculateField(watermains, "LOB_ADJ", "2", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "BreakCount = 3", None)
arcpy.management.CalculateField(watermains, "LOB_ADJ", "3", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "BreakCount = 4", None)
arcpy.management.CalculateField(watermains, "LOB_ADJ", "4", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "BreakCount = 5", None)
arcpy.management.CalculateField(watermains, "LOB_ADJ", "5", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "BreakCount > 5", None)
arcpy.management.CalculateField(watermains, "LOB_ADJ", "6", "PYTHON3", '', "TEXT")

arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)


#this replacement cost per linear foot per pipe diameter was estimated using a polynomial equation based on emperical statistics graphed in excel
#the emperical data included cost up to 12" and I made up guestimate numbers for higher diameter pipes that should be verified emperically
#this should calculate the replacement value, so that we can prioritize replacement of smaller pipes with 8" and changes to improve system performance bang for the buck

DIAM = 20
costLF = 1.2*DIAM*DIAM-12*DIAM+240

print(costLF)

arcpy.management.AddField(watermains, "ReplacementValue", "DOUBLE", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateField(watermains, "ReplacementValue", "(1.2*!DIAMETER!*!DIAMETER!-12*!DIAMETER!+240)*!Shape_Length!", "PYTHON3", '', "TEXT")

#for replacement scheduling and budgeting, consider replacement cost- action for replacing with 8" pvc minimum pipe sizes
#for budgeting, consider comperehensive plan and transmission main extensions along arterial and collector growth corridors for larger pipe sizes

#based on cost groups and replacement needs, model the replacement schedule per year for the next 20 or 30 years

arcpy.management.AddField(watermains, "ReplacementCost", "DOUBLE", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateField(watermains, "ReplacementCost", "(1.2*!DIAMETER!*!DIAMETER!-12*!DIAMETER!+240)*!Shape_Length!", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "DIAMETER < 8 AND DIAMETER > 2", None)
arcpy.management.CalculateField(watermains, "ReplacementCost", "(1.2*64-12*8+240)*!Shape_Length!", "PYTHON3", '', "TEXT")


#the results of this elevates 1-2" copper and poly pipes around the bulbs of cul de sacs, and other random strange things like a 1" copper waterline under the riverfront plaza building, belle crest drive, 6th & Arizona St,
#selection changed to not include pipes under 2 inch diam
#these should not be scheduled for replacement with 8" pvc, we are targeting more 6" transite, di, CI
# at some point here we need to filter out little short segments, bypasses, tower connections, but keeping stuff like the 1916 4" ci on 7th between Ohio and Tennesssee




arcpy.management.AddField(watermains, "ReplacementDif", "DOUBLE", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)
arcpy.management.CalculateField(watermains, "ReplacementDif", "0", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "DIAMETER < 8 AND Shape_Length > 250", None)
arcpy.management.CalculateField(watermains, "ReplacementDif", "(8-!DIAMETER!)", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")
arcpy.management.SelectLayerByAttribute("Water_Main_Break_Stats", "CLEAR_SELECTION", '', None)
arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)

arcpy.management.AddField(watermains, "PriorityScore", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateField(watermains, "PriorityScore", "(100-!PercentLifeRemaining!)/10+!LOB_ADJ!+!LOB!", "PYTHON3", '', "TEXT")



#these commands will clear selected features, these commands should normally be the last step run in each cell
arcpy.management.SelectLayerByAttribute("Water_Main_Break_Stats", "CLEAR_SELECTION", '', None)
arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)
#Break Threshold Calculation
#break estimate 

BreakEstimateCost = 7000
arcpy.management.AddField(watermains, "BreakRateThreshold", "Double", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateField(watermains, "BreakRateThreshold", "(math.log(1+0.05))/(math.log(1+7000/!ReplacementCost!))", "PYTHON3", '', "TEXT")


arcpy.management.AddField(watermains, "AnnualBreakRate", "Double", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.CalculateField(watermains, "AnnualBreakRate", "!BreakCount!/10", "PYTHON3", '', "TEXT")


arcpy.management.AddField(watermains, "BreakThresholdMet", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "AnnualBreakRate >= BreakRateThreshold", None)
arcpy.management.CalculateField(watermains, "BreakThresholdMet", "1", "PYTHON3", '', "TEXT")
arcpy.management.SelectLayerByAttribute(watermains, "CLEAR_SELECTION", '', None)
#evaluate the pressure gradient from hydrant flow test, assign pressure ranges 1-5 and test sensitivity to LOB

#watermain = 'Water_Main_202110'

#modify this to use Darren's Hydrant Flow Test service

StaticHg = 'waterpressure'

arcpy.sa.Sample(StaticHg, watermains, r"Sample_waterpre1", "NEAREST", "OBJECTID", "CURRENT_SLICE", None, "MEAN", None, None, "ROW_WISE", "FEATURE_CLASS")
    
#this seems to represent the values from the hydrant flow test static pressure grid "waterpressure" from the waterlines, probably other ways to do it

arcpy.management.AddField(watermains, "HG_Score", "LONG", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')
arcpy.management.AddField(watermains, "SampleHg", "DOUBLE", None, None, None, '', "NULLABLE", "NON_REQUIRED", '')

#scores entered into histogram, most pressures are between 58-85 psi
#range is about 45-120
#60-85 = 0
#>60=1
#85.1-90 = 2
#90.1-95=3
#95.1-105=4
#>105.1 = 5
arcpy.management.AddJoin(watermains, "OBJECTID", "Sample_waterpre1", "OBJECTID", "KEEP_ALL")

arcpy.management.CalculateField(watermains, watermains+".SampleHg", "!Sample_waterpre1.waterpressure!", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.RemoveJoin(watermains, "Sample_waterpre1")

arcpy.management.AddJoin(watermains, "OBJECTID", "Sample_waterpre1", "OBJECTID", "KEEP_ALL")

arcpy.management.CalculateField(watermains, watermains+".SampleHg", "!Sample_waterpre1.waterpressure!", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.RemoveJoin(watermains, "Sample_waterpre1")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "SampleHg < 60", None)
arcpy.management.CalculateField(watermains, "HG_Score", "1", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "SampleHg >= 60 And SampleHg < 85", None)
arcpy.management.CalculateField(watermains, "HG_Score", "0", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "SampleHg >= 85 And SampleHg < 90", None)
arcpy.management.CalculateField(watermains, "HG_Score", "2", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "SampleHg >= 90 And SampleHg < 95", None)
arcpy.management.CalculateField(watermains, "HG_Score", "3", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "SampleHg >= 95 And SampleHg < 105", None)
arcpy.management.CalculateField(watermains, "HG_Score", "4", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")

arcpy.management.SelectLayerByAttribute(watermains, "NEW_SELECTION", "SampleHg >= 105", None)
arcpy.management.CalculateField(watermains, "HG_Score", "5", "PYTHON3", '', "TEXT", "NO_ENFORCE_DOMAINS")<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup