Python Update Cursor, Delete Rows Between Values

4604
8
12-13-2017 01:09 PM
GeneSipes1
New Contributor III

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

0 Kudos
8 Replies
JoshuaBixby
MVP Esteemed Contributor

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.

0 Kudos
GeneSipes1
New Contributor III

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!

0 Kudos
MitchHolley1
MVP Regular Contributor

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
New Contributor III

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!

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

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
New Contributor III

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

0 Kudos
DavidSolari
New Contributor III

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
Esri Esteemed Contributor

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.

0 Kudos