Selecting maximum Value based on other field using ArcPy

2228
13
Jump to solution
06-27-2021 08:28 AM
badrinathkar
New Contributor III

I want to populate a field (MaxElv) selecting the highest value from another field (Elevation) based on the Id field (Id). The data looks like this:

Id   Elevation   MaxElv
1     50      
1 72
1 83
1 66
2 105
3 27
3 24
2 98
1 46
3 22
2     96
3 26
2 99
4 25
4 21
4 22
3 28
1 52

I have develop the following code:
>>> import arcpy
... fc = 'Country'
... CountryList = []
... with arcpy.da.SearchCursor(fc, ["Id"]) as cur1:
... for row1 in cur1:
... if row1[0] not in CountryList:
... CountryList.append(row1[0])
...
... for i in CountryList:
... ElevList = []
... where = """{} = {}""".format("Id", i)
... with arcpy.da.SearchCursor(fc, ["Elevation"], where) as cur2:
... for row2 in cur2:
... if row2[0] not in ElevList:
... ElevList.append(row2[0])
... ElevList.sort()
... max_Elev= ElevList[-1]
... where2 = """{} = '{}' AND {} = {}""".format("Id", i, "Elevation", max_Elev)
... with arcpy.da.UpdateCursor(fc, ["MaxElv"], where2) as cur3:
... for row3 in cur3:
... row3[0] = 1
... cur3.updateRow(row3)


But it gives the following error
Runtime error
>>> import arcpy
... fc = 'Country'
... CountryList = [] 
... with arcpy.da.SearchCursor(fc, ["Id"]) as cur1:
... for row1 in cur1:
... if row1[0] not in CountryList:
... CountryList.append(row1[0])
... 
... for i in CountryList:
... ElevList = []
... where = """{} = {}""".format("Id", i)
... with arcpy.da.SearchCursor(fc, ["Elevation"], where) as cur2:
... for row2 in cur2:
... if row2[0] not in ElevList:
... ElevList.append(row2[0])
... ElevList.sort()
... max_Elev= ElevList[-1]
... where2 = """{} = '{}' AND {} = {}""".format("Id", i, "Elevation", max_Elev)
... with arcpy.da.UpdateCursor(fc, ["MaxElv"], where2) as cur3:
... for row3 in cur3:
... row3[0] = 1
... cur3.updateRow(row3)


But it gives the following error
Runtime error 
Traceback (most recent call last):
File "<string>", line 20, in <module>
RuntimeError: An invalid SQL statement was used.

Traceback (most recent call last):
File "<string>", line 20, in <module>
RuntimeError: An invalid SQL statement was used.

 

0 Kudos
13 Replies
badrinathkar
New Contributor III

Sir, I don't find any option to delete the post. please guide me.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I am not sure what is going on because the "Pro" cross-post is not redirecting to this one.  Maybe someone merged the two already.

0 Kudos
DanPatterson
MVP Esteemed Contributor

I merged them


... sort of retired...
0 Kudos
DanPatterson
MVP Esteemed Contributor

Summary Statistics (Analysis)—ArcGIS Pro | Documentation

Id is your case field

Statistics field is Elevation with Maximum as the option (or whatever)

skip the maxelev stuff, just join the resultant table back if you need to and persist the join

 


... sort of retired...