Calculating Cumulative Field Values in ArcMap using a case field in ArcGIS 10.4

Question

I want to calculate cumulative field values using an case field from another column in ArcGIS 10.4.

For example, if I have an "ID" column and "Count" column inside my attribute table, I want to calculate cumulative values for each ID separately.

I have used this:

Calculating Cumulative Field Values in ArcMap

I can do that if I select all rows with the same ID and run the field calculator, but that is a lot of manual work, so I want to use "ID" field as case field.

What is the best way to do that? Calculating Cumulative Field Values in ArcMap

This is my attribute table: https://www.image-maps.com/m/private/0/ls0pba738cdjbqik8c8k060pru_cumulative.png — user122678, May 09 '20 at 12:53

BERA · Accepted Answer · 2020-05-09T15:05:47.273

1

Although it is possible using for example dictionaries etc. (for example see: Numbering of objects based on several fields in ArcGIS with ArcPy) I would use pandas (which is included ArcMap >=10.4). When your data is in a pandas dataframe you can do any proccessing you can Think of.

import pandas as pd
import arcpy

fc = r"C:\GIS\data\testdata\ak_riks.shp"
fields = ['LANSKOD','area'] #field to group by, field with values to cumulative sum
field_to_calculate = 'cumsum'

df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor(fc,fields), columns=fields) #Create a pandas dataframe using da.SearchCursor
df2 = df.groupby(fields[0])[fields[1]].cumsum() #https://stackoverflow.com/questions/32847800/how-can-i-use-cumsum-within-a-group-in-pandas
givecumsum = iter(df2.to_list()) #Iterator to return one value at a time
#givecumsum = iter(list(df2)) #If above line does not work

with arcpy.da.UpdateCursor(fc,field_to_calculate) as cursor:
    for row in cursor:
        row[0] = next(givecumsum)
        cursor.updateRow(row)

Screenshot from Pro but the code for ArcMap is the same:

edited May 09 '20 at 15:05

answered May 09 '20 at 13:12

BERA

72,339
13
72
161

I have a .dbf table, not a shapefile, is there a difference in code? Also, is there a way to do that from the field calculator? – user122678 May 09 '20 at 13:34
Try it and see if it works – BERA May 09 '20 at 13:37
When I run the Python window and set my path and column names it doesn`t work. – user122678 May 09 '20 at 13:44
What could be a problem there? – user122678 May 09 '20 at 14:27
"Doesnt work" give no useful info so impossible to say. – BERA May 09 '20 at 14:28
Nothing happend when I paste the code and set my path and column names in Python window from ArcMap 10.4. – user122678 May 09 '20 at 14:30
How can you tell? Have you updated cache in ArcMap attribute table? – BERA May 09 '20 at 14:34
How to do that? How to update cache? – user122678 May 09 '20 at 14:36
I meant to say reload cache: https://desktop.arcgis.com/en/arcmap/10.3/manage-data/tables/refreshing-a-table.htm – BERA May 09 '20 at 14:37
This is the message now: Runtime error Traceback (most recent call last): File "", line 10, in File "C:\Python27\ArcGIS10.4\lib\site-packages\pandas\core\generic.py", line 2083, in getattr (type(self).name, name)) AttributeError: 'Series' object has no attribute 'to_list' – user122678 May 09 '20 at 14:40
That is helpful. I dont know why it does not work, but try: givecumsum = iter(list(df2)) instead of givecumsum = iter(df2.to_list()) – BERA May 09 '20 at 14:44

FelixIP · Answer 2 · 2020-05-10T00:03:01.793

I save my field calculator expressions in the same folder. They are easily located by name and this is why I often prefer them over scripts, especially when execution time is not an issue.

So with slight modification of this solution, accumulation by group can be achieved by using:

d={}
def GroupOrder(groupID,toSum):
  N=d.get(groupID,0);N+=toSum
  d[groupID]=N
  return N
#--------------
GroupOrder(!to_node!, !Shape_Length!)

Calculating Cumulative Field Values in ArcMap using a case field in ArcGIS 10.4

2 Answers2