idea Calculate Field using sequential numbers, partition by GROUP_ID field, order by VALUES field in ArcGIS Pro Ideas

Calculate Field using sequential numbers, partition by GROUP_ID field, order by VALUES field

Bud — Fri, 27 Sep 2024 22:34:51 GMT

ArcGIS Pro 3.3.1; file geodatabase

I want to use the field calculator to populate a new/blank field called ROW_NUM.

For each group, using sequential numbers starting at 1, ordered by the VALUES field (descending)…populate the ROW_NUM field.

GROUP_ID VALUES ROW_NUM
1        0      2
1        1      1
1        0      3

2        0      1
2        0      2

3        1      1
3        0      3
3        1      2

It is my understanding that can’t currently be done using the Calculate Field tool (Python). Could that functionality be added?

If it were an Oracle SQL query, it would look like this:

select
    GROUP_ID,
    VALUES,
    row_number() over (partition by GROUP_ID order by VALUES desc) as rn
from
    MY_TABLE

Re: Calculate Field with sequential numbers, partition by GROUP_ID field, order by VALUES field

Bud — Thu, 22 Aug 2024 18:13:33 GMT

ROW_NUMBER function in FGDB SQL

Re: Calculate Field using sequential numbers, partition by GROUP_ID field, order by VALUES field

JonathanNeal — Thu, 22 Aug 2024 18:41:55 GMT

@Bud What do you think about using this altered Sequential number?
Calculate Field (Data Management)—ArcGIS Pro | Documentation

# Calculates a sequential number # More calculator examples at esriurl.com/CalculatorExamples rec={} def SequentialNumberByGroup(group): global rec pStart = 1 pInterval = 1 if group in rec: rec[group] = rec[group] + pInterval else: rec[group] = pStart return rec[group]

Re: Calculate Field using sequential numbers, partition by GROUP_ID field, order by VALUES field

Bud — Fri, 23 Aug 2024 01:42:54 GMT

@JonathanNeal Thanks. At first glance, that script doesn't seem to order the sequential numbers by the VALUES field (descending), which is a requirement.

Re: Calculate Field using sequential numbers, partition by GROUP_ID field, order by VALUES field

JonathanNeal — Fri, 23 Aug 2024 17:13:03 GMT

@Bud If sorting is a requirement, then the Sort tool will need to be ran first. Interesting idea though, to force processing order with the Calculate Field tool.