How to add Unique ID number to a list if rows contain a consecutive sequence (Arcpy)

Question

I am trying to use an Arcpy Python code to identify the Unique ID number for streets that contain consecutive addresses and ignore rows that do not. For example:

FULLST | LOR | HIR | UID
Gate | 1 | 200 | 1
Gate | 201 | 300 | 2
Smith | 1 | 100 | 3
Smith | 350 | 400 | 4
Smith | 401 | 500 | 5

In this example I need to add UID numbers 1, 2, 4, and 5 to a list. The code I have so far (from a comment by @dslamb here) will grab 2 and 5, but not 1 and 4. For some reason it will not add the first street UID in the consecutive sequence.

streetlist = []
sc = arcpy.SearchCursor(STMS, None, None, "FULLST; LOR; HIR; MCN; OEN; UID", None)
previousSt = None
previousHIR = None
previousMCN = None
previousOEN = None
for row in sc:
 if not previousSt:
 previousSt = row.getValue("FULLST")
 previousHIR = row.getValue("HIR")
 previousMCN = row.getValue("MCN")
 previousOEN = row.getValue("OEN")
 else:
 if previousSt == row.getValue("FULLST") and previousMCN == row.getValue("MCN") and previousOEN == row.getValue("OEN"):
 if (row.getValue("LOR")-1) == previousHIR or row.getValue("LOR") < previousHIR:
 streetlist.append(row.getValue("UID"))
 previousHIR = row.getValue("HIR")
 else:
 previousHIR = row.getValue("HIR")
 else:
 previousSt = row.getValue("FULLST")
 previousHIR = row.getValue("HIR") 
 previousMCN = row.getValue("MCN") 
 previousOEN = row.getValue("OEN") 
del previousHIR
del previousMCN
del previousOEN
del previousSt
del sc
del row

I am using ArcGIS 10.2, ArcInfo License level

• Vince: Step 1: Use arcpy.da.SearchCursor. Step 2: Use a dictionary to store previously found values. Both will improve performance.
• DanMilRiv: @Vince I don't have the data access module, so I am unable to use the arcpy.da codes.
• Vince: If you're using 10.2, you have DA Cursors (they were introduced at 10.1)
• Vince: Please *edit your question to state in the body what software you are using (both version and license level).
• DanMilRiv: @Vince I was wrong, I do have access to data access. In the past when I tried using commands from that module nothing would work. Obviously just user error.

Answer 1

I've modified the code I provided originally, hopefully with what you need. I'm using the da module, and switched the list to a dictionary where the keys are the street name and value is a list of the ids. I think you were primarily missing the first id whenever it switched over to a new street.

streetlist = {}
with arcpy.SearchCursor("G/MSAG/MergedTable",["FULLST","LOR","HIR", "MCN", "OEN" "UID"],sql_clause=(None, 'ORDER BY FULLST,LOR')) as sc:
 previousSt = None
 previousHIR = None
 previousMCN = None
 previousOEN = None
 for row in sc:
 if not previousSt:
 previousSt = row[0]
 previousHIR = row[2]
 previousMCN = row[3]
 previousOEN = row[4]
 streetlist[previousSt]=[row[5]]
 else:
 if previousSt == row[0] and previousMCN == row[3] and previousOEN == row[4]:
 if row[1]<=previousHIR:
 streetlist[previousSt].append(row[5])
 previousHIR = row[1]
 else:
 previousSt = row[0]
 previousHIR = row[2]
 previousMCN = row[3]
 previousOEN = row[4]
 streetlist[previousSt]=[row[5]]

If you want to get the list of ids for each street.

for k,v in streetlist.iteritems():
 print k #this is the street name
 print v #this is the list of ids.

You could also try this to make use of the dictionary's unique key feature and reduce the code. Instead of the if in keys, you could use a try/except.

streetlist = {}
with arcpy.SearchCursor("G/MSAG/MergedTable",["FULLST","LOR","HIR", "MCN", "OEN" "UID"],sql_clause=(None, 'ORDER BY FULLST,LOR')) as sc:
 previousHIR = None
 for row in sc:
 keystring = "%s%s%s%s"%(row[0],row[2],row[3],row[4])
 if not previousHIR:
 previousHIR = row[2]
 streetlist[keystring]=[row[5]]
 else:
 if keystring in streetlist.keys():
 if row[1]<=previousHIR:
 streetlist[keystring].append(row[5])
 previousHIR = row[1]
 else:
 previousHIR = row[2]
 streetlist[keystring]=[row[5]]

Some of the commenters above may also have some speed improvement suggestions.

• DanMilRiv: Sorry for the delayed response but I got tied up with completing some work. Thank you for all your help and suggestions, especially showing me how to search a previous row and the Unique ID idea.
• DanMilRiv: Another resolution I found was to do this after the first streetlist.append: "if row.getValue("UID") in streetlist and previousUID not in streetlist: streetlist2.append(previousUID)" I found it necessary to place the first connected street in a sequence into a separate street list because it needed a separate identification later on.