topic Re: Python: retrieving from list & adding autoincrement IDs in Python Questions

Python: retrieving from list & adding autoincrement IDs

HayleySmall — Mon, 14 Jul 2014 15:27:10 GMT

Hi all,

I was wondering if you could help me write a script for Field Calculator.

I have a list of 2,000+ entries contained in about 40 parks. Each park has a field ParkID which is already filled out. I'd like to create a script that generates UniqueIDs based on the ParkIDs.

Ex. ParkID = Q001

.:. UniqueIDs = Q001_001, Q001_002, all the way to Q001_100 or however many necessary depending on how many entries, but then beginning at 001 again when reaching a new park, i.e. R142_001, R142_002, etc.

I want to first retrieve each ParkID from the list of ParkIDs and then use that list to create an autoincrement starting at 001 for each unique ParkID.

I'd rather do this that just do an autoincrement for each park individually.

I have very little python experience and might not quite understand some details, so could you please explain in depth?

EDIT:
Right now I have the following script in the Codeback of Field Calculator:

rec=0

plot = ""

def autoIncrement(park):

global rec

global plot

pStart = 1

pInterval = 1

if (rec == 0):

rec = pStart

plot = park

plot = plot + "_000"+ str(rec)

rec += pInterval

elif (rec < 10):

plot = park + "_000" + str(rec)

rec += pInterval

elif (rec > 9 and rec < 100):

plot = park + "_00" + str(rec)

rec += pInterval

elif (rec > 99 and rec < 1000):

plot = park + "_0" + str(rec)

rec += pInterval

else:

plot = park + "_" + str(rec)

rec += pInterval

return plot

And the following in the "FIELD_NAME" = autoIncremet(!ParkID!)

This code works, but starts the unique ID at 0 and goes up into the thousands. I am trying to find a way to make it so for each new ParkID, the unique ID restarts at 001 again.

Best,

Hayley

Message was edited by: Hayley Small

Re: Python: retrieving from list & adding autoincrement IDs

WesGoldstein — Mon, 14 Jul 2014 15:29:52 GMT

Hi Hayley Small‌,

I moved your post to the Python‌ space where it should get better attention. There are many places in GeoNet which you can SEARCH for or just BROWSE

Re: Python: retrieving from list & adding autoincrement IDs

ToddUlery — Mon, 14 Jul 2014 17:18:09 GMT

Hi Hayley,

Can I ask if the 2000 + Park data have ParkID = Q '' only? or is it a mix?

Just need to know whether to account for different ParkID's in that field or if one park is only 'Q' another park is another 'P' for example?

Just want to make sure I understand it fully.

Thanks

Todd

Re: Python: retrieving from list & adding autoincrement IDs

HayleySmall — Mon, 14 Jul 2014 17:23:51 GMT

Hi Todd

The parks are either "QXXX" (i.e. Q001), "XXXX" (i.e. X092), RXXX (i.e. R142), BXXX (i.e. B057), or MXXX (i.e. M321)

Thanks.

Re: Python: retrieving from list & adding autoincrement IDs

HayleySmall — Mon, 14 Jul 2014 17:54:53 GMT

Additionally, all Park IDs are already entered into a field "ParkID", so it would just be ParkID_001 for each new park

Re: Python: retrieving from list & adding autoincrement IDs

ToddUlery — Mon, 14 Jul 2014 20:51:42 GMT

Hayley,

Steps to complete:

Right click the field that will be update
Open up the Field Calculator; either save the attachment file and load it in the calculator or copy the Pre-Logic Script Code in the corresponding section after you check the box for Show CodeBlock in the Calculator and also Add the following to the Seciont Below the Pre-Logic Section:
- UniqueID( !ParkID! )

Field Calculator:

Parser: Python

Fields: [YOUR FIELD THAT UPDATES]

Pre-Logic Script Code:

s1 = " "

def UniqueID(ParkID):

global s1, count

y = ParkID

s = str(y)

s = s[0]

if s != s1:

count = 1

else:

count += 1

pad = str(count).zfill(3)

z = ParkID + '_' + pad

s1 = s

return z

Whats going on above:

s1 = " " Empty String Variable that will update to hold the Value from the Field

def UniqueID(ParkID): Defined Function the holds the meat and potatoes

global s1, count Global Variables for transfer of values in and out of function(Local)

y = ParkID Variable set to hold the value of the ParkID Field

s = str(y) Converting the ParkID Field Value to a string Value

s = s[0] Taking the First index of the string value (the letter of the field value)

if s != s1: A if conditional that reads the string value(letter) and compares it to the empty string from earlier

count = 1 count within the first conditional

else: Else Conditional

count += 1 count within the second conditional

pad = str(count).zfill(3) Variable that pads zero's on to the current count (3 zero's)

z = ParkID + '_' + pad Variable that hold a concatenated string value of both the original Field Value from ParkID and added on "_" incremental count.

s1 = s ‌After the first run of the function on the first row it will set the empty variable to the field value for the conditionals, this will alow for the count to continue to add + 1

return z Returns the value to add to table

If this doesn't work please include the error in the results.
Thanks

Todd

Re: Python: retrieving from list & adding autoincrement IDs

HayleySmall — Mon, 14 Jul 2014 21:03:49 GMT

Thanks for your help, Todd. Unfortunately it did not work. Here are the error results:

Re: Python: retrieving from list & adding autoincrement IDs

HayleySmall — Tue, 15 Jul 2014 14:24:59 GMT

Now that I look more closely, I am actually getting a really bizarre output. It seems like some of the Unique IDs are being generated, but some of the are repeating, or restarting again at _001 at random times. Not sure why this would happen. Additionally, some of the Unique IDs are not being generated at all. I'm not sure if they just had not been gotten to before the failure occurred and the values there are leftover from my other trials, or if they are generating just strange numbers in the thousands.

Here are some random snippets/examples:

Ex. 1 shows random numbers in the thousands in the same park as where _001 starts randomly two times.

Ex. 2 shows a park that goes from _001 immediately to _062

Ex. 3 shows a park that goes from _030 to a random numbers, and then a new park begins randomly at _033, and then a new park begins with random numbers.

Hayley

Re: Python: retrieving from list & adding autoincrement IDs

ToddUlery — Tue, 15 Jul 2014 14:46:28 GMT

That does look a little strange. The above error is probably because of null values. In order to have to incremental numbering we needed to access the first index in the range of characters for the string (DATA VALUE). If the data is NULL it cant access that, and will say it is out of range and fail. I would personally run a script on something like this. It allows for better access to the data. May not have the random that seems to occur. I don't know the quality of the data; i.e. what type of abnormalities to account for.

Re: Python: retrieving from list & adding autoincrement IDs

HayleySmall — Tue, 15 Jul 2014 15:02:14 GMT

Todd,

It actually seems like it has to do with the ObjectIDs. It seems to start over or mess up wherever the objectIDs are not perfectly ascending. For example, some data may have been collected over a large period of time, with other parks' data in between, so the object IDs are chunks at a time. IDs 1-200 and then 2300-2450 might belong to one park,, because other parks' info was taken in between (200-2300). I'm not sure if there is a way around this- incrementing based on *not* the objectIDs, but I used the script that I wrote ^above^ and just executed it individually for each park (i.e. 40+ times), which was not ideal, but worked.

Re: Python: retrieving from list & adding autoincrement IDs

ChristopherMoravec1 — Wed, 16 Jul 2014 05:22:39 GMT

Hayley,

I know you got it working, but I went ahead and took a pass at a script anyway. Basically I created a dictionary that holds the "Park ID" values where the Park ID is the key, and stored the number of records that we have encountered for that park as the value. What this allows the code to do is keep track of how many times it has encountered a particular Park ID and use that number to create the new unique ID. It is not smart enough to updated only null records, instead it will just rebuild all the Id's, that may not be what you want in the end.

Here is the code to put in the "Pre-Logic Script Code":

global id_value_dict

id_value_dict = dict()

def calc_new_id(base_id_value):

global id_value_dict

if base_id_value in id_value_dict:

id_value_dict[base_id_value] += 1

else:

id_value_dict[base_id_value] = 1

return str(base_id_value) + "_" + str(id_value_dict[base_id_value]).zfill(3)

You can also check out a gist of it here: A quick script to create a unique ID based on the value of a different column. For example, the column value might have…‌

Hope that helps!

Christopher

Re: Python: retrieving from list & adding autoincrement IDs

ToddUlery — Wed, 16 Jul 2014 12:40:01 GMT

Looks perfect. There is really no 'one' way to do this. I was going to suggest looking at that script and alter it. I ran my script on data that was more perfect and 'yes' it was ascending nicely. It was not accounting for hiccups; just a straight run.

If anything I am glad you were able to get it figured out enough to get through it. Again if there are 2000 + these records on multiple tables, I might suggest writing a separate python script.

Would be glad to help if you need it.

Take care,

Todd

Re: Python: retrieving from list & adding autoincrement IDs

ToddUlery — Wed, 16 Jul 2014 19:18:28 GMT

Hey Hayley, I am currently writing a tool you could run. It expands on Christopher's input for using Dictionaries. Brilliant input. You may have already worked this out, but I will complete it anyway.

Re: Python: retrieving from list & adding autoincrement IDs

ToddUlery — Wed, 16 Jul 2014 21:17:45 GMT

Here is a tool you can use Hayley.

It has 3 parameters:

Path for table/fc
ParkID Field
UniqueID Field

It can be batched if needed.

Please feel free to open it up and look at the guts of it, and don't hesitate to ask anything.

Will Need to export it; I have imported it.

Of course that goes for anybody else that wants to use it too.

Ex. of prints. Should see in geoproc.:

...Unique ID S0029_010 added for S

Value IS NULL, Empty String, OR UNKNOWN

0034 ...Value IS GOOD

{'Q': 10, ' ': 1, 'S': 10, 'R': 1}

0034

...Unique ID 0034_001 added for

T0035 ...Value IS GOOD

{'Q': 10, ' ': 1, 'S': 10, 'R': 1, 'T': 1}

T0035 T

...Unique ID T0035_001 added for T

I am determined to make this work. So let me know if i have not accounted for something and share the error.

Thanks,

Todd

Re: Python: retrieving from list & adding autoincrement IDs

TomGoeke — Thu, 17 Jul 2014 19:23:49 GMT

Thanks for this code. I was looking for an auto-increment tool that would work on a varying base id field. This worked perfect.