unique table values in arcgis

8948
16
08-23-2014 08:30 AM
LukeNewman
New Contributor

Hi, I was wondering if there was a way to obtain unique field values for an attribute table for ModelBuilder in ArcMap 10.1. I haven't got a license for a Frequency Analysis. Thanks.

0 Kudos
16 Replies
RichardFairhurst
MVP Honored Contributor

Actually, I consider Summary Statistics the more powerful tool than Frequency, despite the fact that it is available for all license levels.  You can choose one field for the unique case values or choose many fields to generate multi-field unique case values, or no unique case field at all (Frequency requires at least one field for unique case values).  The tool lets you actually reorder fields in the unique case fields or the summary fields (Frequency does not) and is the only tool in all of ArcGIS I have found that does that.

You can actually get most summary type values beyond First and Last for strings like Min, Max and Count (unlike the Summary you can do from the context menu in a table view), and all summary types for dates (if you type the date field name into the summary input text box and ignore the warning beep).  Dates are converted to numeric values, which will convert back to date values when you calculate them back into a standard date field.  Frequency does not let you get summary values for strings and dates at all.  The Frequency tool only supports Sum operations on numeric fields and no other summary types.

LukeNewman
New Contributor

summary statistics doesn't work for what i'm doing because the values in the table that i am trying to categorize are not numeric, the field in which i need the unique values contain different types of points of interest in Central London such as automotive, eating/drinking, tourism etc and I need to create a new table which displays a tally of each point of interest category. Thanks anyway.

0 Kudos
IanMurray
Frequent Contributor

Not sure of a way to incorporate it into model builder, but with python and cursors it would be relatively simple to return the counts of series of unique text field values.  Also would be simple to export the results out to a csv.  I can post some starter code for you if you need.

0 Kudos
RichardFairhurst
MVP Honored Contributor

Luke Newman wrote:

summary statistics doesn't work for what i'm doing because the values in the table that i am trying to categorize are not numeric, the field in which i need the unique values contain different types of points of interest in Central London such as automotive, eating/drinking, tourism etc and I need to create a new table which displays a tally of each point of interest category. Thanks anyway.

The tool does exactly what you need, based on what you have described.  A Frequency field counting all instances of a unique case value in the source table is always included in every record of the output and all field types (numeric, text or date) can be used as unique case fields.  Text fields also can be used as summary fields if you want the Min, Max, First, Last or Count statistics for the text values.  I use text fields in summaries all the time to get unique street name lists or summaries of text maintenance categories. The toolbox version is much better than the Table View context menu version you are probably thinking of, but even that version works to generate a count field for each unique text field value in a table list.

If you need this field combined with another field you can used the Summary Statistics tool to create a multi-field unique key value list with counts.  This normally applies where you have broad categories in one field with more detailed subcategories in another field that could be repeated under more than one broad category.  Multi-field keys cannot be joined back to another table using a Standard Join unless you concatenate the multi-field values into a new field in both the source and output.  However, the Make Table Query tool can create multi-field joins and a Python cursor routine can also do multi-field matching.

0 Kudos
DanPatterson_Retired
MVP Emeritus

If you are fairly adept at stealing from python code, I have snippets for determining various frequency distributions in the FieldStatistics Toolbox which you can use.  I have never used it in models before.

LukeNewman
New Contributor

Is this toolbox compatible with ArcMap 10.1 because it won't let me open it in ArcToolbox. Thanks.

0 Kudos
RichardFairhurst
MVP Honored Contributor

The Summary Statistics tool help link is for 10.1 and says it is available for all license levels.  I have personally used that tool under 10.1 at all license levels.  Are you able to open the Analysis Tools toolbox and the Statistics toolset and see it?  Does it not open when you double-click it?

The Frequency and Tabulate Intersection tools that are also found under that toolset are not available with any license lower than an Advanced license.  Only the Summary Statistics tool will work under a Basic license.

EDIT:  This new forum format makes it easy to miss the person each reply is being directed to.  I see the issue with opening a toolbox was directed to Dan and not me.  I wish the reply would include quotes from the post being replied to like the old forum did so that it was more obvious what the context of each reply was.

0 Kudos
IanMurray
Frequent Contributor

Richard, I don't believe he was questioning its availability, more its effectiveness with his current task.  Since he is working with text based fields and not numeric summary statistics doesn't seem to be what he is needing.

0 Kudos
RichardFairhurst
MVP Honored Contributor

Ian Murray wrote:

Richard, I don't believe he was questioning its availability, more its effectiveness with his current task.  Since he is working with text based fields and not numeric summary statistics doesn't seem to be what he is needing.

The tool does everything he described.  The tool works in Modelbuilder and will automatically count every unique text category within a point of interest text field if he makes his point-of-interest category field the unique case field.  Summary Statistics remains one of the solutions to this problem as long as the output is defined as a table with every unique text value from a categorizing field with an accompanying count of the instances of that value contained within the source table.  That is exactly what the tool is designed to do.

Other solutions are possible, but none will be simpler.

0 Kudos