"""
Created on Tue Sep 14 12:48:40 2021
"""
import pandas as pd
import os
import datetime
#运行时间
starttime = datetime.datetime.now()
# Customer_Defect.to_excel(writer,index=False,sheet_name=outsheet_Customer_Defect)
# EDB4_Project_Defect.to_excel(writer,index=False,sheet_name=outsheet_EDB4_Project_Defect)
# Pivot_EDB4_Customer_Defect.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Customer_Defect)
# Pivot_EDB4_Monthly_Defect.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Customer_Defect, startcol=0, startrow=(Pivot_EDB4_Customer_Defect_nrows + 4))
# Pivot_Concerned_Customer_Defect_List.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Concerned_Customer_Defect_List)
# Pivot_EDB4_Project_Defect_Personal.to_excel(writer, index=False,sheet_name=outsheet_EDB4_Project_Defect_Personal)
# Pivot_EDB4_Project_Defect_Monthly.to_excel(writer,index=False,sheet_name=outsheet_EDB4_Project_Defect_Monthly)
# Pivot_EDB4_Project_Defect_Project_Total.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Project_Defect_Monthly, startcol=(a+1), startrow=0)
# Pivot_EDB4_Product_Type_Defect_Monthly.to_excel(writer,index=False,sheet_name=outsheet_EDB4_ProductType_Defect_Monthly)
#项目分类列表,检查输出的Concern_Project_List，全部进行分类
FR_Project_List = ['GWM FR5CP AGILE','FR5CP GEELY BUNDLE','FR5CP GAC A18','XPENG_Edward','FR5CP FAW D-bundle',
'FR5CP FAW C105EV','FR5CP BJEV N60 - Classic','PJ_SAIC_IS31_AS32_FrontRadar','SAICEP33LFR5CP']
CR_Project_List = ['CR5CP XPENG E28','GWM CR5CP AGILE','CR5CP GAC A18','CR5CP FAW C105EV','CR5CB GWM']
DASy_Project_List = ['DASy GAC A18','DASy FAW C105EV','DASY_WM_ASE-3','DASy BJEV N60AB','DASY_SAIC_Bundle']
MPC_Project_List = ['Video_Generation3','MPC3 GAC A18','FVC3-BJEV','MPC3 SOKON X1']
#有效列表
Effective_List = ['Limitation Accepted', 'Resolved', 'Unresolved', 'Duplicate']
Ignore_Component_List = ['FBL' 'SEC' 'DIAG' 'COM' 'FSICD' 'FSIFM']
Ignore_Assignee_Group_List = ['EBB4' 'ECV1' 'EDB1' 'EDB2' 'EDB3' 'EDB5' 'EDB6' 'EDP3' 'ESD' 'EST3' 'ESV' 'ESX1' 'ESX5' 'ESX7' 'ESZ1' 'QMC1']
#小组成员，有的人名后面有一个空格需要注意
EDB4_names = ['YU Haoqian ','HUANG Jiyu ','LU Mengya','ZHA Jinglan','EXTERNAL He Ronghua ','EXTERNAL Zhou Fall ','CHEN Jinrong ',
'JIANG Kaijun ','JIANG Qian ','JIN Ning ','JIN XiaoChun ','KAN Rainfoe ','LU Yi ','NI Ruixue ',
'NIE Shuang ','QI Ya ','WU Xiao ','YANG Na ','YAO Honglin ','ZHENG Ling ']
dataFile_Concerned_Customer_Defect_List = 'Concerned_Customer_Defect_List_Analyzed.xlsx'
# 输入表单名
insheet = 'general_report'
# 输出表单名
outsheet = 'All_Raw_Data'
outsheet_Customer_Defect = 'Customer_Defect'
outsheet_EDB4_Project_Defect = 'EDB4_Project_Defect'
# 输出pivot table表单名
outsheet_EDB4_Customer_Defect = 'EDB4_Customer_Defect'
outsheet_EDB4_Project_Defect_Monthly = 'EDB4_Project_Defect_Monthly'
outsheet_EDB4_ProductType_Defect_Monthly = 'EDB4_ProductType_Defect_Monthly'
outsheet_EDB4_Project_Defect_Personal = 'EDB4_Project_Defect_Personal'
outsheet_EDB4_Concerned_Customer_Defect_List = 'Concerned_Customer_Defect'
#将csv文件转换为excel文件备用，csv文件的文件名要检查更新
csv = pd.read_csv('20210918104030051.csv', encoding='utf-8')
csv.to_excel('Concerned_Customer_Defect_List_Analyzed.xlsx', sheet_name='Sheet1', index=False)
#导入'data.xlsx'文件
dataFile = 'data.xlsx'
file_path = os.getcwd()
data_path = os.path.join(file_path, dataFile)
data = pd.read_excel(data_path, sheet_name='general_report', header=3)#从第四行开始读取excel表格'data.xlsx'
data = pd.DataFrame(data)#转换为dataframe格式
data = data.iloc[:-1]#删除最后一行
data['Project'].isnull().value_counts()#检查 Project 列内容为空的有几个
data['Project'] = data['Project'].fillna('9999')#将空值填充为‘9999’
a = data[(data.Project=='9999')].index.tolist()#找出空值所在行的索引
data = data.drop(a)#删除空值所在的行,
###########删除之后的行索引已经不存在了#####################
data = data.reset_index()
columns_name = ['Project', 'Key', 'Summary', 'Issue Type', 'Status', 'Priority','Resolution', 'Assignee',
'Reporter', 'Creator', 'Created', 'Last Viewed','Updated', 'Resolved', 'Affects Version/s',
'Fix Version/s', 'Component/s','Due Date', 'Origin', 'Problem Type', 'Severity']
data = data[columns_name]#保留指定列
#增加表格的列
columns_name.insert(21, 'Project_Product')
columns_name.insert(22, 'Concerned_or_Not')
columns_name.insert(23, 'Assignee_Department')
columns_name.insert(24, 'Assignee_Group')
columns_name.insert(25, 'Reporter_Department')
columns_name.insert(26, 'Reporter_Group')
columns_name.insert(27, 'Reporter_Area')
columns_name.insert(28, 'Test_Group')
columns_name.insert(29, 'Valid_or_Not')
columns_name.insert(30, 'Created_Month')
columns_name.insert(31, 'Created_Day')
columns_name.insert(32, 'Resolved_Day')
columns_name.insert(33, 'Period')
columns_name.insert(34, 'Time_To_Fix_Bugs')
columns_name.insert(35, 'Reviewer')
columns_name.insert(36, 'Result_Finding')
columns_name.insert(37, 'Product_Type')
columns_name.insert(38, 'Customer_Defect_About_FCT')
columns_name.insert(39, 'Due_Reduce_Created')
data = data.reindex(columns=columns_name)
#因为某些原因components无法填写的，在这里手动添加
for i in range(len(data['Key'])):
if data.loc[i,'Key'] == 'FRGVSOKON-663':
data.loc[i,'Component/s'] = 'ACC'
elif data.loc[i,'Key'] == 'DASYSAIC-1453':
data.loc[i,'Component/s'] = 'COM'
elif data.loc[i,'Key'] == 'DASYSAIC-1236':
data.loc[i,'Component/s'] = 'ACC'
elif data.loc[i,'Key'] == 'GWMFRFIVE-3053':
data.loc[i,'Component/s'] = 'RE_Change'
elif data.loc[i,'Key'] == 'SAICMPC-1164':
data.loc[i,'Component/s'] = 'RSF'
elif data.loc[i,'Key'] == 'SAICMPC-1163':
data.loc[i,'Component/s'] = 'FM'
elif data.loc[i,'Key'] == 'SAICMPC-1151':
data.loc[i,'Component/s'] = 'COM'
elif data.loc[i,'Key'] == 'SAICMPC-1147':
data.loc[i,'Component/s'] = 'COM'
elif data.loc[i,'Key'] == 'SAICMPC-677':
data.loc[i,'Component/s'] = 'FM'
elif data.loc[i,'Key'] == 'SAICMPC-675':
data.loc[i,'Component/s'] = 'COM'
elif data.loc[i,'Key'] == 'SAICMPC-674':
data.loc[i,'Component/s'] = 'COM'
elif data.loc[i,'Key'] == 'SAICMPC-673':
data.loc[i,'Component/s'] = 'HMA'
elif data.loc[i,'Key'] == 'FVGTHREEGEELY-2344':
data.loc[i,'Component/s'] = 'FM'
elif data.loc[i,'Key'] == 'GWMCRFIVE-3313':
data.loc[i,'Component/s'] = 'FCT'
elif data.loc[i,'Key'] == 'GWMCRFIVE-3217':
data.loc[i,'Component/s'] = 'FCT'
elif data.loc[i,'Key'] == 'GWMCRFIVE-2672':
data.loc[i,'Component/s'] = 'FCT'
elif data.loc[i,'Key'] == 'GACFR-3642':
data.loc[i,'Component/s'] = 'AEB'
else:
continue
#获取关心列表
data_1 = data.copy(deep=True)
Reporter_with_dep = data_1['Reporter']
Reporter_name_only = list()
# EDB4_names_index = linst()
# print(type(Reporter_with_dep))
# print(Reporter_with_dep)
for i in range(len(Reporter_with_dep)):
Reporter_name_only.append(Reporter_with_dep.values[i].split('(')[0])
# print(Reporter_name_only)
data_1['Reporter'] = Reporter_name_only
data_1 = data_1[data_1['Reporter'].isin(EDB4_names)]
Concern_Project_List = list(set(data_1['Project'].values))
for i in range(len(Concern_Project_List)):
if 'FR' in Concern_Project_List[i]:
FR_Project_List.append(Concern_Project_List[i])
elif 'FrontRadar' in Concern_Project_List[i]:
FR_Project_List.append(Concern_Project_List[i])
elif 'CR' in Concern_Project_List[i]:
CR_Project_List.append(Concern_Project_List[i])
elif 'CornerRadar' in Concern_Project_List[i]:
CR_Project_List.append(Concern_Project_List[i])
elif 'MPC' in Concern_Project_List[i]:
MPC_Project_List.append(Concern_Project_List[i])
elif 'DASY' in Concern_Project_List[i]:
DASy_Project_List.append(Concern_Project_List[i])
elif 'DASy' in Concern_Project_List[i]:
DASy_Project_List.append(Concern_Project_List[i])
else:
continue
print('Generate Concern_Project_List And New_Project_Type_List')
print(Concern_Project_List)
# print(Concern_Project_List)
# ===================================================================================#
# 从‘Component’中找出(属于Ignore_Component)即不等于FBL/SEC/DIAG/COM/FSICD/FSIFM
# 从‘Assignee_Group’中找出属于Ignore_Assignee_Group的
Customer_Defect_About_FCT = list()
Components = list()
Assignee_Group = list()
Component_s = data['Component/s']
Assignee = data['Assignee']
for i in range(len(Assignee)):
curComponents = Component_s.values[i]
curAssignee = Assignee.values[i]
if curComponents in Ignore_Component_List:
curCustomer_Defect_About_FCT = "not need analysis"
else:
curCustomer_Defect_About_FCT = "need analysis"
if curAssignee == 'Unassigned':
curCustomer_Defect_About_FCT = "need analysis"
elif "/" not in curAssignee:
curCustomer_Defect_About_FCT = "not need analysis"
elif "/" in curAssignee:
pre_curAssignee_Group = curAssignee.split('/')[1]
if "-" in pre_curAssignee_Group:
curAssignee_Group = curAssignee.split('/')[1].split('-')[0]
else:
curAssignee_Group = curAssignee.split('/')[1].split(')')[0]
if curAssignee_Group in Ignore_Assignee_Group_List:
curCustomer_Defect_About_FCT = "not need analysis"
else:
curCustomer_Defect_About_FCT = "need analysis"
Customer_Defect_About_FCT.append(curCustomer_Defect_About_FCT)
data['Customer_Defect_About_FCT'] = Customer_Defect_About_FCT
print('Generate Customer_Defect_About_FCT')
# ===================================================================================#
# 从‘Key’中提取Project_Product, 以“-”为分界线isnull
Key = data['Key']
Project_Product = list()
for i in range(len(Key)):
# curProject_Product = data.loc[[row], ['Key']].values[0][0].split('-')[0]
Project_Product.append(Key.values[i].split('-')[0])
data['Project_Product'] = Project_Product
print('Generate Project_Product')
# ===================================================================================#
# 检查项目名是否在关心列表里，确定是否关心该条defect
Concerned_or_Not = list()
Project = data['Project']
for i in range(len(data['Project'])):
curProject = Project.values[i]
if curProject in Concern_Project_List:
curConcerned_or_Not = "Concerned"
else:
curConcerned_or_Not = "Not_Concerned"
Concerned_or_Not.append(curConcerned_or_Not)
data['Concerned_or_Not'] = Concerned_or_Not
print('Generate Concerned_or_Not')
# ===================================================================================#
# 按照项目类型分类： FR CR DASY MPC
Product_Type = list()
Project = data['Project']
#项目分类列表
# FR_Project_List = ['GWM FR5CP AGILE','FR5CP GEELY BUNDLE','FR5CP GAC A18','XPENG_Edward','FR5CP FAW D-bundle',
# 'FR5CP FAW C105EV','FR5CP BJEV N60 - Classic','PJ_SAIC_IS31_AS32_FrontRadar','SAICEP33LFR5CP']
# CR_Project_List = ['CR5CP XPENG E28','GWM CR5CP AGILE','CR5CP GAC A18','CR5CP FAW C105EV','CR5CB GWM']
# DASy_Project_List = ['DASy GAC A18','DASy FAW C105EV','DASY_WM_ASE-3','DASy BJEV N60AB','DASY_SAIC_Bundle']
# MPC_Project_List = ['Video_Generation3','MPC3 GAC A18','FVC3-BJEV','MPC3 SOKON X1']
for i in range(len(data['Project'])):
curProject = Project.values[i]
if curProject in FR_Project_List:
curProduct_Type = "Front_Radar"
elif curProject in CR_Project_List:
curProduct_Type = "Corner_Radar"
elif curProject in DASy_Project_List:
curProduct_Type = "DASy"
elif curProject in MPC_Project_List:
curProduct_Type = "MPC"
else:
curProduct_Type = "Other"
Product_Type.append(curProduct_Type)
data['Product_Type'] = Product_Type
print('Generate Product_Type')
# ===================================================================================#
# 从‘Assignee’中提取"("和“/”之间的字符串
Assignee = data['Assignee']
Assignee_Department = list()
for i in range(len(Assignee)):
curAssignee = Assignee.values[i]
if curAssignee == 'Unassigned':
curAssignee_Department = "Assignee Unassigned"
else:
curAssignee_Department = curAssignee.split('(')[1].split('/')[0]
if "," in curAssignee_Department:
curAssignee_Department = curAssignee_Department.split(', ')[1]
else:
curAssignee_Department = curAssignee_Department
Assignee_Department.append(curAssignee_Department)
data['Assignee_Department'] = Assignee_Department
print('Generate Assignee_Department')
# ===================================================================================#
# 从‘Assignee’中提取'/'和'-'（')'）之间的字符串
Assignee_Group = list()
for i in range(len(Assignee)):
curAssignee = Assignee.values[i]
if curAssignee == 'Unassigned':
curAssignee_Group = "Assignee Unassigned"
elif "/" not in curAssignee:
curAssignee_Group = curAssignee
else:
pre_curAssignee_Group = curAssignee.split('/')[1]
if "-" in pre_curAssignee_Group:
curAssignee_Group = curAssignee.split('/')[1].split('-')[0]
else:
curAssignee_Group = curAssignee.split('/')[1].split(')')[0]
Assignee_Group.append(curAssignee_Group)
data['Assignee_Group'] = Assignee_Group
print('Generate Assignee_Group')
# ===================================================================================#
# 从‘Reporter’中提取"("和“/”之间的字符串
Reporter = data['Reporter']
Reporter_Department = list()
for i in range(len(Reporter)):
curReporter = Reporter.values[i]
if curReporter == 'Unassigned':
curReporter_Department = "Reporter Unassigned"
else:
curReporter_Department = curReporter.split('(')[1].split('/')[0]
if "," in curReporter_Department:
curReporter_Department = curReporter_Department.split(', ')[1]
else:
curReporter_Department = curReporter_Department
Reporter_Department.append(curReporter_Department)
data['Reporter_Department'] = Reporter_Department
print('Generate Reporter_Department')
# ===================================================================================#
# 从‘Reporter’中提取'/'和'-'（')'）之间的字符串
Reporter_Group = list()
for i in range(len(Reporter)):
curReporter = Reporter.values[i]
if curReporter == 'Unassigned':
curReporter_Group = "Reporter Unassigned"
elif "/" not in curReporter:
curReporter_Group = curReporter
else:
pre_curReporter_Group = curReporter.split('/')[1]
if "-" in pre_curReporter_Group:
curReporter_Group = curReporter.split('/')[1].split('-')[0]
else:
curReporter_Group = curReporter.split('/')[1].split(')')[0]
Reporter_Group.append(curReporter_Group)
data['Reporter_Group'] = Reporter_Group
print('Generate Reporter_Group')
# ===================================================================================#
# 检查‘Reporter’字符串中是否包含“-CN”
Reporter_Area = list()
for row in range(len(Reporter)):
curReporter = Reporter.values[i]
if curReporter == 'Unassigned':
curReporter_Area = "Reporter Unassigned"
else:
if "-CN" in curReporter:
curReporter_Area = "CHINA"
else:
curReporter_Area = "OTHER"
Reporter_Area.append(curReporter_Area)
data['Reporter_Area'] = Reporter_Area
print('Generate Reporter_Area')
# ===================================================================================#
# 检查‘Reporter_Group’是否为EDA1~6 or EDB3 EDB4
Test_Group = list()
Reporter_Group = list()
Test_Group_List = ['EDA', 'EDA1', 'EDA2', 'EDA3', 'EDA4', 'EDA5', 'EDA6', 'EDB3', 'EDB4']
for i in range(len(Reporter)):
curReporter = Reporter.values[i]
if curReporter == 'Unassigned':
curReporter_Group = "Reporter Unassigned"
elif "/" not in curReporter: # CHEN Jingying(BCSC-ENG1-VV2)
curReporter_Group = curReporter
curTest_Group = "NO"
else:
pre_curReporter_Group = curReporter.split('/')[1]
if "-" in pre_curReporter_Group:
curReporter_Group = curReporter.split('/')[1].split('-')[0]
if curReporter_Group in Test_Group_List:
curTest_Group = "YES"
else:
curTest_Group = "NO"
else:
curReporter_Group = curReporter.split('/')[1].split(')')[0]
if curReporter_Group in Test_Group_List:
curTest_Group = "YES"
else:
curTest_Group = "NO"
Test_Group.append(curTest_Group)
data['Test_Group'] = Test_Group
print('Generate Test_Group')
# ===================================================================================#
# 检查Resolution是否在有效列表里，确定该条defect是否为有效defect
Resolution = data['Resolution']
Valid_or_Not = list()
Effective_List = ['Limitation Accepted', 'Resolved', 'Unresolved', 'Duplicate']
for i in range(len(Resolution)):
curResolution = Resolution.values[i]
if curResolution in Effective_List:
curValid_or_Not = "VALID"
else:
curValid_or_Not = "INVALID"
Valid_or_Not.append(curValid_or_Not)
data['Valid_or_Not'] = Valid_or_Not
print('Generate Valid_or_Not')
# ===================================================================================#
# 从‘Created’中提取年月日 2021-04-23 10:16:00
Created = data['Created']
Created_Month = list()
Created_Day = list()
for i in range(len(Created)):
# curCreated_Month = data.loc[[row],['Created']].values[0][0] #numpy.datetime64('2021-04-23T08:56:00.000000000')
pre_curCreated_Day = pd.to_datetime(str(Created.values[i]))
curCreated_Month = pre_curCreated_Day.strftime('%Y_%m') # 2021.04 ('%Y.%m.%d')==2021.04.23
Created_Month.append(curCreated_Month)
curCreated_Day = pre_curCreated_Day.strftime('%Y_%m_%d') # 2021.04 ('%Y.%m.%d')==2021.04.23
Created_Day.append(curCreated_Day)
data['Created_Month'] = Created_Month
data['Created_Day'] = Created_Day
print('Generate Created_Month')
# ===================================================================================#
# 从‘Resolved’中提取年月日 2021-04-23 10:16:00
Resolved = data['Resolved']
Resolved_Day = list()
for i in range(len(Resolved)):
curResolved_Day = Resolved.values[i]
# print(pd.isnull(curResolved)) # True False
if pd.isnull(curResolved_Day) == True:
curResolved_Day = curResolved_Day
elif pd.isnull(curResolved_Day) == False:
pre_curResolved_Day = pd.to_datetime(str(curResolved_Day))
curResolved_Day = pre_curResolved_Day.strftime('%Y.%m.%d')
Resolved_Day.append(curResolved_Day)
data['Resolved_Day'] = Resolved_Day
print('Generate Resolved_Day')
# ===================================================================================#
# Due_Reduce_Created = 'Due Date' - 'Created_Day' 得到分析时长，时长超过30时，defect不规范
#'Due Date'和'Created_Day'两列有空值时也是不规范，将空值情况下，Due_Reduce_Created设定为40
Due_Date = data['Due Date']
Due_Reduce_Created = list()
for i in range(len(Due_Date)):
if pd.isnull(Due_Date.values[i]) == True:
Due_Reduce_Created.append(40)
elif pd.isnull(Created.values[i]) == True:
Due_Reduce_Created.append(40)
else:
curCreated_Day = pd.to_datetime(str(Created.values[i]))
curDue_Date = pd.to_datetime(str(Due_Date.values[i]))
curDue_Reduce_Created = (curDue_Date - curCreated_Day).days
Due_Reduce_Created.append(curDue_Reduce_Created)
data['Due_Reduce_Created'] = Due_Reduce_Created
print('Generate Due_Reduce_Created')
# ===================================================================================#
# Period = 'Resolved_Day' - 'Created_Day' 得到分析时长
Created_Day = list()
Resolved_Day = list()
Period = list()
Time_To_Fix_Bugs = list()
for i in range(len(Resolved)):
pre_curCreated_Day = pd.to_datetime(str(Created.values[i]))
pre_curResolved_Day = pd.to_datetime(str(Resolved.values[i]))
# curPeriod = pre_curResolved_Day - pre_curCreated_Day # Timedelta('0 days 22:25:00')
pre_pre_curPeriod = str(pre_curResolved_Day - pre_curCreated_Day)[
:10] # '2021.04.30', numpy.datetime64('NaT')
pre_curPeriod = pre_pre_curPeriod.split(' day')[0] # '0 days ', 'NaT'
if pre_curPeriod == 'NaT':
curPeriod = ''
else:
curPeriod = pre_curPeriod
Period.append(curPeriod)
data['Period'] = Period
print('Generate Period')
# ===================================================================================#
# 'Time_To_Fix_Bugs'分级：每10天为一个等级
Created_Day = list()
Resolved_Day = list()
Period = list()
Time_To_Fix_Bugs = list()
for i in range(len(Resolved)):
pre_curCreated_Day = pd.to_datetime(str(Created.values[i]))
pre_curResolved_Day = pd.to_datetime(str(Resolved.values[i]))
# curPeriod = pre_curResolved_Day - pre_curCreated_Day # Timedelta('0 days 22:25:00')
pre_pre_curPeriod = str(pre_curResolved_Day - pre_curCreated_Day)[
:10] # '2021.04.30', numpy.datetime64('NaT')
pre_curPeriod = pre_pre_curPeriod.split(' day')[0] # '0 days ', 'NaT'
if pre_curPeriod == 'NaT':
curPeriod = ''
Time_To_Fix_Bugs_Level = ''
else:
curPeriod = pre_curPeriod
if curPeriod in ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9']:
Time_To_Fix_Bugs_Level = '0-10D'
elif curPeriod in ['10', '11', '12', '13', '14', '15', '16', '17', '18', '19']:
Time_To_Fix_Bugs_Level = '10-20D'
elif curPeriod in ['20', '21', '22', '23', '24', '25', '26', '27', '28', '29']:
Time_To_Fix_Bugs_Level = '20-30D'
elif curPeriod in ['30', '31', '32', '33', '34', '35', '36', '37', '38', '39']:
Time_To_Fix_Bugs_Level = '30-40D'
elif curPeriod in ['40', '41', '42', '43', '44', '45', '46', '47', '48', '49']:
Time_To_Fix_Bugs_Level = '40-50D'
elif curPeriod in ['50', '51', '52', '53', '54', '55', '56', '57', '58', '59']:
Time_To_Fix_Bugs_Level = '50-60D'
elif curPeriod in ['60', '61', '62', '63', '64', '65', '66', '67', '68', '69']:
Time_To_Fix_Bugs_Level = '60-70D'
elif curPeriod in ['70', '71', '72', '73', '74', '75', '76', '77', '78', '79']:
Time_To_Fix_Bugs_Level = '70-80D'
elif curPeriod in ['80', '81', '82', '83', '84', '85', '86', '87', '88', '89']:
Time_To_Fix_Bugs_Level = '80-90D'
else:
Time_To_Fix_Bugs_Level = 'more_than_90D'
Time_To_Fix_Bugs.append(Time_To_Fix_Bugs_Level)
data['Time_To_Fix_Bugs'] = Time_To_Fix_Bugs
print('Generate Time_To_Fix_Bugs')
# ===================================================================================#
#生成EDB4_Project_Defect
data_2 = data.copy(deep = True)
Reporter_with_dep = data_2['Reporter']
Reporter_name_only = list()
for i in range(len(Reporter_with_dep)):
Reporter_name_only.append(Reporter_with_dep.values[i].split('(')[0])
data_2['Reporter'] = Reporter_name_only
EDB4_Project_Defect = data_2[data_2['Reporter'].isin(EDB4_names)]
#因为某些原因components无法填写的，在这里手动添加
# for i in range(len(Reporter_with_dep)):
# if EDB4_Project_Defect.loc[i,'Key'] == 'FRGVSOKON-663':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'ACC'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'DASYSAIC-1453':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'COM'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'DASYSAIC-1236':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'ACC'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'GWMFRFIVE-3053':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'RE_Change'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'SAICMPC-1164':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'RSF'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'SAICMPC-1163':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'FM'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'SAICMPC-1151':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'COM'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'SAICMPC-1147':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'COM'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'SAICMPC-677':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'FM'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'SAICMPC-675':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'COM'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'SAICMPC-674':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'COM'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'SAICMPC-673':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'HMA'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'FVGTHREEGEELY-2344':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'FM'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'GWMCRFIVE-3313':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'FCT'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'GWMCRFIVE-3217':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'FCT'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'GWMCRFIVE-2672':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'FCT'
# elif EDB4_Project_Defect.loc[i,'Key'] == 'GACFR-3642':
# EDB4_Project_Defect.loc[i,'Component/s'] = 'AEB'
# else:
# continue
print('Generate EDB4_Project_Defect')
# ===================================================================================#
#生成EDB4_defect_Abnormal
pre_EDB4_defect_Abnormal = EDB4_Project_Defect.copy(deep = True)
EDB4_defect_Abnormal = pre_EDB4_defect_Abnormal[pre_EDB4_defect_Abnormal['Due_Reduce_Created'] > 30]
# ===================================================================================#
#生成EDB4_componnents_blanks
pre_EDB4_componnents_blanks = EDB4_Project_Defect.copy(deep = True)
EDB4_componnents_blanks = pre_EDB4_componnents_blanks[pre_EDB4_componnents_blanks['Component/s'].isnull()]
# ===================================================================================#
#生成Customer_Defect
data_3 = data.copy(deep = True)
Customer_Defect_1 = data_3[data_3['Origin'] == 'Customer']
Customer_Defect_2 = Customer_Defect_1[Customer_Defect_1['Problem Type'] == 'Defect']
Customer_Defect_3 = Customer_Defect_2[Customer_Defect_2['Concerned_or_Not'] == 'Concerned']
Customer_Defect = Customer_Defect_3[Customer_Defect_3['Customer_Defect_About_FCT'] == 'need analysis']
print('Generate Customer_Defect')
# ===================================================================================#
# 初步筛选数据得到 Customer_Defect VALID --原始数据表
data_4 = data.copy(deep = True)
Customer_Defect_Valid_1 = data_4[data_4['Valid_or_Not'] == 'VALID']
Customer_Defect_Valid_2 = Customer_Defect_Valid_1[Customer_Defect_Valid_1['Origin'] == 'Customer']
Customer_Defect_Valid_3 = Customer_Defect_Valid_2[Customer_Defect_Valid_2['Problem Type'] == 'Defect']
Customer_Defect_Valid_4 = Customer_Defect_Valid_3[Customer_Defect_Valid_3['Concerned_or_Not'] == 'Concerned']
Customer_Defect_Valid = Customer_Defect_Valid_4[Customer_Defect_Valid_4['Customer_Defect_About_FCT'] == 'need analysis']
output_data_path = os.path.join(file_path, 'Concerned_Customer_Defect_List.xlsx')
Customer_Defect_Valid.to_excel(output_data_path, sheet_name='Sheet1', index=False)
# 读取数据
data_path2 = os.path.join(file_path, 'Concerned_Customer_Defect_List.xlsx')
data_5 = pd.read_excel(data_path2, sheet_name='Sheet1')
# 转换的数据形式
transfer = {'Project': list(), 'Product_Type': list(), 'Key': list(), 'Summary': list(), 'Component/s': list(), 'Created_Month': list()}
# 维护当前的Project,Key,Summary...Severity
curProject = list()
Project_5 = data_5['Project']
curProduct_Type = list()
Product_Type_5 = data_5['Product_Type']
curKey = list()
Key_5 = data_5['Key']
curSummary = list()
Summary_5 = data_5['Summary']
curComponents = list()
Component_s_5 = data_5['Component/s']
curCreated_Month = list()
Created_Month_5 = data_5['Created_Month']
# 逐行进行处理
for i in range(len(Project_5)):
# 该行的Project,Key,Summary...Severity
Project = Project_5.values[i]
Product_Type = Product_Type_5.values[i]
Key = Key_5.values[i]
Summary = Summary_5.values[i]
Components = Component_s_5.values[i]
Created_Month = Created_Month_5.values[i]
# 如果Key不是空,那么将当前的各数据加入字典,并重置curProject,curKey,curSummary...curSeverity
if not pd.isnull(Key):
transfer['Project'].append(curProject)
transfer['Product_Type'].append(curProduct_Type)
transfer['Key'].append(curKey)
transfer['Summary'].append(curSummary)
transfer['Component/s'].append(curComponents)
transfer['Created_Month'].append(curCreated_Month)
curProject = Project
curProduct_Type = Product_Type
curKey = Key
curSummary = Summary
curComponents = Components
curCreated_Month = Created_Month
# 将最后一部分数据加入
transfer['Project'].append(curProject)
transfer['Product_Type'].append(curProduct_Type)
transfer['Key'].append(curKey)
transfer['Summary'].append(curSummary)
transfer['Component/s'].append(curComponents)
transfer['Created_Month'].append(curCreated_Month)
# 第一行为空的,所以删除
transfer = pd.DataFrame(transfer).loc[1:, :]
col_name = transfer.columns.tolist()
#col_name.insert(7, 'Reviewer')
#col_name.insert(8, 'Result_Finding')
#col_name.insert(9, 'Need_To_Add_Test_Case')
transfer = transfer.reindex(columns=col_name)
# 保存到excel
output_data_path = os.path.join(file_path, 'Concerned_Customer_Defect_List.xlsx')
#print(output_data_path)
transfer.to_excel(output_data_path, sheet_name='Sheet1', index=False)
print('generate Concerned_Customer_Defect_List done!')
#生成pivot
# -*- coding: utf-8 -*-defect
Pivot_EDB4_Customer_Defect = Customer_Defect.pivot_table('Key', index='Created_Month', columns='Valid_or_Not', aggfunc='count')
Pivot_EDB4_Customer_Defect_nrows = len(Pivot_EDB4_Customer_Defect)
# monthly EDB4 Defect valid rate analysis --for external 初步透视表
Pivot_EDB4_Monthly_Defect = EDB4_Project_Defect.pivot_table('Key', index='Created_Month', columns='Valid_or_Not', aggfunc='count')
Pivot_EDB4_Monthly_Defect_nrows = len(Pivot_EDB4_Monthly_Defect)
data_path_Concerned_Customer_Defect_List = os.path.join(file_path, dataFile_Concerned_Customer_Defect_List)
# 分析过后的 customer defect数据 --for external 初步透视表
Concerned_Customer_Defect_List = pd.read_excel(data_path_Concerned_Customer_Defect_List, sheet_name='Sheet1')
Pivot_Concerned_Customer_Defect_List = Concerned_Customer_Defect_List.pivot_table('Key', index='Created_Month', columns='Result_Finding', aggfunc='count')
writer = pd.ExcelWriter(dataFile)
columns = ['Project', 'Key', 'Summary', 'Issue Type', 'Status', 'Priority', 'Resolution', 'Assignee', 'Reporter', 'Creator', 'Created', 'Last Viewed',
'Updated', 'Resolved', 'Affects Version/s', 'Fix Version/s', 'Component/s', 'Due Date', 'Origin', 'Problem Type', 'Severity', 'Project_Product', 'Product_Type',
'Concerned_or_Not', 'Assignee_Department', 'Assignee_Group', 'Reporter_Department', 'Reporter_Group', 'Reporter_Area', 'Test_Group', 'Valid_or_Not',
'Created_Month', 'Resolved_Day', 'Period', 'Time_To_Fix_Bugs', 'Customer_Defect_About_FCT']
data.to_excel(writer, index=False, sheet_name=insheet)
data.to_excel(writer, index=False, columns=columns, sheet_name=outsheet)
EDB4_Project_Defect.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Project_Defect)
Customer_Defect.to_excel(writer, index=False, sheet_name=outsheet_Customer_Defect)
EDB4_defect_Abnormal.to_excel(writer, index=False, sheet_name='EDB4_defect_Abnormal')
EDB4_componnents_blanks.to_excel(writer, index=False, sheet_name='EDB4_componnents_blanks')
Pivot_EDB4_Customer_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect)
Pivot_EDB4_Monthly_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect, startcol=0, startrow=(Pivot_EDB4_Customer_Defect_nrows + 4))
Pivot_Concerned_Customer_Defect_List.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Concerned_Customer_Defect_List)
writer.save()
# 读取数据
Pivot_EDB4_Customer_Defect = pd.read_excel(dataFile, sheet_name=outsheet_EDB4_Customer_Defect, nrows=Pivot_EDB4_Customer_Defect_nrows)
# 将数据框的列名全部提取出来存放在列表里
col_name = Pivot_EDB4_Customer_Defect.columns.tolist()
# 在列索引为21的位置插入一列,列名为:Project_Product，刚插入时不会有值，整列都是NaN
col_name.insert(3, 'Total')
col_name.insert(4, 'Valid_Rate')
col_name.insert(5, 'Valid_Rate_of_Total')
Pivot_EDB4_Customer_Defect = Pivot_EDB4_Customer_Defect.reindex(columns=col_name)
Created_Month = Pivot_EDB4_Customer_Defect['Created_Month'].values.tolist()
INVALID = Pivot_EDB4_Customer_Defect['INVALID'].values.tolist()
VALID = Pivot_EDB4_Customer_Defect['VALID'].values.tolist()
Total = Pivot_EDB4_Customer_Defect['Total'].values.tolist()
Valid_Rate = Pivot_EDB4_Customer_Defect['Valid_Rate'].values.tolist()
Created_Month = list()
INVALID = list()
VALID = list()
Total = list()
Valid_Rate = list()
for row in range(Pivot_EDB4_Customer_Defect.shape[0]):
curCreated_Month = Pivot_EDB4_Customer_Defect.loc[[row], ['Created_Month']].values[0][0]
Created_Month.append(curCreated_Month)
curINVALID = Pivot_EDB4_Customer_Defect.loc[[row], ['INVALID']].values[0][0]
if pd.isnull(curINVALID) == True:
curINVALID = int(0)
else:
curINVALID = int(curINVALID)
INVALID.append(curINVALID)
curVALID = Pivot_EDB4_Customer_Defect.loc[[row], ['VALID']].values[0][0]
if pd.isnull(curVALID) == True:
curVALID = int(0)
else:
curVALID = int(curVALID)
VALID.append(curVALID)
curTotal = curVALID + curINVALID
if pd.isnull(curTotal) == True:
curTotal = int(0)
else:
curTotal = int(curTotal)
Total.append(curTotal)
curValid_Rate = curVALID / curTotal
Valid_Rate.append(curValid_Rate)
Pivot_EDB4_Customer_Defect['Created_Month'] = Created_Month
Pivot_EDB4_Customer_Defect['INVALID'] = INVALID
Pivot_EDB4_Customer_Defect['VALID'] = VALID
Pivot_EDB4_Customer_Defect['Total'] = Total
Pivot_EDB4_Customer_Defect['Valid_Rate'] = Valid_Rate
# **************************************add for EDB4_Monthly_Defect data **************************************************
# 读取数据
Pivot_EDB4_Monthly_Defect = pd.read_excel(dataFile, sheet_name=outsheet_EDB4_Customer_Defect, header=(Pivot_EDB4_Customer_Defect_nrows + 4), nrows=Pivot_EDB4_Monthly_Defect_nrows)
col_name_EDB4 = Pivot_EDB4_Monthly_Defect.columns.tolist()
col_name_EDB4.insert(3, 'Total')
col_name_EDB4.insert(4, 'Valid_Rate')
Pivot_EDB4_Monthly_Defect = Pivot_EDB4_Monthly_Defect.reindex(columns=col_name_EDB4)
Created_Month_EDB4 = Pivot_EDB4_Monthly_Defect['Created_Month'].values.tolist()
INVALID_EDB4 = Pivot_EDB4_Monthly_Defect['INVALID'].values.tolist()
VALID_EDB4 = Pivot_EDB4_Monthly_Defect['VALID'].values.tolist()
Total_EDB4 = Pivot_EDB4_Monthly_Defect['Total'].values.tolist()
Valid_Rate_EDB4 = Pivot_EDB4_Monthly_Defect['Valid_Rate'].values.tolist()
Created_Month_EDB4 = list()
INVALID_EDB4 = list()
VALID_EDB4 = list()
Total_EDB4 = list()
Valid_Rate_EDB4 = list()
for row in range(Pivot_EDB4_Monthly_Defect.shape[0]):
curCreated_Month_EDB4 = Pivot_EDB4_Monthly_Defect.loc[[row], ['Created_Month']].values[0][0]
Created_Month_EDB4.append(curCreated_Month_EDB4)
curINVALID_EDB4 = Pivot_EDB4_Monthly_Defect.loc[[row], ['INVALID']].values[0][0]
if pd.isnull(curINVALID_EDB4) == True:
curINVALID_EDB4 = int(0)
else:
curINVALID_EDB4 = int(curINVALID_EDB4)
INVALID_EDB4.append(curINVALID_EDB4)
curVALID_EDB4 = Pivot_EDB4_Monthly_Defect.loc[[row], ['VALID']].values[0][0]
if pd.isnull(curVALID_EDB4) == True:
curVALID_EDB4 = int(0)
else:
curVALID_EDB4 = int(curVALID_EDB4)
VALID_EDB4.append(curVALID_EDB4)
curTotal_EDB4 = curVALID_EDB4 + curINVALID_EDB4
if pd.isnull(curTotal_EDB4) == True:
curTotal_EDB4 = int(0)
else:
curTotal_EDB4 = int(curTotal_EDB4)
Total_EDB4.append(curTotal_EDB4)
curValid_Rate_EDB4 = curVALID_EDB4 / curTotal_EDB4
Valid_Rate_EDB4.append(curValid_Rate_EDB4)
Pivot_EDB4_Monthly_Defect['Created_Month'] = Created_Month_EDB4
Pivot_EDB4_Monthly_Defect['INVALID'] = INVALID_EDB4
Pivot_EDB4_Monthly_Defect['VALID'] = VALID_EDB4
Pivot_EDB4_Monthly_Defect['Total'] = Total_EDB4
Pivot_EDB4_Monthly_Defect['Valid_Rate'] = Valid_Rate_EDB4
# **************************************add for EDB4 Concerned_Customer_Defect **************************************************
# 读取数据
Pivot_Concerned_Customer_Defect_List = pd.read_excel(dataFile, sheet_name=outsheet_EDB4_Concerned_Customer_Defect_List)
col_name_Customer = Pivot_Concerned_Customer_Defect_List.columns.tolist()
print(col_name_Customer)
print(type(col_name_Customer))
if 'Duplicated with internal issue' in col_name_Customer:
print('Duplicated with internal issue is in col_name_Customer')
else:
print('Duplicated with internal issue is not in col_name_Customer,need add this column')
col_name_Customer.insert(1, 'Duplicated with internal issue')
if 'Missed in lab' in col_name_Customer:
print('Missed in lab is in col_name_Customer')
else:
print('Missed in lab is not in col_name_Customer,need add this column')
col_name_Customer.insert(2, 'Missed in lab')
if 'New requirement' in col_name_Customer:
print('New requirement is in col_name_Customer')
else:
print('New requirement is not in col_name_Customer,need add this column')
col_name_Customer.insert(3, 'New requirement')
if 'Performance issue' in col_name_Customer:
print('Performance issue is in col_name_Customer')
else:
print('Performance issue is not in col_name_Customer,need add this column')
col_name_Customer.insert(4, 'Performance issue')
if 'Vehicle scope' in col_name_Customer:
print('Vehicle scope is in col_name_Customer')
else:
print('Vehicle scope is not in col_name_Customer,need add this column')
col_name_Customer.insert(5, 'Vehicle scope')
if 'FSI scope' in col_name_Customer:
print('FSI scope is in col_name_Customer')
else:
print('FSI scope is not in col_name_Customer,need add this column')
col_name_Customer.insert(6, 'FSI scope')
if 'Software test scope' in col_name_Customer:
print('Software test scope is in col_name_Customer')
else:
print('Software test scope is not in col_name_Customer,need add this column')
col_name_Customer.insert(7, 'Software test scope')
if 'Out of test scope' in col_name_Customer:
print('Out of test scope is in col_name_Customer')
else:
print('Out of test scope is not in col_name_Customer,need add this column')
col_name_Customer.insert(8, 'Out of test scope')
col_name_Customer.insert(9, 'Total(report_by_customer_valid)')
col_name_Customer.insert(10, 'Total(already_reviewed)')
col_name_Customer.insert(11, 'Missed_Rate')
Pivot_Concerned_Customer_Defect_List = Pivot_Concerned_Customer_Defect_List.reindex(columns=col_name_Customer)
Created_Month_Customer = Pivot_Concerned_Customer_Defect_List['Created_Month'].values.tolist()
Duplicated_with_internal_issue_Customer = Pivot_Concerned_Customer_Defect_List['Duplicated with internal issue'].values.tolist()
Missed_in_lab_Customer = Pivot_Concerned_Customer_Defect_List['Missed in lab'].values.tolist()
New_requirement_Customer = Pivot_Concerned_Customer_Defect_List['New requirement'].values.tolist()
Performance_issue_Customer = Pivot_Concerned_Customer_Defect_List['Performance issue'].values.tolist()
Vehicle_scope_Customer = Pivot_Concerned_Customer_Defect_List['Vehicle scope'].values.tolist()
FSI_scope_Customer = Pivot_Concerned_Customer_Defect_List['FSI scope'].values.tolist()
Software_test_scope_Customer = Pivot_Concerned_Customer_Defect_List['Software test scope'].values.tolist()
Software_test_scope_Customer = Pivot_Concerned_Customer_Defect_List['Out of test scope'].values.tolist()
Created_Month_Customer = list()
Duplicated_with_internal_issue_Customer = list()
Missed_in_lab_Customer = list()
New_requirement_Customer = list()
Performance_issue_Customer = list()
Vehicle_scope_Customer = list()
FSI_scope_Customer = list()
Software_test_scope_Customer = list()
Out_of_test_scope_Customer = list()
Total_Customer = list()
Total_already_reviewed_Customer = list()
Missed_Rate_Customer = list()
for row in range(Pivot_Concerned_Customer_Defect_List.shape[0]):
curCreated_Month_Customer = Pivot_Concerned_Customer_Defect_List.loc[[row], ['Created_Month']].values[0][0]
Created_Month_Customer.append(curCreated_Month_Customer)
curDuplicated_with_internal_issue_Customer = Pivot_Concerned_Customer_Defect_List.loc[[row], ['Duplicated with internal issue']].values[0][0]
if pd.isnull(curDuplicated_with_internal_issue_Customer) == True:
curDuplicated_with_internal_issue_Customer = int(0)
else:
curDuplicated_with_internal_issue_Customer = int(curDuplicated_with_internal_issue_Customer)
Duplicated_with_internal_issue_Customer.append(curDuplicated_with_internal_issue_Customer)
curMissed_in_lab_Customer = Pivot_Concerned_Customer_Defect_List.loc[[row], ['Missed in lab']].values[0][0]
if pd.isnull(curMissed_in_lab_Customer) == True:
curMissed_in_lab_Customer = int(0)
else:
curMissed_in_lab_Customer = int(curMissed_in_lab_Customer)
Missed_in_lab_Customer.append(curMissed_in_lab_Customer)
curNew_requirement_Customer = Pivot_Concerned_Customer_Defect_List.loc[[row], ['New requirement']].values[0][0]
if pd.isnull(curNew_requirement_Customer) == True:
curNew_requirement_Customer = int(0)
else:
curNew_requirement_Customer = int(curNew_requirement_Customer)
New_requirement_Customer.append(curNew_requirement_Customer)
curPerformance_issue_Customer = Pivot_Concerned_Customer_Defect_List.loc[[row], ['Performance issue']].values[0][0]
if pd.isnull(curPerformance_issue_Customer) == True:
curPerformance_issue_Customer = int(0)
else:
curPerformance_issue_Customer = int(curPerformance_issue_Customer)
Performance_issue_Customer.append(curPerformance_issue_Customer)
curVehicle_scope_Customer = Pivot_Concerned_Customer_Defect_List.loc[[row], ['Vehicle scope']].values[0][0]
if pd.isnull(curVehicle_scope_Customer) == True:
curVehicle_scope_Customer = int(0)
else:
curVehicle_scope_Customer = int(curVehicle_scope_Customer)
Vehicle_scope_Customer.append(curVehicle_scope_Customer)
curFSI_scope_Customer = Pivot_Concerned_Customer_Defect_List.loc[[row], ['FSI scope']].values[0][0]
if pd.isnull(curFSI_scope_Customer) == True:
curFSI_scope_Customer = int(0)
else:
curFSI_scope_Customer = int(curFSI_scope_Customer)
FSI_scope_Customer.append(curFSI_scope_Customer)
curSoftware_test_scope_Customer = Pivot_Concerned_Customer_Defect_List.loc[[row], ['Software test scope']].values[0][0]
if pd.isnull(curSoftware_test_scope_Customer) == True:
curSoftware_test_scope_Customer = int(0)
else:
curSoftware_test_scope_Customer = int(curSoftware_test_scope_Customer)
Software_test_scope_Customer.append(curSoftware_test_scope_Customer)
curOut_of_test_scope_Customer = Pivot_Concerned_Customer_Defect_List.loc[[row], ['Out of test scope']].values[0][0]
if pd.isnull(curOut_of_test_scope_Customer) == True:
curOut_of_test_scope_Customer = int(0)
else:
curOut_of_test_scope_Customer = int(curOut_of_test_scope_Customer)
Out_of_test_scope_Customer.append(curOut_of_test_scope_Customer)
curTotal_already_reviewed_Customer = curDuplicated_with_internal_issue_Customer+curMissed_in_lab_Customer+curNew_requirement_Customer+curPerformance_issue_Customer+curVehicle_scope_Customer+curFSI_scope_Customer+curSoftware_test_scope_Customer+curOut_of_test_scope_Customer
Total_already_reviewed_Customer.append(curTotal_already_reviewed_Customer)
#curMissed_Rate_Customer = curMissed_in_lab_Customer / curTotal_already_reviewed_Customer
#Missed_Rate_Customer.append(curMissed_Rate_Customer)
Pivot_Concerned_Customer_Defect_List['Created_Month'] = Created_Month_Customer
Pivot_Concerned_Customer_Defect_List['Duplicated with internal issue'] = Duplicated_with_internal_issue_Customer
Pivot_Concerned_Customer_Defect_List['Missed in lab'] = Missed_in_lab_Customer
Pivot_Concerned_Customer_Defect_List['New requirement'] = New_requirement_Customer
Pivot_Concerned_Customer_Defect_List['Performance issue'] = Performance_issue_Customer
Pivot_Concerned_Customer_Defect_List['Vehicle scope'] = Vehicle_scope_Customer
Pivot_Concerned_Customer_Defect_List['FSI scope'] = FSI_scope_Customer
Pivot_Concerned_Customer_Defect_List['Software test scope'] = Software_test_scope_Customer
Pivot_Concerned_Customer_Defect_List['Out of test scope'] = Out_of_test_scope_Customer
Pivot_Concerned_Customer_Defect_List['Total(report_by_customer_valid)'] = Pivot_EDB4_Customer_Defect['VALID'] #Pivot_EDB4_Customer_Defect['VALID'] = VALID
Pivot_Concerned_Customer_Defect_List['Total(already_reviewed)'] = Total_already_reviewed_Customer
#Pivot_Concerned_Customer_Defect_List['Missed_Rate'] = Missed_Rate_Customer
# **************************************add for EDB4 Concerned_Customer_Defect end **************************************************
Pivot_Concerned_Customer_Defect_List['Missed_Rate'] = Pivot_Concerned_Customer_Defect_List['Missed in lab']/Pivot_EDB4_Customer_Defect['VALID']
Pivot_EDB4_Customer_Defect['Valid_Rate_of_Total'] = Pivot_Concerned_Customer_Defect_List['Missed in lab']/(Pivot_EDB4_Customer_Defect['Total'] + Pivot_EDB4_Monthly_Defect['Total'])
#################################################################
# EDB4 personal valid rate analysis --for internal 初步透视表
Pivot_EDB4_Project_Defect_Personal = EDB4_Project_Defect.pivot_table('Key', index='Reporter', columns='Valid_or_Not', aggfunc='count')
writer = pd.ExcelWriter(dataFile)
columns = ['Project', 'Key', 'Summary', 'Issue Type', 'Status', 'Priority', 'Resolution', 'Assignee', 'Reporter', 'Creator', 'Created', 'Last Viewed',
'Updated', 'Resolved', 'Affects Version/s', 'Fix Version/s', 'Component/s', 'Due Date', 'Origin', 'Problem Type', 'Severity', 'Project_Product', 'Product_Type',
'Concerned_or_Not', 'Assignee_Department', 'Assignee_Group', 'Reporter_Department', 'Reporter_Group', 'Reporter_Area', 'Test_Group', 'Valid_or_Not',
'Created_Month', 'Resolved_Day', 'Period', 'Time_To_Fix_Bugs', 'Customer_Defect_About_FCT']
data.to_excel(writer, index=False, sheet_name=insheet)
data.to_excel(writer, index=False, columns=columns, sheet_name=outsheet)
EDB4_Project_Defect.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Project_Defect)
Customer_Defect.to_excel(writer, index=False, sheet_name=outsheet_Customer_Defect)
EDB4_defect_Abnormal.to_excel(writer, index=False, sheet_name='EDB4_defect_Abnormal')
EDB4_componnents_blanks.to_excel(writer, index=False, sheet_name='EDB4_componnents_blanks')
Pivot_EDB4_Customer_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect)
Pivot_EDB4_Monthly_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect, startcol=0, startrow=(Pivot_EDB4_Customer_Defect_nrows + 4))
Pivot_Concerned_Customer_Defect_List.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Concerned_Customer_Defect_List)
Pivot_EDB4_Project_Defect_Personal.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Project_Defect_Personal)
writer.save()
# 读取数据
Pivot_EDB4_Project_Defect_Personal = pd.read_excel(dataFile, sheet_name=outsheet_EDB4_Project_Defect_Personal)
col_name = Pivot_EDB4_Project_Defect_Personal.columns.tolist()
# 在列索引为21的位置插入一列,列名为:Project_Product，刚插入时不会有值，整列都是NaN
col_name.insert(4, 'Total')
col_name.insert(5, 'Valid_Rate')
col_name.insert(6, 'Target_Valid_Defect')
Pivot_EDB4_Project_Defect_Personal = Pivot_EDB4_Project_Defect_Personal.reindex(columns=col_name)
Reporter = Pivot_EDB4_Project_Defect_Personal['Reporter'].values.tolist() # reporter
INVALID = Pivot_EDB4_Project_Defect_Personal['INVALID'].values.tolist() # INVALID
VALID = Pivot_EDB4_Project_Defect_Personal['VALID'].values.tolist() # VALID
Total = Pivot_EDB4_Project_Defect_Personal['Total'].values.tolist() # Total
Valid_Rate = Pivot_EDB4_Project_Defect_Personal['Valid_Rate'].values.tolist() # Valid_Rate
Target_Valid_Defect = Pivot_EDB4_Project_Defect_Personal['Target_Valid_Defect'].values.tolist() # Target_Valid_Defect
Reporter = list()
INVALID = list()
VALID = list()
Total = list()
Valid_Rate = list()
Target_Valid_Defect = list()
for row in range(Pivot_EDB4_Project_Defect_Personal.shape[0]):
curReporter = Pivot_EDB4_Project_Defect_Personal.loc[[row], ['Reporter']].values[0][0].split(' (')[0]
Reporter.append(curReporter)
curINVALID = Pivot_EDB4_Project_Defect_Personal.loc[[row], ['INVALID']].values[0][0]
if pd.isnull(curINVALID) == True:
curINVALID = int(0)
else:
curINVALID = int(curINVALID)
INVALID.append(curINVALID)
curVALID = Pivot_EDB4_Project_Defect_Personal.loc[[row], ['VALID']].values[0][0]
if pd.isnull(curVALID) == True:
curVALID = int(0)
else:
curVALID = int(curVALID)
VALID.append(curVALID)
curTotal = curVALID + curINVALID
if pd.isnull(curTotal) == True:
curTotal = int(0)
else:
curTotal = int(curTotal)
Total.append(curTotal)
curValid_Rate = curVALID / curTotal
Valid_Rate.append(curValid_Rate)
curTarget_Valid_Defect = 0.9*curVALID
Target_Valid_Defect.append(curTarget_Valid_Defect)
Pivot_EDB4_Project_Defect_Personal['Reporter'] = Reporter
Pivot_EDB4_Project_Defect_Personal['INVALID'] = INVALID
Pivot_EDB4_Project_Defect_Personal['VALID'] = VALID
Pivot_EDB4_Project_Defect_Personal['Total'] = Total
Pivot_EDB4_Project_Defect_Personal['Valid_Rate'] = Valid_Rate
Pivot_EDB4_Project_Defect_Personal['Target_Valid_Defect'] = Target_Valid_Defect
#product defect analysis --for external 初步透视表
Pivot_EDB4_Product_Type_Defect_Monthly = EDB4_Project_Defect.pivot_table('Key',index='Created_Month',columns='Product_Type',aggfunc='count')
writer = pd.ExcelWriter(dataFile)
columns = ['Project', 'Key', 'Summary', 'Issue Type', 'Status', 'Priority', 'Resolution', 'Assignee', 'Reporter', 'Creator', 'Created', 'Last Viewed',
'Updated', 'Resolved', 'Affects Version/s', 'Fix Version/s', 'Component/s', 'Due Date', 'Origin', 'Problem Type', 'Severity', 'Project_Product', 'Product_Type',
'Concerned_or_Not', 'Assignee_Department', 'Assignee_Group', 'Reporter_Department', 'Reporter_Group', 'Reporter_Area', 'Test_Group', 'Valid_or_Not',
'Created_Month', 'Resolved_Day', 'Period', 'Time_To_Fix_Bugs', 'Customer_Defect_About_FCT']
data.to_excel(writer, index=False, sheet_name=insheet)
data.to_excel(writer, index=False, columns=columns, sheet_name=outsheet)
EDB4_Project_Defect.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Project_Defect)
Customer_Defect.to_excel(writer, index=False, sheet_name=outsheet_Customer_Defect)
EDB4_defect_Abnormal.to_excel(writer, index=False, sheet_name='EDB4_defect_Abnormal')
EDB4_componnents_blanks.to_excel(writer, index=False, sheet_name='EDB4_componnents_blanks')
Pivot_EDB4_Customer_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect)
Pivot_EDB4_Monthly_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect, startcol=0, startrow=(Pivot_EDB4_Customer_Defect_nrows + 4))
Pivot_Concerned_Customer_Defect_List.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Concerned_Customer_Defect_List)
Pivot_EDB4_Project_Defect_Personal.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Project_Defect_Personal)
Pivot_EDB4_Product_Type_Defect_Monthly.to_excel(writer,index=True,sheet_name=outsheet_EDB4_ProductType_Defect_Monthly)
writer.save()
# 读取数据
Pivot_EDB4_Product_Type_Defect_Monthly = pd.read_excel(dataFile, sheet_name=outsheet_EDB4_ProductType_Defect_Monthly)
# 将数据框的列名全部提取出来存放在列表里
col_name = Pivot_EDB4_Product_Type_Defect_Monthly.columns.tolist()
Created_Month = Pivot_EDB4_Product_Type_Defect_Monthly['Created_Month'].values.tolist() # Product_Type
Front_Radar = Pivot_EDB4_Product_Type_Defect_Monthly['Front_Radar'].values.tolist() # Front_Radar
Corner_Radar = Pivot_EDB4_Product_Type_Defect_Monthly['Corner_Radar'].values.tolist() # Corner_Radar
DASy = Pivot_EDB4_Product_Type_Defect_Monthly['DASy'].values.tolist() # DASy
MPC = Pivot_EDB4_Product_Type_Defect_Monthly['MPC'].values.tolist() # MPC
Created_Month = list()
Front_Radar = list()
Corner_Radar = list()
DASy = list()
MPC = list()
for row in range(Pivot_EDB4_Product_Type_Defect_Monthly.shape[0]):
curCreated_Month = Pivot_EDB4_Product_Type_Defect_Monthly.loc[[row], ['Created_Month']].values[0][0]
Created_Month.append(curCreated_Month)
curFront_Radar = Pivot_EDB4_Product_Type_Defect_Monthly.loc[[row], ['Front_Radar']].values[0][0]
if pd.isnull(curFront_Radar) == True:
curFront_Radar = int(0)
else:
curFront_Radar = int(curFront_Radar)
Front_Radar.append(curFront_Radar)
curCorner_Radar = Pivot_EDB4_Product_Type_Defect_Monthly.loc[[row], ['Corner_Radar']].values[0][0]
if pd.isnull(curCorner_Radar) == True:
curCorner_Radar = int(0)
else:
curCorner_Radar = int(curCorner_Radar)
Corner_Radar.append(curCorner_Radar)
curDASy = Pivot_EDB4_Product_Type_Defect_Monthly.loc[[row], ['DASy']].values[0][0]
if pd.isnull(curDASy) == True:
curDASy = int(0)
else:
curDASy = int(curDASy)
DASy.append(curDASy)
curMPC = Pivot_EDB4_Product_Type_Defect_Monthly.loc[[row], ['MPC']].values[0][0]
if pd.isnull(curMPC) == True:
curMPC = int(0)
else:
curMPC = int(curMPC)
MPC.append(curMPC)
Pivot_EDB4_Product_Type_Defect_Monthly['Created_Month'] = Created_Month
Pivot_EDB4_Product_Type_Defect_Monthly['Front_Radar'] = Front_Radar
Pivot_EDB4_Product_Type_Defect_Monthly['Corner_Radar'] = Corner_Radar
Pivot_EDB4_Product_Type_Defect_Monthly['DASy'] = DASy
Pivot_EDB4_Product_Type_Defect_Monthly['MPC'] = MPC
Front_Radar_total_count = sum(Pivot_EDB4_Product_Type_Defect_Monthly['Front_Radar'].values)
Corner_Radar_total_count = sum(Pivot_EDB4_Product_Type_Defect_Monthly['Corner_Radar'].values)
DASy_total_count = sum(Pivot_EDB4_Product_Type_Defect_Monthly['DASy'].values)
MPC_total_count = sum(Pivot_EDB4_Product_Type_Defect_Monthly['MPC'].values)
Pivot_EDB4_Product_Type_Defect_Monthly.append([{'Created_Month':"Total",'Front_Radar':Front_Radar_total_count, 'Corner_Radar':Corner_Radar_total_count,'DASy':DASy_total_count, 'MPC':MPC_total_count}], ignore_index=True)
#monthly EDB4 valid rate analysis --for external 初步透视表
Pivot_EDB4_Project_Defect_Monthly = EDB4_Project_Defect.pivot_table('Key',index='Project',columns='Created_Month',aggfunc='count')
writer = pd.ExcelWriter(dataFile)
columns = ['Project', 'Key', 'Summary', 'Issue Type', 'Status', 'Priority', 'Resolution', 'Assignee', 'Reporter', 'Creator', 'Created', 'Last Viewed',
'Updated', 'Resolved', 'Affects Version/s', 'Fix Version/s', 'Component/s', 'Due Date', 'Origin', 'Problem Type', 'Severity', 'Project_Product', 'Product_Type',
'Concerned_or_Not', 'Assignee_Department', 'Assignee_Group', 'Reporter_Department', 'Reporter_Group', 'Reporter_Area', 'Test_Group', 'Valid_or_Not',
'Created_Month', 'Resolved_Day', 'Period', 'Time_To_Fix_Bugs', 'Customer_Defect_About_FCT']
data.to_excel(writer, index=False, sheet_name=insheet)
data.to_excel(writer, index=False, columns=columns, sheet_name=outsheet)
EDB4_Project_Defect.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Project_Defect)
Customer_Defect.to_excel(writer, index=False, sheet_name=outsheet_Customer_Defect)
EDB4_defect_Abnormal.to_excel(writer, index=False, sheet_name='EDB4_defect_Abnormal')
EDB4_componnents_blanks.to_excel(writer, index=False, sheet_name='EDB4_componnents_blanks')
Pivot_EDB4_Customer_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect)
Pivot_EDB4_Monthly_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect, startcol=0, startrow=(Pivot_EDB4_Customer_Defect_nrows + 4))
Pivot_Concerned_Customer_Defect_List.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Concerned_Customer_Defect_List)
Pivot_EDB4_Project_Defect_Personal.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Project_Defect_Personal)
Pivot_EDB4_Product_Type_Defect_Monthly.to_excel(writer,index=True,sheet_name=outsheet_EDB4_ProductType_Defect_Monthly)
Pivot_EDB4_Project_Defect_Monthly.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Project_Defect_Monthly)
writer.save()
# 读取数据
Pivot_EDB4_Project_Defect_Monthly = pd.read_excel(dataFile, sheet_name=outsheet_EDB4_Project_Defect_Monthly)
col_name = Pivot_EDB4_Project_Defect_Monthly.columns.tolist()
#col_name.insert(21, 'Project_Total')
#Pivot_EDB4_Project_Defect_Monthly = Pivot_EDB4_Project_Defect_Monthly.reindex(columns=col_name)
#Project_Total = Pivot_EDB4_Project_Defect_Monthly['Project_Total'].values.tolist()
#Project_Total = list()
a = 0
if ('2021_01' == col_name):
a = 1
elif (['Project', '2021_01', '2021_02'] == col_name):
a = 2
elif (['Project', '2021_01', '2021_02', '2021_03'] == col_name):
a = 3
elif (['Project', '2021_01', '2021_02', '2021_03', '2021_04'] == col_name):
a = 4
elif (['Project', '2021_01', '2021_02', '2021_03', '2021_04', '2021_05'] == col_name):
a = 5
elif (['Project', '2021_01', '2021_02', '2021_03', '2021_04', '2021_05', '2021_06'] == col_name):
a = 6
elif (['Project', '2021_01', '2021_02', '2021_03', '2021_04', '2021_05', '2021_06', '2021_07'] == col_name):
a = 7
elif (['Project', '2021_01', '2021_02', '2021_03', '2021_04', '2021_05', '2021_06', '2021_07', '2021_08'] == col_name):
a = 8
elif (['Project', '2021_01', '2021_02', '2021_03', '2021_04', '2021_05', '2021_06', '2021_07', '2021_08', '2021_09'] == col_name):
a = 9
elif (['Project', '2021_01', '2021_02', '2021_03', '2021_04', '2021_05', '2021_06', '2021_07', '2021_08', '2021_09', '2021_10'] == col_name):
a = 10
elif (['Project', '2021_01', '2021_02', '2021_03', '2021_04', '2021_05', '2021_06', '2021_07', '2021_08', '2021_09', '2021_10', '2021_11'] == col_name):
a = 11
elif (['Project', '2021_01', '2021_02', '2021_03', '2021_04', '2021_05', '2021_06', '2021_07', '2021_08', '2021_09', '2021_10', '2021_11', '2021_12'] == col_name):
a = 12
else:
pass
Project = list()
January = list()
February = list()
March = list()
April = list()
May = list()
June = list()
July = list()
August = list()
September = list()
October = list()
November = list()
December = list()
for row in range(Pivot_EDB4_Project_Defect_Monthly.shape[0]):
curProject = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['Project']].values[0][0]
Project.append(curProject)
if (a == 1):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
else:
pass
if (a == 2):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
else:
pass
if (a == 3):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
else:
pass
if (a == 4):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
cur2021_04 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_04']].values[0][0]
if pd.isnull(cur2021_04) == True:
cur2021_04 = int(0)
else:
cur2021_04 = int(cur2021_04)
April.append(cur2021_04)
else:
pass
if (a == 5):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
cur2021_04 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_04']].values[0][0]
if pd.isnull(cur2021_04) == True:
cur2021_04 = int(0)
else:
cur2021_04 = int(cur2021_04)
April.append(cur2021_04)
cur2021_05 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_05']].values[0][0]
if pd.isnull(cur2021_05) == True:
cur2021_05 = int(0)
else:
cur2021_05 = int(cur2021_05)
May.append(cur2021_05)
else:
pass
if (a == 6):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
cur2021_04 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_04']].values[0][0]
if pd.isnull(cur2021_04) == True:
cur2021_04 = int(0)
else:
cur2021_04 = int(cur2021_04)
April.append(cur2021_04)
cur2021_05 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_05']].values[0][0]
if pd.isnull(cur2021_05) == True:
cur2021_05 = int(0)
else:
cur2021_05 = int(cur2021_05)
May.append(cur2021_05)
cur2021_06 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_06']].values[0][0]
if pd.isnull(cur2021_06) == True:
cur2021_06 = int(0)
else:
cur2021_06 = int(cur2021_06)
June.append(cur2021_06)
else:
pass
if (a == 7):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
cur2021_04 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_04']].values[0][0]
if pd.isnull(cur2021_04) == True:
cur2021_04 = int(0)
else:
cur2021_04 = int(cur2021_04)
April.append(cur2021_04)
cur2021_05 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_05']].values[0][0]
if pd.isnull(cur2021_05) == True:
cur2021_05 = int(0)
else:
cur2021_05 = int(cur2021_05)
May.append(cur2021_05)
cur2021_06 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_06']].values[0][0]
if pd.isnull(cur2021_06) == True:
cur2021_06 = int(0)
else:
cur2021_06 = int(cur2021_06)
June.append(cur2021_06)
cur2021_07 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_07']].values[0][0]
if pd.isnull(cur2021_07) == True:
cur2021_07 = int(0)
else:
cur2021_07 = int(cur2021_07)
July.append(cur2021_07)
else:
pass
if (a == 8):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
cur2021_04 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_04']].values[0][0]
if pd.isnull(cur2021_04) == True:
cur2021_04 = int(0)
else:
cur2021_04 = int(cur2021_04)
April.append(cur2021_04)
cur2021_05 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_05']].values[0][0]
if pd.isnull(cur2021_05) == True:
cur2021_05 = int(0)
else:
cur2021_05 = int(cur2021_05)
May.append(cur2021_05)
cur2021_06 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_06']].values[0][0]
if pd.isnull(cur2021_06) == True:
cur2021_06 = int(0)
else:
cur2021_06 = int(cur2021_06)
June.append(cur2021_06)
cur2021_07 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_07']].values[0][0]
if pd.isnull(cur2021_07) == True:
cur2021_07 = int(0)
else:
cur2021_07 = int(cur2021_07)
July.append(cur2021_07)
cur2021_08 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_08']].values[0][0]
if pd.isnull(cur2021_08) == True:
cur2021_08 = int(0)
else:
cur2021_08 = int(cur2021_08)
August.append(cur2021_08)
else:
pass
if (a == 9):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
cur2021_04 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_04']].values[0][0]
if pd.isnull(cur2021_04) == True:
cur2021_04 = int(0)
else:
cur2021_04 = int(cur2021_04)
April.append(cur2021_04)
cur2021_05 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_05']].values[0][0]
if pd.isnull(cur2021_05) == True:
cur2021_05 = int(0)
else:
cur2021_05 = int(cur2021_05)
May.append(cur2021_05)
cur2021_06 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_06']].values[0][0]
if pd.isnull(cur2021_06) == True:
cur2021_06 = int(0)
else:
cur2021_06 = int(cur2021_06)
June.append(cur2021_06)
cur2021_07 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_07']].values[0][0]
if pd.isnull(cur2021_07) == True:
cur2021_07 = int(0)
else:
cur2021_07 = int(cur2021_07)
July.append(cur2021_07)
cur2021_08 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_08']].values[0][0]
if pd.isnull(cur2021_08) == True:
cur2021_08 = int(0)
else:
cur2021_08 = int(cur2021_08)
August.append(cur2021_08)
cur2021_09 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_09']].values[0][0]
if pd.isnull(cur2021_09) == True:
cur2021_09 = int(0)
else:
cur2021_09 = int(cur2021_09)
September.append(cur2021_09)
else:
pass
if (a == 10):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
cur2021_04 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_04']].values[0][0]
if pd.isnull(cur2021_04) == True:
cur2021_04 = int(0)
else:
cur2021_04 = int(cur2021_04)
April.append(cur2021_04)
cur2021_05 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_05']].values[0][0]
if pd.isnull(cur2021_05) == True:
cur2021_05 = int(0)
else:
cur2021_05 = int(cur2021_05)
May.append(cur2021_05)
cur2021_06 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_06']].values[0][0]
if pd.isnull(cur2021_06) == True:
cur2021_06 = int(0)
else:
cur2021_06 = int(cur2021_06)
June.append(cur2021_06)
cur2021_07 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_07']].values[0][0]
if pd.isnull(cur2021_07) == True:
cur2021_07 = int(0)
else:
cur2021_07 = int(cur2021_07)
July.append(cur2021_07)
cur2021_08 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_08']].values[0][0]
if pd.isnull(cur2021_08) == True:
cur2021_08 = int(0)
else:
cur2021_08 = int(cur2021_08)
August.append(cur2021_08)
cur2021_09 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_09']].values[0][0]
if pd.isnull(cur2021_09) == True:
cur2021_09 = int(0)
else:
cur2021_09 = int(cur2021_09)
September.append(cur2021_09)
cur2021_10 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_10']].values[0][0]
if pd.isnull(cur2021_10) == True:
cur2021_10 = int(0)
else:
cur2021_10 = int(cur2021_10)
October.append(cur2021_10)
else:
pass
if (a == 11):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
cur2021_04 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_04']].values[0][0]
if pd.isnull(cur2021_04) == True:
cur2021_04 = int(0)
else:
cur2021_04 = int(cur2021_04)
April.append(cur2021_04)
cur2021_05 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_05']].values[0][0]
if pd.isnull(cur2021_05) == True:
cur2021_05 = int(0)
else:
cur2021_05 = int(cur2021_05)
May.append(cur2021_05)
cur2021_06 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_06']].values[0][0]
if pd.isnull(cur2021_06) == True:
cur2021_06 = int(0)
else:
cur2021_06 = int(cur2021_06)
June.append(cur2021_06)
cur2021_07 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_07']].values[0][0]
if pd.isnull(cur2021_07) == True:
cur2021_07 = int(0)
else:
cur2021_07 = int(cur2021_07)
July.append(cur2021_07)
cur2021_08 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_08']].values[0][0]
if pd.isnull(cur2021_08) == True:
cur2021_08 = int(0)
else:
cur2021_08 = int(cur2021_08)
August.append(cur2021_08)
cur2021_09 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_09']].values[0][0]
if pd.isnull(cur2021_09) == True:
cur2021_09 = int(0)
else:
cur2021_09 = int(cur2021_09)
September.append(cur2021_09)
cur2021_10 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_10']].values[0][0]
if pd.isnull(cur2021_10) == True:
cur2021_10 = int(0)
else:
cur2021_10 = int(cur2021_10)
October.append(cur2021_10)
cur2021_11 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_11']].values[0][0]
if pd.isnull(cur2021_11) == True:
cur2021_11 = int(0)
else:
cur2021_11 = int(cur2021_11)
November.append(cur2021_11)
else:
pass
if (a == 12):
cur2021_01 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_01']].values[0][0]
if pd.isnull(cur2021_01) == True:
cur2021_01 = int(0)
else:
cur2021_01 = int(cur2021_01)
January.append(cur2021_01)
cur2021_02 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_02']].values[0][0]
if pd.isnull(cur2021_02) == True:
cur2021_02 = int(0)
else:
cur2021_02 = int(cur2021_02)
February.append(cur2021_02)
cur2021_03 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_03']].values[0][0]
if pd.isnull(cur2021_03) == True:
cur2021_03 = int(0)
else:
cur2021_03 = int(cur2021_03)
March.append(cur2021_03)
cur2021_04 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_04']].values[0][0]
if pd.isnull(cur2021_04) == True:
cur2021_04 = int(0)
else:
cur2021_04 = int(cur2021_04)
April.append(cur2021_04)
cur2021_05 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_05']].values[0][0]
if pd.isnull(cur2021_05) == True:
cur2021_05 = int(0)
else:
cur2021_05 = int(cur2021_05)
May.append(cur2021_05)
cur2021_06 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_06']].values[0][0]
if pd.isnull(cur2021_06) == True:
cur2021_06 = int(0)
else:
cur2021_06 = int(cur2021_06)
June.append(cur2021_06)
cur2021_07 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_07']].values[0][0]
if pd.isnull(cur2021_07) == True:
cur2021_07 = int(0)
else:
cur2021_07 = int(cur2021_07)
July.append(cur2021_07)
cur2021_08 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_08']].values[0][0]
if pd.isnull(cur2021_08) == True:
cur2021_08 = int(0)
else:
cur2021_08 = int(cur2021_08)
August.append(cur2021_08)
cur2021_09 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_09']].values[0][0]
if pd.isnull(cur2021_09) == True:
cur2021_09 = int(0)
else:
cur2021_09 = int(cur2021_09)
September.append(cur2021_09)
cur2021_10 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_10']].values[0][0]
if pd.isnull(cur2021_10) == True:
cur2021_10 = int(0)
else:
cur2021_10 = int(cur2021_10)
October.append(cur2021_10)
cur2021_11 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_11']].values[0][0]
if pd.isnull(cur2021_11) == True:
cur2021_11 = int(0)
else:
cur2021_11 = int(cur2021_11)
November.append(cur2021_11)
cur2021_12 = Pivot_EDB4_Project_Defect_Monthly.loc[[row], ['2021_12']].values[0][0]
if pd.isnull(cur2021_12) == True:
cur2021_12 = int(0)
else:
cur2021_12 = int(cur2021_12)
December.append(cur2021_12)
else:
pass
Pivot_EDB4_Project_Defect_Monthly['Project'] = Project
if (a == 1):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count}
if (a == 2):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count}
if (a == 3):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count, '2021_03': March_count}
if (a == 4):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
Pivot_EDB4_Project_Defect_Monthly['2021_04'] = April
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
April_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_04'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count,
'2021_03': March_count, '2021_04': April_count}
if (a == 5):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
Pivot_EDB4_Project_Defect_Monthly['2021_04'] = April
Pivot_EDB4_Project_Defect_Monthly['2021_05'] = May
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
April_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_04'].values)
May_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_05'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count,
'2021_03': March_count, '2021_04': April_count, '2021_05': May_count}
if (a == 6):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
Pivot_EDB4_Project_Defect_Monthly['2021_04'] = April
Pivot_EDB4_Project_Defect_Monthly['2021_05'] = May
Pivot_EDB4_Project_Defect_Monthly['2021_06'] = June
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
April_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_04'].values)
May_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_05'].values)
June_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_06'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count,
'2021_03': March_count, '2021_04': April_count, '2021_05': May_count, '2021_06': June_count}
if (a == 7):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
Pivot_EDB4_Project_Defect_Monthly['2021_04'] = April
Pivot_EDB4_Project_Defect_Monthly['2021_05'] = May
Pivot_EDB4_Project_Defect_Monthly['2021_06'] = June
Pivot_EDB4_Project_Defect_Monthly['2021_07'] = July
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
April_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_04'].values)
May_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_05'].values)
June_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_06'].values)
July_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_07'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count, '2021_03': March_count,
'2021_04': April_count, '2021_05': May_count, '2021_06': June_count, '2021_07': July_count}
if (a == 8):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
Pivot_EDB4_Project_Defect_Monthly['2021_04'] = April
Pivot_EDB4_Project_Defect_Monthly['2021_05'] = May
Pivot_EDB4_Project_Defect_Monthly['2021_06'] = June
Pivot_EDB4_Project_Defect_Monthly['2021_07'] = July
Pivot_EDB4_Project_Defect_Monthly['2021_08'] = August
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
April_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_04'].values)
May_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_05'].values)
June_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_06'].values)
July_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_07'].values)
August_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_08'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count, '2021_03': March_count,
'2021_04': April_count, '2021_05': May_count, '2021_06': June_count, '2021_07': July_count, '2021_08': August_count}
if (a == 9):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
Pivot_EDB4_Project_Defect_Monthly['2021_04'] = April
Pivot_EDB4_Project_Defect_Monthly['2021_05'] = May
Pivot_EDB4_Project_Defect_Monthly['2021_06'] = June
Pivot_EDB4_Project_Defect_Monthly['2021_07'] = July
Pivot_EDB4_Project_Defect_Monthly['2021_08'] = August
Pivot_EDB4_Project_Defect_Monthly['2021_09'] = September
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
April_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_04'].values)
May_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_05'].values)
June_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_06'].values)
July_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_07'].values)
August_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_08'].values)
September_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_09'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count, '2021_03': March_count,
'2021_04': April_count, '2021_05': May_count, '2021_06': June_count, '2021_07': July_count, '2021_08': August_count,
'2021_09': September_count}
if (a == 10):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
Pivot_EDB4_Project_Defect_Monthly['2021_04'] = April
Pivot_EDB4_Project_Defect_Monthly['2021_05'] = May
Pivot_EDB4_Project_Defect_Monthly['2021_06'] = June
Pivot_EDB4_Project_Defect_Monthly['2021_07'] = July
Pivot_EDB4_Project_Defect_Monthly['2021_08'] = August
Pivot_EDB4_Project_Defect_Monthly['2021_09'] = September
Pivot_EDB4_Project_Defect_Monthly['2021_10'] = October
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
April_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_04'].values)
May_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_05'].values)
June_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_06'].values)
July_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_07'].values)
August_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_08'].values)
September_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_09'].values)
October_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_10'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count, '2021_03': March_count,
'2021_04': April_count, '2021_05': May_count, '2021_06': June_count, '2021_07': July_count, '2021_08': August_count,
'2021_09': September_count, '2021_10': October_count}
if (a == 11):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
Pivot_EDB4_Project_Defect_Monthly['2021_04'] = April
Pivot_EDB4_Project_Defect_Monthly['2021_05'] = May
Pivot_EDB4_Project_Defect_Monthly['2021_06'] = June
Pivot_EDB4_Project_Defect_Monthly['2021_07'] = July
Pivot_EDB4_Project_Defect_Monthly['2021_08'] = August
Pivot_EDB4_Project_Defect_Monthly['2021_09'] = September
Pivot_EDB4_Project_Defect_Monthly['2021_10'] = October
Pivot_EDB4_Project_Defect_Monthly['2021_11'] = November
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
April_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_04'].values)
May_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_05'].values)
June_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_06'].values)
July_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_07'].values)
August_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_08'].values)
September_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_09'].values)
October_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_10'].values)
November_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_11'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count, '2021_03': March_count,
'2021_04': April_count, '2021_05': May_count, '2021_06': June_count, '2021_07': July_count, '2021_08': August_count,
'2021_09': September_count, '2021_10': October_count, '2021_11': November_count}
if (a == 12):
Pivot_EDB4_Project_Defect_Monthly['2021_01'] = January
Pivot_EDB4_Project_Defect_Monthly['2021_02'] = February
Pivot_EDB4_Project_Defect_Monthly['2021_03'] = March
Pivot_EDB4_Project_Defect_Monthly['2021_04'] = April
Pivot_EDB4_Project_Defect_Monthly['2021_05'] = May
Pivot_EDB4_Project_Defect_Monthly['2021_06'] = June
Pivot_EDB4_Project_Defect_Monthly['2021_07'] = July
Pivot_EDB4_Project_Defect_Monthly['2021_08'] = August
Pivot_EDB4_Project_Defect_Monthly['2021_09'] = September
Pivot_EDB4_Project_Defect_Monthly['2021_10'] = October
Pivot_EDB4_Project_Defect_Monthly['2021_11'] = November
Pivot_EDB4_Project_Defect_Monthly['2021_12'] = December
January_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_01'].values)
February_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_02'].values)
March_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_03'].values)
April_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_04'].values)
May_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_05'].values)
June_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_06'].values)
July_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_07'].values)
August_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_08'].values)
September_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_09'].values)
October_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_10'].values)
November_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_11'].values)
December_count = sum(Pivot_EDB4_Project_Defect_Monthly['2021_12'].values)
Pivot_EDB4_Project_Defect_Monthly.loc[60] = {'Project': "Monthly_Total", '2021_01': January_count, '2021_02': February_count, '2021_03': March_count,
'2021_04': April_count, '2021_05': May_count, '2021_06': June_count, '2021_07': July_count, '2021_08': August_count,
'2021_09': September_count, '2021_10': October_count, '2021_11': November_count, '2021_12': December_count}
Pivot_EDB4_Project_Defect_Project_Total = Pivot_EDB4_Project_Defect_Monthly.sum(axis=1)
writer = pd.ExcelWriter(dataFile)
columns = ['Project', 'Key', 'Summary', 'Issue Type', 'Status', 'Priority', 'Resolution', 'Assignee', 'Reporter', 'Creator', 'Created', 'Last Viewed',
'Updated', 'Resolved', 'Affects Version/s', 'Fix Version/s', 'Component/s', 'Due Date', 'Origin', 'Problem Type', 'Severity', 'Project_Product', 'Product_Type',
'Concerned_or_Not', 'Assignee_Department', 'Assignee_Group', 'Reporter_Department', 'Reporter_Group', 'Reporter_Area', 'Test_Group', 'Valid_or_Not',
'Created_Month', 'Resolved_Day', 'Period', 'Time_To_Fix_Bugs', 'Customer_Defect_About_FCT']
data.to_excel(writer, index=False, sheet_name=insheet)
data.to_excel(writer, index=False, columns=columns, sheet_name=outsheet)
EDB4_Project_Defect.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Project_Defect)
Customer_Defect.to_excel(writer, index=False, sheet_name=outsheet_Customer_Defect)
EDB4_defect_Abnormal.to_excel(writer, index=False, sheet_name='EDB4_defect_Abnormal')
EDB4_componnents_blanks.to_excel(writer, index=False, sheet_name='EDB4_componnents_blanks')
Pivot_EDB4_Customer_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect)
Pivot_EDB4_Monthly_Defect.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Customer_Defect, startcol=0, startrow=(Pivot_EDB4_Customer_Defect_nrows + 4))
Pivot_Concerned_Customer_Defect_List.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Concerned_Customer_Defect_List)
Pivot_EDB4_Project_Defect_Personal.to_excel(writer, index=True, sheet_name=outsheet_EDB4_Project_Defect_Personal)
Pivot_EDB4_Product_Type_Defect_Monthly.to_excel(writer,index=True,sheet_name=outsheet_EDB4_ProductType_Defect_Monthly)
Pivot_EDB4_Project_Defect_Monthly.to_excel(writer,index=False,sheet_name=outsheet_EDB4_Project_Defect_Monthly)
Pivot_EDB4_Project_Defect_Project_Total.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Project_Defect_Monthly, startcol=(a+1), startrow=0)
writer.save()
Pivot_EDB4_Project_Defect_Monthly = pd.read_excel(data_path, sheet_name=outsheet_EDB4_Project_Defect_Monthly)
if (a == 1):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','Project_Total']
if (a == 2):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','Project_Total']
if (a == 3):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','Project_Total']
if (a == 4):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','2021_04','Project_Total']
if (a == 5):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','2021_04','2021_05','Project_Total']
if (a == 6):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','2021_04','2021_05','2021_06','Project_Total']
if (a == 7):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','2021_04','2021_05','2021_06','2021_07','Project_Total']
if (a == 8):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','2021_04','2021_05','2021_06','2021_07',
'2021_08','Project_Total']
if (a == 9):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','2021_04','2021_05','2021_06','2021_07',
'2021_08','2021_09','Project_Total']
if (a == 10):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','2021_04','2021_05','2021_06','2021_07',
'2021_08','2021_09','2021_10','Project_Total']
if (a == 11):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','2021_04','2021_05','2021_06','2021_07',
'2021_08','2021_09','2021_10','2021_11','Project_Total']
if (a == 12):
Pivot_EDB4_Project_Defect_Monthly.columns=['Project','2021_01','2021_02','2021_03','2021_04','2021_05','2021_06','2021_07',
'2021_08','2021_09','2021_10','2021_11','2021_12','Project_Total']
else:
pass
#############################################################################################################
#Pivot_EDB4_components_type
# def delete_space(x):
# if type(x) is str:
# return x.strip(' ')
# else:
# return x
number_components =pd.DataFrame(columns=('Key','Created_Month','Component/s'))
Copy_for_EDB4_components = EDB4_Project_Defect.copy(deep = True).reset_index()
Copy_for_EDB4_components.dropna(subset=['Component/s'],inplace=True)
All_components = Copy_for_EDB4_components['Component/s']
Key = Copy_for_EDB4_components['Key']
Created_Month = Copy_for_EDB4_components['Created_Month']
Num = 0
for i in range(len(All_components)):
components_pieces = str(All_components.values[i]).split(',')
for j in range(len(components_pieces)):
number_components.loc[Num,'Key'] = Key.values[i]
number_components.loc[Num,'Created_Month'] = Created_Month.values[i]
number_components.loc[Num,'Component/s'] = components_pieces[j].strip(' ')
Num = Num + 1
# number_components.applymap(delete_space)
Pivot_EDB4_components_type = number_components.pivot_table('Key',index='Created_Month', columns='Component/s', aggfunc='count').fillna(0)
print('generate Pivot_EDB4_components_type')
#############################################################################################################
# 保存到excel
writer = pd.ExcelWriter(dataFile)
columns = ['Project', 'Key', 'Summary', 'Issue Type', 'Status', 'Priority', 'Resolution', 'Assignee', 'Reporter', 'Creator', 'Created', 'Last Viewed',
'Updated', 'Resolved', 'Affects Version/s', 'Fix Version/s', 'Component/s', 'Due Date', 'Origin', 'Problem Type', 'Severity', 'Project_Product', 'Product_Type',
'Concerned_or_Not', 'Assignee_Department', 'Assignee_Group', 'Reporter_Department', 'Reporter_Group', 'Reporter_Area', 'Test_Group', 'Valid_or_Not',
'Created_Month', 'Resolved_Day', 'Period', 'Time_To_Fix_Bugs', 'Customer_Defect_About_FCT']
data.to_excel(writer,index=False,sheet_name=insheet)
data.to_excel(writer,index=False,sheet_name=outsheet)
Customer_Defect.to_excel(writer,index=False,sheet_name=outsheet_Customer_Defect)
EDB4_Project_Defect.to_excel(writer,index=False,sheet_name=outsheet_EDB4_Project_Defect)
EDB4_defect_Abnormal.to_excel(writer, index=False, sheet_name='EDB4_defect_Abnormal')
EDB4_componnents_blanks.to_excel(writer, index=False, sheet_name='EDB4_componnents_blanks')
Pivot_EDB4_components_type.to_excel(writer, index=True, sheet_name='Pivot_EDB4_components_type')
Pivot_EDB4_Customer_Defect.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Customer_Defect)
Pivot_EDB4_Monthly_Defect.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Customer_Defect, startcol=0, startrow=(Pivot_EDB4_Customer_Defect_nrows + 4))
Pivot_Concerned_Customer_Defect_List.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Concerned_Customer_Defect_List)
Pivot_EDB4_Project_Defect_Personal.to_excel(writer, index=False,sheet_name=outsheet_EDB4_Project_Defect_Personal)
Pivot_EDB4_Project_Defect_Monthly.to_excel(writer,index=False,sheet_name=outsheet_EDB4_Project_Defect_Monthly)
Pivot_EDB4_Project_Defect_Project_Total.to_excel(writer, index=False, sheet_name=outsheet_EDB4_Project_Defect_Monthly, startcol=(a+1), startrow=0)
Pivot_EDB4_Product_Type_Defect_Monthly.to_excel(writer,index=False,sheet_name=outsheet_EDB4_ProductType_Defect_Monthly)
writer.save()
print('Complete data processing, done!')
endtime = datetime.datetime.now()
print ((endtime - starttime).seconds)
扫一扫
01-29
2万+
2万+
03-16
235
235
05-18
2405
2405
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
