How do I check if a field in the attribute table contains a blank cell?

1007
5
12-07-2022 11:07 PM
AhmedWaheed1
New Contributor II

I have a number of layers, and each layer contains a large number of data, and I want to know is there any empty cell in the layers attribute table without opening and previewing those tables?

0 Kudos
5 Replies
JohannesLindner
MVP Frequent Contributor

Just start up a SearchCursor for each layer / table:

 

 

def check_for_null_values(layer_or_table):
    fields = [f.name for f in  arcpy.ListFields(layer_or_table)]
    sql = " OR ".join([f"{field} IS NULL" for field in fields])
    num_rows = 0
    empty_fields = []
    with arcpy.da.SearchCursor(layer_or_table, fields, sql) as cursor:
        for row in cursor:
            num_rows += 1
            for i, value in enumerate(row):
                if value is None:
                    empty_fields.append(fields[i])
    empty_fields = sorted(set(empty_fields))
    return (num_rows, empty_fields)



layers = ["TestPoints", "TestLines", "TestPolygons", "TestTable"]
for layer in layers:
    result = check_for_null_values(layer)
    print(f"{layer} contains {result[0]} rows with null values. Fields: {', '.join(result[1])}")

 

TestPoints contains 3 rows with null values. Fields: DISTANCE, DateField, DateField2, #...
TestLines contains 0 rows with null values. Fields: 
TestPolygons contains 1 rows with null values. Fields: IntegerField
TestTable contains 71 rows with null values. Fields: DateField, DateField2, #...

Have a great day!
Johannes
Bud
by
Notable Contributor

What's the database type and version?

[Edit: That's relevant for possible SQL solutions. I posted this thinking I was the first person to reply. Meanwhile, Johannes had already posted a solution in the background without me realizing it.]


For Oracle, here's a related PL/SQL post:

Oracle EGDB: Find junk values in all tables and columns (and log them in a table)

0 Kudos
JohannesLindner
MVP Frequent Contributor

It should be completely independent from the dbms and version, as arcpy handles all the interfacing with the database.

Working on your question right now.


Have a great day!
Johannes
0 Kudos
JohannesLindner
MVP Frequent Contributor

Alternatively, you can also take the script I posted in @Bud 's question  and replace the junk_values list:

def check_for_junk_values(layer_or_table):
    fields = [f.name for f in  arcpy.ListFields(layer_or_table)]
    junk_values = [None]
    data = [dict(zip(fields, row)) for row in arcpy.da.SearchCursor(layer_or_table, fields)]  # [{"Field1": 0, "Field2": 1}, {"Field1": 5, "Field2": 2}]
    counts = dict()
    for row in data:
        for field, value in row.items():
            if value in junk_values:
                key = (field, value)
                try:
                    counts[key] += 1
                except KeyError:
                    counts[key] = 1
    return counts



tables = ["TestPoints", "TestLines", "TestPolygons", "TestTable"]
# you can also use paths to your tables, or list them automatically with arcpy.ListTables() / arcpy.ListFeatureclasses()

log_table = arcpy.management.CreateTable("memory", "JunkLogTable")
arcpy.management.AddField(log_table, "TABLE_NAME", "TEXT")
arcpy.management.AddField(log_table, "COLUMN_NAME", "TEXT")
arcpy.management.AddField(log_table, "VAL_COUNT", "SHORT")
arcpy.management.AddField(log_table, "VALUE", "TEXT")

with arcpy.da.InsertCursor(log_table, ["TABLE_NAME", "COLUMN_NAME", "VALUE", "VAL_COUNT"]) as cursor:
    for table in tables:
        result = check_for_junk_values(table)
        for key, count in result.items():
            col = key[0]
            val = key[1].__repr__()  # __repr__() to get the quotes
            cursor.insertRow([table, col, val, count])

 

This results in a log table which lists the found fields more precisely:

JohannesLindner_0-1670490978576.png

 


Have a great day!
Johannes
RobertKrisher
Esri Regular Contributor

If you're using ArcGIS Pro you can also use the data engineering tools to analyze the contents of your table. On of the statistics they could for fields is how many null values there are: https://pro.arcgis.com/en/pro-app/latest/help/analysis/geoprocessing/data-engineering/view-statistic....