I need to automatically calculate a field based on values from previous rows

[OLANIYANOLAKUNLE]

• I need to automatically calculate a field based on values from previous rows?I need to automatically calculate a field based on values from previous rows such that once the total of all the previous rows is greater than 450 and less than 500, the next row starts counting from 0 or the balance of what made the previous row greater than 500. The script below just calculates the sum of the cities point that falls within the county, I want something that would make the value of a row (i.e. row2) the addition of the value of row1 + value of row2 and so on till when it gets to 500, where it starts from 1 again. Kindly help me please.

[DanPatterson_Retired]

either vsum or row[1] is None and can be ce added together, so it appears row[1], which means you have None's in your column? have you confirmed and is the field numeric.  You could try

if row[1] not None:

    vsum = vsum + row[1] #  or just vsum += row[1]

[OLANIYANOLAKUNLE]

This was what i got when i tried the script

And this is the script below;

CensusBldForm = "SCensusBldForm"  

vsum = 0  

flds = ["OBJECTID", "EstimatedPopulationinEA", "Calculated_EA"] 

with arcpy.da.UpdateCursor(CensusBldForm, flds) as cursor:  

   for row in cursor:  

       vsum += row[1]

       if vsum >= 500: 

          vsum = vsum - 500 

          row[2] = vsum 

          cursor.updateRow(row)

[DanPatterson_Retired]

are youdoing this in the field calculator or in a script...it appears to have worked because 0 would be put in the table if a None was found.  You should visit your threads more often, I have long forgotten the problem

[OLANIYANOLAKUNLE]

I'm actually running it from a script. This is what it should look like really;

When the totaling reaches the 500 threshold, it should then start again from zero (0). 

[OLANIYANOLAKUNLE]

Evening Dan, i know its a long time since i started this thread your code works but it leaves zeros instead of the actual total considering the concatenations from the previous row. Please kindly check my code out so as to see if you can enhance it further?

Form1 = "Form1"
Calculated_EA1=0
with arcpy.da.UpdateCursor(Form1, ["OBJECTID", "EstimatedPopulationinEA", "Calculated_EA"]) as cursor:
for row in cursor:
   Calculated_EA2 = row[1]
   row[2] = (Calculated_EA1 + Calculated_EA2)
   Calculated_EA1 += Calculated_EA2
if row[2] >= 500:
   row[2] = Calculated_EA2 - 500
   cursor.updateRow(row)

[DanPatterson_Retired]

please format your code /blogs/dan_patterson/2016/08/14/script-formatting 

[curtvprice]

This code will work if you  have None values in your field.

UPDATE made generic using a function and fixed bug

def cumfield(tbl, vfield, cumfield, thresh=0):
    vsum = 0
    with arcpy.da.UpdateCursor(tbl, [vfield, cumfield]) as rows:
        for row in rows:
            try:
                 vv = float(row[0])
            except:
                 vv = 0
            if thresh and ((vsum + vv) > thresh):
                 row[1] = vv
            else:
                 row[1] = vsum + vv
            vsum = row[1]
            rows.updateRow(row)‍‍‍‍‍‍‍‍‍‍‍‍‍‍

to use the function:

cumfield("Form1", "EstimatedPopulationinEA", "Calculated_EA", 500) ‍

[OLANIYANOLAKUNLE]

Thanks a MILLION!!!!

[OLANIYANOLAKUNLE]

You're the MAN! Thanks