How do I use Python to edit a field in an attribute table?

Question

I have a few data tables full of information on salt water injection wells, with injection volumes given on a monthly basis. I need to add these monthly values up in order to get an annual value for each well to use on my map. Due to the sheer number of wells (about 1100 per data table), I would prefer to do this using a Python code rather than by hand.

How would I go about populating the new AnnualVolumes field with values calculated from another field?

I'm using ArcMap 10.3.

• Tom: So, you have a table for each month, and the wells are repeated across tables but not within tables? In what format are the tables (Excel, csv, geodatabase)? It sounds like you would then need to join these tables to some spatial feature class; in what format is that?
• wittich: Sounds not really like GIS problem, you might need just a Pivot Table to get the annual sum for each well. If you have to do for each file the same step I recommend a script for R GNU for the calculation. The final result per well you can then join into ArcMap.
• Aaron: Could you provide a sample of what your data looks like and the intended output?
• Michael Stimson: An attribute join on the pivot table should do the trick.. as @wittich said use Excel (or equivalent) to sum the data being sure to keep the field that identifies each point uniquely (you do have that don't you) in the pivot table then export/import to Microsoft (JET) Access, dBase IV or CSV - I've had problems with Excel files in the past and simplifying the table fixes most of the issues. Then join the table to the feature class and use calculate field to update the values... that's about as specific as I can be without seeing your data.

Answer 1

If you are looking for a solution within esri then I'd recommend using the field calculator this is a good tutorial. If the data is in the form of tables then you might be better of using excel or some open source equivalent and reimporting the tables after you have wrangled the data. Both are options that will solve your problem. What you are trying to do with the end calculations and the form of the datasets will dictate the tool.

Answer 2

Since it's coming as part of the standard python installation in ArcGIS Desktop 10.4, it's worth looking at Pandas. I'll use some data I grabbed from Melbourne Water's Rainfall and River Level data pages. The raw data is in this gist for those that want to follow along.

First I'll read the data from the CSV. If your data isn't in a CSV there are plenty of other ways to read data.

>>> import pandas as pd
>>> river_data = pd.read_csv("river_outflow.csv", parse_dates=["date"])
>>> print river_data.head()
 station date mm
0 Footscray 2015年08月03日 0.2
1 Footscray 2015年03月15日 0.0
2 Footscray 2015年03月22日 1.6
3 Footscray 2015年03月29日 0.8
4 Footscray 2015年05月04日 0.0

The parse_dates argument lets me automatically parse the date string in the CSV as a date object in pandas. Since the date field is now a datetime field I can goup by the year, month, or day, along with other attributes.

>>> print river_data.date.dt.month.head()
0 8
1 3
2 3
3 3
4 5
Name: date, dtype: int64
>>> aggregate = river_data.groupby(["station", river_data.date.dt.year]).sum()
>>> print aggregate
 mm
station date 
Footscray 2015 279.6
 2016 37.8
Mornington 2015 16925.0
 2016 223.0

Or if I want use the years as columns I can do a pivot table:

>>> aggregate = river_data.pivot_table("mm", "station", river_data.date.dt.year)
>>> print aggregate
date 2015 2016
station 
Footscray 6.502326 4.2
Mornington 528.906250 223.0

And then I can export the results to a file:

>>> aggregate.to_csv("aggregate.csv")

At that point it's a simple matter to join by the spatial name to my feature class in ArcMap.