topic Re: Python Update Cursor, Delete Rows Between Values in Python Questions

Python Update Cursor, Delete Rows Between Values

GeneSipes1 — Wed, 13 Dec 2017 21:09:28 GMT

I am taking AVL data and attempted to delete out where Street Sweeping is not occuring. That is, between events "Brushes Off" and "Brushes On". I only care about the rows between events "Brushes On" and Brushes Off".

Here is my table structure:

I want to write a simple python script that will delete the rows with event = "GPS Update" that are between event = "Brushes Off" and "Brushes On".

I know this is a simple UpdateCursor and a For Loop. I just do not have the Python experience yet.

Can anyone suggest a solution?

Thanks

Re: Python Update Cursor, Delete Rows Between Values

JoshuaBixby — Wed, 13 Dec 2017 21:58:58 GMT

Sharing with https://community.esri.com/community/developers/gis-developers/python?sr=search&searchId=55eff279-0766-46c2-b428-6805f33cec6f&searchIndex=0‌ to increase visibility of question.

This is definitely doable with a cursor, I just don't have the time right now to gin up any code. One thing you will want to do is make sure to use sql_clause argument to pass an ORDER BY statement to make sure your cursor is working with rows sorted by time column.

Re: Python Update Cursor, Delete Rows Between Values

MitchHolley1 — Sat, 11 Dec 2021 10:21:50 GMT

See if this works for you. As Joshua mentioned, if the table you're showing in your post is sorted on the data field, you'll need to add a sql_clause to sort. If not, the cursor will run in OID order (I think ).

import arcpy

ds = r'complete\path\to\dataset'

switch = ''
with arcpy.da.UpdateCursor(ds, ['event']) as cursor:
    for row in cursor:
        if row[0] == 'Brushes On':
            pass
            switch = 'on'
        elif row[0] == 'Brushes Off':
            pass
            switch = 'off'
        elif switch == 'off':
            cursor.deleteRow()
        else:
            pass
del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Python Update Cursor, Delete Rows Between Values

DanPatterson_Retired — Wed, 13 Dec 2017 22:20:43 GMT

just a thought... have you queried for 'Brushes On' OR 'Brushes Off'

blah blah LIKE 'Brushes%'

then switch the selection

blah blah not like 'Brushes%'

And delete the ones in between in an edit session

ADDENDUM

just do the query, get what you want to keep and save that to a separate featureclass.... Never throw out data, just put it aside.

Re: Python Update Cursor, Delete Rows Between Values

DavidSolari — Sat, 11 Dec 2021 10:21:53 GMT

Assuming your data is backed by any type of database, you can force the cursor to sort by time using the "sql_clause" parameter in the update cursor. This code should do the trick:

import arcpy

table = "name_of_table"
fields = ("time", "event")
postSQL = "ORDER BY time"
with arcpy.da.UpdateCursor(table, fields, sql_clause=(None,postSQL)) as cursor:
    deleteRow = False
    for time, event in cursor:
        if event == "Brushes Off": deleteRow = True
        elif event == "Brushes On": deleteRow = False
        elif deleteRow:
            cursor.deleteRow()
            arcpy.AddMessage("Deleted record at {}".format(time)‍)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

If you're working with shapefiles, the sql_clause will fail. In that case you'll have to:

Load every record into a list with a search cursor with a unique key field (time should be unique, objectid/globalid is safer).
Identify the records that must be deleted and push that record's key into a deletion set
Run through the records again with an update cursor. Delete any record whose key is a member of the deletion set.

Also: make sure you're in an editing session the first time you do this, just in case . Good luck!

Re: Python Update Cursor, Delete Rows Between Values

GeneSipes1 — Wed, 13 Dec 2017 22:57:20 GMT

Thank you! This might just work. I just ran it, and received partially desirable results. Although, that could be due to the original table data I started with. I am going to tinker with it some more tomorrow. Thanks again!

Re: Python Update Cursor, Delete Rows Between Values

GeneSipes1 — Wed, 13 Dec 2017 22:58:38 GMT

That would delete the GPS updates between the on and off events. So that wouldn't work too well for my purposes. Additionally this will be a repetitive task, so I am incorporating python and model builder. Thanks

Re: Python Update Cursor, Delete Rows Between Values

GeneSipes1 — Wed, 13 Dec 2017 22:59:33 GMT

Mitch posted a great start for me. I am going to tinker with it more tomorrow, and will let you know how it comes out. Thanks for the reply!

Re: Python Update Cursor, Delete Rows Between Values

XanderBakker — Sun, 04 Feb 2018 14:01:49 GMT

In addition to what LTS_DSolari mentioned, you may need additional delete statements in the if and first elif statement, in case the records with Brushed Off and Brushed On should be deleted too.