topic Re: Get unique combinations of multiple field values in Python Questions

Get unique combinations of multiple field values

DanEvans — Wed, 02 Mar 2016 10:00:17 GMT

Hi,

I've written a geoprocessing script in Arcpy v10.2 that merges contiguous polylines into multipart polylines. At the moment it can take an SQL expression as an input that selects certain features of the input feature class to apply the algorithm to (e.g. I only want to merge intersecting pipes if they operate at the same pressure range, so I'd use the selection query to only select pipes of that pressure range).

I'm trying to modify this so instead of a selection query, you can select multiple fields from the input feature class, similar to the Dissolve tool. So for example, I could merge all intersecting pipes of the same material, diameter, and pressure range into separate, contiguous, multi-part polyline features.

I'm struggling to find a good way to identify all the unique combinations of values for the selected fields, and loop through each of these combinations, selecting them in turn and applying the algorithm to each of those selections.

The usual way to get unique values from one field is to add all the values to a list and then convert it into a set, but this gets tricky when you instead have multiple fields and you don't know how many fields the user will select! It doesn't seem possible to convert a list of lists to a set, as a set won't accept a list as an element as it's mutable. If I try converting the inner lists to tuples I get an error about tuples not being callable.

Generally the code I've written to try to achieve this is a hacked together mess! There has to be a better way of doing this!

Can anyone suggest how I can do this?

Finally, once I have each combination in a list (or maybe a dictionary, with the field name as the key?) is there a nice/efficient way to generate a SQL query to select the features with that combination of values?

Thanks

Dan

Re: Get unique combinations of multiple field values

Luke_Pinner — Wed, 02 Mar 2016 22:42:21 GMT

You can use the Frequency tool (if you have an Advanced/ArcInfo license) otherwise use the Summary Statistics tool with the fields you want to get unique combinations for as the "Case Fields".

You can then access the output with a search cursor.

Re: Get unique combinations of multiple field values

DarrenWiens2 — Wed, 02 Mar 2016 22:52:10 GMT

The usual way to get unique values from one field is to add all the values to a list and then convert it into a set, but this gets tricky when you instead have multiple fields and you don't know how many fields the user will select!

Can't you use the above method to build and filter a list of unique concatenated values, like [field1_field2_field3, field1_field2_field3, etc.]?

Re: Get unique combinations of multiple field values

DanEvans — Fri, 10 Dec 2021 20:42:54 GMT

Thanks, that'll work for getting the unique combinations, but I'm having some difficulty using those to select out one combination at a time to apply my process to.

At the moment I'm trying to use the SearchCursor to loop through each combination and build a SQL selection query for that combination by concatenating strings together. This isn't ideal though as I have to have lots of if statements to handle different data types and when it runs into NULL values it can't cope either - I could write more string concatenations to deal with NULLS but surely there must be a better way?

Here's what I have so far (is there a 'code' tag in this new forum??):

# Use a SearchCursor to loop through each combination, build a selection query, and apply the algorithm to that selection
with arcpy.da.SearchCursor(inputLines, dissolveFields) as cursor:

  selectionQuery = ''

  for row in cursor:


    for field in row:
     
      arcpy.AddMessage(field)
      count = 0
     
      if count < len(row):
        if type(field) == 'str':
          selectionQuery += arcpy.AddFieldDelimiters(arcpy.env.scratchGDB, dissolveFields[count]) + " = '" + field + "' AND "
        elif type(field) == 'int':
          selectionQuery += arcpy.AddFieldDelimiters(arcpy.env.scratchGDB, dissolveFields[count]) + " = " + int(field) + " AND "
        elif type(field) == 'float':
          selectionQuery += arcpy.AddFieldDelimiters(arcpy.env.scratchGDB, dissolveFields[count]) + " = " + float(field) + " AND "
      else:
        if type(field) == 'str':
          selectionQuery += arcpy.AddFieldDelimiters(arcpy.env.scratchGDB, dissolveFields[count]) + " = '" + field + "'"
        elif type(field) == 'int':
          selectionQuery += arcpy.AddFieldDelimiters(arcpy.env.scratchGDB, dissolveFields[count]) + " = " + int(field)
        elif type(field) == 'float':
          selectionQuery += arcpy.AddFieldDelimiters(arcpy.env.scratchGDB, dissolveFields[count]) + " = " + float(field)

      count += 1


  arcpy.AddMessage(selectionQuery)

  # Apply selection query and create working copy
  arcpy.FeatureClassToFeatureClass_conversion(inputLines, arcpy.env.scratchGDB, 'SuperStrings_temp_in', selectionQuery)

Re: Get unique combinations of multiple field values

DanPatterson_Retired — Thu, 03 Mar 2016 14:26:52 GMT

I do not understand why esri continues to make 'basic' tools only available at the Advanced level, yet provide the opportunity to do so via other means...

Free Frequency ... join the cause

code sample included, using a different approach than those already given

Re: Get unique combinations of multiple field values

Luke_Pinner — Thu, 03 Mar 2016 22:26:48 GMT

Dan Evans see Code Formatting... the basics++

I edited your post to add the formatting.

Re: Get unique combinations of multiple field values

Luke_Pinner — Thu, 03 Mar 2016 22:37:58 GMT

It's not unheard of that ESRI would change a tools license level Dan Patterson, back at ArcGIS 9.0, the 'Add Join' tool was Advanced/ArcInfo only... it's now available with all licenses.

But that was a slightly different case where the same functionality was available in the menus/toolbars at any license level, but not as a GP tool and ESRI was sorting that out. Whereas with 'Frequency', I assume the license level is an artifact from its origin as the Arc/INFO (workstation) frequency command.

That said, it's still what I would consider a 'basic' tool, with the same functionality available via other methods even without resorting* to code...

* I say resorting, but code is often my first choice. That count_combos_demo.py is a neat script btw

Re: Get unique combinations of multiple field values

DanPatterson_Retired — Thu, 03 Mar 2016 22:47:31 GMT

the quest to change/uncover artifacts continues .... split layer by attributes was my first foray a decade or so ago... numpy now makes it so much easier. Alternatives for 'basic' tools, have also been revealed... Voronoi/Delauney, Near, Statistics, Kappa coeff ... to name a few, have been given.

Re: Get unique combinations of multiple field values

RichardFairhurst — Thu, 03 Mar 2016 22:51:56 GMT

I wrote the tool in this Blog specifically for use with the output of the Frequency Tool or Summary Statistics tool when multiple fields are used for the unique case values. It creates a new Long field in both the original source and the frequency output to generate a single unique Long number to represent each multi-field unique combination. It should handle all normal data field types and Null values. The sort order of the unique field values should be correct for each field type (i.e., strings sort as strings, numbers sort as numbers, dates sort as dates, etc.) and the fields can be prioritized in any order. The 10.3 version is better than the 10.2 version, but both work.

Re: Get unique combinations of multiple field values

Luke_Pinner — Thu, 03 Mar 2016 22:54:48 GMT

Re: Get unique combinations of multiple field values

DanPatterson_Retired — Thu, 03 Mar 2016 23:07:22 GMT

yes... the quest... many great images there on that link

Re: Get unique combinations of multiple field values

DanEvans — Fri, 04 Mar 2016 11:26:20 GMT

Thanks all,

Looks like I've solved it by using Summary Statistics to get the unique combinations, then looping over the unique combinations with a SearchCursor. Within this SearchCursor I have another SearchCursor looping over all my input features and an InsertCursor to write only the matches into a temporary feature class.

Thanks again for the help.

Dan

Re: Get unique combinations of multiple field values

RichardFairhurst — Fri, 04 Mar 2016 16:22:40 GMT

Embedded cursors only works for small sets of data. The larger the data grows the inefficiency becomes exponentially bad. Loading a dictionary from the table with the data you want to read only with the unique combinations as the key and values of the fields to write in a list and then using an insert cursor based on the dictionary matches is incredibly fast. I discourage everyone from using embedded cursors. They are a poor solution.

Re: Get unique combinations of multiple field values

DanEvans — Mon, 07 Mar 2016 09:05:44 GMT

I know it's inefficient (I got it working and it took 13 hours to run on my data!) but I spent several days last week tearing my hair out trying to get it to work the efficient way, without success!

Re: Get unique combinations of multiple field values

DanPatterson_Retired — Mon, 07 Mar 2016 11:13:29 GMT

I didn't see a copy of sample data you are trying to use, is it possible to get one, so I can try the alternative approach

Re: Get unique combinations of multiple field values

DanEvans — Tue, 08 Mar 2016 12:06:43 GMT

Hi Dan,

Sorry but I can't share any of the actual data. I can give you an idea of the schema though if that helps?

It's a pipe network (just a standard line feature class though, no geometric network stuff), each feature is a section of pipe (split at fairly arbitrary points). Each section has an 8-digit unique ID, and a number of attributes such as material (2-letter text code), diameter (floating point number), diameter units (either inches or millimetres), pressure tier (Low, Medium and High), etc.

What I want is to combine contiguous sections of pipes with the same attributes into multi-part features. I will then assign each one a unique 'subsystem' ID, and spatial join this back on to the original features.

The Dissolve tool isn't suitable even with 'create multipart features' or 'unsplit lines' checked, as this either combines ALL features with the selected attributes the same (not just those that are connected) in the former case, and will only combine lines that can be combined into single-part features in the latter case.

The main issue I'm having trying to do it with dicts etc. is building a SQL query to select out each unique combination of attributes. It gets very messy when I have to start dealing with different data types.

Don't know if that helps or makes sense. Let me know if you'd like any more info. Thanks for helping.

Dan

Re: Get unique combinations of multiple field values

DanPatterson_Retired — Tue, 08 Mar 2016 14:45:15 GMT

Lets take it out of your field into concept stage... If you can extrapolate to this, then I think I have a solution at least for the reclassification to facilitate querying: Consider

Fields: prefix, state, pets,

you have 2 prefixes: (A, The)

4 states: (wet, dry, sleep, hungry)

3 pets: (cat, dog, fish)

you want all the combinations of the concatenation of the fields values. This will enable a reclassification of the data so that it makes it easier to query and do "stuff" with the results of the query. For "A hungry cat" or "A wet fish" may be class 1 and class 2, but you don't want to query the full slate and you may have numbers in one field and text in another. You just want the data reclassified so that you don't miss any possible combination.

Re: Get unique combinations of multiple field values

RichardFairhurst — Tue, 08 Mar 2016 18:17:51 GMT

Dan:

Actually, the Dissolve tool will work to solve this problem, but not directly on the lines.

1. Buffer the lines a very small distance (0.01 meter/feet for instance).

2. Dissolve the buffers with the unique Case fields set for all fields that you want to group and uncheck the multi-part feature option.

3. Add a Long field called SUBSYSTEM to the dissolved buffers and calculate the ObjectID/FID into it.

4. Spatial Join the lines as the target and the dissolved buffer as the join features with the One to Many option.

5. Select all lines where the case fields of the original lines match the case fields of the dissolved buffers.

6. Dissolve the selected lines with the case fields including all of the original fields plus the SUBSYSTEM field and check the create multi-part feature option.

The lines will be combined into a single multi-part feature that all have the same case field values, but only as long as they touch within the small distance you specified. Separate features will be created for any set of case field values where the line groups do not touch each other within the small distance you specified. Each multi-part line feature will have a unique SUBSYSTEM field value you can use for your relate.

There is no need to loop through the separate case field value sets, since all sets are handled at once by the process above.