Create State Physical Supply Usage Table (PSUT)

Uses values in the WaterBalanceData.csv file to populate values in the StatePSUT.xlsx file

Requires the openyxl module:

In [1]:
#Import modules
import sys, os
import pandas as pd
from openpyxl import load_workbook
ImportError                               Traceback (most recent call last)
<ipython-input-1-fc8c492439d6> in <module>()
      2 import sys, os
      3 import pandas as pd
----> 4 from openpyxl import load_workbook

ImportError: No module named openpyxl

Get/Set the filenames required

In [ ]:
#Set the location of the data directory
dataDir = '../../Data'
#Get the water balance input csv file
inDataFN = dataDir + os.sep + 'StateData' + os.sep + 'la_2010.csv'
#Get the template
inXlsxFN = dataDir + os.sep + 'Templates' + os.sep + 'StatePSUTTemplate.xlsx' #The template that will be filled in
In [3]:
#Load the water balance data into a pandas dataframe
dfData = pd.read_csv(inDataFN)

Below we set the field to column mappings. The number on the right of the '=' refers to the column in the template in which the field on the left occurs.

In [4]:
#Row and column indices
Aq = 5    #Aquaculture
Do = 19   #Domestic
In = 16   #Industrial
Ic = 2    #Irrigation-cropland
Ig = 17   #Irrigation-golf courses
Li = 3    #Livestock
Mi = 7    #Mining
PS = 14   #Public supply
TC = 10   #Thermoelectric-once thru
TR = 9    #Thermoelectric-recirculated
Supply = 20 #Environment
Sf = 21 #Surface
Gw = 22 #Groundwater
In [5]:
#Create the dictionary of GroupNames and cell locations
celLocs = {'Aquaculture_Surface':(Sf,Aq),
In [6]:
#Create the workbook object
wb = load_workbook(filename = inXlsxFN)
In [7]:
for year in (2000,2005, 2010):
    #Get the year worksheet in the workbook
    ws = wb.get_sheet_by_name(str(year))
    #Label the sheet
    ws.cell(column=1,row=1,value="US Water Balance: {}. Values in MGal/Year".format(year))
    #use the dictionary to insert values
    for name, cellLoc in celLocs.items():
        #Get the value for selected year
        val = dfData[(dfData.Group == name) & (dfData.YEAR == year)]['MGal'].iloc[0]
        #insert it into the Excel file
        ws.cell(column = cellLoc[1],row = cellLoc[0],value = val)
In [8]:'BalanceSheet.xlsx')