def main():
#setting up parser
parser = argparse.ArgumentParser(description=__doc__)
utils.add_standard_command_options(parser)
parser.add_argument(
"-e","--edbs",
action="store",dest="edbList",
help="List of 'user/edb' pairs separated by :"
)
parser.add_argument(
"-L","--labels",
action="store",
dest="labels",
help="List of edb labels separated by :"
)
parser.add_argument(
"-s","--substances",
action="store",dest="substances",
help="List of substance names separated by :"
)
parser.add_argument(
"-t","--title",
action="store",dest="title",
help="Report title"
)
parser.add_argument(
"-g","--gc-filter",
action="store",dest="gcfilter",
help="Filter on Geo codes, separated by :"
)
parser.add_argument(
"-o","--outfile",
action="store",dest="outfile",
help="Output filename"
)
parser.add_argument(
"-f","--format",
action="store",dest="format",
help="Output in 'excel','csv' or 'raw' " +
"(Excel-format requires xlwt python module)"
)
parser.add_argument("--substMapping",
action="store",dest="substMapping",
help="File with tab separated mappings of substance names")
parser.add_argument("--markerTable",
action="store",dest="markerTable",
help="Table of codes to be formatted and commented")
parser.add_argument(
"macro",metavar="MACRO",
help="A macro to use"
)
args = parser.parse_args()
if args.markerTable is not None:
keys=["Year","GC","AC","note_1","note_2"]
markerTable = DataTable(keys=keys,desc=[{"id":"Year","type":str},{"id":"GC","type":str},{"id":"AC","type":str},{"id":"note_1","type":str},{"id":"note_2","type":str}])
markerTable.read(args.markerTable)
else:
markerTable=None
substMapping={}
if args.substMapping is not None:
with codecs.open(args.substMapping,encoding="HP Roman8",mode="r") as f:
for line in f:
oldName,newName = line.split(":")
substMapping[oldName.strip()]=newName.strip()
dmn = Domain()
if args.gcfilter is not None:
args.gcfilter = args.gcfilter.split(":")
# Read original macro
with codecs.open(args.macro, encoding="HP Roman8", mode="r") as f:
originalContent = f.read()
# Create a tmp copy of the macro, write content from the original macro
macroTempFile = tempfile.NamedTemporaryFile(
suffix=".sedb",
dir=dmn.tmpDir()
)
tmpMacro = codecs.open(
macroTempFile.name,
encoding="HP Roman8",mode="w"
)
tmpMacro.write(originalContent)
tmpMacro.flush()
# Create a ControlFile obj to simplify reading and modifying macro
macro = ControlFile(macroTempFile.name, removeComments=False)
ebd = macro.findString("edb.edb:")
user = macro.findString("edb.user:"******"edb.reportgeocode:")[-1])
acIndex = int(macro.findString("edb.reportactcode:")[-1])
if args.edbList is None:
ebds = [[user, edb]]
else:
edbs = args.edbList.split(":")
edbs = [e.split("/") for e in edbs]
nedbs = len(edbs)
if args.labels is None:
labels = ["No label"] * len(edbs)
else:
labels = args.labels.split(":")
if len(labels) != nedbs:
log.error("Number of labels specified should match number of edb:s")
sys.exit(1)
if args.substances is None:
log.error("Need to specify substances")
sys.exit(1)
else:
substances = args.substances.split(":")
if args.format not in ('excel','csv','raw'):
log.error(
"Invalid format specifier : %s, should be one of 'excel'" +
", 'csv' or 'raw'" %args.format
)
sys.exit(1)
elif args.format == "excel":
try:
import xlwt
except:
log.error(
"trendReport.py requires python module xlwt to write excel-files")
sys.exit(1)
# first edb
# import pdb; pdb.set_trace()
edb = Edb(dmn, edbs[0][0], edbs[0][1])
# assume same code definitions in all edbs to be processed, read from first
rsrc = edb.rsrc
nrsubstances = len(substances)
unitIndex = int(macro.findString("UNIT :"))
units = rsrc.search[unitIndex]
subdb = Subdb(edb)
subdb.read()
#decode input title using stdin encoding
title=args.title.decode(sys.stdin.encoding)
rawOutput = ""
rawMeta = u"name: %s\nnrmacros: %i\nnrsub: %i\nunit: %s\n" %(
title, nedbs, nrsubstances, units)
emissions = []
for ind, edbUser in enumerate(edbs):
label = labels[ind]
userName = edbUser[0]
edbName = edbUser[1]
macro.setParam("edb.user:"******"edb.edb:", edbName)
macro.setParam("USER :"******"EDB :", edbName)
rawMeta += "macro.%i.edbuser: %s\n" %(ind, userName)
rawMeta += "macro.%i.edbname: %s\n" %(ind, edbName)
rawMeta += "macro.%i.desc: %s\n" %(ind, label)
for subst in substances:
log.info(
"User: %s, edb: %s, substance %s" %(
userName, edbName, subst)
)
substanceIndex = subdb.substIndex(subst)
macro.setParam("ELEMENT :", substanceIndex)
macro.write()
command = "xrepedb -i " + macro.name
log.info("Running xrepedb for substance %s" % subst)
# import pdb; pdb.set_trace()
(returnCode, errMsg, outMsg) = utilities.execute(command)
if returnCode != 0:
log.error("Could not run %s\nstdout: %s\nstderr:%s" %(
command,outMsg,errMsg))
sys.exit(1)
if len(outMsg) < 10:
log.error("Invalid output from xrepedb: %s" % outMsg)
sys.exit(1)
rawOutput += "#MACRO %i \"%s\" \"%s\"\n" % (ind, subst, labels[ind])
rawOutput += outMsg
lines = outMsg.split("\n")[:-1]
for lineInd, line in enumerate(lines):
vals = line.split()
ac = vals[1].split(".")
gc = vals[3].split(".")
if len(ac) == 1:
if ac[0] == "<all>":
acLev1 = "alla"
else:
acLev1 = ac[0]
acLev2 = "alla"
else:
acLev1 = ac[0]
acLev2 = ac[1]
if len(gc) == 1:
if gc[0] == "<all>":
gcLev1 = "alla"
else:
gcLev1 = gc[0]
gcLev2 = "alla"
else:
gcLev1 = gc[0]
gcLev2 = gc[1]
emis = float(vals[4])
if acLev1 == "alla":
acLev1Name = "alla"
acLev2Name = "alla"
else:
node = rsrc.ac[acIndex - 1].root.find(acLev1)
acLev1Name = node.attrib["name"]
if acLev2 == "alla":
acLev2Name = "alla"
else:
node = rsrc.ac[acIndex-1].root.find(
acLev1 + "/" + acLev2
)
acLev2Name = node.attrib["name"]
if gcLev1 == "alla":
gcLev1Name = "alla"
gcLev2Name = "alla"
else:
node = rsrc.gc[gcIndex-1].root.find(gcLev1)
gcLev1Name = node.attrib["name"]
if gcLev2 == "alla":
gcLev2Name = "alla"
else:
node = rsrc.gc[gcIndex - 1].root.find(
gcLev1 + "/" + gcLev2
)
gcLev2Name = node.attrib["name"]
if args.gcfilter is not None:
if gc[0] not in args.gcfilter:
# if args.gcfilter != gcLev1:
continue
emissions.append({"label": label,
"substance": subst,
"ac": '.'.join(ac),
"gc": '.'.join(gc),
"gcLev1": gcLev1Name,
"gcLev2": gcLev2Name,
"acLev1": acLev1Name,
"acLev2": acLev2Name,
"acLev1Code": acLev1,
"acLev2Code": acLev2,
"val": emis,
"edbIndex": ind})
#Close tempfile to automatically remove it
tmpMacro.close()
if args.format == "raw":
outfile = codecs.open(args.outfile,"w","HP Roman8")
outfile.write(rawMeta)
outfile.write(rawOutput)
outfile.close()
elif args.format == "csv":
outfile = open(args.outfile,"w")
desc = [
{'id': 'gc', 'type': unicode},
{'id': 'ac', 'type': unicode},
{'id': 'label', 'type': unicode},
{'id': 'user', 'type': unicode},
{'id': 'edb', 'type': unicode}
]
for subst in substances:
desc.append({'id': subst, 'type': float})
keys = ['gc', 'ac', 'label']
table = DataTable(desc=desc, keys=keys)
log.info("Adding emissions to csv-table")
for emis in emissions:
row = [None] * len(desc)
user = edbs[emis['edbIndex']][0]
edb = edbs[emis['edbIndex']][1]
row[table.colIndex['gc']] = emis['gc']
row[table.colIndex['ac']] = emis['ac']
row[table.colIndex['label']] = emis['label']
row[table.colIndex['user']] = user
row[table.colIndex['edb']] = edb
row[table.colIndex[emis['substance']]] = emis['val']
# data is appended to the correct row, or a new row is added if the
# table keys do not match any existing row
log.debug(
"Adding row for substance %s, gc %s, ac %s" %(
emis['substance'],
emis['gc'],
emis['ac'])
)
table.addRow(row, append=True)
table.write(outfile)
outfile.close()
else:
# Create style objects for excel output
header1Style = xlwt.easyxf(
'font: name Times New Roman,color-index black, bold on',
num_format_str='0.000E+00'
)
markerStyle1 = xlwt.easyxf(
'font: name Times New Roman,color-index red, bold off, italic on',
num_format_str='0.000E+00')
markerStyle2 = xlwt.easyxf(
'font: name Times New Roman,color-index orange, bold off, italic on',
num_format_str='0.000E+00')
normalStyle = xlwt.easyxf(
'font: name Times New Roman,color-index black, bold off',
num_format_str='0.000E+00'
)
excelBook = xlwt.Workbook()
# Creating info sheet
infoWs = excelBook.add_sheet("Info")
infoWs.col(0).width = 256*20
infoWs.col(1).width = 256*25
infoWs.col(2).width = 256*20
infoWs.col(3).width = 256*200
infoWs.write(0,0,u"Rapportnamn:",header1Style)
infoWs.write(0,1,title,header1Style)
infoWs.write(1,0,u"Beskrivning av dataunderlaget",header1Style)
infoWs.write(3,0,u"Makron (specificerar utsökningar ur databasen)",header1Style)
infoWs.write(4,0,u"Etikett",header1Style)
infoWs.write(4,1,u"Ägare till EDB",header1Style)
infoWs.write(4,2,u"EDB (emissiondatabas)",header1Style)
infoWs.write(4,3,u"Beskrivning",header1Style)
for i,edbUser in enumerate(edbs):
userName=edbUser[0]
edbName=edbUser[1]
label=labels[i]
infoWs.write(5+i,0,label)
infoWs.write(5+i,1,userName)
infoWs.write(5+i,2,edbName)
#reading edb description file (if it exists)
edb=Edb(dmn,userName,edbName)
infoWs.write(5+i,3,edb.desc().replace("\n"," "))
#split substances in green house gases and air quality related
ghgList=[s for s in substances if s in ghgs]
aqList=[s for s in substances if s not in ghgs]
#Write air quality headers
firstRow=4
#Add two rows for marker comments
if markerTable is not None:
firstRow+=2
if len(aqList)>0:
aqWs = excelBook.add_sheet(u"Luftföroreningar")
aqWs.col(0).width = 256*25
aqWs.col(1).width = 256*30
aqWs.col(2).width = 256*20
aqWs.col(3).width = 256*15
for col in range(nrsubstances*nedbs):
aqWs.col(col+4).width=256*15
aqWs.write(0,0,u"Rapportnamn:",header1Style)
aqWs.write(0,1,title,header1Style)
aqWs.write(1,0,u"Emissioner av luftföroreningar",header1Style)
aqWs.write(1,1,u"Enhet: "+units,header1Style)
if markerTable is not None:
aqWs.write(2,0,u"OBS! Röd kursiv text anger osäkra värden p.g.a. att en stor del av emissionen är fördelad med schabloner inom kommungruppen. Granska underkategorin \"Energiförsörjning via el-värmeverk samt inom industrin\" för att se eventuella misstänkta värden.",markerStyle1)
aqWs.write(3,0,u"OBS! Orange kursiv text anger osäkra värden p.g.a. att trenden varierar kraftigt och eventuellt felaktigt, ytterligare verifiering krävs. Granska underkategorin \"Energiförsörjning via el-värmeverk samt inom industrin\" för att se eventuella misstänkta värden.",markerStyle2)
aqWs.write(firstRow,0,"Huvudsektor",header1Style)
aqWs.write(firstRow,1,"Undersektor",header1Style)
aqWs.write(firstRow,2,u"Län",header1Style)
aqWs.write(firstRow,3,"Kommun",header1Style)
#Write ghg headers
if len(ghgList)>0:
ghgWs = excelBook.add_sheet(u"Växthusgaser")
ghgWs.col(0).width = 256*25
ghgWs.col(1).width = 256*30
ghgWs.col(2).width = 256*20
ghgWs.col(3).width = 256*15
for col in range(nrsubstances*nedbs):
ghgWs.col(col+4).width=256*15
ghgWs.write(0,0,u"Rapportnamn:",header1Style)
ghgWs.write(0,1,title,header1Style)
ghgWs.write(1,0,u"Emissioner av Växthusgaser",header1Style)
ghgWs.write(2,0,u"CO2-ekv. efter ämnesnamn innebär att emissionen är uttryckt i CO2-ekvivalenter",header1Style)
if markerTable is not None:
ghgWs.write(3,0,u"OBS! Röd kursiv text anger osäkra värden p.g.a. att en stor del av emissionen är fördelad med schabloner inom kommungruppen. Granska underkategorin \"Energiförsörjning via el-värmeverk samt inom industrin\" för att se eventuella misstänkta värden.",markerStyle1)
ghgWs.write(4,0,u"OBS! Orange kursiv text anger osäkra värden p.g.a. att trenden varierar kraftigt och eventuellt felaktigt, ytterligare verifiering krävs. Granska underkategorin \"Energiförsörjning via el-värmeverk samt inom industrin\" för att se eventuella misstänkta värden.",markerStyle2)
ghgWs.write(1,1,u"Enhet: "+units,header1Style)
ghgWs.write(firstRow,0,"Huvudsektor",header1Style)
ghgWs.write(firstRow,1,"Undersektor",header1Style)
ghgWs.write(firstRow,2,u"Län",header1Style)
ghgWs.write(firstRow,3,"Kommun",header1Style)
def getColInd(nmacros, substances,macroInd,subst):
#gets the column index in excel file
sInd=substances.index(subst)
#Including extra columns to write CO2-equivalents
nSubstWithCO2equivalents=0
for s in substances[:sInd+1]:
if s in doubleColumns:
nSubstWithCO2equivalents+=1
return 4 + macroInd+sInd*nmacros+nSubstWithCO2equivalents*(macroInd+1)
#write macro labels and substance headers for air quality sheet
for sInd,subst in enumerate(aqList):
for i,edbUser in enumerate(edbs):
col=getColInd(nedbs,aqList,i,subst)
aqWs.write(firstRow-1,col,labels[i],header1Style)
#If a substance name is given in mapping this is used, otherwise
#The substance bname from the airviro substance list is used
aqWs.write(firstRow,col,substMapping.get(subst,subst),header1Style)
#write macro labels and substance headers for ghg sheet
for sInd,subst in enumerate(ghgList):
for i,edbUser in enumerate(edbs):
col=getColInd(nedbs,ghgList,i,subst)
#If CO2-equivalents are calculated, an extra column is needed
if subst in doubleColumns:
ghgWs.write(firstRow-1,col-1,labels[i],header1Style)
ghgWs.write(firstRow-1,col,labels[i],header1Style)
#If CO2-equivalents are calculated, an extra column is needed
if subst in doubleColumns:
#debug statement
#print "writing subst %s in col %i and %i" %(subst,col-1,col)
ghgWs.write(firstRow,col-1,substMapping.get(subst,subst),header1Style)
ghgWs.write(firstRow,col,substMapping.get(subst,subst)+"CO2-ekv.",header1Style)
elif subst in storedAsCO2equivalents:
#debug statement
#print "writing subst %s in col %i" %(subst,col)
ghgWs.write(firstRow,col,substMapping.get(subst,subst)+"CO2-ekv.",header1Style)
else:
#debug statement
#print "writing subst %s in col %i" %(subst,col)
ghgWs.write(firstRow,col,substMapping.get(subst,subst),header1Style)
#looping over all emissions, writing them to the correct column and row
ghgRow=[]
aqRow=[]
for m in range(nedbs*nrsubstances+4+3*nedbs):
ghgRow.append(firstRow+1)
for m in range(nedbs*nrsubstances+4):
aqRow.append(firstRow+1)
for emis in emissions:
subst = emis["substance"]
emisVal=emis["val"]
edbInd=emis["edbIndex"]
#Check if gc, ac and year can be found in the error list
#debugging marker style
if markerTable is not None:
TableRowInd=markerTable.rowIndices([labels[edbInd],
emis["gc"],
emis["ac"],
"ja","*"])
if len(TableRowInd) >0:
valueStyle=markerStyle1
else:
TableRowInd=markerTable.rowIndices([labels[edbInd],
emis["gc"],
emis["ac"],
"*","ja"])
if len(TableRowInd)>0:
valueStyle=markerStyle2
else:
valueStyle=normalStyle
else:
valueStyle=normalStyle
if subst in ghgList:
col=getColInd(nedbs,ghgList,edbInd,subst)
row=ghgRow[col]
if ghgRow[0]<=+row:
ghgWs.write(row,0,emis["acLev1"],normalStyle)
ghgWs.write(row,1,emis["acLev2"],normalStyle)
ghgWs.write(row,2,emis["gcLev1"],normalStyle)
ghgWs.write(row,3,emis["gcLev2"],normalStyle)
ghgRow[0]+=1
#converts the emission to CO2-ekquivalents
if subst in doubleColumns:
ghgWs.write(row,col-1,float(emisVal),valueStyle)
ghgWs.write(row,col,float(emisVal)*float(ekvFactors[subst]),valueStyle)
else:
ghgWs.write(row,col,float(emisVal),valueStyle)
ghgRow[col]+=1
else:
col=getColInd(nedbs,aqList,edbInd,subst)
row=aqRow[col]
if aqRow[0]<=+row:
aqWs.write(row,0,emis["acLev1"],normalStyle)
aqWs.write(row,1,emis["acLev2"],normalStyle)
aqWs.write(row,2,emis["gcLev1"],normalStyle)
aqWs.write(row,3,emis["gcLev2"],normalStyle)
aqRow[0]+=1
aqWs.write(row,col,float(emisVal),valueStyle)
aqRow[col]+=1
excelBook.save(args.outfile)
log.info("Finished!")
def main():
#setting up parser
parser=OptionParser(usage= usage, version=version)
(options, args) = parser.parse_args()
#Setting up logging environment
logLevel=os.environ.get("LOG_LEVEL")
if logLevel==None or logLevel=="":
logLevel=2
logLevels={0:logging.NOTSET,
1:logging.WARNING,
2:logging.INFO,
3:logging.DEBUG}
rootLogger=logging.getLogger('')
logLevelObj=logLevels[int(logLevel)]
#info is written to stderr
#errors are written stdout
rootLogger.setLevel(logLevelObj)
infoHandler=logging.StreamHandler(sys.stderr)
infoHandler.setLevel(logLevel)
errHandler=logging.StreamHandler(sys.stdout)
errHandler.setLevel(logging.ERROR)
formatter = logging.Formatter('%(name)-12s: %(levelname)-8s %(message)s')
infoHandler.setFormatter(formatter)
errHandler.setFormatter(formatter)
rootLogger.addHandler(infoHandler)
rootLogger.addHandler(errHandler)
logger=logging.getLogger("trendReport")
if len(args)!=2:
parser.error("Incorrect number of arguments")
infile=path.abspath(args[0]) #file with emission data per code
outfile=path.abspath(args[1]) #path for output excel report
if not path.exists(infile):
logger.error("Input asciiReport does not exist")
sys.exit(-1)
#reading emission data file
f=codecs.open(infile,"r","HP Roman8")
dlines=f.readlines()
f.close()
#parsing meta-data file
dlines.pop(0)
dlines.pop(0)
name=dlines.pop(0)[5:].strip()
nrmacros=int(dlines.pop(0).split()[1])
nrsubstances=int(dlines.pop(0).split()[1])
units=dlines.pop(0)[5:].strip()
macros=[]
for mIndex in range(nrmacros):
edbuser=dlines.pop(0).split()[1]
edbname=dlines.pop(0).split()[1]
label=dlines.pop(0).split()[1]
macros.append({"edbuser":edbuser,"edbname":edbname,"label":label})
#finding path to edb.rsrc for first edb in trend
domainPath=os.environ["DBAS_PATH"]
domainName=os.environ["AVDBNAME"]
rsrcPath=path.join(domainPath,"edb",macros[0]["edbuser"],macros[0]["edbname"],"edb.rsrc")
#Creating codeTable object (for look-ups of activitycode names and geocodenames)
try:
cTable=codetable.CodeTable(rsrcPath)
except:
logger.error("Could not build code tree from .../dba/edb/edb.rsrc")
sys.exit(-1)
#parsing emission data file
macroIndex=0
emissions=[]
substances=[]
for line in dlines:
data=line.split()
if data[0]=="""#MACRO""": #if header, update substance and macro index
macroIndex=int(data[1])
subst=data[2].replace("\"","")
if subst not in substances:
substances.append(subst)
else: #else get codes and corresponding name
ac=data[1]
ac=ac.split(".")
if ac[0]=="<all>": #add "all" to get the right depth in the dictionary
ac=["all","all"]
elif len(ac)==1:
ac.append("all")
gc=data[3]
gc=gc.split(".")
if gc[0]=="<all>":
gc=["all","all"]
elif len(gc)==1:
gc.append("all")
gcLev1Name=cTable.gc[ gc[0] ][ "name" ]
if gc[1]!='all':
gcLev2Name=cTable.gc[ gc[0] ][ gc[1] ][ "name" ]
else:
gcLev2Name="Alla"
acLev1Name=cTable.ac[ ac[0] ][ "name" ]
if ac[1]!='all':
acLev2Name=cTable.ac[ ac[0] ][ ac[1] ]["name"]
else:
acLev2Name="Alla"
val=data[4]
emissions.append({"label":label,"substance":subst,"gcLev1":gcLev1Name,"gcLev2":gcLev2Name,"acLev1":acLev1Name,"acLev2":acLev2Name,"val":val,"macroIndex":macroIndex})
#Create style objects for excel output
header1Style=xlwt.easyxf('font: name Times New Roman,color-index black, bold on',
num_format_str='0.000E+00')
normalStyle=xlwt.easyxf('font: name Times New Roman,color-index black, bold off',
num_format_str='0.000E+00')
excelBook=xlwt.Workbook()
#Creating info sheet
infoWs = excelBook.add_sheet("Info")
infoWs.col(0).width = 256*20
infoWs.col(1).width = 256*25
infoWs.col(2).width = 256*20
infoWs.col(3).width = 256*200
infoWs.write(0,0,u"Rapportnamn:",header1Style)
infoWs.write(0,1,name,header1Style)
infoWs.write(1,0,u"Beskrivning av dataunderlaget",header1Style)
infoWs.write(3,0,u"Makron (specificerar utsökningar ur databasen)",header1Style)
infoWs.write(4,0,u"Etikett",header1Style)
infoWs.write(4,1,u"Ägare till EDB",header1Style)
infoWs.write(4,2,u"EDB (emissiondatabas)",header1Style)
infoWs.write(4,3,u"Beskrivning",header1Style)
for m in range(nrmacros):
infoWs.write(5+m,0,macros[m]["label"])
infoWs.write(5+m,1,macros[m]["edbuser"])
infoWs.write(5+m,2,macros[m]["edbname"])
#reading edb description file (if it exists)
edb=Edb(domainName,macros[m]["edbuser"],macros[m]["edbname"])
infoWs.write(5+m,3,edb.desc().replace("\n"," "))
#split substances in green house gases and air quality related
ghgList=[s for s in substances if s in ghgs]
aqList=[s for s in substances if s not in ghgs]
sheets={}
for s in substances:
sheets[s]=excelBook.add_sheet(s)
#Write air quality headers
for subst,sheet in sheets.iteritems():
firstRow=3
sheet.col(0).width = 256*25
sheet.col(1).width = 256*30
sheet.col(2).width = 256*20
sheet.col(3).width = 256*15
for col in range(nrsubstances*nrmacros):
sheet.col(col+4).width=256*15
sheet.write(0,0,u"Rapportnamn:",header1Style)
sheet.write(0,1,name,header1Style)
sheet.write(1,0,u"Emissioner av luftföroreningar",header1Style)
sheet.write(1,1,subst,header1Style)
sheet.write(firstRow,1,"Undersektor",header1Style)
sheet.write(firstRow,2,u"Län",header1Style)
sheet.write(firstRow,3,"Kommun",header1Style)
def getColInd(nmacros,macroInd,co2Ekv=False):
#gets the column index in excel file
if not co2Ekv:
return 4 + macroInd
else:
return 4+ macroInd + nmacros
#write macro labels and substance headers for air quality sheet
for sInd,subst in enumerate(substances):
for mInd,macro in enumerate(macros):
col=getColInd(nrmacros,mInd)
sheets[subst].write(firstRow-1,col,macro["label"],header1Style)
sheets[subst].write(firstRow,col,units,header1Style)
if subst in ghgList:
col=getColInd(nrmacros,mInd,co2Ekv=True)
sheets[subst].write(firstRow-1,col,macro["label"],header1Style)
sheets[subst].write(firstRow,col,units+u" (CO2-ekvivalenter)",header1Style)
#looping over all emissions, writing them to the correct column and row
sheetRow={}
for s in substances:
sheetRow[s]=[]
#For each sheet an array with as many values as there are columns with data
#Each value is initialized with the first row containing data
#Each value is a counter for the row number
for m in range(nrmacros*2+4):
for s in substances:
sheetRow[s].append(firstRow+1)
for emis in emissions:
subst = emis["substance"]
emisVal=emis["val"]
macroInd=emis["macroIndex"]
col=getColInd(nrmacros,macroInd)
row=sheetRow[subst][col]
#If row index for sheet is not larger than current row
#The meta data for the emission is written
if sheetRow[subst][0]<=row:
sheets[subst].write(row,0,emis["acLev1"],normalStyle)
sheets[subst].write(row,1,emis["acLev2"],normalStyle)
sheets[subst].write(row,2,emis["gcLev1"],normalStyle)
sheets[subst].write(row,3,emis["gcLev2"],normalStyle)
sheetRow[subst][0]+=1 #increment row in first col for sheet
sheets[subst].write(row,col,float(emisVal),normalStyle)
sheetRow[subst][col]+=1
if subst in ghgList:
col=getColInd(nrmacros,macroInd,co2Ekv=True)
#converts the emission to CO2-ekquivalents
sheets[subst].write(row,col,float(emisVal)*float(ekvFactors[subst]),normalStyle)
sheetRow[subst][col]+=1
excelBook.save(outfile)
def main():
# setting up parser
parser = OptionParser(usage=usage, version=version)
(options, args) = parser.parse_args()
# Setting up logging environment
logLevel = os.environ.get("LOG_LEVEL")
if logLevel == None or logLevel == "":
logLevel = 2
logLevels = {0: logging.NOTSET, 1: logging.WARNING, 2: logging.INFO, 3: logging.DEBUG}
rootLogger = logging.getLogger("")
logLevelObj = logLevels[int(logLevel)]
# info is written to stderr
# errors are written stdout
rootLogger.setLevel(logLevelObj)
infoHandler = logging.StreamHandler(sys.stderr)
infoHandler.setLevel(logLevel)
errHandler = logging.StreamHandler(sys.stdout)
errHandler.setLevel(logging.ERROR)
formatter = logging.Formatter("%(name)-12s: %(levelname)-8s %(message)s")
infoHandler.setFormatter(formatter)
errHandler.setFormatter(formatter)
rootLogger.addHandler(infoHandler)
rootLogger.addHandler(errHandler)
logger = logging.getLogger("trendReport")
if len(args) != 3:
parser.error("Incorrect number of arguments")
metadatafile = path.abspath(args[0]) # file with meta data for trend report
infile = path.abspath(args[1]) # file with emission data per code
outfile = path.abspath(args[2]) # path for output excel report
if not path.exists(metadatafile):
logger.error("Input asciiReport does not exist")
sys.exit(-1)
if not path.exists(infile):
logger.error("Input asciiReport does not exist")
sys.exit(-1)
# reading emission data file
f = open(infile, "r")
dlines = f.readlines()
f.close()
# reading meta-data file
f = codecs.open(metadatafile, "r", "HP Roman8")
mdlines = f.readlines()
f.close()
# parsing meta-data file
name = mdlines.pop(0)[5:].strip()
nrmacros = int(mdlines.pop(0).split()[1])
nrsubstances = int(mdlines.pop(0).split()[1])
units = mdlines.pop(0)[5:].strip()
macros = []
for mIndex in range(nrmacros):
edbuser = mdlines.pop(0).split()[1]
edbname = mdlines.pop(0).split()[1]
label = mdlines.pop(0).split()[1]
macros.append({"edbuser": edbuser, "edbname": edbname, "label": label})
# finding path to default edb.rsrc file in domain
domainPath = os.environ["DBAS_PATH"]
domainName = os.environ["AVDBNAME"]
rsrcPath = path.join(domainPath, "dba/edb/edb.rsrc")
# Creating codeTable object (for look-ups of activitycode names and geocodenames)
try:
cTable = codetable.CodeTable(rsrcPath)
except:
logger.error("Could not build code tree from .../dba/edb/edb.rsrc")
sys.exit(-1)
# parsing emission data file
macroIndex = 0
emissions = []
substances = []
for line in dlines:
data = line.split()
if data[0] == """#MACRO""": # if header, update substance and macro index
vals = line.split('"')
macroIndex = int(data[1])
subst = vals[1]
if subst not in substances:
substances.append(subst)
else: # else get codes and corresponding name
ac = data[1]
ac = ac.split(".")
if ac[0] == "<all>": # add "all" to get the right depth in the dictionary
ac = ["all", "all"]
elif len(ac) == 1:
ac.append("all")
gc = data[3]
gc = gc.split(".")
if gc[0] == "<all>":
gc = ["all", "all"]
elif len(gc) == 1:
gc.append("all")
gcLev1Name = cTable.gc[gc[0]]["name"]
if gc[1] != "all":
gcLev2Name = cTable.gc[gc[0]][gc[1]]["name"]
else:
gcLev2Name = "Alla"
acLev1Name = cTable.ac[ac[0]]["name"]
if ac[1] != "all":
acLev2Name = cTable.ac[ac[0]][ac[1]]["name"]
else:
acLev2Name = "Alla"
val = data[4]
emissions.append(
{
"label": label,
"substance": subst,
"gcLev1": gcLev1Name,
"gcLev2": gcLev2Name,
"acLev1": acLev1Name,
"acLev2": acLev2Name,
"val": val,
"macroIndex": macroIndex,
}
)
# Create style objects for excel output
header1Style = xlwt.easyxf("font: name Times New Roman,color-index black, bold on", num_format_str="0.000E+00")
normalStyle = xlwt.easyxf("font: name Times New Roman,color-index black, bold off", num_format_str="0.000E+00")
excelBook = xlwt.Workbook()
# Creating info sheet
infoWs = excelBook.add_sheet("Info")
infoWs.col(0).width = 256 * 20
infoWs.col(1).width = 256 * 25
infoWs.col(2).width = 256 * 20
infoWs.col(3).width = 256 * 200
infoWs.write(0, 0, u"Rapportnamn:", header1Style)
infoWs.write(0, 1, name, header1Style)
infoWs.write(1, 0, u"Beskrivning av dataunderlaget", header1Style)
infoWs.write(3, 0, u"Makron (specificerar utsökningar ur databasen)", header1Style)
infoWs.write(4, 0, u"Etikett", header1Style)
infoWs.write(4, 1, u"Ägare till EDB", header1Style)
infoWs.write(4, 2, u"EDB (emissiondatabas)", header1Style)
infoWs.write(4, 3, u"Beskrivning", header1Style)
for m in range(nrmacros):
infoWs.write(5 + m, 0, macros[m]["label"])
infoWs.write(5 + m, 1, macros[m]["edbuser"])
infoWs.write(5 + m, 2, macros[m]["edbname"])
# reading edb description file (if it exists)
edb = Edb(domainName, macros[m]["edbuser"], macros[m]["edbname"])
infoWs.write(5 + m, 3, edb.desc().replace("\n", " "))
# split substances in green house gases and air quality related
ghgList = [s for s in substances if s in ghgs]
aqList = [s for s in substances if s not in ghgs]
# Write air quality headers
firstRow = 3
if len(aqList) > 0:
aqWs = excelBook.add_sheet(u"Luftföroreningar")
aqWs.col(0).width = 256 * 25
aqWs.col(1).width = 256 * 30
aqWs.col(2).width = 256 * 20
aqWs.col(3).width = 256 * 15
for col in range(nrsubstances * nrmacros):
aqWs.col(col + 4).width = 256 * 15
aqWs.write(0, 0, u"Rapportnamn:", header1Style)
aqWs.write(0, 1, name, header1Style)
aqWs.write(1, 0, u"Emissioner av luftföroreningar", header1Style)
aqWs.write(1, 1, u"Enhet: " + units, header1Style)
aqWs.write(firstRow, 0, "Huvudsektor", header1Style)
aqWs.write(firstRow, 1, "Undersektor", header1Style)
aqWs.write(firstRow, 2, u"Län", header1Style)
aqWs.write(firstRow, 3, "Kommun", header1Style)
# Write ghg headers
if len(ghgList) > 0:
ghgWs = excelBook.add_sheet(u"Växthusgaser")
ghgWs.col(0).width = 256 * 25
ghgWs.col(1).width = 256 * 30
ghgWs.col(2).width = 256 * 20
ghgWs.col(3).width = 256 * 15
for col in range(nrsubstances * nrmacros):
ghgWs.col(col + 4).width = 256 * 15
ghgWs.write(0, 0, u"Rapportnamn:", header1Style)
ghgWs.write(0, 1, name, header1Style)
ghgWs.write(1, 0, u"Emissioner av Växthusgaser", header1Style)
ghgWs.write(2, 0, "Uttryckt i CO2-ekvivalenter", header1Style)
ghgWs.write(1, 1, u"Enhet: " + units, header1Style)
ghgWs.write(firstRow, 0, "Huvudsektor", header1Style)
ghgWs.write(firstRow, 1, "Undersektor", header1Style)
ghgWs.write(firstRow, 2, u"Län", header1Style)
ghgWs.write(firstRow, 3, "Kommun", header1Style)
def getColInd(nmacros, substances, macroInd, subst):
# gets the column index in excel file
sInd = substances.index(subst)
return 4 + macroInd + sInd * nmacros
# write macro labels and substance headers for air quality sheet
for sInd, subst in enumerate(aqList):
for mInd, macro in enumerate(macros):
col = getColInd(nrmacros, aqList, mInd, subst)
aqWs.write(firstRow - 1, col, macro["label"], header1Style)
aqWs.write(firstRow, col, subst, header1Style)
# write macro labels and substance headers for ghg sheet
for sInd, subst in enumerate(ghgList):
for mInd, macro in enumerate(macros):
col = getColInd(nrmacros, ghgList, mInd, subst)
ghgWs.write(firstRow - 1, col, macro["label"], header1Style)
ghgWs.write(firstRow, col, subst, header1Style)
# looping over all emissions, writing them to the correct column and row
ghgRow = []
aqRow = []
for m in range(nrmacros * nrsubstances + 4):
ghgRow.append(firstRow + 1)
for m in range(nrmacros * nrsubstances + 4):
aqRow.append(firstRow + 1)
for emis in emissions:
subst = emis["substance"]
emisVal = emis["val"]
macroInd = emis["macroIndex"]
if subst in ghgList:
col = getColInd(nrmacros, ghgList, macroInd, subst)
row = ghgRow[col]
if ghgRow[0] <= +row:
ghgWs.write(row, 0, emis["acLev1"], normalStyle)
ghgWs.write(row, 1, emis["acLev2"], normalStyle)
ghgWs.write(row, 2, emis["gcLev1"], normalStyle)
ghgWs.write(row, 3, emis["gcLev2"], normalStyle)
ghgRow[0] += 1
# converts the emission to CO2-ekquivalents
ghgWs.write(row, col, float(emisVal) * float(ekvFactors[subst]), normalStyle)
ghgRow[col] += 1
else:
col = getColInd(nrmacros, aqList, macroInd, subst)
row = aqRow[col]
if aqRow[0] <= +row:
aqWs.write(row, 0, emis["acLev1"], normalStyle)
aqWs.write(row, 1, emis["acLev2"], normalStyle)
aqWs.write(row, 2, emis["gcLev1"], normalStyle)
aqWs.write(row, 3, emis["gcLev2"], normalStyle)
aqRow[0] += 1
aqWs.write(row, col, float(emisVal), normalStyle)
aqRow[col] += 1
excelBook.save(outfile)
