Get Unique Values from a Field (Maybe Statistics Too?)

698
3
12-13-2023 09:17 PM
Status: Open
Labels (1)
DaveFullerton
Occasional Contributor III

Get Unique Values from a Field (Maybe Statistics Too?)

Getting unique values from a field in code can be done using the snippet provided by @RichRuh :

https://community.esri.com/t5/arcgis-pro-sdk-questions/getting-statistics-on-a-field/td-p/846351

But there are limitations. It took a while to find them, and it is very possible I didn’t find them all.

For our use case, the goal is to allow the user to use any layer of type FeatureLayer in the map’s contents as part of their work process.  Streamlining the process through code involves asking them to select from 3 combo boxes:  map layer name, then field name, and finally field value.

The code snippet from the post above throws exceptions when used with XY Event Layers, and as far as I can see, most feature layers that have a joined table (click link above for more information).

I think it might not be too hard to get something into the API that provides unique values since it is already done in the Pro GUI, for example, in the “Select By Attributes” tool.  But I thought I would mention Statistics as well since it is very much related and worth considering at the same time.  If anyone wants to see that in the API too, it would make sense to leave a comment with details about use cases for that.

3 Comments
MattHowe

Love the idea, especially if it was inbuilt as a normal tool in Pro. You can run a few lines of Python to get the unique values and then some follow up stats that might be more workable. The Pandas library has a nice summary statistics method.

 

import arcpy
import pandas as pd
my_layer = r"C:\\test.shp"
stats_field = "DEPTHS_M"
unique_value_list = [row[0] for row in arcpy.da.SearchCursor(my_layer, [stats_field]]
unique_value_set = set(unique_value_list)
final_list = list(unique_value_set)
stats = pd.Series(final_list)
stats.describe()
# Should give summary stats like count, mean, standard deviation, min, max, and a few percentiles.

 

Bud
by

This isn't what you're asking for, but it still might be of interest:

We could get a list of unique values for a given field using a SQL subquery. A subquery would work in mobile and enterprise geodatabases, but I couldn't get it to work in a file geodatabase.


Sample data (the TYPE field has duplicates):

Bud_0-1702547846664.png

 

Option 1 — Use Select By Attributes: (or a definition query)

Bud_1-1702548080707.png

 objectid in (
    select
        min(objectid) as objectid
    from
        a_test_fc
    group by
        type)


Option 2 — Create a database view: (or a query layer in an enterprise geodatabase)

Bud_3-1702548275387.png

Bud_5-1702548327111.png

select
    objectid,
    type
from
    a_test_fc
where
    objectid in (
        select
            min(objectid) as objectid
        from
            a_test_fc
        group by
            type)
order by
    type

Edit: In hindsight, I didn't need to include the OBJECTID as a field in the view. I could have just used the TYPE field as the unique ID.

 

Those techniques give us a "list of unique values" in the attribute table. We're querying for the first arbitrary row in each group.

Bud
by