topic Re: Checking if field is unique value field in Python Questions

Checking if field is unique value field

JohnDye — Sun, 12 Dec 2021 08:23:53 GMT

I have a parameter in a python toolbox which allows a user to select a field from a dataset given in a previous parameter.

# Feature Class to absorb geometry from
param2 = arcpy.Parameter(
    displayName="Geometry Feature Class",
    name="in_geoFC",
    datatype="GPFeatureLayer",
    parameterType="Required",
    direction="Input")‍‍‍‍‍‍‍

# Table ID field
param3 = arcpy.Parameter(
    displayName="Table Geometry ID Field",
    name="table_geoIDField",
    datatype="GPString",
    parameterType="Required",
    direction="Input",
    enabled=False)
param3.filter.type = "ValueList"
param3.filter.list = []‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The updateParameters section contains logic to update Parameter 3 with the field names for all of the fields in the dataset provided in parameter 2 which have a datatype of string:

def updateParameters(self, parameters):
    """Modify the values and properties of parameters before internal
       validation is performed.  This method is called whenever a parameter
       has been changed."""
    
    # if 'in_geoFC' is populated with a value
    if parameters[2].value:
        # if 'in_geoFC' does not have an error set
        if not parameters[2].hasError():
            #  Create a list of all of the fields in the 'in_geoFC'
            # which have a datatype of 'String'
            fc_geoIDFields = [field.name for field in arcpy.Describe(
                              parameters[2].valueAsText).fields
                              if field.type == 'String']
            # Enable the parameter
            parameters[3].enabled = True
            # Populate the parameter with the list of text fields in the
            # table
            parameters[3].filter.list = fc_geoIDFields‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This all works just fine...

There's one more thing I need to do though. Whichever field the user selects for parameter 3, I need to ensure that the values contained in this field are unique - every record must have a unique value.

I know of a pretty easy and elegant way to get the number of unique values in that field:

len(set(r[0] for r in arpy.da.SearchCursor(parameters[1].valueAsText
                                           , parameters[2].valueAsText)))‍‍‍‍‍‍‍‍

What I don't know is the quickest and most elegant way to get the total number of features in that dataset so that I can compare it to the number of unique values in that field and thus, determine if all of the values in that field are unique.

Keep in mind that this would be occurring in the updateMessages function, so it needs to be a fairly quick process.

Any thoughts on how to get a record count super fast?

Re: Checking if field given contains unique values for dataset

JoshuaBixby — Mon, 16 Oct 2017 20:37:27 GMT

Personally, I like Counter.

Re: Checking if field given contains unique values for dataset

DanPatterson_Retired — Sun, 12 Dec 2021 08:23:56 GMT

some may offer numpy and pandas solutions, but the initial load time

%timeit len(set(random.randint(0,10) for i in range(1000000)))
1.5 s ± 11.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

import numpy as np
%timeit len(np.unique(np.random.randint(0, 10, size=1000000, dtype='l')))
43.4 ms ± 281 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

# ---- is a coffee sip worth the difference? ----‍‍‍‍‍‍‍‍

Re: Checking if field given contains unique values for dataset

JamesCrandall — Sun, 12 Dec 2021 08:23:59 GMT

import numpy as np

nparr = arcpy.da.FeatureClassToNumPyArray(TheInputFeatureClass, ['TheFieldToEvaluate'])
uniqueValueCount = len(np.unique(nparr))
print uniqueValueCount‍‍‍‍‍

Re: Checking if field given contains unique values for dataset

JohnDye — Sun, 12 Dec 2021 08:24:02 GMT

Look at you rockstars! Lightning quick to respond.

I actually came up with this a few minutes later. Benchmarked it against 465,051 records. I'd like to get it down to 3 seconds or less

def calculateRunTime(function, *args):
    startTime = time.time()
    result = function(*args)
    return time.time() - startTime, result
    
def isUniqueValueField(dataset, field):
    idList = []
    with arcpy.da.SearchCursor(dataset, field) as cursor:
        for row in cursor:
            idList.append(row[0])
    if len(idList) != len(set(idList)):
        return False
    else:
        return True
        
>>> calculateRunTime(isUniqueValueField
                    , parameters[2].valueAsText
                    , parameters[3].valueAsText)
(8.847000122070312, True)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Checking if field given contains unique values for dataset

DanPatterson_Retired — Mon, 16 Oct 2017 20:59:35 GMT

the timing for both conversion to numpy array has a bit of overhead but insignificant, for about 25,000 unique points, but still within a coffee sip and an import

%timeit len(np.unique(arcpy.da.FeatureClassToNumPyArray(in_fc, 'OID@')))
107 ms ± 2.2 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

Re: Checking if field given contains unique values for dataset

JamesCrandall — Mon, 16 Oct 2017 21:02:46 GMT

Considering Dan's valid point about numpy setup time, what about simply setting a "set" to the SearchCursor?

print len(set(arcpy.da.SearchCursor(TheInputFeatureClass, 'TheFieldToEvaluate')))‍

Re: Checking if field given contains unique values for dataset

JamesCrandall — Mon, 16 Oct 2017 21:03:51 GMT

Yep. Great point considering the OP needs this to perform during an onLoad event.

Re: Checking if field given contains unique values for dataset

JohnDye — Mon, 16 Oct 2017 21:05:25 GMT

I am doing that to determine the unique values. The catch is determining if that number of unique values matches the length of the recordset - which tells me every value in the given field is unique

Re: Checking if field given contains unique values for dataset

JohnDye — Mon, 16 Oct 2017 21:09:22 GMT

numpy or pandas is likely going to be faster. I always forget about Pandas being available now. I wouldn't hold the import times against them with a Python toolbox. You pay those costs upfront at toolbox initialization unless you're importing inside of your tool classes.

Re: Checking if field given contains unique values for dataset

JamesCrandall — Sun, 12 Dec 2021 08:24:04 GMT

if int(arcpy.GetCount_management(TheInputFeatureClass).getOutput(0))<> len(set(arcpy.da.SearchCursor(TheInputFeatureClass, 'TheFieldToEvaluate'))):
    print 'length did not match'‍‍

Re: Checking if field given contains unique values for dataset

JohnDye — Mon, 16 Oct 2017 21:12:59 GMT

GetCount_management is a no-go because it has to instantiate the geoprocessor. Big performance hit. Not a good thing to do in Python Toolbox parameter validation unless you like making your users watch the cursor spin.

Re: Checking if field is unique value field

JamesCrandall — Mon, 16 Oct 2017 21:22:34 GMT

Aren't you already doing that? I see you are setting up arcpy.da.SearchCursor().

Re: Checking if field given contains unique values for dataset

DanPatterson_Retired — Sun, 12 Dec 2021 08:24:07 GMT

_as_narray ... I have used it recently in some of my blogs.

import numpy as np

a = arcpy.da.SearchCursor(in_fc, 'OID@', explode_to_points=True)._as_narray()

# in_fc is a featureclass,  explode to points not needed, but I wanted a bigger file

len(a) == len(np.unique(a))
True‍‍‍‍‍‍‍‍

Now if you are playing Code Golf, you can put that all in two lines

Re: Checking if field is unique value field

JoshuaBixby — Sun, 12 Dec 2021 08:24:09 GMT

Have you done any benchmarking? Get Count easily beats other methods for getting the total records in a data set, especially for larger data set. If you are already running a cursor against the data set, then using Get Count might be unnecessary, but any extra time added to the script would be from an necessary call and not a slow function.

In terms of balancing simplicity and performance, I am a big fan of Counter, as I stated above. It is trivially slower than proposed set operations, and it gives you much richer information in case that might have value at some point.

import collections

def unique_check_cnt(iterable):
    counter = collections.Counter(iterable)
    return True if counter.most_common(1)[0][1] == 1 else False
    
fc = # path to feature class
fld = # field name to check for uniqueness

with arcpy.da.SearchCursor(fc, fld) as cur:
    print unique_check_cnt(i for i, in cur)‍‍‍‍‍‍‍‍‍‍‍

If the data sets are large and there is a reasonable chance there will be duplicates, then it might pay off to implement a method that can stop as soon as a duplicate is found.

import collections

def unique_check_defdict(iterable):
    d = collections.defaultdict(int)
    for i in iterable:
        if d[i] > 0:
            return False
        d[i] += 1
    else:
        return True

fc = # path to feature class
fld = # field name to check for uniqueness

with arcpy.da.SearchCursor(fc, fld) as cur:
    print unique_check_defdict(i for i, in cur)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Checking if field is unique value field

JohnDye — Sun, 12 Dec 2021 08:24:12 GMT

I benchmarked GetCount_management v the function I defined above. Results below.
My custom function was just a little faster but it wouldn't surprise me to find that GetCount_management were faster against a much larger dataset. I'm currently testing against a dataset with a record count of 465,051 as that's the biggest one I have on my system.

>>> calculateRunTime(arcpy.GetCount_management
                    , parameters[2].valueAsText)
(7.444000005722046, <Result '465051'>)
>>> calculateRunTime(isUniqueValueField
                    , parameters[2].valueAsText
                    , parameters[3].valueAsText)
(6.894999980926514, True)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

However, my custom function also does more than just count the rows. In addition, it compares the record count to the number of unique values in the given field, returning True or False to indicate whether or not the number of unique values in the field matches the total record count (see logic for calculateRunTime and inUniqueValueField functions in previous post). That's really what I'm after. Figuring out if the values in the given field are unique for every record.

I also tried the logic you outlined above, which is much more elegant than mine.

def unique_check_defdict(iterable):
    d = collections.defaultdict(int)
    for i in iterable:
        if d[i] > 0:
            return False
        d[i] += 1
    else:
        return True

def isUniqueValueField_byBixby(fc, field):
    with arcpy.da.SearchCursor(fc, field) as cur:
        print unique_check_defdict(i for i, in cur)

>>> calculateRunTime(isUniqueValueField_byBixby
                    , parameters[2].valueAsText
                    , parameters[3].valueAsText)
True
(7.770999908447266, None)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Since this function is going to execute in a Python toolbox tool under the updateMessages function for the tool - essentially validation, I need it to be as fast as I can possibly get it. GetCount_management might be fast at scale, but if a user throws a smaller dataset at it - waiting almost 8 seconds for validation is probably going to be suboptimal.

I could take this out of the updateMessages function and put it into the runtime execution logic then throw a runtime error if the values aren't unique but I'm trying to avoid doing that.

I'm going to play with numpy today and see if that can get me toward my goal of getting this down to 3 seconds or less against approx. half a million records.

FYI - I really appreciate the help from everyone. If you can think of other ways to optimize or make it go zoom, zoom, please share!

Re: Checking if field is unique value field

JoshuaBixby — Tue, 17 Oct 2017 14:13:29 GMT

Have you tried running your code several times by just looping over the cursor and doing nothing? Setting up the cursor and iterating through it are likely the most expensive part of the function, possibly by orders of magnitude. In most of the examples I tested, the slowest code was < 200 ms for an iterable with 1 million items. It runtimes are several seconds long, it makes me think generating the iterable is taking most of the time.

In production, setting up a cursor for the same data set and just iterating over it will have variability in timing due to resource contention and scheduling. Although it never hurts to optimize code, at some point there is a point of diminishing returns and squeezing 15% improvement out of the code that only account for 15% of the total runtime might not be worth the cost.

Re: Checking if field is unique value field

JohnDye — Tue, 17 Oct 2017 14:26:26 GMT

Good points. The slowest part is mostly likely setting up the cursor and there's not going to be a way around that. The laws of diminishing returns are certainly something to think about here. If I can't get it shrunk down by lunch, I'll probably just take the most efficient code outlined above and use it at runtime and throw an error if the field is not unique.

Re: Checking if field is unique value field

DanPatterson_Retired — Tue, 17 Oct 2017 14:30:36 GMT

the numpy option with the search cursor hasn't been tried... I use it all the time, but I always have it imported, but it take way less time than a base import of arcpy so you probably wouldn't notice the difference. The 'arcgis' module uses it and Pandas extensively, so it is slow

Re: Checking if field is unique value field

JohnDye — Tue, 17 Oct 2017 14:43:21 GMT

Would be nice if there were a Describe property for record count. That would be fast. Seems strange to me that there's not. FIDSet on a layer will return selected records, but you have to set up a cursor and iterate the dataset just to get the full count.