Doing COUNTIFS equivalent in ArcGIS Field Calculator?

Question

I have an attribute table with location data for a series of individual animals. They are often found at the same locations, so I have created a column in my attribute table where I list the rock ID. The Rock Id counts are repeated for each animal in order to keep data collection simple in the field, so rock 1 for animal 1 is not the same as rock 1 for animal 2. (In any instance where two animals used the same rock - I combined the rock id numbering system for their rocks, but this was rare.) I would like to get a count for the number of times an animal is found at each rock, so I can use symbology on my map that varies in size based on this attribute. (More times an animal uses a rock, larger the symbol).

I have a data set that looks something like this and I want to populate rock count to look something like what I have below

I know I can use CountIFS in excel - and then potentially import this in, but Ideally I could calculate this for the data already in ArcGIS using code and the field calculator. The summarize tool works well for telling me the unique totals with two case fields- but I would like to replicate this value in each cell. The output of that table is smaller than my original, since obviously there are some repeats.

I don't think I can do a join from this summary table, because each possible joining attribute (Rock_ID, Animal_ID) has more than one row in both tables.

Answer 1

You can use python with Collections.Counter library to Count unique combinations of Animal_ID & Rock_ID:

A Counter is a dict subclass for counting hashable objects.

It is possible to do this using Field Calculator but I recommend using cursors instead. Adjust input fc and field names and execute in Python Window:

import arcpy
from collections import Counter

fc = 'animals' #In the example screenshot a layer. Can also be a shapefile or fc in database, for example fc = r'C:\data.gdb\animals'

c = Counter()

#Store counts in dictionary
with arcpy.da.SearchCursor(fc,  ['Animal_ID','Rock_ID']) as cursor:
    for row in cursor:
        c[row]+=1

#Update count column
with arcpy.da.UpdateCursor(fc,['Animal_ID','Rock_ID','Rock_Count']) as cursor:
    for row in cursor:
        row[2] = c[tuple(row[:2])]
        cursor.updateRow(row)

Example in ArcGIS Pro, it is the same in ArcMap:

Answer 2

You need a field that uniquely identifies each combination of animal and rock. Then you can use the Summary tool, and join the summary table back to the original table based on that unique field. Then you can calculate the count field based on the joined summary data:

• Create a new field, such as "Animal_Rock_ID" and calculate its value as a concatenation of 'Animal_ID' and 'Rock_ID' (with an underscore between the two, to make sure that a 11 and 1 as well as a 1 and 11 don't end up both being the same "111", but would be "11_1" and "1_11" instead). One way to do this in Python would be: "{}_{}".format(!Animal_ID!, !Rock_ID!)

• Run the summary tool.

• Add a join on your original feature class to the summary table, based on the 'Animal_Rock_ID' field in both.

• Calculate the field values in your original table from the joined summary field.

• Remove the join.

• Optionally, delete the 'Animal_Rock_ID' field (if you have no further or future use for it).