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
Solved! Go to Solution.
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)
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])
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
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
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
don't do a VB sample or I will do one in Pandas
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
smartie now I have to find something other than an iPad poolside
Don't spill your margarita in the laptop.
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.