Test Execution History Analysis



In [ ]:

    
%matplotlib inline
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
import seaborn as sns



In [ ]:

    
# fetch data from database table
from sqlalchemy import create_engine

engine = create_engine('mysql://root:btstest@10.140.23.96/qtreport', echo=False)
sql = '''SELECT t.name AS team_name,
           c.qc_instance_id,
           r.execution_time,
           r.testCase_id,
           r.elapse_time,
           r.testBed_id,
           r.build_id,
           r.result 
           FROM rtreport_testrecord AS r 
             LEFT JOIN rtreport_testteam AS t ON r.team_id=t.id 
             LEFT JOIN rtreport_testcase AS c ON r.testCase_id=c.id;'''
data = pd.read_sql_query(sql, engine)



In [ ]:

    
# analyze by day
one_year_data = data[(data.execution_time > '2016/03/31') & (
        data.execution_time < '2017/04/01')]
# remove CI and QT data
# one_year_data = one_year_data[(one_year_data.team_name != 'CI') & (one_year_data.team_name != 'QT')]

Execution Status Summary



In [ ]:

    
day_data = one_year_data.groupby(pd.TimeGrouper(key='execution_time', freq='1d'))
week_data = one_year_data.groupby(pd.TimeGrouper(key='execution_time', freq='1w'))
month_data = one_year_data.groupby(pd.TimeGrouper(key='execution_time', freq='1m'))

How many executions we have during 2016/04/01 to 2017/04/01?



In [ ]:

    
exec_count = len(one_year_data)
exec_count

How many non-QT executions we have during 2016/04/01 to 2017/04/01?



In [ ]:

    
no_qt_exec_count = len(one_year_data[(one_year_data.team_name != 'CI') & (one_year_data.team_name != 'QT')])
no_qt_exec_count



In [ ]:

    
from pandas import Series
Series([no_qt_exec_count, exec_count-no_qt_exec_count], 
       index=['CRT', 'QT'], name='CRT vs QT').plot(kind='pie', figsize=(8,8))

How many unique cases we have during 2016/04/01 to 2017/04/01?



In [ ]:

    
# using testCase_id
case_count = len(one_year_data.testCase_id.unique())
case_count



In [ ]:

    
# using qc_instance_id
qc_count = len(one_year_data.qc_instance_id.unique())
qc_count

Average execution counts per case



In [ ]:

    
# using testCase_id
float(exec_count) / case_count



In [ ]:

    
# using qc_instance_id
float(exec_count) / qc_count

Less than 1 execution per week

Executions count per day



In [ ]:

    
# execution records per day
day_data.size().plot(kind='area', figsize=(16, 8))

Executions count per week



In [ ]:

    
week_data.size().plot(kind='bar', figsize=(16, 8))

Executions count per month



In [ ]:

    
month_data.size().plot(kind='bar', figsize=(16, 8))



In [ ]:

    
exec_by_day = one_year_data.pivot_table('testCase_id', 
    index=pd.TimeGrouper(key='execution_time', freq='1d'), columns='result', aggfunc='count')



In [ ]:

    
exec_by_day.plot(
    kind='area', stacked=True, color='gr', alpha=0.6, figsize=(16,8)
).legend(['PASS', 'FAILED'])

Stacked execution result per day of team



In [ ]:

    
one_year_data.team_name.unique()



In [ ]:

    
team_name = 'FVHZ_OAM3'
team_exec_data_by_result = one_year_data[one_year_data.team_name == team_name].pivot_table('testCase_id', 
    index=pd.TimeGrouper(key='execution_time', freq='1d'), columns='result', aggfunc='count')
team_exec_data_by_result.plot(title='Execution Result of '+team_name,
    kind='area', stacked=True, color='gr', alpha=0.6, figsize=(16,8)
).legend(['PASS', 'FAILED'])

Unique executed cases per day - by testCase_id



In [ ]:

    
day_data.testCase_id.nunique().plot(kind='area', figsize=(16,8))

In most time around 600 cases executed daily



In [ ]:

    
day_data.testCase_id.nunique().hist(figsize=(16,8))

Unique executed cases per day - by qc_instance_id



In [ ]:

    
day_data.qc_instance_id.nunique().plot(kind='area', figsize=(16,8))

Unique executed cases per week - by testCase_id



In [ ]:

    
week_data.testCase_id.nunique().plot(kind='bar', figsize=(16,8))

Unique executed cases per week - by qc_instance_id



In [ ]:

    
week_data.qc_instance_id.nunique().plot(kind='bar', figsize=(16,8))

Unique executed cases per month - by testCase_id



In [ ]:

    
month_data.testCase_id.nunique().plot(kind='bar', figsize=(16,8))

Unique executed cases per month - by qc_instance_id



In [ ]:

    
month_data.qc_instance_id.nunique().plot(kind='bar', figsize=(16,8))

From case views

by testCase_id
by qc_instance_id



In [ ]:

    
# total executions of each case
exec_by_case = one_year_data.groupby('testCase_id').size().sort_values()
exec_by_case[-20:].plot(kind='barh', figsize=(16, 8))



In [ ]:

    
# total executions of each case - by qc_instance_id
exec_by_qc = one_year_data.groupby('qc_instance_id').size().sort_values(ascending=False)
exec_by_qc



In [ ]:

    
# case execution point view - by testCase_id
one_year_data.plot(
    style='.', x='execution_time', y='testCase_id', figsize=(16, 12))



In [ ]:

    
one_year_data[one_year_data.qc_instance_id>0].plot(
    style='.', x='execution_time', y='qc_instance_id', figsize=(16, 12))



In [ ]:

    
# from case executions point of view, execution counts / case counts
exec_case_counts = one_year_data.groupby('testCase_id').size().value_counts()



In [ ]:

    
graph = exec_case_counts.sort_index().plot(
    grid=False, figsize=(16, 6), logy=True, logx=True)
graph.set_xlabel('Execution Count')
graph.set_ylabel('Case Count')



In [ ]:

    
# top 50 execution counts / case counts
exec_case_counts[:50].sort_index().plot(kind='bar', figsize=(16, 6))



In [ ]:

    
exec_qc_counts = one_year_data.groupby('qc_instance_id').size().value_counts()



In [ ]:

    
graph = exec_qc_counts.sort_index().plot(
    kind='line', figsize=(16, 6), logx=True, logy=True)
graph.set_xlabel('Execution Count')
graph.set_ylabel('QC Count')

How many test cases monthly executed?



In [ ]:

    
exec_case_counts[exec_case_counts.keys()>=12].sum()

How many cases have executions less than 48 (once per week)?



In [ ]:

    
no_weekly_case_counts = exec_case_counts[exec_case_counts.keys() < 48].sum()
no_weekly_qc_counts = exec_qc_counts[exec_qc_counts.keys() < 48].sum()



In [ ]:

    
no_weekly_case_counts*100 / float(case_count)



In [ ]:

    
no_weekly_qc_counts*100 / float(qc_count)

How many cases have executions less than 12 (once per month)?



In [ ]:

    
no_monthly_case_count = exec_case_counts[exec_case_counts.keys() < 12].sum()



In [ ]:

    
no_monthly_qc_count = exec_qc_counts[exec_qc_counts.keys() < 12].sum()



In [ ]:

    
no_monthly_case_count*100 / float(case_count)



In [ ]:

    
no_monthly_qc_count*100 / float(qc_count)

Team Views

Execution counts by team



In [ ]:

    
team_data = one_year_data.groupby('team_name')
team_data.size().sort_values(ascending=False).plot(
    kind='bar', figsize=(16, 6))

Execution per day - by team



In [ ]:

    
team_day_data = one_year_data.pivot_table('testCase_id', 
    index=pd.TimeGrouper(key='execution_time', freq='1d'), columns='team_name', aggfunc='count')



In [ ]:

    
team_day_data.plot(subplots=True, kind='area', grid=False, figsize=(16, 84))

Monthly executions by team



In [ ]:

    
df = one_year_data.groupby(['team_name', pd.TimeGrouper(key='execution_time', freq='1m')]).size().unstack()



In [ ]:

    
plt.figure(figsize=(12, 12))
sns.heatmap(df, robust=True, fmt='d')

From elapse time views



In [ ]:

    
elapse_hour_per_day = day_data['elapse_time'].sum()/3600.0
graph = elapse_hour_per_day.plot(kind='area', figsize=(16,6), stacked=False)
graph.set_xlabel('Time Series')
graph.set_ylabel('Elapse Time Daily(hour)')

Mean elapse hour everyday:



In [ ]:

    
elapse_hour_per_day.mean()

TestBed usage everyday



In [ ]:

    
testbed_per_day = day_data['testBed_id'].nunique()
testbed_per_day.plot(kind='area', figsize=(16,6))

Mean testbed usage everyday:



In [ ]:

    
testbed_per_day.mean()

Mean elapse hour per testbed everyday:



In [ ]:

    
testbed_usage = one_year_data.pivot_table('elapse_time',
    index=pd.TimeGrouper(key='execution_time', freq='1d'), columns='testBed_id', aggfunc='sum').mean(1)/3600
testbed_usage.plot(kind='area', figsize=(16, 8), stacked=False)

Every testbed will be used around (hours):



In [ ]:

    
testbed_usage.mean()

From case stability views

Only for the case that have >=12 executions



In [ ]:

    
over_12_data = one_year_data.groupby('testCase_id').filter(lambda x: len(x) >= 12)



In [ ]:

    
over_12_data.groupby('testCase_id').result.std().plot(
    style='.', figsize=(16, 8))

From stability of build views



In [ ]:

    
one_year_data.groupby('build_id').testCase_id.size().plot(
    style='o', figsize=(16, 6))

From elapse time by case views

elapse time of each case
top elapse time cases

Mean elapse time of all executions



In [ ]:

    
one_year_data[one_year_data.elapse_time >= 0].elapse_time.mean() # s

Mean elapse time of all testcases



In [ ]:

    
elapse_time_by_case = one_year_data[one_year_data.elapse_time >= 0].groupby('testCase_id').elapse_time.mean()
elapse_time_by_case.mean()



In [ ]:

    
elapse_time_by_case.sort_values(ascending=False)

Case longer than 1hour



In [ ]:

    
elapse_time_by_case[elapse_time_by_case>3600].size*100.0/case_count

Case longer than 30min



In [ ]:

    
elapse_time_by_case[elapse_time_by_case>1800].size*100.0/case_count

Case longer than 10min



In [ ]:

    
elapse_time_by_case[elapse_time_by_case>600].size*100.0/case_count

Most case last 5-25 minutes



In [ ]:

    
graph = (elapse_time_by_case/60).astype(int) \
    .value_counts().sort_index().plot(
        grid=False, figsize=(16,6))
graph.set_xlabel('time (min)')
graph.set_ylabel('case count')