Calculating frequency in Field Calculator

Question

I have a table with many instances of duplicate values.

My end goal is to have three columns:

"The Value": a file name. most are non-unique
"Duplicate Instance": "this is the 1st time this file name appears in the list", the 2nd, the 3rd, etc.
"Total count of Duplicates": "this file name appears 5 times in this list"

I have the Duplicate Instance from @FelixP's answer to Auto incrementing field based on groups within feature class?

I can create a separate frequency table with the Frequency tool then join that table to calculate in the total count of each file name, but I would love to skip a few steps and directly calculate the frequency in the Field Calculator interface.

Is it possible to directly calculate frequency in ArcGIS Desktop Field Calculator?

Does it have to be Field Calculator or can it be with code in python window? — BERA, Sep 04 '19 at 12:52
good point for clarification. I'd like to keep it set it up for use in the Field Calculator's "Pre-Logic Script Code" interface. — Zipper1365, Sep 04 '19 at 13:01
I doubt it is possible. You will have to iterate over / field calculate all rows twice and somehow save the results from first iteration and use in second. Using python window is it easier — BERA, Sep 04 '19 at 13:05
Yeah, I thought that might be the case - that any solution would need to but be unable to iterate through again. Still hopeful that someone might have some field calculator magic to share, though! — Zipper1365, Sep 04 '19 at 13:28
Similarly to https://gis.stackexchange.com/questions/193681/calculating-sequential-numbers-into-sorted-table-using-arcgis-desktop/193684#193684 you can go twice, however result goes to 1 field, you need 2 of them. So - script or 2 field calculations. — FelixIP, Sep 04 '19 at 18:56

score 4 · Answer 1 · edited Sep 04 '19 at 21:55

If you don't find a solution using Field Calculator try python window with collections.Counter and two da.UpdateCursors:

import arcpy
from collections import Counter

fc = 'C:\data.gdb\my_riks' #Change
values = 'KKOD' #Change
dupinst = 'dupinst' #Change
totcount = 'totcount' #Change

cnt = Counter()

with arcpy.da.UpdateCursor(fc, [values, dupinst]) as cursor:
    for row in cursor:
        cnt[row[0]] += 1
        row[1] = cnt[row[0]]
        cursor.updateRow(row)

with arcpy.da.UpdateCursor(fc, [values, totcount]) as cursor:
    for row in cursor:
        row[1] = cnt[row[0]]
        cursor.updateRow(row)

score 3 · Answer 2 · answered Sep 04 '19 at 13:30

Field Calculator only iterates once over the table so you cannot generate a frequency list and apply it with one pass. If this is a task you need to repeat I'd recommend using a python script outside Field Calculator or creating a model in ModelBuilder.

Calculating frequency in Field Calculator

2 Answers2