3

So I have a csv table with records that have an ID. I want to find the same IDs in a feature class/shapefile and delete those records in the feature class/shapefile using Python.

I've messed with add.join and relates without a good result so I figured I'd move to cursors. I've setup two search cursors to test the result but I don't think the logic is quite right so I'm posing my question here.

Here's a code snippet. The second cursor will eventually change to Update.Cursor using delete row but not until I get the result correct first.

import arcpy

tc = "d:\\temp\\tc.shp"
changes = "d:\\temp\\changes.csv"

with arcpy.da.SearchCursor(changes,"RECORDNUM_AFTER") as changecur:
    for row in changecur:
        changeIDs=row

tcur = arcpy.SearchCursor(tc, fields="RECORDNUM")
for row in tcur:
    if row in changeIDs:
        print row.getValue("RECORDNUM")
  • 1
    I'm thinking you will want to put the second cursor inside the first one so that you check every value in the second table against the single selected row in the first cursor. – jbchurchill Dec 15 '15 at 17:13
3

Store all of your csv IDs in a python list. Then iterate and delete with an update cursor if the leak number is in the list:

import arcpy

tc = "d:\\temp\\tc.shp"
changes = "d:\\temp\\changes.csv"

#List comprehension for IDs
ids = set ([i for i, in arcpy.da.SearchCursor
       (changes, "RECORDNUM_AFTER") if i != None])

#update cursor to delete rows
with arcpy.da.UpdateCursor (tc, "RECORDNUM") as cursor:
    for recordnum, in cursor:
        if recordnum in ids:
            cursor.deleteRow ()

del cursor

Depending on the number of ids, an sql expression may be faster:

import arcpy

tc = "d:\\temp\\tc.shp"
changes = "d:\\temp\\changes.csv"

#List comprehension for IDs
ids = set ([i for i, in arcpy.da.SearchCursor
       (changes, "RECORDNUM_AFTER") if i != None])

#sql expression
delFld = arcpy.AddFieldDelimiters (tc, "RECORDNUM")
idStr = ", ".join (map (str, ids))
sql = "{0} IN ({1})".format (delFld, idStr)
#filter and delete
arcpy.MakeFeatureLayer_management (tc, "lyr", sql)
arcpy.DeleteRows_management ("lyr")
arcpy.Delete_management ("lyr")

If it's a text field you'll need to tweak the code a bit:

idStr = "', '".join (map (str, ids))
sql = "{0} IN ('{1}')".format (delFld, idStr)
  • Nice...I thought about the list but could not for the life of me think of a way to do the 'list comprehension'. I'll have to sample the sql section for performance. I'm just beginning to learn python. Thanks Emil! – SL_GIS Dec 15 '15 at 18:56
1

This uses the csv module to parse the CSV file, and add the values to a list. It then loops through the list and creates a query on the UpdateCursor for that id and deletes the row if it exists.

Look at the comments in the code to see which parts you might need to alter. The code below assumes the RECORDNUM field type is integer.

import arcpy
import csv

fc = "d:\\temp\\tc.shp"
csv_data = "d:\\temp\\changes.csv"

num_list = []
with open(csv_data, "r") as csv_file:
    reader = csv.reader(csv_file)

    #skips the header, remove this if no header info present
    reader.next()

    #change row[0] to whatever column you need, row[0] is first column
    for row in reader:
        num_list.append(int(row[0]))

num_tuple = str(tuple(num_list))

field = arcpy.AddFieldDelimiters(fc, "RECORDNUM")
query = """{0} IN {1}""".format(field, num_tuple)

with arcpy.da.UpdateCursor(fc, ("RECORDNUM"), query) as cursor:
     for row in cursor:
         cursor.deleteRow()
  • Creating a new cursor for each id could become quite slow if the table is large. Using SQL IN statement once is faster. – Emil Brundage Dec 15 '15 at 19:53
  • @EmilBrundage yeah, you're right, I've updated it. – ianbroad Dec 15 '15 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.