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; } } }

Count of fields in a row that satisfy a query

TessOldemeyer

I am wanting to calculate the count of fields in a row where designated fields in a feature class attribute table satisfy a query (in this case, where the value is greater than 0) and then write the count (sum) of the number of fields that satisfy this query into a new field.

This seems simple, but I am having a hard time wrapping my head around how to do this using SQL or Python. Any suggestions or advice would be much appreciated.

Thanks in advance!

Find more posts tagged with

Accepted answers

RandyBurton

You could use the field calculator and something like:

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">FieldCount</SPAN><SPAN class="punctuation token">(</SPAN>fld1<SPAN class="punctuation token">,</SPAN>fld2<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp; fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>fld1<SPAN class="punctuation token">,</SPAN> fld2<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># field values to list</SPAN>
&nbsp; <SPAN class="keyword token">return</SPAN> sum<SPAN class="punctuation token">(</SPAN>f <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN> <SPAN class="keyword token">for</SPAN> f <SPAN class="keyword token">in</SPAN> fields<SPAN class="punctuation token">)</SPAN>

FieldCount<SPAN class="punctuation token">(</SPAN> !F01!<SPAN class="punctuation token">,</SPAN> !F02!<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">.</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>

Or this for the function:

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">FieldCount</SPAN><SPAN class="punctuation token">(</SPAN>fld1<SPAN class="punctuation token">,</SPAN>fld2<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
 <SPAN class="keyword token">return</SPAN> sum<SPAN class="punctuation token">(</SPAN>v <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN> <SPAN class="keyword token">for</SPAN> k<SPAN class="punctuation token">,</SPAN> v <SPAN class="keyword token">in</SPAN> locals<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>iteritems<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>

Also look at Joshua Bixby‌'s answer in this thread.

All comments

TessOldemeyer

I was looking to get the sum of the number of particular fields in a row that had a value greater than zero (to find how many of these unique attributes each feature had, e.g. ). I think Randy hit on exactly what I was looking for. Thanks a bunch!

TessOldemeyer

Thank you so much. This is exactly what I was looking for! Works great!

RandyBurton

You could use the field calculator and something like:

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">FieldCount</SPAN><SPAN class="punctuation token">(</SPAN>fld1<SPAN class="punctuation token">,</SPAN>fld2<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp; fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>fld1<SPAN class="punctuation token">,</SPAN> fld2<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="comment token"># field values to list</SPAN>
&nbsp; <SPAN class="keyword token">return</SPAN> sum<SPAN class="punctuation token">(</SPAN>f <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN> <SPAN class="keyword token">for</SPAN> f <SPAN class="keyword token">in</SPAN> fields<SPAN class="punctuation token">)</SPAN>

FieldCount<SPAN class="punctuation token">(</SPAN> !F01!<SPAN class="punctuation token">,</SPAN> !F02!<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">.</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>

Or this for the function:

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">FieldCount</SPAN><SPAN class="punctuation token">(</SPAN>fld1<SPAN class="punctuation token">,</SPAN>fld2<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
 <SPAN class="keyword token">return</SPAN> sum<SPAN class="punctuation token">(</SPAN>v <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN> <SPAN class="keyword token">for</SPAN> k<SPAN class="punctuation token">,</SPAN> v <SPAN class="keyword token">in</SPAN> locals<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">.</SPAN>iteritems<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>

Also look at Joshua Bixby‌'s answer in this thread.

blake.terhune

Some sample data would help. I think I understand but it is a very unusual request and I want to make sure.

TessOldemeyer

Nope, I meant the count of fields per row (per feature) where designated fields satisfy a query. Here I am looking for the the count of fields (this would only pertain to 5 of the 40 fields in the feature class) which have a value greater than zero. I would also then like to write the count (sum) of the number of fields that satisfy this query into a new field, so this new field would have a count of 0 (or NULL) or an integer from 1 to 5.

Does this help? I apologize for the lack of clarity. Thanks for the help! I would also like to eventually integrate this into a Python script (eventually).

IanMurray

Thanks for the link, adding to my bookmarks.

blake.terhune

In Python, Joshua Bixby‌ covers the options in this blog post.

In SQL, you can do something like

<SPAN class="keyword token">select</SPAN> <SPAN class="token function">count</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="operator token">*</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">from</SPAN> TableName <SPAN class="keyword token">where</SPAN> FieldName <SPAN class="operator token">></SPAN> <SPAN class="number token">0</SPAN>‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

EDIT:

Just to clarify your question, I will edit it for what I think you meant to say...

I am wanting to calculate the count of rows in a field where designated fields in a feature class attribute table satisfy a query (in this case, where the value is greater than 0) and then write the count (sum) of the number of rows that satisfy this query into a new field.

IanMurray

Have you looked at using cursors to pull the values of each value of each field you need in a row to check see if it is greater than 0?

https://pro.arcgis.com/en/pro-app/arcpy/data-access/updatecursor-class.htm

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

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