AnsweredAssumed Answered

How to convert a python "collection" to a gdb table? (1-to-many)

Question asked by rastrauch Champion on Jul 25, 2017
Latest reply on Aug 3, 2017 by rastrauch

I have a attribute table that contains the results of a SpatialJoin giving me multiple records for each of my target polygon.

I'm trying to convert the above to a one-to-many table; one record for each unique HuntID w/a field listing all the uniqid.e.g.

   'RL450',   '50, 57, 70, 71, 81, 175, 177'

   'DM448,  '72, 76, ...'

   etc. 

Actual formatting (i.e. comma or space separated, quotes, etc, ) is yet tbd and not important at this point

 

I've done quite a bit of searching and have found two different simple functions to give me unique values for one field, and and other method using collections for giving me the 1-to-many "collection", but I haven't been able to figure out a simple way to get this back into a table. 

 

I think I am missing something obvious, and although I have done quite a few searchs and looked thru many of the  The ...py... links  I haven't figure it out.

 

#  the various snippets I've narrowed it down to so far
import arcpy
import os
import numpy

inDS = r'Database Connections\__me@wcgis_test2.sde\sde_wcgis_test2.DBO.FandA'
inDB = r'Database Connections\__me@wcgis_test2.sde'
inFC = r'Database Connections\__me@wcgis_test2.sde\sde_wcgis_test2.DBO.FandA\sde_wcgis_test2.DBO.SJ_hunt2flat'

outTable = r'Database Connections\__me@wcgis_test2.sde\testtable'

huntFld = 'HuntID'
uniqfld = 'uniqid'

#  Function to output a list of of unique values for a field
def unique_values(table, field): 
    with arcpy.da.SearchCursor(table, [field]) as cursor:
        return sorted({row[0] for row in cursor})
       
#  Function to output a array of unique values for a field
def unique_values_2(table, field):
    data = arcpy.da.TableToNumPyArray(table, [field])
    return numpy.unique(data[field])

huntList = unique_values(inFC, huntFld)      
huntList2 = unique_values_2(inFC, huntFld)

# ###  Collection portion
import collections
uniqvals = collections.defaultdict(list)

# output a collection of unique Field1 and the its unique Field2 values
for field1, field2 in arcpy.da.SearchCursor(inFC, (huntFld, uniqfld)):
    uniqvals[field1].append(field2)

# sample output
test = uniqvals.items()[95]
test
# (u'DM448', [72, 76, 77, 78, 79, 80, 81, 83, 86, 87, 88, 89, 90, 91, 92, 93, 105, 112, 116, 122])

BTW - I have tried various combinations of arcpy summary, frequency, pivot tables, Pro's (yes Pro) "summarize within" -- instead of SpatialJoin, but open to other suggestions (although this python solution will be fast enough, I believe). 

 

Thanks

 

This seems right up your alley Dan Patterson 

Outcomes