topic Re: Calculate number of NULL values in a feature dataset in Python Questions

Calculate number of NULL values in a feature dataset

SarahHartholt1 — Wed, 06 Mar 2019 19:06:08 GMT

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.

Re: Calculate number of NULL values in a feature dataset

JoeBorgione — Fri, 10 Dec 2021 22:44:46 GMT

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.
"""
      

        
        ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Calculate number of NULL values in a feature dataset

JoshuaBixby — Wed, 06 Mar 2019 20:19:57 GMT

...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?

Re: Calculate number of NULL values in a feature dataset

SarahHartholt1 — Thu, 07 Mar 2019 13:59:05 GMT

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.

Re: Calculate number of NULL values in a feature dataset

JoshuaBixby — Fri, 10 Dec 2021 22:44:48 GMT

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‍‍‍‍‍

Re: Calculate number of NULL values in a feature dataset

JoeBorgione — Fri, 10 Dec 2021 22:44:51 GMT

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‍‍‍‍‍‍