topic Re: Counting instances of non-zero fields in a record in Python Questions

Counting instances of non-zero fields in a record

DavidFox — Fri, 29 Jul 2016 18:49:20 GMT

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

Re: Counting instances of non-zero fields in a record

DanPatterson_Retired — Sat, 11 Dec 2021 17:49:11 GMT

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

Re: Counting instances of non-zero fields in a record

DanPatterson_Retired — Fri, 29 Jul 2016 21:18:11 GMT

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

Re: Counting instances of non-zero fields in a record

DarrenWiens2 — Sat, 11 Dec 2021 17:49:14 GMT

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

Re: Counting instances of non-zero fields in a record

DarrenWiens2 — Sat, 11 Dec 2021 17:49:18 GMT

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

Re: Counting instances of non-zero fields in a record

DanPatterson_Retired — Fri, 29 Jul 2016 21:55:59 GMT

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

Re: Counting instances of non-zero fields in a record

DarrenWiens2 — Sat, 11 Dec 2021 17:49:20 GMT

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

Re: Counting instances of non-zero fields in a record

DanPatterson_Retired — Fri, 29 Jul 2016 22:11:26 GMT

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

Re: Counting instances of non-zero fields in a record

DarrenWiens2 — Fri, 29 Jul 2016 22:38:24 GMT

Don't spill your margarita in the laptop.

Re: Counting instances of non-zero fields in a record

RandyBurton — Sat, 11 Dec 2021 17:49:23 GMT

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.

Re: Counting instances of non-zero fields in a record

DanPatterson_Retired — Sat, 30 Jul 2016 11:14:59 GMT

Pandas was too clunky, so numpy in the field calculator... with pictures

The only thing to remember

always choose the python parser... and there is no way to set it to default
write a function... doesn't need to be fancy.
any imports are done once, so if numpy is already imported, it is only imported once
the function just contains the variables which will be used. In this example, I used the field names without the ! marks. There is no requirement for the variables to have the same field names
in the expression box, you put function name (ie 'r') plus the variables to pass. The field names are being passed so enclose them in ! marks.
save your function for later use if you like

That's all

Re: Counting instances of non-zero fields in a record

JoshuaBixby — Sat, 30 Jul 2016 15:26:54 GMT

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)

Re: Counting instances of non-zero fields in a record

DavidFox — Wed, 24 Aug 2016 19:11:39 GMT

Thanks, this worked perfectly (after I used the actual field names instead of the aliases).