Full Lean Six Sigma Case Solution In Python¶
Clear Calls Case¶
- Review content provided in PDF to understand the case study background.
- This is an end to end case study. Some parts of the case study will be solved in XL and PPT. Statistical concepts are solved in Python below.
- Refer to case Questions to understand solutions
Copyright 2012 @ Canopus Business Management Group. All Rights Reserved This case study is created by Canopus Business Management Group for indiviual use and learning purpose. Not be reproduced for any other purpose. Refer to www.collaborat.com for terms of use
Author : Nilakantasrinivasan (Neil) neil@collaborat.com
In [66]:
# Importing basic packages
import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
sns.set(color_codes =True)
%matplotlib inline
In [67]:
# Importing the data file for the Clear Calls Case Study
CS=pd.read_excel("Case Study Data XY for python GB.xls", index_col=0)
In [68]:
# Descriptive Statistics of the data
CS.describe().transpose()
Out[68]:
Data Clean up and Preparation for Analysis¶
In [69]:
# Checking for null values
CS.isnull().values.any()
Out[69]:
In [70]:
# Identifying rows with NAN values
CS[pd.isnull(CS).any(axis=1)]
Out[70]:
In [71]:
# Dropping rows with NAN Values
CS=CS.dropna(axis=0)
In [7]:
CS.describe()
Out[7]:
In [72]:
# Locating rows which have zero values
CS.loc[(CS==0).any(axis=1)]
Out[72]:
In [74]:
# Drop 7th row with zero values
CS.drop(index=7, axis=0)
Out[74]:
Identify what labels are contained in each column¶
In [10]:
CS['Type of Technology (Modem, routers, etc)'].unique()
Out[10]:
In [11]:
CS['Service Affiliate CODE'].unique()
Out[11]:
In [12]:
CS['Service Affiliate TEAMS'].unique()
Out[12]:
In [13]:
CS.loc[CS['Type of Technology (Modem, routers, etc)'] == 'd', 'Type of Technology (Modem, routers, etc)'] = 'D'
In [14]:
CS['Type of Technology (Modem, routers, etc)'].unique()
Out[14]:
In [15]:
CS.loc[CS['Service Affiliate CODE'] == 'a', 'Service Affiliate CODE'] = 'A'
In [16]:
CS['Service Affiliate CODE'].unique()
Out[16]:
In [17]:
CS.loc[CS['Service Affiliate TEAMS'] == 'a2', 'Service Affiliate TEAMS'] = 'A2'
In [18]:
CS['Service Affiliate TEAMS'].unique()
Out[18]:
In [77]:
# Group the dataframe into two based on Defects and Non Defects (For defectives here)
CSD=CS[CS['Defects / Non Defects']=='D']
CSD
Out[77]:
In [76]:
# Same as above ( for Non Defectives)
CSN=CS[CS['Defects / Non Defects']=='N']
CSN
Out[76]:
Graphical Summary of All variables¶
In [80]:
# Histogram Ploting for Installation data
plt.title("Histogram for Installation TAT in Hours")
sns.distplot(CS['Installation TAT in Hours'], kde=False)
Out[80]:
Histogram and Boxplot for all the Xs Variables, one after another¶
In [81]:
plt.figure(figsize=(20, 6))
plt.subplot(1,3,1)
sns.set_style("dark")
plt.title("Histogram for Commission to SA")
sns.distplot(CS['Commission to SA'],kde=False)
plt.subplot(1,3,2)
sns.distplot(CSN['Commission to SA'],kde=False,color="Blue", label="Commission for Non Defects")
sns.distplot(CSD['Commission to SA'],kde=False,color = "Gold", label = "Commission for Defects")
plt.title("Histograms for Commission to SA")
plt.legend()
plt.subplot(1,3,3)
sns.boxplot(x=CS['Defects / Non Defects'],y=CS['Commission to SA'])
plt.title("Segmented Boxplot for Commission")
Out[81]:
In [22]:
plt.figure(figsize=(20, 6))
plt.subplot(1,3,1)
sns.set_style("dark")
plt.title("Histogram for Items in Queue")
sns.distplot(CS['No. of installations requests in queue'],kde=False)
plt.subplot(1,3,2)
sns.distplot(CSN['No. of installations requests in queue'],kde=False,color="Blue", label="Items in Queue for Non Defects")
sns.distplot(CSD['No. of installations requests in queue'],kde=False,color = "Gold", label = "Items in Queue for Defects")
plt.title("Histograms for Items in Queue")
plt.legend()
plt.subplot(1,3,3)
sns.boxplot(x=CS['Defects / Non Defects'],y=CS['No. of installations requests in queue'])
plt.title("Segmented Boxplot for Items in Queue")
Out[22]:
In [23]:
plt.figure(figsize=(20, 6))
plt.subplot(1,3,1)
sns.set_style("dark")
plt.title("Histogram for Processing Time@ Back Office")
sns.distplot(CS['Prcessing Time@ Back Office'],kde=False)
plt.subplot(1,3,2)
sns.distplot(CSN['Prcessing Time@ Back Office'],kde=False,color="Blue", label="Prcessing Time@ Back Office for Non Defects")
sns.distplot(CSD['Prcessing Time@ Back Office'],kde=False,color = "Gold", label = "Prcessing Time@ Back Office for Defects")
plt.title("Histograms for Prcessing Time@ Back Office")
plt.legend()
plt.subplot(1,3,3)
sns.boxplot(x=CS['Defects / Non Defects'],y=CS['Prcessing Time@ Back Office'])
plt.title("Segmented Boxplot for Prcessing Time@ Back Office")
Out[23]:
In [24]:
plt.figure(figsize=(20, 6))
plt.subplot(1,3,1)
sns.set_style("dark")
plt.title("Histogram for Distance from the SA office")
sns.distplot(CS['Distance from the SA office'],kde=False)
plt.subplot(1,3,2)
sns.distplot(CSN['Distance from the SA office'],kde=False,color="Blue", label="Distance from the SA office for Non Defects")
sns.distplot(CSD['Distance from the SA office'],kde=False,color = "Gold", label = "Distance from the SA office for Defects")
plt.title("Histograms for Distance from the SA office")
plt.legend()
plt.subplot(1,3,3)
sns.boxplot(x=CS['Defects / Non Defects'],y=CS['Distance from the SA office'])
plt.title("Segmented Boxplot for Distance from the SA office")
Out[24]:
In [25]:
plt.figure(figsize=(20, 6))
plt.subplot(1,3,1)
sns.set_style("dark")
plt.title("Histogram for Distance travelled")
sns.distplot(CS['Distance travelled'],kde=False)
plt.subplot(1,3,2)
sns.distplot(CSN['Distance travelled'],kde=False,color="Blue", label="Distance travelled for Non Defects")
sns.distplot(CSD['Distance travelled'],kde=False,color = "Gold", label = "Distance travelled for Defects")
plt.title("Histograms for Distance travelled")
plt.legend()
plt.subplot(1,3,3)
sns.boxplot(x=CS['Defects / Non Defects'],y=CS['Distance travelled'])
plt.title("Segmented Boxplot for Distance travelled")
Out[25]:
In [82]:
# Ploting multiple boxplots and comparing the factors of two variables
plt.figure(figsize=(20, 6))
plt.subplot(1,2,1)
sns.boxplot(x=CS['Service Affiliate CODE'], y=CS['Installation TAT in Hours'])
plt.subplot(1,2,2)
sns.boxplot(x=CS['Service Affiliate TEAMS'], y=CS['Installation TAT in Hours'])
Out[82]:
In [83]:
# Numercial Descripion of Installation TAT
CS['Installation TAT in Hours'].describe()
Out[83]:
Normality Testing¶
In [28]:
from scipy import stats
stats.normaltest(CS['Installation TAT in Hours']).pvalue
Out[28]:
In [54]:
from statsmodels.graphics.gofplots import qqplot
from matplotlib import pyplot
In [55]:
qqplot(CS['Installation TAT in Hours'], line ='s')
pyplot.show()
Stem and Leaf Plot for Installation TAT¶
In [56]:
import stemgraphic
from stemgraphic import stem_graphic
In [61]:
stemgraphic.stem_graphic(CS['Installation TAT in Hours'], scale = 10)
Out[61]:
Superimposing Customer Spec of 30 hours over the Installation TAT Distribution¶
In [64]:
sns.distplot(CS['Installation TAT in Hours'],hist=False)
plt.axvline(x=30, color='r')
Out[64]:
Scatter Diagrams to screen variables visually¶
In [85]:
sns.pairplot(CS, vars=['Installation TAT in Hours','Commission to SA'])
Out[85]:
In [86]:
sns.pairplot(CS,vars=['Installation TAT in Hours','Commission to SA','No. of installations requests in queue','Prcessing Time@ Back Office','Distance from the SA office','Distance travelled','Installation TAT in Hours'])
plt.title("Pairplot of Variables by Outcome")
Out[86]:
Correlation Coefficient Computation¶
In [31]:
cor = CS.corr(method ='pearson')
cor
Out[31]:
In [32]:
sns.heatmap(cor)
Out[32]:
Multiple Linear Regression¶
In [87]:
# Run No.1
inputs=['No. of installations requests in queue','Distance travelled','Distance from the SA office']
X=CS[inputs]
In [34]:
import statsmodels.api as sm
X = sm.add_constant(X)
y= CS['Installation TAT in Hours']
model = sm.OLS(y, X).fit()
predictions = model.predict(X)
model.summary()
Out[34]:
In [88]:
# Run No.2
inputs1=['No. of installations requests in queue','Distance travelled']
X=CS[inputs1]
X = sm.add_constant(X)
y= CS['Installation TAT in Hours']
model = sm.OLS(y, X).fit()
predictions = model.predict(X)
model.summary()
Out[88]:
In [47]:
# Run No.3 (Without Y Intercept - 'C' in Y=mx+C will be not included in the model now)
inputs1=['No. of installations requests in queue','Distance travelled']
X=CS[inputs1]
y= CS['Installation TAT in Hours']
model = sm.OLS(y, X).fit()
predictions = model.predict(X)
model.summary()
Out[47]:
Hypothesis Testing¶
Each variable (discrete) will be checked whether it influences Installation TAT or not in the following tests
ANOVA using Statsmodels¶
Data has to be stacked
In [89]:
from statsmodels.formula.api import ols # For n-way ANOVA
from statsmodels.stats.anova import anova_lm # For n-way ANOVA
ANOVA for TAT impact by different technologies¶
Ho : Installation TAT for different technologies is same Ha : Installation TAT for different technologies is different - Type of Modem
In [39]:
plt.figure(figsize=(10, 6))
sns.boxplot(x=CS['Type of Technology (Modem, routers, etc)'], y=CS['Installation TAT in Hours'])
Out[39]:
In [40]:
y= CS['Installation TAT in Hours']
X=CS['Type of Technology (Modem, routers, etc)']
formula = 'y ~ X'
model = ols(formula, CS).fit()
aov_table = anova_lm(model)
print(aov_table)
ANOVA for TAT impact by different Service Affilitates¶
Ho : Installation TAT for different Service Affiliates is same Ha : Installation TAT for different Service Affiliates is different
In [41]:
plt.figure(figsize=(10, 6))
sns.boxplot(x=CS['Service Affiliate CODE'], y=CS['Installation TAT in Hours'])
Out[41]:
In [42]:
SA_CODE=CS['Service Affiliate CODE']
formula = 'y ~ SA_CODE'
model = ols(formula, CS).fit()
aov_table = anova_lm(model)
print(aov_table)
ANOVA for TAT impact by different Service Affilitates Team¶
- Ho : Installation TAT for different teams within Service Affiliate the same
- Ha : Installation TAT for different teams within Service Affiliate is different
In [43]:
plt.figure(figsize=(10, 6))
sns.boxplot(x=CS['Service Affiliate TEAMS'], y=CS['Installation TAT in Hours'])
Out[43]:
In [44]:
y= CS['Installation TAT in Hours']
X=CS['Service Affiliate TEAMS']
formula = 'y ~ X'
model = ols(formula, CS).fit()
aov_table = anova_lm(model)
print(aov_table)
Test of Homogenity of Variance (Pre-requisite for ANOVA)¶
This test is conducted for one variable Service Affiliate Code as an example here.
In [45]:
# Test of Homogenity of Variance
w, pvalue = stats.levene(CS['Installation TAT in Hours'][CS['Service Affiliate CODE'] == 'A'],
CS['Installation TAT in Hours'][CS['Service Affiliate CODE'] == 'B'],
CS['Installation TAT in Hours'][CS['Service Affiliate CODE'] == 'C'])
print(w, pvalue)
2t test¶
Pre and Post data comparison after improvement to validate if statistical different
In [103]:
Prepost= pd.read_excel('F:/2019 GB Python/ClearCalls Pre-Post Data for Py .xlsx')
Prepost.head()
Out[103]:
In [104]:
sns.boxplot(x='variable', y='value', data = pd.melt(Prepost), width =0.3)
Out[104]:
In [105]:
from scipy import stats
from scipy.stats import ttest_1samp, ttest_ind,mannwhitneyu,levene,shapiro,wilcoxon
In [106]:
t_statistic, p_value = ttest_ind(Prepost['Pre_TAT'], Prepost['Post_TAT'])
print(t_statistic, p_value)