topic Re: Conditionally deleting records in Python Questions

Conditionally deleting records

PrestonEllison — Tue, 02 Apr 2019 17:17:59 GMT

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?

Re: Conditionally deleting records

JoshuaBixby — Tue, 02 Apr 2019 17:44:06 GMT

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.

Re: Conditionally deleting records

PrestonEllison — Tue, 02 Apr 2019 18:54:25 GMT

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.

Re: Conditionally deleting records

JoshuaBixby — Sun, 12 Dec 2021 03:51:09 GMT

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.

from collections import Counter
from itertools import groupby
from operator import itemgetter

fc = # path to feature class
group_size = 4
case_fields = ["Account_Nu"]
sort_field = "Image_Date"  # Insert name of field with image dates
sql_orderby = "ORDER BY {}, {}".format(", ".join(case_fields), sort_field)

fl = arcpy.MakeFeatureLayer_management(fc, "fl").getOutput(0)
oidName = arcpy.Describe(fl).OIDFieldName
oids = []

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

fl.setSelectionSet("NEW_SELECTION", oids)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Conditionally deleting records

PrestonEllison — Thu, 04 Apr 2019 14:57:10 GMT

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

Re: Conditionally deleting records

JoeBorgione — Thu, 04 Apr 2019 16:01:07 GMT

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 = r'C:\path\to\gdb\featureclass'‍

Re: Conditionally deleting records

JoshuaBixby — Thu, 04 Apr 2019 16:14:02 GMT

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

Re: Conditionally deleting records

PrestonEllison — Thu, 04 Apr 2019 17:12:40 GMT

I was able to get it to the code run successfully and create the feature layer output, however the new feature layer (fl) is just a copy of the original feature class. 8527 records input, 8527 records output.

Re: Conditionally deleting records

JoshuaBixby — Thu, 04 Apr 2019 17:37:13 GMT

Feature layer and table views are not copies of data, they are views of data. If you have a feature class open in ArcMap, you are looking at a feature layer, often times it has the same name as the feature class it is referencing.

The code as written should be creating a new feature layer, which is appears to be doing, but it should also be selecting some records from that feature layer. If you open up the attribute table of the fl feature layer, are any records selected?

Re: Conditionally deleting records

PrestonEllison — Thu, 04 Apr 2019 17:49:00 GMT

There were no records selected. Here is the results window if it helps

Re: Conditionally deleting records

JoshuaBixby — Thu, 04 Apr 2019 18:14:53 GMT

If you type the following after the code runs, what do you get:

print(oids)‍

I tested the code on some sample data on my machine before posting it, so I think the logic and code are sound. Can you post either a subset and/or sanitized set of data here for me to test on?

Re: Conditionally deleting records

PrestonEllison — Thu, 04 Apr 2019 18:46:50 GMT

Absolutely, whats the best route to get you a sample data set?

When i run the print, I get the following....

Re: Conditionally deleting records

JoshuaBixby — Thu, 04 Apr 2019 19:03:00 GMT

Just looking at your screenshot, you left off the last line, the one that actually selects the records:

fl.setSelectionSet("NEW_SELECTION", oids)‍

Re: Conditionally deleting records

PrestonEllison — Thu, 04 Apr 2019 19:11:25 GMT

Yikes, how did i miss that.

It works! This is perfect. Im assuming i could change the frequency of the selection by adjusting line 6 "group size"

Re: Conditionally deleting records

JoshuaBixby — Thu, 04 Apr 2019 20:12:55 GMT

Yes, the group_size variable sets how many photo points you want per Account_Nu, roughly. If you only have 3 points and the group size is 6, it will just select all 3. The trick that took a bit of thought was how to get staggered points along the photo traversing path. Grabbing the first or last x number of photos is easy, but I figured you don't want just 4 photos at the start of the property if there are 50 photos along the perimeter of the property.

Re: Conditionally deleting records

PrestonEllison — Thu, 04 Apr 2019 20:25:18 GMT

Exactly. The idea was to get a representative view of the entire property vs just a cluster of points from roughly the same location.The way this is set up is extremely helpful and i cant thank you enough.

cheers