Counting instances of non-zero fields in a record

2901
12
Jump to solution
07-29-2016 11:49 AM
DavidFox
New Contributor III

arcgis_table_snip.JPG

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)
0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
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)

View solution in original post

12 Replies
DanPatterson_Retired
MVP Emeritus

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])
0 Kudos
DanPatterson_Retired
MVP Emeritus

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

0 Kudos
DarrenWiens2
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
0 Kudos
DarrenWiens2
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
DanPatterson_Retired
MVP Emeritus

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

DarrenWiens2
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
DanPatterson_Retired
MVP Emeritus

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

0 Kudos
DarrenWiens2
MVP Honored Contributor

Don't spill your margarita in the laptop.

RandyBurton
MVP Alum

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.

0 Kudos