How to count occurrences of one field grouped by values of another?

Two "summarize" operations will do it. This is a basic operation requiring no extra licenses.

First compute a field that concatenates Field1 and Field2. (If your table is not editable or should not be modified, do these operations on a copy of it.) It's a good idea to delimit the concatenation; here I have used "|" as a delimiter.

Field 1    Field 2    Concatenation
Apple      Green      Apple|Green
Apple      Red        Apple|Red
Apple      Red        Apple|Red
Citrus     Yellow     Citrus|Yellow
Citrus     Green      Citrus|Green
Citrus     Orange     Citrus|Orange
Citrus     Orange     Citrus|Orange

The summary of the Concatenation field, retaining the first occurrence of Field1, will look like

Concatenation  Field1  Count
Apple|Green    Apple   1
Apple|Red      Apple   2
Citrus|Yellow  Citrus  1
Citrus|Green   Citrus  1
Citrus|Orange  Citrus  2

Finally, the summary of Field1 obtains what you want:

Field1 Count
Apple  2
Citrus 3

You probably want to use either the "Summary Statistics" tool (ArcView/Basic license and above)

Calculates summary statistics for field(s) in a table.

or "Frequency" tool (ArcInfo/Advanced license required)

Reads a table and a set of fields and creates a new table containing unique field values and the number of occurrences of each unique field value.

You could then join this back to your source table and calculate a field (if needed), or just use the Join Field tool (the example in Help shows the exact workflow you are talking about).

Joins the contents of a table to another table based on a common attribute field.


You could do this by using a dictionary to keep track of values in field 1 along with the number of combinations each value has with field 2.

import arcpy,csv

fields = ['Field 1','Field 2']
with arcpy.da.SearchCursor(table,fields) as rows:
    with open('output_table.csv','wb') as f_out:
        dw = csv.DictWriter(f_out,fields)
        dw.writeheader()
        d = {}
        for row in rows:
            field1_val = row[0]
            field2_val = row[1]
            if field1_val not in d.keys():
                # If this is the first time encountering the variable, add it to the dictionary and create
                # a new list with the associated value in field 2 as the first item in said list
                d[field1_val] = [field2_val]
            else:
                # If the variable has already been encountered, try adding the value in field 2 to the list
                # unless it is already in there
                if field2_val not in d[field1_val]:
                    d[field1_val].append(field2_val)
        # Now that we have assembled a dictionary containing all variables in field 1 and lists of the associated
        # values found in field 2, we can iterate through the variable/list pairs and write to a csv file
        for k,v in d.iteritems():
            dw.writerow(dict(k,len(v)))