topic Re: List of unique values in multiple columns in Python Questions

List of unique values in multiple columns

JamesCrandall — Tue, 09 Feb 2021 01:07:39 GMT

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.

Re: List of unique values in multiple columns

Anonymous User — Tue, 09 Feb 2021 01:34:59 GMT

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!

Re: List of unique values in multiple columns

JamesCrandall — Tue, 09 Feb 2021 01:48:20 GMT

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!

Re: List of unique values in multiple columns

Anonymous User — Tue, 09 Feb 2021 02:03:00 GMT

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!

Re: List of unique values in multiple columns

JamesCrandall — Tue, 09 Feb 2021 02:07:30 GMT

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" } ]

Re: List of unique values in multiple columns

DanPatterson — Tue, 09 Feb 2021 02:21:43 GMT

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

Re: List of unique values in multiple columns

JamesCrandall — Tue, 09 Feb 2021 02:34:53 GMT

I get

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

Re: List of unique values in multiple columns

Anonymous User — Tue, 09 Feb 2021 02:40:42 GMT

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)

Re: List of unique values in multiple columns

JamesCrandall — Tue, 09 Feb 2021 02:42:47 GMT

Throws an error:

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

Re: List of unique values in multiple columns

DanPatterson — Tue, 09 Feb 2021 02:50:00 GMT

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)

Re: List of unique values in multiple columns

Anonymous User — Tue, 09 Feb 2021 02:50:56 GMT

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

Re: List of unique values in multiple columns

Anonymous User — Tue, 09 Feb 2021 03:01:09 GMT

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"])]))

Re: List of unique values in multiple columns

JamesCrandall — Tue, 09 Feb 2021 03:18:23 GMT

That does it!

Re: List of unique values in multiple columns

Anonymous User — Tue, 09 Feb 2021 03:21:41 GMT

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"]))