7

Is it possible to iterate fields in a table to remove Null values and spaces (where there is no value)? In other words, I would like to replace Null and " " with "" (no space).

Cobbling arcpy scripts, I have this:

import arcpy

fc = "{path to geodatabase and feature class}"
fieldList = arcpy.ListFields(fc)
for field in fieldList:
    with arcpy.da.UpdateCursor(fc, [fieldList]) as cursor:
        for row in cursor:
            if row[0] == None:
                row[0] = ''
            elif row[0] == ' ':
                row[0] = ''
                cursor.updateRow(row)

print "Processing complete"

I'm aware of using "remove" in the field calculator, but you have to go field by field. I'd like to do this for the whole table.

5 Answers 5

4

I think you've got it the wrong way around. Not for field in fieldList then for row in cursor, rather for row in cursor and for field in fieldList except you will need to index properly... you're only trying to adjust the first field in the fieldList with row[0].

import arcpy

fc = "{path to geodatabase and feature class}"
fieldList = arcpy.ListFields(fc)
# remove some key fields from the list
desc = arcpy.Describe(fc)

fieldList.remove(desc.shapeFieldName)
fieldList.remove(desc.OIDFieldName)

with arcpy.da.UpdateCursor(fc, [fieldList]) as cursor:
    fRange = range(len(fieldList)) # create an index 0 to the number of elements in fieldList - 1

    for row in cursor:
        SomethingUpdated = False # a flag for changes

        # step through each field in the row by its index
        for index in fRange:
            if row[index] == None:
                row[index] = ''         #set the field to empty string
                SomethingUpdated = True #flag to store row
            else:
                val = str(row[index]).replace(" ","") # remove spaces
                if len(val) == 0:
                    # value is nothing but spaces or empty
                    row[index] = ''         #set the field to empty string
                    SomethingUpdated = True #flag to store row

        if SomethingUpdated:
            cursor.updateRow(row)

print "Processing complete"

Be aware that no checking is being done for field types; trying to set '' into a numeric field will cause this script to crash hard!

3

I shall offer my variation:

import arcpy

fc = "{path to geodatabase and feature class}"

fieldList = [f.name for f in arcpy.ListFields(fc) if f.type == "String"]

if fieldList:

    with arcpy.da.UpdateCursor(fc, [fieldList]) as cursor:
        for row in cursor:
            for i in range (len(fieldList)):
                if not row[i]:
                    row[i] == ""
                elif row[i] == " ":
                    row[i] == ""
            cursor.updateRow(row)

print "Processing complete"
7
  • I like how you put together your field list of only string fields! Users new to python can find the single line syntax a bit confusing, would you mind expanding the construction of the field list please. Jan 15, 2015 at 0:19
  • I like this answer, though to be extra safe I would modify the elif slightly: elif row[i].rstrip() == "": ... That way you'll also pick up any instances of 2 or more spaces.
    – mr.adam
    Jan 15, 2015 at 0:36
  • 1
    @Michael That list formation syntax took me a while to get down in my head as well. the basic format is [{code}var{code} for var in [list] if var{logic test}]. The syntax is a method of working with lists to create other lists. So for my code, the new list is populated with each field object's name from the listfields code if that field's type is string. It probably makes even less sense now. Jan 15, 2015 at 0:45
  • Conceptually, this is exactly what I'm looking for. However, I ran the script as provided and got the error: File "C:\Test\RemoveNullAndSpaces.py", line 7, in <module> with arcpy.da.UpdateCursor(fc, [fieldList]) as cursor: TypeError: 'field_names' must be string or non empty sequence of strings
    – Lance
    Jan 15, 2015 at 14:08
  • try modifying the list comprehension to: ... if t.type.lower() == "string". The error is saying that the list of field names is empty. Also, it's good to add print statements so you can keep track of what's going on. print the fieldList after the line where you create it.
    – mr.adam
    Jan 15, 2015 at 14:42
2

I'm not sure that I understand what you are asking, because your question does not describe what is not working in your code, but I think:

    for row in cursor:
        if row[0] == None:
            row[0] = ''
        elif row[0] == ' ':
            row[0] = ''
            cursor.updateRow(row)

needs to be:

    for row in cursor:
        if row[0] == None:
            row[0] = ''
        elif row[0] == ' ':
            row[0] = ''
        cursor.updateRow(row)

You will also need to filter out text fields from your other fields because they are the only ones you can write strings to.

1

I don't believe that it's possible to calculate for an entire table at once, you will still need to calculate field by field.

As far as actually performing the edit the best way to update the data would be with arcpy.CalculateField_management - which is effectively the field calculator in your Python script - rather than an Update Cursor (which has to operate row at a time). You can use a Python expression in arcpy.CalculateField_management (or the field calculator itself) to check a string and return the updated string, for example:

(!field_name! or "").strip() # will also strip the trailing spaces

Which you can throw at arcpy.CalculateField_management in a script;

import arcpy

fc = "{path to geodatabase and feature class}"
fieldList = arcpy.ListFields(fc, field_type="String") # don't want to try this on an integer

for field in fieldList:
    expression = """(!{}! or "").strip()""".format(field.name)
    arcpy.CalculateField_management(fc, field.name, expression, "PYTHON_9.3")
3
  • My table has 52 fields, including 1 Object ID, 1 Geometry, and 3 Double type fields. That's 47 fields I'd rather not individually recalculate to remove null values and single spaces. The 5 fields are fairly obvious that I wouldn't need to check for type before running the calculation. I would just skip those. It's just that running the calculation on 47 fields, one-by-one is super tedious.
    – Lance
    Jan 15, 2015 at 14:14
  • @Lance Sorry, should have made that clearer - you can call the field calculator as part of a Python script with arcpy.CalculateField_management which processes an entire column at once, rather than having to worry about row by row. Answer updated to reflect this.
    – om_henners
    Jan 15, 2015 at 21:51
  • Thank you for the clarification! I added a print statement at the end to know when the script completed. Took a bit more than 5 min. to run, but a lot less tedious than me doing it! Thanks for your help!
    – Lance
    Jan 19, 2015 at 18:53
1
fc = inFeature # feeature class containing the null values
Row = # field containing the null values
selection = arcpy.SelectLayerByAttribute_management(fc, "New_Selection", "Row is NULL") # selects the rows with null values
arcpy.DeleteRows_management(fc) # deletes selected rows
1
  • 1
    Please add a little explanation to your code.
    – MaryBeth
    Mar 25, 2016 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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