topic Re: Selecting maximum Value based on other field using ArcPy in ArcMap Questions

Selecting maximum Value based on other field using ArcPy

badrinathkar — Sun, 27 Jun 2021 16:45:04 GMT

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.

Selecting maximum Value based on other field using ArcMap?

badrinathkar — Sun, 27 Jun 2021 15:22:02 GMT

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.

Re: Selecting maximum Value based on other field using ArcPy

JayantaPoddar — Sun, 27 Jun 2021 15:34:44 GMT

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

Re: Selecting maximum Value based on other field using ArcPy

JoshuaBixby — Sun, 27 Jun 2021 16:08:27 GMT

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

Re: Selecting maximum Value based on other field using ArcMap?

JoshuaBixby — Sun, 27 Jun 2021 16:42:22 GMT

Question cross posted: https://community.esri.com/t5/arcmap-questions/selecting-maximum-value-based-on-other-field-using-arcpy/m-p/1072990. @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

Re: Selecting maximum Value based on other field using ArcPy

badrinathkar — Sun, 27 Jun 2021 16:46:04 GMT

Sir, I have edited the post. Please see.

Re: Selecting maximum Value based on other field using ArcPy

badrinathkar — Sun, 27 Jun 2021 17:28:14 GMT

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.

Re: Selecting maximum Value based on other field using ArcPy

JayantaPoddar — Sun, 27 Jun 2021 17:36:13 GMT

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)?

Re: Selecting maximum Value based on other field using ArcPy

badrinathkar — Sun, 27 Jun 2021 18:19:58 GMT

Sir, please see the attached figure.

Re: Selecting maximum Value based on other field using ArcMap?

badrinathkar — Sun, 27 Jun 2021 18:32:05 GMT

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

Re: Selecting maximum Value based on other field using ArcPy

DanPatterson — Sun, 27 Jun 2021 18:37:41 GMT

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

Re: Selecting maximum Value based on other field using ArcMap?

JoshuaBixby — Sun, 27 Jun 2021 19:30:42 GMT

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.

Re: Selecting maximum Value based on other field using ArcPy

JoshuaBixby — Sun, 27 Jun 2021 19:36:07 GMT

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.

Re: Selecting maximum Value based on other field using ArcMap?

DanPatterson — Sun, 27 Jun 2021 21:18:48 GMT

I merged them