1

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]))

enter image description here

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']

  • In order for the field to be printed, do all rows in the field have to not be NULL? How would you handle a case where one field had a single instance of NULL? – Aaron Jan 8 at 18:49
  • 1
    In order for the field to be printed, all rows have to contain a value, if a single row is NULL it should NOT print the field name, as it goes against the logic I'm trying to write. – GIS_GOD Jan 8 at 18:59
  • Thanks, I updated my answer to address your comments. – Aaron Jan 16 at 13:56
2

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)

  • Thanks for the help Tom, let me wrap my head around this and try to implement and I'll come back to vote this as the answer. – GIS_GOD Jan 8 at 19:01
  • How would I change the logic to do the inverse? Only print field names where all rows ARE NULL? – GIS_GOD Jan 8 at 19:15
  • 1
    @GIS_GOD, to keep fields where all rows are null, simply invert the where clause by adding the word NOT: where_clause = '{} IS NOT NULL'.format(field) – Tom Jan 8 at 19:21
  • Very helpful, last question. When I run this on multiple fields, as shown in your second part of the code, it seems to repeat the same field twice, could this have something do with the break being inside the loop? – GIS_GOD Jan 8 at 19:30
  • @GIS_GOD, the break is in the correct place. No matter where the break is, it wouldn't repeat the same field. I would double-check the field names you're passing into it. – Tom Jan 8 at 20:22
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.

0

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')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.