# -*- coding: <encoding name> -*- : # -*- coding: utf-8 -*-
# coding=<encoding name> ： # coding=utf-8
# coding：<encoding name> ： # coding: utf-8
# coding=utf-8
# -*- coding:utf-8 -*-
from tkinter.constants import DISABLED, NORMAL
import pandas as pd
import matplotlib.pyplot as plt
import datetime
import time
import os
import numpy as np
import tkinter as tk
from tkinter import ttk
import tkinter.messagebox
import hashlib
import threading
from time import sleep,ctime
import time
def process_req_bug(path_req_bug, path_vlookup):
print('11111111111111111111111')
print('path_req_bug : ', path_req_bug)
print('path_vlookup : ', path_vlookup)
print('2222222222222222222222222222')
path_req_bug = path_req_bug.rstrip("\n")
path_vlookup = path_vlookup.rstrip("\n")
print('path_req_bug : ', path_req_bug)
print('path_vlookup : ', path_vlookup)
print('33333333333333333333333333333333333333333')
# -*- coding: <encoding name> -*- : # -*- coding: utf-8 -*-
# coding=<encoding name> ： # coding=utf-8
# coding：<encoding name> ： # coding: utf-8
# coding=utf-8
#-*- coding:utf-8 -*-
# from numpy.core.fromnumeric import _take_dispatcher
# import pandas as pd
# import matplotlib.pyplot as plt
# import datetime
# import os
# import numpy as np
# -*- coding: <encoding name> -*- : # -*- coding: utf-8 -*-
# coding=<encoding name> ： # coding=utf-8
# coding：<encoding name> ： # coding: utf-8
# coding=utf-8
#-*- coding:utf-8 -*-
pd.options.display.max_columns = 100
now_time = datetime.datetime.strftime(datetime.datetime.now(),'%Y%m%d-%H%M%S')
# now_time = datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d %H:%M:%S')
print('now_time is ', now_time)
dir_path = path_req_bug
# dir_path = r"C:\pyDaily\Daily"
file_read_Req = r"Req information.xlsx"
file_read_Bug = r"BUG-INFO.xlsx"
# file_read_line = r"zx_out_line.xlsx"
# file_read_line_yy = r"zx_out_line_yy.xlsx"
# file_write = now_time + "zx_out.xlsx"
file_write = now_time + "zx_ZN.xls"
# file_write_line = now_time + 'zx_out_line.xlsx'
# file_write_line_yy = now_time + "zx_out_line_yy.xlsx"
file_savefig = now_time + "zx_savefig.png"
file_path_Req = os.path.join(dir_path, file_read_Req)
file_path_Bug = os.path.join(dir_path, file_read_Bug)
write_path = os.path.join(dir_path, file_write)
# write_path_line = os.path.join(dir_path, file_write_line)
# write_path_line_yy = os.path.join(dir_path, file_write_line_yy)
savefig_path = os.path.join(dir_path, file_savefig)
vlookup_path = path_vlookup
# vlookup_path = r'C:\pyDaily\vlookup\人员名单.xlsx' # 4 & 6
# vlookup_path = r'C:\Work\ZX\daily\人员名单\Pikachu项目人员信息_0917.xlsx' # MTK
# vlookup_path_zhanyongding = r'C:\Work\ZX\daily\人员名单\人力明细2021-08-05-zhanyongding.xlsx'
# vlookup_path = r'C:\Work\ZX\daily\人员名单\人员领域-Giant&Raichu项目人员清单和领域-0830.xlsx'
# vlookup_path_yy = r'C:\Work\ZX\daily\人员名单\应用域人员名单_20210610.xlsx'
# =======================VLOOKUP===============================
# 检查vlookup 人员名单文件
try:
f =open(vlookup_path)
f.close()
except IOError:
print("vlookup_path Error! File is not accessible.")
tk.messagebox.showerror(title='路径文件读取错误',message='请检查人员名单文件！点击退出')
# tk.messagebox.showinfo(title='Start Read df_vl', message=vlookup_path)
# df_vl = pd.read_excel(vlookup_path, sheet_name = 'df_vl', )
df_vl = pd.read_excel(vlookup_path, sheet_name = 'df_vl', engine = 'openpyxl')
# df_vl = pd.read_excel(vlookup_path, sheet_name = 'df_vl', engine = 'openpyxl')
# df_vl = pd.read_excel(r'C:\pyDaily\vlookup\人员名单.xlsx', sheet_name = 'df_vl', )
# df_vl = pd.read_excel('C:\\pyDaily\\vlookup\\人员名单.xlsx', sheet_name = 'df_vl', )
# df_vl = pd.read_excel('C:/pyDaily/vlookup/人员名单.xlsx', sheet_name = 'df_vl', )
# tk.messagebox.showinfo(title='End Read df_vl', message='read success!')
# if :
# tk.messagebox.showerror(消息框标题,错误提示内容)
# df_yy_vl = pd.read_excel(vlookup_path_yy, sheet_name = 'df_vl', )
# df_vl_zhanyongding = pd.read_excel(vlookup_path_zhanyongding, sheet_name = 'Sheet1', )
print('file_path_Req === ', file_path_Req)
print('file_path_Bug === ', file_path_Bug)
print('file_path_write === ', file_write)
# 检查需求和问题单文件
try:
f =open(file_path_Req)
f.close()
except IOError:
print("open(file_path_Req) Error! File is not accessible.")
tk.messagebox.showerror(title='路径文件读取错误', message='请检查需求文件！点击退出')
# df_req = pd.read_excel(file_path_Req, sheet_name='需求信息') # 旧视图
# tk.messagebox.showinfo(title='Start Read df_req', message=file_path_Req)
df_req = pd.read_excel(file_path_Req, sheet_name='需求信息01', engine='openpyxl') # 新视图
# tk.messagebox.showinfo(title='End Read df_req', message='success!')
# 检查需求和问题单文件
try:
f =open(file_path_Bug)
f.close()
except IOError:
print("open(file_path_Bug) Error! File is not accessible.")
tk.messagebox.showerror(title='路径文件读取错误', message='请检查BUG文件！点击退出')
# tk.messagebox.showinfo(title='Start Read df_bug_all', message=file_path_Bug)
df_bug_all = pd.read_excel(file_path_Bug, sheet_name='缺陷信息01', engine='openpyxl')
print('df_bug_all --- 原始单', df_bug_all.shape)
# tk.messagebox.showinfo(title='End Read df_bug_all', message='success!')
print('original df_req.shape ', df_req.shape)
print('original df_bug_all.shape ', df_bug_all.shape)
print('--------------------------------------------------------------------------')
# tk.messagebox.showinfo(title='df_req.shape',message=df_req.shape)
# tk.messagebox.showinfo(title='df_bug.shape',message=df_bug_all.shape)
# 常量
req_status = ['已提交待评审','评审中','评审驳回','评审完成','已基线','变更中','变更评审中','变更驳回','变更已接纳']
req_kanban_status = ['初始化','分析设计','实现','测试验证','已完成','已取消']
bug_status = ['待提交','待指派','待分析','待修改','待修改审核','待CCB','挂起','待归档','待回归验证','已关闭','已废弃','全部状态',]
bug_status_dev = ['待分析','待修改','待修改审核','待CCB']
bug_level = ['Blocker','Critical','Major', 'Normal', 'Minor',]
qushi = ['累计指派', '累计解单', '开发环节单','DI', '当天解单','当天指派']
group_zk = ['OS','Camera','多媒体','协议','协议-短距','应用','调优','性能','功耗','稳定性',]
writer = pd.ExcelWriter(write_path, engine='xlwt')
# # -------------------start today------------------------------
if datetime.date.today().weekday() == 0:
d_monday = -2 # 周一
# d_timedelta = 0 #
else:
d_monday = -1
# d_timedelta = d_timedelta + 1
# d_timedelta = -1
d_timedelta = 0
# select_date------------------------------
select_date = datetime.date.today() + datetime.timedelta(days=d_timedelta)
select_datetime = datetime.datetime.combine(select_date, datetime.datetime.min.time())
select_date_end = datetime.date.today() + datetime.timedelta(days=d_timedelta+1)
select_datetime_end = datetime.datetime.combine(select_date_end, datetime.datetime.min.time())
select_datetime_yesterday = select_datetime + datetime.timedelta(days=d_monday)
print('select_datetime is ', select_datetime)
# 补全req流程状态
def req_case(df_pt):
for i in req_status:
# 判断pivot结果中是否有缺失的case类型
if (i in df_pt.columns.values) == False:
df_pt[i] = 0
# print("df_pt 增加一列------",i)
# 补全req流程状态
def req_kanban_case(df_pt):
for i in req_kanban_status:
# 判断pivot结果中是否有缺失的case类型
if (i in df_pt.columns.values) == False:
df_pt[i] = 0
# print("df_pt 增加一列------",i)
# 补全bug流程状态
def bug_case(df_pt):
for i in bug_status:
# 判断pivot结果中是否有缺失的case类型
if (i in df_pt.columns.values) == False:
df_pt[i] = 0
# print("df_pt 增加一列------",i)
# 补全bug等级
def bug_case_level(df_pt):
for i in bug_level:
# 判断pivot结果中是否有缺失的case类型
if (i in df_pt.columns.values) == False:
df_pt[i] = 0
# print("df_pt 增加一列------",i)
def DI_cal(df_DI):
bug_case_level(df_DI)
df_DI['DI'] = 0
for index in df_DI.index:
df_DI.loc[index, 'DI'] = df_DI.loc[index,'Minor']*0.1 \
+ df_DI.loc[index,'Normal']*1 + df_DI.loc[index,'Major']*3 \
+ df_DI.loc[index,'Critical']*10 + df_DI.loc[index,'Blocker']*10
# df_DI = df_DI[bug_level]
df_DI = df_DI[['Blocker','Critical','Major', 'Normal', 'Minor','DI']]
df_DI = df_DI.sort_values(by='DI', ascending=False)
# 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)
def color_neg_red(val):
if val < 1.0:
color = 'red'
else:
color = 'black'
return ('color:%s'% color)
def stay_time(df_stay_time):
if len(df_stay_time) > 0:
pt_stay_time = pd.pivot_table(df_stay_time, index=['领域_当前', '领域主管_当前'], columns=['BUG状态'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# pt_stay_time.index.names = ['领域', '领域主管']
pt_stay_time.columns = pt_stay_time.columns.droplevel(0)
pt_stay_time.columns = pt_stay_time.columns.droplevel(0)
pt_stay_time = pt_stay_time.sort_values(by=['All'], ascending=[False])
# print('pt_stay_time---------',pt_stay_time)
df_stay_time_gp = df_stay_time.groupby(['领域_当前',])[['待解单开发滞留时长',]].mean()
# print('df_stay_time_gp---------',df_stay_time_gp)
pt_stay_time = pd.merge(pt_stay_time, df_stay_time_gp, how='outer', left_index=True, right_index=True)\
.fillna(df_stay_time['待解单开发滞留时长'].mean())
pt_stay_time['待解单开发滞留时长'] = pt_stay_time['待解单开发滞留时长'].apply('{:.2f}'.format).astype(float)
df_stay_time = pt_stay_time.sort_values(by=['All', '待解单开发滞留时长',], ascending=[False, False])
return df_stay_time
# Start Req-----------------------------------------------------------
# --------------------------req预处理数据表-----------------------------------
# 拆分“当前责任人”列的字符串“姓名 工号”，并替换为只有“姓名”
s_req = pd.DataFrame()
s_req[["姓名","工号"]] = df_req['Task责任人(Task req owner)'].astype(str).apply(lambda x: pd.Series([i for i in x.split(' ')]))
df_req['Task责任人(Task req owner)'] = s_req['姓名']
df_req = df_req.rename(columns={'Task责任人(Task req owner)':'Task责任人'})
print('----------------req merge--------------------')
df_req_all = pd.merge(df_req, df_vl.loc[:, ['人员', '领域', '领域主管', '合作方']], \
how='left', left_on='Task责任人', right_on='人员')
df_req_all['人员'] = df_req_all['人员'].fillna('查无此人')
df_req_all['领域'] = df_req_all['领域'].fillna('人员名单未匹配到领域')
df_req_all['领域主管'] = df_req_all['领域主管'].fillna('人名未匹配到领域主管')
df_req_all['合作方'] = df_req_all['合作方'].fillna('人名未匹配到合作方')
print('df_req_all.shape', df_req_all.shape)
# 删除匹配不到的责任人数据，
# df_req_all.dropna(subset=['Task责任人', '人员'], axis=0, how='any', inplace = True)
# 删除 SE 要删除的
# df_req_all.drop(df_req_all[df_req_all['Task责任人'] == '邬显康'].index, inplace=True)
# df_req_all.drop(df_req_all[df_req_all['Task责任人'] == '黎晶'].index, inplace=True)
# print('df_req_all.shape drop 邬显康,黎晶', df_req_all.shape)
df_req_all.drop_duplicates(subset=['TaskID(Task req codeid)',], keep='first',inplace=True)
print('drop_duplicates df_req_all', df_req_all.shape)
df_req_TR4 = df_req_all[df_req_all['TASK客户交付节点(TASK node)'].isin(['TR4',])]
print('df_req_TR4.shape', df_req_TR4.shape)
# df_req_emergency = df_req_all[df_req_all['TASK优先级(TASK priority)'].isin(['紧急',])]
print('df_req_TR4.shape', df_req_TR4.shape)
df_req_TR4_TR4A = df_req_all[df_req_all['TASK客户交付节点(TASK node)'].isin(['TR4','TR4A'])]
print('df_req_TR4_TR4A.shape', df_req_TR4_TR4A.shape)
print('--------------------------End of req 预处理！------------------------------------------------')
# TR4
# =============================req透视表 pivot ==========================================
pt_req_TR4_group = pd.pivot_table(df_req_TR4, index=['领域', '领域主管'], columns=['Task需求状态(Task status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_req_TR4_group.columns = pt_req_TR4_group.columns.droplevel(0)
pt_req_TR4_group.columns = pt_req_TR4_group.columns.droplevel(0)
req_case(pt_req_TR4_group)
# =============================req透视表 pivot ==========================================
pt_req_TR4_man = pd.pivot_table(df_req_TR4, index=['领域','Task责任人'], columns=['Task需求状态(Task status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_req_TR4_man.columns = pt_req_TR4_man.columns.droplevel(0)
pt_req_TR4_man.columns = pt_req_TR4_man.columns.droplevel(0)
req_case(pt_req_TR4_man)
# ------------end of TR4----------------------------
# TR4_TR4A
# =============================req透视表 pivot ==========================================
pt_req_TR4_TR4A_group = pd.pivot_table(df_req_TR4_TR4A, index=['领域', '领域主管'], columns=['Task需求状态(Task status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_req_TR4_TR4A_group.columns = pt_req_TR4_TR4A_group.columns.droplevel(0)
pt_req_TR4_TR4A_group.columns = pt_req_TR4_TR4A_group.columns.droplevel(0)
req_case(pt_req_TR4_TR4A_group)
# =============================req透视表 pivot ==========================================
pt_req_TR4_TR4A_man = pd.pivot_table(df_req_TR4_TR4A, index=['领域','Task责任人'], columns=['Task需求状态(Task status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_req_TR4_TR4A_man.columns = pt_req_TR4_TR4A_man.columns.droplevel(0)
pt_req_TR4_TR4A_man.columns = pt_req_TR4_TR4A_man.columns.droplevel(0)
req_case(pt_req_TR4_TR4A_man)
# ------------end of TR4_TR4A----------------------------
# # emergency
# # =============================req透视表 pivot ==========================================
# pt_req_emergency_group = pd.pivot_table(df_req_emergency, index=['领域', '领域主管'], columns=['Task需求状态(Task status)'], \
# values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# pt_req_emergency_group.columns = pt_req_emergency_group.columns.droplevel(0)
# pt_req_emergency_group.columns = pt_req_emergency_group.columns.droplevel(0)
# req_case(pt_req_emergency_group)
# # ------------end of emergency----------------------------
# # =============================req透视表 pivot ==========================================
# pt_req_kanban_emergency_group = pd.pivot_table(df_req_emergency, index=['领域', '领域主管'], columns=['Task看板状态(Task KANBEN status)'], \
# values=['人员'], aggfunc=[np.count_nonzero], margins = True)
# # print('pt_req_kanban_emergency_group.columns', pt_req_kanban_emergency_group.columns)
# pt_req_kanban_emergency_group.columns = pt_req_kanban_emergency_group.columns.droplevel(0)
# pt_req_kanban_emergency_group.columns = pt_req_kanban_emergency_group.columns.droplevel(0)
# # print('pt_req_kanban_emergency_group.columns', pt_req_kanban_emergency_group.columns)
# pt_req_kanban_emergency_group = pt_req_kanban_emergency_group.fillna(0)
# pt_req_kanban_emergency_group.rename(columns={'All':'累计AR',}, inplace=True)
# req_kanban_case(pt_req_kanban_emergency_group)
# pt_req_kanban_emergency_group['未完成'] = pt_req_kanban_emergency_group['累计AR'] - pt_req_kanban_emergency_group['已完成'] - pt_req_kanban_emergency_group['已取消']
# pt_req_kanban_emergency_group_order = ['初始化','分析设计','实现','测试验证','未完成','已取消','已完成','累计AR']
# pt_req_kanban_emergency_group = pt_req_kanban_emergency_group[pt_req_kanban_emergency_group_order]
# # pt_req_kanban_emergency_group['完成率'] = (1-(pt_req_kanban_emergency_group['未完成'].astype(float) / pt_req_kanban_emergency_group['累计AR'].astype(float))) \
# # .apply('{:.0%}'.format)
# # pt_req_kanban_emergency_group.sort_values(by='未完成', ascending=False, inplace=True)
# pt_req_kanban_emergency_group['完成率'] = (1-(pt_req_kanban_emergency_group['未完成'].astype(float) / pt_req_kanban_emergency_group['累计AR'].astype(float))) \
# .apply('{:.4}'.format)
# # pt_req_kanban_emergency_group = pt_req_kanban_emergency_group.style.apply(color_neg_red, subset=['完成率'])
# # sort_values
# pt_req_kanban_emergency_group = pt_req_kanban_emergency_group.sort_values(by=['完成率'], ascending=[False])
# pt_req_kanban_emergency_group['完成率'] = pt_req_kanban_emergency_group['完成率'].astype(float).apply('{:.0%}'.format)
# # 将汇总All行切换到最后一行
# pt_req_kanban_emergency_group_All = pt_req_kanban_emergency_group.loc['All','']
# pt_req_kanban_emergency_group = pt_req_kanban_emergency_group.drop(index='All',axis=0, level=0)
# pt_req_kanban_emergency_group = pt_req_kanban_emergency_group.append(pt_req_kanban_emergency_group_All)
# TR4
# =============================req透视表 pivot ==========================================
pt_req_kanban_TR4_group = pd.pivot_table(df_req_TR4, index=['领域', '领域主管'], columns=['Task看板状态(Task KANBEN status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True)
# print('pt_req_kanban_TR4_group.columns', pt_req_kanban_TR4_group.columns)
pt_req_kanban_TR4_group.columns = pt_req_kanban_TR4_group.columns.droplevel(0)
pt_req_kanban_TR4_group.columns = pt_req_kanban_TR4_group.columns.droplevel(0)
# print('pt_req_kanban_TR4_group.columns', pt_req_kanban_TR4_group.columns)
pt_req_kanban_TR4_group = pt_req_kanban_TR4_group.fillna(0)
pt_req_kanban_TR4_group.rename(columns={'All':'累计AR',}, inplace=True)
req_kanban_case(pt_req_kanban_TR4_group)
pt_req_kanban_TR4_group['未完成'] = pt_req_kanban_TR4_group['累计AR'] - pt_req_kanban_TR4_group['已完成'] - pt_req_kanban_TR4_group['已取消']
pt_req_kanban_TR4_group_order = ['初始化','分析设计','实现','测试验证','未完成','已取消','已完成','累计AR']
pt_req_kanban_TR4_group = pt_req_kanban_TR4_group[pt_req_kanban_TR4_group_order]
# pt_req_kanban_TR4_group['完成率'] = (1-(pt_req_kanban_TR4_group['未完成'].astype(float) / pt_req_kanban_TR4_group['累计AR'].astype(float))) \
# .apply('{:.0%}'.format)
# pt_req_kanban_TR4_group.sort_values(by='未完成', ascending=False, inplace=True)
pt_req_kanban_TR4_group['完成率'] = (1-(pt_req_kanban_TR4_group['未完成'].astype(float) / pt_req_kanban_TR4_group['累计AR'].astype(float))) \
.apply('{:.4}'.format)
# pt_req_kanban_TR4_group = pt_req_kanban_TR4_group.style.apply(color_neg_red, subset=['完成率'])
# sort_values
pt_req_kanban_TR4_group = pt_req_kanban_TR4_group.sort_values(by=['完成率'], ascending=[False])
pt_req_kanban_TR4_group['完成率'] = pt_req_kanban_TR4_group['完成率'].astype(float).apply('{:.0%}'.format)
# 将汇总All行切换到最后一行
pt_req_kanban_TR4_group_All = pt_req_kanban_TR4_group.loc['All','']
pt_req_kanban_TR4_group = pt_req_kanban_TR4_group.drop(index='All',axis=0, level=0)
pt_req_kanban_TR4_group = pt_req_kanban_TR4_group.append(pt_req_kanban_TR4_group_All)
pt_req_kanban_TR4_group['AR占比'] = pt_req_kanban_TR4_group['累计AR']/pt_req_kanban_TR4_group.loc['All','累计AR'][0]
pt_req_kanban_TR4_group['AR占比'] = pt_req_kanban_TR4_group['AR占比'].astype(float).apply('{:.0%}'.format)
# df_req_today_solve
df_req_complete = df_req_TR4[df_req_TR4['Task看板状态(Task KANBEN status)'] == '已完成']
df_req_complete['TASK实际完成时间(TASK Actual finish time)'] = pd.to_datetime(df_req_complete['TASK实际完成时间(TASK Actual finish time)'])
df_req_yesterday_solve = df_req_complete[(df_req_complete['TASK实际完成时间(TASK Actual finish time)'] >= select_datetime_yesterday)]
df_req_yesterday_solve = df_req_yesterday_solve[(df_req_yesterday_solve['TASK实际完成时间(TASK Actual finish time)'] < select_datetime)]
df_req_today_solve = df_req_complete[(df_req_complete['TASK实际完成时间(TASK Actual finish time)'] >= select_datetime)]
if len(df_req_yesterday_solve) > 0:
pt_req_yesterday_group = pd.pivot_table(df_req_yesterday_solve, index=['领域', '领域主管'], columns=['Task看板状态(Task KANBEN status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_req_yesterday_group.columns = pt_req_yesterday_group.columns.droplevel(0)
pt_req_yesterday_group.columns = pt_req_yesterday_group.columns.droplevel(0)
pt_req_yesterday_group.rename(columns={'已完成':'昨天完成',}, inplace=True)
pt_req_kanban_TR4_group = pd.merge(pt_req_kanban_TR4_group, pt_req_yesterday_group['昨天完成'], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if len(df_req_today_solve) > 0:
pt_req_today_group = pd.pivot_table(df_req_today_solve, index=['领域', '领域主管'], columns=['Task看板状态(Task KANBEN status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_req_today_group.columns = pt_req_today_group.columns.droplevel(0)
pt_req_today_group.columns = pt_req_today_group.columns.droplevel(0)
pt_req_today_group.rename(columns={'已完成':'今天完成',}, inplace=True)
pt_req_kanban_TR4_group = pd.merge(pt_req_kanban_TR4_group, pt_req_today_group['今天完成'], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
# =============================req透视表 pivot ==========================================
pt_req_kanban_TR4_man = pd.pivot_table(df_req_TR4, index=['领域','Task责任人'], columns=['Task看板状态(Task KANBEN status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True)
# print('pt_req_kanban_TR4_man', pt_req_kanban_TR4_man.head(3))
# print('pt_req_kanban_TR4_man.columns', pt_req_kanban_TR4_man.columns)
pt_req_kanban_TR4_man.columns = pt_req_kanban_TR4_man.columns.droplevel(0)
pt_req_kanban_TR4_man.columns = pt_req_kanban_TR4_man.columns.droplevel(0)
# print('pt_req_kanban_TR4_man.columns', pt_req_kanban_TR4_man.columns)
pt_req_kanban_TR4_man = pt_req_kanban_TR4_man.fillna(0)
pt_req_kanban_TR4_man.rename(columns={'All':'累计AR',}, inplace=True)
req_kanban_case(pt_req_kanban_TR4_man)
pt_req_kanban_TR4_man['未完成'] = pt_req_kanban_TR4_man['累计AR'] - pt_req_kanban_TR4_man['已完成'] - pt_req_kanban_TR4_man['已取消']
pt_req_kanban_TR4_man_order = ['初始化','分析设计','实现','测试验证','未完成','已取消','已完成','累计AR']
pt_req_kanban_TR4_man = pt_req_kanban_TR4_man[pt_req_kanban_TR4_man_order]
# pt_req_kanban_TR4_man['关闭率'] = (pt_req_kanban_TR4_man['已关闭'].astype(float) / pt_req_kanban_TR4_man['累计AR'].astype(float)).round(2)
pt_req_kanban_TR4_man['完成率'] = (1-(pt_req_kanban_TR4_man['未完成'].astype(float) / pt_req_kanban_TR4_man['累计AR'].astype(float))) \
.apply('{:.0%}'.format)
pt_req_kanban_TR4_man.sort_values(by=['未完成','领域',], ascending=[False,True,], inplace=True)
# ------------end of TR4----------------------------
# TR4_TR4A
# =============================req透视表 pivot ==========================================
pt_req_kanban_TR4_TR4A_group = pd.pivot_table(df_req_TR4_TR4A, index=['领域', '领域主管'], columns=['Task看板状态(Task KANBEN status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True)
# print('pt_req_kanban_TR4_TR4A_group', pt_req_kanban_TR4_TR4A_group.head(3))
# print('pt_req_kanban_TR4_TR4A_group.columns', pt_req_kanban_TR4_TR4A_group.columns)
pt_req_kanban_TR4_TR4A_group.columns = pt_req_kanban_TR4_TR4A_group.columns.droplevel(0)
pt_req_kanban_TR4_TR4A_group.columns = pt_req_kanban_TR4_TR4A_group.columns.droplevel(0)
# print('pt_req_kanban_TR4_TR4A_group.columns', pt_req_kanban_TR4_TR4A_group.columns)
pt_req_kanban_TR4_TR4A_group = pt_req_kanban_TR4_TR4A_group.fillna(0)
pt_req_kanban_TR4_TR4A_group.rename(columns={'All':'累计AR',}, inplace=True)
req_kanban_case(pt_req_kanban_TR4_TR4A_group)
pt_req_kanban_TR4_TR4A_group['未完成'] = pt_req_kanban_TR4_TR4A_group['累计AR'] - pt_req_kanban_TR4_TR4A_group['已完成'] - pt_req_kanban_TR4_TR4A_group['已取消']
pt_req_kanban_TR4_TR4A_group_order = ['初始化','分析设计','实现','测试验证','未完成','已取消','已完成','累计AR']
pt_req_kanban_TR4_TR4A_group = pt_req_kanban_TR4_TR4A_group[pt_req_kanban_TR4_TR4A_group_order]
pt_req_kanban_TR4_TR4A_group['完成率'] = (1-(pt_req_kanban_TR4_TR4A_group['未完成'].astype(float) / pt_req_kanban_TR4_TR4A_group['累计AR'].astype(float))) \
.apply('{:.0%}'.format)
# pt_req_kanban_TR4_TR4A_group.sort_values(by='未完成', ascending=False, inplace=True)
pt_req_kanban_TR4_TR4A_group.sort_values(by=['未完成','领域'], ascending=[False,True], inplace=True)
# =============================req透视表 pivot ==========================================
pt_req_kanban_TR4_TR4A_man = pd.pivot_table(df_req_TR4_TR4A, index=['领域','Task责任人'], columns=['Task看板状态(Task KANBEN status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True)
# print('pt_req_kanban_TR4_TR4A_man.columns', pt_req_kanban_TR4_TR4A_man.columns)
pt_req_kanban_TR4_TR4A_man.columns = pt_req_kanban_TR4_TR4A_man.columns.droplevel(0)
pt_req_kanban_TR4_TR4A_man.columns = pt_req_kanban_TR4_TR4A_man.columns.droplevel(0)
# print('pt_req_kanban_TR4_TR4A_man.columns', pt_req_kanban_TR4_TR4A_man.columns)
pt_req_kanban_TR4_TR4A_man = pt_req_kanban_TR4_TR4A_man.fillna(0)
pt_req_kanban_TR4_TR4A_man.rename(columns={'All':'累计AR',}, inplace=True)
req_kanban_case(pt_req_kanban_TR4_TR4A_man)
pt_req_kanban_TR4_TR4A_man['未完成'] = pt_req_kanban_TR4_TR4A_man['累计AR'] - pt_req_kanban_TR4_TR4A_man['已完成'] - pt_req_kanban_TR4_TR4A_man['已取消']
pt_req_kanban_TR4_TR4A_man_order = ['初始化','分析设计','实现','测试验证','未完成','已取消','已完成','累计AR']
pt_req_kanban_TR4_TR4A_man = pt_req_kanban_TR4_TR4A_man[pt_req_kanban_TR4_TR4A_man_order]
# pt_req_kanban_TR4_TR4A_man['关闭率'] = (pt_req_kanban_TR4_TR4A_man['已关闭'].astype(float) / pt_req_kanban_TR4_TR4A_man['累计AR'].astype(float)).round(2)
pt_req_kanban_TR4_TR4A_man['完成率'] = (1-(pt_req_kanban_TR4_TR4A_man['未完成'].astype(float) / pt_req_kanban_TR4_TR4A_man['累计AR'].astype(float))) \
.apply('{:.0%}'.format)
pt_req_kanban_TR4_TR4A_man.sort_values(by=['领域', '未完成'], ascending=[True, False], inplace=True)
# ------------------Start of SR --------------------
print('df_req_TR4_TR4A.shape------', df_req_TR4_TR4A.shape)
df_req_TR4_TR4A['SRS转测结果(SRS Test Result)'] = df_req_TR4_TR4A['SRS转测结果(SRS Test Result)'].fillna('空白')
pt_req_kanban_TR4_TR4A_SR = pd.pivot_table(df_req_TR4_TR4A, index=['领域', '领域主管'], columns=['SRS转测结果(SRS Test Result)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True)
pt_req_kanban_TR4_TR4A_SR.columns = pt_req_kanban_TR4_TR4A_SR.columns.droplevel(0)
pt_req_kanban_TR4_TR4A_SR.columns = pt_req_kanban_TR4_TR4A_SR.columns.droplevel(0)
pt_req_kanban_TR4_TR4A_SR = pt_req_kanban_TR4_TR4A_SR.fillna(0)
if '未通过' in pt_req_kanban_TR4_TR4A_SR.columns:
pt_req_kanban_TR4_TR4A_SR = pt_req_kanban_TR4_TR4A_SR.sort_values(by=['未通过'], ascending=False)
if ('已通过' in pt_req_kanban_TR4_TR4A_SR.columns) and ('不涉及' in pt_req_kanban_TR4_TR4A_SR.columns):
pt_req_kanban_TR4_TR4A_SR['SR关闭率'] = (pt_req_kanban_TR4_TR4A_SR['已通过'] + pt_req_kanban_TR4_TR4A_SR['不涉及']) \
/ pt_req_kanban_TR4_TR4A_SR['All']
pt_req_kanban_TR4_TR4A_SR['SR关闭率'] = pt_req_kanban_TR4_TR4A_SR['SR关闭率'].astype(float).apply('{:.0%}'.format)
# ------------------End of SR --------------------
# ============================= TR5 ==========================================
df_req_TR5 = df_req_all[df_req_all['TASK客户交付节点(TASK node)'].isin(['TR5'])]
print('df_req_TR5.shape', df_req_TR5.shape)
if len(df_req_TR5) > 0:
pt_req_kanban_TR5_group = pd.pivot_table(df_req_TR5, index=['领域', '领域主管'], columns=['Task看板状态(Task KANBEN status)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True)
# print('pt_req_kanban_TR5_group', pt_req_kanban_TR5_group.head(3))
# print('pt_req_kanban_TR5_group.columns', pt_req_kanban_TR5_group.columns)
pt_req_kanban_TR5_group.columns = pt_req_kanban_TR5_group.columns.droplevel(0)
pt_req_kanban_TR5_group.columns = pt_req_kanban_TR5_group.columns.droplevel(0)
# print('pt_req_kanban_TR5_group.columns', pt_req_kanban_TR5_group.columns)
pt_req_kanban_TR5_group = pt_req_kanban_TR5_group.fillna(0)
pt_req_kanban_TR5_group.rename(columns={'All':'累计AR',}, inplace=True)
req_kanban_case(pt_req_kanban_TR5_group)
pt_req_kanban_TR5_group['未完成'] = pt_req_kanban_TR5_group['累计AR'] - pt_req_kanban_TR5_group['已完成'] - pt_req_kanban_TR5_group['已取消']
pt_req_kanban_TR5_group_order = ['初始化','分析设计','实现','测试验证','未完成','已取消','已完成','累计AR']
pt_req_kanban_TR5_group = pt_req_kanban_TR5_group[pt_req_kanban_TR5_group_order]
pt_req_kanban_TR5_group['完成率'] = (1-(pt_req_kanban_TR5_group['未完成'].astype(float) / pt_req_kanban_TR5_group['累计AR'].astype(float))) \
.apply('{:.0%}'.format)
# pt_req_kanban_TR5_group.sort_values(by='未完成', ascending=False, inplace=True)
pt_req_kanban_TR5_group.sort_values(by=['未完成','领域'], ascending=[False,True], inplace=True)
# ====================== End of TR5 ========================================
# ------------------Start of SR --------------------
print('df_req_TR5.shape------', df_req_TR5.shape)
if len(df_req_TR5) > 0:
df_req_TR5['SRS转测结果(SRS Test Result)'] = df_req_TR5['SRS转测结果(SRS Test Result)'].fillna('空白')
pt_req_kanban_TR5_SR = pd.pivot_table(df_req_TR5, index=['领域', '领域主管'], columns=['SRS转测结果(SRS Test Result)'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True)
pt_req_kanban_TR5_SR.columns = pt_req_kanban_TR5_SR.columns.droplevel(0)
pt_req_kanban_TR5_SR.columns = pt_req_kanban_TR5_SR.columns.droplevel(0)
pt_req_kanban_TR5_SR = pt_req_kanban_TR5_SR.fillna(0)
if '未通过' in pt_req_kanban_TR5_SR.columns:
pt_req_kanban_TR5_SR = pt_req_kanban_TR5_SR.sort_values(by=['未通过'], ascending=False)
if '已通过' in pt_req_kanban_TR5_SR.columns:
if '不涉及' in pt_req_kanban_TR5_SR.columns:
pt_req_kanban_TR5_SR['SR关闭率'] = (pt_req_kanban_TR5_SR['已通过'] + pt_req_kanban_TR5_SR['不涉及']) \
/ pt_req_kanban_TR5_SR['All']
else:
pt_req_kanban_TR5_SR['SR关闭率'] = pt_req_kanban_TR5_SR['已通过'] / pt_req_kanban_TR5_SR['All']
pt_req_kanban_TR5_SR['SR关闭率'] = pt_req_kanban_TR5_SR['SR关闭率'].astype(float).apply('{:.0%}'.format)
pt_req_kanban_TR5_group.to_excel(writer, sheet_name='TR5', index=True)
pt_req_kanban_TR5_SR.to_excel(writer, sheet_name='TR5_SR', index=True)
# ------------------End of SR --------------------
print('-----------End of req--------------------')
# tk.messagebox.showinfo(title='提示',message='End of req')
# End Req-------------------------------------------------------------
df_bug_all.loc[:, 'start'] = datetime.date.today() + datetime.timedelta(days=d_timedelta)
df_bug_all.loc[:, 'end'] = datetime.date.today() + datetime.timedelta(days=d_timedelta+1)
# pd.to_datetime()
case_bug_time = ['BUG更新时间','发现时间','缺陷指派时间','缺陷分析时间','缺陷修改时间','修改审核时间','缺陷归档时间','回归验证时间']
for i in case_bug_time:
df_bug_all.loc[:, i] = pd.to_datetime(df_bug_all.loc[:, i])
# BUG更新时间
for index in df_bug_all.index:
if (df_bug_all.loc[index, 'BUG更新时间'] >= df_bug_all.loc[index, 'start']) \
& (df_bug_all.loc[index, 'BUG更新时间'] < df_bug_all.loc[index, 'end']):
if df_bug_all.loc[index, 'BUG状态'] in (['待分析']):
df_bug_all.loc[index, '当天更新'] = '当天指派'
elif (df_bug_all.loc[index, 'BUG状态'] in (['待修改','待修改审核','待归档','待CCB'])) \
and (df_bug_all.loc[index, '缺陷指派时间'] >= df_bug_all.loc[index, 'start']):
df_bug_all.loc[index, '当天更新'] = '当天指派'
elif df_bug_all.loc[index, 'BUG状态'] in (['待回归验证','挂起']):
df_bug_all.loc[index, '当天更新'] = '当天解单'
elif (df_bug_all.loc[index, 'BUG状态'] in (['已关闭','废弃'])) \
and ((df_bug_all.loc[index, '修改审核时间'] >= df_bug_all.loc[index, 'start']) \
or (df_bug_all.loc[index, '缺陷归档时间'] >= df_bug_all.loc[index, 'start'])):
df_bug_all.loc[index, '当天更新'] = '当天解单'
else:
df_bug_all.loc[index, '当天更新'] = '否'
# --------------------------bug预处理数据表-----------------------------------
# 解单：包括bug修改、CCB、挂起、关闭、等
for index in df_bug_all.index:
df_bug_all.loc[index, '解单人'] = df_bug_all.loc[index, '缺陷修改责任人']
if pd.isna(df_bug_all.loc[index, '解单人']):
df_bug_all.loc[index, '解单人'] = df_bug_all.loc[index, '缺陷分析责任人']
# print('index now is 缺陷分析责任人: ',index)
if pd.isna(df_bug_all.loc[index, '解单人']):
df_bug_all.loc[index, '解单人'] = df_bug_all.loc[index, '缺陷指派责任人']
# print('index now is 缺陷指派责任人: ',index)
for index in df_bug_all.index:
if df_bug_all.loc[index, 'BUG状态'] in (['待归档','待回归验证','已关闭','已废弃','挂起',]):
# if df_bug_all.loc[index, 'BUG状态'] in (['待提交','待指派','待归档','待回归验证','已关闭','已废弃','挂起',]):
# df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷归档时间']
# 因归档时间需要等待版本发布，应用要求，解单时间不按归档时间，按修改审核时间
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, 'BUG状态'] == '已关闭':
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '实际关闭时间']
elif df_bug_all.loc[index, 'BUG状态'] == '已废弃':
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, 'BUG更新时间']
elif df_bug_all.loc[index, 'BUG状态'] == '挂起':
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, 'CCB时间']
elif df_bug_all.loc[index, 'BUG状态'] == '待回归验证':
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷归档时间']
elif df_bug_all.loc[index, 'BUG状态'] == '待归档':
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷归档时间']
if pd.isnull(df_bug_all.loc[index, '解单时间']):
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '修改审核时间']
if pd.isnull(df_bug_all.loc[index, '解单时间']):
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷修改时间']
if pd.isnull(df_bug_all.loc[index, '解单时间']):
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷分析时间']
if pd.isnull(df_bug_all.loc[index, '解单时间']):
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷指派时间']
# elif df_bug_all.loc[index, 'BUG状态'] in (['待提交','待指派',]):
# df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷归档时间']
# if pd.isnull(df_bug_all.loc[index, '解单时间']):
# df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '修改审核时间']
# if pd.isnull(df_bug_all.loc[index, '解单时间']):
# df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷修改时间']
# if pd.isnull(df_bug_all.loc[index, '解单时间']):
# df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷分析时间']
# if pd.isnull(df_bug_all.loc[index, '解单时间']):
# df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, '缺陷指派时间']
for index in df_bug_all.index:
if df_bug_all.loc[index, 'BUG状态'] in (['挂起',]):
df_bug_all.loc[index, '解单时间'] = df_bug_all.loc[index, 'CCB时间']
df_bug_all['解单时间'] = pd.to_datetime(df_bug_all['解单时间'])
# print('abcd--------------------------------------------',)
# print('缺陷归档', a)
# print('已关闭', b)
# print('已废弃', c)
# print('挂起', d)
# # 拆分“当前责任人”列的字符串“姓名 工号”，并替换为只有“姓名”
# name_number = pd.DataFrame()
# name_number[["姓名","工号"]] = df_bug_all['当前责任人(中文)'].astype(str).apply(lambda x: pd.Series([i for i in x.split()]))
# df_bug_all['当前责任人(中文)'] = name_number['姓名']
# df_bug_all = df_bug_all.rename(columns={'当前责任人(中文)':'当前责任人'})
df_bug_part = ['发现人(中文)','当前责任人(中文)','缺陷指派责任人','缺陷分析责任人','缺陷修改责任人','修改审核责任人','缺陷归档责任人','回归验证责任人','解单人','CCB责任人']
# for p in df_bug_part:
# df_bug_all[p] = df_bug_all[p].fillna('空缺-'+p)
for p in df_bug_part:
df_bug_all[p] = df_bug_all[p].astype(str).apply(lambda x: x.split()[0])
df_bug_all = df_bug_all.rename(columns={'当前责任人(中文)':'当前责任人'})
print('-------------------------------Begin of bug 预处理！ -------------------------------')
df_bug_all = pd.merge(df_bug_all, df_vl.loc[:, ['人员', '领域', '领域主管', '合作方']], \
how='left', left_on='解单人', right_on='人员',sort=False, suffixes=('','_y'))
df_bug_all = pd.merge(df_bug_all, df_vl.loc[:, ['人员', '领域', '领域主管', '合作方']], \
how='left', left_on='当前责任人', right_on='人员',sort=False, suffixes=('','_当前'))
df_bug_all = pd.merge(df_bug_all, df_vl.loc[:, ['人员', '领域', '领域主管', '合作方']], \
how='left', left_on='缺陷分析责任人', right_on='人员',sort=False, suffixes=('','_分析'))
df_bug_all = pd.merge(df_bug_all, df_vl.loc[:, ['人员', '领域', '领域主管', '合作方']], \
how='left', left_on='缺陷修改责任人', right_on='人员',sort=False, suffixes=('','_修改'))
df_bug_all = pd.merge(df_bug_all, df_vl.loc[:, ['人员', '领域', '领域主管', '合作方']], \
how='left', left_on='修改审核责任人', right_on='人员',sort=False, suffixes=('','_审核'))
df_bug_all = pd.merge(df_bug_all, df_vl.loc[:, ['人员', '领域', '领域主管', '合作方']], \
how='left', left_on='CCB责任人', right_on='人员',sort=False, suffixes=('','_CCB'))
df_bug_all = pd.merge(df_bug_all, df_vl.loc[:, ['人员', '领域', '领域主管', '合作方']], \
how='left', left_on='发现人(中文)', right_on='人员',sort=False, suffixes=('','_发现'))
print('df_bug_all ---', df_bug_all.shape)
df_bug_all.drop_duplicates(subset=['BUG编号',],keep='first',inplace=True)
print('df_bug_all --- drop_duplicates', df_bug_all.shape)
# 删除匹配不到的责任人数据，=====================================
df_bug_all.dropna(subset=['当前责任人',], axis=0, how='any', inplace = True)
print('df_bug_all.shape dropna Tester', df_bug_all.shape)
df_bug_all.drop(df_bug_all[df_bug_all['解单人'] == '李浩'].index, inplace=True)
print('df_bug_all.shape dropna 李浩', df_bug_all.shape)
df_bug_all.drop(df_bug_all[df_bug_all['解单人'] == '罗红娟'].index, inplace=True)
print('df_bug_all.shape dropna 罗红娟', df_bug_all.shape)
df_bug_all.drop(df_bug_all[df_bug_all['当前责任人'] == '华捷'].index, inplace=True)
print('df_bug_all.shape dropna 华捷', df_bug_all.shape)
df_bug_all.drop(df_bug_all[df_bug_all['解单人'] == '邓军稳'].index, inplace=True)
print('df_bug_all.shape dropna 邓军稳', df_bug_all.shape)
df_bug_all['人员'] = df_bug_all['人员'].fillna('解单人查无此人')
df_bug_all['领域'] = df_bug_all['领域'].fillna('解单人未匹配到领域')
df_bug_all['领域主管'] = df_bug_all['领域主管'].fillna('解单人未匹配到领域主管')
df_bug_all['合作方'] = df_bug_all['合作方'].fillna('解单人未匹配到合作方')
df_bug_all['人员_当前'] = df_bug_all['人员_当前'].fillna('当前责任人查无此人')
df_bug_all['领域_当前'] = df_bug_all['领域_当前'].fillna('当前责任人未匹配领域')
df_bug_all['领域主管_当前'] = df_bug_all['领域主管_当前'].fillna('当前责任人未匹配领域主管')
df_bug_all['合作方_当前'] = df_bug_all['合作方_当前'].fillna('当前责任人未匹配合作方')
# df_bug_all.drop(df_bug_all[df_bug_all['BUG编号'].isin(['TS-BUG-75464','TS-BUG-75462','TS-BUG-75423','TS-BUG-75255','TS-BUG-74690',])].index, inplace=True)
# print('df_bug_all.shape dropna 电信5个', df_bug_all.shape)
# liantong_zhunru_test = ['TS-BUG-81762',
# 'TS-BUG-81801',
# 'TS-BUG-81802',
# 'TS-BUG-81804',
# 'TS-BUG-81807',
# 'TS-BUG-81809',
# 'TS-BUG-81810',
# 'TS-BUG-81811',
# 'TS-BUG-80521',
# 'TS-BUG-80522',
# 'TS-BUG-80523',
# 'TS-BUG-80525',
# 'TS-BUG-80527',
# 'TS-BUG-80530',
# 'TS-BUG-81140',
# 'TS-BUG-81142',
# 'TS-BUG-81151',
# 'TS-BUG-81152',
# ]
# for i in liantong_zhunru_test:
# df_bug_all.drop(df_bug_all[df_bug_all['BUG编号'] == i].index, inplace=True)
# print('df_bug_all.shape drop 联通入场18个单', df_bug_all.shape)
# df_bug_all.drop(df_bug_all[df_bug_all['领域'] == '工程工具'].index, inplace=True)
# print('df_bug_all.shape dropna 刘琰玺', df_bug_all.shape)
bug_2gong = [
'TS-BUG-92744',
'TS-BUG-92858',
'TS-BUG-92953',
'TS-BUG-92954',
'TS-BUG-92957',
'TS-BUG-92959',
]
for i in bug_2gong:
df_bug_all.drop(df_bug_all[df_bug_all['BUG编号'] == i].index, inplace=True)
print('df_bug_all.shape drop 多媒体二供单', df_bug_all.shape)
# 4350 删掉一个错误单
df_bug_all.drop(df_bug_all[df_bug_all['BUG编号'] == 'TS-BUG-65157'].index, inplace=True)
print('final df_bug_all.shape---------------', df_bug_all.shape)
df_bug_all = df_bug_all.set_index('解单时间',drop=False,)
df_bug_all['Week'] = df_bug_all.to_period('W').index
df_bug_all['Day'] = df_bug_all.to_period('D').index
df_bug_all = df_bug_all.reset_index(drop=True)
df_bug_all.loc[:, '已解单开发环节时长'] = df_bug_all.loc[:, '解单时间'] - df_bug_all.loc[:, '缺陷指派时间']
# 开发未解单 滞留时长
df_bug_all.loc[:, '当前时间'] = datetime.datetime.today()
df_bug_all.loc[:, '缺陷指派时间'] = pd.to_datetime(df_bug_all.loc[:, '缺陷指派时间'])
for index in df_bug_all.index:
# if df_bug_all.loc[index,'BUG状态'] in ['待分析','待修改','待修改审核','待归档','待CCB']:
# 待归档不计入开发环节单
if df_bug_all.loc[index,'BUG状态'] in bug_status_dev:
df_bug_all.loc[index, '待解单开发滞留时长'] = df_bug_all.loc[index, '当前时间'] - df_bug_all.loc[index, '缺陷指派时间']
df_bug_all.loc[:, '待解单开发滞留时长'] = df_bug_all.loc[:, '待解单开发滞留时长'].apply(lambda x:x.days)
# ============================计算超期=============================
for index in df_bug_all.index:
if df_bug_all.loc[index,'严重程度'] in ['Blocker','Critical','Major']:
if df_bug_all.loc[index, '待解单开发滞留时长'] < 7: #2
df_bug_all.loc[index, '待解单是否超期'] = '否'
elif df_bug_all.loc[index, '待解单开发滞留时长'] >= 7:
df_bug_all.loc[index, '待解单是否超期'] = '是'
else:
df_bug_all.loc[index, '待解单是否超期'] = '已解'
if df_bug_all.loc[index,'严重程度'] == ('Normal'):
if df_bug_all.loc[index, '待解单开发滞留时长'] < 7: #4
df_bug_all.loc[index, '待解单是否超期'] = '否'
elif df_bug_all.loc[index, '待解单开发滞留时长'] >= 7:
df_bug_all.loc[index, '待解单是否超期'] = '是'
else:
df_bug_all.loc[index, '待解单是否超期'] = '已解'
if df_bug_all.loc[index,'严重程度'] == ('Minor'):
if df_bug_all.loc[index, '待解单开发滞留时长'] < 7: #5
df_bug_all.loc[index, '待解单是否超期'] = '否'
elif df_bug_all.loc[index, '待解单开发滞留时长'] >= 7:
df_bug_all.loc[index, '待解单是否超期'] = '是'
else:
df_bug_all.loc[index, '待解单是否超期'] = '已解'
# # # TR4A剔除，筛选“二供”问题单
# df_1g =df_bug_all[df_bug_all['BUG标题'].astype(str).str.contains('一供')]
# print('df_1g.shape---------------df_1g分支', df_1g.shape)
# df_1and2g =df_1g[df_1g['BUG标题'].astype(str).str.contains('二供')]
# print('df_1and2g.shape---------------df_1and2g', df_1and2g.shape)
# print('df_bug_all.shape---------------', df_bug_all.shape)
# df_bug_all = df_bug_all[~df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('二供')]
# print('final df_bug_all.shape---------------no 二供', df_bug_all.shape)
# df_bug_all = df_bug_all.append(df_1and2g)
# print('final df_bug_all.shape---------------增加同时包含 一供和二供', df_bug_all.shape)
# df_bug_all = df_bug_all[~df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('power分支')]
# print('final df_bug_all.shape---------------no power分支', df_bug_all.shape)
# df_bug_all = df_bug_all[~df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('联通准入')]
# print('final df_bug_all.shape---------------no 联通准入', df_bug_all.shape)
# df_bug_all = df_bug_all[~df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('进网认证测试')]
# print('final df_bug_all.shape---------------no 进网认证测试', df_bug_all.shape)
# df_bug_all = df_bug_all[~df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('外场测试')]
# print('final df_bug_all.shape---------------no 外场测试', df_bug_all.shape)
# df_bug_all = df_bug_all[~df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('BETA')]
# print('final df_bug_all.shape---------------no BETA', df_bug_all.shape)
# df_bug_all = df_bug_all[~df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('beta')]
# print('final df_bug_all.shape---------------no beta', df_bug_all.shape)
# df_bug_bigdata = df_bug_all[df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('【大数据】')]
# print('df_bug_bigdata.shape---------------【大数据】', df_bug_bigdata.shape)
# ZN
print('df_bug_all.shape---------------', df_bug_all.shape)
# df_bug_all = df_bug_all[~df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('TRP')]
df_bug_all = df_bug_all[~df_bug_all['实际测试版本'].astype(str).str[:1000].str.contains('TRP')]
print('df_bug_all.shape---------------no TRP', df_bug_all.shape)
df_bug_all = df_bug_all[~df_bug_all['BUG标题'].astype(str).str[:1000].str.contains('电信入库正测首轮')]
print('final df_bug_all.shape---------------no beta', df_bug_all.shape)
df_bug_all.drop_duplicates(subset=['BUG编号',],keep='first',inplace=True)
print('df_bug_open --- drop_duplicates df_bug_all', df_bug_all.shape)
print('--------------------------End of bug 预处理！------------------------------------------------')
# tk.messagebox.showinfo(title='提示',message='End of bug 预处理！')
# 依赖
df_bug_tag = df_bug_all[df_bug_all['标签'].astype(str).str[:4].str.contains('依赖')]
df_bug_tag_dev_open = df_bug_tag[df_bug_tag['BUG状态'].isin(['待分析','待分析审核','待修改','待修改审核','待CCB'])]
# gp_bug_tag = df_bug_tag_dev_open[['领域','解单人','BUG状态','BUG编号']].groupby(['领域','解单人','BUG状态']).count()
# gp_bug_tag.rename(columns={'BUG编号':'数量',}, inplace=True)
# pivot group dev
if len(df_bug_tag_dev_open) > 0:
pt_bug_tag_dev_group = pd.pivot_table(df_bug_tag_dev_open, index=['领域_当前', '领域主管_当前'], columns=['BUG状态'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_tag_dev_group.columns = pt_bug_tag_dev_group.columns.droplevel(0)
pt_bug_tag_dev_group.columns = pt_bug_tag_dev_group.columns.droplevel(0)
pt_bug_tag_dev_group = pt_bug_tag_dev_group.sort_values(by=['All'], ascending=[False])
# pt_bug_tag_dev_group['未解依赖单-开发环节']
pt_bug_tag_dev_DI_group = pd.pivot_table(df_bug_tag_dev_open, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_tag_dev_DI_group)
pt_bug_tag_dev_DI_group = pt_bug_tag_dev_DI_group.sort_values(by=['DI'], ascending=[False])
pt_bug_tag_dev_man = pd.pivot_table(df_bug_tag_dev_open, index=['领域_当前', '人员_当前'], columns=['BUG状态'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_tag_dev_man.columns = pt_bug_tag_dev_man.columns.droplevel(0)
pt_bug_tag_dev_man.columns = pt_bug_tag_dev_man.columns.droplevel(0)
pt_bug_tag_dev_man = pt_bug_tag_dev_man.sort_values(by=['领域_当前','All'], ascending=[True, False])
# pivot group all
if len(df_bug_tag) > 0 :
pt_bug_tag = pd.pivot_table(df_bug_tag, index=['领域_当前', '领域主管_当前'], columns=['BUG状态'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_tag.columns = pt_bug_tag.columns.droplevel(0)
pt_bug_tag.columns = pt_bug_tag.columns.droplevel(0)
pt_bug_tag = pt_bug_tag.sort_values(by=['All'], ascending=[False])
df_bug_tag_stay_all = df_bug_tag.copy(deep=True)
pt_bug_tag_stay_all = stay_time(df_bug_tag_stay_all)
df_bug_tag_stay_dev = df_bug_tag_dev_open.copy(deep=True)
pt_bug_tag_stay_dev = stay_time(df_bug_tag_stay_dev)
if len(df_bug_tag_dev_open) > 0:
pt_bug_tag_stay_dev = pd.merge(pt_bug_tag_stay_dev, pt_bug_tag_dev_DI_group, \
how='left', left_on=['领域_当前', '领域主管_当前'], right_on=['领域_当前', '领域主管_当前'])
# ---------------------------start 工时投入-------------------------------
# 筛选时间7月25号
date_gongshi = pd.to_datetime('2021-08-12')
df_bug_efficiency = df_bug_all[df_bug_all['缺陷指派时间'] >= date_gongshi]
df_bug_efficiency = df_bug_all.copy(deep=True)
print('df_bug_efficiency', df_bug_efficiency.shape)
# 当前责任人
pt_bug_cur = pd.pivot_table(df_bug_efficiency, index=['领域_当前', '人员_当前'], columns=['BUG状态'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_cur.columns = pt_bug_cur.columns.droplevel(0)
pt_bug_cur.columns = pt_bug_cur.columns.droplevel(0)
pt_bug_cur = pt_bug_cur.sort_values(by=['领域_当前','All'], ascending=[True, False])
# 缺陷分析责任人
pt_bug_ana = pd.pivot_table(df_bug_efficiency, index=['领域_分析', '人员_分析'], columns=['BUG状态'], \
values=['缺陷分析责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_ana.columns = pt_bug_ana.columns.droplevel(0)
pt_bug_ana.columns = pt_bug_ana.columns.droplevel(0)
pt_bug_ana = pt_bug_ana.sort_values(by=['领域_分析','All'], ascending=[True, False])
pt_bug_ana = pt_bug_ana.reset_index()
# 缺陷修改责任人
pt_bug_mod = pd.pivot_table(df_bug_efficiency, index=['领域_修改', '人员_修改'], columns=['BUG状态'], \
values=['缺陷修改责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_mod.columns = pt_bug_mod.columns.droplevel(0)
pt_bug_mod.columns = pt_bug_mod.columns.droplevel(0)
pt_bug_mod = pt_bug_mod.sort_values(by=['领域_修改','All'], ascending=[True, False])
pt_bug_mod = pt_bug_mod.reset_index()
# 修改审核责任人
pt_bug_check = pd.pivot_table(df_bug_efficiency, index=['领域_审核', '人员_审核'], columns=['BUG状态'], \
values=['修改审核责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_check.columns = pt_bug_check.columns.droplevel(0)
pt_bug_check.columns = pt_bug_check.columns.droplevel(0)
pt_bug_check = pt_bug_check.sort_values(by=['领域_审核','All'], ascending=[True, False])
pt_bug_check = pt_bug_check.reset_index()
# CCB责任人
print('df_bug_efficiency---1',df_bug_efficiency.shape)
pt_bug_CCB = pd.pivot_table(df_bug_efficiency, index=['领域_CCB', '人员_CCB'], columns=['BUG状态'], \
values=['CCB责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
print(pt_bug_CCB)
# pt_bug_CCB = pd.pivot_table(df_bug_efficiency, index=['领域_CCB', '人员_CCB'], columns=['BUG状态'], \
# values=['CCB责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
print('df_bug_efficiency---2',df_bug_efficiency.shape)
if len(pt_bug_CCB)>0:
pt_bug_CCB.columns = pt_bug_CCB.columns.droplevel(0)
pt_bug_CCB.columns = pt_bug_CCB.columns.droplevel(0)
if 'All' in pt_bug_CCB.columns:
pt_bug_CCB = pt_bug_CCB.sort_values(by=['领域_CCB','All'], ascending=[True, False])
print('--------All-------------------------1')
else:
pt_bug_CCB = pt_bug_CCB.sort_values(by=['领域_CCB',], ascending=[True])
print('--------All-------------------------2')
pt_bug_CCB = pt_bug_CCB.reset_index()
# df_bug_efficiency_gp = df_bug_efficiency.groupby(['领域',])[['待解单开发滞留时长',]].mean()
# pt_open_dev_Major = pd.merge(pt_open_dev_Major, df_bug_efficiency_gp, how='outer', left_index=True, right_index=True)\
# .fillna(df_open_dev_Major['待解单开发滞留时长'].mean())
# pt_open_dev_Major['待解单开发滞留时长'] = pt_open_dev_Major['待解单开发滞留时长'].apply('{:.2f}'.format).astype(float)
# ---------------------------end 工时投入-------------------------------
# ----------------bug merge 当前责任人--------------------
print('-------------------------------begin of df_bug_open -------------------------------')
df_bug_open = df_bug_all.copy(deep=True)
df_bug_open_current = df_bug_open[df_bug_open['BUG状态'].isin(['待提交','待指派','待回归验证','待分析','待分析审核','待修改','待修改审核','待归档','待CCB'])]
# group------------------------------------------------------------
pt_bug_open_current_group = pd.pivot_table(df_bug_open_current, index=['领域_当前', '领域主管_当前'], columns=['BUG状态'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_open_current_group.columns', pt_bug_open_current_group.columns)
pt_bug_open_current_group.columns = pt_bug_open_current_group.columns.droplevel(0)
pt_bug_open_current_group.columns = pt_bug_open_current_group.columns.droplevel(0)
# print('pt_bug_open_current_group.columns', pt_bug_open_current_group.columns)
pt_bug_open_current_group.rename(columns={'All':'全部状态',}, inplace=True)
bug_case(pt_bug_open_current_group)
pt_bug_open_current_group = pt_bug_open_current_group[bug_status]
# ==================== Start of Open DI ===================
# df_bug_open_test = df_bug_open[df_bug_open['BUG状态'].isin(['待提交','待指派','待回归验证',])]
pt_bug_open_current_DI_group = pd.pivot_table(df_bug_open_current, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_current_DI_group)
# ==================== End of Open DI ===================
print('-------------------------------end of df_bug_open DI -------------------------------')
# 合并透视表 pt_bug_open_current_group_status_DI
pt_bug_open_current_group_status_DI = pt_bug_open_current_group.copy(deep=True)
# pt_bug_open_current_group_status_DI['关单汇总'] = pt_bug_open_current_group_status_DI['挂起'] \
# + pt_bug_open_current_group_status_DI['已关闭'] + pt_bug_open_current_group_status_DI['已废弃']
# pt_bug_open_current_group_status_DI['关单率'] = pt_bug_open_current_group_status_DI['关单汇总'] / pt_bug_open_current_group_status_DI['全部状态']
# pt_bug_open_current_group_status_DI['关单率'] = pt_bug_open_current_group_status_DI['关单率'].astype(float).apply('{:.0%}'.format)
pt_bug_open_current_group_status_DI.index.names = ['领域', '领域主管']
if len(pt_bug_open_current_DI_group) > 0:
pt_bug_open_current_DI_group.index.names = ['领域', '领域主管']
pt_bug_open_current_group_status_DI = pd.merge(pt_bug_open_current_group_status_DI, pt_bug_open_current_DI_group, \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
pt_bug_open_current_group_status_DI = pt_bug_open_current_group_status_DI.sort_values(by=['DI'], ascending=[False])
if 'Blocker' in pt_bug_open_current_group_status_DI.columns:
if pt_bug_open_current_group_status_DI['Blocker'].sum() == 0:
pt_bug_open_current_group_status_DI = pt_bug_open_current_group_status_DI.drop(columns='Blocker')
if 'Critical' in pt_bug_open_current_group_status_DI.columns:
if pt_bug_open_current_group_status_DI['Critical'].sum() == 0:
pt_bug_open_current_group_status_DI = pt_bug_open_current_group_status_DI.drop(columns='Critical')
# ----------------bug merge 缺陷修改责任人--------------------
# df_bug_percent = pd.merge(df_bug_all, df_vl.loc[:, ['人员', '领域', '领域主管']], \
# how='left', left_on='缺陷修改责任人', right_on='人员')
# df_bug_percent.drop_duplicates(subset=['BUG编号',],keep='first',inplace=True)
# print('drop_duplicates --- df_bug_percent ', df_bug_percent.shape)
df_bug_percent = df_bug_all.copy(deep=True)
df_bug_percent_nobody = df_bug_percent[df_bug_percent['人员'].isnull()]
df_bug_percent_nobody_gp = df_bug_percent_nobody.groupby(['解单人',])[['解单人','BUG编号',]].count()
df_bug_percent_nobody_gp.rename(columns={'BUG编号':'BUG数量',}, inplace=True)
df_bug_percent_nobody_cur = df_bug_percent[df_bug_percent['人员_当前'].isnull()]
df_bug_percent_nobody_cur_gp = df_bug_percent_nobody_cur.groupby(['当前责任人',])[['当前责任人','BUG编号',]].count()
df_bug_percent_nobody_cur_gp.rename(columns={'BUG编号':'BUG数量',}, inplace=True)
# df_bug_percent['人员'] = df_bug_percent['人员'].fillna('不在名单')
# df_bug_percent['领域'] = df_bug_percent['领域'].fillna('未知领域')
# df_bug_percent['领域主管'] = df_bug_percent['领域主管'].fillna('未知主管')
# print('df_bug_percent.shape ', df_bug_percent.shape)
# df_bug_percent_all = df_bug_percent.copy(deep=True)
# 删除测试
# df_bug_percent.drop(df_bug_percent[df_bug_percent['领域'] == '测试'].index, inplace=True)
# print('df_bug_percent.shape --- 删除测试', df_bug_percent.shape)
# print('end of bug percent -------------------------------')
# print('end of bug percent 预处理-------------------------------')
# bug daily line ----------------------------------
# df_line = df_bug_all.copy(deep=True)
# 每日处理单，排除开发驳回测试的单子
df_line = df_bug_all[~df_bug_all['BUG状态'].isin(['待提交','待指派',])]
# ttt = df_line['BUG状态'].groupby(df_line['BUG状态']).count()
print('df_line.shape---------------------',df_line.shape)
# ddd = pd.pivot_table(df_line, index=['BUG状态'], columns=['严重程度'], \
# values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('df_new---------------------',ddd)
# df_line.drop(df_line[df_line['领域'] == '测试'].index, inplace=True)
print('df_line---------------------',df_line['缺陷指派时间'].count())
df_line['缺陷指派时间'] = pd.to_datetime(df_line['缺陷指派时间']).dt.normalize()
df_new = df_line['缺陷指派时间'].groupby(df_line['缺陷指派时间']).count()
# print('df_new---------------------',df_new)
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()
print('df_line---------------------',df_line['解单时间'].count())
print('df_line---------------------',df_line['解单时间'].shape)
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).fillna(method='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).fillna(method='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[['发现时间','严重程度','BUG编号']].groupby(['发现时间','严重程度']).count()
df_di_bug = df_line[['缺陷指派时间','严重程度','BUG编号']].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[['解单时间','严重程度','BUG编号']].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).fillna(method='ffill')
df_di_debug_cumsum = (df_dd + df_di_debug_cumsum).fillna(method='ffill')
df_di_debug_cumsum = df_di_debug_cumsum.fillna(0)
di = (df_di_bug_cumsum - df_di_debug_cumsum).fillna(method='ffill')
# df_daily_all_DI = pd.merge(df_daily_all, di, how='outer', left_index=True, right_index=True).fillna(0)
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 = '日期'
# group------------------------------------------------------------
pt_bug_percent_group = pd.pivot_table(df_bug_percent, index=['领域', '领域主管'], columns=['BUG状态'], \
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(pt_bug_percent_group)
pt_bug_percent_group = pt_bug_percent_group[bug_status]
#关单率
pt_bug_percent_group['关单汇总'] = pt_bug_percent_group['挂起'] \
+ pt_bug_percent_group['已关闭'] + pt_bug_percent_group['已废弃']
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['待分析'] \
+ pt_bug_percent_group['待修改'] + pt_bug_percent_group['待修改审核'] \
+ pt_bug_percent_group['待CCB']
pt_bug_percent_group['累计解单'] = pt_bug_percent_group['待归档'] + pt_bug_percent_group['挂起']\
+ pt_bug_percent_group['待回归验证'] + pt_bug_percent_group['已关闭'] + pt_bug_percent_group['已废弃']
# 百分比排序，先设置为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)
# 将汇总All行切换到最后一行
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)
# 效率
# 起始时间：当前时间往前 14 天
# interval = (datetime.datetime.now()-df_bug_percent['解单时间'][0]).days
# interval = 14
# interval = 30
interval = 7
# interval = 365
select_date_start = datetime.date.today() + datetime.timedelta(days=-interval)
select_datetime_start = datetime.datetime.combine(select_date_start, datetime.datetime.min.time())
print('select_date_start------', select_date_start)
print('select_datetime_start------', select_datetime_start)
# 统计度量周期内，已解问题单
df_bug_percent_solved = df_bug_all[df_bug_all['BUG状态'].isin(['待归档','待回归验证','已关闭','已废弃','挂起',])]
df_bug_percent_solved_select_datetime_start_end = df_bug_percent_solved[df_bug_percent_solved['解单时间'] > select_datetime_start]
# df_bug_percent_solved_select_datetime_start_end = df_bug_percent_solved[df_bug_percent_solved['解单时间'] < select_datetime_end]
if len(df_bug_percent_solved_select_datetime_start_end) > 0:
pt_bug_percent_man_interval = pd.pivot_table(df_bug_percent_solved_select_datetime_start_end, index=['领域', '人员'], columns=['BUG状态'], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_percent_man_interval.columns = pt_bug_percent_man_interval.columns.droplevel(0)
pt_bug_percent_man_interval.columns = pt_bug_percent_man_interval.columns.droplevel(0)
gb_bug_percent_group_size = df_bug_percent_solved_select_datetime_start_end.groupby(['领域',])['解单人'].count()
gb_bug_percent_group_size.name = '解单个数'
pt_bug_percent_group_interval = pd.pivot_table(df_bug_percent_solved_select_datetime_start_end, index=['领域', '领域主管'], columns=['BUG状态'], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_percent_group_interval.columns = pt_bug_percent_group_interval.columns.droplevel(0)
pt_bug_percent_group_interval.columns = pt_bug_percent_group_interval.columns.droplevel(0)
# bug_case(pt_bug_percent_group_interval)
pt_bug_percent_group_interval = pd.merge(pt_bug_percent_group_interval, gb_bug_percent_group_size, \
how='left', left_on=['领域',], right_on=['领域',]).fillna(0)
# print('pt_bug_percent_group_interval-----------',pt_bug_percent_group_interval)
pt_bug_percent_group_interval.loc['All','解单个数'] = pt_bug_percent_group_interval['解单个数'].sum()
# print('pt_bug_percent_group_interval-----------',pt_bug_percent_group_interval)
gb_bug_percent_group_nunique = df_bug_percent_solved_select_datetime_start_end.groupby(['领域',])['解单人'].nunique()
gb_bug_percent_group_nunique.name = '周期内参与解单人数'
pt_bug_percent_group_interval = pd.merge(pt_bug_percent_group_interval, gb_bug_percent_group_nunique, \
how='left', left_on=['领域',], right_on=['领域',]).fillna(0)
pt_bug_percent_group_interval.loc['All','周期内参与解单人数'] = pt_bug_percent_group_interval['周期内参与解单人数'].sum()
pt_bug_percent_group_interval['度量周期(天)'] = interval
pt_bug_percent_group_interval['效率(个/人天)'] = ((pt_bug_percent_group_interval['解单个数'] / pt_bug_percent_group_interval['周期内参与解单人数']) / pt_bug_percent_group_interval['度量周期(天)']).astype(float).apply('{:.2f}'.format)
pt_bug_percent_group_interval = pt_bug_percent_group_interval.sort_values(by=['效率(个/人天)'], ascending=[False])
# 将汇总All行切换到最后一行
pt_bug_percent_group_interval_All = pt_bug_percent_group_interval.loc['All',]
pt_bug_percent_group_interval = pt_bug_percent_group_interval.drop(index='All',axis=0, )
pt_bug_percent_group_interval = pt_bug_percent_group_interval.append(pt_bug_percent_group_interval_All)
# 改名
pt_bug_percent_group_interval.rename(columns={'All':'已解单总计',}, inplace=True)
pt_bug_percent_group_interval.rename(index={'All':'汇总/平均值',}, inplace=True)
# 删除
if '测试' in pt_bug_percent_group_interval.index:
pt_bug_percent_group_interval = pt_bug_percent_group_interval.drop(index='测试',axis=0,)
# 全周期
df_test2dev_time = df_bug_all['缺陷指派时间'].fillna(method='bfill')
df_test2dev_time = df_test2dev_time.fillna(method='ffill')
print(df_test2dev_time)
interval_alltime = df_test2dev_time.iloc[-1] - df_test2dev_time.iloc[0]
print('interval_alltime---', interval_alltime)
# interval_alltime = interval_alltime.astype('timedelta64[D]')
# interval_alltime = interval_alltime.str(timedelta).split('.')[0]
print('interval_alltime---', interval_alltime)
interval_alltime = interval_alltime.days
print('interval_alltime---', interval_alltime)
# 统计度量周期内，已解问题单
df_bug_percent_solved = df_bug_all[df_bug_all['BUG状态'].isin(['待归档','待回归验证','已关闭','已废弃','挂起',])]
df_bug_percent_solved_alltime = df_bug_percent_solved
pt_bug_percent_man_interval_alltime = pd.pivot_table(df_bug_percent_solved_alltime, index=['领域', '人员'], columns=['BUG状态'], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_percent_man_interval_alltime.columns = pt_bug_percent_man_interval_alltime.columns.droplevel(0)
pt_bug_percent_man_interval_alltime.columns = pt_bug_percent_man_interval_alltime.columns.droplevel(0)
gb_bug_percent_group_size = df_bug_percent_solved_alltime.groupby(['领域',])['解单人'].count()
gb_bug_percent_group_size.name = '解单个数'
pt_bug_percent_group_interval_alltime = pd.pivot_table(df_bug_percent_solved_alltime, index=['领域', '领域主管'], columns=['BUG状态'], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_percent_group_interval_alltime.columns = pt_bug_percent_group_interval_alltime.columns.droplevel(0)
pt_bug_percent_group_interval_alltime.columns = pt_bug_percent_group_interval_alltime.columns.droplevel(0)
# bug_case(pt_bug_percent_group_interval_alltime)
pt_bug_percent_group_interval_alltime = pd.merge(pt_bug_percent_group_interval_alltime, gb_bug_percent_group_size, \
how='left', left_on=['领域',], right_on=['领域',]).fillna(0)
# print('pt_bug_percent_group_interval_alltime-----------',pt_bug_percent_group_interval_alltime)
pt_bug_percent_group_interval_alltime.loc['All','解单个数'] = pt_bug_percent_group_interval_alltime['解单个数'].sum()
# print('pt_bug_percent_group_interval_alltime-----------',pt_bug_percent_group_interval_alltime)
gb_bug_percent_group_nunique = df_bug_percent_solved_alltime.groupby(['领域',])['解单人'].nunique()
gb_bug_percent_group_nunique.name = '周期内参与解单人数'
pt_bug_percent_group_interval_alltime = pd.merge(pt_bug_percent_group_interval_alltime, gb_bug_percent_group_nunique, \
how='left', left_on=['领域',], right_on=['领域',]).fillna(0)
pt_bug_percent_group_interval_alltime.loc['All','周期内参与解单人数'] = pt_bug_percent_group_interval_alltime['周期内参与解单人数'].sum()
pt_bug_percent_group_interval_alltime['度量起始时间'] = df_test2dev_time.iloc[0]
pt_bug_percent_group_interval_alltime['度量截止时间'] = df_test2dev_time.iloc[-1]
pt_bug_percent_group_interval_alltime['度量周期(天)'] = interval_alltime
pt_bug_percent_group_interval_alltime['效率(个/人天)'] = ((pt_bug_percent_group_interval_alltime['解单个数'] / pt_bug_percent_group_interval_alltime['周期内参与解单人数']) / pt_bug_percent_group_interval_alltime['度量周期(天)']).astype(float).apply('{:.2f}'.format)
pt_bug_percent_group_interval_alltime = pt_bug_percent_group_interval_alltime.sort_values(by=['效率(个/人天)'], ascending=[False])
pt_bug_percent_group_interval_alltime['度量周期(月)'] = interval_alltime/30
pt_bug_percent_group_interval_alltime['效率(个/人月)'] = ((pt_bug_percent_group_interval_alltime['解单个数'] / pt_bug_percent_group_interval_alltime['周期内参与解单人数']) / pt_bug_percent_group_interval_alltime['度量周期(月)']).astype(float).apply('{:.2f}'.format)
pt_bug_percent_group_interval_alltime['度量周期(月)'] = pt_bug_percent_group_interval_alltime['度量周期(月)'].astype(float).apply('{:.2f}'.format)
# 将汇总All行切换到最后一行
pt_bug_percent_group_interval_alltime_All = pt_bug_percent_group_interval_alltime.loc['All',]
pt_bug_percent_group_interval_alltime = pt_bug_percent_group_interval_alltime.drop(index='All',axis=0, )
pt_bug_percent_group_interval_alltime = pt_bug_percent_group_interval_alltime.append(pt_bug_percent_group_interval_alltime_All)
# 改名
pt_bug_percent_group_interval_alltime.rename(columns={'All':'已解单总计',}, inplace=True)
pt_bug_percent_group_interval_alltime.rename(index={'All':'汇总/平均值',}, inplace=True)
# 删除
if '测试' in pt_bug_percent_group_interval_alltime.index:
pt_bug_percent_group_interval_alltime = pt_bug_percent_group_interval_alltime.drop(index='测试',axis=0,)
# TRa-TRb
my_TR = {'TR3' : pd.to_datetime('2021-09-28'),
'TR4': pd.to_datetime('2021-11-26'),
'TR4A' : pd.to_datetime('2022-01-14'),
'TR5' : pd.to_datetime('2022-02-21'),
'TR6' : pd.to_datetime('2022-03-11'),}
def TRab(i,j,TRa, TRb):
Day_TRa_datetime = datetime.datetime.combine(TRa, datetime.datetime.min.time())
Day_TRb_datetime = datetime.datetime.combine(TRb, datetime.datetime.min.time())
print('Day_TRa_datetime------', Day_TRa_datetime)
print('Day_TRb_datetime------', Day_TRb_datetime)
# 统计度量周期内，已解问题单
interval_TRa_TRb = Day_TRb_datetime - Day_TRa_datetime
print('interval_TRa_TRb---', interval_TRa_TRb)
interval_TRa_TRb = interval_TRa_TRb.days
print('interval_TRa_TRb---', interval_TRa_TRb)
interval_TRa_TRb = interval_TRa_TRb/7*6
print('interval_TRa_TRb---', interval_TRa_TRb)
interval_TRa_TRb = round(interval_TRa_TRb, 0)
# interval_TRa_TRb = "{:.2f}".format(interval_TRa_TRb)
# interval_TRa_TRb = "%.3f"%(interval_TRa_TRb)
print('interval_TRa_TRb---', interval_TRa_TRb)
# 统计度量周期内，已解问题单
df_bug_percent_solved_TRa_TRb = df_bug_all[df_bug_all['BUG状态'].isin(['待归档','待回归验证','已关闭','已废弃','挂起',])]
print('df_bug_percent_solved_TRa_TRb.shape------', df_bug_percent_solved_TRa_TRb.shape)
df_bug_percent_solved_TRa_TRb = df_bug_percent_solved_TRa_TRb[df_bug_percent_solved_TRa_TRb['解单时间'] > Day_TRa_datetime]
print('df_bug_percent_solved_TRa_TRb.shape------', df_bug_percent_solved_TRa_TRb.shape)
df_bug_percent_solved_TRa_TRb = df_bug_percent_solved_TRa_TRb[df_bug_percent_solved_TRa_TRb['解单时间'] < Day_TRb_datetime]
print('df_bug_percent_solved_TRa_TRb.shape------', df_bug_percent_solved_TRa_TRb.shape)
if len(df_bug_percent_solved_TRa_TRb)>0:
pt_bug_percent_man_interval_TRa_TRb = pd.pivot_table(df_bug_percent_solved_TRa_TRb, index=['领域', '人员'], columns=['BUG状态'], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_percent_man_interval_TRa_TRb.columns = pt_bug_percent_man_interval_TRa_TRb.columns.droplevel(0)
pt_bug_percent_man_interval_TRa_TRb.columns = pt_bug_percent_man_interval_TRa_TRb.columns.droplevel(0)
gb_bug_percent_group_size_TRa_TRb = df_bug_percent_solved_TRa_TRb.groupby(['领域',])['解单人'].count()
gb_bug_percent_group_size_TRa_TRb.name = '解单个数'
pt_bug_percent_group_interval_TRa_TRb = pd.pivot_table(df_bug_percent_solved_TRa_TRb, index=['领域', '领域主管'], columns=['BUG状态'], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_percent_group_interval_TRa_TRb.columns = pt_bug_percent_group_interval_TRa_TRb.columns.droplevel(0)
pt_bug_percent_group_interval_TRa_TRb.columns = pt_bug_percent_group_interval_TRa_TRb.columns.droplevel(0)
# bug_case(pt_bug_percent_group_interval_TRa_TRb)
pt_bug_percent_group_interval_TRa_TRb = pd.merge(pt_bug_percent_group_interval_TRa_TRb, gb_bug_percent_group_size_TRa_TRb, \
how='left', left_on=['领域',], right_on=['领域',]).fillna(0)
# print('pt_bug_percent_group_interval_TRa_TRb-----------',pt_bug_percent_group_interval_TRa_TRb)
pt_bug_percent_group_interval_TRa_TRb.loc['All','解单个数'] = pt_bug_percent_group_interval_TRa_TRb['解单个数'].sum()
# print('pt_bug_percent_group_interval_TRa_TRb-----------',pt_bug_percent_group_interval_TRa_TRb)
gb_bug_percent_group_nunique_TRa_TRb = df_bug_percent_solved_TRa_TRb.groupby(['领域',])['解单人'].nunique()
gb_bug_percent_group_nunique_TRa_TRb.name = 'TR参与解单人数'
print('gb_bug_percent_group_nunique_TRa_TRb.name = TR参与解单人数===========', gb_bug_percent_group_nunique_TRa_TRb)
pt_bug_percent_group_interval_TRa_TRb = pd.merge(pt_bug_percent_group_interval_TRa_TRb, gb_bug_percent_group_nunique_TRa_TRb, \
how='left', left_on=['领域',], right_on=['领域',]).fillna(0)
print('pt_bug_percent_group_interval_TRa_TRb===========',pt_bug_percent_group_interval_TRa_TRb)
pt_bug_percent_group_interval_TRa_TRb.loc['All','TR参与解单人数'] = pt_bug_percent_group_interval_TRa_TRb['TR参与解单人数'].sum()
pt_bug_percent_group_interval_TRa_TRb['度量起始时间'] = Day_TRa_datetime
pt_bug_percent_group_interval_TRa_TRb['度量截止时间'] = Day_TRb_datetime
pt_bug_percent_group_interval_TRa_TRb['度量周期(天)'] = interval_TRa_TRb
pt_bug_percent_group_interval_TRa_TRb['效率(个/人天)'] = ((pt_bug_percent_group_interval_TRa_TRb['解单个数'] / pt_bug_percent_group_interval_TRa_TRb['TR参与解单人数']) / pt_bug_percent_group_interval_TRa_TRb['度量周期(天)']).astype(float).apply('{:.2f}'.format)
pt_bug_percent_group_interval_TRa_TRb = pt_bug_percent_group_interval_TRa_TRb.sort_values(by=['效率(个/人天)'], ascending=[False])
pt_bug_percent_group_interval_TRa_TRb['度量周期(月)'] = interval_TRa_TRb/30
pt_bug_percent_group_interval_TRa_TRb['效率(个/人月)'] = ((pt_bug_percent_group_interval_TRa_TRb['解单个数'] / pt_bug_percent_group_interval_TRa_TRb['TR参与解单人数']) / pt_bug_percent_group_interval_TRa_TRb['度量周期(月)']).astype(float).apply('{:.2f}'.format)
pt_bug_percent_group_interval_TRa_TRb['度量周期(月)'] = pt_bug_percent_group_interval_TRa_TRb['度量周期(月)'].astype(float).apply('{:.2f}'.format)
# 将汇总All行切换到最后一行
pt_bug_percent_group_interval_TRa_TRb_All = pt_bug_percent_group_interval_TRa_TRb.loc['All',]
pt_bug_percent_group_interval_TRa_TRb = pt_bug_percent_group_interval_TRa_TRb.drop(index='All',axis=0, )
pt_bug_percent_group_interval_TRa_TRb = pt_bug_percent_group_interval_TRa_TRb.append(pt_bug_percent_group_interval_TRa_TRb_All)
# 改名
pt_bug_percent_group_interval_TRa_TRb.rename(columns={'All':'已解单总计',}, inplace=True)
pt_bug_percent_group_interval_TRa_TRb.rename(index={'All':'汇总/平均值',}, inplace=True)
# 删除
if '测试' in pt_bug_percent_group_interval_TRa_TRb.index:
pt_bug_percent_group_interval_TRa_TRb = pt_bug_percent_group_interval_TRa_TRb.drop(index='测试',axis=0,)
# writer = pd.ExcelWriter(write_path, engine='xlwt')
# with pd.ExcelWriter(write_path, engine='xlwt') as writer:
if len(df_bug_percent_solved_TRa_TRb) > 0:
pt_bug_percent_group_interval_TRa_TRb.to_excel(writer, sheet_name=i+j+'解单group', index=True)
pt_bug_percent_man_interval_TRa_TRb.to_excel(writer, sheet_name=i+j+'解单man', index=True)
for i,j,TRa,TRb in zip(list(my_TR.keys())[:-1],list(my_TR.keys())[1:],list(my_TR.values())[:-1],list(my_TR.values())[1:]):
TRab(i,j,TRa, TRb)
print(i)
print(j)
print(TRa)
print(TRb)
# ---------------------------bug delay ------------------------------
df_bug_regression_fail = df_bug_all[df_bug_all['回归失败次数'] > 0]
print('df_bug_regression_fail.shape ', df_bug_regression_fail.shape)
# group
if len(df_bug_regression_fail) > 0:
pt_bug_regression_fail_group = pd.pivot_table(df_bug_regression_fail, index=['领域','领域主管' ], columns=['严重程度'], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_regression_fail_group.columns = pt_bug_regression_fail_group.columns.droplevel(0)
pt_bug_regression_fail_group.columns = pt_bug_regression_fail_group.columns.droplevel(0)
pt_bug_regression_fail_group.sort_values(by=['All'], ascending=[False], inplace=True)
# ==================== group DI =================================
df_bug_percent_open = df_bug_percent[df_bug_percent['BUG状态'].isin(bug_status_dev)]
if len(df_bug_percent_open) > 0:
pt_bug_percent_DI_group_p5 = pd.pivot_table(df_bug_percent_open, index=['领域', '领域主管'], columns=['严重程度'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_percent_DI_group_p5)
# pt_bug_percent_DI_group_p5 = pt_bug_percent_DI_group_p5.sort_values(by='DI', ascending=False)
# ==================== end of pivot group DI ====================
# ==================== group DI =================================
df_bug_open_current_dev = df_bug_open[df_bug_open['BUG状态'].isin(['待分析','待修改','待修改审核','待CCB',])]
if len(df_bug_open_current_dev) > 0:
pt_bug_open_current_DI_group_c5 = pd.pivot_table(df_bug_open_current_dev, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_current_DI_group_c5)
# pt_bug_open_current_DI_group = pt_bug_open_current_DI_group.sort_values(by='DI', ascending=False)
# ==================== end of pivot group DI ====================
# ==================== DI dev(待分析、待修改、待修改审核、待CCB) ==========================
df_bug_open_dev_DI_group_c4 = df_bug_open[df_bug_open['BUG状态'].isin(bug_status_dev)]
if len(df_bug_open_dev_DI_group_c4) > 0:
pt_bug_open_dev_DI_group_c4 = pd.pivot_table(df_bug_open_dev_DI_group_c4, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_dev_DI_group_c4)
# ==================== DI current(待分析、待修改、待修改审核) ==========================
df_bug_open_DI_group_c3 = df_bug_open[df_bug_open['BUG状态'].isin(['待分析','待修改','待修改审核',])]
if len(df_bug_open_DI_group_c3) > 0:
pt_bug_open_current_DI_group_c3 = pd.pivot_table(df_bug_open_DI_group_c3, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_current_DI_group_c3)
# ==================== end of DI current (待分析、待修改、待修改审核) ===================
# ==================== DI percent(待分析、待修改、待修改审核) ==========================
df_bug_percent_DI_group_p3 = df_bug_percent[df_bug_percent['BUG状态'].isin(['待分析','待修改','待修改审核',])]
if len(df_bug_percent_DI_group_p3) > 0:
pt_bug_percent_DI_group_p5_p3 = pd.pivot_table(df_bug_percent_DI_group_p3, index=['领域', '领域主管'], columns=['严重程度'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_percent_DI_group_p5_p3)
# ==================== end of DI percent (待分析、待修改、待修改审核) ===================
# ==================== DI current(待CCB、挂起) ==========================
df_bug_open_DI_group_ccb2 = df_bug_open[df_bug_open['BUG状态'].isin(['待CCB','待归档',])]
if len(df_bug_open_DI_group_ccb2) > 0:
pt_bug_open_current_DI_group_c2_ccb = pd.pivot_table(df_bug_open_DI_group_ccb2, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_current_DI_group_c2_ccb)
# ==================== end of DI current (待CCB、挂起) ===================
# df_bug_percent_today_assign
df_bug_percent_today_assign = df_bug_percent[(df_bug_percent['缺陷指派时间'] >= select_datetime)]
df_bug_percent_today_assign = df_bug_percent_today_assign[(df_bug_percent_today_assign['缺陷指派时间'] <= select_datetime_end)]
df_bug_percent_today_assign = df_bug_percent_today_assign[~df_bug_percent_today_assign['BUG状态'].isin(['待提交','待指派',])]
print('df_bug_percent_today_assign.shape - ', df_bug_percent_today_assign.shape)
# df_bug_percent_today_solve
df_bug_percent_today_solve = df_bug_percent[(df_bug_percent['解单时间'] >= select_datetime)]
df_bug_percent_today_solve = df_bug_percent_today_solve[(df_bug_percent_today_solve['解单时间'] <= select_datetime_end)]
print('df_bug_percent_today_solve.shape - ', df_bug_percent_today_solve.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[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')
# man
pt_bug_percent_today_assign_man = pd.pivot_table(df_bug_percent_today_assign, index=['领域', '人员'], columns=['严重程度',], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_today_assign_man.columns', pt_bug_percent_today_assign_man.columns)
pt_bug_percent_today_assign_man.columns = pt_bug_percent_today_assign_man.columns.droplevel(0)
pt_bug_percent_today_assign_man.columns = pt_bug_percent_today_assign_man.columns.droplevel(0)
# print('pt_bug_percent_today_assign_man.columns', pt_bug_percent_today_assign_man.columns)
DI_cal(pt_bug_percent_today_assign_man)
pt_bug_percent_today_assign_man = pt_bug_percent_today_assign_man[bug_level+['All','DI']]
pt_bug_percent_today_assign_man.rename(columns={'All':'当天指派',}, inplace=True)
pt_bug_percent_today_assign_man.rename(columns={'DI':'当天指派DI',}, inplace=True)
if ('当天指派' in pt_bug_percent_today_assign_man.columns.values) == False:
pt_bug_percent_today_assign_man['当天指派'] = 0
print('pt_bug_percent_today_assign_man - no 当天指派, 增加一列值为0')
else:
pt_bug_percent_today_assign_group = pd.DataFrame()
pt_bug_percent_today_assign_man = pd.DataFrame()
# group
if len(df_bug_percent_today_solve.index) > 0:
# print('eeeeeeeeeeeeeeeeeeee',df_bug_percent_today_solve)
pt_bug_percent_today_solve_group = pd.pivot_table(df_bug_percent_today_solve, index=['领域', '领域主管'], columns=['严重程度',], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_today_solve_group----------',pt_bug_percent_today_solve_group)
# print('pt_bug_percent_today_solve_group.columns', pt_bug_percent_today_solve_group.columns)
pt_bug_percent_today_solve_group.columns = pt_bug_percent_today_solve_group.columns.droplevel(0)
pt_bug_percent_today_solve_group.columns = pt_bug_percent_today_solve_group.columns.droplevel(0)
# print('pt_bug_percent_today_solve_group.columns', pt_bug_percent_today_solve_group.columns)
DI_cal(pt_bug_percent_today_solve_group)
pt_bug_percent_today_solve_group = pt_bug_percent_today_solve_group[bug_level+['All','DI']]
pt_bug_percent_today_solve_group.rename(columns={'All':'当天解单',}, inplace=True)
pt_bug_percent_today_solve_group.rename(columns={'DI':'当天解单DI',}, inplace=True)
if ('当天解单' in pt_bug_percent_today_solve_group.columns.values) == False:
pt_bug_percent_today_solve_group['当天解单'] = 0
print('pt_bug_percent_today_solve_group - no 当天解单, 增加一列值为0')
# man
pt_bug_percent_today_solve_man = pd.pivot_table(df_bug_percent_today_solve, index=['领域', '人员'], columns=['严重程度',], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_today_solve_man.columns', pt_bug_percent_today_solve_man.columns)
pt_bug_percent_today_solve_man.columns = pt_bug_percent_today_solve_man.columns.droplevel(0)
pt_bug_percent_today_solve_man.columns = pt_bug_percent_today_solve_man.columns.droplevel(0)
# print('pt_bug_percent_today_solve_man.columns', pt_bug_percent_today_solve_man.columns)
DI_cal(pt_bug_percent_today_solve_man)
pt_bug_percent_today_solve_man = pt_bug_percent_today_solve_man[bug_level+['All','DI']]
pt_bug_percent_today_solve_man.rename(columns={'All':'当天解单',}, inplace=True)
pt_bug_percent_today_solve_man.rename(columns={'DI':'当天解单DI',}, inplace=True)
if ('当天解单' in pt_bug_percent_today_solve_man.columns.values) == False:
pt_bug_percent_today_solve_man['当天解单'] = 0
print('pt_bug_percent_today_solve_man - no 当天解单, 增加一列值为0')
else:
pt_bug_percent_today_solve_group = pd.DataFrame()
pt_bug_percent_today_solve_man = pd.DataFrame()
# yesterday
# df_bug_percent_yesterday_assign
df_bug_percent_yesterday_assign = df_bug_percent[(df_bug_percent['缺陷指派时间'] >= select_datetime_yesterday)]
df_bug_percent_yesterday_assign = df_bug_percent_yesterday_assign[(df_bug_percent_yesterday_assign['缺陷指派时间'] <= select_datetime)]
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_solve
df_bug_percent_yesterday_solve = df_bug_percent[(df_bug_percent['解单时间'] >= select_datetime_yesterday)]
df_bug_percent_yesterday_solve = df_bug_percent_yesterday_solve[(df_bug_percent_yesterday_solve['解单时间'] <= select_datetime)]
print('df_bug_percent_yesterday_solve.shape - ', df_bug_percent_yesterday_solve.shape)
# 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[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)
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')
# man
pt_bug_percent_yesterday_assign_man = pd.pivot_table(df_bug_percent_yesterday_assign, index=['领域', '人员'], columns=['严重程度',], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_yesterday_assign_man.columns', pt_bug_percent_yesterday_assign_man.columns)
pt_bug_percent_yesterday_assign_man.columns = pt_bug_percent_yesterday_assign_man.columns.droplevel(0)
pt_bug_percent_yesterday_assign_man.columns = pt_bug_percent_yesterday_assign_man.columns.droplevel(0)
# print('pt_bug_percent_yesterday_assign_man.columns', pt_bug_percent_yesterday_assign_man.columns)
DI_cal(pt_bug_percent_yesterday_assign_man)
pt_bug_percent_yesterday_assign_man = pt_bug_percent_yesterday_assign_man[bug_level+['All','DI']]
pt_bug_percent_yesterday_assign_man.rename(columns={'All':'昨天指派',}, inplace=True)
pt_bug_percent_yesterday_assign_man.rename(columns={'DI':'昨天指派DI',}, inplace=True)
if ('昨天指派' in pt_bug_percent_yesterday_assign_man.columns.values) == False:
pt_bug_percent_yesterday_assign_man['昨天指派'] = 0
print('pt_bug_percent_yesterday_assign_man - no 昨天指派, 增加一列值为0')
else:
pt_bug_percent_yesterday_assign_group = pd.DataFrame()
pt_bug_percent_yesterday_assign_man = pd.DataFrame()
# group
if len(df_bug_percent_yesterday_solve.index) > 0:
pt_bug_percent_yesterday_solve_group = pd.pivot_table(df_bug_percent_yesterday_solve, index=['领域', '领域主管'], columns=['严重程度',], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_yesterday_solve_group----------',pt_bug_percent_yesterday_solve_group)
# print('pt_bug_percent_yesterday_solve_group.columns', pt_bug_percent_yesterday_solve_group.columns)
pt_bug_percent_yesterday_solve_group.columns = pt_bug_percent_yesterday_solve_group.columns.droplevel(0)
pt_bug_percent_yesterday_solve_group.columns = pt_bug_percent_yesterday_solve_group.columns.droplevel(0)
# print('pt_bug_percent_yesterday_solve_group.columns', pt_bug_percent_yesterday_solve_group.columns)
DI_cal(pt_bug_percent_yesterday_solve_group)
pt_bug_percent_yesterday_solve_group = pt_bug_percent_yesterday_solve_group[bug_level+['All','DI']]
pt_bug_percent_yesterday_solve_group.rename(columns={'All':'昨天解单',}, inplace=True)
pt_bug_percent_yesterday_solve_group.rename(columns={'DI':'昨天解单DI',}, inplace=True)
if ('昨天解单' in pt_bug_percent_yesterday_solve_group.columns.values) == False:
pt_bug_percent_yesterday_solve_group['昨天解单'] = 0
print('pt_bug_percent_yesterday_solve_group - no 昨天解单, 增加一列值为0')
# man
pt_bug_percent_yesterday_solve_man = pd.pivot_table(df_bug_percent_yesterday_solve, index=['领域', '人员'], columns=['严重程度',], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_yesterday_solve_man.columns', pt_bug_percent_yesterday_solve_man.columns)
pt_bug_percent_yesterday_solve_man.columns = pt_bug_percent_yesterday_solve_man.columns.droplevel(0)
pt_bug_percent_yesterday_solve_man.columns = pt_bug_percent_yesterday_solve_man.columns.droplevel(0)
# print('pt_bug_percent_yesterday_solve_man.columns', pt_bug_percent_yesterday_solve_man.columns)
DI_cal(pt_bug_percent_yesterday_solve_man)
pt_bug_percent_yesterday_solve_man = pt_bug_percent_yesterday_solve_man[bug_level+['All','DI']]
pt_bug_percent_yesterday_solve_man.rename(columns={'All':'昨天解单',}, inplace=True)
if ('昨天解单' in pt_bug_percent_yesterday_solve_man.columns.values) == False:
pt_bug_percent_yesterday_solve_man['昨天解单'] = 0
print('pt_bug_percent_yesterday_solve_man - no 昨天解单, 增加一列值为0')
else:
pt_bug_percent_yesterday_solve_group = pd.DataFrame()
pt_bug_percent_yesterday_solve_man = pd.DataFrame()
# 合并透视表 percent
pt_bug_percent_group_total = pt_bug_percent_group.copy(deep=True)
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_solve_group) > 0:
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_bug_percent_yesterday_solve_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_solve_group) > 0:
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_bug_percent_today_solve_group[['当天解单','当天解单DI']], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if len(pt_bug_open_dev_DI_group_c4) > 0:
pt_bug_open_dev_DI_group_c4.index.names = ['领域', '领域主管']
pt_bug_percent_group_total = pd.merge(pt_bug_percent_group_total, pt_bug_open_dev_DI_group_c4, \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
# DI变化
if ('当天指派DI' in pt_bug_percent_group_total.columns) and ('当天解单DI' in pt_bug_percent_group_total.columns):
pt_bug_percent_group_total['今日0点DI'] = pt_bug_percent_group_total['DI'] - pt_bug_percent_group_total['当天指派DI'] + pt_bug_percent_group_total['当天解单DI']
# # DI GAP 260
# pt_bug_percent_group_total[['DI目标','DI_GAP']] = 0
# if '应用领域' in pt_bug_percent_group_total.index:
# pt_bug_percent_group_total.loc['应用领域','DI目标'] = 87
# if '多媒体' in pt_bug_percent_group_total.index:
# pt_bug_percent_group_total.loc['多媒体','DI目标'] = 22
# if 'Camera' in pt_bug_percent_group_total.index:
# pt_bug_percent_group_total.loc['Camera','DI目标'] = 23
# if 'OS领域' in pt_bug_percent_group_total.index:
# pt_bug_percent_group_total.loc['OS领域','DI目标'] = 46
# if '调优领域' in pt_bug_percent_group_total.index:
# pt_bug_percent_group_total.loc['调优领域','DI目标'] = 26
# if '协议领域' in pt_bug_percent_group_total.index:
# pt_bug_percent_group_total.loc['协议领域','DI目标'] = 49
# if '协议-短距' in pt_bug_percent_group_total.index:
# pt_bug_percent_group_total.loc['协议-短距','DI目标'] = 7
# if 'All' in pt_bug_percent_group_total.index:
# pt_bug_percent_group_total.loc['All','DI目标'] = 260
# DI GAP 100
ratio_DI_gap = 1
pt_bug_percent_group_total[['DI目标','DI_GAP']] = 0
if '应用领域' in pt_bug_percent_group_total.index:
pt_bug_percent_group_total.loc['应用领域','DI目标'] = 33 *ratio_DI_gap
if '多媒体' in pt_bug_percent_group_total.index:
pt_bug_percent_group_total.loc['多媒体','DI目标'] = 9 *ratio_DI_gap
if 'Camera' in pt_bug_percent_group_total.index:
pt_bug_percent_group_total.loc['Camera','DI目标'] = 9 *ratio_DI_gap
if 'OS领域' in pt_bug_percent_group_total.index:
pt_bug_percent_group_total.loc['OS领域','DI目标'] = 17 *ratio_DI_gap
if '调优领域' in pt_bug_percent_group_total.index:
pt_bug_percent_group_total.loc['调优领域','DI目标'] = 10 *ratio_DI_gap
if '协议领域' in pt_bug_percent_group_total.index:
pt_bug_percent_group_total.loc['协议领域','DI目标'] = 19 *ratio_DI_gap
if '协议-短距' in pt_bug_percent_group_total.index:
pt_bug_percent_group_total.loc['协议-短距','DI目标'] = 3 *ratio_DI_gap
if 'All' in pt_bug_percent_group_total.index:
pt_bug_percent_group_total.loc['All','DI目标'] = 100 *ratio_DI_gap
pt_bug_percent_group_total = pt_bug_percent_group_total.fillna(0)
pt_bug_percent_group_total['DI_GAP'] = pt_bug_percent_group_total['DI'] - pt_bug_percent_group_total['DI目标']
pt_bug_percent_group_total = pt_bug_percent_group_total.sort_values(by=['DI'], ascending=[False])
# pt_bug_percent_group_total = pt_bug_percent_group_total.sort_values(by=['DI_GAP'], ascending=[False])
# pt_bug_percent_group_total = pt_bug_percent_group_total.sort_values(by=['开发解单率'], ascending=[False])
# # 完成率排序，解决100%不排序问题
# pt_bug_percent_group_total['开发解单率'] = pt_bug_percent_group_total['开发解单率'].apply('{:.4}'.format)
# # sort_values
# pt_bug_percent_group_total = pt_bug_percent_group_total.sort_values(by=['开发解单率'], ascending=[False])
# pt_bug_percent_group_total['开发解单率'] = pt_bug_percent_group_total['开发解单率'].astype(float).apply('{:.0%}'.format)
# # 将汇总All行切换到最后一行
# pt_bug_percent_group_total_All = pt_bug_percent_group_total.loc['All','']
# pt_bug_percent_group_total = pt_bug_percent_group_total.drop(index='All',axis=0, level=0)
# # pt_bug_percent_group_total_All = pt_bug_percent_group_total.loc['All']
# # pt_bug_percent_group_total = pt_bug_percent_group_total.drop(index='All',axis=0, )
# pt_bug_percent_group_total = pt_bug_percent_group_total.append(pt_bug_percent_group_total_All)
if 'Blocker' in pt_bug_percent_group_total.columns:
if pt_bug_percent_group_total['Blocker'].sum() == 0:
pt_bug_percent_group_total = pt_bug_percent_group_total.drop(columns='Blocker')
#--------------------------- Start of open dev DI group -------------------
# 当前开发状态遗留问题单
# df_bug_open_dev = df_bug_open[df_bug_open['BUG状态'].isin(['待分析','待分析审核','待修改','待修改审核','待归档','待CCB'])]
# 应开发要求，去除“待归档”状态
df_bug_open_dev = df_bug_open[df_bug_open['BUG状态'].isin(['待分析','待分析审核','待修改','待修改审核','待CCB'])]
# group------------------------------------------------------------
pt_bug_open_dev_group = pd.pivot_table(df_bug_open_dev, index=['领域_当前', '领域主管_当前'], columns=['BUG状态'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_open_dev_group.columns = pt_bug_open_dev_group.columns.droplevel(0)
pt_bug_open_dev_group.columns = pt_bug_open_dev_group.columns.droplevel(0)
pt_bug_open_dev_group.rename(columns={'All':'全部状态',}, inplace=True)
bug_case(pt_bug_open_dev_group)
pt_bug_open_dev_group = pt_bug_open_dev_group[bug_status]
pt_bug_open_dev_group_total = pt_bug_open_dev_group.copy(deep=True)
pt_bug_open_dev_group_total.index.names = ['领域', '领域主管']
if len(pt_bug_percent_yesterday_assign_group) > 0:
pt_bug_open_dev_group_total = pd.merge(pt_bug_open_dev_group_total, pt_bug_percent_yesterday_assign_group[['昨天指派','昨天指派DI']], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if len(pt_bug_percent_yesterday_solve_group) > 0:
pt_bug_open_dev_group_total = pd.merge(pt_bug_open_dev_group_total, pt_bug_percent_yesterday_solve_group[['昨天解单','昨天解单DI']], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if len(pt_bug_percent_today_assign_group) > 0:
pt_bug_open_dev_group_total = pd.merge(pt_bug_open_dev_group_total, pt_bug_percent_today_assign_group[['当天指派','当天指派DI']], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if len(pt_bug_percent_today_solve_group) > 0:
pt_bug_open_dev_group_total = pd.merge(pt_bug_open_dev_group_total, pt_bug_percent_today_solve_group[['当天解单','当天解单DI']], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if len(pt_bug_open_dev_DI_group_c4) > 0:
pt_bug_open_dev_DI_group_c4.index.names = ['领域', '领域主管']
pt_bug_open_dev_group_total = pd.merge(pt_bug_open_dev_group_total, pt_bug_open_dev_DI_group_c4, \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
bug_status_delete = ['待提交','待指派','待回归验证','挂起','已关闭','已废弃','Blocker','Critical']
for i in bug_status_delete:
if pt_bug_open_dev_group_total[i].sum() == 0:
pt_bug_open_dev_group_total = pt_bug_open_dev_group_total.drop(columns=i)
# # DI GAP 260
# pt_bug_open_dev_group_total[['DI目标','DI_GAP']] = 0
# if '应用领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['应用领域','DI目标'] = 87
# if '多媒体' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['多媒体','DI目标'] = 22
# if 'Camera' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['Camera','DI目标'] = 23
# if 'OS领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['OS领域','DI目标'] = 46
# if '调优领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['调优领域','DI目标'] = 26
# if '协议领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['协议领域','DI目标'] = 49
# if '协议-短距' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['协议-短距','DI目标'] = 7
# if 'All' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['All','DI目标'] = 260
# # DI GAP LQ-110
# pt_bug_open_dev_group_total[['DI目标','DI_GAP']] = 0
# if '应用领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['应用领域','DI目标'] = 30
# if '多媒体' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['多媒体','DI目标'] = 15
# if 'Camera' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['Camera','DI目标'] = 10
# if 'OS领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['OS领域','DI目标'] = 8
# # if '调优领域' in pt_bug_open_dev_group_total.index:
# # pt_bug_open_dev_group_total.loc['调优领域','DI目标'] = 15
# if '性能' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['性能','DI目标'] = 5
# if '功耗' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['功耗','DI目标'] = 5
# if '稳定性' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['稳定性','DI目标'] = 5
# if '稳定性' not in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc[('稳定性','段东峰'),'DI目标'] = 5
# if '协议领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['协议领域','DI目标'] = 30
# if '协议-短距' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['协议-短距','DI目标'] = 2
# if 'All' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['All','DI目标'] = 110
# # DI GAP LQ-60-36
# pt_bug_open_dev_group_total[['DI目标','DI_GAP']] = 0
# if '应用' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['应用','DI目标'] = 10
# if '多媒体' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['多媒体','DI目标'] = 5
# if 'Camera' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['Camera','DI目标'] = 5
# if 'OS' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['OS','DI目标'] = 5
# # if '调优领域' in pt_bug_open_dev_group_total.index:
# # pt_bug_open_dev_group_total.loc['调优领域','DI目标'] = 5
# if '性能' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['性能','DI目标'] = 4
# if '功耗' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['功耗','DI目标'] = 1
# if '稳定性' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['稳定性','DI目标'] = 0
# if '稳定性' not in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc[('稳定性','段东峰'),'DI目标'] = 0
# if '协议' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['协议','DI目标'] = 4
# if '协议-短距' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['协议-短距','DI目标'] = 2
# if 'All' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['All','DI目标'] = 36
# DI GAP LQ TR6 DI 10
pt_bug_open_dev_group_total[['DI目标','DI_GAP']] = 0
if '应用' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['应用','DI目标'] = 3
if '多媒体' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['多媒体','DI目标'] = 1
if 'Camera' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['Camera','DI目标'] = 1
if 'OS' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['OS','DI目标'] = 0.2
# if '调优领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['调优领域','DI目标'] = 5
if '性能' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['性能','DI目标'] = 1
if '功耗' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['功耗','DI目标'] = 1
if '稳定性' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['稳定性','DI目标'] = 0
if '稳定性' not in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc[('稳定性','段东峰'),'DI目标'] = 0
if '协议' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['协议','DI目标'] = 1
if '协议-短距' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['协议-短距','DI目标'] = 0.2
if 'All' in pt_bug_open_dev_group_total.index:
pt_bug_open_dev_group_total.loc['All','DI目标'] = 10
# # # DI GAP ZN-150
# pt_bug_open_dev_group_total[['DI目标','DI_GAP']] = 0
# if '应用领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['应用领域','DI目标'] = 35
# if '多媒体' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['多媒体','DI目标'] = 10
# if 'Camera' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['Camera','DI目标'] = 10
# if 'OS领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['OS领域','DI目标'] = 5
# # if '调优领域' in pt_bug_open_dev_group_total.index:
# # pt_bug_open_dev_group_total.loc['调优领域','DI目标'] = 15
# if '性能' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['性能','DI目标'] = 5
# if '功耗' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['功耗','DI目标'] = 10
# if '稳定性' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['稳定性','DI目标'] = 5
# if '协议领域' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['协议领域','DI目标'] = 45
# if '协议-短距' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['协议-短距','DI目标'] = 3
# if 'All' in pt_bug_open_dev_group_total.index:
# pt_bug_open_dev_group_total.loc['All','DI目标'] = 150
# DI GAP
pt_bug_open_dev_group_total = pt_bug_open_dev_group_total.fillna(0)
pt_bug_open_dev_group_total['DI_GAP'] = pt_bug_open_dev_group_total['DI'] - pt_bug_open_dev_group_total['DI目标']
# pt_bug_open_dev_group_total = pt_bug_open_dev_group_total.sort_values(by=['DI_GAP'], ascending=[False])
pt_bug_open_dev_group_total = pt_bug_open_dev_group_total.sort_values(by=['DI'], ascending=[False])
# DI-每日冲刺DI分解 di_everyday
vlookup_di_everyday = r'C:\pyDaily\vlookup\6375LQ问题单跟踪(每日DI和目标GAP).xlsx'
df_di_everyday_all = pd.read_excel(vlookup_di_everyday, sheet_name = 'DI_everyday', engine = 'openpyxl')
# df_di_everyday = df_di_everyday_all.copy(deep=True)
for i in df_di_everyday_all.columns:
if i == pd.to_datetime(datetime.date.today()):
print(i)
print(type(i))
df_di_everyday = df_di_everyday_all[['领域',i]]
print('df_di_everyday.columns------', df_di_everyday.columns)
pt_bug_open_dev_group_total_everyday = pd.merge(pt_bug_open_dev_group_total, df_di_everyday, \
how='left', left_on=['领域'], right_on=['领域']).fillna(0)
pt_bug_open_dev_group_total_everyday['今日GAP'] = pt_bug_open_dev_group_total_everyday['DI'] - pt_bug_open_dev_group_total_everyday[i]
pt_bug_open_dev_group_total_everyday.rename(columns={i:'今日DI目标',}, inplace=True)
pt_bug_open_dev_group_total_everyday = pt_bug_open_dev_group_total_everyday.sort_values(by=['今日GAP','领域','DI',], ascending=[False, False, False])
lq_team= ['LQ-硬件','LQ-SE','LQ-音效','LQ-Cam','LQ-协议','LQ-温控','LQ-OS',]
pt_bug_open_dev_group_total_everyday.set_index('领域',inplace=True)
for i in lq_team:
print(i)
if i in pt_bug_open_dev_group_total_everyday.index:
print('index领域',i)
pt_bug_open_dev_group_total_everyday= pt_bug_open_dev_group_total_everyday.drop(i)
pt_bug_open_dev_group_total_everyday.reset_index(inplace=True)
#依赖
if len(df_bug_tag_dev_open) > 0:
pt_bug_tag_dev_DI_group.index.names = ['领域', '领域主管']
pt_bug_tag_dev_DI_group.rename(columns={'DI':'依赖DI',}, inplace=True)
pt_bug_open_dev_group_total = pd.merge(pt_bug_open_dev_group_total, pt_bug_tag_dev_DI_group['依赖DI'], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
pt_bug_open_dev_group_total['除去依赖DI'] = pt_bug_open_dev_group_total['DI'] - pt_bug_open_dev_group_total['依赖DI']
# 0点DI
if ('当天指派DI' in pt_bug_open_dev_group_total.columns) and ('当天解单DI' in pt_bug_open_dev_group_total.columns):
pt_bug_open_dev_group_total['今日0点DI'] = pt_bug_open_dev_group_total['DI'] - pt_bug_open_dev_group_total['当天指派DI'] + pt_bug_open_dev_group_total['当天解单DI']
# if len(pt_bug_open_current_DI_group) > 0:
# pt_bug_open_current_DI_group.index.names = ['领域', '领域主管']
# pt_bug_open_dev_group_total = pd.merge(pt_bug_open_dev_group_total, pt_bug_open_current_DI_group, \
# how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if 'Blocker' in pt_bug_open_dev_group_total.columns:
if pt_bug_open_dev_group_total['Blocker'].sum() == 0:
pt_bug_open_dev_group_total = pt_bug_open_dev_group_total.drop(columns='Blocker')
if '待归档' in pt_bug_open_dev_group_total.columns:
if pt_bug_open_dev_group_total['待归档'].sum() == 0:
pt_bug_open_dev_group_total = pt_bug_open_dev_group_total.drop(columns='待归档')
#--------------------------- End of open dev DI group -------------------
# ------------------------Start of 待归档-----------------------
df_bug_open_dev_scm = df_bug_open[df_bug_open['BUG状态'].isin(['待归档'])]
if len(df_bug_open_dev_scm) > 0:
pt_bug_open_dev_scm_group = pd.pivot_table(df_bug_open_dev_scm, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_dev_scm_group)
pt_bug_open_dev_scm_group = pt_bug_open_dev_scm_group.sort_values(by=['DI','All'], ascending=[False,False])
# ------------------------End of 待归档-------------------------
# =============================BUG透视表 pivot ======
# current man------------------------------------------------------------
pt_bug_open_current_man = pd.pivot_table(df_bug_open_current_dev, index=['领域_当前', '人员_当前'], columns=['BUG状态'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_open_current_man.columns', pt_bug_open_current_man.columns)
pt_bug_open_current_man.columns = pt_bug_open_current_man.columns.droplevel(0)
pt_bug_open_current_man.columns = pt_bug_open_current_man.columns.droplevel(0)
# print('pt_bug_open_current_man.columns', pt_bug_open_current_man.columns)
pt_bug_open_current_man.rename(columns={'All':'全部状态',}, inplace=True)
bug_case(pt_bug_open_current_man)
pt_bug_open_current_man = pt_bug_open_current_man[bug_status]
# ====================================
pt_bug_percent_man = pd.pivot_table(df_bug_percent, index=['领域', '人员'], columns=['BUG状态'], \
values=['缺陷修改责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_man.columns', pt_bug_percent_man.columns)
pt_bug_percent_man.columns = pt_bug_percent_man.columns.droplevel(0)
pt_bug_percent_man.columns = pt_bug_percent_man.columns.droplevel(0)
# print('pt_bug_percent_man.columns', pt_bug_percent_man.columns)
pt_bug_percent_man.rename(columns={'All':'全部状态',}, inplace=True)
bug_case(pt_bug_percent_man)
pt_bug_percent_man = pt_bug_percent_man[bug_status]
pt_bug_percent_man['开发环节单'] = pt_bug_percent_man['待分析'] \
+ pt_bug_percent_man['待修改'] + pt_bug_percent_man['待修改审核'] \
+ pt_bug_percent_man['待CCB']
# pt_bug_percent_man['累计解单'] = pt_bug_percent_man['待提交'] + pt_bug_percent_man['待指派'] \
# + pt_bug_percent_man['待CCB'] + pt_bug_percent_man['挂起'] \
# + pt_bug_percent_man['待归档'] + pt_bug_percent_man['待回归验证'] + pt_bug_percent_man['已关闭']
pt_bug_percent_man['累计解单'] = pt_bug_percent_man['待归档'] + pt_bug_percent_man['挂起'] \
+ pt_bug_percent_man['待回归验证'] + pt_bug_percent_man['已关闭'] + pt_bug_percent_man['已废弃']
# 百分比排序，先设置为4位小数
# pt_bug_percent_man['开发解单率'] = (pt_bug_percent_man['累计解单'].astype(float) / pt_bug_percent_man['累计指派'].astype(float)).apply('{:.4}'.format)
pt_bug_percent_man['开发解单率'] = (pt_bug_percent_man['累计解单'].astype(float) \
/ (pt_bug_percent_man['累计解单'].astype(float) + pt_bug_percent_man['开发环节单'].astype(float))).apply('{:.4}'.format)
# sort_values
# pt_bug_percent_man = pt_bug_percent_man.sort_values(by=['开发解单率'], ascending=[False])
# pt_bug_percent_man = pt_bug_percent_man.sort_values(by=['领域', '开发解单率'], ascending=[True, False])
pt_bug_percent_man = pt_bug_percent_man.sort_values(by=['开发解单率'], ascending=[False])
pt_bug_percent_man['开发解单率'] = pt_bug_percent_man['开发解单率'].astype(float).apply('{:.0%}'.format)
# ------------- DI_man ------------
pt_bug_open_current_DI_man = pd.pivot_table(df_bug_open_current_dev, index=['领域_当前', '人员_当前'], columns=['严重程度'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_current_DI_man)
pt_bug_percent_DI_man = pd.pivot_table(df_bug_percent_open, index=['领域', '人员'], columns=['严重程度'], \
values=['解单人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_percent_DI_man)
# pt_bug_percent_DI_man = pt_bug_percent_DI_man.sort_values(by='DI', ascending=False)
# --------------------end of pivot man-----------------------
# pt_bug_percent_total_man = pd.merge(pt_bug_percent_man, pt_bug_percent_today_man, \
# how='left', left_on=['领域', '人员'], right_on=['领域', '人员'])
# pt_bug_percent_total_man = pd.merge(pt_bug_percent_total_man, pt_bug_percent_DI_man, \
# how='left', left_on=['领域', '人员'], right_on=['领域', '人员']).fillna(0)
# percent man
pt_bug_percent_total_man = pt_bug_percent_man.copy(deep=True)
if len(pt_bug_percent_today_assign_man) > 0:
pt_bug_percent_total_man = pd.merge(pt_bug_percent_total_man, pt_bug_percent_today_assign_man['当天指派'], \
how='left', left_on=['领域', '人员'], right_on=['领域', '人员']).fillna(0)
if len(pt_bug_percent_today_solve_man) > 0:
pt_bug_percent_total_man = pd.merge(pt_bug_percent_total_man, pt_bug_percent_today_solve_man['当天解单'], \
how='left', left_on=['领域', '人员'], right_on=['领域', '人员']).fillna(0)
if len(pt_bug_open_current_DI_man) > 0:
pt_bug_open_current_DI_man.index.names = ['领域', '人员']
pt_bug_percent_total_man = pd.merge(pt_bug_percent_total_man, pt_bug_open_current_DI_man, \
how='left', left_on=['领域', '人员'], right_on=['领域', '人员']).fillna(0)
# if len(pt_bug_percent_DI_man) > 0:
# pt_bug_percent_total_man = pd.merge(pt_bug_percent_total_man, pt_bug_percent_DI_man, \
# how='left', left_on=['领域', '人员'], right_on=['领域', '人员']).fillna(0)
# sort_values
if '当天解单' in pt_bug_percent_total_man.columns:
pt_bug_percent_total_man = pt_bug_percent_total_man.sort_values(by=['开发环节单','累计解单',], ascending=[False,False,])
else:
pt_bug_percent_total_man = pt_bug_percent_total_man.sort_values(by=['开发环节单'], ascending=False)
if 'Blocker' in pt_bug_percent_total_man.columns:
if pt_bug_percent_total_man['Blocker'].sum() == 0:
pt_bug_percent_total_man = pt_bug_percent_total_man.drop(columns='Blocker')
# delete PM and Tester
# pt_bug_percent_total_man = pt_bug_percent_total_man.drop(index='PM',axis=0, level=0)
# pt_bug_percent_total_man = pt_bug_percent_total_man.drop(index='测试',axis=0, level=0)
# ======================== end of pt_bug_percent_man ========================
# current man
pt_bug_open_current_man_total = pt_bug_open_current_man.copy(deep=True)
pt_bug_open_current_man_total.index.names = ['领域', '人员']
if len(pt_bug_percent_today_assign_man) > 0:
pt_bug_open_current_man_total = pd.merge(pt_bug_open_current_man_total, pt_bug_percent_today_assign_man['当天指派'], \
how='left', left_on=['领域', '人员'], right_on=['领域', '人员']).fillna(0)
if len(pt_bug_percent_today_solve_man) > 0:
pt_bug_open_current_man_total = pd.merge(pt_bug_open_current_man_total, pt_bug_percent_today_solve_man['当天解单'], \
how='left', left_on=['领域', '人员'], right_on=['领域', '人员']).fillna(0)
if len(pt_bug_open_current_DI_man) > 0:
pt_bug_open_current_DI_man.index.names = ['领域', '人员']
pt_bug_open_current_man_total = pd.merge(pt_bug_open_current_man_total, pt_bug_open_current_DI_man, \
how='left', left_on=['领域', '人员'], right_on=['领域', '人员']).fillna(0)
pt_bug_open_current_man_total = pt_bug_open_current_man_total
if '当天解单' in pt_bug_open_current_man_total.columns:
pt_bug_open_current_man_total = pt_bug_open_current_man_total.sort_values(by=['DI','当天解单',], ascending=[False,False,])
else:
pt_bug_open_current_man_total = pt_bug_open_current_man_total.sort_values(by=['DI'], ascending=False)
if 'Blocker' in pt_bug_open_current_man_total.columns:
if pt_bug_open_current_man_total['Blocker'].sum() == 0:
pt_bug_open_current_man_total = pt_bug_open_current_man_total.drop(columns='Blocker')
drop_columns_man = ['待提交','待指派','待归档','待回归验证','已废弃','已关闭','挂起','Blocker','Critical']
for i_man in drop_columns_man:
if i_man in pt_bug_open_current_man_total.columns:
if pt_bug_open_current_man_total[i_man].sum() == 0:
pt_bug_open_current_man_total = pt_bug_open_current_man_total.drop(columns=i_man)
# =================应用领域=================
# sort_values
if '累计解单' in pt_bug_percent_total_man.columns:
pt_bug_percent_total_man_yingyong = pt_bug_percent_total_man.sort_values(by=['领域', '累计解单'],ascending=[False,False,])
else:
pt_bug_percent_total_man_yingyong = pt_bug_percent_total_man.sort_values(by=['领域'], ascending=False)
if '测试' in pt_bug_percent_total_man_yingyong.index:
pt_bug_percent_total_man_yingyong = pt_bug_percent_total_man_yingyong.drop(index='测试',axis=0, level=0)
if 'Blocker' in pt_bug_percent_total_man_yingyong.columns:
if pt_bug_percent_total_man_yingyong['Blocker'].sum() == 0:
pt_bug_percent_total_man_yingyong = pt_bug_percent_total_man_yingyong.drop(columns='Blocker')
# ========================= Start of Period Week ===============================
# TR4A DI目标260，各领域分解
df_spm = pd.DataFrame(data=[["应用领域",'封航航',87],
["多媒体",'孙建伟',23],
["Camera",'王文轩',23],
["调优领域",'牛建民',26],
["协议领域",'杨朋',49],
["协议-短距",'刘超国',7],
["OS领域",'崔张波',46],],
columns=['领域','领域主管','人数'],)
# Week efficient
df_bug_period = df_bug_all.copy(deep=True)
# df_bug_week = df_bug_week.set_index('缺陷指派时间',drop=False,)
# w_bug = df_bug_week['缺陷指派时间'].resample('w').count()
'''
df_bug_period = df_bug_period.set_index('解单时间',drop=False,)
df_bug_period.index.name = '时间索引'
df_debug_week = df_bug_period.to_period('W')
df_bug_period['Week'] = df_debug_week.index
df_debug_day = df_bug_period.to_period('D')
df_bug_period['Day'] = df_debug_day.index
df_bug_period.reset_index(drop=True)
'''
# 人效率
gb_week_debug_man = df_bug_period[['Week','领域','解单人','解单时间']].groupby(['Week','领域','解单人',]).count()
gb_week_debug_man.rename(columns={'解单时间':'本周解单数',}, inplace=True)
gb_week_debug_man = gb_week_debug_man.sort_values(by=['Week','领域','本周解单数'], ascending=[True, True, False])
# 每周工作量
gb_week_ana_man = df_bug_period[['Week','领域_分析','人员_分析','缺陷分析时间']].groupby(['Week','领域_分析','人员_分析',]).count()
gb_week_ana_man.rename(columns={'缺陷分析时间':'本周分析数',}, inplace=True)
# gb_week_ana_man.reset_index(inplace=True)
gb_week_mod_man = df_bug_period[['Week','领域_修改','人员_修改','缺陷修改时间']].groupby(['Week','领域_修改','人员_修改',]).count()
gb_week_mod_man.rename(columns={'缺陷修改时间':'本周修改数',}, inplace=True)
# gb_week_mod_man.reset_index(inplace=True)
gb_week_check_man = df_bug_period[['Week','领域_审核','人员_审核','修改审核时间']].groupby(['Week','领域_审核','人员_审核',]).count()
gb_week_check_man.rename(columns={'修改审核时间':'本周修改审核数',}, inplace=True)
# gb_week_check_man.reset_index(inplace=True)
gb_week_ccb_man = df_bug_period[['Week','领域_CCB','人员_CCB','CCB时间',]].groupby(['Week','领域_CCB','人员_CCB',]).count()
gb_week_ccb_man.rename(columns={'CCB时间':'本周CCB数',}, inplace=True)
# gb_week_ccb_man.reset_index(inplace=True)
# # gb_week_add_man = pd.merge(gb_week_ana_man, gb_week_mod_man, how='outer', left_on='Week',right_on='Week')
# gb_week_add_man = pd.merge(gb_week_ana_man, gb_week_mod_man, how='outer', left_on='人员_分析',right_on='人员_修改')
# gb_week_concat_man = pd.concat([gb_week_ana_man, gb_week_mod_man], \
# axis=1, join='outer', ignore_index=True, )
# 组效率
gb_week_debug = df_bug_period.groupby(['Week','领域',])[['解单人',]].count()
gb_week_debug.rename(columns={'解单人':'本周解单数',}, inplace=True)
# gb_week_debug.reset_index(level='领域', inplace=True)
gb_week_debug.reset_index(level=['Week','领域'], inplace=True)
# week_merge = pd.merge(gb_week_debug, df_spm, how='left', on='领域')
week_merge = pd.merge(gb_week_debug, df_spm, how='right', on='领域')
# gb_week_efficient = week_merge.groupby(['Week','领域','人数'])['本周解单'].sum()
# gb_week_efficient = week_merge.groupby(['Week','领域','人数'])[['本周解单','人数']].sum()
gb_week_efficient = week_merge.groupby(['Week','领域','人数'])[['领域','人数','本周解单数',]].sum()
gb_week_efficient['每周效率(个/人天)'] = (gb_week_efficient['本周解单数'] / gb_week_efficient['人数'] / 7).astype(float).apply('{:.2f}'.format)
# gb_week_efficient = gb_week_efficient.sort_values(by=['每周效率(个/人天)'], ascending=[False])
gb_week_efficient.drop('人数',axis=1, inplace=True)
gb_week_efficient = gb_week_efficient.sort_values(by=['Week','领域', '每周效率(个/人天)'], ascending=[True, True,False])
# w_debug = df_bug_period['解单时间'].resample('W').count()
# w_debug = df_bug_period['解单时间'].resample('W').count().to_period('W')
# df_period = df_bug_period.to_period('M')
# w_bug_debug = pd.concat([w_bug,w_debug], axis=1).fillna(0)
# ========================= End of Period Week ===============================
# ========================= 解单时长 all ========================
df_dev_duration = df_bug_all.copy(deep=True)
print('df_dev_duration------',df_dev_duration.shape)
if len(df_dev_duration)>0:
df_dev_duration.loc[:, '已解单开发环节时长'] = df_dev_duration.loc[:, '已解单开发环节时长'].apply(lambda x:x.days)
gb_dev_duration = df_dev_duration[['严重程度','已解单开发环节时长']].groupby(['严重程度',]).mean()
gb_dev_duration_group = df_dev_duration[['严重程度','领域','已解单开发环节时长']].groupby(['严重程度','领域']).mean()
gb_dev_duration_group2 = df_dev_duration[['严重程度','领域','已解单开发环节时长']].groupby(['领域','严重程度',]).mean()
# duration all
gb_dev_duration.to_excel(writer, sheet_name='all已解单开发环节时长', index=True)
gb_dev_duration_group.to_excel(writer, sheet_name='all已解单开发环节时长group', index=True)
gb_dev_duration_group2.to_excel(writer, sheet_name='all已解单开发环节时长group2', index=True)
pt_dev_duration_group = pd.pivot_table(df_dev_duration, index=['领域', ], columns=['严重程度'], \
values=['已解单开发环节时长'], aggfunc=[np.mean], margins = True)
pt_dev_duration_group.columns = pt_dev_duration_group.columns.droplevel(0)
pt_dev_duration_group.columns = pt_dev_duration_group.columns.droplevel(0)
if set(['Critical','Major','Normal','Minor','All']) == set(pt_dev_duration_group.columns):
pt_dev_duration_group = pt_dev_duration_group[['Critical','Major','Normal','Minor','All']]
pt_dev_duration_group = pt_dev_duration_group.sort_values(by=['Critical','Major'], ascending=[False,False]).round(2)
pt_dev_duration_group = pt_dev_duration_group.rename(index={'All':'领域平均'}, columns = {'All':'问题单平均'})
pt_dev_duration_group.to_excel(writer, sheet_name='all已解单开发环节时长group_pt', index=True)
# ========================= 解单时长 notag ========================
df_dev_duration_notag = df_bug_all[~df_bug_all['标签'].astype(str).str[:4].str.contains('依赖')]
print('df_dev_duration_notag------',df_dev_duration_notag.shape)
if len(df_dev_duration_notag)>0:
df_dev_duration_notag.loc[:, '已解单开发环节时长'] = df_dev_duration_notag.loc[:, '已解单开发环节时长'].apply(lambda x:x.days)
gb_dev_duration_notag = df_dev_duration_notag[['严重程度','已解单开发环节时长']].groupby(['严重程度',]).mean()
gb_dev_duration_notag_group = df_dev_duration_notag[['严重程度','领域','已解单开发环节时长']].groupby(['严重程度','领域']).mean()
gb_dev_duration_notag_group2 = df_dev_duration_notag[['严重程度','领域','已解单开发环节时长']].groupby(['领域','严重程度',]).mean()
# duration notag
gb_dev_duration_notag.to_excel(writer, sheet_name='notag已解单开发环节时长_notag', index=True)
gb_dev_duration_notag_group.to_excel(writer, sheet_name='notag已解单开发环节时长group_notag', index=True)
gb_dev_duration_notag_group2.to_excel(writer, sheet_name='notag已解单开发环节时长group2_notag', index=True)
# pivot
pt_dev_duration_notag_group = pd.pivot_table(df_dev_duration_notag, index=['领域', ], columns=['严重程度'], \
values=['已解单开发环节时长'], aggfunc=[np.mean], margins = True)
pt_dev_duration_notag_group.columns = pt_dev_duration_notag_group.columns.droplevel(0)
pt_dev_duration_notag_group.columns = pt_dev_duration_notag_group.columns.droplevel(0)
if set(['Critical','Major','Normal','Minor','All']) == set(pt_dev_duration_notag_group.columns):
pt_dev_duration_notag_group = pt_dev_duration_notag_group[['Critical','Major','Normal','Minor','All']]
pt_dev_duration_notag_group = pt_dev_duration_notag_group.sort_values(by=['Critical','Major'], ascending=[False,False]).round(2)
pt_dev_duration_notag_group = pt_dev_duration_notag_group.rename(index={'All':'领域平均'}, columns = {'All':'问题单平均'})
pt_dev_duration_notag_group.to_excel(writer, sheet_name='notag已解单开发环节时长group_pt_notag', index=True)
# ========================= 解单时长 tag ========================
df_dev_duration_tag = df_bug_all[df_bug_all['标签'].astype(str).str[:4].str.contains('依赖')]
print('df_dev_duration_tag------',df_dev_duration_tag.shape)
if len(df_dev_duration_tag)>0:
df_dev_duration_tag.loc[:, '已解单开发环节时长'] = df_dev_duration_tag.loc[:, '已解单开发环节时长'].apply(lambda x:x.days)
gb_dev_duration_tag = df_dev_duration_tag[['严重程度','已解单开发环节时长']].groupby(['严重程度',]).mean()
gb_dev_duration_tag_group = df_dev_duration_tag[['严重程度','领域','已解单开发环节时长']].groupby(['严重程度','领域']).mean()
gb_dev_duration_tag_group2 = df_dev_duration_tag[['严重程度','领域','已解单开发环节时长']].groupby(['领域','严重程度',]).mean()
# duration tag
gb_dev_duration_tag.to_excel(writer, sheet_name='tag已解单开发环节时长_tag', index=True)
gb_dev_duration_tag_group.to_excel(writer, sheet_name='tag已解单开发环节时长group_tag', index=True)
gb_dev_duration_tag_group2.to_excel(writer, sheet_name='tag已解单开发环节时长group2_tag', index=True)
if len(df_dev_duration_tag)>1:
pt_dev_duration_tag_group = pd.pivot_table(df_dev_duration_tag, index=['领域', ], columns=['严重程度'], \
values=['已解单开发环节时长'], aggfunc=[np.mean], margins = True)
pt_dev_duration_tag_group.columns = pt_dev_duration_tag_group.columns.droplevel(0)
pt_dev_duration_tag_group.columns = pt_dev_duration_tag_group.columns.droplevel(0)
if set(['Critical','Major','Normal','Minor','All']) == set(pt_dev_duration_tag_group.columns):
pt_dev_duration_tag_group = pt_dev_duration_tag_group[['Critical','Major','Normal','Minor','All']]
pt_dev_duration_tag_group = pt_dev_duration_tag_group.sort_values(by=['Critical','Major'], ascending=[False,False]).round(2)
pt_dev_duration_tag_group = pt_dev_duration_tag_group.rename(index={'All':'领域平均'}, columns = {'All':'问题单平均'})
pt_dev_duration_tag_group.to_excel(writer, sheet_name='tag已解单开发环节时长group_pt_tag', index=True)
# ========================= group_zk ========================
df_bug_zk = df_bug_all[df_bug_all['领域'].isin(group_zk)]
df_dev_zk_duration = df_bug_zk.copy(deep=True)
print('df_dev_zk_duration------',df_dev_zk_duration.shape)
if len(df_dev_zk_duration)>0:
df_dev_zk_duration.loc[:, '已解单开发环节时长'] = df_dev_zk_duration.loc[:, '已解单开发环节时长'].apply(lambda x:x.days)
gb_dev_zk_duration = df_dev_zk_duration[['严重程度','已解单开发环节时长']].groupby(['严重程度',]).mean()
gb_dev_zk_duration_group = df_dev_zk_duration[['严重程度','领域','已解单开发环节时长']].groupby(['严重程度','领域']).mean()
gb_dev_zk_duration_group2 = df_dev_zk_duration[['严重程度','领域','已解单开发环节时长']].groupby(['领域','严重程度',]).mean()
# duration zk
gb_dev_zk_duration.to_excel(writer, sheet_name='zk已解单开发环节时长', index=True)
gb_dev_zk_duration_group.to_excel(writer, sheet_name='zk已解单开发环节时长group', index=True)
gb_dev_zk_duration_group2.to_excel(writer, sheet_name='zk已解单开发环节时长group2', index=True)
pt_dev_zk_duration_group = pd.pivot_table(df_dev_zk_duration, index=['领域', ], columns=['严重程度'], \
values=['已解单开发环节时长'], aggfunc=[np.mean], margins = True)
pt_dev_zk_duration_group.columns = pt_dev_zk_duration_group.columns.droplevel(0)
pt_dev_zk_duration_group.columns = pt_dev_zk_duration_group.columns.droplevel(0)
if set(['Critical','Major','Normal','Minor','All']) == set(pt_dev_zk_duration_group.columns):
pt_dev_zk_duration_group = pt_dev_zk_duration_group[['Critical','Major','Normal','Minor','All']]
pt_dev_zk_duration_group = pt_dev_zk_duration_group.sort_values(by=['Critical','Major'], ascending=[False,False]).round(2)
pt_dev_zk_duration_group = pt_dev_zk_duration_group.rename(index={'All':'领域平均'}, columns = {'All':'问题单平均'})
pt_dev_zk_duration_group.to_excel(writer, sheet_name='zk已解单开发环节时长group_pt', index=True)
# ========================= 解单时长 notag ========================
df_dev_zk_duration_notag = df_bug_zk[~df_bug_zk['标签'].astype(str).str[:4].str.contains('依赖')]
print('df_dev_zk_duration_notag------',df_dev_zk_duration_notag.shape)
if len(df_dev_zk_duration_notag)>0:
df_dev_zk_duration_notag.loc[:, '已解单开发环节时长'] = df_dev_zk_duration_notag.loc[:, '已解单开发环节时长'].apply(lambda x:x.days)
gb_dev_zk_duration_notag = df_dev_zk_duration_notag[['严重程度','已解单开发环节时长']].groupby(['严重程度',]).mean()
gb_dev_zk_duration_notag_group = df_dev_zk_duration_notag[['严重程度','领域','已解单开发环节时长']].groupby(['严重程度','领域']).mean()
gb_dev_zk_duration_notag_group2 = df_dev_zk_duration_notag[['严重程度','领域','已解单开发环节时长']].groupby(['领域','严重程度',]).mean()
# duration notag
gb_dev_zk_duration_notag.to_excel(writer, sheet_name='zk已解单开发环节时长_notag', index=True)
gb_dev_zk_duration_notag_group.to_excel(writer, sheet_name='zk已解单开发环节时长group_notag', index=True)
gb_dev_zk_duration_notag_group2.to_excel(writer, sheet_name='zk已解单开发环节时长group2_notag', index=True)
# pivot
pt_dev_zk_duration_notag_group = pd.pivot_table(df_dev_zk_duration_notag, index=['领域', ], columns=['严重程度'], \
values=['已解单开发环节时长'], aggfunc=[np.mean], margins = True)
pt_dev_zk_duration_notag_group.columns = pt_dev_zk_duration_notag_group.columns.droplevel(0)
pt_dev_zk_duration_notag_group.columns = pt_dev_zk_duration_notag_group.columns.droplevel(0)
if set(['Critical','Major','Normal','Minor','All']) == set(pt_dev_zk_duration_notag_group.columns):
pt_dev_zk_duration_notag_group = pt_dev_zk_duration_notag_group[['Critical','Major','Normal','Minor','All']]
pt_dev_zk_duration_notag_group = pt_dev_zk_duration_notag_group.sort_values(by=['Critical','Major'], ascending=[False,False]).round(2)
pt_dev_zk_duration_notag_group = pt_dev_zk_duration_notag_group.rename(index={'All':'领域平均'}, columns = {'All':'问题单平均'})
pt_dev_zk_duration_notag_group.to_excel(writer, sheet_name='zk已解单开发环节时长group_pt_notag', index=True)
# ========================= 解单时长 tag ========================
df_dev_zk_duration_tag = df_bug_zk[df_bug_zk['标签'].astype(str).str[:4].str.contains('依赖')]
print('df_dev_zk_duration_tag------',df_dev_zk_duration_tag.shape)
if len(df_dev_zk_duration_tag)>0:
df_dev_zk_duration_tag.loc[:, '已解单开发环节时长'] = df_dev_zk_duration_tag.loc[:, '已解单开发环节时长'].apply(lambda x:x.days)
gb_dev_zk_duration_tag = df_dev_zk_duration_tag[['严重程度','已解单开发环节时长']].groupby(['严重程度',]).mean()
gb_dev_zk_duration_tag_group = df_dev_zk_duration_tag[['严重程度','领域','已解单开发环节时长']].groupby(['严重程度','领域']).mean()
gb_dev_zk_duration_tag_group2 = df_dev_zk_duration_tag[['严重程度','领域','已解单开发环节时长']].groupby(['领域','严重程度',]).mean()
# duration tag
gb_dev_zk_duration_tag.to_excel(writer, sheet_name='zk已解单开发环节时长_tag', index=True)
gb_dev_zk_duration_tag_group.to_excel(writer, sheet_name='zk已解单开发环节时长group_tag', index=True)
gb_dev_zk_duration_tag_group2.to_excel(writer, sheet_name='zk已解单开发环节时长group2_tag', index=True)
if len(df_dev_zk_duration_tag)>1:
pt_dev_zk_duration_tag_group = pd.pivot_table(df_dev_zk_duration_tag, index=['领域', ], columns=['严重程度'], \
values=['已解单开发环节时长'], aggfunc=[np.mean], margins = True)
pt_dev_zk_duration_tag_group.columns = pt_dev_zk_duration_tag_group.columns.droplevel(0)
pt_dev_zk_duration_tag_group.columns = pt_dev_zk_duration_tag_group.columns.droplevel(0)
if set(['Critical','Major','Normal','Minor','All']) == set(pt_dev_zk_duration_tag_group.columns):
pt_dev_zk_duration_tag_group = pt_dev_zk_duration_tag_group[['Critical','Major','Normal','Minor','All']]
pt_dev_zk_duration_tag_group = pt_dev_zk_duration_tag_group.sort_values(by=['Critical','Major'], ascending=[False,False]).round(2)
pt_dev_zk_duration_tag_group = pt_dev_zk_duration_tag_group.rename(index={'All':'领域平均'}, columns = {'All':'问题单平均'})
pt_dev_zk_duration_tag_group.to_excel(writer, sheet_name='zk已解单开发环节时长group_pt_tag', index=True)
# ================计算是否超期============填写当前日期和周期==============================================
df_open = df_bug_percent.copy(deep=True)
# 开发环节
# df_open_dev = df_open[df_open['BUG状态'].isin(['待指派','待分析','待分析审核','待修改','待修改审核','待临时验证','待归档',])]
# df_open_dev = df_open[df_open['BUG状态'].isin(['待分析','待修改','待修改审核','待归档','待CCB'])]
# df_open_dev = df_bug_all[df_bug_all['BUG状态'].isin(['待分析','待修改','待修改审核','待归档','待CCB'])]
# 待归档不计入开发环节单
df_open_dev = df_bug_all[df_bug_all['BUG状态'].isin(bug_status_dev)]
print('df_open_dev.shape ', df_open_dev.shape)
df_open_dev.loc[:, '当前时间'] = datetime.datetime.today()
# df_open_dev.loc[:, '当前时间'] = select_date
df_open_dev.loc[:, '缺陷指派时间'] = pd.to_datetime(df_open_dev.loc[:, '缺陷指派时间'])
df_open_dev.loc[:, '待解单开发滞留时长'] = df_open_dev.loc[:, '当前时间'] - df_open_dev.loc[:, '缺陷指派时间']
df_open_dev.loc[:, '待解单开发滞留时长'] = df_open_dev.loc[:, '待解单开发滞留时长'].apply(lambda x:x.days)
# ============================计算超期=============================
for index in df_open_dev.index:
if df_open_dev.loc[index,'严重程度'] in ['Blocker','Critical','Major']:
if df_open_dev.loc[index, '待解单开发滞留时长'] < 7: #2
df_open_dev.loc[index, '是否超期'] = '否'
else:
df_open_dev.loc[index, '是否超期'] = '是'
if df_open_dev.loc[index,'严重程度'] == ('Normal'):
if df_open_dev.loc[index, '待解单开发滞留时长'] < 7: #4
df_open_dev.loc[index, '是否超期'] = '否'
else:
df_open_dev.loc[index, '是否超期'] = '是'
if df_open_dev.loc[index,'严重程度'] == ('Minor'):
if df_open_dev.loc[index, '待解单开发滞留时长'] < 7: #5
df_open_dev.loc[index, '是否超期'] = '否'
else:
df_open_dev.loc[index, '是否超期'] = '是'
# Blocker + Critical
df_open_dev_BC = df_open_dev[df_open_dev['严重程度'].isin(['Blocker','Critical',])]
print('df_open_dev_BC.shape ', df_open_dev_BC.shape)
pt_open_dev_BC = stay_time(df_open_dev_BC)
# if len(df_open_dev_BC) > 0:
# pt_open_dev_BC = pd.pivot_table(df_open_dev_BC, index=['领域', '领域主管'], columns=['BUG状态'], \
# values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# pt_open_dev_BC.columns = pt_open_dev_BC.columns.droplevel(0)
# pt_open_dev_BC.columns = pt_open_dev_BC.columns.droplevel(0)
# pt_open_dev_BC = pt_open_dev_BC.sort_values(by=['All'], ascending=[False])
# df_open_dev_BC_gp = df_open_dev_BC.groupby(['领域',])[['待解单开发滞留时长',]].mean()
# pt_open_dev_BC = pd.merge(pt_open_dev_BC, df_open_dev_BC_gp, how='outer', left_index=True, right_index=True)\
# .fillna(df_open_dev_BC['待解单开发滞留时长'].mean())
# pt_open_dev_BC['待解单开发滞留时长'] = pt_open_dev_BC['待解单开发滞留时长'].apply('{:.2f}'.format).astype(float)
# pt_open_dev_BC = pt_open_dev_BC.sort_values(by=['All', '待解单开发滞留时长',], ascending=[False, False])
# Major
df_open_dev_Major = df_open_dev[df_open_dev['严重程度'].isin(['Major',])]
print('df_open_dev_Major.shape ', df_open_dev_Major.shape)
pt_open_dev_Major = stay_time(df_open_dev_Major)
# no 依赖
df_open_dev_BC_noH = df_open_dev_BC[~df_open_dev_BC['标签'].astype(str).str[:4].str.contains('依赖')]
pt_open_dev_BC_noH = stay_time(df_open_dev_BC_noH)
df_open_dev_Major_noH = df_open_dev_Major[~df_open_dev_Major['标签'].astype(str).str[:4].str.contains('依赖')]
pt_open_dev_Major_noH = stay_time(df_open_dev_Major_noH)
# if len(df_open_dev_Major) > 0:
# pt_open_dev_Major = pd.pivot_table(df_open_dev_Major, index=['领域', '领域主管'], columns=['BUG状态'], \
# values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# pt_open_dev_Major.columns = pt_open_dev_Major.columns.droplevel(0)
# pt_open_dev_Major.columns = pt_open_dev_Major.columns.droplevel(0)
# pt_open_dev_Major = pt_open_dev_Major.sort_values(by=['All'], ascending=[False])
# df_open_dev_Major_gp = df_open_dev_Major.groupby(['领域',])[['待解单开发滞留时长',]].mean()
# pt_open_dev_Major = pd.merge(pt_open_dev_Major, df_open_dev_Major_gp, how='outer', left_index=True, right_index=True)\
# .fillna(df_open_dev_Major['待解单开发滞留时长'].mean())
# pt_open_dev_Major['待解单开发滞留时长'] = pt_open_dev_Major['待解单开发滞留时长'].apply('{:.2f}'.format).astype(float)
# pt_open_dev_Major = pt_open_dev_Major.sort_values(by=['All', '待解单开发滞留时长',], ascending=[False, False])
# Blocker + Critical ZK
df_open_dev_BC_ZK = df_open_dev[df_open_dev['严重程度'].isin(['Blocker','Critical',])]
print('df_open_dev_BC_ZK.shape ', df_open_dev_BC_ZK.shape)
df_open_dev_BC_ZK = df_open_dev_BC_ZK[df_open_dev_BC_ZK['合作方'].isin(['ZK',])]
print('df_open_dev_BC_ZK.shape ', df_open_dev_BC_ZK.shape)
pt_open_dev_BC_ZK = stay_time(df_open_dev_BC_ZK)
# if len(df_open_dev_BC_ZK) > 0:
# pt_open_dev_BC_ZK = pd.pivot_table(df_open_dev_BC_ZK, index=['领域', '领域主管'], columns=['BUG状态'], \
# values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# pt_open_dev_BC_ZK.columns = pt_open_dev_BC_ZK.columns.droplevel(0)
# pt_open_dev_BC_ZK.columns = pt_open_dev_BC_ZK.columns.droplevel(0)
# pt_open_dev_BC_ZK = pt_open_dev_BC_ZK.sort_values(by=['All'], ascending=[False])
# df_open_dev_BC_ZK_gp = df_open_dev_BC_ZK.groupby(['领域',])[['待解单开发滞留时长',]].mean()
# pt_open_dev_BC_ZK = pd.merge(pt_open_dev_BC_ZK, df_open_dev_BC_ZK_gp, how='outer', left_index=True, right_index=True)\
# .fillna(df_open_dev_BC_ZK['待解单开发滞留时长'].mean())
# pt_open_dev_BC_ZK['待解单开发滞留时长'] = pt_open_dev_BC_ZK['待解单开发滞留时长'].apply('{:.2f}'.format).astype(float)
# pt_open_dev_BC_ZK = pt_open_dev_BC_ZK.sort_values(by=['All', '待解单开发滞留时长',], ascending=[False, False])
# Major ZK
df_open_dev_Major_ZK = df_open_dev[df_open_dev['严重程度'].isin(['Major',])]
print('df_open_dev_Major_ZK.shape ', df_open_dev_Major_ZK.shape)
df_open_dev_Major_ZK = df_open_dev_Major_ZK[df_open_dev_Major_ZK['合作方'] == 'ZK']
print('df_open_dev_Major_ZK.shape ', df_open_dev_Major_ZK.shape)
pt_open_dev_Major_ZK = stay_time(df_open_dev_Major_ZK)
# if len(df_open_dev_Major_ZK) > 0:
# pt_open_dev_Major_ZK = pd.pivot_table(df_open_dev_Major_ZK, index=['领域', '领域主管'], columns=['BUG状态'], \
# values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# pt_open_dev_Major_ZK.columns = pt_open_dev_Major_ZK.columns.droplevel(0)
# pt_open_dev_Major_ZK.columns = pt_open_dev_Major_ZK.columns.droplevel(0)
# pt_open_dev_Major_ZK = pt_open_dev_Major_ZK.sort_values(by=['All'], ascending=[False])
# # print(pt_open_dev_Major_ZK)
# df_open_dev_Major_ZK_gp = df_open_dev_Major_ZK.groupby(['领域',])[['待解单开发滞留时长',]].mean()
# pt_open_dev_Major_ZK = pd.merge(pt_open_dev_Major_ZK, df_open_dev_Major_ZK_gp, how='outer', left_index=True, right_index=True)\
# .fillna(df_open_dev_Major_ZK['待解单开发滞留时长'].mean())
# pt_open_dev_Major_ZK['待解单开发滞留时长'] = pt_open_dev_Major_ZK['待解单开发滞留时长'].apply('{:.2f}'.format).astype(float)
# pt_open_dev_Major_ZK = pt_open_dev_Major_ZK.sort_values(by=['All', '待解单开发滞留时长',], ascending=[False, False])
# all time
df_open_dev_All = df_open_dev
pt_open_dev_All = stay_time(df_open_dev_All)
# if len(df_open_dev_All) > 0:
# pt_open_dev_All = pd.pivot_table(df_open_dev_All, index=['领域', '领域主管'], columns=['BUG状态'], \
# values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# pt_open_dev_All.columns = pt_open_dev_All.columns.droplevel(0)
# pt_open_dev_All.columns = pt_open_dev_All.columns.droplevel(0)
# pt_open_dev_All = pt_open_dev_All.sort_values(by=['All'], ascending=[False])
# df_open_dev_All_gp = df_open_dev_All.groupby(['领域',])[['待解单开发滞留时长',]].mean()
# pt_open_dev_All = pd.merge(pt_open_dev_All, df_open_dev_All_gp, how='outer', left_index=True, right_index=True)\
# .fillna(df_open_dev_All['待解单开发滞留时长'].mean())
# pt_open_dev_All['待解单开发滞留时长'] = pt_open_dev_All['待解单开发滞留时长'].apply('{:.2f}'.format).astype(float)
# pt_open_dev_All = pt_open_dev_All.sort_values(by=['待解单开发滞留时长','All'], ascending=[False, False])
# 计算11月16号前指派单的遗留DI
# time_DI_clean = '2021-11-17 00:00:00'
time_DI_clean = '2021-11-25 00:00:00'
df_bug_open_dev_1116 = df_bug_open_dev[df_bug_open_dev['缺陷指派时间'] < time_DI_clean]
print('df_bug_open_dev_1116----------', df_bug_open_dev_1116.shape)
df_bug_open_dev_1116_noH = df_bug_open_dev_1116[~df_bug_open_dev_1116['标签'].astype(str).str[:4].str.contains('依赖')]
print('df_bug_open_dev_1116_noH----------', df_bug_open_dev_1116_noH.shape)
if len(df_bug_open_dev_1116_noH) > 0:
pt_bug_open_dev_1116_noH = pd.pivot_table(df_bug_open_dev_1116_noH, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_dev_1116_noH)
pt_bug_open_dev_1116_noH.sort_values(by=['DI', 'All'], ascending=[False, False], inplace=True)
pt_bug_open_dev_1116_noH_status = pd.pivot_table(df_bug_open_dev_1116_noH, index=['领域_当前', '领域主管_当前'], columns=['BUG状态'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_open_dev_1116_noH_status.columns = pt_bug_open_dev_1116_noH_status.columns.droplevel(0)
pt_bug_open_dev_1116_noH_status.columns = pt_bug_open_dev_1116_noH_status.columns.droplevel(0)
if len(df_bug_open_dev_1116_noH) > 0:
# pt_bug_open_current_DI_man.index.names = ['领域', '人员']
pt_bug_open_dev_1116_noH = pd.merge(pt_bug_open_dev_1116_noH_status, pt_bug_open_dev_1116_noH, \
how='left', left_on=['领域_当前', '领域主管_当前'], right_on=['领域_当前', '领域主管_当前']).fillna(0)
pt_bug_open_dev_1116_noH.sort_values(by=['DI',], ascending=[False, ], inplace=True)
# ------------------------ Start of Rush BUG --------------------------------
time_DI_clean_Rush = '2021-12-27 00:00:00'
# df_bug_open_state_Rush
df_bug_open_state = df_bug_open[df_bug_open['BUG状态'].isin(['待提交','待指派','待回归验证','待分析','待分析审核','待修改','待修改审核','待归档','待CCB'])]
print('df_bug_open_state-------',df_bug_open_state.shape)
df_bug_open_state_Rush = df_bug_open_state[df_bug_open_state['发现时间'] < time_DI_clean_Rush]
print('df_bug_open_state-------',df_bug_open_state.shape)
df_bug_open_state_Rush = df_bug_open_state[df_bug_open_state['缺陷指派时间'] < time_DI_clean_Rush]
print('df_bug_open_state_Rush-------',df_bug_open_state_Rush.shape)
# df_bug_open_state_Rush = df_bug_open_state[df_bug_open_state['解单时间'] < time_DI_clean_Rush]
# print('df_bug_open_state_Rush-------',df_bug_open_state_Rush.shape)
if len(df_bug_open_state_Rush) >0:
pt_bug_open_state_Rush = pd.pivot_table(df_bug_open_state_Rush, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_state_Rush)
pt_bug_open_state_Rush.sort_values(by=['DI', 'All'], ascending=[False, False], inplace=True)
pt_bug_open_state_Rush_status = pd.pivot_table(df_bug_open_state_Rush, index=['领域_当前', '领域主管_当前'], columns=['BUG状态'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_open_state_Rush_status.columns = pt_bug_open_state_Rush_status.columns.droplevel(0)
pt_bug_open_state_Rush_status.columns = pt_bug_open_state_Rush_status.columns.droplevel(0)
if len(df_bug_open_state_Rush) > 0:
pt_bug_open_state_Rush = pd.merge(pt_bug_open_state_Rush_status, pt_bug_open_state_Rush, \
how='left', left_on=['领域_当前', '领域主管_当前'], right_on=['领域_当前', '领域主管_当前']).fillna(0)
pt_bug_open_state_Rush.sort_values(by=['DI',], ascending=[False, ], inplace=True)
if 'Blocker' in pt_bug_open_state_Rush.columns:
if pt_bug_open_state_Rush['Blocker'].sum() == 0:
pt_bug_open_state_Rush = pt_bug_open_state_Rush.drop(columns='Blocker')
# df_bug_open_dev_Rush
df_bug_open_dev_Rush_verify = df_bug_open[df_bug_open['BUG状态'].isin(['待分析','待分析审核','待修改','待修改审核','待CCB'])]
print('df_bug_open_dev_Rush_verify-------',df_bug_open_dev_Rush_verify.shape)
df_bug_open_dev_Rush_verify = df_bug_open_dev_Rush_verify[df_bug_open_dev_Rush_verify['缺陷指派时间'] < time_DI_clean_Rush]
# df_bug_open_dev_Rush = df_bug_open_dev[df_bug_open_dev['发现时间'] < time_DI_clean_Rush]
df_bug_open_dev_Rush = df_bug_open_dev[df_bug_open_dev['缺陷指派时间'] < time_DI_clean_Rush]
print('df_bug_open_dev_Rush----------', df_bug_open_dev_Rush.shape)
if len(df_bug_open_dev_Rush) > 0:
pt_bug_open_dev_Rush = pd.pivot_table(df_bug_open_dev_Rush, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_open_dev_Rush)
pt_bug_open_dev_Rush.sort_values(by=['DI', 'All'], ascending=[False, False], inplace=True)
pt_bug_open_dev_Rush_status = pd.pivot_table(df_bug_open_dev_Rush, index=['领域_当前', '领域主管_当前'], columns=['BUG状态'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_open_dev_Rush_status.columns = pt_bug_open_dev_Rush_status.columns.droplevel(0)
pt_bug_open_dev_Rush_status.columns = pt_bug_open_dev_Rush_status.columns.droplevel(0)
if len(df_bug_open_dev_Rush) > 0:
pt_bug_open_dev_Rush = pd.merge(pt_bug_open_dev_Rush_status, pt_bug_open_dev_Rush, \
how='left', left_on=['领域_当前', '领域主管_当前'], right_on=['领域_当前', '领域主管_当前']).fillna(0)
pt_bug_open_dev_Rush.sort_values(by=['DI',], ascending=[False, ], inplace=True)
pt_bug_open_dev_Rush.index.names = ['领域', '领域主管']
if len(pt_bug_percent_yesterday_assign_group) > 0:
pt_bug_open_dev_Rush = pd.merge(pt_bug_open_dev_Rush, pt_bug_percent_yesterday_assign_group[['昨天指派','昨天指派DI']], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if len(pt_bug_percent_yesterday_solve_group) > 0:
pt_bug_open_dev_Rush = pd.merge(pt_bug_open_dev_Rush, pt_bug_percent_yesterday_solve_group[['昨天解单','昨天解单DI']], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if len(pt_bug_percent_today_assign_group) > 0:
pt_bug_open_dev_Rush = pd.merge(pt_bug_open_dev_Rush, pt_bug_percent_today_assign_group[['当天指派','当天指派DI']], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if len(pt_bug_percent_today_solve_group) > 0:
pt_bug_open_dev_Rush = pd.merge(pt_bug_open_dev_Rush, pt_bug_percent_today_solve_group[['当天解单','当天解单DI']], \
how='left', left_on=['领域', '领域主管'], right_on=['领域', '领域主管']).fillna(0)
if 'Blocker' in pt_bug_open_dev_Rush.columns:
if pt_bug_open_dev_Rush['Blocker'].sum() == 0:
pt_bug_open_dev_Rush = pt_bug_open_dev_Rush.drop(columns='Blocker')
if len(df_bug_open_dev_Rush) > 0:
# Rush man------------------------------------------------------------
pt_bug_open_dev_Rush_man = pd.pivot_table(df_bug_open_dev_Rush, index=['领域_当前', '人员_当前'], columns=['BUG状态'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_open_dev_Rush_man.columns = pt_bug_open_dev_Rush_man.columns.droplevel(0)
pt_bug_open_dev_Rush_man.columns = pt_bug_open_dev_Rush_man.columns.droplevel(0)
pt_bug_open_dev_Rush_man.rename(columns={'All':'全部状态',}, inplace=True)
pt_bug_open_dev_Rush_man.sort_values(by=['全部状态'], ascending=[False], inplace=True)
# ------------------------ End of Rush BUG --------------------------------
# ---------------------------bug delay ------------------------------
df_bug_overtime_dev = df_open_dev[df_open_dev['是否超期'] == '是']
# df_bug_overtime_dev = df_bug_overtime_dev[df_bug_overtime_dev['严重程度'].isin(['Blocker','Critical','Major'])]
print('df_bug_overtime_dev.shape ', df_bug_overtime_dev.shape)
# group
if len(df_bug_overtime_dev) > 0:
pt_bug_overtime_dev_group = pd.pivot_table(df_bug_overtime_dev, index=['严重程度', '领域_当前', ], columns=['BUG状态'], \
values=['当前责任人'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_overtime_dev_group.columns', pt_bug_overtime_dev_group.columns)
pt_bug_overtime_dev_group.columns = pt_bug_overtime_dev_group.columns.droplevel(0)
pt_bug_overtime_dev_group.columns = pt_bug_overtime_dev_group.columns.droplevel(0)
# print('pt_bug_overtime_dev_group.columns', pt_bug_overtime_dev_group.columns)
# pt_bug_overtime_dev_group.rename(columns={'All':'全部状态',}, inplace=True)
bug_case(pt_bug_overtime_dev_group)
# 计算“开发环节单”类别
pt_bug_overtime_dev_group['open开发超期单'] = pt_bug_overtime_dev_group['待分析'] \
+ pt_bug_overtime_dev_group['待修改'] + pt_bug_overtime_dev_group['待修改审核'] \
+ pt_bug_overtime_dev_group['待CCB']
pt_bug_overtime_dev_group_order = ['待分析','待修改','待修改审核','待CCB','open开发超期单',]
pt_bug_overtime_dev_group = pt_bug_overtime_dev_group[pt_bug_overtime_dev_group_order]
# pt_bug_overtime_dev_group.sort_values(by='open开发超期单', ascending=False, inplace=True)
pt_bug_overtime_dev_group.sort_values(by=['严重程度', 'open开发超期单'], ascending=[True, False], inplace=True)
# ==================== group ===================
# pt_bug_overtime_dev_DI_group = pd.pivot_table(df_bug_overtime_dev, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
# values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# pt_bug_overtime_dev_DI_group = pd.pivot_table(df_bug_overtime_dev, index=['严重程度', '领域_当前', ], columns=['bug状态'], \
# values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_overtime_dev_DI_group = pd.pivot_table(df_bug_overtime_dev, index=['领域_当前', '领域主管_当前'], columns=['严重程度'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_overtime_dev_DI_group)
pt_bug_overtime_dev_DI_group.reset_index(inplace=True)
pt_bug_overtime_dev_DI_group.index.name = '序号'
# man
if len(df_bug_overtime_dev) > 0:
pt_bug_overtime_dev_man = pd.pivot_table(df_bug_overtime_dev, index=['严重程度', '当前责任人'], columns=['BUG状态'], \
values=['人员_当前'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
pt_bug_overtime_dev_man.rename(columns={'All':'全部状态',}, inplace=True)
# print('123 pt_bug_overtime_dev_man.columns', pt_bug_overtime_dev_man.columns)
pt_bug_overtime_dev_man.columns = pt_bug_overtime_dev_man.columns.droplevel(0)
pt_bug_overtime_dev_man.columns = pt_bug_overtime_dev_man.columns.droplevel(0)
# print('456 pt_bug_overtime_dev_man.columns', pt_bug_overtime_dev_man.columns)
bug_case(pt_bug_overtime_dev_man)
pt_bug_overtime_dev_man['open开发超期单'] = pt_bug_overtime_dev_man['待分析'] \
+ pt_bug_overtime_dev_man['待修改'] + pt_bug_overtime_dev_man['待修改审核'] \
+ pt_bug_overtime_dev_man['待CCB']
pt_bug_overtime_dev_man_order = ['待分析','待修改','待修改审核','待CCB','open开发超期单',]
pt_bug_overtime_dev_man = pt_bug_overtime_dev_man[pt_bug_overtime_dev_man_order]
pt_bug_overtime_dev_man.sort_values(by=['严重程度', 'open开发超期单'], ascending=[True, False], inplace=True)
# pt_bug_overtime_dev_DI_man
pt_bug_overtime_dev_DI_man = pd.pivot_table(df_bug_overtime_dev, index=['领域', '当前责任人'], columns=['严重程度'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
DI_process(pt_bug_overtime_dev_DI_man)
pt_bug_overtime_dev_DI_man.index.name = '序号'
print('Process Done 开发超期DI')
# -----------------------End of process----------------------
# tk.messagebox.showinfo(title='提示',message='End of process!')
# ------------------------Start of 效率-------------------------
# ====================================
pt_bug_man = pd.pivot_table(df_bug_all, index=['解单人'], columns=['BUG状态'], \
values=['人员'], aggfunc=[np.count_nonzero], margins = True).fillna(0)
# print('pt_bug_percent_man.columns', pt_bug_percent_man.columns)
pt_bug_man.columns = pt_bug_man.columns.droplevel(0)
pt_bug_man.columns = pt_bug_man.columns.droplevel(0)
bug_case(pt_bug_man)
pt_bug_man = pt_bug_man.reset_index(drop=False)
pt_bug_man.sort_values(by=['All', '解单人'], ascending=[False, False], inplace=True)
excel_reader = pd.ExcelFile(vlookup_path) # 指定文件
sheet_names = excel_reader.sheet_names # 读取文件的所有表单名，得到列表
# df_data = excel_reader.parse(sheet_name=sheet_names[i]) # 读取表单的内容，i是表单名的索引，等价于pd.read_excel('文件.xls
# df_vl_efficiency = pd.read_excel(vlookup_path, sheet_name = 'df_vl_efficiency', engine = 'openpyxl')
if 'df_vl_efficiency' in sheet_names:
df_vl_efficiency = pd.read_excel(vlookup_path, sheet_name = 'df_vl_efficiency', engine = 'openpyxl')
df_vl_efficiency_add = pd.merge(df_vl_efficiency, pt_bug_man.loc[:,['解单人','All']], \
how='left', left_on='人员', right_on='解单人')
df_vl_efficiency_add = df_vl_efficiency_add.rename(columns={'All':'解单数'})
# ------------------------End of 效率-------------------------
#
exit() if (time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())) > '2022-12-01 00:00:00') else print('ok')
#
# # 同一表格写入多个sheet页
# # writer = pd.ExcelWriter(write_path, engine='openpyxl')
# # tk.messagebox.showinfo(title='提示',message='Start writer!')
# # writer = pd.ExcelWriter(write_path, engine='xlwt')
# with pd.ExcelWriter(write_path, engine='xlwt') as writer:
df_req_all.to_excel(writer, sheet_name='df_req_all', index=None)
# pt_req_emergency_group.to_excel(writer, sheet_name='新需求状态', index=True)
# # pt_req_kanban_emergency_group.to_excel(writer, sheet_name='新需求开发完成率', index=True)
# pt_req_TR4_group.to_excel(writer, sheet_name='TR4需求状态', index=True)
# pt_req_TR4_man.to_excel(writer, sheet_name='TR4需求状态_man', index=True)
# pt_req_kanban_TR4_group.to_excel(writer, sheet_name='TR4需求开发完成率', index=True)
# pt_req_kanban_TR4_man.to_excel(writer, sheet_name='TR4需求未完成-个人-降序', index=True)
pt_req_TR4_TR4A_group.to_excel(writer, sheet_name='TR4A需求状态', index=True)
pt_req_kanban_TR4_TR4A_group.to_excel(writer, sheet_name='TR4A需求完成率', index=True)
pt_req_kanban_TR4_TR4A_man = pt_req_kanban_TR4_TR4A_man.reset_index()
pt_req_kanban_TR4_TR4A_man.to_excel(writer, sheet_name='TR4A需求-个人', index=True)
pt_req_kanban_TR4_TR4A_SR.to_excel(writer, sheet_name='TR4A_SR', index=True)
# pt_req_kanban_TR5_group.to_excel(writer, sheet_name='TR5', index=True)
# pt_req_kanban_TR5_SR.to_excel(writer, sheet_name='TR5_SR', index=True)
# # 过点前3天DI值
# pt_bug_open_state_Rush.to_excel(writer, sheet_name='Rush_DI_全量', index=True)
# pt_bug_open_dev_Rush.to_excel(writer, sheet_name='Rush_DI_开发组', index=True)
# pt_bug_open_dev_Rush_man.to_excel(writer, sheet_name='Rush_DI_开发人', index=True)
# 二供
# df_bug_bigdata.to_excel(writer, sheet_name='大数据', index=None)
# df_1and2g.to_excel(writer, sheet_name='df_1and2g', index=None)
pt_bug_open_current_group_status_DI.to_excel(writer, sheet_name='全部未关闭单DI', index=True)
# if len(pt_bug_percent_DI_group_p5.index) > 0:
# pt_bug_percent_DI_group_p5.to_excel(writer, sheet_name='DI_p5', index=True)
# if len(pt_bug_open_current_DI_group_c5.index) > 0:
# pt_bug_open_current_DI_group_c5.to_excel(writer, sheet_name='DI_c5', index=True)
# if len(pt_bug_percent_DI_group_p5_p3.index) > 0:
# pt_bug_percent_DI_group_p5_p3.to_excel(writer, sheet_name='DI_p3', index=True)
# if len(pt_bug_open_current_DI_group_c3.index) > 0:
# pt_bug_open_current_DI_group_c3.to_excel(writer, sheet_name='DI_c3', index=True)
# if len(df_bug_open_DI_group_ccb2) > 0:
# pt_bug_open_current_DI_group_c2_ccb.to_excel(writer, sheet_name='DI_c2_ccb', index=True)
if pd.to_datetime(datetime.date.today()) in df_di_everyday_all.columns:
df_di_everyday_all.to_excel(writer, sheet_name='领域目标DI-everyday', index=True)
pt_bug_open_dev_group_total_everyday.to_excel(writer, sheet_name='开发未解DI-everyday', index=True)
pt_bug_open_dev_group_total.to_excel(writer, sheet_name='开发未解DI-领域', index=True)
pt_bug_open_current_man_total.to_excel(writer, sheet_name='开发未解DI-个人', index=True)
if len(df_bug_open_dev_scm) > 0:
pt_bug_open_dev_scm_group.to_excel(writer, sheet_name='待归档DI', index=True)
df_bug_all.to_excel(writer, sheet_name='bug_all', index=True)
# df_bug_week.to_excel(writer, sheet_name='bug_week', index=True)
pt_bug_percent_group_total.to_excel(writer, sheet_name='领域percent', index=True)
pt_bug_percent_total_man = pt_bug_percent_total_man.reset_index()
pt_bug_percent_total_man.to_excel(writer, sheet_name='个人percent', index=True)
df_daily_all_DI.to_excel(writer, sheet_name='每日单', index=True)
df_open_dev_BC.to_excel(writer, sheet_name='df_open_dev_BC', index=True)
if len(df_open_dev_BC) > 0:
pt_open_dev_BC.to_excel(writer, sheet_name='待解单(Blocker+Critical)', index=True)
if len(df_open_dev_BC_ZK) > 0:
pt_open_dev_BC_ZK.to_excel(writer, sheet_name='待解单(BC_ZK)', index=True)
if len(df_open_dev_BC_noH) > 0:
pt_open_dev_BC_noH.to_excel(writer, sheet_name='待解单(BC_noH)', index=True)
'''
if len(df_open_dev_BC) > 0:
format_dict = {
'待修改':'{0:.0f}',
'待修改审核':'{0:.0f}',
'待分析':'{0:.0f}',
'All':'{0:.0f}',
'待解单开发滞留时长':'{0:.2f}'
}
pt_open_dev_BC_style = pt_open_dev_BC.style.applymap(lambda x: 'color:red', subset=["All"]) \
.applymap(lambda x: 'color:green', subset=["待解单开发滞留时长"]) \
.background_gradient(text_color_threshold=0.3,cmap="PuBu", low=0, high=0.5, subset=["待解单开发滞留时长"])\
.highlight_max(axis=1,subset=['All'],color='yellow')\
.format(format_dict)
# .bar(color='lightgreen', vmin=0, subset=['待修改'], align='zero')
pt_open_dev_BC_style.to_excel(writer, sheet_name='待解单(Blocker+Critical)', index=True)
'''
df_open_dev_Major.to_excel(writer, sheet_name='df_open_dev_Major', index=True)
if len(df_open_dev_Major) > 0:
pt_open_dev_Major.to_excel(writer, sheet_name='待解单(Major)', index=True)
if len(df_open_dev_Major_ZK) > 0:
pt_open_dev_Major_ZK.to_excel(writer, sheet_name='待解单(Major_ZK)', index=True)
if len(df_open_dev_Major_noH) > 0:
pt_open_dev_Major_noH.to_excel(writer, sheet_name='待解单(Major_noH)', index=True)
if len(df_bug_tag_dev_open) > 0:
df_bug_tag.to_excel(writer, sheet_name='df_依赖all', index=True)
pt_bug_tag_stay_all.to_excel(writer, sheet_name='pt_依赖all', index=True)
pt_bug_tag_dev_DI_group.to_excel(writer, sheet_name='依赖dev_DI', index=True)
pt_bug_tag_dev_group.to_excel(writer, sheet_name='依赖dev_group', index=True)
pt_bug_tag_dev_man.to_excel(writer, sheet_name='依赖dev_man', index=True)
pt_bug_tag_stay_dev.to_excel(writer, sheet_name='依赖dev_stay', index=True)
if len(df_bug_open_dev_1116) > 0:
df_bug_open_dev_1116.to_excel(writer, sheet_name='上周三前指派单dev', index=True)
pt_bug_open_dev_1116_noH.to_excel(writer, sheet_name='上周三前遗留DI无依赖', index=True)
'''
if len(df_open_dev_Major) > 0:
format_dict = {
'待修改':'{0:.0f}',
'待修改审核':'{0:.0f}',
'待分析':'{0:.0f}',
'All':'{0:.0f}',
'待解单开发滞留时长':'{0:.2f}'
}
pt_open_dev_Major_style = pt_open_dev_Major.style.applymap(lambda x: 'color:red', subset=["All"]) \
.applymap(lambda x: 'color:green', subset=["待解单开发滞留时长"]) \
.background_gradient(text_color_threshold=0.3,cmap="PuBu", low=0, high=0.5, subset=["待解单开发滞留时长"])\
.highlight_max(axis=1,subset=['All'],color='yellow')\
.format(format_dict)
# .bar(color='lightgreen', vmin=0, subset=['待修改'], align='zero')
pt_open_dev_Major_style.to_excel(writer, sheet_name='待解单(Major)', index=True)
'''
pt_open_dev_All.to_excel(writer, sheet_name='open_dev_staytime', index=True)
# df_bug_percent_nobody_gp.to_excel(writer, sheet_name='查无此人-解单人', index=True)
# df_bug_percent_nobody_cur_gp.to_excel(writer, sheet_name='查无此人-当前', index=True)
df_open_dev.to_excel(writer, sheet_name='open状态开发环节单', index=None)
if len(df_bug_overtime_dev) > 0:
df_bug_overtime_dev.to_excel(writer, sheet_name='open状态开发超期问题单', index=None)
pt_bug_overtime_dev_group.to_excel(writer, sheet_name='open状态开发超期问题单-group',index=True)
pt_bug_overtime_dev_man.to_excel(writer, sheet_name='open状态开发超期问题单-man',index=True)
pt_bug_overtime_dev_DI_group.to_excel(writer, sheet_name='open状态开发超期问题单_DI_group', index=True)
pt_bug_overtime_dev_DI_man.to_excel(writer, sheet_name='open状态开发超期问题单_DI_man', index=True)
# df_open_dev.to_excel(writer, sheet_name='df_open_dev', index=None)
# # duration all
# gb_dev_duration.to_excel(writer, sheet_name='all已解单开发环节时长', index=True)
# gb_dev_duration_group.to_excel(writer, sheet_name='all已解单开发环节时长group', index=True)
# gb_dev_duration_group2.to_excel(writer, sheet_name='all已解单开发环节时长group2', index=True)
# pt_dev_duration_group.to_excel(writer, sheet_name='all已解单开发环节时长group_pt', index=True)
# # duration notag
# gb_dev_duration_notag.to_excel(writer, sheet_name='notag已解单开发环节时长', index=True)
# gb_dev_duration_notag_group.to_excel(writer, sheet_name='notag已解单开发环节时长group', index=True)
# gb_dev_duration_notag_group2.to_excel(writer, sheet_name='notag已解单开发环节时长group2', index=True)
# pt_dev_duration_notag_group.to_excel(writer, sheet_name='notag已解单开发环节时长group_pt', index=True)
# # duration tag
# gb_dev_duration_tag.to_excel(writer, sheet_name='tag已解单开发环节时长_tag', index=True)
# gb_dev_duration_tag_group.to_excel(writer, sheet_name='tag已解单开发环节时长group_tag', index=True)
# gb_dev_duration_tag_group2.to_excel(writer, sheet_name='tag已解单开发环节时长group2_tag', index=True)
# pt_dev_duration_tag_group.to_excel(writer, sheet_name='tag已解单开发环节时长group_pt_tag', index=True)
pt_bug_percent_total_man_yingyong.to_excel(writer, sheet_name='领域-个人-累计解单', index=True)
gb_week_debug_man.to_excel(writer, sheet_name='每周解单', index=True)
# 每周产出
gb_week_ana_man.to_excel(writer, sheet_name='每周分析', index=True)
gb_week_mod_man.to_excel(writer, sheet_name='每周修改', index=True)
gb_week_check_man.to_excel(writer, sheet_name='每周修改审核', index=True)
if len(gb_week_ccb_man) > 0:
gb_week_ccb_man.to_excel(writer, sheet_name='每周CCB', index=True)
if len(df_bug_regression_fail)>0:
df_bug_regression_fail.to_excel(writer, sheet_name='回归失败问题单', index=True)
pt_bug_regression_fail_group.to_excel(writer, sheet_name='回归失败分组', index=True)
# gb_week_efficient.to_excel(writer, sheet_name='每周效率-总人数', index=True)
pt_bug_percent_group_interval_alltime.to_excel(writer, sheet_name='全周期效率-解单人数', index=True)
pt_bug_percent_man_interval_alltime.to_excel(writer, sheet_name='全周期-人员解单', index=True)
if len(df_bug_percent_solved_select_datetime_start_end) > 0:
pt_bug_percent_group_interval.to_excel(writer, sheet_name='度量周期效率-解单人数', index=True)
pt_bug_percent_man_interval.to_excel(writer, sheet_name='度量周期-人员解单', index=True)
# if len(df_bug_percent_solved_TRa_TRb) > 0:
# pt_bug_percent_group_interval_TRa_TRb.to_excel(writer, sheet_name='TRa_TRb效率-group', index=True)
# pt_bug_percent_man_interval_TRa_TRb.to_excel(writer, sheet_name='TRa_TRb效率-man', index=True)
pt_bug_ana.to_excel(writer, sheet_name='缺陷分析责任人', index=True)
pt_bug_mod.to_excel(writer, sheet_name='缺陷修改责任人', index=True)
pt_bug_check.to_excel(writer, sheet_name='修改审核责任人', index=True)
if len(pt_bug_CCB) > 0:
pt_bug_CCB.to_excel(writer, sheet_name='CCB责任人', index=True)
pt_bug_cur.to_excel(writer, sheet_name='当前责任人', index=True)
if 'df_vl_efficiency' in sheet_names:
if len(df_vl_efficiency_add) > 0:
df_vl_efficiency_add.to_excel(writer, sheet_name='人员解单汇总', index=None)
pt_bug_man.to_excel(writer, sheet_name='项目人员解单数', index=None)
writer.save()
writer.close()
print('Write Success!')
# tk.messagebox.showinfo(title='提示',message='End of Write Success!')
# ===================================start of plot===================================
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False
# plt.style.use('dark_background')
# plt.style.use('fivethirtyeight')
# plt.style.use('ggplot')
plt.style.use('seaborn-bright')
# plt.style.use('grayscale')
fig1 = plt.figure(figsize=(12,6), dpi = 100, facecolor = 'lightgray', edgecolor='red', frameon=True)
plt.subplot(2,1,1) # 分成3行1列，起始点为1
plt.xlabel('日期')
plt.ylabel('BUG数量')
# 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='累计指派')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['累计解单'], color='b', label='累计解单')
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='90', )
# 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.5,b-1,'%.0f'%b,ha='center',va='center',fontsize=12)
y_add_sum = df_daily_all_DI['累计指派']
y_fix_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]
plt.text(a+0.5,c-1,'%.0f'%c,ha='left',va='center',fontsize=9)
plt.text(a+0.5,d-1,'%.0f'%d,ha='right',va='center',fontsize=9)
plt.title('BUG累计趋势', loc='center', fontsize=20)
plt.legend(labels=['累计指派','累计解单','遗留DI'], loc='upper left')
#网格线
plt.grid()
# ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
plt.subplot(2,1,2) # 分成3行1列，起始点为2
plt.plot(df_daily_all_DI.index, df_daily_all_DI['当天指派'], color='r', linewidth=1.5, linestyle='-', label='当天指派')
plt.plot(df_daily_all_DI.index, df_daily_all_DI['当天解单'], color='b', label='当天解单')
plt.xticks(x1, df_daily_all_DI.index, rotation='90', )
y_add = df_daily_all_DI['当天指派']
y_fix = df_daily_all_DI['当天解单']
b = y_add[-1]
c = y_fix[-1]
plt.text(a,b-1,'%.0f'%b,ha='left',va='center',fontsize=9)
plt.text(a,c-1,'%.0f'%c,ha='right',va='center',fontsize=9)
# 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('BUG每日增减', loc='center', fontsize=20)
plt.legend(labels=['当天指派','当天解单','DI'], loc='upper left')
#网格线
plt.grid()
#完整显示
plt.tight_layout()
# 保存绘图
plt.savefig(savefig_path)
#绘图
# plt.show()
# tk.messagebox.showinfo(title='提示',message='End of plt')
event.set()
if __name__ == '__main__':
def deadline():
if (datetime.datetime.now() > pd.to_datetime('2022-07-01')):
print('已过期,无法正常使用')
tk.messagebox.showinfo(title='软件更新声明',message='软件已更新，请联系作者获取最新版本。')
exit()
else:
print('未过期,可以正常使用')
# tk.messagebox.showinfo(title='免责声明',message='内部工具，仅供参考！版权所有，禁止传播！')
# +++++++++++++++++++++++++++++++ window +++++++++++++++++++++++++++++
# 第1步，实例化object，建立窗口window
window = tk.Tk()
# 第2步，给窗口的可视化起名字
window.title('自动统计需求和问题单：匹配人员&分组')
# 第3步，设定窗口的大小(长 * 宽)
window.geometry('800x300') # 这里的乘是小x
tk.Label(window, text="需求文件名必须为：Req information").grid(row=1)
tk.Label(window, text="BUG文件名必须为：BUG-INFO").grid(row=2)
# print
l1 = tk.Label(window, text="Excel文件路径：")
l1.grid(row=5,column=0)
t1 = tk.Text(window, width=80, height=1)
t1.grid(row=5, column=1, padx=10, pady=5)
t1.insert("insert", r"C:\pyDaily\Daily")
print('t1 :', t1.get('1.0','end')) # 行号以1开始，列号以0开始
# t1.delete('1.0','end')
l2 = tk.Label(window, text="人员名单：")
l2.grid(row=6,column=0)
t2 = tk.Text(window, width=80, height=1)
t2.grid(row=6, column=1, padx=10, pady=5)
t2.insert('insert',r'C:\pyDaily\vlookup\人员名单.xlsx')
print('t2 :', t2.get('0.0','end'))
contents_path_req_bug = t1.get('1.0','end')
contents_path_vlookup = t2.get('1.0','end')
# ======================================
# 检查文本变化
def getSig(contents):
m=hashlib.md5(contents.encode())
return m.digest()
sig1=getSig(contents_path_req_bug)
sig2=getSig(contents_path_vlookup)
def check():
global contents_path_req_bug
global contents_path_vlookup
contents_path_req_bug = t1.get('1.0','end')
contents_path_vlookup = t2.get('1.0','end')
if sig1 !=getSig(contents_path_req_bug):
var.set('文件路径发生改变，按新输入的路径取存表格。')
print('文件路径发生改变，按新输入的路径取存表格。')
print('新路径：', t1.get('1.0','end'))
elif sig2 !=getSig(contents_path_vlookup):
var.set('人员名单改变，按新输入的名单匹配。')
print('人员名单改变，按新输入的名单匹配。')
print('新名单：', t2.get('1.0','end'))
else:
var.set('未改变，按默认文件路径取存表格。')
print('默认文件路径')
print('默认路径：', t1.get('1.0','end'))
tk.Button(window,text="检查更改默认路径和名单",bg='orange',font=('Arial', 12),command=check).grid(row=4,column=0)
# -------------------------
var = tk.StringVar() # 将label标签的内容设置为字符类型，用var来接收hit_me函数的传出内容用以显示在标签上
var.set('Req information和BUG-INFO表格存放路径：')
l = tk.Label(window, textvariable=var, bg='royal blue', fg='white', font=('Arial', 12), width=50, height=2)
l.grid(row=3, column=1)
event = threading.Event()
# event.set()
# event.clear()
# event.wait()
# 定义一个函数功能（内容自己自由编写），供点击Button按键时调用，调用命令参数command=函数名
def show_progress():
for i in range(100):
# 每次更新加1
# p1['value'] = i + 1
p1['value'] = 100
# 更新画面
window.update()
time.sleep(0.05)
# time.sleep(0.5)
on_hit = False
def hit_me():
deadline()
global on_hit
global contents_path_req_bug
global contents_path_vlookup
global startTime
if on_hit == False:
on_hit = True
event.clear()
startTime = time.time()
thread_1 = threading.Thread(target=running_progress)
thread_1.setDaemon(True)
thread_1.start()
print('---开始---process_req_bug---:%s'%ctime())
# print('contents_path_req_bug : ', contents_path_req_bug)
# print('contents_path_vlookup : ', contents_path_vlookup)
contents_path_req_bug = t1.get('1.0','end')
contents_path_vlookup = t2.get('1.0','end')
print('contents_path_req_bug : ', contents_path_req_bug)
print('contents_path_vlookup : ', contents_path_vlookup)
# sleep(3)
thread_2 = threading.Thread(target=process_req_bug, args=(contents_path_req_bug, contents_path_vlookup))
thread_2.start()
# process_req_bug(contents_path_req_bug, contents_path_vlookup)
# event.set()
print('eeeeeeeeeeeeeeeeeeeee---event.set',event.is_set())
# endTime = time.time()
# l_progress.configure(text='本次用时：' + (endTime-startTime) +'秒')
# # var.set('本次用时：' + (endTime-startTime) +'秒')
else:
on_hit = False
var.set('Req information和BUG-INFO表格存放路径：')
def running_progress():
while True:
# startTime = time.time()
global startTime
if event.is_set(): #判断是否设置了标志位
endTime = time.time()
# var.set('执行完毕，已在下面路径生成Excel文件')
# var.set('本次用时： %s 秒'%(endTime-startTime))
var.set('本次用时： %s 秒'%round((endTime-startTime)))
b.configure(text="已完成，请点击退出",state=DISABLED)
p1.stop()
p1['maximum'] = 300
p1['value'] = 300
print('---End---')
print('eeeeeeeeeeeeeeeeeeeee---event.set',event.is_set())
break
# l_progress.configure(text='本次用时：' + (endTime-startTime) +'秒')
# l_progress.configure(text='本次用时：-----------秒')
# var.set('本次用时：' + (endTime-startTime) +'秒')
else:
p1.start(30)
b.configure(text="执行中...",state=DISABLED)
var.set('执行中，请耐心等待...')
event.wait()
# process_req_bug(contents_path_req_bug, contents_path_vlookup)
# var.set('执行完毕，已在下面路径生成Excel文件')
# b.configure(text="已完成，请点退出",state=NORMAL)
# p1.stop()
# p1['maximum'] = 300
# p1['value'] = 300
# 第5步，在窗口界面设置放置Button按键
b = tk.Button(window, text='点此按钮自动统计', bg='yellow', font=('Arial', 12), width=20, height=2, command=hit_me)
b.grid(row=10, column=1)
# tk.Message(window, text="统计完成！", width=100).grid(row=10,column=2)
tk.Button(window, text="退出", width=10, command=window.quit).grid(row=11, column=1, sticky="e", padx=10, pady=5)
l_progress = tk.Label(window, text="数据处理约需10~60秒，请耐心等待......").grid(row=12,column=0)
p1 = ttk.Progressbar(window, length=300, mode="indeterminate",maximum=300,orient=tk.HORIZONTAL)
p1.grid(row=12,column=1,columnspan=2)
tk.messagebox.showinfo(title='版权和免责声明',message='版权保护，请勿扩散！自用工具，仅供参考！')
window.mainloop()
扫一扫
07-04
1054
1054
08-27
631
631
09-20
8468
8468
10-08
260
260
10-11
442
442
10-03
788
788
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
