topic Re: Sequential number based on two other fields? in Python Questions

Sequential number based on two other fields?

ionarawilson1 — Wed, 10 Jul 2013 19:45:33 GMT

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)

Re: Sequential number based on two other fields?

StacyRendall1 — Sat, 11 Dec 2021 14:00:18 GMT

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)

Re: Sequential number based on two other fields?

ionarawilson1 — Wed, 10 Jul 2013 21:16:22 GMT

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

Re: Sequential number based on two other fields?

RhettZufelt — Sat, 11 Dec 2021 14:00:21 GMT

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.

Re: Sequential number based on two other fields?

StacyRendall1 — Thu, 11 Jul 2013 00:01:13 GMT

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)

Re: Sequential number based on two other fields?

ionarawilson1 — Thu, 11 Jul 2013 12:29:18 GMT

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

Re: Sequential number based on two other fields?

ionarawilson1 — Thu, 11 Jul 2013 12:41:33 GMT

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!!!

Re: Sequential number based on two other fields?

ionarawilson1 — Thu, 11 Jul 2013 13:50:12 GMT

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

Re: Sequential number based on two other fields?

ionarawilson1 — Sat, 11 Dec 2021 14:00:23 GMT

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

Re: Sequential number based on two other fields?

ionarawilson1 — Sat, 11 Dec 2021 14:00:26 GMT

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)

Re: Sequential number based on two other fields?

RhettZufelt — Sat, 11 Dec 2021 14:00:29 GMT

Yes, would use a searchcursor to populate the lists.

Somthing like this:

yearList = []
countyList = []

with da.SearchCursor(inFc,["years","counties"]) as cursor:
      for row in cursor:
         year= str(row[0])
         county = str(row[1])
         yearList.append(year)
         countyList.append(county)
del cursor

Of course, this will have the same issue, as if you delete one row (if it was the highest sequence number), the next time the maxValue searchcursor is run on it, the "old" number would be the "new" maxValue again.

Also, in the code I provide previously, I should not have put the maxValue SearchCursor within the loop. It is hardcoded to the FC and fields, so no need to run that process each time. would move that just above the loop.

R_

Re: Sequential number based on two other fields?

RhettZufelt — Sat, 11 Dec 2021 14:00:32 GMT

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)

Does the change in red above get rid of the error?

R_

Re: Sequential number based on two other fields?

ionarawilson1 — Sat, 11 Dec 2021 14:00:34 GMT

No, the problem was with something else. Can you please look at this code and tell me why I get the following error message, but I dont't get an error when I run Stacy's original code?

Traceback Info:
File "D:\ArcGISData\SARS\Python_10April2013\BoundaryReporting_26March2013_changes_july2013_added_domain_changed_again.py", line 260, in <module>
sequenceDict2[key] = 1

Error Info:
<type 'exceptions.TypeError'>: 'unicode' object does not support item assignment

# Iterate through rows and get highest ID values

    sequenceDict = {}
    for row1 in cur1:
        if sequenceDict < row1.PlanID:
            arcpy.AddMessage("TEST")
            sequenceDict = row1.PlanID
            arcpy.AddMessage(sequenceDict)

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

    # Iterate through rows and update values

    sequenceDict2 = {}
    sequenceDict2 = sequenceDict
    arcpy.AddMessage(sequenceDict2)
    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:

         sequenceDict2[key] = 1
    row2.PlanID = key + '-' + str(sequenceDict2[key]).zfill(3)
    cur2.updateRow(row2)

Re: Sequential number based on two other fields?

RhettZufelt — Sat, 11 Dec 2021 14:00:37 GMT

No, the problem was with something else. Can you please look at this code and tell me why I get the following error message, but I dont't get an error when I run Stacy's original code?

Traceback Info:
File "D:\ArcGISData\SARS\Python_10April2013\BoundaryReporting_26March2013_changes_july2013_added_domain_changed_again.py", line 260, in <module>
sequenceDict2[key] = 1

Error Info:
<type 'exceptions.TypeError'>: 'unicode' object does not support item assignment
# Iterate through rows and get highest ID values

    sequenceDict = {}
    for row1 in cur1:
        if sequenceDict[key] < row1.PlanID:
            arcpy.AddMessage("TEST")
            sequenceDict[key] = row1.PlanID
            arcpy.AddMessage(sequenceDict)

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

    # Iterate through rows and update values

    sequenceDict2 = {}
    sequenceDict2 = sequenceDict
    arcpy.AddMessage(sequenceDict2)
    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:

         sequenceDict2[key] = 1
    row2.PlanID = key + '-' + str(sequenceDict2[key]).zfill(3)
    cur2.updateRow(row2)

I suspect it is related to trying to compare an entire dictionary to a value, then set the entire dict to a value.

Need to have key:value pair for the dict. So, sequenceDict = row1.PlanID would now overwite the setting of sequenceDict as a dictionary and now assigns it as a variable with row1.PlanID as the value.

Then later, you set sequenceDict2 equal to a variable (not a dict) and try to use it as a dict ( sequenceDict2[key] = 1).

R_

Re: Sequential number based on two other fields?

ionarawilson1 — Thu, 11 Jul 2013 19:34:57 GMT

Thank you Rhett. I need to go back to my original code and try to figure out from there how to create the unique value by county and year. Thanks again!

Re: Sequential number based on two other fields?

StacyRendall1 — Thu, 11 Jul 2013 20:13:09 GMT

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!!!

The script just creates the UniqueID based on the set passed to it. There is no simple way to make it permanent, apart from never running the script again...

The only ways that come to mind for making it permanent would be by using some kind of version control...? I don't really know. It ought to be possible to do something by writing/reading information to/from a text file, but that would be a lot of work to program.

Have you looked into Geodatabase versioning tools? I don't know if they will help you, but they might...

Re: Sequential number based on two other fields?

StacyRendall1 — Sat, 11 Dec 2021 14:00:40 GMT

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

What is PlanID?

When you delete a feature its information disappears. You no longer are able to use it as the basis for a calculation.

I think you need to step back from the problem for a minute and try to figure out exactly what you are trying to do. This is a normal part of programming complex things. Get a piece of paper and write/draw all the information you have, then think about how you can get the output you require. For example, the answer I posted earlier was basically:

increment counter whenever a value of column1+column2 appears
write colum1+column2+counter to column3

This is called pseudo-code. If you can write a pseudo-code that works on paper (or, say, in Excel) then you can program it. Without doing this process you are stabbing in the dark.

Re: Sequential number based on two other fields?

StacyRendall1 — Thu, 11 Jul 2013 20:25:45 GMT

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!!!

I would guess that PlanID is not a text field...? Everything else looks OK.

Re: Sequential number based on two other fields?

ionarawilson1 — Mon, 15 Jul 2013 17:58:14 GMT

Thank you Stacy, I created a workflow but since I am such a newbie I still need help. Thanks!!!

Re: Sequential number based on two other fields?

ionarawilson1 — Tue, 16 Jul 2013 18:14:02 GMT

Thank you Rhett and Stacy for your great help! I figure out a way to do it and it is working perfectly. If we delete the record, the other records don't get overwritten. I will soon post the solution after I clean up my code a little. I appreciate your help and patience!!!