4

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.

  • 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? – Tom Mar 1 '16 at 22:51
  • 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. – wittich Mar 1 '16 at 23:11
  • 2
    Could you provide a sample of what your data looks like and the intended output? – Aaron Mar 1 '16 at 23:14
  • 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. – Michael Stimson Mar 1 '16 at 23:31
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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.