Count of fields in a row that satisfy a query

3332
8
Jump to solution
01-27-2017 02:50 PM
TessOldemeyer
Occasional Contributor

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!

Tags (4)
0 Kudos
1 Solution

Accepted Solutions
RandyBurton
MVP Regular Contributor

You could use the field calculator and something like:

def FieldCount(fld1,fld2, ...):
  fields = [fld1, fld2, ...] # field values to list
  return sum(f > 0 for f in fields)

FieldCount( !F01!, !F02!, ... )‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Or this for the function:

def FieldCount(fld1,fld2, ...):
 return sum(v > 0 for k, v in locals().iteritems())‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

View solution in original post

8 Replies
IanMurray
Frequent Contributor

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

BlakeTerhune
MVP Regular Contributor

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

In SQL, you can do something like

select count(*) from TableName where FieldName > 0

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
Frequent Contributor

Thanks for the link, adding to my bookmarks.

TessOldemeyer
Occasional Contributor

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

0 Kudos
BlakeTerhune
MVP Regular Contributor

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

0 Kudos
TessOldemeyer
Occasional Contributor

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! 

0 Kudos
RandyBurton
MVP Regular Contributor

You could use the field calculator and something like:

def FieldCount(fld1,fld2, ...):
  fields = [fld1, fld2, ...] # field values to list
  return sum(f > 0 for f in fields)

FieldCount( !F01!, !F02!, ... )‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Or this for the function:

def FieldCount(fld1,fld2, ...):
 return sum(v > 0 for k, v in locals().iteritems())‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

TessOldemeyer
Occasional Contributor

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

0 Kudos