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/
#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
#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
#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.
#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
#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)
}
#Create the workbook object
wb = load_workbook(filename = inXlsxFN)
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)
wb.save(dataDir+os.sep+'BalanceSheet.xlsx')