Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

The problem: I have a geodatabase with several datasets and many more feature classes within. The fields within the feature classes have been populated through joins with shapefiles and manual edits. Often times string fields will become populated with whitespace (i.e. '', ' ', ' ', etc) or the string "Null", and numeric fields will become populated with a zero (0). I would like to find these records and replace them with a true NULL value. I have the following code, which uses an UpdateCursor, but it still very slow and doesn't catch all of the NULL-like records. Does anyone know of other ways to accomplish this task?

GDB = arcpy.GetParameterAsText(0) #input geodatabase
arcpy.env.workspace = GDB
datasetList = arcpy.ListDatasets() #list datasets

for dataset in datasetList:
        arcpy.env.workspace = os.path.join(GDB, dataset)
        fcList = arcpy.ListFeatureClasses()
        for fc in fcList:
                arcpy.AddMessage("Processing %s..." % fc)
                #count features
                arcpy.MakeTableView_management(fc, "tempTableView")
                count = int(arcpy.GetCount_management("tempTableView").getOutput(0))
                if count > 0:
                        fieldList = arcpy.ListFields(fc)
                        for field in fieldList:
                                arcpy.AddMessage("...%s" % field.name)
                                rows = arcpy.UpdateCursor(fc)
                                for row in rows:
                                        count = 0
                                        if row.isNull(field.name):
                                                continue # if already null
                                        elif field.type == "Text":
                                                value = row.getValue(field.name)
                                                if value.lstrip(' ') == '' or value.lower() == '<null>': # looks for whitespace or '<null>'
                                                        row.setNull(field.name)
                                                        count += 1
                                        elif field.type == "ShortInteger" or field.type == "LongInteger" or field.type == "Float" or field.type == "Double":
                                                value = row.getValue(field.name)
                                                if value == 0:
                                                        row.setNull(field.name)
                                                        count += 1
                                        if count > 0: # update row if records have changed
                                                rows.updateRow(row)
                                del rows
                else:
                        arcpy.AddMessage("...NO RECORDS FOUND.")
share|improve this question
    
What version of arcgis do you have? –  Paul Aug 12 at 18:27
    
So, you have both 10.0 & 10.1? –  Paul Aug 12 at 18:34
    
I have access to both, yes. Looking for a solution that will work for either or both. –  Barbarossa Aug 12 at 19:02
    
Have you looked at this question here ? You could implement the 10 or 10.1 code into yours gis.stackexchange.com/questions/89362/… –  GISKid Aug 12 at 19:11
    
Is this a personal (mdb), file (gdb), or RDBMS (ArcSDE)? –  RyanDalton Aug 12 at 21:59

2 Answers 2

Since I'm most familiar with 10.1+, and cursors in general are a lot better in the da module, here's a potential solution. Currently, you are creating a cursor each time you change fields, which means you are taking a hit there. Furthermore, you are checking the field type for each record instead of just using the field type once to filter initially.

I've changed how Null values are checked, but I haven't thoroughly tested it to check for all possible values. For the small sample dataset I had, it worked @ 10.2.2.

#Return None if the value needs to be changed, else return the value
def nullify(value):
    x = value    
    if value is not None: #True null fields are read as None types
        if type(value) == str:
            if value.lstrip(' ') == '' or value.lower() == '<null>':
                x = None
        else: 
            if value == 0:
                x = None 

return x

#We're only interested in some fields
ftypes = ("String", "SmallInteger", "Integer", "Double")
fieldList = [f.name for f in arcpy.ListFields(fc) if f.type in ftypes]

with arcpy.da.UpdateCursor(fc, fieldList) as rows:
    for row in rows:            
        nulled = map(nullify, row)
        if row != nulled: #Only update if the row actually needs to be changed.
            rows.updateRow(nulled)
share|improve this answer
    
Thanks @Paul, I like what you've done. I too prefer the da module. However, colleagues that share my workflow only have access to 10.0. I'll give it a try. –  Barbarossa Aug 12 at 19:45
    
I tried what you suggested and I get the following error Objects in this class cannot be updated outside an edit session on vals = [r for r in row]. Any suggestions? –  Barbarossa Aug 12 at 21:35
    
@Barbarossa, I'm not sure; I've never come across that error before. Do the fields allow Null values? I've changed my code slightly there, but it should be the same. –  Paul Aug 12 at 21:46
    
I adjusted with if f.isNullable == True for the fieldList variable to eliminate not nullable fields. I have found that the error may be due to the presence of a geometric network. –  Barbarossa Aug 13 at 17:33

Instead of using a cursor to look at all fields for each record (one at a time using the cursor), what about querying all records from a single field (in bulk) using "Select By Attributes" tool and then calculate all of the selected records using "Calculate Field" tool? Then you could just loop over each field in Python?

Using your code as a base, I mocked up this (which works on a File GDB. You'll have to correct the field syntax per database type based upon SQL reference for query expressions used in ArcGIS. I left some testing statements and the CalculateField statement commented out so you could test it out before actually breaking something.

I also borrowed the code to get your featureclasses from @gotchula answer to Listing all feature classes in File Geodatabase, including within feature datasets?

import arcpy
import os

GDB = arcpy.GetParameterAsText(0) #input geodatabase
arcpy.env.workspace = GDB

def listFcsInGDB():
    ''' set your arcpy.env.workspace to a gdb before calling '''
    #http://gis.stackexchange.com/questions/5893/listing-all-feature-classes-in-file-geodatabase-including-within-feature-datase
    for fds in arcpy.ListDatasets('','feature') + ['']:
        for fc in arcpy.ListFeatureClasses('','',fds):
            yield os.path.join(arcpy.env.workspace, fds, fc)

fcList = listFcsInGDB()

for fc in fcList:
    arcpy.AddMessage("Processing %s..." % fc)
    #count features
    tempTableView = arcpy.MakeTableView_management(fc, "tempTableView")
    count = int(arcpy.GetCount_management("tempTableView").getOutput(0))
    if count > 0:
        fieldList = arcpy.ListFields(fc)
        for field in fieldList:
            arcpy.AddMessage("...{0} ({1})".format(field.name, field.type) )
            if field.type == "Text" or field.type == "String":
                whereclause = "\"{0}\" = '' or \"{0}\" = ' ' or \"{0}\" = '  ' or \"{0}\" is NULL".format(field.name)
                #arcpy.AddMessage("Searching for " +field.name+ " where " +whereclause)
                arcpy.SelectLayerByAttribute_management("tempTableView", "NEW_SELECTION", whereclause)
                #featcount = str(arcpy.GetCount_management("tempTableView").getOutput(0))
                #arcpy.AddMessage(featcount)
                #arcpy.CalculateField_management(tempTableView,"{0}".format(field.name), "NULL")
                count += 1
            elif field.type == "Integer" or field.type == "ShortInteger" or field.type == "SmallInteger" or field.type == "LongInteger" or field.type == "Short" or field.type == "Long" or field.type == "Float" or field.type == "Single" or field.type == "Double" or field.type == "Numeric":
                whereclause = "\"{0}\" = 0 or \"{0}\" is NULL".format(field.name)
                #arcpy.AddMessage("Searching for " +field.name+ " where " +whereclause)
                arcpy.SelectLayerByAttribute_management("tempTableView", "NEW_SELECTION", whereclause)
                #featcount = str(arcpy.GetCount_management("tempTableView").getOutput(0))
                #arcpy.AddMessage(featcount)
                #arcpy.CalculateField_management(tempTableView,"{0}".format(field.name), "0")
                count += 1
            else:
                ##Not an editable field
                arcpy.AddMessage("Not an editable field type of: " + field.type)
                pass
share|improve this answer
    
Your answer actually resembles my original code, which ran slower than I would have liked. Knowing that cursors are way faster than field calculator, I was hoping to use cursors. However, if I cannot figure out how to implement them, I will happily accept your answer, as it works in both 10.0 and 10.1, as well as on various types of geodatabases. –  Barbarossa Aug 13 at 17:20
    
@Barbarossa, You say using select & calculate is slower than cursoring through all records and updating individual columns per row? I hope you share your benchmark statistics after you complete the two code options because that would surprise me, especially on larger datasets. –  RyanDalton Aug 13 at 17:45
    
I have usually seen speed gains using cursors on my smaller databases, however I stand corrected. After trying all 3 methods I have the following times: 1) arcpy.UpdateCursor - 1hr35m55s, 2) Select Layer and Calculate field - 34m36s, 3) arcpy.da.UpdateCursor - 1m40s. I expected the da.UpdateCursor to be much faster, but it requires an edit session on my geometric network. –  Barbarossa Aug 13 at 20:43
    
@Barbarossa, is that 1m40s for arcpy.da.UpdateCursor correct? If so, wow, that's much faster than the others! –  RyanDalton Aug 13 at 21:23
    
Yes it's correct. Look here for another example. –  Barbarossa Aug 13 at 21:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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