topic Re: How do I check if a field in the attribute table contains a blank cell? in Data Management Questions

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

AhmedWaheed1 — Thu, 08 Dec 2022 07:07:51 GMT

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?

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

JohannesLindner — Thu, 08 Dec 2022 07:41:06 GMT

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, #...

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

Bud — Thu, 08 Dec 2022 08:32:59 GMT

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)

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

JohannesLindner — Thu, 08 Dec 2022 08:09:52 GMT

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.

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

JohannesLindner — Thu, 08 Dec 2022 09:17:14 GMT

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:

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

RobertKrisher — Thu, 08 Dec 2022 14:29:33 GMT

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-statistics.htm.