topic Cumulative Sum by year? in Python Questions

Cumulative Sum by year?

SpencerMeyer — Wed, 09 Jan 2013 14:27:01 GMT

Hello,

I have a table that is something like:

Object    Year    Acres
1            1980   5
2            1979   3
3            1978   2
4            1980   8
5            1979   2
6            1976   6

I would like to use python to create a cumulative sum so that I end up with a table that looks like:
1976       6
1978       2
1979       5
1980       13

I'm going to be graphing this using matplotlib, showing trends in acres over time.

I'm a beginner with python. So far I've tried to use FeatureClasstoNumPyArray and numpy's cumsum, but I can't get the array structured correctly to sum by year. I think part of my problem is that I don't really understand how an attribute table gets converted to an array.

If numpy is the wrong approach, pleases let me know. My requirements are that I can graph cumulative acres over time from a dataset that has polygons of various sizes occuring in specific years. If it matters for processing time, this dataset has about 140,000 records. Also, if you have any suggestions for python graphing modules or sample code that will do with kind of thing well, I'd like to know about them.

thanks!

Re: Cumulative Sum by year?

T__WayneWhitley — Wed, 09 Jan 2013 15:16:51 GMT

You're making this harder than it needs to be... use the Summary Statistics tool (or the Frequency tool if you prefer and licensed for it).

You can simply execute the tool from Toolbox or script it, up to you, but it's the only tool you will need to perform this function...could also use a cursor, but it likely isn't necessary...

EDIT- ...so Year would be your 'case' and Acres would be your 'statistic' to sum for.

Re: Cumulative Sum by year?

SpencerMeyer — Wed, 09 Jan 2013 15:23:28 GMT

Hello,

I would like to use python to create a cumulative sum so that I end up with a table that looks like:
1976       6
1978       2
1979       5
1980       13

Actually, I meant THIS is what I want:
1976       6
1978       8
1979       13
1980       26

Summary stats gets me the first part, but then I need to do a cumulative sum by year so I can graph such that the x-axis is the year, and y-axis is total, cumulative acres.

I'm sorry I didn't explain it correctly the first time. Thanks for your quick response.

Re: Cumulative Sum by year?

T__WayneWhitley — Wed, 09 Jan 2013 15:34:39 GMT

Oh I see, looks like you are sorting by year (which would make sense on an axis)....this is a bit of a workaround, but it will work if you take the table result of the Summary Statistics output and input that into a sorted update cursor (by year), then in a new field calculate the previous record acreage + the current record acreage... make sense?

In other words, use a variable to 'hold' the last record's acreage value + the cummulative acreage to this point in the processing --- so in your example initiating the update cursor to the 1st record, you can fetch the 1st acreage value: 6 (initiating the variable to 0, setvalue for the new 'cummulative value' field to 0)

Iterate over the records, the next record value is for 1978 is 2 (using getvalue) --- add that to your current var value of 6, that's 6 + 2 = 8. setvalue, update the var to 8, go to the next row...

Next row is for 1979 is 5, so 8 + 5 = 13...

Get it? The code is easy to write, see the webhelp examples for cursor processing...post back if you get stuck.

-Wayne

Re: Cumulative Sum by year?

T__WayneWhitley — Sat, 11 Dec 2021 20:51:30 GMT

...in short, see the code below (also the webhelp link below that will help explain). Note that comments to aid you in following the code are preceded by the '#' symbol. Sorry, I forgot you said you were just beginning to use Python.

# import the module, arcpy...
import arcpy

# Set the current workspace environment variable.
arcpy.env.workspace = r'C:\Documents and Settings\whitley-wayne\My Documents\ArcGIS\Default.gdb'

# I set up a 'mock' table representing the summary output table,
# called 'SummaryStatsOutput'.  Set the table variable 'tbl' to that...
tbl = 'SummaryStatsOutput'

# Define another variable 'accumVal' to represent accumulated ACREAGE.
# Initiate it to zero (0).
accumVal = 0

# Establish the 'rows' cursor variable on tbl, which takes 5 parameters -
# The 3 middle ones are 'blank'...
# The last one is the sort parameter - sorting by YEAR...
# (since my mock table was not in order).
rows = arcpy.UpdateCursor(tbl, '', '', '', 'YEAR A')

# looping on 'row' objects contained in the 'rows' cursor object...
for row in rows:
     # Adding the current 'getValue' fetch from the ACREAGE field to accumVal.
     # (This is '0' for 1st record).
     accumVal = accumVal + row.getValue('ACREAGE')
     # Setting the ACREAGE field to the 'new' accumVal.
     # (a new field wasn't necessary)
     row.setValue('ACREAGE', accumVal)
     # Updating the row object within the cursor object
     # (committing the changes)
     rows.updateRow(row)

# Outside the loop, delete the objects to remove lock reference on table
del row, rows

Here is the 10 webhelp for the update cursor:
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//000v0000003m000000

Re: Cumulative Sum by year?

JamesCrandall — Wed, 09 Jan 2013 16:32:44 GMT

Check out the pandas library -- it is very slick for time-series work. Plus, you can integrate with your matplotlib work too.

http://pandas.pydata.org/pandas-docs/dev/timeseries.html

Re: Cumulative Sum by year?

JamesCrandall — Sat, 11 Dec 2021 20:51:33 GMT

Tip: if you plan to use matplotlib to generate many figures from inside of a loop, make sure to close the figure each time you are finished saving it out!

plt.figure()

#...plotting code here

plt.savefig(out)
plt.close()

If you don't you will get severe memory leak and the ArcGIS software you are running the python script from will bomb out. In my case I am running this as an ArcToolbox script and ArcCatalog would shutdown after a few hundred figures were generated.

Re: Cumulative Sum by year?

ChrisSnyder — Sat, 11 Dec 2021 20:51:35 GMT

In Python (and using the data access cursor model in ArcGIS v10.1) the code would look like this... Basically a re-write of the 'SUM' functionality of summary statistics tool using da cursors and a Python Dictionary. You would then use an insertCursor to write the values from 'summaryDict' back to a table on disk (not shown) or maybe you could hand the values in the dictionary straight to matplotlib?

myTable = r"C:\test.gdb\my_table"
summaryDict = {}
searchRows = arcpy.da.SearchCursor(myTable, ["YEAR","ACRES"])
for searchRow in searchRows:
    yearValue, acresValue = searchRow
    if yearValue in summaryDict:
        summaryDict[yearValue] = summaryDict[yearValue] + acresValue
    else:
        summaryDict[yearValue] = acresValue
yearKeys = summaryDict.keys()
yearKeys.sort() #sort the years in ascending order
#Print some output
for yearValue in yearKeys:
    print str(yearValue) + " = " + str(summaryDict[yearValue]) + " acres"

Re: Cumulative Sum by year?

T__WayneWhitley — Thu, 10 Jan 2013 18:20:47 GMT

Beautiful!

Re: Cumulative Sum by year?

MatthewHowe — Tue, 05 Jul 2016 07:45:09 GMT

Great stuff thanks! I'm hoping to use this to create creaming curves for oil and gas exploration. What I have noticed is that it creates totals of a value per yer but I think the main aim was to create cumulative totals. As an example I used this code on my data:

2011 = 35.0 mmboe

2012 = 616.666667 mmboe

2013 = 389.166667 mmboe

2014 = 348.333333 mmboe

2015 = 107.5 mmboe

2016 = 433.333333 mmboe

Of which is correct as it creates totals for each year. Is it possible to create a cumulative total of this so each previous year is added to the total of the following year? I then want to plot cumulative curves using matplotlib.

Re: Cumulative Sum by year?

JoshuaBixby — Sat, 11 Dec 2021 20:51:38 GMT

The code by Chris Snyder has all of the information already available in the summaryDict, you just need to add some logic to the final loop to grab the previous year in addition to the current year. Moving to a defaultdict instead of a regular dictionary allows the original code to be simplified some.

from collections import defaultdict
myTable = r"C:\test.gdb\my_table"
summaryDict = defaultdict(float)
searchRows = arcpy.da.SearchCursor(myTable, ["YEAR","ACRES"])
for yearValue, acresValue in searchRows:
    summaryDict[yearValue] += acresValue 
    
#Print some output  
for yearValue in sorted(summaryDict):  
    print str(yearValue) + " = " + str(summaryDict[yearValue] + summaryDict[yearValue - 1]) + " acres"

Re: Cumulative Sum by year?

RichardFairhurst — Sat, 11 Dec 2021 20:51:41 GMT

I think the way you have written the last part does not accumulated the values from the first key of the dictionary to the last. It only seems to add the current and prior year, not the current and all prior years. I think the last part needs a variable outside the loop to accumulate the summary values for each successive sorted year as shown below:

#Print some output

cum = 0
for yearValue in sorted(summaryDict):
    cum += summaryDict[yearValue]    
    print '{0} = {1} acres'.format(str(yearValue), str(cum))

This has the benefit of not needing to assume that each year key has a prior year value, which allows years to be skipped if the data did not have every year (and the first year should throw an error with Joshua's code as shown since there is no prior year key in the dictionary for the first year, but it still needs to print).

Re: Cumulative Sum by year?

JoshuaBixby — Wed, 06 Jul 2016 01:14:09 GMT

Richard Fairhurst, you are correct, my code is only summing a given year and its previous year because I believe that is what was asked: "Is it possible to create a cumulative total of this so each previous year is added to the total of the following year?" Although the word "cumulative" was used, I wasn't sure whether the intent was a running total over all years or a moving two-year total. If the former, something like your code would be needed; if the latter, mine will work. Maybe Chris Snyder could clarify.

Regarding my code, since I am using a defaultdict instead of a regular dictionary, it will not err on summing the first year to a non-existent prior year. Since the defaultdict is initialized with float, any non-existent year will get created with a value of zero.

Re: Cumulative Sum by year?

MinhazulIslam919 — Fri, 08 Dec 2023 04:37:21 GMT

Thanks a lot for the tip. It worked for me. Easy and neat.