Create multiple graphs from large geodatabase table

ModernElectric · ‎08-23-2013

Greetings

     I have a rather large project I am trying to do and I hope I can get Python to do it for me. I have a file geodatabase table called "SERVICE METERS DEMAND" which is basically composed of the peak demand reading on an electric meter for a given month. I have the range of readings from November 2011 to March 2013. The table contains about 172,000 total records with almost 10,000 electric meters.

     What I want to be able to do is to take an individual meter by number (TWACs Number) and create a graph visually showing the peak demand for each month. This would be used by our engineering department and our meter techs for analysis and decisions making. The big thing is - I update this table about 4 times a year so my geodatabase can have the latest demand readings that other departments use. With that being said, I would like to create a script that can automatically create all new graphs and export them out to a .PDF format. I can than use the Attachment geoprocessing tools to link those .PDF graphs to the meter locations.

     Do you think this is something that can be done by Python? If so - how do I start this.

I currently have ArcMap 10.0 on one machine and in the process of testing a bunch of different tools/processes on ArcMap 10.2 on a different machine.

Thank you in advance.
Can send a sample of the SERVICE METERS DEMAND table as well

StacyRendall1 · ‎08-25-2013

This can definitely be done in Python, but you will have to learn a lot!

Here are a few bits to get you started:

SearchCursor (10.0), da.SearchCursor (10.2) allows you to iterate through tables and get values. You could store the values (date, reading) within lists in a Python dictionary by meter number.

Python datetime module (inbuilt) will be helpful for working with dates.

Matplotlib allows you to plot things from Python.

I don't know how to make PDFs from Python, but I have no doubt whatsoever that it is possible...

I would suggest you start playing around with matplotlib, datetime and searchcursors first, once you are familiar with them, start to think about the data structure that you will use to get the information from the searchcursor, analyse the monthly peak, and pass the result to matplotlib. Happy to help if you have more specific queries!

JamesCrandall · ‎08-26-2013

matplotlib is definitely a good option. We also incorporate pandas data frames to help shape and group data and date ranges. As a final step, Attachements are enabled and populated in a File Geodatabase.

Lots of possibilities and learning to do!

ModernElectric · ‎08-27-2013

Stacy-

Thank you for the ideas. I have playing around with the SearchCursor in Python and so far all I am getting it to do is to "Print" to the screen. What else would I have to do to extract this data out?

This can definitely be done in Python, but you will have to learn a lot!

Here are a few bits to get you started:

SearchCursor (10.0), da.SearchCursor (10.2) allows you to iterate through tables and get values. You could store the values (date, reading) within lists in a Python dictionary by meter number.

Python datetime module (inbuilt) will be helpful for working with dates.

Matplotlib allows you to plot things from Python.

I don't know how to make PDFs from Python, but I have no doubt whatsoever that it is possible...

I would suggest you start playing around with matplotlib, datetime and searchcursors first, once you are familiar with them, start to think about the data structure that you will use to get the information from the searchcursor, analyse the monthly peak, and pass the result to matplotlib. Happy to help if you have more specific queries!

JamesCrandall · ‎08-27-2013

Stacy-

Thank you for the ideas. I have playing around with the SearchCursor in Python and so far all I am getting it to do is to "Print" to the screen. What else would I have to do to extract this data out?

I would get my Search Cursor into a pandas data frame and use that to group and plot as needed.


FeatClassArray = []
fc = r"H:\Documents\ArcGIS\Default.gdb\MyFeatureClass"
fields = [f.name for f in arcpy.ListFields(fc)]   

with arcpy.da.SearchCursor(fc, fields) as cursor:
   for row in cursor:
       FeatClassArray.append(row)

df = DataFrame(FeatClassArray)

#now use the df with matplotlib functionality to build your plots/graphs

ModernElectric · ‎08-27-2013

Having a hard time getting multiple fields to show up.

I have about (4) fields that I need to show:
    TWACs_NUMBER
    PERIOD_ENDS
    KWH_USAGE
    KW_DEMAND

So Far - I can get the "TWACs_NUMBER to "print" to the screen - but I need all of these fields to "print" so I can sort them by TWACs NUMBER

ModernElectric · ‎08-27-2013

I think I should include an example of the data that I am using to better explain what I am trying to accomplish

Attached: Example.xlsx

This is also in a File Geodatabase as a Geodatabase Table
This table is composed of around 9500 meter records (METER NUMBER)

1) Go through the table and find METER NUMBER record with the billing period, KWH and KW Demand rows attached.

2) Extract/export/select a unique METER NUMBER with the data attached (billing period, KWH and KW Demand)

3) Take the data and build a graph showing the Billing Period along the "X" Axis, KWH and KW Demand along the "Y" Axis

4) Export the graph to a .PDF with the METER NUMBER as the file name

From there I can use the Attachments Geoprocessing tool to take the .PDF and attach it to the Meter Location in the .MXD

Long Term Goal is to create a One-Touch custom Button to select a specific METER NUMBER to create a graph described above and automatically create a .PDF.

I know this sounds like a huge project - and want to learn how to build it piece by piece to eventually accomplish the Long Term Goal.

Thanks for your help in advanced

StacyRendall1 · ‎08-27-2013

Having a hard time getting multiple fields to show up.

I have about (4) fields that I need to show:
    TWACs_NUMBER
    PERIOD_ENDS
    KWH_USAGE
    KW_DEMAND

So Far - I can get the "TWACs_NUMBER to "print" to the screen - but I need all of these fields to "print" so I can sort them by TWACs NUMBER

A few questions:

Please include your code so far.

What Arc version are you using?

Have you read and played with the examples included in the docs, which I attached in the last post?

To include code, simply copy it from your editor or the Python window in ArcMap/Catalog (if that is what you are using) then paste it in your reply. To preserve indentation and identify it as code, select all the code and click the # button on the forum writing tools at the top of the edit box.

StacyRendall1 · ‎08-27-2013

I think I should include an example of the data that I am using to better explain what I am trying to accomplish

Attached: Example.xlsx

This is also in a File Geodatabase as a Geodatabase Table
This table is composed of around 9500 meter records (METER NUMBER)

1) Go through the table and find METER NUMBER record with the billing period, KWH and KW Demand rows attached.

2) Extract/export/select a unique METER NUMBER with the data attached (billing period, KWH and KW Demand)

3) Take the data and build a graph showing the Billing Period along the "X" Axis, KWH and KW Demand along the "Y" Axis

4) Export the graph to a .PDF with the METER NUMBER as the file name

From there I can use the Attachments Geoprocessing tool to take the .PDF and attach it to the Meter Location in the .MXD

Long Term Goal is to create a One-Touch custom Button to select a specific METER NUMBER to create a graph described above and automatically create a .PDF.

I know this sounds like a huge project - and want to learn how to build it piece by piece to eventually accomplish the Long Term Goal.

Thanks for your help in advanced

Excellent! It is great that you have written out a plan, many people don't and then get hopelessly lost... It definitely looks possible.

Right now we'll get step 1 partly working, just printing to screen. Once that is good we can put the data in a useful data structure, rather than just printing it.

I think James' suggestion, of using Pandas, is good. I haven't used it myself, but I think it works pretty well with time data and plotting functions. So there is something else you will need to look up and have a play with...

Do you intend to only ever assess one meter at a time? You have a few options, but for now we may as well keep part 1 collecting all the data, and between part 1 and part 2 define the one METER NUMBER to make the graph of.

JakeSkinner · ‎08-28-2013

Here is an example on how to accomplish this. First, you will want to create a sample graph in ArcMap. Here is a screen shot of the parameters I used:

[ATTACH=CONFIG]27044[/ATTACH]

After the graph is created, right-click on it and save it out as a .grf file. Now you can use the below code to create a graph of the "KW Demand by Meter" by specifying a meter number:

import arcpy
from arcpy import env
env.overwriteOutput = 1
env.workspace = r"C:\temp\python\test.gdb"

table = "Meters"

arcpy.TableSelect_analysis(table, r"in_memory\table_sel", "METER_NUMBER = " + arcpy.GetParameterAsText(0))

out_graph_name = arcpy.GetParameterAsText(0)
out_graph_pdf = r"C:\Temp\python\Userdata" + "\\" + arcpy.GetParameterAsText(0) + ".pdf"
input_template = r"C:\Temp\Python\UserData\KW Demand.grf" #grf file previously saved
input_data = r"in_memory\table_sel"

# Create the graph
graph = arcpy.Graph()

# Add a vertical bar series to the graph
graph.addSeriesBarVertical(input_data, "KW_DEMAND")

# Specify the title of the left axis
graph.graphAxis[0].title = "KW Demand"

# Specify the title of the bottom axis
graph.graphAxis[2].title = "Meter Number"

# Specify the title of the Graph
graph.graphPropsGeneral.title = "KW Demand by Meter"

# Output a graph, which is created in-memory
arcpy.MakeGraph_management(input_template, graph, out_graph_name)

# Save the graph as an image
arcpy.SaveGraph_management(out_graph_name, out_graph_pdf, "MAINTAIN_ASPECT_RATIO", 600, 375)