nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Get a count of field values

JoeBorgione

I'm working with some data that is a ridiculously large table (184 fields) and I would like to asses how many instances of of each field are non-null. In other words if the values are more more often null, I'll drop the field(s). For now I've written a script that performs an iterative selection for each field, and as you can imagine with 184 fields and a few thousand records it's pretty slow. I tried a couple other approaches that failed, but I have to think there is a better way to get a count of records for which a given field is populated. Here is what I've done to date:

<SPAN class="keyword token">import</SPAN> arcpy

table <SPAN class="operator token">=</SPAN> r<SPAN class="string token">'J:\some\path\to\file.gdb\tableName'</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>

<SPAN class="keyword token">for</SPAN> f <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>ListFields<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
    fields<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">.</SPAN>name<SPAN class="punctuation token">)</SPAN>
 
arcpy<SPAN class="punctuation token">.</SPAN>MakeTableView_management<SPAN class="punctuation token">(</SPAN>table<SPAN class="punctuation token">,</SPAN><SPAN class="string token">'tv'</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="keyword token">for</SPAN> f <SPAN class="keyword token">in</SPAN> fields<SPAN class="punctuation token">:</SPAN>
    select <SPAN class="operator token">=</SPAN> f<SPAN class="string token">'{f} is not null'</SPAN>
    arcpy<SPAN class="punctuation token">.</SPAN>SelectLayerByAttribute_management<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'tv'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'NEW_SELECTION'</SPAN><SPAN class="punctuation token">,</SPAN>select<SPAN class="punctuation token">)</SPAN>
    c <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>GetCount_management<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'tv'</SPAN><SPAN class="punctuation token">)</SPAN>
    
    <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Field {} has {} non-null records'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>f<SPAN class="punctuation token">,</SPAN>c<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Finde mehr Beiträge mit dem Tag

Accepted answers

All comments

JoshuaBixby

Your current approach is the most "ArcGIS" way, i.e., using ArcGIS geoprocessing tools, of doing it. The slowness is caused more by having 184 fields, which is a ridiculously excessive number of fields even for a data warehouse, than your approach.

If memory is abundant, you could load the whole dataset into a Python or NumPy data structure and then count the number of non-null values. Counting in a Python or NumPy data structure should be much faster than your current approach, but you have to be able to load all the data into memory or paging will slow it down.

HannesZiegler

Hi Joe,

As also stated by Joshua, iterating through 184 fields in arcpy is going to be slow. I suggest you leverage the arcgis module and the Spatially Enabled Data Frame (extension of pandas.DataFrame), maybe something like this:

infc = r"Path\To\Your\Data"
sedf = pandas.DataFrame.spatial.from_featureclass(infc) #Creates a pandas.DataFrame from your featureclass
idx = sedf.isnull() # Returns an index that is True for None, False otherwise
idx.sum() # True = 1, False = 0; idx.sum() will summarize each column to tell you how many null values are in that column

That will give you something like this:

<SPAN>OBJECTID    0 
ORIG_FID    0 
DATA        8 
SHAPE       0 
dtype: int64</SPAN>

DanPatterson_Retired

Small demo... you should use

flds = "*"

flds <SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Parts'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Int_nulls'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Float_nulls'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Text_nulls'</SPAN><SPAN class="punctuation token">]</SPAN>

d0 <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>TableToNumPyArray<SPAN class="punctuation token">(</SPAN>in_fc<SPAN class="punctuation token">,</SPAN> field_names<SPAN class="operator token">=</SPAN>flds<SPAN class="punctuation token">,</SPAN> skip_nulls<SPAN class="operator token">=</SPAN><SPAN class="token boolean">False</SPAN><SPAN class="punctuation token">,</SPAN> null_value<SPAN class="operator token">=</SPAN>nd<SPAN class="punctuation token">)</SPAN>

d1 <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>TableToNumPyArray<SPAN class="punctuation token">(</SPAN>in_fc<SPAN class="punctuation token">,</SPAN> field_names<SPAN class="operator token">=</SPAN>flds<SPAN class="punctuation token">,</SPAN> skip_nulls<SPAN class="operator token">=</SPAN><SPAN class="token boolean">True</SPAN><SPAN class="punctuation token">)</SPAN>

d0
 
array<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN>    <SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN>      nan<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'a'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN>
       <SPAN class="punctuation token">(</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">999</SPAN><SPAN class="punctuation token">,</SPAN>     <SPAN class="number token">6.10</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'None'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN>
       <SPAN class="punctuation token">(</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="operator token">-</SPAN><SPAN class="number token">999</SPAN><SPAN class="punctuation token">,</SPAN>     <SPAN class="number token">2.10</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'None'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>
      dtype<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Parts'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<i4'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Int_nulls'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<i4'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN>
             <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Float_nulls'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<f8'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Text_nulls'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<U5'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
d1
array<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>
      dtype<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Parts'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<i4'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Int_nulls'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<i4'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN>
             <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Float_nulls'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<f8'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="string token">'Text_nulls'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'<U5'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>

<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

A table with 3 records and I samples some fields, I have a function to convert nulls of any kind so that they are converted from <null> to something useful (I blogged about this).

BUT!!! if you dump the nulls versus you keep the nulls

d1.shape
(0,)

d0.shape
(3,)<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Way ******** faster than a search cursor

MikeVolz

Is your solution done in Pro using only default python modules or did you need to add additional modules using the clone? If needing the clone, what is the module(s) that you needed to add to execute your solution?

DanPatterson_Retired

numpy comes with pro... in fact arcgis pro requires numpy, so does the arcgis module, and pandas scipy etc etc

this can be done with or without a clone.

Of course, as in all modules, you have to import numpy which is usually do as

<SPAN class="keyword token">import</SPAN> numpy <SPAN class="keyword token">as</SPAN> np<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

most python IDEs allow you to set default imports if you feel lazy or are forgetful.

dougbrowning

I wonder if just loading into memory then used the Arc way would be faster also.

arcpy.FeatureClassToFeatureClass_conversion(old.gdb, "in_memory", "tempFC")

DanPatterson_Retired

doubtful since it doesn't write to memory

Get Count—Data Management toolbox | ArcGIS Desktop

JoshuaBixby

You raise an interesting point/question. At first, I thought the same as Dan but for different reasons. Given the way that Windows 10 caches files in spare system memory, I thought there was a good chance that running Select Layer by Attribute on the same data set over and over would be hitting a cache and therefore not sped up much by moving the data to in-memory. It turns out, I was wrong.

I did some testing on a feature class with a handful of columns but millions of rows of data. I created a feature layer and then ran successive "IS NOT NULL" selections against the columns. It turns out the first run was a bit slower but negligibly so. All of the selections took about 8 seconds. I then copied the feature class into memory workspace and repeated the test, and the selection ran in about 0.2 seconds. So, copying the data into memory made a huge difference.

The only caveat I would add is that in-memory won't help nearly as much if the data set can't actually fit in RAM. If copying the data set results in ArcGIS paging some of the data to disk because there isn't enough RAM, the selections could be even slower than leaving them in the file GDB.

dougbrowning

Well I knew it would work since I do it all the time . Arc is very chatty on the network (or hard drive). So moving it to RAM eliminates all those calls to hardware. Plus I guessed he was hitting SDE which means a network call, a server call, and a hard drive spin.

To fit it in RAM use PyScripter 64 bit.

Hope that helps the OP.

wwnde

@hziegler-esristaff way is what I have always used. ArcPro 2.15 now allows toggling between desktop and spatially enabled dataframes. Additionally from the python API you can now launch, code within spatially referenced dataframes and save and share as an item in ArcOnline. Apart from using numpy, can also use pandas.

import pandas as pd
df = pd.read_csv(r'file directory')
df.isna().sum() # for entire dataframe
df.fieldname.isna()

The advantage with spatially dataframes is it levarages numpy and pandas to access statistical abilities that can otherwise be accessed 'cumbersomely' in arcpy. It also accords excellent visualization in matplotlib and seaborn python libraries.

Nimm an der Diskussion teil

Angemeldete Mitglieder können Beiträge verfassen, Updates folgen und mehr. Neu hier? Registriere ein kostenloses Konto.

Schnellzugriffe

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.