TLDR: I don't develop for a living. If someone could point me in the right direction to make my script more readable/pythonic I would appreciate the assistance.
In particular I'm struggling with the charts found in makeCharts() and code-flow throughout.
I have tried writing makeCharts() section as a for loop for workbook.add_chart({"type": "pie"}) as piechart, but received a _exit error which I guess makes sense as I'm not writing till the end of the function.
Background: We use a Security Incident Response Platform which has a available API but somewhat awful reporting. To offset this I have been writing a personal project script while learning pandas which pulls the last 30/60/90 day case metrics and creates some charts. The below is my current complete script sans identifying information.
Objectives: I really have been struggling to make this script:
A) More Pythonic (specifically makeCharts() and main()).
B) Faster I timed it at 42seconds across 350+ entries, which leads me to believe my main() loop could be improved.
C) Easier to read code-flow in the process of addressing the above.
# imports
# defined globals
start = time.time()
def main(api):
# Finds all cases on SIRP endpoint
# Creates all30, all60, all90 dictionaries from epoch
all30, all60, all90 = {}, {}, {}
response = api.find_cases(range="all", sort=[])
def addToDict(arg, response, i):
arg[(i)] = {
"Name":
response.json()[i]["title"],
"ID":
response.json()[i]["id"],
"Owner":
response.json()[i]["owner"],
"Severity":
response.json()[i]["severity"],
"Created": (time.strftime(
"%m/%d/%Y %H:%M:%S",
time.gmtime(response.json()[i]["createdAt"] / 1000.0)))
}
if 'endDate' in response.json()[i]:
arg[(i)].update({
"Closed": (time.strftime(
"%m/%d/%Y %H:%M:%S",
time.gmtime(response.json()[i]["endDate"] / 1000.0))),
"Resolution":
response.json()[i]["resolutionStatus"]
})
return
if response.status_code == 200:
i = 0
while i < len(response.json()):
if (response.json()[i]["createdAt"] / 1000) > time.mktime(
(datetime.date.today() -
datetime.timedelta(days=30)).timetuple()):
addToDict(all30, response, i)
if (response.json()[i]["createdAt"] / 1000) > time.mktime(
(datetime.date.today() -
datetime.timedelta(days=60)).timetuple()):
addToDict(all60, response, i)
else:
addToDict(all90, response, i)
i += 1
makeSS(all30, all60, all90)
def makeSS(all30, all60, all90):
# creates (4) dataframes in pandas
# all30/df1 - 30day dataframe from all30 dict
# all60/df2 - 60day dataframe from all60 dict
# all90/df3 - 90 day dataframe from all90 dict
# df4 - separate sheet for chart data
df1 = pd.DataFrame(
all30,
index=['Created', 'Severity', 'Owner', 'Name', 'Closed',
'Resolution']).transpose()
df2 = pd.DataFrame(
all60,
index=['Created', 'Severity', 'Owner', 'Name', 'Closed',
'Resolution']).transpose()
df3 = pd.DataFrame(
all90,
index=['Created', 'Severity', 'Owner', 'Name', 'Closed',
'Resolution']).transpose()
df4 = pd.DataFrame({
'Created': (df1.count()['Created']),
'Closed': (df1.count()['Closed']),
'Owner': (df1['Owner'].value_counts().to_dict()),
'Resolution': (df1['Resolution'].value_counts().to_dict()),
'Severity': (df1['Severity'].value_counts().to_dict())
})
makeCharts(df1, df2, df3, df4)
def makeCharts(df1, df2, df3, df4):
# create pie charts, xlsx and save locally
with pd.ExcelWriter("foo.xlsx",
engine="xlsxwriter",
options={"strings_to_urls": False}) as writer:
workbook = writer.book
worksheet = workbook.add_worksheet("Summary Charts")
worksheet.hide_gridlines(2)
piechart = workbook.add_chart({"type": "pie"})
piechart.set_title({'name': 'New vs. Closed Cases'})
piechart.set_style(10)
piechart.add_series({
'name': 'Open vs. Closed Cases Last 30',
'categories': '=Tracking!$B$1:$C$1',
'values': '=Tracking!$B$2:$C$2',
})
worksheet.insert_chart("D2", piechart, {
'x_offset': 25,
'y_offset': 10
})
piechart1 = workbook.add_chart({"type": "pie"})
piechart1.set_title({'name': 'Severities'})
piechart1.set_style(10)
piechart1.add_series({
'name': 'Severity Last 30',
'categories': '=Tracking!$A$2:$A$4',
'values': '=Tracking!$F$2:$F$4',
})
worksheet.insert_chart("M2", piechart1, {
'x_offset': 25,
'y_offset': 10
})
piechart2 = workbook.add_chart({"type": "pie"})
piechart2.set_title({'name': 'Resolution Last 30'})
piechart2.set_style(10)
piechart2.add_series({
'name': 'Resolution Last 30',
'categories': '=Tracking!$A$5:$A$6',
'values': '=Tracking!$E$5:$E$6',
})
worksheet.insert_chart("D19", piechart2, {
'x_offset': 25,
'y_offset': 10
})
piechart3 = workbook.add_chart({"type": "pie"})
piechart3.set_title({'name': 'Case Ownership Last 30'})
piechart3.set_style(10)
piechart3.add_series({
'name': 'Case Ownership Last 30',
'categories': '=Tracking!$A$7:$A$10',
'values': '=Tracking!$D$7:$D$10',
})
worksheet.insert_chart("M19", piechart3, {
'x_offset': 25,
'y_offset': 10
})
df1.to_excel(writer,
sheet_name="Cases newer than 30 Days",
startrow=1,
header=False)
formatSS((writer.sheets["Cases newer than 30 Days"]), workbook, df1)
df2.to_excel(writer,
sheet_name="Cases older than 60 days",
startrow=1,
header=False)
formatSS((writer.sheets["Cases older than 60 days"]), workbook, df2)
df3.to_excel(writer,
sheet_name="Cases older than 90 days",
startrow=1,
header=False)
formatSS((writer.sheets["Cases older than 90 days"]), workbook, df3)
df4.to_excel(writer, sheet_name="Tracking")
writer.save()
send()
def formatSS(worksheet, workbook, arg):
# add width to columns, filter, freeze
worksheet.set_column("A:A", 3.5, workbook.add_format())
worksheet.set_column("B:B", 17.25, workbook.add_format())
worksheet.set_column("C:C", 10, workbook.add_format())
worksheet.set_column("D:D", 10, workbook.add_format())
worksheet.set_column("E:E", 100, workbook.add_format())
worksheet.set_column("F:F", 17.25, workbook.add_format())
worksheet.set_column("G:G", 11.25, workbook.add_format())
worksheet.freeze_panes(1, 0)
worksheet.autofilter("A1:G100")
header_format = workbook.add_format({
"bold": True,
"text_wrap": True,
"valign": "top",
"fg_color": "#CCCCCC",
"border": 1,
})
for col_num, value in enumerate(arg.columns.values):
worksheet.write(0, col_num + 1, value, header_format)
return worksheet, workbook
def send():
# send the created xlsx
msg = MIMEMultipart()
msg["From"] = "[email protected]"
msg["To"] = sendTo
msg["Subject"] = "Metrics"
msg.attach(
MIMEText("Attached are the requested case metrics in .XLSX format."))
part = MIMEBase("application", "octet-stream")
part.set_payload(open("Foo.xlsx", "rb").read())
encoders.encode_base64(part)
part.add_header("Content-Disposition",
'attachment; filename="Foo.xlsx"')
msg.attach(part)
smtp = smtplib.SMTP(smtp_server)
smtp.starttls()
smtp.sendmail(msg["From"], [msg["To"]], msg.as_string())
smtp.quit()
main(api)
print('It took', time.time() - start, 'seconds.')
exit()
