7

Is there a way of calculating the number of unique text strings in a field in ArcGIS 10.1? I have a number of repeated strings in this field, but would like to calculate the number of unique strings in a field based on groupings from another field.

E.g - Current table:

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

Result:

Field 1    Field 2
Apple      2
Citrus     3
2
  • Your first sentence is called "summarizing" the field and it is available directly from the menu. But what do you mean by "based on groupings from another field"? There is no evident relationship between the "returns" and the "current table" in your example. – whuber Sep 13 '13 at 19:20
  • 1
    For the Apple category, there are 2 different text values, green and red. For the citrus category there are 3, yellow green and orange. – Joules Sep 13 '13 at 19:32
12

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
1
  • 1
    I have done a summary this way before, definitely a good approach. – rachel.passer Dec 16 '15 at 18:18
5

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.

1
  • 1
    From what I can see, summary statistics only deals with numeric fields, I'm looking for string statistics. – Joules Sep 13 '13 at 19:35
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.