#-*- coding:utf-8-*-
import pandas as pd
import os
import numpy as np
import sqlite3
import datetime
import matplotlib.pyplot as plt
#----------- Setting--------------------
# project_name = "O17"
project_name = "C3U_V"
if project_name == "O17":
data_type = "jira"
if project_name == "C3U_V":
data_type = "Jira-phone"
# project_name = "M483_DevCloud"
# data_type = "DevCloud"
# project_name = "M483_chandao"
# data_type = "chandao"
project_path = "D:\Py"
project_path = project_path + "\\" + project_name
path_Input = project_path + "\Today_Input"
path_Output = project_path + "\Today_Output"
#============================= Start of def ==========================================
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--------------------
#-------------------------------- Start of def ---------------------------------
# 状态归属
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 "非开发测试发布状态"
# 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):
if isinstance(df_DI.columns, pd.MultiIndex):
print("列是多级索引")
df_DI.columns = df_DI.columns.droplevel(0)
if isinstance(df_DI.columns, pd.MultiIndex):
print("列是多级索引")
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 in ['jira','Jira-phone']:
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[list(filter(lambda x: x in pt_bug_percent_group.columns, 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('{:.1%}'.format)
# 计算“未关闭”类别
pt_bug_percent_group['开发环节单'] = pt_bug_percent_group[cfg_bug_status_dev].sum(axis=1)
pt_bug_percent_group['累计解单'] = pt_bug_percent_group[list(filter(lambda x: x in pt_bug_percent_group.columns, cfg_bug_status_test))].sum(axis=1) + pt_bug_percent_group[list(filter(lambda x: x in pt_bug_percent_group.columns, 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('{:.1%}'.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)
# 超期6天，Due Date超期，未解时长
if len(df_to_pt.index) > 0:
pt_over_6 = pd.pivot_table(df_to_pt, index=['领域', '领域分组'], columns=['超6天未更新'], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_over_6.columns = pt_over_6.columns.droplevel(0)
pt_over_6.columns = pt_over_6.columns.droplevel(0)
pt_over_6.rename(columns={True:'超6天未更新'}, inplace=True)
pt_over_2 = pd.pivot_table(df_to_pt, index=['领域', '领域分组'], columns=['超2天未更新'], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_over_2.columns = pt_over_2.columns.droplevel(0)
pt_over_2.columns = pt_over_2.columns.droplevel(0)
pt_over_2.rename(columns={True:'超2天未更新'}, inplace=True)
pt_over_Due = pd.pivot_table(df_to_pt, index=['领域', '领域分组'], columns=['Due超期'], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_over_Due.columns = pt_over_Due.columns.droplevel(0)
pt_over_Due.columns = pt_over_Due.columns.droplevel(0)
pt_over_Due.rename(columns={True:'Due超期'}, inplace=True)
# ========================= 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 '超6天未更新' in pt_over_6.columns:
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_over_6['超6天未更新'], \
how='left', left_on=['领域', '领域分组'], right_on=['领域', '领域分组']).fillna(0)
if '超2天未更新' in pt_over_2.columns:
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_over_2['超2天未更新'], \
how='left', left_on=['领域', '领域分组'], right_on=['领域', '领域分组']).fillna(0)
if 'Due超期' in pt_over_Due.columns:
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_over_Due['Due超期'], \
how='left', left_on=['领域', '领域分组'], right_on=['领域', '领域分组']).fillna(0)
if len(pt_bug_percent_group_total) > 0:
for cc in ['昨天派单','昨天派单DI','昨天解单','昨天解单DI','今天派单','今天派单DI','今天解单','今天解单DI','超6天未更新','超2天未更新','Due超期']:
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','总数','超6天未更新','超2天未更新','Due超期']], \
how='right', 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['目标个数'] = 0
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('{:.1%}'.format)
pt_bug_report['余数占比'] = pt_bug_report['剩余总数']/pt_bug_report.loc['All','剩余总数'][0]
pt_bug_report['余数占比'] = pt_bug_report['余数占比'].astype(float).apply('{:.1%}'.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值占比','昨天总计','目标个数','余数趋势','余数占比','解决率','昨天派单','昨天解单','总数','今天派单','今天解单','超6天未更新','超2天未更新','Due超期']]
pt_bug_report_new = pt_bug_report_new.sort_values(by=['领域', '剩余总数'], ascending=[False, False])
# 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:
if pt_df is not None:
pt_df = All_to_behind(pt_df)
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
#-------------------------------- End of def ---------------------------------
# def df_normalization(data_type, project_name, project_path="D:\Py"):
# jira
if data_type == "jira":
# =====================读取jira数据========================
read_file_name = [ x for x in os.listdir(path_Input) if x.endswith(".csv")]
path_Input_file = os.path.join(path_Input, read_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, header=0, 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)
# 筛选longcheer
df_jira_lq = df_jira[df_jira['Assignee'].astype(str).str.startswith('p-') == True]
# df_jira_lq = df_jira.copy()
print('P开头用户名------------df_jira_lq.shape', df_jira_lq.shape)
# df_jira_lq_bug = df_jira_lq[df_jira_lq['Issue Type']== "Bug"]
# df_jira_lq_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','key','Component/s','Labels', 'Due Date','状态','严重等级','提交人员','开发人员','测试人员','创建时间','解决时间','更新时间','到期时间','派单时间','解单时间'])
df_bug_model['Issue Type'] = df_jira_bug['Issue Type']
df_bug_model['ID'] = df_jira_bug['Issue key']
df_bug_model['key'] = df_jira_bug['Issue key']
df_bug_model['Component/s'] = df_jira_bug['Component/s']
df_bug_model['状态'] = df_jira_bug['Status']
df_bug_model['严重等级'] = df_jira_bug['Priority']
df_bug_model['Summary'] = df_jira_bug['Summary']
df_bug_model['Labels'] = df_jira_bug['Labels']
df_bug_model['Due Date'] = df_jira_bug['Due Date']
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_jira_lq_all = regularization_jira(df_jira_lq)
#========================== 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']
# Jira-phone
# Jira-phone
if data_type == "Jira-phone":
# =====================读取jira数据========================
read_file_name = [ x for x in os.listdir(path_Input) if x.endswith(".csv")]
path_Input_file = os.path.join(path_Input, read_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, header=0, 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)
# 筛选longcheer
df_jira_lq = df_jira[df_jira['Assignee'].astype(str).str.startswith('p-') == True]
# df_jira_lq = df_jira.copy()
print('P开头用户名------------df_jira_lq.shape', df_jira_lq.shape)
# df_jira_lq_bug = df_jira_lq[df_jira_lq['Issue Type']== "Bug"]
# df_jira_lq_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','key','Component/s','Labels', 'Due Date','状态','严重等级','提交人员','开发人员','测试人员','创建时间','解决时间','更新时间','到期时间','派单时间','解单时间'])
df_bug_model['Issue Type'] = df_jira_bug['Issue Type']
df_bug_model['ID'] = df_jira_bug['Issue key']
df_bug_model['key'] = df_jira_bug['Issue key']
df_bug_model['Component/s'] = df_jira_bug['Component/s']
df_bug_model['状态'] = df_jira_bug['Status']
df_bug_model['严重等级'] = df_jira_bug['Priority']
df_bug_model['Summary'] = df_jira_bug['Summary']
df_bug_model['Labels'] = df_jira_bug['Labels']
df_bug_model['Due Date'] = df_jira_bug['Due Date']
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_jira_lq_all = regularization_jira(df_jira_lq)
#========================== start of bug配置 ============================
cfg_bug_status_all = ['Open','In Progress','Resolved','Verified','Integrated','More Info','Closed','Reopened','Qualcomm/MTK/Google','Scheduled',]
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',] + ['Testing']
cfg_bug_status_close = ['Closed'] + ['Integrating','Integrated',"Won't Do"]
cfg_bug_level = ['Blocker', 'Critical', 'Major', 'Minor']
wontdo = r"Won't Do"
cfg_req_status_all = ['Open','Testing','Integrating','Integrated'] +[wontdo]
# cfg_req_status_all = ['Open','Testing','Integrating','Integrated',"Won't Do"]
cfg_req_status_dev = ['Open',]
cfg_req_status_test = ['Testing',]
cfg_req_status_close = ['Integrating','Integrated',"Won't Do"]
cfg_req_level = ['P0', 'P1', 'P2', 'P3']
# DevCloud
if data_type == "DevCloud":
# =====================读取DevCloud数据========================
read_file_name = [ x for x in os.listdir(path_Input) if x.endswith(".xlsx")]
path_Input_file = os.path.join(path_Input, read_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_DevCloud_lq_all = regularization_DevCloud(df_DevCloud_bug)
i,j = 1,1
df_DevCloud_lq_all.set_index('ID',inplace=True)
for index in df_DevCloud_lq_all.index:
if df_DevCloud_lq_all.loc[index, '状态'] in '开发人员定位修改':
df_DevCloud_lq_all.loc[index, '派单时间'] = df_DevCloud_lq_all.loc[index, '更新时间']
print("派单时间 = '开发人员定位修改'+'更新时间'", i)
i = i + 1
# if pd.isnull(df_DevCloud_lq_all.loc[index, '派单时间']):
# if df_DevCloud_lq_all.loc[index, '状态'] in ['审核人员审核修改','CCB审核','CMO归档','撤销','非问题关闭','测试经理组织测试','测试人员回归测试','问题单回归不通过','已关闭',]:
# df_DevCloud_lq_all.loc[index, '派单时间'] = df_DevCloud_lq_all.loc[index, '更新时间']
# print("派单时间 = '状态不精确'+'更新时间'", j)
# j = j + 1
i,j = 1,1
for index in df_DevCloud_lq_all.index:
if df_DevCloud_lq_all.loc[index, '状态'] == '审核人员审核修改':
df_DevCloud_lq_all.loc[index, '解单时间'] = df_DevCloud_lq_all.loc[index, '更新时间']
print("解单时间 = '审核人员审核修改'+'更新时间'", i)
i = i + 1
# if pd.isnull(df_DevCloud_lq_all.loc[index, '解单时间']):
# if df_DevCloud_lq_all.loc[index, '状态'] in ['测试经理组织测试','测试人员回归测试','已关闭','非问题关闭','撤销']:
# df_DevCloud_lq_all.loc[index, '解单时间'] = df_DevCloud_lq_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 writer --------------------------
now_time = datetime.datetime.strftime(datetime.datetime.now(), "%Y%m%d-%H%M%S")
print("now_time is : ", now_time)
write_file_name = now_time + "_" + read_file_name[0] + ".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)
# --------------------------- End of writer --------------------------
# 通用
#============================ 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_jira_lq_all.columns:
df_jira_lq_all.set_index('ID',inplace=True)
for index in df_read_db_bug.index:
if index in df_jira_lq_all.index:
if pd.isnull(df_jira_lq_all.loc[index, '派单时间']):
df_jira_lq_all.loc[index, '派单时间'] = df_read_db_bug.loc[index, '派单时间']
for index in df_read_db_bug.index:
if index in df_jira_lq_all.index:
if pd.isnull(df_jira_lq_all.loc[index, '解单时间']):
df_jira_lq_all.loc[index, '解单时间'] = df_read_db_bug.loc[index, '解单时间']
# Jira-phone不精确的时间填充
if data_type == 'Jira-phone':
for index in df_jira_lq_all.index:
if pd.isnull(df_jira_lq_all.loc[index, '解单时间']):
if df_jira_lq_all.loc[index, '状态'] in ['Testing','Integrating','Integrated']:
df_jira_lq_all.loc[index, '解单时间'] = df_jira_lq_all.loc[index, '更新时间']
# DevCloud不精确的时间填充
if data_type == 'DevCloud':
# DevCloud不精确的时间填充
for index in df_jira_lq_all.index:
if pd.isnull(df_jira_lq_all.loc[index, '派单时间']):
if df_jira_lq_all.loc[index, '状态'] in ['审核人员审核修改','CCB审核','CMO归档','测试经理组织测试','测试人员回归测试','问题单回归不通过','已关闭','非问题关闭','撤销',]:
df_jira_lq_all.loc[index, '派单时间'] = df_jira_lq_all.loc[index, '更新时间']
# DevCloud不精确的时间填充
for index in df_jira_lq_all.index:
if pd.isnull(df_jira_lq_all.loc[index, '解单时间']):
if df_jira_lq_all.loc[index, '状态'] in ['CMO归档','测试经理组织测试','测试人员回归测试','问题单回归不通过','已关闭','非问题关闭','撤销']:
df_jira_lq_all.loc[index, '解单时间'] = df_jira_lq_all.loc[index, '更新时间']
if data_type == "DevCloud":
df_jira_lq_all = pd.concat([df_read_db_bug, df_jira_lq_all], axis=0, join='outer', ignore_index=False)
df_jira_lq_all = df_jira_lq_all.reset_index()
df_jira_lq_all.drop_duplicates(subset=['ID',], keep='last',inplace=True)
if "index" in df_jira_lq_all.columns:
df_jira_lq_all = df_jira_lq_all.drop(['index'],axis=1)
print("sqlite df_read_db_bug updated! ---> ", df_jira_lq_all.shape)
print("sqlite df_read_db_bug updated! ---> ", df_jira_lq_all.columns)
break
if "ID" not in df_jira_lq_all.columns:
df_jira_lq_all = df_jira_lq_all.reset_index()
print("failed to read sqlite updated! ---> ")
# df_jira_lq_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_jira_lq_all.columns:
df_jira_lq_all.drop(['领域'], axis=1, inplace=True)
if "领域分组" in df_jira_lq_all.columns:
df_jira_lq_all.drop(['领域分组'], axis=1, inplace=True)
if "姓名" in df_jira_lq_all.columns:
df_jira_lq_all.drop(['姓名'], axis=1, inplace=True)
if "用户名" in df_jira_lq_all.columns:
df_jira_lq_all.drop(['用户名'], axis=1, inplace=True)
df_jira_lq_all = pd.merge(df_jira_lq_all, df_vlookup_group_man, left_on='开发人员', right_on='用户名', how='left', indicator=False)
df_jira_lq_all.drop(['用户名'], axis=1, inplace=True)
df_jira_lq_all['姓名'] = df_jira_lq_all['姓名'].fillna('姓名null')
df_jira_lq_all['领域分组'] = df_jira_lq_all['领域分组'].fillna('领域分组null')
df_jira_lq_all['领域'] = df_jira_lq_all['领域'].fillna('领域null')
# 去除MI领域
#================================= start of VB process ===================================
# 时长
df_jira_lq_all['创建时间'] = pd.to_datetime(df_jira_lq_all['创建时间'], format='mixed')
df_jira_lq_all['更新时间'] = pd.to_datetime(df_jira_lq_all['更新时间'], format='mixed')
df_jira_lq_all['解决时间'] = pd.to_datetime(df_jira_lq_all['解决时间'], format='mixed')
df_jira_lq_all['到期时间'] = pd.to_datetime(df_jira_lq_all['到期时间'], format='mixed')
df_jira_lq_all['派单时间'] = pd.to_datetime(df_jira_lq_all['派单时间'], format='mixed')
df_jira_lq_all['解单时间'] = pd.to_datetime(df_jira_lq_all['解单时间'], format='mixed')
df_jira_lq_all['未解时长'] = datetime.datetime.now() - df_jira_lq_all['创建时间']
df_jira_lq_all['未解时长'] = (df_jira_lq_all['未解时长'].dt.total_seconds() / (24*60*60)).round(1)
df_jira_lq_all['滞留时长'] = datetime.datetime.now() - df_jira_lq_all['更新时间']
df_jira_lq_all['滞留时长'] = (df_jira_lq_all['滞留时长'].dt.total_seconds() / (24*60*60)).round(1)
df_jira_lq_all['是否算DI'] = df_jira_lq_all['状态'].apply(lambda x: "Y" if x in cfg_bug_status_DI else "N")
df_jira_lq_all['是否Bug'] = df_jira_lq_all['状态'].apply(lambda x: "Y" if x in cfg_bug_status_all else "N")
df_jira_lq_all['状态类型'] = df_jira_lq_all['状态'].apply(lambda x: s_statusType(x))
# df_jira_lq_all['超6天未更新'] = (df_jira_lq_all['滞留时长'] > 6) & (df_jira_lq_all['是否算DI'] == "Y")
# df_jira_lq_all['超2天未更新'] = (df_jira_lq_all['滞留时长'] > 2) & (df_jira_lq_all['是否算DI'] == "Y")
df_jira_lq_all['超6天未更新'] = (df_jira_lq_all['滞留时长'] > 6) & (df_jira_lq_all['状态类型'] == "dev")
df_jira_lq_all['超2天未更新'] = (df_jira_lq_all['滞留时长'] > 2) & (df_jira_lq_all['状态类型'] == "dev")
df_jira_lq_all['超6天未更新'].value_counts()
df_jira_lq_all['超6天未更新'].count()
df_jira_lq_all[df_jira_lq_all['超6天未更新'] == True].shape
df_jira_lq_all['Due超期'] = ((datetime.datetime.now()-datetime.timedelta(days=1)) > df_jira_lq_all['到期时间']) & (df_jira_lq_all['状态类型'] == "dev")
df_jira_lq_all['研发超时'] = (df_jira_lq_all['超6天未更新'] == True) & (df_jira_lq_all['状态类型'] == "dev")
print('---研发超时---', df_jira_lq_all['研发超时'].value_counts())
df_jira_lq_all['测试超时'] = (df_jira_lq_all['超6天未更新'] == True) & (df_jira_lq_all['状态类型'] == "test")
print('---测试超时---', df_jira_lq_all['测试超时'].value_counts())
select_day(-6)
df_jira_lq_all['一周派单'] = (df_jira_lq_all['派单时间'] > select_today_start) & (df_jira_lq_all['状态类型'] == "dev")
print('---一周派单---', df_jira_lq_all['一周派单'].value_counts())
df_jira_lq_all['一周解决'] = (df_jira_lq_all['解单时间'] > select_today_start)
print('---一周解决---', df_jira_lq_all['一周解决'].value_counts())
# select_day(-1)
select_day(0)
df_jira_lq_all['昨天派单'] = (df_jira_lq_all['派单时间'] > select_yesterday_start) & (df_jira_lq_all['派单时间'] < select_today_start)
print('---昨天派单---', df_jira_lq_all['昨天派单'].value_counts())
df_jira_lq_all['昨天解决'] = (df_jira_lq_all['解单时间'] > select_yesterday_start) & (df_jira_lq_all['解单时间'] < select_today_start)
print('---昨天解决---', df_jira_lq_all['昨天解决'].value_counts())
df_jira_lq_all.drop_duplicates(subset=['ID'], keep='last',inplace=True)
################################### End of 预处理 vlookup #######################################
if data_type in ['jira','Jira-phone']:
# pt_jira_bug
df_bug_all = df_jira_lq_all[df_jira_lq_all['Issue Type']== "Bug"]
print('df_bug_all------------info-------------',df_bug_all.info())
df_req_all = df_jira_lq_all[~(df_jira_lq_all['Issue Type']== "Bug")]
print('df_req_all------------info-------------',df_req_all.info())
elif data_type == "DevCloud":
df_bug_all = df_DevCloud_bug.copy() # DevCloud
print('df_bug_all------------info-------------',df_bug_all.info())
def pt_report_jira(df_to_report):
df_bug_dev_lcs = df_to_report[(df_to_report['领域'] == "龙旗软件")]
df_bug_dev_lcs_not = df_to_report[~(df_to_report['领域'] == "龙旗软件")]
if len(df_to_report)>0:
pt_df_to_report = pt_report_DI(df_to_report)
if pt_df_to_report is not None:
index_All_pt_bug_all = pt_df_to_report.loc['All','']
index_All_pt_bug_all.name = ('所有问题单总计', '')
pt_rename_bug_dev_lcs = pd.DataFrame()
if len(df_bug_dev_lcs) > 0:
pt_rename_bug_dev_lcs = pt_All_rename(df_bug_dev_lcs, ('LQ软件总计', ''))
pt_rename_bug_dev_lcs_not = pd.DataFrame()
if len(df_bug_dev_lcs_not) > 0:
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
return pt_bug_dev_all
pt_bug_all = pd.DataFrame()
pt_bug_dev_test_hw_all = pd.DataFrame()
if len(df_bug_all) > 0:
pt_bug_all = pt_report_DI(df_bug_all)
pt_bug_dev_test_hw_all = pt_report_jira(df_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)
print('规整columns------pt_bug_dev_test_hw_all',pt_bug_dev_test_hw_all.columns)
# pt_jira_req
pt_jira_req = pd.DataFrame()
df_req_all = df_jira_lq_all[~(df_jira_lq_all['Issue Type']== "Bug")]
if len(df_req_all) >0 :
pt_jira_req = pt_report_jira(df_req_all)
# pt_jira_req = pt_jira_req.sort_values(by=['领域', '剩余总数'], ascending=[False, False])
for c in ['遗留DI','昨天DI值','DI值趋势','DI值占比',]:
if c in list(pt_jira_req.columns):
pt_jira_req.drop(c, axis=1, inplace=True)
print('规整columns------pt_jira_req',pt_jira_req.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)
# ============================= End of 透视表 pivot ==========================================
# ============================ start of daily line bug ===================================
def DailyLine(parm_df):
# 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 = parm_df[(parm_df['领域'] == "龙旗软件")]
# 每日处理单
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('{:.1%}'.format)
df_daily_all_DI['累计解决率float'] = (df_daily_all_DI['累计解单'] / df_daily_all_DI['累计派单'])
df_daily_all_DI = df_daily_all_DI.reindex(columns=['今天派单','今天解单','累计派单','累计解单','累计剩余','累计解决率']+cfg_bug_level+['DI']+['累计解决率float'])
return df_daily_all_DI
if df_bug_all is not None:
df_daily_all_DI = DailyLine(df_bug_all)
if df_req_all is not None:
df_daily_req = DailyLine(df_req_all)
#========================== start ==========================
# ++++++++++++++++++++ dev +++++++++++++++++++++
def pt_DI(df_DI):
if len(df_DI)>0:
pt_DI = pd.pivot_table(df_DI, index=['领域分组'], columns=['严重等级',], \
values=['姓名'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_DI)
# pt_DI.columns = pt_DI.columns.droplevel(0)
# pt_DI.columns = pt_DI.columns.droplevel(0)
pt_DI = pt_DI.sort_values(by=['All'], ascending=[False])
# 将汇总All行切换到最后一行
if 'All' in pt_DI.index:
pt_DI_index_All = pt_DI.loc['All']
pt_DI = pt_DI.drop(index='All')
pt_DI = pt_DI._append(pt_DI_index_All)
pt_DI = pt_DI[['Blocker','Critical','Major','Minor','All','DI']]
return pt_DI
df_bug_dev_lcs_DI = pd.DataFrame()
pt_bug_dev_lcs_DI = pd.DataFrame()
if len(df_bug_all)>0:
df_bug_dev_lcs_DI = df_bug_all[(df_bug_all['领域'] == "龙旗软件") & (df_bug_all['状态类型'] == "dev")]
if len(df_bug_dev_lcs_DI) > 0:
pt_bug_dev_lcs_DI = pt_DI(df_bug_dev_lcs_DI)
# pt_bug_dev_lcs_DI = pt_bug_dev_lcs_DI[['Blocker','Critical','Major','Minor','All','DI']]
# pt_bug_dev_lcs_DI = DI_process(pt_bug_dev_lcs_DI)
# ++++++++++++++++++++++++++++++BC+++++++++++++++++++++++++++++++++++++
df_bug_dev_lcs_DI_BC = df_bug_all[(df_bug_all['领域'] == "龙旗软件") & (df_bug_all['状态类型'] == "dev") & (df_bug_all['严重等级'].isin(['Blocker','Critical']))]
pt_bug_dev_lcs_DI_BC = pt_DI(df_bug_dev_lcs_DI_BC)
pt_bug_dev_lcs_DI_BC = pt_bug_dev_lcs_DI_BC
if "Major" in pt_bug_dev_lcs_DI_BC.columns:
pt_bug_dev_lcs_DI_BC.drop(['Major'], axis=1, inplace=True)
if "Minor" in pt_bug_dev_lcs_DI_BC.columns:
pt_bug_dev_lcs_DI_BC.drop(['Minor'], axis=1, inplace=True)
def pt_status_group_man(df_status):
if len(df_status)>0:
pt_status_group_man = pd.pivot_table(df_status, index=['严重等级','领域分组','姓名'], columns=['状态',], \
values=['ID'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_status_group_man.columns = pt_status_group_man.columns.droplevel(0)
pt_status_group_man.columns = pt_status_group_man.columns.droplevel(0)
pt_status_group_man = pt_status_group_man.sort_values(by=['严重等级','All'], ascending=[True,False])
# 将汇总All行切换到最后一行
# if 'All' in pt_status_group_man.index:
# pt_status_group_man_index_All = pt_status_group_man.loc['All']
# pt_status_group_man = pt_status_group_man.drop(index='All')
# pt_status_group_man = pt_status_group_man._append(pt_status_group_man_index_All)
if 'All' in pt_status_group_man.index:
pt_status_group_man_index_All = pt_status_group_man.loc['All','','']
pt_status_group_man = pt_status_group_man.drop(index='All',axis=0, level=0)
pt_status_group_man = pt_status_group_man._append(pt_status_group_man_index_All)
return pt_status_group_man
pt_bug_dev_lcs_status_BC = pt_status_group_man(df_bug_dev_lcs_DI_BC)
df_bug_dev_lcs_DI_BC = df_bug_dev_lcs_DI_BC[['key','Summary','严重等级','Issue Type','状态','Component/s','领域','领域分组','姓名','创建时间','更新时间','未解时长']]
#-----------------------------BC All--------------------
df_bug_BC_all = df_bug_all[(df_bug_all['领域'] == "龙旗软件") & (df_bug_all['严重等级'].isin(['Blocker','Critical']))]
pt_bug_BC_all = pt_report_DI(df_bug_BC_all)
# if len(pt_bug_BC_all)>0:
if pt_bug_BC_all is not None:
for c in ['Major','Minor']:
if c in list(pt_bug_BC_all.columns):
pt_bug_BC_all.drop(c, axis=1, inplace=True)
print('规整columns------pt_bug_BC_all',pt_bug_BC_all.columns)
#==========================================================================
writer = pd.ExcelWriter(write_path_file_name, engine='openpyxl')
if df_bug_dev_lcs_DI_BC is not None:
df_bug_dev_lcs_DI_BC.to_excel(writer, sheet_name='df_BC', index=False)
if pt_bug_dev_lcs_DI_BC is not None:
pt_bug_dev_lcs_DI_BC.to_excel(writer, sheet_name='pt_BC', index=True)
if pt_bug_dev_lcs_status_BC is not None:
pt_bug_dev_lcs_status_BC.to_excel(writer, sheet_name='pt_BC_status', index=True)
if pt_bug_BC_all is not None:
df_bug_BC_all.to_excel(writer, sheet_name='df_BC_all', index=True)
pt_bug_BC_all.to_excel(writer, sheet_name='pt_BC_all', index=True)
if df_bug_dev_lcs_DI is not None:
df_bug_dev_lcs_DI.to_excel(writer, sheet_name='df_DI', index=False)
if pt_bug_dev_lcs_DI is not None:
pt_bug_dev_lcs_DI.to_excel(writer, sheet_name='pt_DI', index=True)
if df_jira is not None:
df_jira.to_excel(writer, sheet_name='原始单', index=True)
if df_jira_lq_all is not None:
df_jira_lq_all.to_excel(writer, sheet_name='龙旗单', index=False)
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_jira_req is not None:
pt_jira_req.to_excel(writer, sheet_name='pt_jira_req', index=True)
if df_daily_req is not None:
df_daily_req.to_excel(writer, sheet_name='Req_line', index=True)
# if pt_jira_bug is not None:
# pt_jira_bug.to_excel(writer, sheet_name='pt_jira_bug', index=True)
if pt_bug_dev_test_hw_all is not None:
pt_bug_dev_test_hw_all.to_excel(writer, sheet_name='pt_Bug_report_DI', index=True)
if pt_bug_all is not None:
pt_bug_all.to_excel(writer, sheet_name='pt_bug_all', index=True)
df_null = df_bug_all[df_bug_all['姓名'] == '姓名null']
if df_null is not None:
df_null.to_excel(writer, sheet_name='问题单_姓名空白', index=False)
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 df 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\SimHei.ttc", size=12)
my_font = FontProperties(fname=r"c:\windows\fonts\simsun.ttc", size=12)
# my_font = FontProperties(fname=r"c:\windows\fonts\simsunb.ttf", size=12)
plt.rcParams['font.family'] = ['sans-serif']
plt.rcParams['font.sans-serif'] = ['SimSun'] #显示中文
# plt.rcParams['font.sans-serif'] = ['SimHei'] #显示中文
# plt.rcParams['font.sans-serif'] = [u'SimHei'] #显示中文
# plt.rcParams['font.sans-serif'] = ['SimSun'] #显示中文
# plt.rcParams['font.sans-serif'] = ['SimSun-ExtB'] #显示中文
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=(20,24), dpi = 150, facecolor = 'lightgray', edgecolor='red', frameon=True, constrained_layout=True)
plt.rcParams['figure.constrained_layout.use'] = True
# plt.subplots(layout="constrained")
if len(df_daily_all_DI) > 0:
##################################################################################
print("Start to plt 1 0==={==================>>> ")
plt.subplot(4,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='Total Bug')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['累计解单'], color='g', label='Resolved')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['累计剩余'], color='b', label='Unresolved')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['DI'], color='gold', label='DI')
# plt.plot(df_daily_all_DI.index, df_daily_all_DI['累计解决率float']*1000, color='darkviolet', label='percent')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['累计解决率float']*1000, color='Violet', label='Percent')
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='bottom',fontsize=11)
y_add_sum = df_daily_all_DI['累计派单']
y_fix_sum = df_daily_all_DI['累计解单']
y_remain_sum = df_daily_all_DI['累计剩余']
y_percent = df_daily_all_DI['累计解决率float']*1000
# 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]
f = y_percent[-1]
plt.text(a+0.1,c-0.1,'%.0f'%c,ha='left',va='bottom',fontsize=11)
plt.text(a+0.1,d-0.1,'%.0f'%d,ha='left',va='top',fontsize=11)
plt.text(a+0.1,e-0.1,'%.0f'%e,ha='left',va='top',fontsize=11)
plt.text(a+0.1,f-0.1,df_daily_all_DI['累计解决率'].iloc[-1],ha='left',va='center',fontsize=11)
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()
print("End to plt 1 0==={==================>>> ")
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
print("Start to plt 2 0==={==================>>> ")
plt.subplot(4,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='g', 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=11)
plt.text(a,c-0.1,'%.0f'%c,ha='left',va='center',fontsize=11)
# 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.ylabel(u'每日BUG数量', fontproperties=my_font)
# plt.legend(labels=['今天派单','今天解单','DI'], loc='upper left')
plt.legend(loc='upper left',)
#网格线
plt.grid()
print("End to plt 2 0==={==================>>> ")
#--------------------------------------------------------------------------------------------
print("Start to plt 3 0==={==================>>> ")
# if pt_bug_dev_lcs_DI is not None:
if len(pt_bug_dev_lcs_DI) > 0:
plt.subplot(4,1,3) # 分成3行1列，起始点为3
#x轴数据
x= pt_bug_dev_lcs_DI.index
#y轴数据
y= pt_bug_dev_lcs_DI['All']
bar_width = 0.3
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.bar(x,y,bar_width,edgecolor='black',align="center",alpha=0.2,bottom=0,color="deepskyblue",hatch="/",label="遗留Bug")
plt.text(x[i],y[i]+10,'%.0f' % y[i],ha='center',va='center',fontsize=11,)
x3 = range(len(x))
# plt.xticks(x3, x, rotation='vertical', fontproperties=my_font)
plt.xticks(x3, x, rotation=45, 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([-0.5+(-bar_width / 2), 0.5+(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()
print("End to plt 3 0==={==================>>> ")
#---------------------------------------------------------------------------
print("Start to plt 4 0==={==================>>> ")
if len(df_daily_req) > 0:
plt.subplot(4,1,4) # 分成4行1列，起始点为4
plt.xlabel(u'日期', fontproperties=my_font)
plt.ylabel(u'需求数量', fontproperties=my_font)
plt.plot(df_daily_req.index, df_daily_req['累计派单'], color='r', linewidth=1.5, linestyle='-', label='Total SR')
plt.plot(df_daily_req.index, df_daily_req['累计解单'], color='g', label='Resolved')
plt.plot(df_daily_req.index, df_daily_req['累计剩余'], color='b', label='Unresolved')
#plt.plot(df_daily_req.index, df_daily_req['DI'], color='gold', label='DI')
plt.plot(df_daily_req.index, df_daily_req['累计解决率float']*1000, color='Violet', label='Percent')
x1 = range(len(df_daily_req.index))
plt.xticks(x1, df_daily_req.index, rotation='vertical', fontproperties=my_font)
y_DI = df_daily_req['DI']
# 只显示最后一个值
a = len(df_daily_req.index)
b = y_DI[-1]
#plt.text(a+0.1,b-0.1,'%.0f'%b,ha='left',va='center',fontsize=11)
y_add_sum = df_daily_req['累计派单']
y_fix_sum = df_daily_req['累计解单']
y_remain_sum = df_daily_req['累计剩余']
y_percent = df_daily_req['累计解决率float']*1000
c = y_add_sum[-1]
d = y_fix_sum[-1]
e = y_remain_sum[-1]
f = y_percent[-1]
plt.text(a+0.1,c-0.1,'%.0f'%c,ha='left',va='bottom',fontsize=11)
plt.text(a+0.1,d-0.1,'%.0f'%d,ha='left',va='top',fontsize=11)
plt.text(a+0.1,e-0.1,'%.0f'%e,ha='left',va='bottom',fontsize=11)
plt.text(a+0.1,f-0.1,df_daily_req['累计解决率'].iloc[-1],ha='left',va='center',fontsize=11)
plt.title(u'需求累计趋势', loc='center', fontsize=20, fontproperties=my_font)
plt.legend(loc='upper left', )
#网格线
plt.grid()
print("End to plt 4 0==={==================>>> ")
##################################################################################################333
# #显示标签
# plt.legend(loc='upper left', fontproperties=my_font)
# #完整显示
# plt.tight_layout()
# # 保存绘图
# plt.savefig(write_path_savefig)
#完整显示
fig1.tight_layout()
# 保存绘图
fig1.savefig(write_path_savefig)
#绘图
# plt.show()
# tk.messagebox.showinfo(title='提示',message='End of plt')
# event.set()
print("All plt success! 0==={==================>>> ")
扫一扫
2520
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
