Sequential number based on two other fields?

[ionarawilson1]

I need to create an ID that includes the year, county and sequential number. The sequential number or the county makes the id different

So
if year 2013, county 001  - the sequential number is 001  and unique id is 2013-001-001
if year 2013, county 001 - the sequential number is 002 and unique id is 2013-001-002
If year 2014, county 001 - sequential number is 001 and unique id is 2014-001-001
If year 2014, county 005 - sequential number is 001 and unique id is 2014-005-001

I already have the code to calculate the sequential number but now it is just calculating the numbers from 001, 002, 003 for the fields when sorted by the sequential number field. Is there a way to create the sequential number based on year and county ? Thank you

   arcpy.SelectLayerByAttribute_management("Stewardship", "CLEAR_SELECTION")       filter = 'NOT SequenceNumber IS NULL' #filter for non-Null values     cur1 = arcpy.UpdateCursor("Stewardship", filter, "", "", "SequenceNumber A")   # Iterate through rows and get highest ID values     high_id = 0     for row1 in cur1:         if high_id < row1.SequenceNumber:             high_id = row1.SequenceNumber       filter = 'SequenceNumber IS NULL' #filter for Null values     cur2 = arcpy.UpdateCursor("Stewardship", filter)      # Iterate through rows and update values     i = high_id     for row2 in cur2:         i += 1         row2.SequenceNumber = (i)         cur2.updateRow(row2)  

[StacyRendall1]

Your question is kind of confusing, but I think I know what you are after... It may replace some of the older code you have developed.

Once again, a dictionary is a really handy way to solve this.

Make the year/county info the key. Each time your code comes across a new year/county combo it can add it do the dictionary, and return sequential number 1. If that year/county is already in the dictionary, it can increment the value in the dictionary and return the new number...

If your fields are year and county:

    # Iterate through rows and update values
    # make empty dictionary
    sequenceDict = {}
    for row3 in cur3:
        key = str(row3.year) + '-' + str(row3.county) # use year/county as the key
        # if key already in dict, increment it; otherwise set it to a value of 1
        if key in sequenceDict:
            sequenceDict[key] += 1
        else:
            sequenceDict[key] = 1
        # our entire sequence; zfill pads the result with zeros until it is three characters in length
        row3.SequenceNumber = key + '-' + str(sequenceDict[key]).zfill(3)
        cur3.updateRow(row3)

[ionarawilson1]

Stacey, do I keep the other cursors or I delete them? or do I delete cur2 and keep cur 1 and cur3? Thanks

[RhettZufelt]

Kind of depends on how many years and counties you have.  If not too many, might be best to use a dictionary and populate it with the possible lookup values to extract.

Another way might be to:

create a new field for the sequencnum
create a new field for the new id

iterate through and append to a list your years and another list for counties (by number)

Then something like:

for year in years:
     for county in counties:
        lyr.definitionQuery = "years" = year AND "counties" = county    # I know this isn't valid syntax, just a basic outline but would only show features in one year and county
        maxValue = arcpy.SearchCursor(infc, "", "", "", "sequencnum D").next().getValue(sequencnum)  # this would grab the max sequencnum if exists so you know what to start with.  if no exist, then set to 1
        
        then do your update cursor and update the newid field to (str(year) + "-" + str(county)  + "-" + str(sequencenum))   # may have to .zfill to get the zeros right.

something like that should "filter" the table to just one year and county at a time, then you can do the sequence number(s), then it will move onto the same year, next county, then after that year, it will do next year, first county and so on.

R_

I see Stacey posted while I was writing this.  Both methods should work, but I think Stacey's is more elegant, and probably execute faster.

[StacyRendall1 · Accepted solution]

Well, I think this uniqueID (year-county-sequenceNumber) replaces the other things you were doing. As I said before, there is a lot going on in your question, so I'm not 100% sure...

If all you want is this uniqueID (and it is in a text field called uniqueID) this should be all you need:

    arcpy.SelectLayerByAttribute_management("Stewardship", "CLEAR_SELECTION")      cur = arcpy.UpdateCursor("Stewardship")      # Iterate through rows and update values     # make empty dictionary     sequenceDict = {}     for row in cur:         key = str(row.year) + '-' + str(row.county) # use year/county as the key         # if key already in dict, increment it; otherwise set it to a value of 1         if key in sequenceDict:             sequenceDict[key] += 1         else:             sequenceDict[key] = 1         # entire uniqueID; zfill pads the result with zeros until it is three characters in length         row.uniqueID = key + '-' + str(sequenceDict[key]).zfill(3)         cur.updateRow(row)

[ionarawilson1]

Stacy, this is perfect. It worked !!! You are a genius! Thank you so much!!!

[ionarawilson1]

Stacy, there is only one issue. If for example I delete one 3266-045-009 and create another polygon and run the tool again, the value 3266-045-010 will become 3266-045-009 and the one I just created will become 3266-045-010. Is there a way to make the uniqueID permanent? Thanks again!!!

[ionarawilson1]

Basically, it would look at the highest value for that unique id and create the new PlanID from there.

[ionarawilson1]

Kind of depends on how many years and counties you have.  If not too many, might be best to use a dictionary and populate it with the possible lookup values to extract.

Another way might be to:

create a new field for the sequencnum
create a new field for the new id

iterate through and append to a list your years and another list for counties (by number)

Then something like:

for year in years:
     for county in counties:
        lyr.definitionQuery = "years" = year AND "counties" = county    # I know this isn't valid syntax, just a basic outline but would only show features in one year and county
        maxValue = arcpy.SearchCursor(infc, "", "", "", "sequencnum D").next().getValue(sequencnum)  # this would grab the max sequencnum if exists so you know what to start with.  if no exist, then set to 1
        
        then do your update cursor and update the newid field to (str(year) + "-" + str(county)  + "-" + str(sequencenum))   # may have to .zfill to get the zeros right.

something like that should "filter" the table to just one year and county at a time, then you can do the sequence number(s), then it will move onto the same year, next county, then after that year, it will do next year, first county and so on.

R_

I see Stacey posted while I was writing this.  Both methods should work, but I think Stacey's is more elegant, and probably execute faster.

Rhett, Can you please elaborate on this? Do I create a search cursor first to create the lists? How do I create the lists? Thanks

[ionarawilson1]

I tried to combine what I had before (sorting by the highest value) with what Stacy has written. But I am getting an error at the end:

Traceback Info:
  File "D:\ArcGISData\SARS\Python_10April2013\BoundaryReporting_26March2013_changes_july2013_added_domain_changed_again.py", line 234, in <module>
    row.PlanID = key + '-' + str(sequenceDict2[key]).zfill(3)

Error Info:
     <type 'exceptions.RuntimeError'>: ERROR 999999: Error executing function.



Does anybody know why? Thank you!!!

   filter = 'NOT SequenceNumber IS NULL' #filter for non-Null values
    cur1 = arcpy.UpdateCursor("Stewardship", filter, "", "", "SequenceNumber A")

# Iterate through rows and get highest ID values

    sequenceDict = {}
    for row1 in cur1:
        if sequenceDict < row1.PlanID:
            sequenceDict = row1.PlanID


    filter = 'SequenceNumber IS NULL' #filter for Null values
    cur2 = arcpy.UpdateCursor("Stewardship", filter)

    # Iterate through rows and update values

    sequenceDict2 = {}
    sequenceDict2 = sequenceDict
    for row2 in cur2:
        key = str(row2.FFY) + '-' + str(row2.County) # use year/county as the key
        if (key in sequenceDict2):
         sequenceDict2[key] += 1
        else:
            sequenceDict[key] = 1
    row.PlanID = key + '-' + str(sequenceDict2[key]).zfill(3)
    cur2.updateRow(row2)