Counting instances of non-zero fields in a record

1932
12
Jump to solution
07-29-2016 11:49 AM
DavidFox
New Contributor II

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

0 Kudos
DanPatterson_Retired
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

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

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

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

0 Kudos