topic Re: ArcPy Update Cursor if/else in Python Questions

ArcPy Update Cursor if/else

ModernElectric — Tue, 18 Jun 2019 22:27:58 GMT

Good Afternoon:

I have been doing multiple searches on how to correctly write an if/else statement using Update Cursor to update a specific field based on the criteria in a different field, and lets say I got nothing.

Looking for a little help on how to write this:

I have a field called BILLING_MONTH_TEXT (text) and another field called BILLING_DATE (text).

What I need to do is say (As an Example) if BILLING_MONTH_TEXT is May-2019 then it would update BILLING_DATE with a value of 30. If it is April-2019, the value would be 29. And so on and so on.

If I could get started, I can figure out how to fill in the remaining months and values.

Thank You for your help.

Re: ArcPy Update Cursor if/else

JoeBorgione — Sat, 11 Dec 2021 21:44:08 GMT

Rather than an if elif else block, you might want to take a look at https://community.esri.com/blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-dictionaries.

I've used Richard's approach more than once and it's pretty slick. In your case, you would make a dictionary of Month/Number pairs of keys and values like this:

monthDict = {'April':29,'May':30,'Month':Number}
‍

and your update cursor might look something like this:

target = 'your feature class'
updateFields = ‍‍['BILLING_MONTH_TEXT', 'BILLING_DATE']

with arcpy.da.UpdateCursor(target,updateFields) as updateRows:
    for updateRow in updateRows:
        keyValue = updateRow[0]
        if keyValue in monthDict:
           updateRow[1] = monthDict[keyValue][0]
           updateRows.updateRow(updateRow)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Untested...

Re: ArcPy Update Cursor if/else

ModernElectric — Wed, 19 Jun 2019 14:24:39 GMT

Joe:

Thank you for taking a look and providing me with additional information to look at. I build the script example you sent me. However, I am getting this error:

updateRow[1] = monthDict[keyValue][0]
TypeError: 'int' object has no attribute '__getitem__'

Any advice?

Re: ArcPy Update Cursor if/else

JoshuaBixby — Wed, 19 Jun 2019 14:46:55 GMT

Taking a step back, are you looking to set the billing date on the second to last day of each month?

Re: ArcPy Update Cursor if/else

JoeBorgione — Sat, 11 Dec 2021 21:44:11 GMT

As mentioned, untested, but....

dateDict = {'April':29,'May':31}
keyValue = 'April'
if keyValue in dateDict:
    print(dateDict.get(keyValue,[0]))‍‍‍‍

Just worked for me, returning 29. See if this revised code works for you:

with arcpy.da.UpdateCursor(target,updateFields) as updateRows:
    for updateRow in updateRows:
        keyValue = updateRow[0]
        if keyValue in monthDict:
           updateRow[1] = monthDict.get(keyValue,[0])
           updateRows.updateRow(updateRow)‍‍‍‍‍‍

Got the idea from: https://www.tutorialspoint.com/python/dictionary_get.htm

Re: ArcPy Update Cursor if/else

ModernElectric — Wed, 19 Jun 2019 14:55:09 GMT

I think I am being a little vague on what I am trying to do. I have a table of reading data (approx 15,000 records) of approx 30 months. I have one field that is the billing date (ex. May-2019). The other field is the billing month (ex. 30). Basically, I started with Dec-2016 which has a value of 1, Jan-2017 which is assigned a value of 2 and so on and so forth to May-2019. A previous step uses the Convert Date tool to get the May-2019. This step is to update the Billing Month field based on the value in the Billing Date field.

Hope this helps and makes more sense.

Re: ArcPy Update Cursor if/else

JoshuaBixby — Wed, 19 Jun 2019 15:08:00 GMT

So effectively your final data would look something like:

BILL_DATE	BILL_MONTH
Dec-2016	1
Dec-2016	1
Jan-2017	2
...	...
June-2018	19
...	...
June-2018	19
July-2018	20
...	...
May-2019	30

Re: ArcPy Update Cursor if/else

ModernElectric — Wed, 19 Jun 2019 15:13:40 GMT

Joshua:

Yes, in a nut shell that would be correct.

I have a series of Model(s) in ArcToolBox that runs my process for me (Billing Data) and it takes several hours to run the process. I am working on transitioning the model(s) to python scripts to make the process run faster and maybe run it automatically through a series of batch file(s).

Re: ArcPy Update Cursor if/else

ModernElectric — Wed, 19 Jun 2019 15:16:30 GMT

Thank You Joe. That worked. I checked the table in ArcCatalog after running the script and the field I needed updating in-fact had the series of 29(s) and 30(s) updated.

Going to spend some time learning more about Dictionaries and Update Cursor from the information you attached to this post.

Re: ArcPy Update Cursor if/else

JoeBorgione — Wed, 19 Jun 2019 15:24:34 GMT

Dictionaries are cool; they take a moment to get your head wrapped around them, but they are worth the effort. Same goes for update cursors. This forum is where I learned about both, with a ton of guidance from Joshua Bixby‌

Re: ArcPy Update Cursor if/else

ModernElectric — Wed, 19 Jun 2019 17:21:24 GMT

Joe:

You been a huge help and I appreciate it. Wanted to see if you could help me on one more thing. One of the process is to take a field called READING_DATE (Date field type) in a m/d/y format and convert it to a field BILLING_DATE (Text field type) in a format of m-y (May-2019). I am currently using the CONVERT_TIME tool in a data model and with almost 15,000 rows it takes almost 5 hours to complete. I want to be able to use a cursor to see if this will drastically decrease the amount of time it takes to convert the field.

Thanks Again

Re: ArcPy Update Cursor if/else

JoeBorgione — Sat, 11 Dec 2021 21:44:14 GMT

Ooooo. Everyone's favorite; date fields.... I'm not familiar with the CONVERT_TIME tool you mention, but python has the datetime module and when you import it, you get the strftime() method and that guy is your friend. Check this out:

import datetime

today = datetime.datetime.now()
Today1 = today.strftime('%m-%d-%y')
Today2 = today.strftime('%m %d %y')
Today3 = today.strftime('%B-%Y')


print(Today1)
print(Today2)
print(Today3)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Copy that and and run it in a python window of your choice. Afterwards, check out:

https://docs.python.org/2/library/datetime.html

https://www.w3schools.com/python/python_datetime.asp

these are bookmark-worthy....

Re: ArcPy Update Cursor if/else

ModernElectric — Wed, 19 Jun 2019 18:59:03 GMT

Joe:

Thank you for the links. That is a cool script. However, I can I use this within a feature class/table? I have a field (READING_DATE) Date type and (BILLING_DATE) Text type. Example here:

I want to run a script that will take the reading date (date type) and convert it to the billing date (text type) and write/update the billing date field.

The tool I use now Convert Time Field (Data Management) takes 5 to 6 hours to do this process.

Re: ArcPy Update Cursor if/else

JoshuaBixby — Sat, 11 Dec 2021 21:44:16 GMT

The tool I use now Convert Time Field (Data Management) takes 5 to 6 hours to do this process.

The Convert Time Field tool should be able to process millions of records in minutes. Something is definitely off if it is taking that long for the number of records you have involved.

I agree with Joe that dictionaries are cool. Beyond being cool, they are at the core of so much of the Python language. All that said, I don't really see dictionaries as being well suited to your situation. Your situation isn't really if/else, it is more group by and increment.

Regarding your question of converting READING_DATE to BILLING_DATE, the answer partially depends on whether you are using ArcMap or ArcGIS Pro. How dates are returned from cursors and the field calculator varies between the two products.

After you having BILLING_DATE populated, the following code (adopted from Sort Field and then Calculate Sequential Values ) will give you the results using an approach that I think is more suited to your data.

import arcpy
from itertools import count, groupby
from operator import itemgetter

tbl = # path to table/feature class or name of table view/feature layer
case_fields = ["BILLING_DATE"]
increment_field = ["BILLING_MONTH"]
sql_orderby = "ORDER BY {}".format(", ".join(case_fields))

with arcpy.da.UpdateCursor(
   tbl,
   case_fields + increment_field,
   sql_clause=(None, sql_orderby)
) as cursor:
    counter = count()
    case_func = itemgetter(*(cursor.fields.index(fld) for fld in case_fields))
    for key, group in groupby(cursor, case_func):
        key = key if isinstance(key, tuple) else (key,)
        c = next(counter) + 1
        for row in group:
              cursor.updateRow(list(key) + [c])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: ArcPy Update Cursor if/else

JoeBorgione — Wed, 19 Jun 2019 19:54:02 GMT

That's just a concept script; you'd need to take those elements and add them into an update cursor.

Re: ArcPy Update Cursor if/else

ModernElectric — Wed, 19 Jun 2019 19:55:33 GMT

The Convert Time Field tool is being used with a number of other tool(s) in a stand-alone python script that will later be combined into a batch file for easier processing.

The code that I am using to convert the raw READING_DATE field to the text file is this:

arcpy.ConvertTimeField_management(ELECTRIC_METER_READING_RAWDATA, "READING_DATE", "'Not Used'", "READING_DATE_CONVERT_TEXT", "TEXT", "MM/dd/yyyy;1033;;")

Last time I tried to run it with only about 8,000 rows, it took about 25 minutes to run.

This is what it looks like when its finally done: