Production of Graphs from Geodatabase Table

[ModernElectric]

What I have is a table I produced from our Customer Information System showing the billed KWH Usage and KW Demand for a ALL electric meters over a period of time (From July 2011 to July 2013). There are about 900,000 records in this table of around 9500 electric meters. What I am trying to accomplish is listed below:

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

[DaveBarrett]

Hi,

Have part of an answer for you, the code below will allow you to extract out the information based on the unique billing numbers within your table. It doesn't yet create and export the graphs but more on that in a  minute. Here is the code for extracting the unique billing tables.

## extract unique records from a gdb table and make mulitple tables for
## graphing

import arcpy, os

arcpy.env.overwriteOutput = True

table = r'H:\Python\test.gdb\billing'

fields = ['BILLING_PERIOD','METER_NUMBER','KWH','KW_DEMAND']

# empty list to store unique meter numbers
meterNumbers = []

# use a search cursor to extract the unique numbers
with arcpy.da.SearchCursor(table,fields) as cursor:
    for row in cursor:
        if not row[1] in meterNumbers:
            meterNumbers.append(row[1])

# for each unique cursor make a table view using the billing number for
# the selection
for meter in meterNumbers:
    ## omit the quotes if you table stores the meter number as a number not a string
    expression = arcpy.AddFieldDelimiters(table,'METER_NUMBER') + " = " + "'" + meter + "'"
    arcpy.MakeTableView_management(table,"CurrentMeter",expression)
    # copy the rows to a permanent table in the same location
    outTable = os.path.join(os.path.dirname(table),("meter_" + meter))
    arcpy.CopyRows_management("CurrentMeter",outTable)

    ## at this point can use the graphing options to generate the graphs and save
    ## unfortunately don't have the answer yet for this

the copy rows at the end is not strictly need as you can do the graph creation in memory for each billing table.

If you want to create the graph purely using arcpy and ArcGIS you have a couple of options. There are the make and save graph tools which you can use based on a template graph created in arcmap, all that is required then is to modify the data source for each graph to your current table.

Alternatively use the arcpy graph class to carry out all these steps, I believe you will need to use this class to modify the data sources in the previous example. THe save graph will then allow you to export to pdf.

There is also matplotlib shipped with arcpy and that may be a better way to create the graphs. Sorry haven't used it so can't give you any steering on utilising it.

Hope this helps and gets you started. If i come up with anything I'll post again.

Cheers

Dave

[JakeSkinner]

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]27045[/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)

[ModernElectric]

Hi,

Have part of an answer for you, the code below will allow you to extract out the information based on the unique billing numbers within your table. It doesn't yet create and export the graphs but more on that in a  minute. Here is the code for extracting the unique billing tables.

## extract unique records from a gdb table and make mulitple tables for
## graphing

import arcpy, os

arcpy.env.overwriteOutput = True

table = r'H:\Python\test.gdb\billing'

fields = ['BILLING_PERIOD','METER_NUMBER','KWH','KW_DEMAND']

# empty list to store unique meter numbers
meterNumbers = []

# use a search cursor to extract the unique numbers
with arcpy.da.SearchCursor(table,fields) as cursor:
    for row in cursor:
        if not row[1] in meterNumbers:
            meterNumbers.append(row[1])

# for each unique cursor make a table view using the billing number for
# the selection
for meter in meterNumbers:
    ## omit the quotes if you table stores the meter number as a number not a string
    expression = arcpy.AddFieldDelimiters(table,'METER_NUMBER') + " = " + "'" + meter + "'"
    arcpy.MakeTableView_management(table,"CurrentMeter",expression)
    # copy the rows to a permanent table in the same location
    outTable = os.path.join(os.path.dirname(table),("meter_" + meter))
    arcpy.CopyRows_management("CurrentMeter",outTable)

    ## at this point can use the graphing options to generate the graphs and save
    ## unfortunately don't have the answer yet for this

the copy rows at the end is not strictly need as you can do the graph creation in memory for each billing table.

If you want to create the graph purely using arcpy and ArcGIS you have a couple of options. There are the make and save graph tools which you can use based on a template graph created in arcmap, all that is required then is to modify the data source for each graph to your current table.

Alternatively use the arcpy graph class to carry out all these steps, I believe you will need to use this class to modify the data sources in the previous example. THe save graph will then allow you to export to pdf.

There is also matplotlib shipped with arcpy and that may be a better way to create the graphs. Sorry haven't used it so can't give you any steering on utilising it.

Hope this helps and gets you started. If i come up with anything I'll post again.

Cheers

Dave

Dave-

   Thank you for the Python script to find the unique Meter Number and export them out. I will play with this and see what I can come up with.

[ModernElectric]

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]27045[/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)

Jake-

  Thank you for the Python Script for creating a graph. I will work with that and see what I can come up with

[ModernElectric]

Okay - what is not working here?

## extract unique records from a gdb table and make mulitple tables for
## graphing

import arcpy, os

arcpy.env.overwriteOutput = True

table = "C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb\billing"

fields = "BILLING_PERIOD";"METER_NUMBER";"KWH";"KW_DEMAND"

# empty list to store unique meter numbers
meterNumbers = []

# use a search cursor to extract the unique numbers
with arcpy.SearchCursor(table,fields) as cursor:
    for row in cursor:
        if not row[1] in meterNumbers:
            meterNumbers.append(row[1])

This is the error I'm getting:
Traceback (most recent call last):
  File "Z:\Operations\Maps and Records\GeoDatabase\MEWCO GIS System\Python Scripts\SearchCursorExample.py", line 16, in <module>
    with arcpy.SearchCursor(table,fields) as cursor:
  File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\__init__.py", line 820, in SearchCursor
    return gp.searchCursor(*args)
  File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\geoprocessing\_base.py", line 357, in searchCursor
    self._gp.SearchCursor(*gp_fixargs(args)))
IOError: "C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb illing" does not exist

[JakeSkinner]

Your fields need to be a list.

http://resources.arcgis.com/en/help/main/10.2/index.html#//018w00000011000000

Instead of:

fields = "BILLING_PERIOD";"METER_NUMBER";"KWH";"KW_DEMAND"

try:

fields = ["BILLING_PERIOD","METER_NUMBER","KWH","KW_DEMAND"]

Mind me asking what's the purpose of creating a list of the meter numbers?

[ModernElectric]

Your fields need to be a list.

http://resources.arcgis.com/en/help/main/10.2/index.html#//018w00000011000000

Instead of:

fields = "BILLING_PERIOD";"METER_NUMBER";"KWH";"KW_DEMAND"

try:

fields = ["BILLING_PERIOD","METER_NUMBER","KWH","KW_DEMAND"]

Mind me asking what's the purpose of creating a list of the meter numbers?

Jake-

What I am trying to do is to take a File Geodatabase Table that lists Electric Meters with their Peak KW Demand on a given billing period to eventually make a graph.

See previous posts in this thread for full explination.

I am having a heck of a time with this Search Cursor just trying to get a list of unique meter numbers with all of the rest of the data and isolating the rows of the same meter number

[JakeSkinner]

That is what the original script that I posted does.  What you will need to do is create a sample graph and update the script with the correct paths to your data.  Then, create a toolbox > right-click on the toolbox > Add Script > browse to the script.  You will also need to add a parameter to the script of type Long.

A user can then click on the script and it will open a dialog for them to specify a Meter Number.  Once they do and execute the tool, the meter number is queried and the graph is saved to a PDF.

[ModernElectric]

That is what the original script that I posted does.  What you will need to do is create a sample graph and update the script with the correct paths to your data.  Then, create a toolbox > right-click on the toolbox > Add Script > browse to the script.  You will also need to add a parameter to the script of type Long.

A user can then click on the script and it will open a dialog for them to specify a Meter Number.  Once they do and execute the tool, the meter number is queried and the graph is saved to a PDF.

Jake-

   Sounds like I have most of the parts....a good part of them from you...and I really appreciate that.

The biggest headache right now is I cannot figure out the Script for the Search Cursor to isolate individual meter number records to produce a single graph for a specific meter number