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; } } }

Conditionally deleting records

arthurpete

Im trying to come up with a way to conditionally delete some records in a table. For instance, i have variably shaped polygons that when spatially joined with a point feature class tend to result in excess points on the larger polygons. I would like to set up a definition query to select and eventually delete every other or even every 2 out of 3 rows IF (and only if) there are, say more than 10 occurrences of that record. Im trying to implement this in arcmap via a python/sql query. Im sure there is way to do this in the output file (.csv) but I would like to maintain the integrity of the data in arcmap prior to exporting it.

Any help on how to go about doing this?

Find more posts tagged with

Accepted answers

bixb0012

Try the following in the interactive Python window (modify Lines #05 & #08). The code will select the records to keep, which you can invert to select the records to delete.

<SPAN class="keyword token">from</SPAN> collections <SPAN class="keyword token">import</SPAN> Counter
<SPAN class="keyword token">from</SPAN> itertools <SPAN class="keyword token">import</SPAN> groupby
<SPAN class="keyword token">from</SPAN> operator <SPAN class="keyword token">import</SPAN> itemgetter

fc <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to feature class</SPAN>
group_size <SPAN class="operator token">=</SPAN> <SPAN class="number token">4</SPAN>
case_fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"Account_Nu"</SPAN><SPAN class="punctuation token">]</SPAN>
sort_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Image_Date"</SPAN>  <SPAN class="comment token"># Insert name of field with image dates</SPAN>
sql_orderby <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ORDER BY {}, {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>case_fields<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> sort_field<SPAN class="punctuation token">)</SPAN>

fl <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>MakeFeatureLayer_management<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"fl"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>getOutput<SPAN class="punctuation token">(</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN>
oidName <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Describe<SPAN class="punctuation token">(</SPAN>fl<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>OIDFieldName
oids <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>fl<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">,</SPAN> sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> sql_orderby<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
    oidField <SPAN class="operator token">=</SPAN> cur<SPAN class="punctuation token">.</SPAN>fields<SPAN class="punctuation token">.</SPAN>index<SPAN class="punctuation token">(</SPAN>oidName<SPAN class="punctuation token">)</SPAN>
    case_func <SPAN class="operator token">=</SPAN> itemgetter<SPAN class="punctuation token">(</SPAN>
        <SPAN class="operator token">*</SPAN><SPAN class="punctuation token">(</SPAN>cur<SPAN class="punctuation token">.</SPAN>fields<SPAN class="punctuation token">.</SPAN>index<SPAN class="punctuation token">(</SPAN>fld<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> fld <SPAN class="keyword token">in</SPAN> case_fields<SPAN class="punctuation token">)</SPAN>
    <SPAN class="punctuation token">)</SPAN>
    count <SPAN class="operator token">=</SPAN> Counter<SPAN class="punctuation token">(</SPAN>case_func<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cur<SPAN class="punctuation token">)</SPAN>
    cur<SPAN class="punctuation token">.</SPAN>reset<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="keyword token">for</SPAN> key<SPAN class="punctuation token">,</SPAN> group <SPAN class="keyword token">in</SPAN> groupby<SPAN class="punctuation token">(</SPAN>cur<SPAN class="punctuation token">,</SPAN> case_func<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
        group_step <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN>
            slice<SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">)</SPAN>
            <SPAN class="keyword token">if</SPAN> count<SPAN class="punctuation token">[</SPAN>key<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token"><</SPAN> group_size <SPAN class="keyword token">else</SPAN>
            slice<SPAN class="punctuation token">(</SPAN>count<SPAN class="punctuation token">[</SPAN>key<SPAN class="punctuation token">]</SPAN><SPAN class="operator token">%</SPAN>group_size<SPAN class="punctuation token">,</SPAN>None<SPAN class="punctuation token">,</SPAN>count<SPAN class="punctuation token">[</SPAN>key<SPAN class="punctuation token">]</SPAN><SPAN class="operator token">//</SPAN>group_size<SPAN class="punctuation token">)</SPAN>
        <SPAN class="punctuation token">)</SPAN>
        oids <SPAN class="operator token">+=</SPAN> <SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN>oidField<SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> list<SPAN class="punctuation token">(</SPAN>group<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN>group_step<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN>

fl<SPAN class="punctuation token">.</SPAN>setSelectionSet<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"NEW_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN> oids<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>‍</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>

All comments

bixb0012

As Joe Borgione‌ points out, you are currently setting fc to a tuple containing a string, not a string.

JoeBorgione

Is it a lower case x or and upper case X? that'll make a difference. Just as a matter of style, when setting paths, I prefer to use the 'r' functionality in python which allows you enter raw text. I'm lazy and would rather let the computer escape special characters like the \ .

fc <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'C:\path\to\gdb\featureclass'</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

arthurpete

Bear with me as i dont have a good working understanding of Python.

for line 5 my path looks like this...

fc = ("x:\\Street_Level_Photography\\Street_Level_Photography.gdb\\Export_Output")

and im getting the following: Error 000732: Input Features: Dataset x:/Street_Level_Photography/Street_Level_Photography.gdb/Export_Output does not exist or is not supported

Im guessing there is a syntax error. Im not sure if this makes a difference but the data is not stored locally, should i use the server name rather than the drive name? Server name would be gemini, my drive name is x

bixb0012

Try the following in the interactive Python window (modify Lines #05 & #08). The code will select the records to keep, which you can invert to select the records to delete.

<SPAN class="keyword token">from</SPAN> collections <SPAN class="keyword token">import</SPAN> Counter
<SPAN class="keyword token">from</SPAN> itertools <SPAN class="keyword token">import</SPAN> groupby
<SPAN class="keyword token">from</SPAN> operator <SPAN class="keyword token">import</SPAN> itemgetter

fc <SPAN class="operator token">=</SPAN> <SPAN class="comment token"># path to feature class</SPAN>
group_size <SPAN class="operator token">=</SPAN> <SPAN class="number token">4</SPAN>
case_fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">"Account_Nu"</SPAN><SPAN class="punctuation token">]</SPAN>
sort_field <SPAN class="operator token">=</SPAN> <SPAN class="string token">"Image_Date"</SPAN>  <SPAN class="comment token"># Insert name of field with image dates</SPAN>
sql_orderby <SPAN class="operator token">=</SPAN> <SPAN class="string token">"ORDER BY {}, {}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN><SPAN class="string token">", "</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>case_fields<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> sort_field<SPAN class="punctuation token">)</SPAN>

fl <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>MakeFeatureLayer_management<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"fl"</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>getOutput<SPAN class="punctuation token">(</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN>
oidName <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>Describe<SPAN class="punctuation token">(</SPAN>fl<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>OIDFieldName
oids <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>fl<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"*"</SPAN><SPAN class="punctuation token">,</SPAN> sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> sql_orderby<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cur<SPAN class="punctuation token">:</SPAN>
    oidField <SPAN class="operator token">=</SPAN> cur<SPAN class="punctuation token">.</SPAN>fields<SPAN class="punctuation token">.</SPAN>index<SPAN class="punctuation token">(</SPAN>oidName<SPAN class="punctuation token">)</SPAN>
    case_func <SPAN class="operator token">=</SPAN> itemgetter<SPAN class="punctuation token">(</SPAN>
        <SPAN class="operator token">*</SPAN><SPAN class="punctuation token">(</SPAN>cur<SPAN class="punctuation token">.</SPAN>fields<SPAN class="punctuation token">.</SPAN>index<SPAN class="punctuation token">(</SPAN>fld<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> fld <SPAN class="keyword token">in</SPAN> case_fields<SPAN class="punctuation token">)</SPAN>
    <SPAN class="punctuation token">)</SPAN>
    count <SPAN class="operator token">=</SPAN> Counter<SPAN class="punctuation token">(</SPAN>case_func<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cur<SPAN class="punctuation token">)</SPAN>
    cur<SPAN class="punctuation token">.</SPAN>reset<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="keyword token">for</SPAN> key<SPAN class="punctuation token">,</SPAN> group <SPAN class="keyword token">in</SPAN> groupby<SPAN class="punctuation token">(</SPAN>cur<SPAN class="punctuation token">,</SPAN> case_func<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
        group_step <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN>
            slice<SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">)</SPAN>
            <SPAN class="keyword token">if</SPAN> count<SPAN class="punctuation token">[</SPAN>key<SPAN class="punctuation token">]</SPAN> <SPAN class="operator token"><</SPAN> group_size <SPAN class="keyword token">else</SPAN>
            slice<SPAN class="punctuation token">(</SPAN>count<SPAN class="punctuation token">[</SPAN>key<SPAN class="punctuation token">]</SPAN><SPAN class="operator token">%</SPAN>group_size<SPAN class="punctuation token">,</SPAN>None<SPAN class="punctuation token">,</SPAN>count<SPAN class="punctuation token">[</SPAN>key<SPAN class="punctuation token">]</SPAN><SPAN class="operator token">//</SPAN>group_size<SPAN class="punctuation token">)</SPAN>
        <SPAN class="punctuation token">)</SPAN>
        oids <SPAN class="operator token">+=</SPAN> <SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN>oidField<SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> list<SPAN class="punctuation token">(</SPAN>group<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">[</SPAN>group_step<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">]</SPAN>

fl<SPAN class="punctuation token">.</SPAN>setSelectionSet<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"NEW_SELECTION"</SPAN><SPAN class="punctuation token">,</SPAN> oids<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>‍</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>

arthurpete

Hopefully this help to provide some context. Im working with parcel data and gps tagged photos. After a spatial join the photo points are associated with a parcel or rather an "Account_Nu". Below is an example of a typical scenario where one parcel may grab 20 points and the adjacent parcel grabs 3 or 4 based upon the length of existing road frontage. 3 or 4 pictures per parcel is desirable but anything over 10 is a bit excessive. I can parse these points down by running the delete identical tool but im limited to about a 10 ft buffer. The reason being is some parcels only have 30 or 40 ft of road frontage and im trying to at least get a couple points per parcel, any larger buffer and i could exclude entire parcels all together. The goal is to indiscriminately delete every other (or every 2 out of 3) point to still provide coverage but keep it trimmed as best as possible.

Below is a snapshot of the table, the "Account_Nu" being the primary field used to clean up the data. As you can see, multiple Account_Nu's are associated with many JPGs.

bixb0012

I see what you are after, but it is hard to respond in abstract concepts. If you can describe the structure of your data, and/or provide sample data, you will get better and more specific responses.

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.