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