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: https://openpyxl.readthedocs.io/en/default/

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
#--Columns--
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
#--Rows--
Sf = 21 #Surface
Gw = 22 #Groundwater
In [5]:
#Create the dictionary of GroupNames and cell locations
celLocs = {'Aquaculture_Surface':(Sf,Aq),
           'Aquaculture_Groundwater':(Gw,Aq),
           'Domestic_Surface':(Sf,Do),
           'Domestic_Groundwater':(Gw,Do),
           'Industrial_Surface':(Sf,In),
           'Industrial_Groundwater':(Gw,In),
           'Irrigation_Crop_Surface':(Sf,Ic),
           'Irrigation_Crop_Groundwater':(Gw,Ic),
           'Irrigation_Golf_Surface':(Sf,Ig),
           'Irrigation_Golf_Groundwater':(Gw,Ig),
           'Livestock_Surface':(Sf,Li),
           'Livestock_Groundwater':(Gw,Li),
           'Mining_Surface':(Sf,Mi),
           'Mining_Groundwater':(Gw,Mi),
           'PublicSupply_Surface':(Sf,PS),
           'PublicSupply_Groundwater':(Gw,PS),
           'ThermoElec_OnceThru_Surface':(Sf,TC),
           'ThermoElec_OnceThru_Groundwater':(Gw,TC),
           'ThermoElec_Recirc_Surface':(Sf,TR),
           'ThermoElec_Recirc_Groundwater':(Gw,TR),
           'Supply':(4,12)
          } 
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]:
wb.save(dataDir+os.sep+'BalanceSheet.xlsx')