Find and Replace Null values in ModelBuilder

[CraigPrisland]

Hi all,

I'm trying to create a model that will take selected feature classes, search their attributes, and remove any <Null> values in these tables.  Some of these fields will be an integar field type so I would need to replace these <Null's> with 0.  Do you have any ideas to go about this?  Thanks in advance!

[EricRice]

One way I quickly thought of is to just convert the feature class to a shapefile.  Shapefile's do not support Null entries and automatically convert Null to zero. 

Regards,
Eric

[NobbirAhmed]

In ModelBuilder, try using Iterate Field Values iterator - you'll get it from the Insert menu > Iterators.

Make your data input to the Iterate Field Values as follows:

[ATTACH=CONFIG]14519[/ATTACH]

The iterator iterates through each value of a chosen field of the selected features. If there is no selection then it iterates all features. To get 0 for null values, be sure to set Null Value to 0 (last parameter in this dialog).

[ATTACH=CONFIG]14520[/ATTACH]

In the above dialog, for the BEAT field of crime data, all <null>s will be replaced by 0 (in this case). You can replace null with any other value (such as -9999). Hope that helps. Thanks.

[CraigPrisland]

Thanks for the responses.  Is there anyway to search all of the fields in the selected map layers and not just one field?

[NobbirAhmed · Accepted solution]

You can do it ModelBuilder but in a cumbersome way. You will need to embed two extra models and a script tool in a main model. Instead, use this script - simple and straightforward.

import arcpy import os  # replace data path with your OWN data. in_data = r"C:\data\f.gdb\parcels"  field_list = [] numeric_fields = ["Double", "Integer", "SmallInteger"]  fields = arcpy.ListFields(in_data) for field in fields:     if (field.type in numeric_fields):         field_list.append(field.name)  # convert the list of fields to a semicolor delimited string fields = ";".join(field_list)    rows = arcpy.UpdateCursor(in_data, '', '', fields) count = len(field_list)  for row in rows:     for k in range(count):         if row.isNull(field_list):             row.setValue(field_list, 0)         rows.updateRow(row)

Let me know if you have any trouble.

If you want you can modify the script a little bit to use it in a script tool.

[CraigPrisland]

That worked great.  Thanks!

[AmandaKuepfer]

Hi,

This thread seems to be the answer to my issue but I can't quite get it to work.

Basically, I am looking for a way to replace <Null> by -9999 in multiple fields at once, using ArcGIS 10.1. The Find and Replace function in the Attribute table doesnt seem to work for <Null> cells. I've never used the ModelBuilder option and wondered if either of you could explain the steps / code in more detail? In particular, could you highlight the sections I need to adapt so it works for my own data? (I tend to get confused with what's script and what's user-specific :/)

My file name 'data' and address is:

"C:\Users\Amanda K\Desktop\NN\data.gdb\data"

Field names include: sst, chl and sal.


Any help would be much appreciated.

Regards,
Amanda

[CraigPrisland]

You can do it ModelBuilder but in a cumbersome way. You will need to embed two extra models and a script tool in a main model. Instead, use this script - simple and straightforward.

import arcpy
import os

# replace data path with your OWN data.
in_data = r"C:\data\f.gdb\parcels"

field_list = []
numeric_fields = ["Double", "Integer", "SmallInteger"]

fields = arcpy.ListFields(in_data)
for field in fields:
    if (field.type in numeric_fields):
        field_list.append(field.name)

# convert the list of fields to a semicolor delimited string
fields = ";".join(field_list)  

rows = arcpy.UpdateCursor(in_data, '', '', fields)
count = len(field_list)

for row in rows:
    for k in range(count):
        if row.isNull(field_list):
            row.setValue(field_list, 0)
        rows.updateRow(row)

Let me know if you have any trouble.

If you want you can modify the script a little bit to use it in a script tool.

I am also trying to incorporate "float" field types as well into this script.  I am adding "float" into this line: numeric_fields = ["Double", "Integer", "SmallInteger", "Float"].  However, the float field does not update.  Any ideas?  Thank you in advance.

[NobbirAhmed]

Consult this topic:

Field (arcpy)

The field types in arcpy.Field object are mapped as follows - float type is mapped to Single. So, use 'Single' instead of 'Float'

Blob �??Blob
Date �??Date
Double �??Double
Geometry �??Geometry
Guid �??Guid
Integer �??Integer (Long Integer)
OID �??Object ID
Raster �??Raster
Single �??Single (Float)
SmallInteger �??Small Integer (Short Integer)
String �??String (Text)