#-*- coding:utf-8-*-
import pandas as pd
import os
import numpy as np
import sqlite3
import datetime
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei'] # 指定默认字体为SimHei
plt.rcParams['axes.unicode_minus'] = False # 用来正常显示负号
def select_day(s_day):
global select_today_start
global select_today_end
global select_yesterday_start
d_timedelta = s_day
# d_timedelta = -1
# select_date------------------------------
select_date_start = datetime.date.today() + datetime.timedelta(days=d_timedelta)
select_date_end = datetime.date.today() + datetime.timedelta(days=d_timedelta+1)
select_today_start = datetime.datetime.combine(select_date_start, datetime.datetime.min.time())
select_today_end = datetime.datetime.combine(select_date_end, datetime.datetime.min.time())
# # -------------------start today------------------------------
if (datetime.date.today().weekday() + s_day) == 0:
d_monday = -3 # 周一
# d_timedelta = 0 #
else:
d_monday = -1
select_yesterday_start = select_today_start + datetime.timedelta(days=d_monday)
print('select_today_start is ---------- ', select_today_start)
print('select_today_end is ---------- ', select_today_end)
print('select_yesterday_start is ---------- ', select_yesterday_start)
#-----------end select date--------------------
# def df_normalization(data_type, project_name, project_path="D:\Py"):
now_time = datetime.datetime.strftime(datetime.datetime.now(), "%Y%m%d-%H%M%S")
print("now_time is : ", now_time)
#-----------end select date--------------------
project_path = "D:\Py"
project_name = "M5151"
# project_name = "O17"
data_type = "jira"
# project_name = "M483_DevCloud"
# data_type = "DevCloud"
# project_name = "M483_chandao"
# data_type = "chandao"
project_path = project_path + "\\" + project_name
path_Input = project_path + "\Today_Input"
path_Output = project_path + "\Today_Output"
write_file_name = now_time + ".xlsx"
write_path_file_name = os.path.join(path_Output, write_file_name)
file_savefig = now_time + "_savefig.png"
write_path_savefig = os.path.join(path_Output, file_savefig)
# jira
if data_type == "jira":
# =====================读取jira数据========================
file_name = [ x for x in os.listdir(path_Input) if x.endswith(".csv")]
path_Input_file = os.path.join(path_Input, file_name[0])
# 确保读取路径文件存在
if not os.path.exists(path_Input_file):
raise FileNotFoundError(f"The file {path_Input_file} does not exist.")
df_jira = pd.read_csv(path_Input_file, encoding='GBK')
# df_jira = pd.read_csv(path_Input_file, encoding='utf-8')
print('原始数据---------df_jira_lq.shape', df_jira.shape)
column_jira = ['Issue Type', 'Issue key', 'Issue id', 'Summary', 'Resolution', 'Created', 'Assignee', 'Component/s', 'Priority', 'Reporter', 'Resolved', 'Updated', 'Labels', 'Due Date', 'Status']
df_jira = df_jira[column_jira]
print('选列---------df_jira.shape', df_jira.shape)
df_jira_lq = df_jira[df_jira['Assignee'].astype(str).str.startswith('p-') == True]
print('P开头用户名------------df_jira_lq.shape', df_jira_lq.shape)
df_jira_bug = df_jira_lq[df_jira_lq['Issue Type']== "Bug"]
df_jira_req = df_jira_lq[df_jira_lq['Issue Type']== "Sub-task"]
#============================= start of 读取bug单，转换为标准字段=================================
def regularization_jira(df_jira_bug):
df_bug_model = pd.DataFrame(columns=['ID','状态','严重等级','提交人员','开发人员','测试人员','创建时间','解决时间','更新时间','到期时间','派单时间','解单时间'])
df_bug_model['ID'] = df_jira_bug['Issue id']
df_bug_model['状态'] = df_jira_bug['Status']
df_bug_model['严重等级'] = df_jira_bug['Priority']
df_bug_model['提交人员'] = df_jira_bug['Reporter']
df_bug_model['开发人员'] = df_jira_bug['Assignee']
df_bug_model['测试人员'] = df_jira_bug['Reporter']
df_bug_model['创建时间'] = df_jira_bug['Created']
df_bug_model['解决时间'] = df_jira_bug['Resolved']
df_bug_model['更新时间'] = df_jira_bug['Updated']
df_bug_model['到期时间'] = df_jira_bug['Due Date']
# ==================== start of 数据预处理=========================
df_bug_model['派单时间'] = df_bug_model['创建时间']
df_bug_model['解单时间'] = df_bug_model['解决时间']
return df_bug_model
df_bug_all = regularization_jira(df_jira_bug)
df_req_all = regularization_jira(df_jira_req)
#========================== start of bug配置 ============================
cfg_bug_status_all = ['OPEN','In Progress','Reopened','Integrated','Resolved','Verified','Closed','Qualcomm/MTK/Google','Scheduled','More Info']
cfg_bug_status_DI = ['OPEN','In Progress','Reopened','Integrated','Resolved','Verified','Qualcomm/MTK/Google','More Info']
cfg_bug_status_dev = ['OPEN','In Progress','Reopened','More Info','Qualcomm/MTK/Google']
cfg_bug_status_test = ['Resolved','Verified','Integrated']
cfg_bug_status_close = ['Closed']
cfg_bug_level = ['Blocker', 'Critical', 'Major', 'Minor']
# DevCloud
if data_type == "DevCloud":
# =====================读取DevCloud数据========================
file_name = [ x for x in os.listdir(path_Input) if x.endswith(".xlsx")]
path_Input_file = os.path.join(path_Input, file_name[0])
# 确保读取路径文件存在
if not os.path.exists(path_Input_file):
raise FileNotFoundError(f"The file {path_Input_file} does not exist.")
df_DevCloud_bug = pd.read_excel(path_Input_file, )
print('---------df_DevCloud_bug.shape', df_DevCloud_bug.shape)
column_DevCloud = ['编号','标题','创建人','创建时间','更新时间','结束时间','状态','处理人','重要程度','测试负责人',]
df_DevCloud_bug = df_DevCloud_bug[column_DevCloud]
#============================= start of 读取bug单，转换为标准字段=================================
def regularization_DevCloud(df_DevCloud_bug):
df_bug_model = pd.DataFrame(columns=['ID','状态','严重等级','提交人员','开发人员','测试人员','创建时间','解决时间','更新时间','到期时间','派单时间','解单时间'])
df_bug_model['ID'] = df_DevCloud_bug['编号']
df_bug_model['状态'] = df_DevCloud_bug['状态']
df_bug_model['严重等级'] = df_DevCloud_bug['重要程度']
df_bug_model['提交人员'] = df_DevCloud_bug['创建人']
df_bug_model['开发人员'] = df_DevCloud_bug['处理人']
df_bug_model['测试人员'] = df_DevCloud_bug['测试负责人']
df_bug_model['创建时间'] = df_DevCloud_bug['创建时间'].apply(lambda x: str(x))
# df_bug_model['解决时间'] = df_DevCloud_bug['Resolved']
df_bug_model['更新时间'] = df_DevCloud_bug['更新时间'].apply(lambda x: str(x))
df_bug_model['到期时间'] = df_DevCloud_bug['结束时间'].apply(lambda x: str(x))
# ==================== start of 数据预处理=========================
# df_bug_model['派单时间'] = df_bug_model['创建时间']
# df_bug_model['解单时间'] = df_bug_model['解决时间']
return df_bug_model
df_bug_all = regularization_DevCloud(df_DevCloud_bug)
i,j = 1,1
df_bug_all.set_index('ID',inplace=True)
for index in df_bug_all.index:
if df_bug_all.loc[index, '状态'] in '开发人员定位修改':
df_bug_all.loc[index, '派单时间'] = df_bug_all.loc[index, '更新时间']
print("派单时间 = '开发人员定位修改'+'更新时间'", i)
i = i + 1
# if pd.isnull(df_bug_all.loc[index, '派单时间']):
# if df_bug_all.loc[index, '状态'] in ['审核人员审核修改','CCB审核','CMO归档','撤销','非问题关闭','测试经理组织测试','测试人员回归测试','问题单回归不通过','已关闭',]:
# df_bug_all.loc[index, '派单时间'] = df_bug_all.loc[index, '更新时间']
# print("派单时间 = '状态不精确'+'更新时间'", j)
# j = j + 1
i,j = 1,1
for index in df_bug_all.index:
if df_bug_all.loc[index, '状态'] == '审核人员审核修改':
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '更新时间']
print("解单时间 = '审核人员审核修改'+'更新时间'", i)
i = i + 1
# if pd.isnull(df_bug_all.loc[index, '解单时间']):
# if df_bug_all.loc[index, '状态'] in ['测试经理组织测试','测试人员回归测试','已关闭','非问题关闭','撤销']:
# df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '更新时间']
# print("解单时间 = '状态不精确'+'更新时间'", j)
# j = j + 1
#========================== start of bug配置 ============================
cfg_bug_status_all = ['新建','测试经理或者项目经理审核','开发人员定位修改','审核人员审核修改','CCB审核','CMO归档','撤销','非问题关闭','测试经理组织测试','测试人员回归测试','问题单回归不通过','已关闭',]
cfg_bug_status_DI = ['CCB审核','CMO归档','问题单回归不通过','测试经理或者项目经理审核','测试经理组织测试','测试人员回归测试','开发人员定位修改','审核人员审核修改','新建',]
cfg_bug_status_dev = ['开发人员定位修改','审核人员审核修改','问题单回归不通过','CCB审核','CMO归档',]
cfg_bug_status_test = ['新建','测试经理或者项目经理审核','测试经理组织测试','测试人员回归测试',]
cfg_bug_status_close = ['已关闭','非问题关闭','撤销']
cfg_bug_level = ['关键','重要','一般','提示']
# 通用
#============================ start of sqlite3 -> fill 派单时间和解单时间 ===========================
#使用sqlite3库打开Test.db数据库，cur是数据库游标
conn = sqlite3.connect("D:/Py/DB/Bug.db")
cur = conn.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cur.fetchall()
for table in tables:
print(table[0])
project_name_bug = project_name + "_bug"
project_name_req = project_name + "_req"
# project_name_group = project_name + "_group"
for i in tables:
print('for-----',i)
if project_name_bug in i:
print('yes----->>>>>',i)
#读取sqlite3到df
sql_query = f"SELECT * FROM {project_name_bug};"
df_read_db_bug = pd.read_sql_query(sql_query, conn)
df_read_db_bug.drop_duplicates(subset=['ID',], keep='last',inplace=True)
df_read_db_bug.set_index('ID',inplace=True)
if "ID" in df_bug_all.columns:
df_bug_all.set_index('ID',inplace=True)
for index in df_read_db_bug.index:
if index in df_bug_all.index:
if pd.isnull(df_bug_all.loc[index, '派单时间']):
df_bug_all.loc[index, '派单时间'] = df_read_db_bug.loc[index, '派单时间']
for index in df_read_db_bug.index:
if index in df_bug_all.index:
if pd.isnull(df_bug_all.loc[index, '解单时间']):
df_bug_all.loc[index, '解单时间'] = df_read_db_bug.loc[index, '解单时间']
# 不精确的时间填充
for index in df_bug_all.index:
if pd.isnull(df_bug_all.loc[index, '派单时间']):
if df_bug_all.loc[index, '状态'] in ['审核人员审核修改','CCB审核','CMO归档','测试经理组织测试','测试人员回归测试','问题单回归不通过','已关闭','非问题关闭','撤销',]:
df_bug_all.loc[index, '派单时间'] = df_bug_all.loc[index, '更新时间']
# 不精确的时间填充
for index in df_bug_all.index:
if pd.isnull(df_bug_all.loc[index, '解单时间']):
if df_bug_all.loc[index, '状态'] in ['CMO归档','测试经理组织测试','测试人员回归测试','问题单回归不通过','已关闭','非问题关闭','撤销']:
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '更新时间']
df_bug_all = pd.concat([df_read_db_bug, df_bug_all], axis=0, join='outer', ignore_index=False)
df_bug_all = df_bug_all.reset_index()
df_bug_all.drop_duplicates(subset=['ID',], keep='last',inplace=True)
if "index" in df_bug_all.columns:
df_bug_all = df_bug_all.drop(['index'],axis=1)
print("sqlite df_read_db_bug updated! ---> ", df_bug_all.shape)
print("sqlite df_read_db_bug updated! ---> ", df_bug_all.columns)
break
if "ID" not in df_bug_all.columns:
df_bug_all = df_bug_all.reset_index()
print("failed to read sqlite updated! ---> ")
# df_bug_all.to_sql(project_name_bug, conn, if_exists='replace', index=False)
# print("Success to sqlite ! ", project_name_bug)
# if data_type == "jira":
# df_req_all = db_update(project_name_req, df_req_all)
# df_req_all.to_sql(project_name_req, conn, if_exists='replace', index=False)
# print("Success to sqlite ! ", project_name_req)
# ========================读取vlookup数据===========================
vlookup_file = r"Form.xlsm"
project_path_file = os.path.join(project_path, vlookup_file)
# 检查vlookup人员名单
try:
f = open(project_path_file)
f.close()
except IOError:
print("project_path_file Error! File is not accessible!")
#========================== start of 读取人员名单 ========================
conn = sqlite3.connect("D:/Py/DB/Bug.db")
cur = conn.cursor()
cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cur.fetchall()
project_name_group = project_name + "_group"
df_read_db_group = pd.DataFrame()
for i in tables:
print('for-----',i)
if project_name_group in i:
print('yes----->>>>>',i)
#读取sqlite3到df
sql_query = f"SELECT * FROM {project_name_group};"
df_read_db_group = pd.read_sql_query(sql_query, conn)
break
df_vlookup_group_man = pd.read_excel(project_path_file, sheet_name='领域')
df_vlookup_group_man = df_vlookup_group_man[['领域', '领域分组', '姓名','用户名']]
if df_read_db_group is not None:
df_vlookup_group_man = pd.concat([df_read_db_group, df_vlookup_group_man], axis=0, join='outer', ignore_index=False)
df_vlookup_group_man.drop_duplicates(subset=['用户名',], keep='last',inplace=True)
df_vlookup_group_man.to_sql(project_name_group, conn, if_exists='replace', index=False)
print("Success to sqlite ! ", project_name_group)
#========================== end of 读取人员名单 ==========================
# merge
if "领域" in df_bug_all.columns:
df_bug_all.drop(['领域'], axis=1, inplace=True)
if "领域分组" in df_bug_all.columns:
df_bug_all.drop(['领域分组'], axis=1, inplace=True)
if "姓名" in df_bug_all.columns:
df_bug_all.drop(['姓名'], axis=1, inplace=True)
if "用户名" in df_bug_all.columns:
df_bug_all.drop(['用户名'], axis=1, inplace=True)
df_bug_all = pd.merge(df_bug_all, df_vlookup_group_man, left_on='开发人员', right_on='用户名', how='left', indicator=False)
df_bug_all.drop(['用户名'], axis=1, inplace=True)
df_bug_all['姓名'] = df_bug_all['姓名'].fillna('姓名null')
df_bug_all['领域分组'] = df_bug_all['领域分组'].fillna('领域分组null')
df_bug_all['领域'] = df_bug_all['领域'].fillna('领域null')
#================================= start of VB process ===================================
# 时长
df_bug_all['创建时间'] = pd.to_datetime(df_bug_all['创建时间'], format='mixed')
df_bug_all['更新时间'] = pd.to_datetime(df_bug_all['更新时间'], format='mixed')
df_bug_all['解决时间'] = pd.to_datetime(df_bug_all['解决时间'], format='mixed')
df_bug_all['到期时间'] = pd.to_datetime(df_bug_all['到期时间'], format='mixed')
df_bug_all['派单时间'] = pd.to_datetime(df_bug_all['派单时间'], format='mixed')
df_bug_all['解单时间'] = pd.to_datetime(df_bug_all['解单时间'], format='mixed')
df_bug_all['滞留时长'] = datetime.datetime.now() - df_bug_all['更新时间']
df_bug_all['滞留时长'] = (df_bug_all['滞留时长'].dt.total_seconds() / (24*60*60)).round(1)
df_bug_all['是否算DI'] = df_bug_all['状态'].apply(lambda x: "Y" if x in cfg_bug_status_DI else "N")
df_bug_all['是否Bug'] = df_bug_all['状态'].apply(lambda x: "Y" if x in cfg_bug_status_all else "N")
df_bug_all['超过6天未更新'] = (df_bug_all['滞留时长'] > 6) & (df_bug_all['是否算DI'] == "Y")
df_bug_all['超过6天未更新'].value_counts()
df_bug_all['超过6天未更新'].count()
df_bug_all[df_bug_all['超过6天未更新'] == True].shape
df_bug_all['Due超期'] = datetime.datetime.now() > df_bug_all['到期时间']
# 状态归属
def s_statusType(s):
if s in cfg_bug_status_dev:
return "dev"
if s in cfg_bug_status_test:
return "test"
if s in cfg_bug_status_close:
return "Closed"
else:
return "非开发测试发布状态"
df_bug_all['状态类型'] = df_bug_all['状态'].apply(lambda x: s_statusType(x))
df_bug_all['研发超时'] = (df_bug_all['超过6天未更新'] == True) & (df_bug_all['状态类型'] == "dev")
print('---研发超时---', df_bug_all['研发超时'].value_counts())
df_bug_all['测试超时'] = (df_bug_all['超过6天未更新'] == True) & (df_bug_all['状态类型'] == "test")
print('---测试超时---', df_bug_all['测试超时'].value_counts())
select_day(-6)
df_bug_all['一周指派'] = (df_bug_all['派单时间'] > select_today_start) & (df_bug_all['状态类型'] == "dev")
print('---一周指派---', df_bug_all['一周指派'].value_counts())
df_bug_all['一周解决'] = (df_bug_all['解单时间'] > select_today_start)
print('---一周解决---', df_bug_all['一周解决'].value_counts())
select_day(-1)
# select_day(0)
df_bug_all['昨天指派'] = (df_bug_all['派单时间'] > select_yesterday_start) & (df_bug_all['派单时间'] < select_today_start)
print('---昨天指派---', df_bug_all['昨天指派'].value_counts())
df_bug_all['昨天解决'] = (df_bug_all['解单时间'] > select_yesterday_start) & (df_bug_all['解单时间'] < select_today_start)
print('---昨天解决---', df_bug_all['昨天解决'].value_counts())
# calculate
# 补全bug流程状态
def bug_case_status(pt_df):
for i in cfg_bug_status_all:
# 判断pivot结果中是否有缺失的case类型
if (i in pt_df.columns.values) == False:
pt_df[i] = 0
# 补全bug等级
def bug_case_level(pt_df):
for i in cfg_bug_level:
# 判断pivot结果中是否有缺失的case类型
if (i in pt_df.columns.values) == False:
pt_df[i] = 0
def DI_cal(df_DI):
bug_case_level(df_DI)
df_DI['DI'] = 0
# print(df_DI)
for index in df_DI.index:
df_DI.loc[index, 'DI'] = df_DI.loc[index,cfg_bug_level[0]]*10+ df_DI.loc[index,cfg_bug_level[1]]*3 \
+ df_DI.loc[index,cfg_bug_level[2]]*1 + df_DI.loc[index,cfg_bug_level[3]]*0.1
# DI pivot process
def DI_process(df_DI):
df_DI.columns = df_DI.columns.droplevel(0)
df_DI.columns = df_DI.columns.droplevel(0)
DI_cal(df_DI)
# =========================== start of DI report =======================================
def pt_report_DI(df_to_pt):
if data_type == "jira":
df_to_pt_DI = df_to_pt[df_to_pt['状态类型'] == "dev"] # jira
elif data_type == "DevCloud":
df_to_pt_DI = df_to_pt[df_to_pt['是否算DI'] == "Y"] # DevCloud
if (len(df_to_pt) > 0) & (len(df_to_pt_DI) > 0):
# df_to_pt_DI = df_to_pt.copy()
# ================================= start of today =================================
# df_bug_percent_today_assign
# df_to_pt = df_to_pt.copy()
df_bug_percent_today_assign = df_to_pt[(df_to_pt['派单时间'] >= select_today_start)]
df_bug_percent_today_assign = df_bug_percent_today_assign[(df_bug_percent_today_assign['派单时间'] <= select_today_end)]
print('df_bug_percent_today_assign.shape - ', df_bug_percent_today_assign.shape)
# group
if len(df_bug_percent_today_assign.index) > 0:
pt_bug_percent_today_assign_group = pd.pivot_table(df_bug_percent_today_assign, index=['领域', '领域分组'], columns=['严重等级',], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_percent_today_assign_group.columns = pt_bug_percent_today_assign_group.columns.droplevel(0)
pt_bug_percent_today_assign_group.columns = pt_bug_percent_today_assign_group.columns.droplevel(0)
DI_cal(pt_bug_percent_today_assign_group)
pt_bug_percent_today_assign_group = pt_bug_percent_today_assign_group[cfg_bug_level+['All','DI']]
pt_bug_percent_today_assign_group.rename(columns={'All':'当天指派',}, inplace=True)
pt_bug_percent_today_assign_group.rename(columns={'DI':'当天指派DI',}, inplace=True)
if ('当天指派' in pt_bug_percent_today_assign_group.columns.values) == False:
pt_bug_percent_today_assign_group['当天指派'] = 0
print('pt_bug_percent_today_assign_group - no 当天指派, 增加一列值为0')
else:
pt_bug_percent_today_assign_group = pd.DataFrame()
# df_bug_percent_today_resolve
# df_to_pt = df_to_pt.copy()
df_bug_percent_today_resolve = df_to_pt[(df_to_pt['解单时间'] >= select_today_start)]
df_bug_percent_today_resolve = df_bug_percent_today_resolve[(df_bug_percent_today_resolve['解单时间'] <= select_today_end)]
print('df_bug_percent_today_resolve.shape - ', df_bug_percent_today_resolve.shape)
# group
if len(df_bug_percent_today_resolve.index) > 0:
pt_bug_percent_today_resolve_group = pd.pivot_table(df_bug_percent_today_resolve, index=['领域', '领域分组'], columns=['严重等级',], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_percent_today_resolve_group.columns = pt_bug_percent_today_resolve_group.columns.droplevel(0)
pt_bug_percent_today_resolve_group.columns = pt_bug_percent_today_resolve_group.columns.droplevel(0)
DI_cal(pt_bug_percent_today_resolve_group)
pt_bug_percent_today_resolve_group = pt_bug_percent_today_resolve_group[cfg_bug_level+['All','DI']]
pt_bug_percent_today_resolve_group.rename(columns={'All':'当天解单',}, inplace=True)
pt_bug_percent_today_resolve_group.rename(columns={'DI':'当天解单DI',}, inplace=True)
if ('当天解单' in pt_bug_percent_today_resolve_group.columns.values) == False:
pt_bug_percent_today_resolve_group['当天解单'] = 0
print('pt_bug_percent_today_resolve_group - no 当天解单, 增加一列值为0')
else:
pt_bug_percent_today_resolve_group = pd.DataFrame()
# ================================= start of yesterday =================================
# df_bug_percent_yesterday_assign
df_bug_percent_yesterday_assign = df_to_pt[(df_to_pt['派单时间'] >= select_yesterday_start)]
df_bug_percent_yesterday_assign = df_bug_percent_yesterday_assign[(df_bug_percent_yesterday_assign['派单时间'] <= select_today_start)]
# df_bug_percent_yesterday_assign = df_bug_percent_yesterday_assign[~df_bug_percent_yesterday_assign['BUG状态'].isin(['待提交','待指派',])]
print('df_bug_percent_yesterday_assign.shape - ', df_bug_percent_yesterday_assign.shape)
# df_bug_percent_yesterday_resolve
df_bug_percent_yesterday_resolve = df_to_pt[(df_to_pt['解单时间'] >= select_yesterday_start)]
df_bug_percent_yesterday_resolve = df_bug_percent_yesterday_resolve[(df_bug_percent_yesterday_resolve['解单时间'] <= select_today_start)]
print('df_bug_percent_yesterday_resolve.shape - ', df_bug_percent_yesterday_resolve.shape)
# yesterday group
# group
if len(df_bug_percent_yesterday_assign.index) > 0:
pt_bug_percent_yesterday_assign_group = pd.pivot_table(df_bug_percent_yesterday_assign, index=['领域', '领域分组'], columns=['严重等级',], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_percent_yesterday_assign_group.columns = pt_bug_percent_yesterday_assign_group.columns.droplevel(0)
pt_bug_percent_yesterday_assign_group.columns = pt_bug_percent_yesterday_assign_group.columns.droplevel(0)
DI_cal(pt_bug_percent_yesterday_assign_group)
pt_bug_percent_yesterday_assign_group = pt_bug_percent_yesterday_assign_group[cfg_bug_level+['All','DI']]
pt_bug_percent_yesterday_assign_group.rename(columns={'All':'昨天指派',}, inplace=True)
pt_bug_percent_yesterday_assign_group.rename(columns={'DI':'昨天指派DI',}, inplace=True)
print('pt_bug_percent_yesterday_assign_group - yyyyyyyyyyyyyyyyyyyyy - 昨天指派+1')
if ('昨天指派' in pt_bug_percent_yesterday_assign_group.columns.values) == False:
pt_bug_percent_yesterday_assign_group['昨天指派'] = 0
print('pt_bug_percent_yesterday_assign_group - no 昨天指派, 增加一列值为0')
else:
pt_bug_percent_yesterday_assign_group = pd.DataFrame()
# group
if len(df_bug_percent_yesterday_resolve.index) > 0:
pt_bug_percent_yesterday_resolve_group = pd.pivot_table(df_bug_percent_yesterday_resolve, index=['领域', '领域分组'], columns=['严重等级',], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_yesterday_resolve_group----------',pt_bug_percent_yesterday_resolve_group)
# print('pt_bug_percent_yesterday_resolve_group.columns', pt_bug_percent_yesterday_resolve_group.columns)
pt_bug_percent_yesterday_resolve_group.columns = pt_bug_percent_yesterday_resolve_group.columns.droplevel(0)
pt_bug_percent_yesterday_resolve_group.columns = pt_bug_percent_yesterday_resolve_group.columns.droplevel(0)
# print('pt_bug_percent_yesterday_resolve_group.columns', pt_bug_percent_yesterday_resolve_group.columns)
DI_cal(pt_bug_percent_yesterday_resolve_group)
pt_bug_percent_yesterday_resolve_group = pt_bug_percent_yesterday_resolve_group[cfg_bug_level+['All','DI']]
pt_bug_percent_yesterday_resolve_group.rename(columns={'All':'昨天解单',}, inplace=True)
pt_bug_percent_yesterday_resolve_group.rename(columns={'DI':'昨天解单DI',}, inplace=True)
if ('昨天解单' in pt_bug_percent_yesterday_resolve_group.columns.values) == False:
pt_bug_percent_yesterday_resolve_group['昨天解单'] = 0
print('pt_bug_percent_yesterday_resolve_group - no 昨天解单, 增加一列值为0')
else:
pt_bug_percent_yesterday_resolve_group = pd.DataFrame()
if len(df_to_pt) > 0:
pt_bug_percent_group_total = pd.pivot_table(df_to_pt, index=['领域', '领域分组'], columns=['严重等级'], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_percent_group_total)
# pt_bug_percent_group_total = pt_bug_percent_group_total.reindex(columns=['关键','重要','一般','提示','All','DI'])
pt_bug_percent_group_total = pt_bug_percent_group_total.reindex(columns=cfg_bug_level+['All','DI'])
pt_bug_percent_group_total = pt_bug_percent_group_total.sort_values(by='DI', ascending=False)
pt_bug_percent_group_total.rename(columns={'关键':'总关键',}, inplace=True)
pt_bug_percent_group_total.rename(columns={'重要':'总重要',}, inplace=True)
pt_bug_percent_group_total.rename(columns={'一般':'总一般',}, inplace=True)
pt_bug_percent_group_total.rename(columns={'提示':'总提示',}, inplace=True)
pt_bug_percent_group_total.rename(columns={'All':'总数',}, inplace=True)
pt_bug_percent_group_total.rename(columns={'DI':'总DI',}, inplace=True)
pt_df_bug_dev_DI_group = pd.DataFrame()
if len(df_to_pt_DI) > 0:
pt_df_bug_dev_DI_group = pd.pivot_table(df_to_pt_DI, index=['领域', '领域分组'], columns=['严重等级'], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_df_bug_dev_DI_group)
pt_df_bug_dev_DI_group.rename(columns={'All':'剩余总计',}, inplace=True)
pt_df_bug_dev_DI_group.rename(columns={'DI':'遗留DI',}, inplace=True)
if len(df_to_pt)>0:
# =============================== start of percent ====================================
# group------------------------------------------------------------
pt_bug_percent_group = pd.pivot_table(df_to_pt, index=['领域', '领域分组'], columns=['状态'], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_group.columns', pt_bug_percent_group.columns)
pt_bug_percent_group.columns = pt_bug_percent_group.columns.droplevel(0)
pt_bug_percent_group.columns = pt_bug_percent_group.columns.droplevel(0)
# print('pt_bug_percent_group.columns', pt_bug_percent_group.columns)
pt_bug_percent_group.rename(columns={'All':'全部状态',}, inplace=True)
bug_case_status(pt_bug_percent_group)
#pt_bug_percent_group = pt_bug_percent_group['cfg_bug_status]
print('pt_bug_percent_group === ',pt_bug_percent_group)
#关单率
pt_bug_percent_group['关单汇总'] = pt_bug_percent_group[cfg_bug_status_close].sum(axis=1)
pt_bug_percent_group['关单率'] = pt_bug_percent_group['关单汇总'].astype(float) / pt_bug_percent_group['全部状态'].astype(float)
pt_bug_percent_group['关单率'] = pt_bug_percent_group['关单率'].astype(float).apply('{:.4}'.format)
# sort_values
pt_bug_percent_group = pt_bug_percent_group.sort_values(by=['关单率'], ascending=[False])
pt_bug_percent_group['关单率'] = pt_bug_percent_group['关单率'].astype(float).apply('{:.0%}'.format)
# 计算“未关闭”类别
pt_bug_percent_group['开发环节单'] = pt_bug_percent_group[cfg_bug_status_dev].sum(axis=1)
pt_bug_percent_group['累计解单'] = pt_bug_percent_group[cfg_bug_status_test].sum(axis=1) + pt_bug_percent_group[cfg_bug_status_close].sum(axis=1)
# 百分比排序，先设置为4位小数
pt_bug_percent_group['解决率'] = (pt_bug_percent_group['累计解单'].astype(float) \
/ (pt_bug_percent_group['累计解单'].astype(float) + pt_bug_percent_group['开发环节单'].astype(float))).apply('{:.4}'.format)
# # sort_values
pt_bug_percent_group = pt_bug_percent_group.sort_values(by=['解决率'], ascending=[False])
pt_bug_percent_group['解决率'] = pt_bug_percent_group['解决率'].astype(float).apply('{:.0%}'.format)
if 'All' in pt_bug_percent_group.index:
pt_bug_percent_group_All = pt_bug_percent_group.loc['All','']
pt_bug_percent_group = pt_bug_percent_group.drop(index='All',axis=0, level=0)
pt_bug_percent_group = pt_bug_percent_group._append(pt_bug_percent_group_All)
# ========================= start of 合并透视表 ====================================
if len(pt_bug_percent_yesterday_assign_group) > 0:
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_bug_percent_yesterday_assign_group[['昨天指派','昨天指派DI']], \
how='left', left_on=['领域', '领域分组'], right_on=['领域', '领域分组']).fillna(0)
if len(pt_bug_percent_yesterday_resolve_group) > 0:
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_bug_percent_yesterday_resolve_group[['昨天解单','昨天解单DI']], \
how='left', left_on=['领域', '领域分组'], right_on=['领域', '领域分组']).fillna(0)
if len(pt_bug_percent_today_assign_group) > 0:
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_bug_percent_today_assign_group[['当天指派','当天指派DI']], \
how='left', left_on=['领域', '领域分组'], right_on=['领域', '领域分组']).fillna(0)
if len(pt_bug_percent_today_resolve_group) > 0:
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_bug_percent_today_resolve_group[['当天解单','当天解单DI']], \
how='left', left_on=['领域', '领域分组'], right_on=['领域', '领域分组']).fillna(0)
if len(pt_bug_percent_group_total) > 0:
for cc in ['昨天指派','昨天指派DI','昨天解单','昨天解单DI','当天指派','当天指派DI','当天解单','当天解单DI']:
print('遍历cc------------------',cc)
if cc in pt_bug_percent_group_total.columns:
print(cc)
# break
else:
pt_bug_percent_group_total[cc] = 0
print('增加cc为空列------------------',cc)
# DI变化
# if ('当天指派DI' in pt_bug_percent_group_total.columns) and ('当天解单DI' in pt_bug_percent_group_total.columns):
# pt_bug_percent_group_total['昨天DI值'] = pt_bug_percent_group_total['总DI'] - pt_bug_percent_group_total['当天指派DI'] + pt_bug_percent_group_total['当天解单DI']
if pt_df_bug_dev_DI_group is not None:
if pt_bug_percent_group_total is not None:
pt_bug_report = pd.merge(pt_df_bug_dev_DI_group, pt_bug_percent_group_total[['昨天指派','昨天指派DI','昨天解单','昨天解单DI','当天指派','当天指派DI','当天解单','当天解单DI','总数']], \
how='left', left_on=['领域', '领域分组'], right_on=['领域', '领域分组']).fillna(0)
pt_bug_report['昨天DI值'] = pt_bug_report['遗留DI'] - pt_bug_report['当天指派DI'] + pt_bug_report['当天解单DI']
pt_bug_report['DI值趋势'] = pt_bug_report['当天指派DI'] - pt_bug_report['当天解单DI']
pt_bug_report['昨天总计'] = pt_bug_report['剩余总计'] - pt_bug_report['当天指派'] + pt_bug_report['当天解单']
pt_bug_report['余数趋势'] = pt_bug_report['当天指派'] - pt_bug_report['当天解单']
if len(pt_bug_percent_group) > 0:
pt_bug_report = pd.merge(pt_bug_report, pt_bug_percent_group[['解决率','关单率']], \
how='left', left_on=['领域', '领域分组'], right_on=['领域', '领域分组']).fillna(0)
pt_bug_report['DI值占比'] = pt_bug_report['遗留DI']/pt_bug_report.loc['All','遗留DI'][0]
pt_bug_report['DI值占比'] = pt_bug_report['DI值占比'].astype(float).apply('{:.0%}'.format)
pt_bug_report['余数占比'] = pt_bug_report['剩余总计']/pt_bug_report.loc['All','剩余总计'][0]
pt_bug_report['余数占比'] = pt_bug_report['余数占比'].astype(float).apply('{:.0%}'.format)
# pt_bug_report_new = pt_bug_report[['关键','重要','一般','提示','剩余总计','遗留DI','昨天DI值','DI值趋势','DI值占比','昨天总计','余数趋势','余数占比','解决率','昨天指派','昨天解单','总数','关单率']]
# pt_bug_report_new = pt_bug_report[cfg_bug_level+['剩余总计','遗留DI','昨天DI值','DI值趋势','DI值占比','昨天总计','余数趋势','余数占比','解决率','昨天指派','昨天解单','总数','关单率']]
pt_bug_report_new = pt_bug_report[cfg_bug_level+['剩余总计','遗留DI','昨天DI值','DI值趋势','DI值占比','昨天总计','余数趋势','余数占比','解决率','昨天指派','昨天解单','总数',]]
pt_bug_report_new = pt_bug_report_new.sort_values(by=['领域', '遗留DI'], ascending=[False, False])
return pt_bug_report_new
# 将汇总All行切换到最后一行
def All_to_behind(df_All_to_behind):
if 'All' in df_All_to_behind.index:
index_All = df_All_to_behind.loc['All','']
df_All_to_behind = df_All_to_behind.drop(index='All',axis=0, level=0)
df_All_to_behind = df_All_to_behind._append(index_All)
return df_All_to_behind
# =============================bug透视表 pivot ==========================================
def pt_DI_raname(df_to_pt_DI):
pt_df_bug_dev_DI_group = pd.DataFrame()
if len(df_to_pt_DI) > 0:
pt_df_bug_dev_DI_group = pd.pivot_table(df_to_pt_DI, index=['领域', '领域分组'], columns=['严重等级'], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_df_bug_dev_DI_group)
pt_df_bug_dev_DI_group.rename(columns={'All':'剩余总计',}, inplace=True)
pt_df_bug_dev_DI_group.rename(columns={'DI':'遗留DI',}, inplace=True)
def pt_All_rename(df, name_tuple):
if len(df) > 0:
pt_df = pt_report_DI(df)
pt_df = All_to_behind(pt_df)
if len(pt_df) > 0:
index_All = pt_df.loc['All','']
index_All.name = name_tuple
pt_df = pt_df.drop(index='All',axis=0, level=0)
pt_df = pt_df._append(index_All)
return pt_df
# jira
if data_type == "jira":
df_bug_dev_lcs = df_bug_all[(df_bug_all['领域'] == "龙旗软件")]
df_bug_dev_lcs_not = df_bug_all[~(df_bug_all['领域'] == "龙旗软件")]
pt_bug_all = pt_report_DI(df_bug_all)
if pt_bug_all is not None:
index_All_pt_bug_all = pt_bug_all.loc['All','']
index_All_pt_bug_all.name = ('所有问题单总计', '')
pt_rename_bug_dev_lcs = pt_All_rename(df_bug_dev_lcs, ('LQ软件总计', ''))
pt_rename_bug_dev_lcs_not = pt_All_rename(df_bug_dev_lcs_not, ('非LQ软件总计', ''))
pt_bug_dev_all = pd.DataFrame()
if pt_rename_bug_dev_lcs is not None:
if pt_rename_bug_dev_lcs_not is not None:
pt_bug_dev_all = pd.concat([pt_rename_bug_dev_lcs, pt_rename_bug_dev_lcs_not],axis=0)
pt_bug_dev_all = pt_bug_dev_all._append(index_All_pt_bug_all)
else:
pt_bug_dev_all = pt_rename_bug_dev_lcs
pt_bug_dev_test_hw_all = pt_bug_dev_all.copy()
for c in ['昨天总计','余数趋势','目标值个数','余数占比',]:
if c in list(pt_bug_dev_test_hw_all.columns):
pt_bug_dev_test_hw_all.drop(c, axis=1, inplace=True)
print('规整columns------pt_bug_dev_test_hw_all',pt_bug_dev_test_hw_all.columns)
# DevCloud
if data_type == "DevCloud":
df_bug_dev = df_bug_all[(df_bug_all['状态类型'] == "dev")]
df_bug_dev_lcs = df_bug_all[(df_bug_all['领域'] == "LQ软件")]
df_bug_dev_hys = df_bug_all[(df_bug_all['领域'] == "HY软件")]
df_bug_dev_lch = df_bug_all[(df_bug_all['领域'] == "LQ硬件")]
df_bug_dev_hyh = df_bug_all[(df_bug_all['领域'] == "HY硬件")]
df_bug_dev_sw = df_bug_all[(df_bug_all['领域'] == "LQ软件") | (df_bug_all['领域'] == "HY软件")]
df_bug_dev_hw = df_bug_all[(df_bug_all['领域'] == "LQ硬件") | (df_bug_all['领域'] == "HY硬件")]
df_bug_test = df_bug_all[(df_bug_all['状态类型'] == "test")]
df_bug_test_lct = df_bug_all[(df_bug_all['领域'] == "LQ测试")]
df_bug_test_hyt = df_bug_all[(df_bug_all['领域'] == "HY测试")]
df_bug_test_hw = df_bug_all[(df_bug_all['领域'] == "硬件测试")]
df_bug_sw_dev_test = pd.concat([df_bug_dev_sw, df_bug_test_lct, df_bug_test_hyt], axis=0)
pt_bug_all = pt_report_DI(df_bug_all)
if pt_bug_all is not None:
index_All_pt_bug_all = pt_bug_all.loc['All','']
index_All_pt_bug_all.name = ('DevCloud所有问题单总计', '')
# pt_bug_dev_DI = pt_report_DI(df_bug_dev_DI)
# if pt_bug_dev_DI is not None:
# index_All_pt_bug_dev_DI = pt_bug_dev_DI.loc['All','']
# index_All_pt_bug_dev_DI.name = ('DevCloud遗留问题单总计', '')
pt_bug_dev_sw = pt_report_DI(df_bug_dev_sw)
if pt_bug_dev_sw is not None:
index_All_pt_bug_dev_sw = pt_bug_dev_sw.loc['All','']
index_All_pt_bug_dev_sw.name = ('DevCloud软件总计', '')
pt_bug_dev_hw = pt_report_DI(df_bug_dev_hw)
if pt_bug_dev_hw is not None:
index_All_pt_bug_dev_hw = pt_bug_dev_hw.loc['All','']
index_All_pt_bug_dev_hw.name = ('DevCloud非软件总计', '')
if len(df_bug_test)>0:
pt_bug_test = pt_report_DI(df_bug_test)
if pt_bug_test is not None:
index_All_pt_bug_test = pt_bug_test.loc['All','']
index_All_pt_bug_test.name = ('DevCloud测试总计', '')
if len(df_bug_sw_dev_test)>0:
pt_bug_sw_dev_test = pt_report_DI(df_bug_sw_dev_test)
if pt_bug_sw_dev_test is not None:
index_All_pt_bug_sw_dev_test = pt_bug_sw_dev_test.loc['All','']
index_All_pt_bug_sw_dev_test.name = ('DevCloud软件开发测试总计', '')
pt_rename_bug_dev_lcs = pt_All_rename(df_bug_dev_lcs, ('LQ软件总计', ''))
pt_rename_bug_dev_hys = pt_All_rename(df_bug_dev_hys, ('HY软件总计', ''))
pt_rename_bug_test_lct = pt_All_rename(df_bug_test_lct, ('LQ测试总计', ''))
pt_rename_bug_test_hyt = pt_All_rename(df_bug_test_hyt, ('HY测试总计', ''))
pt_rename_bug_dev_hw = pt_All_rename(df_bug_dev_hw, ('非软件总计', ''))
pt_bug_dev_all = pd.DataFrame()
if pt_rename_bug_dev_lcs is not None:
if pt_rename_bug_dev_hys is not None:
pt_bug_dev_all = pd.concat([pt_rename_bug_dev_lcs,pt_rename_bug_dev_hys],axis=0)
pt_bug_dev_all = pt_bug_dev_all._append(index_All_pt_bug_dev_sw)
else:
pt_bug_dev_all = pt_rename_bug_dev_lcs
pt_bug_test_all = pd.DataFrame()
if pt_rename_bug_test_lct is not None:
if pt_rename_bug_test_hyt is not None:
pt_bug_test_all = pd.concat([pt_rename_bug_test_lct,pt_rename_bug_test_hyt],axis=0)
pt_bug_test_all = pt_bug_test_all._append(index_All_pt_bug_test)
else:
pt_bug_test_all = pt_rename_bug_test_lct
pt_bug_dev_test_all = pd.DataFrame()
if pt_bug_dev_all is not None:
if pt_bug_test_all is not None:
pt_bug_dev_test_all = pd.concat([pt_bug_dev_all,pt_bug_test_all],axis=0)
pt_bug_dev_test_all = pt_bug_dev_test_all._append(index_All_pt_bug_sw_dev_test)
else:
pt_bug_dev_test_all = pt_bug_dev_all
pt_bug_dev_test_hw_all = pd.DataFrame()
if pt_rename_bug_dev_hw is not None:
pt_bug_dev_test_hw_all = pd.concat([pt_bug_dev_test_all,pt_rename_bug_dev_hw],axis=0)
pt_bug_dev_test_hw_all = pt_bug_dev_test_hw_all._append(index_All_pt_bug_all)
# pt_bug_dev_test_hw_all = pt_bug_dev_test_hw_all._append(index_All_pt_bug_dev_DI)
else:
pt_bug_dev_test_hw_all = pt_bug_dev_test_all
# pt_bug_dev_test_hw_all = pt_bug_dev_test_hw_all._append(index_All_pt_bug_all)
for c in ['昨天总计','余数趋势','目标值个数','余数占比',]:
if c in list(pt_bug_dev_test_hw_all.columns):
pt_bug_dev_test_hw_all.drop(c, axis=1, inplace=True)
# =============================req透视表 pivot ==========================================
# ============================ start of daily line bug ===================================
# df_line = df_bug_all.copy(deep=True)
# df_line = df_bug_all[~df_bug_all['状态'].isin(cfg_bug_status_dev)]
df_line = df_bug_all[~df_bug_all['状态'].isin(['新建','测试经理或者项目经理审核'])]
# 每日处理单
df_line['派单时间'] = pd.to_datetime(df_line['派单时间']).dt.normalize()
df_new = df_line['派单时间'].groupby(df_line['派单时间']).count()
df_new.name = '当天指派'
df_cumsum = df_new.cumsum()
df_cumsum.name = '累计指派'
df_daily_bug = pd.merge(df_new, df_cumsum, how='outer', left_index=True, right_index=True)
df_line['解单时间'] = pd.to_datetime(df_line['解单时间']).dt.normalize()
df_debug = df_line['解单时间'].groupby(df_line['解单时间']).count()
df_debug.name = '当天解单'
df_debug_cumsum = df_debug.cumsum()
df_debug_cumsum.name = '累计解单'
df_daily_debug = pd.merge(df_debug, df_debug_cumsum, how='outer', left_index=True, right_index=True)
df_daily_all = pd.merge(df_daily_bug, df_daily_debug, how='outer', left_index=True, right_index=True).ffill()
df_daily_bug_debug = pd.merge(df_new, df_debug, how='outer', left_index=True, right_index=True).fillna(0)
df_daily_bug_debug_cumsum = pd.merge(df_cumsum, df_debug_cumsum, how='outer', left_index=True, right_index=True).ffill()
df_daily_bug_all = pd.merge(df_daily_bug_debug, df_daily_bug_debug_cumsum, how='outer', left_index=True, right_index=True)
df_di_bug = df_line[['派单时间','严重等级','ID']].groupby(['派单时间','严重等级']).count()
df_di_bug = df_di_bug.unstack().fillna(0)
df_di_bug.columns = df_di_bug.columns.droplevel(0)
DI_cal(df_di_bug)
df_di_bug_cumsum = df_di_bug.cumsum()
df_di_debug = df_line[['解单时间','严重等级','ID']].groupby(['解单时间','严重等级']).count()
df_di_debug = df_di_debug.unstack().fillna(0)
df_di_debug.columns = df_di_debug.columns.droplevel(0)
DI_cal(df_di_debug)
df_di_debug_cumsum = df_di_debug.cumsum()
df_dd = df_di_bug_cumsum + df_di_debug_cumsum
df_dd.loc[:,:] = 0
df_di_bug_cumsum = (df_dd + df_di_bug_cumsum).ffill()
df_di_debug_cumsum = (df_dd + df_di_debug_cumsum).ffill()
df_di_debug_cumsum = df_di_debug_cumsum.fillna(0)
di = (df_di_bug_cumsum - df_di_debug_cumsum).ffill()
df_daily_all_DI = pd.merge(df_daily_bug_all, di, how='outer', left_index=True, right_index=True).fillna(0)
df_daily_all_DI.index = df_daily_all_DI.index.strftime('%Y-%m-%d')
df_daily_all_DI.index.name = '日期'
df_daily_all_DI['累计剩余'] = df_daily_all_DI['累计指派'] - df_daily_all_DI['累计解单']
# df_daily_all_DI = df_daily_all_DI.applymap(lambda x: '%.1f' % x)
# df_daily_all_DI['DI'].apply(lambda x: '%.1f' % x)
df_daily_all_DI = df_daily_all_DI.applymap(lambda x: round(x,1))
df_daily_all_DI['累计解决率'] = (df_daily_all_DI['累计解单'] / df_daily_all_DI['累计指派']).apply('{:.0%}'.format)
df_daily_all_DI = df_daily_all_DI.reindex(columns=['当天指派','当天解单','累计指派','累计解单','累计剩余','累计解决率']+cfg_bug_level+['DI'])
#========================== start write ==========================
writer = pd.ExcelWriter(write_path_file_name, engine='openpyxl')
if df_jira is not None:
df_jira.to_excel(writer, sheet_name='原始单', index=True)
if df_bug_all is not None:
df_bug_all.to_excel(writer, sheet_name='问题单', index=False)
# df_null = df_bug_all[df_bug_all['姓名'] == '姓名null']
# if len(df_null) > 0:
# df_null.to_excel(writer, sheet_name='问题单_姓名空白', index=False)
# if data_type == "DevCloud":
# if len(pt_bug_dev_test_hw_all) > 0:
if pt_bug_dev_test_hw_all is not None:
pt_bug_dev_test_hw_all.to_excel(writer, sheet_name='Bug_report_DI', index=True)
# if len(pt_bug_all) > 0:
if pt_bug_all is not None:
pt_bug_all.to_excel(writer, sheet_name='pt_bug_all', index=True)
# if len(pt_req_dev_all) > 0:
# pt_req_dev_all.to_excel(writer, sheet_name='Req_report', index=True)
df_null = df_bug_all[df_bug_all['姓名'] == '姓名null']
# if len(df_null) > 0:
if df_null is not None:
df_null.to_excel(writer, sheet_name='问题单_姓名空白', index=False)
# if len(df_daily_all_DI) > 0:
if df_daily_all_DI is not None:
df_daily_all_DI.to_excel(writer, sheet_name='DI_line', index=True)
writer.close()
print("write success! ---> ",write_path_file_name)
#保存数据库
df_bug_all.to_sql(project_name_bug, conn, if_exists='replace', index=False)
print("Success to sqlite ! ", project_name_bug)
#提交对数据库的操作
conn.commit()
#最后别忘了关数据库，如果忘了关闭数据库，会出现数据库文件一直在被锁定状态，无法修改删除等。
conn.close()
# return df_bug_all
# ===================================start of plot===================================
from matplotlib.font_manager import FontProperties
import matplotlib.pyplot as plt
my_font = FontProperties(fname=r"c:\windows\fonts\simsun.ttc", size=14)
# my_font = FontProperties(fname=r"c:\windows\fonts\simsunb.ttf", size=14)
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
print('plt.style.available--->>> ', plt.style.available)
# plt.style.use('dark_background')
# plt.style.use('fivethirtyeight')
# plt.style.use('ggplot')
# plt.style.use('seaborn')
plt.style.use('classic')
# plt.style.use('grayscale')
fig1 = plt.figure(figsize=(12,18), dpi = 100, facecolor = 'lightgray', edgecolor='red', frameon=True)
##################################################################################
plt.subplot(3,1,1) # 分成3行1列，起始点为1
plt.xlabel(u'日期', fontproperties=my_font)
plt.ylabel(u'BUG数量', fontproperties=my_font)
# df_daily_all_DI.drop([len(df_daily_all_DI)-1],inplace=True)
plt.plot(df_daily_all_DI.index, df_daily_all_DI['累计指派'], color='r', linewidth=1.5, linestyle='-', label='Assigned')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['累计解单'], color='b', label='Resolved')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['累计剩余'], color='g', label='remain')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['DI'], color='gold', label='DI')
x1 = range(len(df_daily_all_DI.index))
plt.xticks(x1, df_daily_all_DI.index, rotation='vertical', fontproperties=my_font)
# plt.yticks(np.arange(0,5000,1000),)
y_DI = df_daily_all_DI['DI']
# # for a,b in zip(x1, y_DI): #柱子上的数字显示
# # plt.text(a,b+0.5,'%.0f'%b,ha='center',va='bottom',fontsize=9);
# 只显示最后一个值
a = len(df_daily_all_DI.index)
b = y_DI[-1]
plt.text(a+0.1,b-0.1,'%.0f'%b,ha='left',va='center',fontsize=12)
y_add_sum = df_daily_all_DI['累计指派']
y_fix_sum = df_daily_all_DI['累计解单']
y_remain_sum = df_daily_all_DI['累计剩余']
# for a,c in zip(x1, y_add_sum,): #柱子上的数字显示
# plt.text(a,c+0.5,'%.0f'%c,ha='center',va='bottom',fontsize=9);
# for a,d in zip(x1, y_fix_sum): #柱子上的数字显示
# plt.text(a,d+0.5,'%.0f'%d,ha='center',va='bottom',fontsize=9);
c = y_add_sum[-1]
d = y_fix_sum[-1]
e = y_remain_sum[-1]
plt.text(a+0.1,c-0.1,'%.0f'%c,ha='left',va='center',fontsize=12)
plt.text(a+0.1,d-0.1,'%.0f'%d,ha='left',va='center',fontsize=12)
plt.text(a+0.1,e-0.1,'%.0f'%e,ha='left',va='center',fontsize=12)
plt.title(u'BUG累计趋势', loc='center', fontsize=20, fontproperties=my_font)
# plt.legend(labels=['累计指派','累计解单','遗留DI'], loc='upper left', )
plt.legend(loc='upper left', )
#网格线
plt.grid()
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
plt.subplot(3,1,2) # 分成3行1列，起始点为2
plt.plot(df_daily_all_DI.index, df_daily_all_DI['当天指派'], color='r', linewidth=1.5, linestyle='-', label='Bug')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['当天解单'], color='b', label='Debug')
plt.xticks(x1, df_daily_all_DI.index, rotation='vertical', fontproperties=my_font)
y_add = df_daily_all_DI['当天指派']
y_fix = df_daily_all_DI['当天解单']
b = y_add[-1]
c = y_fix[-1]
plt.text(a,b-0.1,'%.0f'%b,ha='left',va='center',fontsize=12)
plt.text(a,c-0.1,'%.0f'%c,ha='left',va='center',fontsize=12)
# for a,b in zip(x1, y_add,): #柱子上的数字显示
# plt.text(a,b+0.5,'%.0f'%b,ha='center',va='bottom',fontsize=9);
# for a,c in zip(x1, y_fix): #柱子上的数字显示
# plt.text(a,c+0.5,'%.0f'%c,ha='center',va='bottom',fontsize=9);
# plt.yticks(np.arange(0,500,100),)
plt.title(u'BUG每日增减', loc='center', fontsize=20, fontproperties=my_font)
# plt.legend(labels=['当天指派','当天解单','DI'], loc='upper left')
plt.legend(loc='upper left',)
#网格线
plt.grid()
# ++++++++++++++++++++ bar +++++++++++++++++++++
df_bug_dev_lcs_DI = df_bug_all[(df_bug_all['领域'] == "龙旗软件") & (df_bug_all['状态类型'] == "dev")]
# df_bug_dev_lcs_DI = df_bug_all.copy()
if len(df_bug_dev_lcs_DI)>0:
pt_bug_dev_lcs_DI = pd.pivot_table(df_bug_dev_lcs_DI, index=['领域分组'], columns=['严重等级',], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_dev_lcs_DI.columns = pt_bug_dev_lcs_DI.columns.droplevel(0)
pt_bug_dev_lcs_DI.columns = pt_bug_dev_lcs_DI.columns.droplevel(0)
pt_bug_dev_lcs_DI = pt_bug_dev_lcs_DI.sort_values(by=['All'], ascending=[False])
# 将汇总All行切换到最后一行
if 'All' in pt_bug_dev_lcs_DI.index:
pt_bug_dev_lcs_DI_index_All = pt_bug_dev_lcs_DI.loc['All']
pt_bug_dev_lcs_DI = pt_bug_dev_lcs_DI.drop(index='All')
pt_bug_dev_lcs_DI = pt_bug_dev_lcs_DI._append(pt_bug_dev_lcs_DI_index_All)
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
plt.subplot(3,1,3) # 分成3行1列，起始点为3
#x轴数据
x= pt_bug_dev_lcs_DI.index
#y轴数据
y= pt_bug_dev_lcs_DI['All']
bar_width = 0.2
for i in range(len(x)):
plt.bar(x,y,bar_width,edgecolor='black',align="center",alpha=0.2,bottom=0,color="blue",hatch="/",label="遗留Bug")
plt.text(x[i],y[i]+5,'%.0f' % y[i],ha='center',va='center',fontsize=12,)
x3 = range(len(x))
plt.xticks(x3, x, rotation='vertical', fontproperties=my_font)
plt.tick_params(labelsize=12)
plt.xlabel(u'领域分组', fontproperties=my_font)
plt.ylabel(u'BUG单剩余数量', fontproperties=my_font)
plt.title(u'Bug领域分布', loc='center', fontsize=20, fontproperties=my_font)
# 设置坐标轴的范围
plt.xlim([-bar_width / 2, len(x) - (1 - bar_width / 2)])
plt.ylim(0, max(y) * 1.2) # 为y轴设置合理的范围，留出一些空间
# 要修改直方图的背景颜色，可以使用plt.gca().set_facecolor()方法。
plt.gca().set_facecolor("lightgrey")
# # 添加网格线
# `linewidth`参数用于控制网格线的宽度。
plt.grid()
# #显示标签
# plt.legend(loc='upper left', fontproperties=my_font)
#完整显示
plt.tight_layout()
# 保存绘图
plt.savefig(write_path_savefig)
#绘图
# plt.show()
# tk.messagebox.showinfo(title='提示',message='End of plt')
# event.set()
print("plt success ======>>>>>> !")
2537
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
