Sorting in a dictionary for a newbie

1232
15
06-30-2017 10:55 AM
MattBeyers
New Contributor

I'm pretty much a newbie to Python in ArcGIS. I have a table of origins and destinations and their distance. I want to be able to choose the closest n destinations for each origin and write that to a new table. I had been using cursors with SQL and OrderBy but that was taking way too long. I found Richard Fairhurst's Blog posts about using dictionaries (/blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-diction...) but I'm afraid the logic is beyond me right now.

I've put the relevant piece of code below. 'neighborconstraint' is the n closest destinations that the user has specified. 'DistanceSort' is the table that we're writing to. 'Matrix' is the input table. The second table, 'NeighborLimit,' selects the nearest n distances.

Any help?

#Have table of Origin (BID) to Destination (SID) distances (Distance). Want to select a neighbor limit of shortest distances.
DistanceSort = "DistanceSort"
#Create empty output table, where insert cursor will put distances in ascending order, grouped by BID
arcpy.CreateTable_management(outpath, DistanceSort)
arcpy.AddField_management(outpath + DistanceSort, "BID","LONG")
arcpy.AddField_management(outpath + DistanceSort, "SID","LONG")
arcpy.AddField_management(outpath + DistanceSort, "Distance","DOUBLE")
arcpy.AddMessage("Empty distance sort table created")

#Faster to use dictionary that sorts distance in ascending order, then insert???
#Prepare insert cursor to insert BID, SID, and Distance according to to SQL clause. Inserted into the the DistanceSort table 
iCursor = arcpy.da.InsertCursor(outpath + DistanceSort, ["BID","SID","Distance"])           
with arcpy.da.SearchCursor(outpath + Matrix, ["BID","SID","Distance"], sql_clause=(None, 'ORDER BY BID, DISTANCE')) as cursor:
    for row in cursor:
        iCursor.insertRow(row)
del iCursor
arcpy.AddMessage("Insert cursor created to insert sorted distances, SID, and BID")

#Create empty output table, where insert cursor will put distances under the Neighborlimit.
arcpy.AddMessage("Calculating neighbor constraint")
NeighborLimit = "NeighborLimit"                    
arcpy.CreateTable_management(outpath, NeighborLimit)
arcpy.AddField_management(outpath + NeighborLimit, "BID","LONG")
arcpy.AddField_management(outpath + NeighborLimit, "SID","LONG")
arcpy.AddField_management(outpath + NeighborLimit, "Distance","DOUBLE")
arcpy.AddMessage("Empty neighborlimit table created")

BIDList = sorted({row[0] for row in cursor})
arcpy.AddMessage("sorted BID list created")

iCursor = arcpy.da.InsertCursor(outpath + NeighborLimit, ["BID","SID","Distance"])
for BID in BIDList:
    expression = arcpy.AddFieldDelimiters(outpath + DistanceSort, "BID") + ' = ' + str(BID)
    with arcpy.da.SearchCursor(outpath + DistanceSort, ["BID", "SID", "Distance"], where_clause=expression) as cursor:  #or keep fields as  ["BID","SID","Distance"]?
        x = 1
        for row in cursor:
            if x < int(neighborconstraint) + 1:
                iCursor.insertRow(row)
                x = x + 1                        
del iCursor
0 Kudos
15 Replies
BlakeTerhune
MVP Regular Contributor

As you found, dictionaries (and lists) do not preserve the order of items added. However, there is an OrderedDict in the collections module that will maintain the order of items. However, instead of trying to do the distance checking manually, Near or Generate Near Table might suit your needs better.

MattBeyers
New Contributor

We need to go the long route instead of Generate Near Table since we don't have ArcGIS Desktop Advanced. Thanks for the info about OrderedDict.

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

I have Near as Table equivalent.. but for Pro.  If you are code adept, perhaps you can pull the scripts and modify them or create your own toolbox for ArcMap

0 Kudos
BlakeTerhune
MVP Regular Contributor

Also, the sorted() method will sort dictionaries by their key value (unless otherwise specified with the key parameter).

EDIT:

I see you're using a dictionary but not actually assigning anything to the value. In other words, you just need a list. Or in this case, a tuple would work because it's immutable and preserves the order items were inserted. Try something like

BIDList = tuple(BID for BID, SID, Dist in in cursor)
0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

Blake... FYI for the future

# Python 3.5.3

def func(**kw): print(kw.keys())       # a small function

func(a=1, b=2, c=3, d=4, e=5)          # let's run it with some keys
dict_keys(['d', 'c', 'e', 'a', 'b'])   # results are as expected... not sorted

# ----- switching over to python 3.6.1 ---- just reusing the same code block

>>> def func(**kw): print(kw.keys())   # a small function

... 
>>> func(a=1, b=2, c=3, d=4, e=5)      # let's run it with some keys
dict_keys(['a', 'b', 'c', 'd', 'e'])   # cool!!! results are now sorted

# ---- reference link ---- There is a small insinuation in help
# https://mail.python.org/pipermail/python-dev/2016-September/146327.html
0 Kudos
BlakeTerhune
MVP Regular Contributor

It means that the main goal of the PEP 468 "Preserving the order of
**kwargs in a function" is now implemented in Python 3.6

Interesting...

0 Kudos
XanderBakker
Esri Esteemed Contributor

To sort a dictionary by value (not by key) use a lambda function:

for key, val in sorted(dct.items(), key=lambda x: x[1]):  
    # do something with key and value pair

See also: https://community.esri.com/docs/DOC-1927 

BlakeTerhune
MVP Regular Contributor

Could you step back and clarify what the source tables look like? Ignore any intermediate table work your script is doing.

0 Kudos
MattBeyers
New Contributor

The table has an origin ID (OID), a destination ID (SID), and a distance between the two. I want to sort by OID then by distance, then save the first n distances for each origin ID to a table on disk.

0 Kudos