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

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 )

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Najděte více příspěvků označených jako

Accepted answers

All comments

WesMiller

Could you expand on your example so we may get a better understanding of what you are trying to do?

DanPatterson_Retired

previous link and advice can be found here if anyone is looking for background I need to automatically calculate a field based on values from previous rows

AdrianWelsh

It looks like clarity is needed on both of these threads. Is there a way to combine both of them in order to consolidate and alleviate confusion?

DanPatterson_Retired

Waiting for clarification ... but that is not in our hands

OLANIYANOLAKUNLE

Thanks for your reponse(s), what i want to achieve is to get the sum total of values from a field called population, but i really don't want the final sum, what i need is to get the sum as you loop through the rows (that's the population value) i.e if

row 1 = 23 (row 1 would be 23 as there's no other row/record before row1)

row 2 = 30 (row 2 would be 30 + the value in row 1)

row 3 = 22 (row 3 would be 22 + the value in row 2), till you reach the last row.

I also want to add a constraint whereby if the sum of the population values in the rows becomes 500, the next row's value would start from zero or the difference that makes the 500 from the previous row. Thanks

DanPatterson_Retired

the quickest way would be to use a spreadsheet since you can copy the formula as you need it. Since you don't seem to be doing thousands or rows nor have you indicated that this would be a daily calculation, it may be faster in the long run to set up a template spreadsheet, then bring it into Arc* for further work.

AdrianWelsh

It's such a bummer when the quickest way is to not use Python, but easier is easier... I agree with Dan.

JoshuaBixby

The first part of your request, if I understand it, seems straightforward enough, but I don't get the threshold condition. You state if the population values in the rows becomes 500 (do you mean exactly 500?), then the next row's value is either zero or some difference. Well, which is it, zero or the difference? Can you present an example like the one above but showing the 500 threshold value in action?

OLANIYANOLAKUNLE

Excel wont work as we intend to use the form in ArcGIS Online?

RebeccaStrauch__GISP

Can you use Python and create a gp tool? that is, do you have access to ArcGIS Server? I am not at a location that I can write /test Python script right now, but I had a similar need to looping thru records, and resetting a value every so often. In my case, I used a cursor, and as the value of a field changed, my value incremented. Once it hit 10, it would reset to one and start again.

so using a cursor and a variable to keep a running sum of your values...a quick test after each row in the cursor, reset as necessary. Of course, this assumes one time need....not real time as records are added, since unless you add a field, and store the sum in the field (which could be totally legit, if it helps).

a snippet of my code...althoug not what you are needing, might help demonstrate (posting...then will edit to format) edited on iPad...may not be quite right on indentation (sorry)

displayTrancsCnt = 1
displayID = 1
#   The next lines are within a cursor, use counters and sum with tests
displayTransCnt += 1  # increments to count 10 trans for each group
if displayTransCnt == 11:     # once count hits 11...
        displayTransCnt = 1    # ..reset the count
        displayID += 1         # ..increment the ID for next set of 10
        displayGrpCnt += 1     # ..increment the sets of 10, before repeating
if displayGrpCnt == 11:  # once Grp hits 10 sets.., 
        displayGrpCnt = 1      # ..resets to grp count to 1 again
        displayID = 1          # ..resets the ID to 1 again

OLANIYANOLAKUNLE

Thank you very much for your response, can you kindly relate the script above to feature layers instead as i can't really relate them as needed. Thanks

OLANIYANOLAKUNLE

My python script calculates only the value of the immediate previous record instead of calculating the values of all preceding records before that particular record. The example (the image) below is the undesired output;

While the image below here is what i want to achieve;

This is my python script i used;

import arcpy
from arcpy import da
CensusBldForm = "CensusBldForm"
Calculated_EA1=0
with arcpy.da.UpdateCursor(CensusBldForm, ["OBJECTID", "EstimatedPopulationinEA", "Calculated_EA"]) as cursor:
     for row in cursor:
          Calculated_EA2 = row[1]
          row[2] = (Calculated_EA1 + Calculated_EA2)
          Calculated_EA1 = Calculated_EA2
          cursor.updateRow(row)

Kindly assist me with the above please.

curtvprice

I think this is a little simpler.

I put the field list in a variable to keep the code block skinny (easier to read)

import arcpy 
CensusBldForm = "CensusBldForm" 
vsum = 0 
flds = ["OBJECTID", "EstimatedPopulationinEA", "Calculated_EA"]
with arcpy.da.UpdateCursor(CensusBldForm, flds) as cursor: 
    for row in cursor: 
          vsum = vsum + row[1]
          # a fancy way of the same thing follows
          # vsum += row[1]
          row[2] = vsum
          cursor.updateRow(row)

DanPatterson_Retired

yup... that is the cumulative sum

curtvprice

I also want to add a constraint whereby if the sum of the population values in the rows becomes 500, the next row's value would start from zero or the difference that makes the 500 from the previous row.

OLANIYAN OLAKUNLE - I think this will do what you want with the threshold reset, if I am understanding your description correctly:

    for row in cursor: 
          vsum = vsum + row[1]
          if vsum >= 500:
            vsum = vsum - 500
          row[2] = vsum
          cursor.updateRow(row)

["OBJECTID", "EstimatedPopulationinEA", "Calculated_EA"]

alculated_EA"]

OLANIYANOLAKUNLE

I got the error below when i incorporated the lines of code you suggested;

vsum = vsum + row[1]
TypeError: unsupported operand type(s) for +: 'float' and 'NoneType'

See my code;

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

What do you think i'm doing wrong?

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

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)

curtvprice

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

UPDATE made generic using a function and fixed bug

<SPAN class="keyword token">def</SPAN> <SPAN class="token function">cumfield</SPAN><SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> vfield<SPAN class="punctuation token">,</SPAN> cumfield<SPAN class="punctuation token">,</SPAN> thresh<SPAN class="operator token">=</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; vsum <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>UpdateCursor<SPAN class="punctuation token">(</SPAN>tbl<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN>vfield<SPAN class="punctuation token">,</SPAN> cumfield<SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> rows<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> rows<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">try</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vv <SPAN class="operator token">=</SPAN> float<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">except</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vv <SPAN class="operator token">=</SPAN> <SPAN class="number token">0</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> thresh <SPAN class="operator token">and</SPAN> <SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">(</SPAN>vsum <SPAN class="operator token">+</SPAN> vv<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">></SPAN> thresh<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> vv
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">else</SPAN><SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="operator token">=</SPAN> vsum <SPAN class="operator token">+</SPAN> vv
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; vsum <SPAN class="operator token">=</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rows<SPAN class="punctuation token">.</SPAN>updateRow<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

to use the function:

cumfield<SPAN class="punctuation token">(</SPAN><SPAN class="string token">"Form1"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"EstimatedPopulationinEA"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"Calculated_EA"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">500</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

Připojte se ke konverzaci

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.