class ORSPlots(object): # ORSCalculator
"""A class to create the SAIDI/SAIFI charts
from the ORS data."""
Timestamp = 'Date'
Cap = 'Cap/Limit'
Target = 'Target'
Collar = 'Collar'
Planned = 'Planned'
Unplanned = 'Unplanned Normalised'
CapUnplanned = 'Unplanned (Normalised Out)'
# Set the order of the headings and data in the sheet
NetworkHeadings = ["ELIN", "OTPO", "TPCO"]
IndexHeadings = ["SAIDI", "SAIFI"]
DataHeadings = [Cap, Target, Collar,
Planned, Unplanned, CapUnplanned]
def __init__(self, ORSCalculatorInstance, xlInstance):
self.ORS = ORSCalculatorInstance
self.xlInst = xlInstance
self.Sheet = Sheet(self.xlInst)
self.InputSheet = "Input"
self.CalculationSheet = "Calculation"
self.StatsSheet = "Statistics"
self.Graphs = XlGraphs(xlInstance, self.Sheet)
self.srcRowOffset = 2 # The number of rows taken up by headings (before the data begins)
self.srcColumnOffset = 1 # The number of columns that the data is offset by
# Set the order of the headings and data in the sheet
self.NetworkHeadings = ["ELIN", "OTPO", "TPCO"]
self.IndexHeadings = ["SAIDI", "SAIFI"]
self.DateOrigin = pos(row=4, col=1) # The postion to place the fist date on the Excel sheet
# Graph offsets and dimentions
self.ChartDimentions = pos(x=700, y=480)
self.ChartOrigins = []
x = 85
y = 100
for network in self.NetworkHeadings:
self.ChartOrigins.append(pos(x=x, y=y))
x += self.ChartDimentions.x # Shift the charts for the next network to the right
def __repr__(self):
"""Return an identifying instance of the network being handled"""
return "in the __repr__ for ORSPlots" + self.ORS.networknames[0]
def Annual_Sheets(self, dates):
"""Create a new sheet for every indvidual year in the ORS results"""
for date in dates:
year = str(date.year)
self.Create_Sheet(year)
self.Fill_Dates(date, year)
self.Populate_Fixed_Stats(year) # Com Com table values scaled linearly
self.Populate_Daily_Stats(date, year) # Daily real world SAIDI/SAIDI
self.Create_Graphs(date)
def Clean_Workbook(self):
self.Sheet.rmvSheet(keepList=[self.InputSheet, self.StatsSheet])
def Create_Sheets(self, dates):
"""Creates all the sheets needed in the workbook.
Autofills the dates. All other data is network specific"""
for date in dates:
year = str(date.year)
self.Create_Sheet(year)
self.Fill_Dates(date, year)
def Create_Sheet(self, suffix):
"""Create the "Calculation" sheet in excel, delete it if it already exisits"""
suffix = " " + suffix
# Remove the sheet, if it exists, then re-add it -- Don't do this when adding multiple networks
self.Sheet.rmvSheet(removeList=[self.CalculationSheet+suffix])
self.Sheet.addSheet(self.CalculationSheet+suffix)
DataOrigin = pos(row=4, col=1)
LeftHeadings = [self.Timestamp]
TableHeadings = LeftHeadings + \
len(self.NetworkHeadings) * len(self.IndexHeadings) * self.DataHeadings
# Assign row 3 data
self.Sheet.setRange(self.CalculationSheet+suffix, DataOrigin.row-1, DataOrigin.col, [TableHeadings]) # Write the row headings into the table
self.Sheet.mergeCells(self.CalculationSheet+suffix, 1, DataOrigin.col, DataOrigin.row-1, DataOrigin.col) # Date Cells
# Assign row 2 data
col = DataOrigin.col + len(LeftHeadings)
columns = list(np.linspace(col,
col + (len(self.NetworkHeadings) * len(self.IndexHeadings) - 1) * len(self.DataHeadings),
len(self.NetworkHeadings) * len(self.IndexHeadings)))
index = 0
for col in columns:
self.Sheet.setRange(self.CalculationSheet+suffix, DataOrigin.row-2, int(col), [[self.IndexHeadings[index % len(self.IndexHeadings)]]]) # self.IndexHeadings[int((col-len(LeftHeadings)) % len(self.IndexHeadings))]
index += 1
self.Sheet.mergeCells(self.CalculationSheet+suffix, 2, col, 2, col - 1 +
len(self.NetworkHeadings) * len(self.IndexHeadings) * len(self.DataHeadings) / (len(self.NetworkHeadings) * len(self.IndexHeadings))) # Row 2
# Assign row 1 data
col = DataOrigin.col + len(LeftHeadings)
columns = list(np.linspace(col,
col + (len(self.NetworkHeadings) * len(self.IndexHeadings) - 2) * len(self.DataHeadings),
len(self.NetworkHeadings)))
index = 0
for col in columns:
self.Sheet.setRange(self.CalculationSheet+suffix, DataOrigin.row-3, int(col), [[self.NetworkHeadings[index % len(self.NetworkHeadings)]]])
index += 1
self.Sheet.mergeCells(self.CalculationSheet+suffix, 1, col, 1, col - 1 +
len(self.NetworkHeadings) * len(self.IndexHeadings) * len(self.DataHeadings) / len(self.NetworkHeadings)) # Row 1
def Generate_Dates(self, startdate, enddate=None):
"""Generate an array of all the days for 1 fincial year"""
if type(enddate) != datetime.datetime:
enddate = datetime.datetime(startdate.year + 1, 3, 31)
dt = datetime.timedelta(1) # A time delta of 1 day
TimeStamps = []
while startdate <= enddate:
TimeStamps.append([startdate])
startdate = startdate + dt
return TimeStamps
def Fill_Dates(self, date, suffix):
"""Fill the date column in the Excel sheet with the date values read from the parser.
Also performs and autofit (width) and freezes panes."""
suffix = " " + suffix
row, col = \
self.Sheet.setRange(self.CalculationSheet+suffix, self.DateOrigin.row, self.DateOrigin.col, self.Generate_Dates(date))
self.Sheet.autofit(self.CalculationSheet+suffix, self.DateOrigin.col)
ActiveWindow = self.xlInst.xlApp.Windows(self.xlInst.xlBook.Name)
ActiveWindow.SplitColumn = 1
ActiveWindow.SplitRow = 3
ActiveWindow.FreezePanes = True
def _Correct_Graph_Slope(self, suffix, enddate=datetime.datetime.now()):
"""When the data is truncated for stacked area charts we need two of the same end dates
in a row to create a vertical drop on the graph, otherwise it slopes with delta x = one time interval"""
suffix = " " + suffix
searchterm = datetime.datetime(enddate.year, enddate.month, enddate.day)
searchterm = searchterm.strftime(str(searchterm.day) + "/%m/%Y") # Get rid of the zero padding (%e and %-d don't work on Windows)
maxrow = self.Sheet.getMaxRow(self.CalculationSheet+suffix, 1, 4)
results = self.Sheet.search(shtRange(self.CalculationSheet+suffix, None, 4, 1, maxrow, 1),
searchterm)
if len(results) == 1:
# If there is only one date, we haven't corrected slope yet so do it now
self.Sheet.setCell(self.CalculationSheet+suffix, results[0].Row+1, 1, results[0].Value)
def _Correct_Graph_Axis(self, ChartName, enddate=datetime.datetime.now()):
"""Adds the final end of year date to the x axis e.g. 31/3/xxxx"""
self.Graphs.Set_Max_X_Value(ChartName, enddate)
def Populate_Fixed_Stats(self, suffix):
"""Create series values for the Limit, Cap, and Collar.
Populate the excel sheet with these values."""
suffix = " " + suffix
network = self.ORS.networknames[0]
if network == "ELIN":
ColOffset = 0
elif network == "OTPO":
ColOffset = len(self.IndexHeadings) * len(self.DataHeadings)
elif network == "TPCO":
ColOffset = len(self.IndexHeadings) * len(self.DataHeadings) * (len(self.NetworkHeadings) - 1)
RowHeadings = ["CAP", "TARGET", "COLLAR"] # The order the rows appear in the Excel spreadsheet
self.Sheet.set_calculation_mode("manual")
# Assumes that the data will start in col 2 i.e. col 1 is for the dates
Column = 2 + ColOffset
OriginCol = Column
for param in self.IndexHeadings:
for heading in RowHeadings:
LinearRange = np.linspace(0, self.ORS._get_CC_stats(heading)[self.IndexHeadings.index(param)],
self.Sheet.getMaxRow(self.CalculationSheet+suffix, 1, 4) - 3 + 1)[1:]
self.Sheet.setRange(self.CalculationSheet+suffix, 4, Column, [[i] for i in LinearRange])
Column += 1
Column += len(self.DataHeadings) - len(RowHeadings)
self.Sheet.set_calculation_mode("automatic")
def _Calc_Rows(self, dates, ORS):
SAIDIcol = []
SAIFIcol = []
for day in dates:
SAIDIrow = []
SAIFIrow = []
x, y = ORS._get_indicies(day, "planned", applyBoundary=True)
SAIDIrow.append(x)
SAIFIrow.append(y)
x, y = ORS._get_indicies(day, "unplanned", applyBoundary=True)
SAIDIrow.append(x)
SAIFIrow.append(y)
x, y = ORS._get_indicies(day, "unplanned", applyBoundary=False)
SAIDIrow.append(x-SAIDIrow[1])
SAIFIrow.append(y-SAIFIrow[1])
SAIDIcol.append(SAIDIrow)
SAIFIcol.append(SAIFIrow)
# Here for debugging only
#row=4
#self.Sheet.setRange(sheetname, row, ColOffset, [SAIDIrow])
#self.Sheet.setRange(sheetname, row, ColOffset+len(self.DataHeadings), [SAIFIrow])
#row += 1
return SAIDIcol, SAIFIcol
def Populate_Daily_Stats(self, enddate, FiscalYear):
"""Create series values for the Planned and Unplanned SAIDI/SAIFI.
Populate the excel sheet with these values."""
# enddate = datetime.datetime.now() (default option)
#FiscalYear = str(self._get_fiscal_year(enddate))
sheetname = self.CalculationSheet + " " + FiscalYear
if enddate > datetime.datetime.now():
enddate = datetime.datetime.now()
network = self.ORS.networknames[0]
ColOffset = 5 # Magic number: offset of the column in the data table
if network == "ELIN":
ColOffset += 0
elif network == "OTPO":
ColOffset += len(self.DataHeadings) * len(self.IndexHeadings)
elif network == "TPCO":
ColOffset += len(self.DataHeadings) * len(self.IndexHeadings) * (len(self.NetworkHeadings) - 1)
self.Sheet.set_calculation_mode("manual")
startdate = self.Sheet.getDateTime(self.Sheet.getCell(sheetname, 4, 1))
lastdate = self.Sheet.getDateTime(self.Sheet.getCell(sheetname, self.Sheet.getMaxRow(sheetname, 1, 4), 1))
delta_days = (lastdate - startdate).days + 1
FiscalyearDays = [startdate + datetime.timedelta(days=i) for i in range(delta_days)]
# Truncate the data if it is for the present year
maxrow = self.Sheet.getMaxRow(sheetname, 1, 4)
#enddate = datetime.datetime.now() # Use this variable to force the graphs to only display a limited set of information
searchterm = datetime.datetime(enddate.year, enddate.month, enddate.day)
searchterm = searchterm.strftime(str(searchterm.day) + "/%m/%Y") # Get rid of the zero padding (%e and %-d don't work on Windows)
searchresult = self.Sheet.search(shtRange(sheetname, None, 4, 1, maxrow, 1),
searchterm)
if len(searchresult) == 1 or len(searchresult) == 2: # There could be two dates, if we are duplicating the end date to achieve a vertical truncation on our stacked area chart
StopTime = self.Sheet.getDateTime(searchresult[0].Value)
else:
StopTime = FiscalyearDays[-1] # update this to be FiscalyearDays[0] now?
SAIDIcol, SAIFIcol = self._Calc_Rows(FiscalyearDays, self.ORS)
# The table columns need to be cummulative
SAIDIsums = [0 for i in self.DataHeadings[3:]]
SAIFIsums = [0 for i in self.DataHeadings[3:]]
SAIDITable = []
SAIFITable = []
row = 4
# Loop through every row
for SAIDIrow, SAIFIrow, day in zip(SAIDIcol, SAIFIcol, FiscalyearDays):
ColumnIndex = 0
# Loop through every column
for SAIDIval, SAIFIval in zip(SAIDIrow, SAIFIrow):
# Add the new rows to the table stored in memmory
if day <= StopTime: # means we will stop on the current day, but then fixing graph slopes break
SAIDIsums[ColumnIndex] += SAIDIval
SAIFIsums[ColumnIndex] += SAIFIval
else:
SAIDIsums[ColumnIndex] = None
SAIFIsums[ColumnIndex] = None
ColumnIndex += 1
#self.Sheet.setRange(sheetname, row, ColOffset, [SAIDIsums])
#self.Sheet.setRange(sheetname, row, ColOffset+len(self.DataHeadings), [SAIFIsums])
SAIDITable.append(SAIDIsums[:]) # This copys by value, not by reference
SAIFITable.append(SAIFIsums[:]) # This copys by value, not by reference
row += 1
self.Sheet.setRange(sheetname, 4, ColOffset, SAIDITable)
self.Sheet.setRange(sheetname, 4, ColOffset+len(self.DataHeadings), SAIFITable)
self._Correct_Graph_Slope(FiscalYear, StopTime) # Makes the area plot look a bit better, but mutates the source data, so must be run last
self.Sheet.set_calculation_mode("automatic")
def _get_fiscal_year(self, enddate):
"""Get the fiscal year as defined in NZ. Returns the year as a int"""
year = enddate.year
if enddate.month - 4 < 0:
year -= 1
return year
def Create_Graphs(self, enddate, sheetname_suffix):
"""Create the SAIDI/SAIFI chart
@param enddate: Used to set the x axis on the chart and name the chart"""
FiscalYear = self._get_fiscal_year(enddate) # (default option for sheetname)
# Always set the end date of the graph to be the 1/4/xxxx (you may want to change this in future)
if True:
graphenddate = datetime.datetime(FiscalYear+1, 4, 1)
# Used to set the name of the chart...
if enddate > datetime.datetime.now():
enddate = datetime.datetime.now()
network = self.ORS.networknames[0]
ColOffset = 2 # Magic number: where the data starts in the table (column 2)
FullName = ""
if network == "ELIN":
ColOffset += 0
chartpath = os.path.expanduser('~/Documents/SAIDI and SAIFI/Templates/ORSChartEIL2.crtx')
FullName = "Electricity Invercargill"
elif network == "OTPO":
ColOffset += len(self.DataHeadings) * len(self.IndexHeadings)
chartpath = os.path.expanduser('~/Documents/SAIDI and SAIFI/Templates/ORSChartOJV2.crtx')
FullName = "OtagoNet+ESL"
elif network == "TPCO":
ColOffset += len(self.DataHeadings) * len(self.IndexHeadings) * (len(self.NetworkHeadings) - 1)
chartpath = os.path.expanduser('~/Documents/SAIDI and SAIFI/Templates/ORSChartTPC2.crtx')
FullName = "The Power Company"
ylables = ["Average Outage Duration (Minutes/ICP)", "Average No. Outages (Interruptions/ICP)"]
for stat in self.IndexHeadings:
ChartName = datetime.datetime(enddate.year, enddate.month, enddate.day)
ChartName = ChartName.strftime(str(ChartName.day) + "/%m/%Y")
ChartName = FullName + " " + stat + " YTD as at " + ChartName # Make sure there is no zero padding on the day
# Check for a duplicate chart name...
NumDuplicates = 0
TempName = ChartName
while self.Graphs.Chart_Exists(TempName):
TempName = ChartName
NumDuplicates += 1
TempName += " (" + str(NumDuplicates) + ")"
ChartName = TempName
self.Graphs.Create_Chart(ChartName, self.Generate_Date_Range(sheetname_suffix),
sheetname=self.CalculationSheet + " " + sheetname_suffix)
# Add the indvidual series to the chart
i = self.IndexHeadings.index(stat) * (len(self.DataHeadings) * (len(self.IndexHeadings) - 1)) # Add a sub-offset
for col in range(i + ColOffset, i + ColOffset + len(self.DataHeadings)):
self.Graphs.Add_Series(ChartName, self.Generate_Range(sheetname_suffix, col), serieslabels=True)
# Apply the templated style, reapply attributes like ylabel
self.Graphs.Apply_Template(ChartName, chartpath,
ylabel=ylables[self.IndexHeadings.index(stat)], ylabelsize=15)
# Apply a fix to the final dates on the graph
self._Correct_Graph_Axis(ChartName, graphenddate)
# Make the chart bigger
self.Graphs.Set_Dimentions(ChartName, self.ChartDimentions.x, self.ChartDimentions.y)
# Stack charts from the same network vetically
origin = self.ChartOrigins[self.NetworkHeadings.index(network)]
self.Graphs.Set_Position(ChartName, origin.x, origin.y + self.IndexHeadings.index(stat) * self.ChartDimentions.y)
def Generate_Date_Range(self, suffix, **kwargs):
"""@return: String of a formatted excel range.
Create an excel formulae for a range"""
sheetname = kwargs.get("sheetname", self.CalculationSheet + " " + suffix)
return "='%s'!%s%d:%s%d" % (sheetname, self.Sheet.num_to_let(self.DateOrigin.col), self.DateOrigin.row,
self.Sheet.num_to_let(self.DateOrigin.col), self.Sheet.getMaxRow(sheetname, self.DateOrigin.col, self.DateOrigin.row))
def Generate_Range(self, suffix, col, **kwargs):
"""@return: String of a formatted excel range.
Create an excel formulae for a range i nthe supplied sheets"""
sheetname = kwargs.get("sheetname", self.CalculationSheet + " " + suffix)
if type(col) is int:
col = self.Sheet.num_to_let(col) # Convert the column number to a corosponding letter to match an excel table
return "='%s'!%s%d:%s%d" % (sheetname, col, self.DateOrigin.row-1,
col, self.Sheet.getMaxRow(sheetname, self.DateOrigin.col, self.DateOrigin.row))
def Analyse_Graphs(self):
"""Layout and create graphs suitable for comparrison and analysis. So place all the graphs on one sheet."""
# Create a new sheet
sheetname = "All Graphs"
self.Sheet.rmvSheet(removeList=[sheetname])
self.Sheet.addSheet(sheetname)
class ORSPlots(object): # ORSCalculator
"""A class to create the SAIDI/SAIFI charts
from the ORS data."""
Timestamp = 'Date'
Cap = 'Cap/Limit'
Target = 'Target'
Collar = 'Collar'
Planned = 'Planned'
Unplanned = 'Unplanned Normalised'
CapUnplanned = 'Unplanned (Normalised Out)'
# Set the order of the headings and data in the sheet
NetworkHeadings = ["ELIN", "OTPO", "TPCO"]
IndexHeadings = ["SAIDI", "SAIFI"]
DataHeadings = [Cap, Target, Collar, Planned, Unplanned, CapUnplanned]
def __init__(self, ORSCalculatorInstance, xlInstance):
self.ORS = ORSCalculatorInstance
self.xlInst = xlInstance
self.Sheet = Sheet(self.xlInst)
self.InputSheet = "Input"
self.CalculationSheet = "Calculation"
self.StatsSheet = "Statistics"
self.Graphs = XlGraphs(xlInstance, self.Sheet)
self.srcRowOffset = 2 # The number of rows taken up by headings (before the data begins)
self.srcColumnOffset = 1 # The number of columns that the data is offset by
# Set the order of the headings and data in the sheet
self.NetworkHeadings = ["ELIN", "OTPO", "TPCO"]
self.IndexHeadings = ["SAIDI", "SAIFI"]
self.DateOrigin = pos(
row=4,
col=1) # The postion to place the fist date on the Excel sheet
# Graph offsets and dimentions
self.ChartDimentions = pos(x=700, y=480)
self.ChartOrigins = []
x = 85
y = 100
for network in self.NetworkHeadings:
self.ChartOrigins.append(pos(x=x, y=y))
x += self.ChartDimentions.x # Shift the charts for the next network to the right
def __repr__(self):
"""Return an identifying instance of the network being handled"""
return "in the __repr__ for ORSPlots" + self.ORS.networknames[0]
def Annual_Sheets(self, dates):
"""Create a new sheet for every indvidual year in the ORS results"""
for date in dates:
year = str(date.year)
self.Create_Sheet(year)
self.Fill_Dates(date, year)
self.Populate_Fixed_Stats(
year) # Com Com table values scaled linearly
self.Populate_Daily_Stats(date,
year) # Daily real world SAIDI/SAIDI
self.Create_Graphs(date)
def Clean_Workbook(self):
self.Sheet.rmvSheet(keepList=[self.InputSheet, self.StatsSheet])
def Create_Sheets(self, dates):
"""Creates all the sheets needed in the workbook.
Autofills the dates. All other data is network specific"""
for date in dates:
year = str(date.year)
self.Create_Sheet(year)
self.Fill_Dates(date, year)
def Create_Sheet(self, suffix):
"""Create the "Calculation" sheet in excel, delete it if it already exisits"""
suffix = " " + suffix
# Remove the sheet, if it exists, then re-add it -- Don't do this when adding multiple networks
self.Sheet.rmvSheet(removeList=[self.CalculationSheet + suffix])
self.Sheet.addSheet(self.CalculationSheet + suffix)
DataOrigin = pos(row=4, col=1)
LeftHeadings = [self.Timestamp]
TableHeadings = LeftHeadings + \
len(self.NetworkHeadings) * len(self.IndexHeadings) * self.DataHeadings
# Assign row 3 data
self.Sheet.setRange(
self.CalculationSheet + suffix, DataOrigin.row - 1, DataOrigin.col,
[TableHeadings]) # Write the row headings into the table
self.Sheet.mergeCells(self.CalculationSheet + suffix, 1,
DataOrigin.col, DataOrigin.row - 1,
DataOrigin.col) # Date Cells
# Assign row 2 data
col = DataOrigin.col + len(LeftHeadings)
columns = list(
np.linspace(
col, col +
(len(self.NetworkHeadings) * len(self.IndexHeadings) - 1) *
len(self.DataHeadings),
len(self.NetworkHeadings) * len(self.IndexHeadings)))
index = 0
for col in columns:
self.Sheet.setRange(
self.CalculationSheet + suffix, DataOrigin.row - 2, int(col),
[[self.IndexHeadings[index % len(self.IndexHeadings)]]]
) # self.IndexHeadings[int((col-len(LeftHeadings)) % len(self.IndexHeadings))]
index += 1
self.Sheet.mergeCells(
self.CalculationSheet + suffix, 2, col, 2,
col - 1 + len(self.NetworkHeadings) * len(self.IndexHeadings) *
len(self.DataHeadings) /
(len(self.NetworkHeadings) * len(self.IndexHeadings))) # Row 2
# Assign row 1 data
col = DataOrigin.col + len(LeftHeadings)
columns = list(
np.linspace(
col, col +
(len(self.NetworkHeadings) * len(self.IndexHeadings) - 2) *
len(self.DataHeadings), len(self.NetworkHeadings)))
index = 0
for col in columns:
self.Sheet.setRange(
self.CalculationSheet + suffix, DataOrigin.row - 3, int(col),
[[self.NetworkHeadings[index % len(self.NetworkHeadings)]]])
index += 1
self.Sheet.mergeCells(
self.CalculationSheet + suffix, 1, col, 1,
col - 1 + len(self.NetworkHeadings) * len(self.IndexHeadings) *
len(self.DataHeadings) / len(self.NetworkHeadings)) # Row 1
def Generate_Dates(self, startdate, enddate=None):
"""Generate an array of all the days for 1 fincial year"""
if type(enddate) != datetime.datetime:
enddate = datetime.datetime(startdate.year + 1, 3, 31)
dt = datetime.timedelta(1) # A time delta of 1 day
TimeStamps = []
while startdate <= enddate:
TimeStamps.append([startdate])
startdate = startdate + dt
return TimeStamps
def Fill_Dates(self, date, suffix):
"""Fill the date column in the Excel sheet with the date values read from the parser.
Also performs and autofit (width) and freezes panes."""
suffix = " " + suffix
row, col = \
self.Sheet.setRange(self.CalculationSheet+suffix, self.DateOrigin.row, self.DateOrigin.col, self.Generate_Dates(date))
self.Sheet.autofit(self.CalculationSheet + suffix, self.DateOrigin.col)
ActiveWindow = self.xlInst.xlApp.Windows(self.xlInst.xlBook.Name)
ActiveWindow.SplitColumn = 1
ActiveWindow.SplitRow = 3
ActiveWindow.FreezePanes = True
def _Correct_Graph_Slope(self, suffix, enddate=datetime.datetime.now()):
"""When the data is truncated for stacked area charts we need two of the same end dates
in a row to create a vertical drop on the graph, otherwise it slopes with delta x = one time interval"""
suffix = " " + suffix
searchterm = datetime.datetime(enddate.year, enddate.month,
enddate.day)
searchterm = searchterm.strftime(
str(searchterm.day) + "/%m/%Y"
) # Get rid of the zero padding (%e and %-d don't work on Windows)
maxrow = self.Sheet.getMaxRow(self.CalculationSheet + suffix, 1, 4)
results = self.Sheet.search(
shtRange(self.CalculationSheet + suffix, None, 4, 1, maxrow, 1),
searchterm)
if len(results) == 1:
# If there is only one date, we haven't corrected slope yet so do it now
self.Sheet.setCell(self.CalculationSheet + suffix,
results[0].Row + 1, 1, results[0].Value)
def _Correct_Graph_Axis(self, ChartName, enddate=datetime.datetime.now()):
"""Adds the final end of year date to the x axis e.g. 31/3/xxxx"""
self.Graphs.Set_Max_X_Value(ChartName, enddate)
def Populate_Fixed_Stats(self, suffix):
"""Create series values for the Limit, Cap, and Collar.
Populate the excel sheet with these values."""
suffix = " " + suffix
network = self.ORS.networknames[0]
if network == "ELIN":
ColOffset = 0
elif network == "OTPO":
ColOffset = len(self.IndexHeadings) * len(self.DataHeadings)
elif network == "TPCO":
ColOffset = len(self.IndexHeadings) * len(
self.DataHeadings) * (len(self.NetworkHeadings) - 1)
RowHeadings = ["CAP", "TARGET", "COLLAR"
] # The order the rows appear in the Excel spreadsheet
self.Sheet.set_calculation_mode("manual")
# Assumes that the data will start in col 2 i.e. col 1 is for the dates
Column = 2 + ColOffset
OriginCol = Column
for param in self.IndexHeadings:
for heading in RowHeadings:
LinearRange = np.linspace(
0,
self.ORS._get_CC_stats(heading)[self.IndexHeadings.index(
param)],
self.Sheet.getMaxRow(self.CalculationSheet + suffix, 1, 4)
- 3 + 1)[1:]
self.Sheet.setRange(self.CalculationSheet + suffix, 4, Column,
[[i] for i in LinearRange])
Column += 1
Column += len(self.DataHeadings) - len(RowHeadings)
self.Sheet.set_calculation_mode("automatic")
def _Calc_Rows(self, dates, ORS):
SAIDIcol = []
SAIFIcol = []
for day in dates:
SAIDIrow = []
SAIFIrow = []
x, y = ORS._get_indicies(day, "planned", applyBoundary=True)
SAIDIrow.append(x)
SAIFIrow.append(y)
x, y = ORS._get_indicies(day, "unplanned", applyBoundary=True)
SAIDIrow.append(x)
SAIFIrow.append(y)
x, y = ORS._get_indicies(day, "unplanned", applyBoundary=False)
SAIDIrow.append(x - SAIDIrow[1])
SAIFIrow.append(y - SAIFIrow[1])
SAIDIcol.append(SAIDIrow)
SAIFIcol.append(SAIFIrow)
# Here for debugging only
#row=4
#self.Sheet.setRange(sheetname, row, ColOffset, [SAIDIrow])
#self.Sheet.setRange(sheetname, row, ColOffset+len(self.DataHeadings), [SAIFIrow])
#row += 1
return SAIDIcol, SAIFIcol
def Populate_Daily_Stats(self, enddate, FiscalYear):
"""Create series values for the Planned and Unplanned SAIDI/SAIFI.
Populate the excel sheet with these values."""
# enddate = datetime.datetime.now() (default option)
#FiscalYear = str(self._get_fiscal_year(enddate))
sheetname = self.CalculationSheet + " " + FiscalYear
if enddate > datetime.datetime.now():
enddate = datetime.datetime.now()
network = self.ORS.networknames[0]
ColOffset = 5 # Magic number: offset of the column in the data table
if network == "ELIN":
ColOffset += 0
elif network == "OTPO":
ColOffset += len(self.DataHeadings) * len(self.IndexHeadings)
elif network == "TPCO":
ColOffset += len(self.DataHeadings) * len(
self.IndexHeadings) * (len(self.NetworkHeadings) - 1)
self.Sheet.set_calculation_mode("manual")
startdate = self.Sheet.getDateTime(self.Sheet.getCell(sheetname, 4, 1))
lastdate = self.Sheet.getDateTime(
self.Sheet.getCell(sheetname,
self.Sheet.getMaxRow(sheetname, 1, 4), 1))
delta_days = (lastdate - startdate).days + 1
FiscalyearDays = [
startdate + datetime.timedelta(days=i) for i in range(delta_days)
]
# Truncate the data if it is for the present year
maxrow = self.Sheet.getMaxRow(sheetname, 1, 4)
#enddate = datetime.datetime.now() # Use this variable to force the graphs to only display a limited set of information
searchterm = datetime.datetime(enddate.year, enddate.month,
enddate.day)
searchterm = searchterm.strftime(
str(searchterm.day) + "/%m/%Y"
) # Get rid of the zero padding (%e and %-d don't work on Windows)
searchresult = self.Sheet.search(
shtRange(sheetname, None, 4, 1, maxrow, 1), searchterm)
if len(searchresult) == 1 or len(
searchresult
) == 2: # There could be two dates, if we are duplicating the end date to achieve a vertical truncation on our stacked area chart
StopTime = self.Sheet.getDateTime(searchresult[0].Value)
else:
StopTime = FiscalyearDays[
-1] # update this to be FiscalyearDays[0] now?
SAIDIcol, SAIFIcol = self._Calc_Rows(FiscalyearDays, self.ORS)
# The table columns need to be cummulative
SAIDIsums = [0 for i in self.DataHeadings[3:]]
SAIFIsums = [0 for i in self.DataHeadings[3:]]
SAIDITable = []
SAIFITable = []
row = 4
# Loop through every row
for SAIDIrow, SAIFIrow, day in zip(SAIDIcol, SAIFIcol, FiscalyearDays):
ColumnIndex = 0
# Loop through every column
for SAIDIval, SAIFIval in zip(SAIDIrow, SAIFIrow):
# Add the new rows to the table stored in memmory
if day <= StopTime: # means we will stop on the current day, but then fixing graph slopes break
SAIDIsums[ColumnIndex] += SAIDIval
SAIFIsums[ColumnIndex] += SAIFIval
else:
SAIDIsums[ColumnIndex] = None
SAIFIsums[ColumnIndex] = None
ColumnIndex += 1
#self.Sheet.setRange(sheetname, row, ColOffset, [SAIDIsums])
#self.Sheet.setRange(sheetname, row, ColOffset+len(self.DataHeadings), [SAIFIsums])
SAIDITable.append(
SAIDIsums[:]) # This copys by value, not by reference
SAIFITable.append(
SAIFIsums[:]) # This copys by value, not by reference
row += 1
self.Sheet.setRange(sheetname, 4, ColOffset, SAIDITable)
self.Sheet.setRange(sheetname, 4, ColOffset + len(self.DataHeadings),
SAIFITable)
self._Correct_Graph_Slope(
FiscalYear, StopTime
) # Makes the area plot look a bit better, but mutates the source data, so must be run last
self.Sheet.set_calculation_mode("automatic")
def _get_fiscal_year(self, enddate):
"""Get the fiscal year as defined in NZ. Returns the year as a int"""
year = enddate.year
if enddate.month - 4 < 0:
year -= 1
return year
def Create_Graphs(self, enddate, sheetname_suffix):
"""Create the SAIDI/SAIFI chart
@param enddate: Used to set the x axis on the chart and name the chart"""
FiscalYear = self._get_fiscal_year(
enddate) # (default option for sheetname)
# Always set the end date of the graph to be the 1/4/xxxx (you may want to change this in future)
if True:
graphenddate = datetime.datetime(FiscalYear + 1, 4, 1)
# Used to set the name of the chart...
if enddate > datetime.datetime.now():
enddate = datetime.datetime.now()
network = self.ORS.networknames[0]
ColOffset = 2 # Magic number: where the data starts in the table (column 2)
FullName = ""
if network == "ELIN":
ColOffset += 0
chartpath = os.path.expanduser(
'~/Documents/SAIDI and SAIFI/Templates/ORSChartEIL2.crtx')
FullName = "Electricity Invercargill"
elif network == "OTPO":
ColOffset += len(self.DataHeadings) * len(self.IndexHeadings)
chartpath = os.path.expanduser(
'~/Documents/SAIDI and SAIFI/Templates/ORSChartOJV2.crtx')
FullName = "OtagoNet+ESL"
elif network == "TPCO":
ColOffset += len(self.DataHeadings) * len(
self.IndexHeadings) * (len(self.NetworkHeadings) - 1)
chartpath = os.path.expanduser(
'~/Documents/SAIDI and SAIFI/Templates/ORSChartTPC2.crtx')
FullName = "The Power Company"
ylables = [
"Average Outage Duration (Minutes/ICP)",
"Average No. Outages (Interruptions/ICP)"
]
for stat in self.IndexHeadings:
ChartName = datetime.datetime(enddate.year, enddate.month,
enddate.day)
ChartName = ChartName.strftime(str(ChartName.day) + "/%m/%Y")
ChartName = FullName + " " + stat + " YTD as at " + ChartName # Make sure there is no zero padding on the day
# Check for a duplicate chart name...
NumDuplicates = 0
TempName = ChartName
while self.Graphs.Chart_Exists(TempName):
TempName = ChartName
NumDuplicates += 1
TempName += " (" + str(NumDuplicates) + ")"
ChartName = TempName
self.Graphs.Create_Chart(
ChartName,
self.Generate_Date_Range(sheetname_suffix),
sheetname=self.CalculationSheet + " " + sheetname_suffix)
# Add the indvidual series to the chart
i = self.IndexHeadings.index(stat) * (len(self.DataHeadings) *
(len(self.IndexHeadings) - 1)
) # Add a sub-offset
for col in range(i + ColOffset,
i + ColOffset + len(self.DataHeadings)):
self.Graphs.Add_Series(ChartName,
self.Generate_Range(
sheetname_suffix, col),
serieslabels=True)
# Apply the templated style, reapply attributes like ylabel
self.Graphs.Apply_Template(
ChartName,
chartpath,
ylabel=ylables[self.IndexHeadings.index(stat)],
ylabelsize=15)
# Apply a fix to the final dates on the graph
self._Correct_Graph_Axis(ChartName, graphenddate)
# Make the chart bigger
self.Graphs.Set_Dimentions(ChartName, self.ChartDimentions.x,
self.ChartDimentions.y)
# Stack charts from the same network vetically
origin = self.ChartOrigins[self.NetworkHeadings.index(network)]
self.Graphs.Set_Position(
ChartName, origin.x, origin.y +
self.IndexHeadings.index(stat) * self.ChartDimentions.y)
def Generate_Date_Range(self, suffix, **kwargs):
"""@return: String of a formatted excel range.
Create an excel formulae for a range"""
sheetname = kwargs.get("sheetname",
self.CalculationSheet + " " + suffix)
return "='%s'!%s%d:%s%d" % (
sheetname, self.Sheet.num_to_let(self.DateOrigin.col),
self.DateOrigin.row, self.Sheet.num_to_let(self.DateOrigin.col),
self.Sheet.getMaxRow(sheetname, self.DateOrigin.col,
self.DateOrigin.row))
def Generate_Range(self, suffix, col, **kwargs):
"""@return: String of a formatted excel range.
Create an excel formulae for a range i nthe supplied sheets"""
sheetname = kwargs.get("sheetname",
self.CalculationSheet + " " + suffix)
if type(col) is int:
col = self.Sheet.num_to_let(
col
) # Convert the column number to a corosponding letter to match an excel table
return "='%s'!%s%d:%s%d" % (
sheetname, col, self.DateOrigin.row - 1, col,
self.Sheet.getMaxRow(sheetname, self.DateOrigin.col,
self.DateOrigin.row))
def Analyse_Graphs(self):
"""Layout and create graphs suitable for comparrison and analysis. So place all the graphs on one sheet."""
# Create a new sheet
sheetname = "All Graphs"
self.Sheet.rmvSheet(removeList=[sheetname])
self.Sheet.addSheet(sheetname)
class ParseORS(object):
NetworkNames = ["ELIN", "TPCO", "OTPO", "LLNW"] # The order the network names appear in the input spreadsheet
InputSheet = "Input" # The name of the Excel sheet used for user input
Months = ["March (yr 0)", "April", "May", "June", "July", "August", "September", "October",
"November", "December", "January", "February", "March (yr 1)"] # The months in a fiscal year
def __init__(self, xlInstance):
self.xlInst = xlInstance
self.Sheet = Sheet(xlInstance)
# Create some dictionaries to hold customer numbers
self.Customer_Nums = {}
for name in self.NetworkNames:
self.Customer_Nums[name] = {}
# Create a ordered list of network charts/tables to display
self.StartDates = []
def Read_Num_Cust(self):
"""Read the average number of customers"""
o = pos(row=3, col=1)
Table = self.Sheet.getRange(self.InputSheet, o.row, o.col,
self.Sheet.getMaxRow(self.InputSheet, o.col, o.row),
self.Sheet.getMaxCol(self.InputSheet, o.col, o. row))
# Get the number of customers - fill up the dictionaries
for row in Table:
cell = row.__iter__()
year = cell.next().year # Skip to column B
cell.next() # Skip over column C
for name in self.NetworkNames:
self.Customer_Nums[name][year] = cell.next() # Get the value of the current cell and incriment the address pointer
return self.Customer_Nums
def _Read_Last_Date(self):
"""Read the last date the user has input data for"""
o = pos(row=3, col=1)
# Read the last date in the yearly table
FinalDate = self.Sheet.getCell(self.InputSheet, o.col,
self.Sheet.getMaxCol(self.InputSheet, o.col, o. row))
return self.Sheet.getDateTime(FinalDate)
def Read_Num_Cust_Final(self):
"""Read the number of customers in the final year only.
This is data from the by month table."""
o = pos(row=3, col=1)
MonthlyICPs = {}
# Read the last date in the yearly table
FinalDate = self._Read_Last_Date()
Table = self.Sheet.getRange(self.InputSheet, o.row, o.col,
self.Sheet.getMaxRow(self.InputSheet, o.col, o.row),
self.Sheet.getMaxCol(self.InputSheet, o.col, o. row))
def Read_Dates_To_Publish(self):
"""Read the dates that require results to be outputted.
Returns the date range expected by the ORS calcualtor."""
o = pos(row=3, col=1)
Table = self.Sheet.getRange(self.InputSheet, o.row, o.col,
self.Sheet.getMaxRow(self.InputSheet, o.col, o.row),
self.Sheet.getMaxCol(self.InputSheet, o.col, o. row))
# Get the number of customers - fill up the dictionaries
for row in Table:
if row[-1] == "Y" or row[-1] == "y":
self.StartDates.append(datetime.datetime(row[0].year-1, 4, 1)) # Substract 1 from the end of fiscal year to get the start of the year
if len(self.StartDates):
return min(self.StartDates), datetime.datetime(max(self.StartDates).year+1, 3, 31) # Add 1 back to the year, so we get the end of fiscal year
else:
return None, None
def Read_Last_Date(self):
"""Read (get) the last date field"""
o = pos(row=17, col=12)
lastdate = self.Sheet.getCell(self.InputSheet, o.row, o.col)
try:
return self.Sheet.getDateTime(lastdate)
except AttributeError:
# The user entered an invalid date
return datetime.datetime.now()
def Restore_Input_Default(self):
"""Restore the default state of the table that has all the ICP counts for each network"""
o = pos(row=3, col=1)
OutputTable = []
for year in CUST_NUMS.get(self.NetworkNames[0]):
RowData = [datetime.datetime(year, 3, 31), year-1]
for name in self.NetworkNames:
RowData.append(CUST_NUMS.get(name).get(year))
#RowData.append("Y") # The default option is to display the sheet
OutputTable.append(RowData)
# Sort the rows in the table so that we have the dates in cronological order
OutputTable = sorted(OutputTable, key=lambda e: e[0], reverse=False)
# Set the output range in excel
self.Sheet.setRange(self.InputSheet, o.row-1, o.col+2, [self.NetworkNames])
self.Sheet.setRange(self.InputSheet, o.row, o.col, OutputTable)
def Set_Mean_ICPs(self, lastyear):
"""Sets the mean (average) number of ICPS in the annual table from
records in the monthly table. The date 31/03/XXXX must appear in the
main annual data table, otherwise no updates will occur."""
o_table1 = pos(row=3, col=1) # Top left cnr of annual table
o_table2 = pos(row=3, col=11) # Top left cnr of the monthly table
# Table Rows: [Month name, Year of months occurance, self.NetworkNames[]]
# Find the average number of ICPs
coloffset = 2 # Offset of ICP data from the left most column in the table
maxrow = 15 # Last row in the table
for network in self.NetworkNames:
# Find the number of records in the monthly table
lastrow = self.Sheet.getMaxRow(self.InputSheet, coloffset + o_table2.col, o_table2.row)
if lastrow > maxrow:
lastrow = o_table2.row
# Compute the average number of ICPs (using the first and last row in the monthly table,
# in the case of only a single month, then the first and last row are the same).
avrg = (self.Sheet.getCell(self.InputSheet, lastrow, coloffset + o_table2.col) + \
self.Sheet.getCell(self.InputSheet, o_table2.row, coloffset + o_table2.col)) / 2
# Place the average in the specified record
# Input param replcaed the line below
#lastyear = int(self.Sheet.getCell(self.InputSheet, o_table2.row, o_table2.col + 1)) + 1
try:
lastrow = self.Sheet.brief_search(self.InputSheet, "31/03/"+str(lastyear+1)).Row
self.Sheet.setCell(self.InputSheet, lastrow, coloffset + o_table1.col, avrg)
except:
pass
coloffset += 1
def Restore_Table_2(self, lastyear=None):
"""Builds the table for gathering ICP data by month"""
o_table1 = pos(row=3, col=1) # Top left cnr of annual table
o_table2 = pos(row=3, col=11) # Top left cnr of the monthly table
# Get the row for the last date in the annual data table (table1)
lastrow = self.Sheet.getMaxRow(self.InputSheet, o_table1.col, o_table1.row)
if lastrow > 10000:
lastrow = o_table1.row
# If lastyear is None, then the last row in year in the last row of the
# annual data table (table1) is used.
if not lastyear:
lastyear = self.Sheet.getDateTime(
self.Sheet.getCell(self.InputSheet, lastrow, o_table1.col)).year
# Build the two left most column of table 2 (Months names, Fiscal year)
rowindex = 0
# Iterate over an array of months (arranged in order for a fiscal year)
for month in self.Months:
fiscalyear = lastyear
if self.Months.index("December") >= self.Months.index(month):
# We are in first 3/4 of the year
fiscalyear -= 1
# Write each row in table2 (first two columns only), for example, "March (yr0), 2016"
self.Sheet.setRange(self.InputSheet, o_table2.row+rowindex, o_table2.col, [(month, fiscalyear)])
rowindex += 1
# DON'T DO THIS! This will copy the last years average... we want the number of ICPs
# as of the 31st March on the previous fiscal year instead...
# Automatically copy the previous years data to record 0
##lastrow -= 1 # Get the previous years data
##if lastrow <= o_table1.row:
## lastrow = o_table1.row
##previousrange= self.Sheet.getRange(self.InputSheet, lastrow, o_table1.col+2, lastrow, o_table1.col+5)
##self.Sheet.setRange(self.InputSheet, o_table2.row, o_table2.col+2, previousrange)
def Set_Year(self, year):
"""Used to update the table labels at the end/start of a new fiscal year"""
o_table1 = pos(row=3, col=1) # Top left cnr of annual table
o_table2 = pos(row=3, col=11) # Top left cnr of the monthly table
# Try and find the year in table1, otherwise create a new row(s)
lastrow = self.Sheet.getMaxRow(self.InputSheet, o_table1.col, o_table1.row)
if lastrow > 10000:
lastrow = o_table1.row
try:
lastyear = self.Sheet.getDateTime(
self.Sheet.getCell(self.InputSheet, lastrow, o_table1.col)).year
except:
lastyear = datetime.datetime.strptime(
self.Sheet.getCell(self.InputSheet, lastrow, o_table1.col),
"%d/%m/%Y").year
# Change the labels on table 2 to match the selected year
self.Restore_Table_2(year+1)
while lastyear <= year:
# We need to create some new rows in table1
lastrow += 1
lastyear += 1
self.Sheet.setRange(self.InputSheet, lastrow, o_table1.col, [(datetime.datetime(lastyear, 3, 31), lastyear-1)])