Python Update Cursor, Delete Rows Between Values

[GeneSipes1]

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

[JoshuaBixby]

Sharing with https://community.esri.com/community/developers/gis-developers/python?sr=search&searchId=55eff279-07...‌ 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.

[GeneSipes1]

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!

[MitchHolley1]

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

[GeneSipes1]

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!

[DanPatterson_Retired]

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

blah blah LIKE 'Brushes%'

then switch the selection

 or 

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.

[GeneSipes1]

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

[DavidSolari]

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:

1. Load every record into a list with a search cursor with a unique key field (time should be unique, objectid/globalid is safer).
2. Identify the records that must be deleted and push that record's key into a deletion set
3. 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!

[XanderBakker]

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.