Hello everyone,
I have a layer with:
*LINK ID
*Road_Point_ID
*House Number
*Lat
*Lon
I want to take the Max and Min House Number of each Link ID, but I could not found the correct way to do this; I tried with Summary Statitics tool, although if give me the information I require, it removes the Lon and Lat values and those values are necessary; I will try with the Select by Attributes tool, using a SQL sentence: "HOUSE_NUMBER = (SELECT MIN(HOUSE_NUMBER) FROM name_table", but this only select the max or min of the all table and not of each Link ID;
Do you have any idea to solve this question?
Thank you so much for your support;
Regards.
Solved! Go to Solution.
There are several ways this can be addressed. If the feature class is already loaded into a map, and thus has a corresponding layer, I think the easiest approach is to iterate over the feature class finding the OIDs that correspond to the max and min values you want, then you can select those OIDs and export the selection to a new feature class.
from collections import defaultdict
lyr_name = "minmax_example"
max_values = defaultdict(lambda: (None,-math.inf))
min_values = defaultdict(lambda: (None,math.inf))
with arcpy.da.SearchCursor(lyr_name, ["OID@", "LINK_ID", "HOUSE_NUMBER"]) as cur:
for oid, lid, hn in cur:
max_values[lid] = max(max_values[lid],(oid,hn), key=lambda x: x[1])
min_values[lid] = min(min_values[lid],(oid,hn), key=lambda x: x[1])
max_oids = [oid for oid,_ in max_values.values()]
min_oids = [oid for oid,_ in min_values.values()]
lyr = arcpy.mp.ArcGISProject("CURRENT").activeMap.listLayers(lyr_name)[0]
lyr.setSelectionSet(max_oids + min_oids)
Why not join your result table with the input table to append the info you need? Alternatively try Excel with pivot tables.
Hello L R,
I tried that but I can't figure out how to join the information correctly, what value can I use from one table and the other that matches correctly? I mean the only two values that match, are the Link_ID and House Number values, but the problem is:
-If I select to join based on LINK_ID the original table has several equal values of Link_ID
-If I select to join based on House Number, there are several numbers that are the same
It causes me to join Lat/Lon incorrectly.
I want to take the Max and Min House Number of each Link ID
So after you get the Max and Min house number, what do you want to do with it or where do you want to store it? Do you want the information in a new table, or do you want to update the existing table?
Hello Joshua,
I will try to generate a new layer with only the max and min values of each LINK ID, for that reason I need the Lat/Lon values.
There are several ways this can be addressed. If the feature class is already loaded into a map, and thus has a corresponding layer, I think the easiest approach is to iterate over the feature class finding the OIDs that correspond to the max and min values you want, then you can select those OIDs and export the selection to a new feature class.
from collections import defaultdict
lyr_name = "minmax_example"
max_values = defaultdict(lambda: (None,-math.inf))
min_values = defaultdict(lambda: (None,math.inf))
with arcpy.da.SearchCursor(lyr_name, ["OID@", "LINK_ID", "HOUSE_NUMBER"]) as cur:
for oid, lid, hn in cur:
max_values[lid] = max(max_values[lid],(oid,hn), key=lambda x: x[1])
min_values[lid] = min(min_values[lid],(oid,hn), key=lambda x: x[1])
max_oids = [oid for oid,_ in max_values.values()]
min_oids = [oid for oid,_ in min_values.values()]
lyr = arcpy.mp.ArcGISProject("CURRENT").activeMap.listLayers(lyr_name)[0]
lyr.setSelectionSet(max_oids + min_oids)
Hello Joshua,
That was awesome, it works!!!.
Just one final question, the HOUSE_NUMBER field is data type "Text" and the other fields are "Numeric"; so when i run the scrip an error message appears that said "TypeError: '>' not supported between instances of 'str' and 'float'"; I did a research about this and I found the problem is because the fields are not equal. I said before the scrip works because I did a test with all the fields as Data Type "Numeric" and is working fine but do you know if something can do to avoid that error?;
Thank you so much for your support.
Try changing the key argument on lines 9 & 10 to:
key=lambda x: float(x[1])
Thanks!!! Issue was solved!!!
Have a great day!