Select attribute based on minimum value of a different attribute

[WarwickPrewer]

I'm working in ArcGISPro 2.9.0 and have a table with 5 fields: ObjectID, In_FID, Dist, Frequency, Days. There are identical values for In_FID (e.g. ten 2s, four 3s, etc) and for each there is a unique number of Days. I need to extract out only the rows which have the lowest Days for each unique In_FID, show these 2 fields and the Dist column. Any ideas please?

I'm also trying it in Python.

Thanks

Warwick

[JohannesLindner · Accepted solution]

With Python:

• backup your table
• copy/paste the script below, change the table variable, run

# read table
fields = ["In_FID", "Days"]
table = "table_path_or_layer_name"
table_data = [row for row in arcpy.da.SearchCursor(table, fields)]

# create a dictionary {fid: min(days)}
fids = set([td[0] for td in table_data])  # unique fids
min_days = dict()
for fid in fids:
    days = [td[1] for td in table_data if td[0] == fid]
    min_days[fid] = min(days)

# delete all rows where Days > min(Days[fid])
with arcpy.da.UpdateCursor(table, fields) as cursor:
    for fid, days in cursor:
        if days > min_days[fid]:
            cursor.deleteRow()

[WarwickPrewer]

Thanks Johannes

Warwick

[JohannesLindner]

With Arcade:

• Create a new integer field
• Calculate the field, change language to Arcade, use the script below
• The rows you want have IntegerField = 1. You can use that to eg
  • Select Layer By Attribute
  • Query definition
  • Delete all other rows

var fid = $feature.In_FID
var min_days = Min(Filter($featureset, "In_FID = @fid"), "Days")
return IIF($feature.Days == min_days, 1, 0)

[DanLee]

Using existing tools:

1. Run Summary Statistics on Days with Minimum option, and specify In_FID as the Case Field.

2. Use Add Join to join the output table with the original table via In_FID or run Join Field to transfer MIN_DAYS via In_FID to the input.  Now you should be able to make that selection.

[WarwickPrewer]

Thanks DanLee. I tried this but it didn't seem to work. I've sorted it out now. Thanks. Warwick