# Calculating total numbers of wells that fall into each county of table using ArcPy?

Directions: For each county calculate the total numbers of wells that fall into that county, and update the county table with the total count. This is the code I have so far.

Problems:

1. The counties selected only returns 1, so I know there is an error somewhere.
2. Not sure if things are in order and I am not sure if the intersect of counties and wells and the total count even happened.

``````import arcpy

#variables
CountyPath = "C:\\PythonProgramming\\Lesson 5\\Lesson5_Data\\COUNTIES.shp"
WellsPath = "C:\\PythonProgramming\\Lesson 5\\Lesson5_Data\\Wells.shp"
fieldList = "COUNTY"

# select single counties
AllCounties = [] # blank list
with arcpy.da.SearchCursor(CountyPath,'COUNTY') as cursor:
for row in cursor: # go through each feature one by one
if row[0].upper() not in AllCounties: # check it's not already in the list
AllCounties.append(row[0].upper())

for COname in AllCounties:
where_clause = "upper(COUNTY) = '" + COname + "'"
print where_clause

arcpy.MakeFeatureLayer_management (CountyPath, "county_lyr", where_clause)
arcpy.MakeFeatureLayer_management (WellsPath, "wells_lyr")

#use update cursor to calculate the total numbers of wells that fall into that county
with arcpy.da.UpdateCursor(CountyPath, fieldList, where_clause) as cursor:
for row in cursor:
row[0] = row[0].upper()
cursor.updateRow(row)

#select statement
arcpy.SelectLayerByLocation_management("wells_lyr", "INTERSECT", "county_lyr", "", "NEW_SELECTION")
``````

I am new to this.

• Your SelectLayerByLocation needs to be earlier, and your update cursor is only performing an upper case replacement of your counties. – Michael Stimson Jul 13 '18 at 1:46
• Have you considered using a spatial join? Much simpler! You could pretty much condense your script down to just a few lines (spatial join and then join field). – user2856 Jul 13 '18 at 3:06
• I agree with @Luke. This is a textbook case for a point in polygon spatial join. – Fezter Jul 13 '18 at 3:52
• I recommend not thinking about GIS SE as being an online GIS tutor. Since your question involves ArcPy code it is worth reviewing these tips for writing code snippets. – PolyGeo Jul 14 '18 at 23:24

Continuing from where your previous question left off:

``````for COname in AllCounties:
where_clause = "upper(COUNTY) = '" + COname + "'"
print where_clause

arcpy.MakeFeatureLayer_management (CountyPath, "county_lyr", where_clause) # Just one county
arcpy.MakeFeatureLayer_management (WellsPath, "wells_lyr")                 # all wells

# reduce all wells to the ones inside the county
arcpy.SelectLayerByLocation_management("wells_lyr", "INTERSECT", "county_lyr", "", "NEW_SELECTION")
# find the count of wells selected
CountOfWells = int( arcpy.GetCount_management("wells_lyr").getOutput(0) )
# print a message identifying the county and the count of wells

# don't use update cursor to calculate the total numbers of wells that fall into that county
# arcpy.GetCount_management works just fine. Use this to update your count in this county field
with arcpy.da.UpdateCursor("wells_lyr", "CountThisCounty") as cursor:
for row in cursor:
row[0] = CountOfWells
cursor.updateRow(row)
``````

The process works like this:

1. Create a layer that contains just one county.
2. Select from the wells the ones that intersect that county.
3. Get the count of wells in that county (I think that's what you're after).
4. Using an update cursor on the wells set the values and store.
• Thank you, I am going to work through this right now and see what I get. I really appreciate it. – Amanda Young Jul 13 '18 at 2:24
• All is good so far, the list of counties print line by line. However, I get this message: Traceback (most recent call last): File "C:/PythonProgramming/Lesson 5/AYoung_Homework5.py", line 30, in <module> for row in cursor: RuntimeError: A column was specified that does not exist. (I need to update the COUNTY table with the column called NoWells... not sure if this is the information I need for that parameter? – Amanda Young Jul 13 '18 at 2:58
• with arcpy.da.UpdateCursor("wells_lyr", "CountThisCounty") as cursor: for row in cursor: #this is the line I am getting the error row[0] = CountOfWells cursor.updateRow(row) – Amanda Young Jul 13 '18 at 3:06
• Substitute the real field name from your wells feature class for "CountThisCounty"; the field in the wells wasn't mentioned in your script so I just made that up as a place marker. – Michael Stimson Jul 13 '18 at 6:35