# Calculate number of NULL values in a feature dataset

1314
5
03-06-2019 11:06 AM
New Contributor II

Can someone tell me how to count the number of NULL fields in a feature dataset? I would like to use Model Bulider or arcpy.

5 Replies
MVP Esteemed Contributor

I'll try to get you started in python.  You'll need to first get all the feature datasets, and then all the feature classes in each of them, and then all the fields in each of feature classes.  If you have tables in the feature datasets, you'll need to loop through them as well:

import arcpy

arcpy.env.workspace = r'X:\path\to\your.gdb'
gdb = arcpy.env.workspace

fds_list = arcpy.ListDatasets('*','Feature')

for fds in fds_list:
arcpy.env.workspace = '{}\{}'.format(gdb,fds)
fc_list = arcpy.ListFeatureClasses()
for fc in fc_list:
field_list = arcpy.ListFields(fc)
counter = 0
with arcpy.da.SearchCursor(fc,field_list) as cursor:
for row in cursor:
if row[0] == None:
counter += 1
else:
pass
print('{}\{}  {} is null {} times'.format(gdb,fc,row[0],counter))

"""
completely untested, and unverified.  there may be better ways to do this
and knowing the guys that frequent this forum, you're going to get some good
ideas; that print statement can be directed to a file if you wish.
"""

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
That should just about do it....
MVP Esteemed Contributor

...the number of NULL fields...

Not to get hung up on semantics, but records/rows contain NULL.  A field can be configured to allow NULLs, but the field itself is not NULL.

Are you interested in finding fields where every record/row is NULL?  Or, are you interested in finding every record/row where any field has a NULL?

New Contributor II

I would like to tally up the number of NULL records to see what percentage of the data has been entered into the feature dataset/database. I am adding data to feature classes as it becomes available

For example, say I have a feature class storing 10 sample locations. I have sample station names but am missing elevation values. Once I receive an excel file with the elevation values they will be added to the feature class. To start, my feature class had 10 assigned records ( 1 station name for each record) and 10 NULL records (elevation values were unknown for each record). Once I have entered the data, all of the records will contain information and there are no longer NULL records.

MVP Esteemed Contributor

Since Joe roughed out some code for listing feature classes in feature datasets, I will just post how you can structure the cursor:

with arcpy.da.SearchCursor(fc, "*") as cursor:
total_recs, null_recs = 0, 0
for row in cursor:
total_recs += 1
null_recs += 1 if None in row else 0‍‍‍‍‍
MVP Esteemed Contributor

And to get a percentage of nulls per field add line 6 to Joshua's suggestion:

with arcpy.da.SearchCursor(fc, "*") as cursor:
total_recs, null_recs = 0, 0
for row in cursor:
total_recs += 1
null_recs += 1 if None in row else 0
per_cent = (null_recs / total_recs) * 100‍‍‍‍‍‍
That should just about do it....