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

[ModernElectric]

Right-click on the Script in the toolbox, click the Source tab, and make sure this is set to the script:

[ATTACH=CONFIG]27069[/ATTACH]

Also, check to make sure the Parameter is set:

[ATTACH=CONFIG]27070[/ATTACH]

Then, when you double-click on the Script in the toolbox, you should be presented with the following:

[ATTACH=CONFIG]27071[/ATTACH]

The user can enter in a meter number, and then an output graph will be created like the one attached.

Okay - so far this is an awesome start. I am atleast getting the script to run without any errors and the .PDF is coming out - but the graph is not showing the data.... no worries.

This is something for me to build off of......

The thing I want to do is to be able to run a script that will do these for Each meter (around 9500 total) at one time. This current script works for on demand graphs.

Thanks alot so far

[JakeSkinner]

Here is an update to the script that will perform this for each unique meter number:

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

table = "Meters"

fields = ["METER_NUMBER"]

list = []

with arcpy.da.SearchCursor(table, fields) as cursor:
    for row in cursor:
        list.append("{0}".format(row[0]))

del cursor, row

#remove duplicates from list
list = dict.fromkeys(list)
list = list.keys()

for n in list:
    arcpy.TableSelect_analysis(table, r"in_memory\table_sel", "METER_NUMBER = " + n)
    out_graph_name = n
    out_graph_pdf = r"C:\Temp" + "\\" + n + ".pdf"
    input_template = r"C:\Temp\KW Demand.grf"
    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)

If you want to run this from toolbox, right-click on the script within toolbox > Properties > Parameters tab and delete the parameter previously created.

[ModernElectric]

Jake-

Thank you for that. Close.....

However - I am getting this error:

<type 'exceptions.AttributeError'>: 'module' object has no attrbute 'da'
Failed to execute

Here is an update to the script that will perform this for each unique meter number:

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

table = "Meters"

fields = ["METER_NUMBER"]

list = []

with arcpy.da.SearchCursor(table, fields) as cursor:
    for row in cursor:
        list.append("{0}".format(row[0]))

del cursor, row

#remove duplicates from list
list = dict.fromkeys(list)
list = list.keys()

for n in list:
    arcpy.TableSelect_analysis(table, r"in_memory\table_sel", "METER_NUMBER = " + n)
    out_graph_name = n
    out_graph_pdf = r"C:\Temp" + "\\" + n + ".pdf"
    input_template = r"C:\Temp\KW Demand.grf"
    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)

If you want to run this from toolbox, right-click on the script within toolbox > Properties > Parameters tab and delete the parameter previously created.

[MattSayler]

Jake-

Thank you for that. Close.....

However - I am getting this error:

<type 'exceptions.AttributeError'>: 'module' object has no attrbute 'da'
Failed to execute

Jake, he's running 10.0. The da cursors didn't show up until 10.1. Also, I don't think 10.0 cursors will take a list for the fields, just a formatted string.

[ModernElectric]

Jake, he's running 10.0. The da cursors didn't show up until 10.1. Also, I don't think 10.0 cursors will take a list for the fields, just a formatted string.

Okay - I am trying to run this script off of ArcMap 10.0... I am in the process of upgrading to 10.2.... What is the latest Python that I will need?

[MattSayler]

Okay - I am trying to run this script off of ArcMap 10.0... I am in the process of upgrading to 10.2.... What is the latest Python that I will need?

10.0 uses python 2.6, 10.2 uses python 2.7. The differences you'll more likely notice will be in the arcpy sitepackage itself (don't try to use 2.7 with 10.0).

If you don't have a 10.2 environment to work in, it will probably be easier to code using 10.0, and then make changes when you can reliably test in 10.2. That way you'll have the logic and a working process already laid out. The changes should be pretty minimal to get it going in 10.2. Repointing paths, switching to da cursors (which isn't actually required, but could be beneficial), and passing fields as a list instead of a string may be all that's needed. 10.2 MIGHT even be backwards compatible with 10.0 scripts, but I'm not sure as we haven't moved past 10.0 yet ourselves. Probably not totally.

[JakeSkinner]

If interested, here is the script that will work for 10.0:

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

table = "Meters"

list = []

rows = arcpy.SearchCursor(table)
for row in rows:
    list.append(row.METER_NUMBER)

del row, rows

#remove duplicates from list
list = dict.fromkeys(list)
list = list.keys()

for n in list:
    arcpy.TableSelect_analysis(table, r"in_memory\table_sel", "METER_NUMBER = " + str(n))
    out_graph_name = n
    out_graph_pdf = r"C:\Temp" + "\\" + str(n) + ".pdf"
    input_template = r"C:\Temp\KW Demand.grf"
    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 PDF
    arcpy.SaveGraph_management(out_graph_name, out_graph_pdf, "MAINTAIN_ASPECT_RATIO", 600, 375)

[ModernElectric]

Jake-

  Thank you for the script for ArcMap 10.0

It looks good - but - I have tried the script in ArcCatalog and ArcMap and each time - it crashes and get the "ArcGIS Desktop has
encountered a serious application error and is unable to continue" Error screen.

[ModernElectric]

At this time - I have installed ArcGIS 10.2 and the script is working perfectly. Except (1) thing that I cannot figure out.

The field that is being used to name the .PDF is TWACs_NUMBER, which is a "Double" field type.

I am having a hard time getting the naming of the .PDF to come out properly.

Example:
TWACs_Number - 14710787
The way the .PDF is being created: 14710787.0.pdf

This is going to make it difficult running the attachment tool because of the .0 at the end since in the table there is no .0

This is what the stand-alone Python script looks like:

import arcpy
import arcpy
from arcpy import env
env.overwriteOutput = 1
env.workspace = r"C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb"

table = "SERVICE_METERS_DEMAND_F1FEEDER"

list = []

rows = arcpy.SearchCursor(table)
for row in rows:
    list.append(row.TWACs_NUMBER)

del row, rows

# Remove duplicates from list
list = dict.fromkeys(list)
list = list.keys()

for n in list:
    arcpy.TableSelect_analysis(table, r"in_memory\table_sel", "TWACs_NUMBER = " + str(n))
    out_graph_name = n
    out_graph_pdf = r"C:\MEWCo GIS System\Electric Graphs\Electric Meters\F-1 Feeder" + "\\" + str(n) + ".pdf"
    input_template = r"C:\MEWCo GIS System\Electric Graphs\GIS Graph Temps\ELECTRIC METER DEMAND - KWH USAGE.grf"
    input_data = r"in_memory\table_sel"

# Create the Graph
    graph = arcpy.Graph()

# Add a Vertical Bar series to the graph - KWH Usage
    graph.addSeriesBarVertical(input_data, "KWH_USAGE")

# Specify the title of the Left Axis
    graph.graphAxis[0].title = "KWH_USAGE"

# Specify the title of the Bottom Axis
    graph.graphAxis[2].title = "BILLING_PERIOD"

# Specify the title of the Graph
    graph.graphPropsGeneral.title = "KWH Usage by Electric TWACs Number"

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

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

Also - why is it when the .PDF is greated - the graph title and lables are ALL lower case letters?
Thank you

[DaveBarrett]

import arcpy
import arcpy
from arcpy import env
env.overwriteOutput = 1
env.workspace = r"C:\MEWCo GIS System\Electric System\MEWCo_Electric_Model-LOCAL.gdb"

table = "SERVICE_METERS_DEMAND_F1FEEDER"

list = []

rows = arcpy.SearchCursor(table)
for row in rows:
    list.append(row.TWACs_NUMBER)

del row, rows

# Remove duplicates from list
list = dict.fromkeys(list)
list = list.keys()

for n in list:
    arcpy.TableSelect_analysis(table, r"in_memory\table_sel", "TWACs_NUMBER = " + str(n))
    out_graph_name = n
    out_graph_pdf = r"C:\MEWCo GIS System\Electric Graphs\Electric Meters\F-1 Feeder" + "\\" + str(n) + ".pdf"
    input_template = r"C:\MEWCo GIS System\Electric Graphs\GIS Graph Temps\ELECTRIC METER DEMAND - KWH USAGE.grf"
    input_data = r"in_memory\table_sel"

# Create the Graph
    graph = arcpy.Graph()

# Add a Vertical Bar series to the graph - KWH Usage
    graph.addSeriesBarVertical(input_data, "KWH_USAGE")

# Specify the title of the Left Axis
    graph.graphAxis[0].title = "KWH_USAGE"

# Specify the title of the Bottom Axis
    graph.graphAxis[2].title = "BILLING_PERIOD"

# Specify the title of the Graph
    graph.graphPropsGeneral.title = "KWH Usage by Electric TWACs Number"

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

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

Hi not sure about the graph labels being lower case but the issue you are having with the .0 at the end of the number is due to the way python handles floating point numbers. If you take the example

number = float(123456)
print number
'123456.0'
# where you use the str(number) use a slice to remove the .0 in the pdf name
str(number[:-2]
'123456'

This should hopefully remove the issue when using the attachments tool

Cheers

Dave