Search Cursor Return Field Name If All Rows Contain a Value

Question

I'm trying to print the field name to a list if all rows in that field are not NULL. Here is the logic I have so far.

fiberCable = r'Orlando\Orlando_FIM_prep\FIBERCABLE'
list = []
with arcpy.da.SearchCursor(fiberCable, "inventory_status_code") as cursor:
     for row in cursor:
         if row[0] is not None:
             list.append(str(row[0]))

Using the logic on the table above it prints out this list:

['Preliminary Designed', 'DesignComplete', 'DesignComplete', 'DesignComplete', 'DesignComplete']

How do I get the search cursor to look at the row of values in the field and if there is a value present in all rows, print the field name, which in this case would be 'inventory_status_code'.

The outcome I would be looking for here is: ['inventory_status_code']

Answer 1

The most efficient way to do it (i.e., not reading every single row in the table) is to do this...see the comments for explanation:

import arcpy
fiberCable = r'Orlando\Orlando_FIM_prep\FIBERCABLE'
field = "inventory_status_code"
# Go ahead and put the field name into the output list
output_fields = [field]
# Compose a where clause to limit the cursor to iterate over records where the field is null
where_clause = '{} IS NULL'.format(field)
with arcpy.da.SearchCursor(fiberCable, field, where_clause) as cursor:
     # If any records are null, remove the field name from the output list and then break out of the loop.
     for row in cursor:
         output_fields.remove(field)
         break

Assuming that you want to do this for multiple fields:

import arcpy
fiberCable = r'Orlando\Orlando_FIM_prep\FIBERCABLE'
fields = ["inventory_status_code", 'another_field_name']
output_fields = list()
for field in fields:
    output_fields.append(field)
    where_clause = '{} IS NULL'.format(field)
    with arcpy.da.SearchCursor(fiberCable, field, where_clause) as cursor:
         for row in cursor:
             output_fields.remove(field)
             break

By the way, never ever ever use a python keyword of any kind as a variable name (e.g., list)

Answer 2

A quick way to do this might be to use a numpy array. You can easily convert your data to a numpy array with the arcpy.da.featureclasstonumpyarray tool.

Note, NULL is represented as NaN in numpy.

You can use the argwhere and isnan functions to locate positions in the array of null values.

For example, I created a numpy array as such:

a = np.array([[1,2,3,4],[1,2,3,np.NaN],[1,2,np.NaN,4]])

So, the 2D array looks like this:

[[1,2,3,4],
[1,2,3,np.NaN],
[1,2,np.NaN,4]]

Then, if I use the following line:

np.argwhere(np.isnan(a))[:,1]

I get the following result:

array([3, 2], dtype=int64)

Indicating that columns 3 and 2 contain NaN (NULL) values. Remember that the indexing starts at 0, so the first column is 0 and the last column is 3.

So, really, just two lines of code is all you need to quickly identify the columns containing the Null values.

Answer 3

This is a very simple task using Geopandas and harnessing Panda's ability to manipulate and query dataframes.

import geopandas as gp

gdf = gp.read_file('/temp/myshapefile.shp')
print(gdf.drop(gdf.columns[gdf.isna().all()].tolist(), axis=1).columns)

---

For example, the following prints all columns except for C which contains all None values:

In [1]: import geopandas as gp

In [2]: gdf = gp.read_file('/temp/myshapefile.shp')

In [3]: gdf
Out[3]: 
   Id  A      B     C                         geometry
0   0  1.65   0.00  None  POINT (1173731.7407 5354616.9386)
1   0  None   2.20  None  POINT (1114084.319 5337803.2708)
2   0  2.25   6.55  None  POINT (1118876.2311 5307167.5724)
3   0     0   0.00  None  POINT (1179707.5312 5313710.8389)

In [4]: print(gdf.drop(gdf.columns[gdf.isna().all()].tolist(), axis=1).columns)
Index(['Id', 'A', 'B', 'geometry'], dtype='object')