nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

List of unique values in multiple columns

JamesCrandall

I need create a list of unique values from multiple date columns ("startSurvey" and "EndSurvey")...(I guess a list of lists is fine as long as I can iterate over it and grab the 2 unique values). In the end, I am trying to build sql for a separate SearchCursor.

The desired result of the example needs to be:

[[StartSurvey: 6/5/2020 12:21:19 AM, EndSurvey: 6/5/2020 12:35:32 AM, [StartSurvey: 6/11/2020 12:42:05 AM, EndSurvey: 6/11/2020 5:13:18 AM]]

It doesn't necessarily have to be list of lists like this, but in some way that I can easily iterate over it to grab the unique StartDate/EndDate valules.

Najděte více příspěvků označených jako

Accepted answers

Anonymous User

This should give you a unique list of tuples.

date_info_list = list(set([(row[0], row[1]) for row in arcpy.da.SearchCursor(fc, ["StartSuvey", "EndSurvey"])]))

All comments

Anonymous User

Ah my apologies, I missed the part where we're looking to de-duplicate the data. There's a number of different ways to skin that cat, but here's one that'll capture one entry per unique GUID for you by using the GUID as the key to a dictionary.

import arcpy
fc = "C:/path/to/data.gdb/FeatureClass"

unique_entries = dict()
with arcpy.da.SearchCursor(fc, ["GUID", "StartSuvey", "EndSurvey"]) as scur:
    unique_entries[row[0]] = {"start": row[1], "end": row[2]}

with arcpy.da.SearchCursor(fc, ["GUID"]) as scur:	
	unique_GUIDs = list(set([row[0] for row in scur]))

for GUID in unique_GUIDs:
    print("Start: {}, End: {}".format(unique_entries[GUID]["start"], unique_entries[GUID]["end"]))

JamesCrandall

That does it!

Anonymous User

This should give you a unique list of tuples.

date_info_list = list(set([(row[0], row[1]) for row in arcpy.da.SearchCursor(fc, ["StartSuvey", "EndSurvey"])]))

Anonymous User

dicts aren't hashable, I am not sure that set() would work.

danretired

TableToNumPyArray—ArcGIS Pro | Documentation

ArcGIS Pro 2.7.x Python 3.6.whatever

You don't indicate what fc is.

I just gave a command line example... the principle is the same. If you prefer functions, here are two that I have used for years.

Do you have nulls in your table? if so, there are other parameters you can specify.

I don't allow nulls in my tables, but if I get one, I fix it and use a function to call the helper

Helper script

def make_nulls(in_fc, include_oid=True, int_null=-999):
    """Return null values for a list of fields objects.

    Thes excludes objectid and geometry related fields.
    Throw in whatever else you want.

    Parameters
    ----------
    in_fc : featureclass or featureclass table
        Uses arcpy.ListFields to get a list of featureclass/table fields.
    include_oid : boolean
        Include the `object id` field to denote unique records and geometry
        in featureclasses or geodatabase tables.  This is recommended, if you
        wish to join attributes back to geometry.
    int_null : integer
        A default to use for integer nulls since there is no ``nan`` equivalent
        Other options include

    >>> np.iinfo(np.int32).min # -2147483648
    >>> np.iinfo(np.int16).min # -32768
    >>> np.iinfo(np.int8).min  # -128

    >>> [i for i in cur.__iter__()]
    >>> [[j if j else -999 for j in i] for i in cur.__iter__() ]

    Notes
    -----
    The output objectid and geometry fields are renamed to
    `OID_`, `X_cent`, `Y_cent`, where the latter two are the centroid values.
    """
    nulls = {'Double': np.nan, 'Single': np.nan, 'Float': np.nan,
             'Short': int_null, 'SmallInteger': int_null, 'Long': int_null,
             'Integer': int_null, 'String': str(None), 'Text': str(None),
             'Date': np.datetime64('NaT'), 'Geometry': np.nan}
    #
    desc = Describe(in_fc)
    if desc['dataType'] not in ('FeatureClass', 'Table'):
        print("Only Featureclasses and tables are supported")
        return None, None
    in_flds = desc['fields']
    good = [f for f in in_flds if f.editable and f.type != 'Geometry']
    # good = [f for f in in_flds if f.type != 'Geometry']
    fld_dict = {f.name: f.type for f in good}
    fld_names = list(fld_dict.keys())
    null_dict = {f: nulls[fld_dict[f]] for f in fld_names}
    # -- insert the OBJECTID field
    if include_oid and desc['hasOID']:
        oid_name = desc['OIDFieldName']
        oi = {oid_name: -999}
        null_dict = dict(list(oi.items()) + list(null_dict.items()))
        fld_names.insert(0, oid_name)
    return null_dict, fld_names

Main script to read tables and fix nulls

# Featureclass table attribute data
def tbl_data(in_tbl):
    """Pull all editable attributes from a featureclass tables.

    During the process, <null> values are changed to an appropriate type.

    Parameters
    ----------
    in_tbl : text
        Path to the input featureclass.

    Notes
    -----
    The output objectid and geometry fields are renamed to
    `OID_`, `X_cent`, `Y_cent`, where the latter two are the centroid values.
    """
    flds = ['OID@']
    null_dict, fld_names = make_nulls(in_tbl, include_oid=True, int_null=-999)
    if flds not in fld_names:
        new_names = out_flds = fld_names
    if fld_names[0] == 'OID@':
        out_flds = flds + fld_names[1:]
        new_names = ['OID_', 'X_cent', 'Y_cent'] + out_flds[3:]
    a = TableToNumPyArray(
        in_tbl, out_flds, skip_nulls=False, null_value=null_dict
    )
    a.dtype.names = new_names
    return np.asarray(a)

JamesCrandall

Throws an error:

date_info_set = set(date_info_list)
TypeError: unhashable type: 'dict'

Anonymous User

ahh, ok @JamesCrandall my bad I was leading you down the wrong path.

if you just want a list of unique dates, then use @Anonymous User s original list of dictionaries method and don't worry about the unique ID.

Then convert the list to a set, which will show only unique entries in a list.

# Using a list of dictionaries approach 
with arcpy.da.SearchCursor(fc, ["StartSuvey", "EndSurvey"]) as scur:
    # Use a list comprehension to pull the data out of the cusror 
    date_info_list = [{"start": row[0], "end": row[1]} for row in scur]
date_info_set = set(date_info_list)

JamesCrandall

I get

arr = TableToNumPyArray(fc, "*")
TypeError: long() argument must be a string or a number, not 'NoneType'

danretired

By example... a small table with 10K records pulling out the unique combinations from 3 fields

import arcpy
from arcpy.da import TableToNumPyArray, NumPyArrayToTable
f = r"C:\arcpro_npg\npg\Project_npg\npgeom.gdb\sample_10k"
arr = TableToNumPyArray(f, "*")

flds = arr.dtype.names  # ---- to get all the fields
sub = arr[['County', 'Town_class', 'Facility']]  # --- subsample from 3 fields
sub.shape
(9996,)

uni, idx, cnts = np.unique(sub, True, return_counts=True)
uni.shape
(24,)

uni
array([('A', 'A_', 'Hall'), ('A', 'A_', 'Hosp'), ('A', 'B_', 'Hall'),
       ('A', 'B_', 'Hosp'), ('A', 'C_', 'Hall'), ('A', 'C_', 'Hosp'),
       ('B', 'A_', 'Hall'), ('B', 'A_', 'Hosp'), ('B', 'B_', 'Hall'),
       ('B', 'B_', 'Hosp'), ('B', 'C_', 'Hall'), ('B', 'C_', 'Hosp'),
       ('C', 'A_', 'Hall'), ('C', 'A_', 'Hosp'), ('C', 'B_', 'Hall'),
       ('C', 'B_', 'Hosp'), ('C', 'C_', 'Hall'), ('C', 'C_', 'Hosp'),
       ('D', 'A_', 'Hall'), ('D', 'A_', 'Hosp'), ('D', 'B_', 'Hall'),
       ('D', 'B_', 'Hosp'), ('D', 'C_', 'Hall'), ('D', 'C_', 'Hosp')],
      dtype={'names':['County','Town_class','Facility'], 'formats':['<U4','<U12','<U16'], 'offsets':[8,24,72], 'itemsize':2184})

# ----
# NumPyArrayToTable  =====>  and back to Arc-world

JamesCrandall

Maybe I'm missing the obvious, but when I run and print the suggested solution I don't see unique dates. That is, I get this:

[
  {
    "start": "2020-05-27 23:44:17",
    "guid": "{A6618411-6CF7-4D7C-9D11-C6F73AC6BC1B}",
    "end": "2020-05-28 05:22:50"
  },
  {
    "start": "2020-05-27 23:44:17",
    "guid": "{A6618411-6CF7-4D7C-9D11-C6F73AC6BC1B}",
    "end": "2020-05-28 05:22:50"
  },
  {
    "start": "2020-05-27 23:44:17",
    "guid": "{A6618411-6CF7-4D7C-9D11-C6F73AC6BC1B}",
    "end": "2020-05-28 05:22:50"
  },
  {
    "start": "2020-06-04 00:19:40",
    "guid": "{8C6FCDEC-4BF1-4A71-8479-2925952A0993}",
    "end": "2020-06-04 04:23:58"
  },
  {
    "start": "2020-06-04 00:19:40",
    "guid": "{8C6FCDEC-4BF1-4A71-8479-2925952A0993}",
    "end": "2020-06-04 04:23:58"
  },
  {
    "start": "2020-06-04 00:19:40",
    "guid": "{8C6FCDEC-4BF1-4A71-8479-2925952A0993}",
    "end": "2020-06-04 04:23:58"
  }
 ]

But I need this:

[
  {
    "start": "2020-05-27 23:44:17",
    "guid": "{A6618411-6CF7-4D7C-9D11-C6F73AC6BC1B}",
    "end": "2020-05-28 05:22:50"
  },  
  {
    "start": "2020-06-04 00:19:40",
    "guid": "{8C6FCDEC-4BF1-4A71-8479-2925952A0993}",
    "end": "2020-06-04 04:23:58"
  }
 ]

Anonymous User

Hi @JamesCrandall,

Speaking from the experience of having to do pretty much the same thing before, repeatedly, all I can say is that the a List of Dictionaries as suggested above by @Anonymous User is the way you want to go.
It will make incorporating your code much easier.

The only thing I would add would be to use a unique ID to help pull out the exact record that you want:

# Using a list of dictionaries approach 
with arcpy.da.SearchCursor(fc, ["JoinID", "StartSuvey", "EndSurvey"]) as scur:
    # Use a list comprehension to pull the data out of the cusror 
    date_info_list = [{"ID": row[0], "start": row[1], "end": row[2]}} for row in scur]

(Use which ever field would be most appropriate instead of Join ID, just used that as it was the only unique ID I could see from your table)

Best of luck!

JamesCrandall

I'm not getting a unique list of StartSurvey and EndSurvey values. Can you point out where this is supposed to happen in your example.

Thanks!

Anonymous User

Hi @JamesCrandall ,

It is indeed possible to structure your data as a list of lists. Personally though, my preference would be to use list of dictionaries instead. The main difference being that elsewhere in your script when we go to access the data we've stored in the variable, we can refer to the start date and end date using a name that describe the information.

If we go with a list of lists, we need to keep track of which order we originally put these values into the sub-list. On small scale applications it's not a big deal keeping track of what order you put the data in there, but as scripts grow and change hands over the years - little things like this make scripts easier to understand and maintain.

I've outlined the code for both approaches below -

import arcpy
fc = "C:/path/to/data.gdb/FeatureClass"
	
# Using a list of lists approach 
with arcpy.da.SearchCursor(fc, ["StartSuvey", "EndSurvey"]) as scur:
    # Use a list comprehension to pull the data out of the cusror
    date_info_list = [[row[0], row[1]] for row in scur]

for date_list in date_info_list:
    print("Start: {}, End: {}".format(date_list[0], date_list[1]))
	

# Using a list of dictionaries approach 
with arcpy.da.SearchCursor(fc, ["StartSuvey", "EndSurvey"]) as scur:
    # Use a list comprehension to pull the data out of the cusror 
    date_info_list = [{"start": row[0], "end": row[1]} for row in scur]

for date_dict in date_info_list:
    print("Start: {}, End: {}".date_dict["start"], date_dict["end"])

Hope that helps!

Připojte se k diskusi

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.