nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

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.

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!

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

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!

LTS_DSolari

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:

<SPAN class="keyword token">import</SPAN> arcpy

table <SPAN class="operator token">=</SPAN> <SPAN class="string token">"name_of_table"</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">"time"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"event"</SPAN><SPAN class="punctuation token">)</SPAN>
postSQL <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ORDER BY time"</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN> fields<SPAN class="punctuation token">,</SPAN> sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN>postSQL<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; deleteRow <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">False</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> time<SPAN class="punctuation token">,</SPAN> event <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> event <SPAN class="operator token">==</SPAN> <SPAN class="string token">"Brushes Off"</SPAN><SPAN class="punctuation token">:</SPAN> deleteRow <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">elif</SPAN> event <SPAN class="operator token">==</SPAN> <SPAN class="string token">"Brushes On"</SPAN><SPAN class="punctuation token">:</SPAN> deleteRow <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">False</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">elif</SPAN> deleteRow<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>deleteRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; arcpy<SPAN class="punctuation token">.</SPAN>AddMessage<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Deleted record at {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>time<SPAN class="punctuation token">)</SPAN>‍<SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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!

DanPatterson_Retired

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.

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 ).

<SPAN class="keyword token">import</SPAN> arcpy

ds <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'complete\path\to\dataset'</SPAN>

switch <SPAN class="operator token">=</SPAN> <SPAN class="string token">''</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>ds<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'event'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">==</SPAN> <SPAN class="string token">'Brushes On'</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">pass</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; switch <SPAN class="operator token">=</SPAN> <SPAN class="string token">'on'</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">elif</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">==</SPAN> <SPAN class="string token">'Brushes Off'</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">pass</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; switch <SPAN class="operator token">=</SPAN> <SPAN class="string token">'off'</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">elif</SPAN> switch <SPAN class="operator token">==</SPAN> <SPAN class="string token">'off'</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cursor<SPAN class="punctuation token">.</SPAN>deleteRow<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">pass</SPAN>
<SPAN class="keyword token">del</SPAN> cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

bixb0012

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.