96 lines
3.9 KiB
Python
96 lines
3.9 KiB
Python
#!/usr/bin/python3.8
|
|
import pandas as pd
|
|
import os
|
|
import glob
|
|
|
|
|
|
def init():
|
|
global df
|
|
|
|
#list_of_files = glob.glob('*.xlsx') # * means all if need specific format then *.csv
|
|
latest_file = max(glob.iglob('*.xlsx'),key=os.path.getctime)
|
|
|
|
if latest_file.startswith("~$"):
|
|
latest_file=latest_file.replace("~$", "")
|
|
|
|
print(latest_file)
|
|
df = pd.read_excel(latest_file, engine="openpyxl", sheet_name="daily")
|
|
|
|
if df.size > 0:
|
|
createHtmlSummary(df)
|
|
|
|
|
|
def createHtmlSummary(df):
|
|
|
|
#df = self.totalall
|
|
|
|
df=df.sort_values(by=['HUB'])
|
|
unique_hub=df["HUB"].unique()
|
|
|
|
SummaryString='<html><body><style> table, th, td { border: 1px solid black; border-collapse: collapse; padding: 10px; text-align: center; }</style><br><p><b>Processed SLO count: '+ str(len(df)) +'</b></p><table><tr><th></th>'
|
|
|
|
|
|
#Adding colum headers for ever hub
|
|
for hub in unique_hub:
|
|
SummaryString+="<th>"+hub+"</th>"
|
|
|
|
SummaryString+="<th>total</th>"
|
|
|
|
|
|
####################################
|
|
####### Between 99 and 100 ##########
|
|
####################################
|
|
SummaryString+='</tr><tr><td style="background-color:Green;">>= 99</td>'
|
|
|
|
tmp99 = df[(df.evaluatedPercentage >=99) & (df.evaluatedPercentage <= 100)].groupby("HUB")["evaluatedPercentage"].count().reset_index()
|
|
|
|
for hub in unique_hub:
|
|
#for index, row in tmp99.iterrows():
|
|
SummaryString+='<td>{0}</td>'.format(tmp99[(tmp99.HUB == hub)]["evaluatedPercentage"].values[0] if tmp99[(tmp99.HUB == hub)].size != 0 else 0)
|
|
|
|
SummaryString+='<td><b>{0}</b></td>'.format(len(df[(df.evaluatedPercentage >=99) & (df.evaluatedPercentage <= 100)]))
|
|
####################################
|
|
####### Between 98 and 99 ##########
|
|
####################################
|
|
SummaryString+='</tr><tr><td style="background-color:Orange;">>= 98</td>'
|
|
|
|
#if tmp98.size != 0:
|
|
tmp98 = df[(df.evaluatedPercentage >=98) & (df.evaluatedPercentage < 99)].groupby("HUB")["evaluatedPercentage"].count().reset_index()
|
|
|
|
for hub in unique_hub:
|
|
SummaryString+='<td>{0}</td>'.format(tmp98[(tmp98.HUB == hub)]["evaluatedPercentage"].values[0] if tmp98[(tmp98.HUB == hub)].size != 0 else 0)
|
|
|
|
SummaryString+='<td><b>{0}</b></td>'.format(len(df[(df.evaluatedPercentage >=98) & (df.evaluatedPercentage < 99)]))
|
|
|
|
####################################
|
|
####### Between 0 and 98 ###########
|
|
####################################
|
|
SummaryString+='</tr><tr><td style="background-color:Red;">< 98</td>'
|
|
|
|
tmp0 = df[(df.evaluatedPercentage >=0) & (df.evaluatedPercentage < 98)].groupby("HUB")["evaluatedPercentage"].count().reset_index()
|
|
|
|
for hub in unique_hub:
|
|
SummaryString+='<td>{0}</td>'.format(tmp0[(tmp0.HUB == hub)]["evaluatedPercentage"].values[0] if tmp0[(tmp0.HUB == hub)].size != 0 else 0)
|
|
|
|
SummaryString+='<td><b>{0}</b></td>'.format(len(df[(df.evaluatedPercentage >=0) & (df.evaluatedPercentage < 98)]))
|
|
|
|
####################################
|
|
####### -1 --> N/A ###########
|
|
####################################
|
|
SummaryString+='</tr><tr><td style="background-color:DodgerBlue;">-1</td>'
|
|
|
|
tmpNA = df[(df.evaluatedPercentage == -1)].groupby("HUB")["evaluatedPercentage"].count().reset_index()
|
|
|
|
for hub in unique_hub:
|
|
SummaryString+='<td>{0}</td>'.format(tmpNA[(tmpNA.HUB == hub)]["evaluatedPercentage"].values[0] if tmpNA[(tmpNA.HUB == hub)].size != 0 else 0)
|
|
|
|
SummaryString+='<td><b>{0}</b></td>'.format(len(df[(df.evaluatedPercentage == -1)]))
|
|
|
|
SummaryString+="</tr></table></body></html>"
|
|
|
|
|
|
with open('summary.txt', 'w') as f:
|
|
f.write(SummaryString)
|
|
|
|
if __name__ == "__main__":
|
|
init() |