🚩 Some of the graphs in this notebook are interactive, don't forget to click around :) 📢¶

 

Hector Ramirez
ramirez.gomez.hector@gmail.com


Libraries¶

In [1]:
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import plotly.offline as py_offline
import matplotlib.pyplot as plt
import scipy.stats as stats

Task A¶

Read & Explore Data¶

In [2]:
df_a = pd.read_excel('Take Home Technical Screen.xlsx', sheet_name='Task A') # Read Task A data
df_a.head()
Out[2]:
Product Month ID (YYYYMM) Defects Opportunities Defect Rates Mean Rate 2 Sigma Limit 3 Sigma Limit
0 Product A 201607.0 37.0 222.000000 166666.666667 81319.08913 188681.909296 242363.319379
1 Product A 201608.0 26.0 36.000000 722222.222222 81319.08913 188681.909296 242363.319379
2 Product A 201609.0 66.0 375.000000 176000.000000 81319.08913 188681.909296 242363.319379
3 Product A 201610.0 61.0 494.000000 123481.781377 81319.08913 188681.909296 242363.319379
4 Product A 201611.0 64.0 980.333333 65283.917035 81319.08913 188681.909296 242363.319379
In [3]:
print(df_a.shape)
df_a.info() 
(111, 8)
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 111 entries, 0 to 110
Data columns (total 8 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   Product            28 non-null     object 
 1   Month ID (YYYYMM)  24 non-null     float64
 2   Defects            24 non-null     float64
 3   Opportunities      24 non-null     float64
 4   Defect Rates       24 non-null     float64
 5   Mean Rate          24 non-null     float64
 6   2 Sigma Limit      24 non-null     float64
 7   3 Sigma Limit      24 non-null     float64
dtypes: float64(7), object(1)
memory usage: 7.1+ KB
In [4]:
df_a.head(30) # Display first 30 rows of the data
Out[4]:
Product Month ID (YYYYMM) Defects Opportunities Defect Rates Mean Rate 2 Sigma Limit 3 Sigma Limit
0 Product A 201607.0 37.0 222.000000 166666.666667 81319.08913 188681.909296 242363.319379
1 Product A 201608.0 26.0 36.000000 722222.222222 81319.08913 188681.909296 242363.319379
2 Product A 201609.0 66.0 375.000000 176000.000000 81319.08913 188681.909296 242363.319379
3 Product A 201610.0 61.0 494.000000 123481.781377 81319.08913 188681.909296 242363.319379
4 Product A 201611.0 64.0 980.333333 65283.917035 81319.08913 188681.909296 242363.319379
5 Product A 201612.0 104.0 1297.666667 80143.847932 81319.08913 188681.909296 242363.319379
6 Product A 201701.0 86.0 2292.000000 37521.815009 81319.08913 188681.909296 242363.319379
7 Product A 201702.0 98.0 2444.666667 40087.264794 81319.08913 188681.909296 242363.319379
8 Product A 201703.0 130.0 2460.333333 52838.368785 81319.08913 188681.909296 242363.319379
9 Product A 201704.0 107.0 1888.666667 56653.723968 81319.08913 188681.909296 242363.319379
10 Product A 201705.0 135.0 1939.666667 69599.587558 81319.08913 188681.909296 242363.319379
11 Product A 201706.0 158.0 2173.000000 72710.538426 81319.08913 188681.909296 242363.319379
12 Product A 201707.0 116.0 2268.000000 51146.384480 81319.08913 188681.909296 242363.319379
13 Product A 201708.0 226.0 2188.000000 103290.676417 81319.08913 188681.909296 242363.319379
14 Product A 201709.0 171.0 2299.333333 74369.382430 81319.08913 188681.909296 242363.319379
15 Product A 201710.0 124.0 2199.000000 56389.267849 81319.08913 188681.909296 242363.319379
16 Product A 201711.0 119.0 2543.000000 46795.123869 81319.08913 188681.909296 242363.319379
17 Product A 201712.0 146.0 2779.666667 52524.283487 81319.08913 188681.909296 242363.319379
18 Product A 201801.0 134.0 2133.000000 62822.315987 81319.08913 188681.909296 242363.319379
19 Product A 201802.0 152.0 2167.333333 70132.266995 81319.08913 188681.909296 242363.319379
20 Product A 201803.0 170.0 2143.666667 79303.374281 81319.08913 188681.909296 242363.319379
21 Product A 201804.0 126.0 2841.333333 44345.377757 81319.08913 188681.909296 242363.319379
22 Product A 201805.0 134.0 3515.666667 38115.103821 81319.08913 188681.909296 242363.319379
23 Product A 201806.0 243.0 3214.666667 75591.041062 81319.08913 188681.909296 242363.319379
24 NaN NaN NaN NaN NaN NaN NaN NaN
25 NaN NaN NaN NaN NaN NaN NaN NaN
26 Chart 1 NaN NaN NaN NaN NaN NaN NaN
27 NaN NaN NaN NaN NaN NaN NaN NaN
28 NaN NaN NaN NaN NaN NaN NaN NaN
29 NaN NaN NaN NaN NaN NaN NaN NaN
In [5]:
df_a = df_a.iloc[:24] # Drop the rows with missing values
In [6]:
df_a.nunique() # Check for unique values
Out[6]:
Product               1
Month ID (YYYYMM)    24
Defects              23
Opportunities        24
Defect Rates         24
Mean Rate             1
2 Sigma Limit         1
3 Sigma Limit         1
dtype: int64
In [7]:
print(df_a.isnull().sum()) # There's no missing values
Product              0
Month ID (YYYYMM)    0
Defects              0
Opportunities        0
Defect Rates         0
Mean Rate            0
2 Sigma Limit        0
3 Sigma Limit        0
dtype: int64
In [8]:
df_a.head()
Out[8]:
Product Month ID (YYYYMM) Defects Opportunities Defect Rates Mean Rate 2 Sigma Limit 3 Sigma Limit
0 Product A 201607.0 37.0 222.000000 166666.666667 81319.08913 188681.909296 242363.319379
1 Product A 201608.0 26.0 36.000000 722222.222222 81319.08913 188681.909296 242363.319379
2 Product A 201609.0 66.0 375.000000 176000.000000 81319.08913 188681.909296 242363.319379
3 Product A 201610.0 61.0 494.000000 123481.781377 81319.08913 188681.909296 242363.319379
4 Product A 201611.0 64.0 980.333333 65283.917035 81319.08913 188681.909296 242363.319379
In [9]:
df_a['Month ID (YYYYMM)'] = df_a['Month ID (YYYYMM)'].astype(int).astype(str) # Convert the Month ID to string
df_a['Date'] = pd.to_datetime(df_a['Month ID (YYYYMM)'], format='%Y%m') # Convert the Month ID to datetime
In [10]:
df_a.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 24 entries, 0 to 23
Data columns (total 9 columns):
 #   Column             Non-Null Count  Dtype         
---  ------             --------------  -----         
 0   Product            24 non-null     object        
 1   Month ID (YYYYMM)  24 non-null     object        
 2   Defects            24 non-null     float64       
 3   Opportunities      24 non-null     float64       
 4   Defect Rates       24 non-null     float64       
 5   Mean Rate          24 non-null     float64       
 6   2 Sigma Limit      24 non-null     float64       
 7   3 Sigma Limit      24 non-null     float64       
 8   Date               24 non-null     datetime64[ns]
dtypes: datetime64[ns](1), float64(6), object(2)
memory usage: 1.8+ KB

1. Create Chart #1 below the data set that shows the number of Defects over time.¶

In [11]:
py_offline.init_notebook_mode(connected=True) # for offline plot
fig = px.scatter(df_a, x="Date", y="Defects", title="Defects (historical)")
fig.update_traces(marker=dict(size=10))  
fig.add_traces(px.line(df_a, x="Date", y="Defects").data)
fig.update_traces(line=dict(width=4), selector=dict(type='scatter', mode='lines'))  
fig.update_layout(title=dict(x=0.5, font=dict(size=24))) 
py_offline.iplot(fig)

2. Create Chart #2 below the data set that shows the number of Opportunities over time.¶

In [12]:
fig = px.scatter(df_a, x="Date", y="Opportunities", title="Opportunities (historical)")
fig.update_traces(marker=dict(size=10))  
fig.add_traces(px.line(df_a, x="Date", y="Opportunities").data)
fig.update_traces(line=dict(width=4), selector=dict(type='scatter', mode='lines'))  
fig.update_layout(title=dict(x=0.5, font=dict(size=24))) 
py_offline.iplot(fig)# fig.show()

3. Create Chart #3 below the data set that shows the Defect Rates with the Mean Rate and Limits over time.¶

In [13]:
fig = go.Figure()

# 2 Sigma Limit
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['2 Sigma Limit'],
                         mode='none',
                         name='2 Sigma Limit',
                         fill = 'tozeroy'))

# 3 Sigma Limit
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['3 Sigma Limit'],
                         mode='none',
                         name='3 Sigma Limit',
                         fill='tozeroy'))

# Defect Rates
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['Defect Rates'],
                         mode='lines+markers',
                         name='Defect Rates',
                         line=dict(width=3),  
                         marker=dict(size=10)
                         )) 

# Mean Rate
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['Mean Rate'],
                         mode='lines',
                         name='Mean Rate',
                         line=dict(color='red', width=3)))


# Update layout
fig.update_layout(title='Defect Rates [Defects/Opportunities] (historical)',
                  xaxis_title='Date',
                  yaxis_title='Rate',
                  title_x=0.5,  
                  title_font=dict(size=22)) 
fig.update_layout(title=dict(x=0.5, font=dict(size=24))) 

py_offline.iplot(fig)

4. Create Chart #4 below the data set that shows all the information from Chart #1, Chart #2, and Chart #3. Chart #4 must be presentable to key stakeholders with different levels of mastery on the data (ranging from individual contributors to senior management and executives). Be mindful of formatting, clarify, and aesthetics.¶

In [14]:
# Create plot with 2 rows and 1 column
fig = make_subplots(rows=2, cols=1, subplot_titles=("Defects & Opportunities (historical)", "Defect Rates (historical)"))

# Graph A: Defects
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['Defects'], mode='lines+markers', name='Defects', line=dict(width=4, color='#EF553B'), marker=dict(size=10), legendgroup='Group1'),
              row=1, col=1)

# Graph B: Opportunities
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['Opportunities'], mode='lines+markers', name='Opportunities', line=dict(width=4, color='#00CC96'), marker=dict(size=10), legendgroup='Group2'),
              row=1, col=1)

# Graph C: Defect Rates, Mean Rate, and Sigma Limits
# 2 Sigma Limit
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['2 Sigma Limit'], mode='none', name='2 Sigma (95%)', fill='tonexty', fillcolor='rgba(255, 253, 150, 0.3)', legendgroup='Group5'), 
              row=2, col=1)
# 3 Sigma Limit
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['3 Sigma Limit'], mode='none', name='3 Sigma (99%)', fill='tonexty', fillcolor='rgba(255, 155, 0, 0.3)', legendgroup='Group6'), 
              row=2, col=1)
# Defect Rates
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['Defect Rates'], mode='lines+markers', name='Defect Rates', line=dict(width=4, color = '#636EFA'), marker=dict(size=10), legendgroup='Group3'), 
              row=2, col=1)
# Mean Rate
fig.add_trace(go.Scatter(x=df_a['Date'], y=df_a['Mean Rate'], mode='lines', name='Mean Rate', line=dict(color='red', width=4, dash='dash'), legendgroup='Group4'), 
              row=2, col=1)

# Update layout
fig.update_layout(height=800, showlegend=True, legend=dict(y=0.5))
fig.update_yaxes(title_text="Units", row=1, col=1)
fig.update_yaxes(title_text="Rate units", row=2, col=1)
py_offline.iplot(fig)

5. Analyze Chart #4 and annotate at least three observations / findings specific to the data provided.¶

  • Both Defects and Opportunities have increased in time.
  • There are more Opportunities than Defects in all times. Nevertheless, July, August and September 2016, the difference between them is lower.
  • Defect Rates is defined as Defects/Opportunities (multiplied by 10^6). If Defects trace is divided by the Opportunities trace then the Defect Rates trace is obtained.
  • It seems that the Defect Rate from August 2016 could be a potential outlier, indicating a possible anomaly in the process.
  • August 2016 Defect Rate value is real high due to Defects and Opportunities are very close. It seems that the higher Opportunities compared to Defects, the lower the Defect Rate is, and viceversa.
  • Besides August 2016 Defect Rate value, all of the historical values fall within 2 Sigma, i.e. are inside the 95% of the values.
  • We are using parametrical statistics (Mean, and Sigmas) to analyze a non-normal distribution of Defect Rates (check the Addendum - Normality test at the end of this notebook), it would be recommended to use the non-parametrical statistics (Median and Interquartil range). If we had access to the original data, we could use boxplots instead.

 

 

 

Task B¶

Read & Explore Data¶

In [15]:
df_b = pd.read_excel('Take Home Technical Screen.xlsx', sheet_name='Task B') # read Task B data
df_b.head()
Out[15]:
Complaint.Number Date Product Quantity Defect Account.Number
0 CN-000132 2020-01-01 Prod-05 9 HW-ERROR-61 Account-407
1 CN-000133 2020-01-01 Prod-00 6 HW-ERROR-75 Account-433
2 CN-000134 2020-01-01 Prod-03 11 HW-ERROR-67 Account-412
3 CN-000135 2020-01-01 Prod-09 4 HW-ERROR-74 Account-459
4 CN-000136 2020-01-01 Prod-04 6 CS-ERROR-52 Account-416
In [16]:
print(df_b.shape)
df_b.info() 
(679, 6)
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 679 entries, 0 to 678
Data columns (total 6 columns):
 #   Column            Non-Null Count  Dtype         
---  ------            --------------  -----         
 0   Complaint.Number  679 non-null    object        
 1   Date              679 non-null    datetime64[ns]
 2   Product           679 non-null    object        
 3   Quantity          679 non-null    int64         
 4   Defect            679 non-null    object        
 5   Account.Number    679 non-null    object        
dtypes: datetime64[ns](1), int64(1), object(4)
memory usage: 32.0+ KB
In [17]:
df_b.nunique() # Check for unique values
Out[17]:
Complaint.Number    679
Date                387
Product              11
Quantity             11
Defect               51
Account.Number       76
dtype: int64
In [18]:
print(df_b.isnull().sum()) # There's no missing values
Complaint.Number    0
Date                0
Product             0
Quantity            0
Defect              0
Account.Number      0
dtype: int64
In [19]:
countries = pd.read_excel('Take Home Technical Screen.xlsx', sheet_name='Task B Country List') # read country list
countries.head()
Out[19]:
Account.Number Country
0 Account-407 Taiwan
1 Account-433 Italy
2 Account-412 Vietnam
3 Account-459 Italy
4 Account-416 Canada
In [20]:
print(countries.shape)
countries.info() 
(76, 2)
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 76 entries, 0 to 75
Data columns (total 2 columns):
 #   Column          Non-Null Count  Dtype 
---  ------          --------------  ----- 
 0   Account.Number  76 non-null     object
 1   Country         76 non-null     object
dtypes: object(2)
memory usage: 1.3+ KB
In [21]:
print(countries.isnull().sum()) # There's no missing values
Account.Number    0
Country           0
dtype: int64

1. Add Country to "Task B" sheet using mapping table provided in "Task B Country List" sheet.¶

In [22]:
df_b = df_b.merge(countries, on='Account.Number', how='left') # Merge the data with the country list
df_b.head()
Out[22]:
Complaint.Number Date Product Quantity Defect Account.Number Country
0 CN-000132 2020-01-01 Prod-05 9 HW-ERROR-61 Account-407 Taiwan
1 CN-000133 2020-01-01 Prod-00 6 HW-ERROR-75 Account-433 Italy
2 CN-000134 2020-01-01 Prod-03 11 HW-ERROR-67 Account-412 Vietnam
3 CN-000135 2020-01-01 Prod-09 4 HW-ERROR-74 Account-459 Italy
4 CN-000136 2020-01-01 Prod-04 6 CS-ERROR-52 Account-416 Canada

2. Summarize "Task B" sheet reported volumes for each product; calculate percent difference between product volumes in 2020 and 2021.¶

In [23]:
df_b['Year'] = df_b['Date'].dt.year # Extract the year from the date
In [24]:
df_b.head()
Out[24]:
Complaint.Number Date Product Quantity Defect Account.Number Country Year
0 CN-000132 2020-01-01 Prod-05 9 HW-ERROR-61 Account-407 Taiwan 2020
1 CN-000133 2020-01-01 Prod-00 6 HW-ERROR-75 Account-433 Italy 2020
2 CN-000134 2020-01-01 Prod-03 11 HW-ERROR-67 Account-412 Vietnam 2020
3 CN-000135 2020-01-01 Prod-09 4 HW-ERROR-74 Account-459 Italy 2020
4 CN-000136 2020-01-01 Prod-04 6 CS-ERROR-52 Account-416 Canada 2020
In [25]:
# Segment year 2020 and 2021
df_2020 = df_b[df_b['Year'] == 2020]
df_2021 = df_b[df_b['Year'] == 2021]

# Compute product volumes for 2020 and 2021
volume_2020 = df_2020.groupby('Product')['Quantity'].sum().reset_index()
volume_2021 = df_2021.groupby('Product')['Quantity'].sum().reset_index()

# Merge 2020 and 2021 product volumes
merged_data = pd.merge(volume_2020, volume_2021, on='Product', suffixes=('_2020', '_2021'))

# Compute product volume for both years
merged_data['Quantity_2020+2021'] = merged_data['Quantity_2020'] + merged_data['Quantity_2021'] 

# Compute the percent difference between 2021 and 2020
merged_data['Percent_Difference'] = ((merged_data['Quantity_2021'] - merged_data['Quantity_2020']) / merged_data['Quantity_2020']) * 100
In [26]:
merged_data
Out[26]:
Product Quantity_2020 Quantity_2021 Quantity_2020+2021 Percent_Difference
0 Prod-00 140 75 215 -46.428571
1 Prod-01 208 214 422 2.884615
2 Prod-02 192 199 391 3.645833
3 Prod-03 282 196 478 -30.496454
4 Prod-04 189 160 349 -15.343915
5 Prod-05 261 179 440 -31.417625
6 Prod-06 284 225 509 -20.774648
7 Prod-07 193 130 323 -32.642487
8 Prod-08 169 198 367 17.159763
9 Prod-09 323 125 448 -61.300310
10 Prod-10 125 99 224 -20.800000
In [27]:
merged_data['Legend_Labels'] = merged_data['Product'] + " (" + merged_data['Quantity_2020+2021'].astype(str) + ")"
fig = px.pie(merged_data, values='Quantity_2020+2021', names='Legend_Labels', title='Volume by Product (2020-2021)')
fig.update_layout(title=dict(x=0.5, font=dict(size=24)), width=500, height=500) 
py_offline.iplot(fig)
In [28]:
fig = px.bar(merged_data, x='Product', y='Percent_Difference',
             title='Percentage difference in reported volume between 2020-2021',
             text='Percent_Difference', color='Product')

fig.update_traces(texttemplate='%{text:.2f}%', textposition='outside')
fig.update_layout(uniformtext_minsize=8, uniformtext_mode='hide',
                  yaxis=dict(title='Percentage difference (%)'),
                  xaxis=dict(title='Product'))

fig.update_layout(title=dict(x=0.5, font=dict(size=24))) # Center the title
fig.update_yaxes(range=[-75, 75]) # Set the y-axis range
fig.add_hline(y=0, line_dash="dash", line_color="black") # Add a horizontal line at 0
py_offline.iplot(fig)

3. Identify the top 3 countries with highest complaint volume in year 2020.¶

In [29]:
df_2020 = df_b[df_b['Year'] == 2020] # Filter the data for 2020
In [30]:
df_2020.groupby('Country').size().sort_values(ascending=False).head(3) # Mexico, Taiwan, and China are the top 3 countries with highest complaint volume in 2020
Out[30]:
Country
Mexico    64
Taiwan    50
Italy     45
dtype: int64

4. Defects are tagged as either HW, SW, or CS errors. Summarize complaint volumes for each of these categories.¶

In [31]:
df_b['Defect_Category']  = df_b['Defect'].str[:2] # Extract the first two characters of the defect code
In [32]:
defect_counts = df_b['Defect_Category'].value_counts()
print(defect_counts)
Defect_Category
SW    300
HW    257
CS    122
Name: count, dtype: int64
In [33]:
labels_with_counts = [f'{index} ({value})' for index, value in defect_counts.items()]
fig = px.pie(defect_counts, values='count', names=labels_with_counts, title='Defect Categories (2020-2021)')
fig.update_layout(title=dict(x=0.5, font=dict(size=24)), width=500, height=500) 
py_offline.iplot(fig)

 

 

 

Addendum - Normality test¶

In [34]:
df_a['Defect Rates'].describe() # Mean and Median (50%) are not so close, so the data is possibly not-normally distributed
Out[34]:
count        24.000000
mean     100751.430509
std      137216.102210
min       37521.815009
25%       52179.808735
50%       67441.752297
75%       79513.492694
max      722222.222222
Name: Defect Rates, dtype: float64
In [35]:
plt.hist(df_a['Defect Rates'], bins='auto', alpha=0.7, rwidth=0.85, color='blue')
plt.title('Histogram of Defect Rates')
plt.show()
No description has been provided for this image
In [36]:
print(max(df_a['Defect Rates'])) # Possible outlier
722222.2222222222
In [37]:
defect_rates = df_a['Defect Rates'][df_a['Defect Rates'] != max(df_a['Defect Rates'])] # Remove the possible outlier
In [38]:
# Histogram without the outlier doesn't seems to be normally distributed
plt.hist(defect_rates, bins='auto', alpha=0.7, rwidth=0.85, color='blue')
plt.title('Histogram of Defect Rates')
plt.show()
No description has been provided for this image
In [39]:
# Q-Q plot without the outlier indicates that the data is not normally distributed
stats.probplot(defect_rates, dist="norm", plot=plt)
plt.title('Gráfico Q-Q')
plt.show()
No description has been provided for this image
In [40]:
# Shapiro-Wilk statistics indicates could be a normal distribution
# Nevertheless, the p-value is less than 0.05, so we reject the null hypothesis that the data is normally distributed, i.e. it's not normally distributed
shapiro_test = stats.shapiro(defect_rates)
print(f"Shapiro-Wilk test statistic: {shapiro_test[0]}, p-value: {shapiro_test[1]}") 
Shapiro-Wilk test statistic: 0.7896416187286377, p-value: 0.0002627919602673501