Counting instances of non-zero fields in a record

2190
12
Jump to solution
07-29-2016 11:49 AM
by
New Contributor II

Referring to the table snippet above, each record has five fields for the last five fiscal years. The data will be either the number of that year or zero.

I need to calculate the Burns_5Yrs field based on the count of the number of non-zero years (in fields FY11 - FY15) in that record. The first three rows have examples manually entered. For example, the first record has activity in FY11 and FY14 so the result for Burns_5Yrs should be 2. The second row is also 2 and the third row is 4.

I've been doing this manually but now I have a table with 198 records so I'd like a programming solution, preferably in Python. Any help would be appreciated as I really don't know where to start.

Dave

Tags (1)
1 Solution

Accepted Solutions
MVP Esteemed Contributor

The code block can be bypassed completely by using the sum() function directly in the expression block:

`sum(1 for field in (!FY11!, !FY12!, !FY13!, !FY14!, !FY15!) if field)`
12 Replies
MVP Esteemed Contributor

Numpy has a direct solution, so get your data into an array either using tabletonumpyarray or featureclasstonumpyarray or whatever

```>>> a
array([[  0,    0, 2013,    0, 2015],
[2011, 2112, 2013,    0, 2015],
[2011,    0,    0, 2014, 2015],
[2011,    0, 2013, 2014,    0]])
>>> np.sum(np.where(a !=0, 1,0), axis=1)
array([2, 4, 3, 3])```
MVP Esteemed Contributor

David... marking threads as discussions rather than questions, if it is indeed a question, really limits your audience to those that view everything or just questions.  With the addition of Ideas, you have to limit your viewing to everything or one of the litany of options, with many selecting questions

MVP Honored Contributor

You can use an update cursor for this:

```>>> fc = 'points_test' # feature layer
... fields = ['FY11','FY12','FY13','FY14','FY15','Burns_5Yrs'] # your fields, with burns last
... with arcpy.da.UpdateCursor(fc,fields) as cursor: # create cursor
...     for row in cursor: # loop through features
...         row[-1] = sum(x > 0 for x in row[:-1]) # sum non-zeros, place in last position
...         cursor.updateRow(row) # write new value, after changing burns value```
MVP Honored Contributor

You can also use the field calculator:

Parser = Python

Expression:

`blah( !FY11! , !FY12! , !FY13! , !FY14! , !FY15! )`

Codeblock:

```def blah(f1,f2,f3,f4,f5):
params = locals() # dictionary of all arguments in function
my_sum = 0 # cumulative total
for k,v in params.iteritems(): # loop through dictionary
if v>0: # inspect value
my_sum += 1 # add to cumulative total
return my_sum # return total```
MVP Esteemed Contributor

don't do a VB sample or I will do one in Pandas

MVP Honored Contributor

What the heck...

Parser = VB Script

Expression:

`result`

Codeblock:

```result = 0
if [FY11] > 0 then
result = result + 1
end if
if [FY12] > 0 then
result = result + 1
end if
if [FY13] > 0 then
result = result + 1
end if
if [FY14] > 0 then
result = result + 1
end if
if [FY15] > 0 then
result = result + 1
end if```
MVP Esteemed Contributor

smartie now I have to find something other than an iPad poolside

MVP Honored Contributor

Don't spill your margarita in the laptop.

MVP Regular Contributor

This might simplify the field calculator code:

```def blah(f1,f2,f3,f4,f5):
params = locals() # dictionary of all arguments in function
return sum( v > 0 for k, v in params.iteritems())```

or just:

```def blah(f1,f2,f3,f4,f5):
return sum( v > 0 for k, v in locals().iteritems())```

I like the locals() function.  Thanks Darren.