Selecting maximum Value based on other field using ArcPy

1117
13
Jump to solution
06-27-2021 08:28 AM
Labels (3)
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
1 Solution

Accepted Solutions
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...

View solution in original post

13 Replies
JayantaPoddar
MVP Esteemed Contributor

It would help if you could add the Python script using "Insert/Edit Code Sample" option.

JayantaPoddar_0-1624807999388.png

 



Think Location
0 Kudos
badrinathkar
New Contributor III

Sir, I have edited the post. Please see.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Questions along these lines get asked fairly regularly on the Esri Community, probably every 4-6 months.  There is no singular way to solve the question, but some ways are much more efficient than others.  In your case, the approach is one of the less efficient ones, so I won't discuss your invalid SQL statement but suggest a different approach using SQL sorting.

 

lyr = # name of layer or path to data set
flds = ["Id", "Elevation", "MaxElv"]
sql = "ORDER BY Id, Elevation DESC"

with arcpy.da.UpdateCursor(lyr, flds, sql_clause=(None, sql)) as cur:
    eid, elev, max_elev = next(cur)
    max_elev = elev
    cur.updateRow([eid, elev, max_elev])
    prev_eid = eid
    
    for eid, elev, _ in cur:
        if eid != prev_eid:
            max_elev = elev
        cur.updateRow([eid, elev, max_elev])
        prev_eid = eid

 

badrinathkar
New Contributor III

Sorry to say! But your script populates the MaxElv field only with the value of Elevation with which it strikes first for the same Ids.

0 Kudos
JayantaPoddar
MVP Esteemed Contributor
sql = "ORDER BY Id, Elevation DESC"

 

I feel this line does the trick. The first Elevation Value for each ID is the Max_Elev value.

Joshua's script works perfectly for me.

What results/anomalies are you getting (screenshot)? 



Think Location
badrinathkar
New Contributor III

Sir, please see the attached figure.

Error1.png

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

I see you have an "FID" field, I am assuming you are working with shape files.  If you consult the documentation, UpdateCursor—ArcMap | Documentation (arcgis.com)

DISTINCT, ORDER BY, and ALL are only supported when working with databases. They are not supported by other data sources (such as dBASE or INFO tables).

In short, my approach won't work for you because you are working with shape files.

0 Kudos
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
Traceback (most recent call last):
File "<string>", line 20, in <module>
RuntimeError: An invalid SQL statement was used.

 

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Question cross posted:  https://community.esri.com/t5/arcmap-questions/selecting-maximum-value-based-on-other-field-using-ar...@badrinathkar , I suggest deleting this one since people have started responding to the other one.  In the future, Python-related questions that are not specific to ArcMap or Pro can be asked https://community.esri.com/t5/python/ct-p/python

0 Kudos