0

I need to find how many routes are assigned to each account number in a feature class. I'm using search cursor twice, first to build a set of account numbers, second to get the number of times a route number is found associated with an account number. The second search cursor, I think, would be introduced after a for loop based on each value in the accounts set. As the search cursor is run through each value in the set, it should count the number of times a route number is found and then build a dict with the account number as the key and the number of different routes as the value.

Here is what I have so far:

import arcpy

# set workspace
arcpy.env.workspace = r"C:\RS_Data\Workspace\gisdb\layers.gdb"

# create input variable based on service location layer
inAccts = r"C:\RS_Data\Workspace\gisdb\layers.gdb\Accts"

# create empty set and dict 
Accts = set()
AcctRts = {}

# Populate Accts with account numbers
with arcpy.da.SearchCursor(inAccts, "Account_ID") as cursor:
    for acct in cursor:
        Accts.add(acct[0])

# populate dict
for acct in Accts:
    with arcpy.da.SearchCursor(inAccts, "Exist_Route") as cursor:
        for row in cursor:
            RtCt = arcpy.GetCount_management(row[0])
            AcctRts[acct] = RtCt

for k in AcctRts.items():
    print k

And here is the error I'm getting:

Traceback (most recent call last):
  File "C:\Python27\ArcGIS10.2\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 323, in RunScript
    debugger.run(codeObject, __main__.__dict__, start_stepping=0)
  File "C:\Python27\ArcGIS10.2\Lib\site-packages\pythonwin\pywin\debugger\__init__.py", line 60, in run
    _GetCurrentDebugger().run(cmd, globals,locals, start_stepping)
  File "C:\Python27\ArcGIS10.2\Lib\site-packages\pythonwin\pywin\debugger\debugger.py", line 654, in run
    exec cmd in globals, locals
  File "N:\Python\Completed scripts\MultiDay_Check_num_of_routes_per_Acct.py", line 1, in <module>
    import arcpy
RuntimeError: cannot open '051244-0001'
Traceback (most recent call last):
  File "C:\Python27\ArcGIS10.2\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 323, in RunScript
    debugger.run(codeObject, __main__.__dict__, start_stepping=0)
  File "C:\Python27\ArcGIS10.2\Lib\site-packages\pythonwin\pywin\debugger\__init__.py", line 60, in run
    _GetCurrentDebugger().run(cmd, globals,locals, start_stepping)
  File "C:\Python27\ArcGIS10.2\Lib\site-packages\pythonwin\pywin\debugger\debugger.py", line 654, in run
    exec cmd in globals, locals
  File "N:\Python\Completed scripts\MultiDay_Check_num_of_routes_per_Acct.py", line 1, in <module>
    import arcpy
  File "C:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\management.py", line 14483, in GetCount
    raise e
ExecuteError: Failed to execute. Parameters are not valid.
ERROR 000732: Input Rows: Dataset 021 does not exist or is not supported
Failed to execute (GetCount).
  • 1
    If "Exist_Route" is a field in the inAccts table, using "GetCount" will not work. "GetCount" returns the number of rows in an input table/fc. What is the format of the data in the "Exist_Route" field? Say for instance it is a comma delimited string like so: "route1, route2, route3". In this case you could use "len(row[0].split(','))" to return the number of routes in the string. – GeoJohn Jun 16 '16 at 20:13
  • 1
    Also... Looks like you might be missing a boolean evaluation immediately after the "for row in cursor" line, and your second search cursor should include both fields. – GeoJohn Jun 16 '16 at 20:19
  • What kind of data is the field "Exist_route"? Is it a boolean, or a count, or an ID number? is there ever a case where you have an account number without an Exist_route? if so, is the "Exist_route" None or null or 0? – crld Jun 16 '16 at 20:44
  • Exist_Route is a string field with one value per field so the len(row[0].split(',') doesn't apply. No null values. I also can't build a list because of duplicate values. Therefore the option is to build a set of unique Exist_Route values per account number but then len() is not an option. I thought I could build another for loop to base a counter on for the Exist_Route set and use that value but it seems to be getting tied up. – geoJshaun Jun 16 '16 at 21:22
1

Since this is the same source file you can do this in a single dictionary. Dictionary keys already cannot contain duplicates, so they are effectively a set already. I assume you want the count of unique routes for each account. Here is the code that will get you both the unique list of routes associated with each account and the count of the unique routes for each account in a list for each account (code is untested, but should work):

import arcpy

# set workspace
arcpy.env.workspace = r"C:\RS_Data\Workspace\gisdb\layers.gdb"

# create input variable based on service location layer
inAccts = r"C:\RS_Data\Workspace\gisdb\layers.gdb\Accts"

# create empty dict 
AcctRts = {}

# Populate AcctRts with account keys with list of routes and count in a list
with arcpy.da.SearchCursor(inAccts, ["Account_ID", "Exist_Route"]) as cursor:
    for row in cursor:
        acct = row[0]
        rt = row[1]
        if not acct in AcctRts:
           # account is not in the Dictionary so add the account key
           # account key holds a list of routes and a count of 1 in a list
           AcctRts[acct] = [[rt], 1]
        elif not rt in AcctRts[acct][0]:
           # Route is not in the route list so append it and increase count
           AcctRts[acct][0].append(rt)
           AcctRts[acct][1] += 1

If you really mean to count the number of times each route is associated with an account that has multiple routes with duplicates that can be done with a dictionary of routes holding a count in the list rather than a list of routes in the list and extending the concept of this code to a second level.

  • That worked perfectly. I started off with a a single dictionary and no set but ran into a sql expression error in the search cursor when I ran it so I figured using two fields for the cursor parameters was tripping it up, hence the attempt to use two cursors. Thanks! – geoJshaun Jun 17 '16 at 16:04
  • 1
    I notice you removed my post as an answer. If it ran, but did not produce the results you wanted, than that is due to the fact that you have not given a clear example of what you want to count. There are a variety of possible counts with 2 fields: 1) Account could have a count of 8 records, 2) Account could have a count of 3 different unique route names in the 8 records, and 3) Account could have a list of 3 counts for the 3 unique routes, i.e. a count of 4 for the first unique route, a count of 3 for the second unique route and a count of 1 for the third unique route. Which do you want? – Richard Fairhurst Jun 17 '16 at 18:18
1

collections.defaultdict works brilliantly here.

Consider the following:

from collections import defaultdict
intDict, listDict = defaultdict(int), defaultdict(list)    

for i in xrange(15):
    if i % 4:
        intDict["A"] += 1
        listDict["A"].append(i)
    else:
        intDict["B"] += 1
        listDict["B"].append(i)

>>>print(intDict)
defaultdict(<type 'int'>, {'A': 11, 'B': 4})
>>>print(listDict)
defaultdict(<type 'list'>, {'A': [1, 2, 3, 5, 6, 7, 9, 10, 11, 13, 14], 'B': [0, 4, 8, 12]})

Remember that dictionary keys must be hashable (so one of the immutable data types). Depending on your needs, you might want a tuple as a key, or even some nested dictionaries.

  • @ Paul and Richard. My apologies. I was away for a few days. I removed the check from Richard's answer because I wanted to explore the possibility of getting a the count of route and then returning the route number that was listed the majority of times per account number. If an account has a tie between route numbers, it will return the first. – geoJshaun Jun 22 '16 at 15:59
  • However, I took Richard's code and it worked perfectly and was then able to paste everything into excel, made a pivot with acct numbers as rows, route numbers as columns, and count of route as values. I then used MAX at the right of the array and then used MATCH in the adjacent column which returned the value at the top of the table (route number) for the corresponding MAX value or just returned the first route on the left. – geoJshaun Jun 22 '16 at 16:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.