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

[JakeSkinner]

Attached is a zip file with an example.  Try the following:

1.  Extract the zip file to your C:\Temp directory
2.  Open ArcMap
3.  Under C:\Temp, expand the Data.gdb > Graph toolbox > double-click on the Script
4.  Specify a meter number, i.e. 8151830
5.  Click OK to execute the tool

Within your C:\Temp directory you should have a graph as a PDF.  Is this what you are looking for?

[ModernElectric]

Attached is a zip file with an example.  Try the following:

1.  Extract the zip file to your C:\Temp directory
2.  Open ArcMap
3.  Under C:\Temp, expand the Data.gdb > Graph toolbox > double-click on the Script
4.  Specify a meter number, i.e. 8151830
5.  Click OK to execute the tool

Within your C:\Temp directory you should have a graph as a PDF.  Is this what you are looking for?

Jake-

  This is a great start - but it doesn't seem to be working according to your instructions. Is it open in ArcMap or ArcCatalog? - Also I dont see a place that asks me to type in a meter number

[ModernElectric]

Jake-

  This is a great start - but it doesn't seem to be working according to your instructions. Is it open in ArcMap or ArcCatalog? - Also I dont see a place that asks me to type in a meter number

This is an error I get back:

Traceback (most recent call last):
  File "C:\temp\Script1.py", line 8, in <module>
    arcpy.TableSelect_analysis(table, r"in_memory\table_sel", "METER_NUMBER = " + arcpy.GetParameterAsText(0))
  File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\analysis.py", line 153, in TableSelect
    raise e
ExecuteError: ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used. [Meters]
An invalid SQL statement was used. [SELECT * FROM Meters WHERE METER_NUMBER = ]
Failed to execute (TableSelect).

[JakeSkinner]

You can open ArcMap, and then navigate to C:\temp using the Catalog window.  When you double-click on the script within the Toolbox, what do you see?  Can you send a screen shot?  What meter number did you specify?

[ModernElectric]

You can open ArcMap, and then navigate to C:\temp using the Catalog window.  When you double-click on the script within the Toolbox, what do you see?  Can you send a screen shot?  What meter number did you specify?

This is what I see

[DaveBarrett]

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.gdbilling" does not exist

It looks like this error is mainly caused by the use of the 10.1 data access search cursor not the normal search cursor at 10.0. The 10.0 search cursor does not support using the with context manager as well. There is no option to specify fields in 10.0 search cursor. The help below shows the syntax you need to use.

http://resources.arcgis.com/en/help/main/10.1/index.html#//018v00000050000000

Sorry should have checked what version it was you were using

Dave

[DaveBarrett]

This is an error I get back:

Traceback (most recent call last):
  File "C:\temp\Script1.py", line 8, in <module>
    arcpy.TableSelect_analysis(table, r"in_memory\table_sel", "METER_NUMBER = " + arcpy.GetParameterAsText(0))
  File "C:\Program Files\ArcGIS\Desktop10.0\arcpy\arcpy\analysis.py", line 153, in TableSelect
    raise e
ExecuteError: ERROR 999999: Error executing function.
An invalid SQL statement was used.
An invalid SQL statement was used. [Meters]
An invalid SQL statement was used. [SELECT * FROM Meters WHERE METER_NUMBER = ]
Failed to execute (TableSelect).

It looks like the error here with the sql statement is missing field delimiters
the statement should have "field" and then the equals
old:

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

new:

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

alternatively for the field name you wish to use in the statment replace '"METER_NUMBER"' with

arcpy.AddFieldDelimiters(table,"METER_NUMBER")

this will add the correct delimiters for the workspace you are using.

http://resources.arcgis.com/en/help/main/10.1/index.html#/AddFieldDelimiters/018v0000006p000000/

hope this helps.

Dave

[DaveBarrett]

## 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

Jake -

The generation of the list of Billing Numbers was there to help ModernGIS automate the hole process. If I was doing this I would aim to have an input of the whole table and an output location to store all the graphs. It should be possible to use the list of unique Billing Numbers in a for loop to generate each graph.

I've developed a bit of a lazy habit where I try to use the minimum clicks necessary. Can def cause a few headaches in the development though!

Cheers

Dave

[ModernElectric]

Not sure if this makes a difference at all - but I am currently running ArcMap 10.0 and using Python 2.6. I am using IDLE to edit and run the script. Is this making a difference in the code and how its running? I am planning on upgrading to ArcMap 10.2 in the next week.

[JakeSkinner]

This is what I see

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.