1

I'm trying to find a way to return all records that have the highest value in a field for each set of unique IDs. The attached table shows what I'm trying to achieve. The highlighted records would be the ones I want to have selected so I can export them to a new table.

For each set of unique IDs (Int_ID) I want to select those that have the highest value in (MPO_Lanes). I need to keep all the attributes, so Case items in Summary Statistics does not appear to work.

If your curious why I want to do this...for each Street Intersection (Int_ID), I want to identify which Street Legs (ExtID) have the most lanes (MPO_Lanes) and therefore identifying which Street Legs would be considered the "Major" street at that intersection.

I'm using ArcGIS 10.4.1, advanced.

enter image description here

  • What sort of database/feature source are your features in? If you're using personal (not file) geodatabase or SDE you can use the MAX operator gis.stackexchange.com/questions/24163/… – Michael Stimson Jun 28 '17 at 20:33
  • I might be missing something, but I don't think the Max operator will work because I need the max determined within the groups of identical INT_IDs. I posted a better example of the table. Note MPO_Lanes = 3 is selected for INT_ID 2681 but MPO_Lanes = 2 is selected for INT_ID 2934. – Mercator Jun 28 '17 at 21:35
  • BTW, the data is in a file GDB, but could be moved to SDE or personal GDB. – Mercator Jun 28 '17 at 21:36
  • 3
    You could create a summary table with Summary Statistics resources.arcgis.com/en/help/main/10.2/index.html#//… with a case field of Int_ID and summary field of MPO_Lanes with the MAXIMUM statistics type then join by Int_ID and select where SumTable.MPO_Lanes_Maximum = MPO_Lanes (field names are assumed but after the join you should see fairly clearly in the select by attributes dialog). – Michael Stimson Jun 28 '17 at 21:51
  • Looks like a task for Summary Statistics – Vince Jun 28 '17 at 22:03
1

The following arcpy script will accomplish what you want. This can be added to a script tool and has 5 parameters:

import arcpy

infc = arcpy.GetParameter(0) # Input feature class
infield = arcpy.GetParameterAsText(1)  # Group By Field
stcountfield = arcpy.GetParameterAsText(2)  # Street Count Field
streetname = arcpy.GetParameterAsText(3)  # Street Name or ID field
primaryst = arcpy.GetParameterAsText(4)  #  New field to hold Primary Street name or id

def maxkey(dict):
    keylist = []
    for key in dict.iterkeys():
        keylist.append(key)
    return max(keylist)

# Create set from group-by field values
fieldvals = []      
with arcpy.da.SearchCursor(infc,[infield]) as scur:
    for row in scur:
        fieldvals.append(row[0])
del scur
fieldset = []
for field in set(fieldvals):
    fieldset.append(field)

# Create dictionary of max street count = street name, update records with primary street per group-by value

for val in fieldset:
    maxvaluedict = {}
    primstdict = {}
    expression = infield+"="+str(val)
    arcpy.SelectLayerByAttribute_management(infc,"NEW_SELECTION",expression)
    with arcpy.da.SearchCursor(infc,[infield,stcountfield,streetname]) as cur:
        for row in cur:
            if row[1] not in maxvaluedict:
                maxvaluedict[row[1]] = [row[0]]
            elif row[0] not in maxvaluedict[row[1]]:
                maxvaluedict[row[1]].append(row[0])
            if row[1] not in primstdict:
                primstdict[row[1]] = [row[2]]
            elif row[2] not in primstdict[row[1]]:
                primstdict[row[1]].append(row[2])
    del cur

    with arcpy.da.UpdateCursor(infc,[infield,primaryst]) as ucur:
        for row in ucur:
            row[0] = val
            elist = []
            for e in primstdict[maxkey(maxvaluedict)]:
                elist.append(str(e))
            row[1] = ','.join(elist)
            ucur.updateRow(row)
    del ucur

For an example, I've run this on my roads table and set

infield = "LOC"

stcountfield = "PAR_NUM"

streetname = "FEAT_NAME"

primaryst = "PrimaryStreet"

As you can see, PrimaryStreet was populated with the street name of the largest PAR_NUM, grouped by LOC.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.