Select attribute based on minimum value of a different attribute

168
5
Jump to solution
06-27-2022 01:36 AM
Labels (3)
WarwickPrewer
New Contributor II

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

 

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
MVP Regular Contributor

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

 


Have a great day!
Johannes

View solution in original post

0 Kudos
5 Replies
JohannesLindner
MVP Regular Contributor

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

 


Have a great day!
Johannes
0 Kudos
WarwickPrewer
New Contributor II

Thanks Johannes

Warwick

0 Kudos
JohannesLindner
MVP Regular Contributor

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)

Have a great day!
Johannes
0 Kudos
DanLee
by Esri Regular Contributor
Esri Regular Contributor

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.

0 Kudos
WarwickPrewer
New Contributor II

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

0 Kudos