Week 2: Data Collection & Cleaning

Transforming Raw Data into Reliable Insights"

Data Source Kaggel.com

1. Data Cleaning Process

In this assignment, we were required to remove duplicate records, handle missing values, and treat outliers in the dataset. These steps ensure that the data is accurate, consistent, and reliable for further analysis. After applying the cleaning techniques to our project dataset, we generated a “Before vs After Cleaning Report” which highlights the differences in data quality by showing the dataset’s shape, missing values, and sample rows before cleaning compared to the cleaned dataset.

Dataset Overview

The dataset has been successfully loaded for analysis. It contains 85,907 rows and 20 columns. The dataset comprises customer service interactions, including details about the agent, customer remarks, issue reported, response time, CSAT score, and other transactional information.

2. Initial Dataset Inspection

Shape Before Cleaning

Rows: 85,907
Columns: 20

3. Missing Values Before Cleaning

Column	Missing Values
Unique id	0
channel_name	0
category	0
Sub-category	0
Customer Remarks	57,165
Order_id	18,232
order_date_time	68,693
Issue_reported at	0
issue_responded	0
Survey_response_Date	0
Customer_City	68,828
Product_category	68,711
Item_price	68,701
connected_handling_time	85,665
Agent_name	0
Supervisor	0
Manager	0
Tenure Bucket	0
Agent Shift	0
CSAT Score	0

Observation: Several columns contain significant missing values, especially Customer Remarks, order_date_time, Customer_City, and connected_handling_time.

4. Missing Value Handling Strategy

The missing values were handled using Mean / Median / Mode imputation based on the data type and distribution.

Column	Mean	Median	Mode	Used Strategy
Unique id	nan	nan	0000978d-8599-4e17-8cf6-28e9faeba4c9	Mode
channel_name	nan	nan	Inbound	Mode
category	nan	nan	Returns	Mode
Sub-category	nan	nan	Reverse Pickup Enquiry	Mode
Customer Remarks	nan	nan	Good	Mode
Order_id	nan	nan	0000a850-ca65-4eb1-ace1-9e34d5d1637a	Mode
order_date_time	nan	nan	09/08/2023 11:55	Mode
Issue_reported at	nan	nan	13/08/2023 10:40	Mode
issue_responded	nan	nan	28/08/2023 00:00	Mode
Survey_response_Date	nan	nan	28-Aug-23	Mode
Customer_City	nan	nan	HYDERABAD	Mode
Product_category	nan	nan	Electronics	Mode
Item_price	5660.77	979	999.0	Median
connected_handling_time	462.4	427	282.0	Median
Agent_name	nan	nan	Wendy Taylor	Mode
Supervisor	nan	nan	Carter Park	Mode
Manager	nan	nan	John Smith	Mode
Tenure Bucket	nan	nan	>90	Mode
Agent Shift	nan	nan	Morning	Mode
CSAT Score	4.24	5	5	Median

Explanation:

Mode imputation was applied to categorical variables.
Median was used for skewed numeric variables like Item_price, connected_handling_time, and CSAT Score to avoid distortion from outliers.

5. Duplicate Rows Check

Duplicate Rows Removed: 0

The dataset contained no duplicate entries.

6. Quartile & Outlier Explanation (Numeric Columns)

📊 Column: Item_price

Q1 (middle of lower half) = 979.00
Q2 (median / middle of dataset) = 979.00
Q3 (middle of upper half) = 979.00
IQR (Q3 - Q1) = 0.00
Lower Bound = 979.00
Upper Bound = 979.00
Note: Values between 979.00 and 979.00 are considered normal; only values < 979.00 or > 979.00 are outliers.
➝ Outliers Found: 8600 below, 8599 above

📊 Column: connected_handling_time

Q1 (middle of lower half) = 427.00
Q2 (median / middle of dataset) = 427.00
Q3 (middle of upper half) = 427.00
IQR (Q3 - Q1) = 0.00
Lower Bound = 427.00
Upper Bound = 427.00
Note: Values between 427.00 and 427.00 are considered normal; only values < 427.00 or > 427.00 are outliers.
➝ Outliers Found: 120 below, 120 above

📊 Column: CSAT Score

Q1 (middle of lower half) = 4.00
Q2 (median / middle of dataset) = 5.00
Q3 (middle of upper half) = 5.00
IQR (Q3 - Q1) = 1.00
Lower Bound = 2.50
Upper Bound = 6.50
Note: Values between 2.50 and 6.50 are considered normal; only values < 2.50 or > 6.50 are outliers.
➝ Outliers Found: 12513 below, 0 above

7. Outlier Detection & Quartile Analysis

Numeric Columns Analyzed: Item_price, connected_handling_time, CSAT Score

Column	Q1	Median	Q3	IQR	Lower Bound	Upper Bound	Outliers Below	Outliers Above
Item_price	979	979	979	0	979	979	8,600	8,599
connected_handling_time	427	427	427	0	427	427	120	120
CSAT Score	4	5	5	1	2.5	6.5	12,513	0

Observation:

Item_price and connected_handling_time have extreme outliers due to identical Q1, Q2, Q3 values (IQR = 0).
CSAT Score shows 12,513 low outliers (scores below 2.5).

8. Before Cleaning (First 20 Rows)

ID	Call Type	Category	Subcategory	Remarks	Case ID	Timestamp	Start Time	End Time	Date	City	Department	Amount	Balance	Agent 1	Agent 2	Agent 3	Training	Shift	Rating
7e9ae164-6a8b-4521-a2d4-58f7c9fff13f	Outcall	Product Queries	Life Insurance	NaN	c27c9bb4-fa36-4140-9f1f-21009254ffdb	NaN	01/08/2023 11:13	01/08/2023 11:47	01-Aug-23	NaN	NaN	NaN	NaN	Richard Buchanan	Mason Gupta	Jennifer Nguyen	On Job Training	Morning	5
b07ec1b0-f376-43b6-86df-ec03da3b2e16	Outcall	Product Queries	Product Specific Information	NaN	d406b0c7-ce17-4654-b9de-f08d421254bd	NaN	01/08/2023 12:52	01/08/2023 12:54	01-Aug-23	NaN	NaN	NaN	NaN	Vicki Collins	Dylan Kim	Michael Lee	>90	Morning	5
200814dd-27c7-4149-ba2b-bd3af3092880	Inbound	Order Related	Installation/demo	NaN	c273368d-b961-44cb-beaf-62d6fd6c00d5	NaN	01/08/2023 20:16	01/08/2023 20:38	01-Aug-23	NaN	NaN	NaN	NaN	Duane Norman	Jackson Park	William Kim	On Job Training	Evening	5
eb0d3e53-c1ca-42d3-8486-e42c8d622135	Inbound	Returns	Reverse Pickup Enquiry	NaN	5aed0059-55a4-4ec6-bb54-97942092020a	NaN	01/08/2023 20:56	01/08/2023 21:16	01-Aug-23	NaN	NaN	NaN	NaN	Patrick Flores	Olivia Wang	John Smith	>90	Evening	5
ba903143-1e54-406c-b969-46c52f92e5df	Inbound	Cancellation	Not Needed	NaN	e8bed5a9-6933-4aff-9dc6-ccefd7dcde59	NaN	01/08/2023 10:30	01/08/2023 10:32	01-Aug-23	NaN	NaN	NaN	NaN	Christopher Sanchez	Austin Johnson	Michael Lee	0-30	Morning	5
1cfde5b9-6112-44fc-8f3b-892196137a62	Email	Returns	Fraudulent User	NaN	a2938961-2833-45f1-83d6-678d9555c603	NaN	01/08/2023 15:13	01/08/2023 18:39	01-Aug-23	NaN	NaN	NaN	NaN	Desiree Newton	Emma Park	John Smith	0-30	Morning	5
11a3ffd8-1d6b-4806-b198-c60b5934c9bc	Outcall	Product Queries	Product Specific Information	NaN	bfcb562b-9a2f-4cca-aa79-fd4e2952f901	NaN	01/08/2023 15:31	01/08/2023 23:52	01-Aug-23	NaN	NaN	NaN	NaN	Shannon Hicks	Aiden Patel	Olivia Tan	>90	Morning	5
372b51a5-fa19-4a31-a4b8-a21de117d75e	Inbound	Returns	Exchange / Replacement	Very good	88537e0b-5ffa-43f9-bbe2-fe57a0f4e4ae	NaN	01/08/2023 16:17	01/08/2023 16:23	01-Aug-23	NaN	NaN	NaN	NaN	Laura Smith	Evelyn Kimura	Jennifer Nguyen	On Job Training	Evening	5
6e4413db-4e16-42fc-ac92-2f402e3df03c	Inbound	Returns	Missing	Shopzilla app and it's all coustomer care serv...	e6be9713-13c3-493c-8a91-2137cbbfa7e6	NaN	01/08/2023 21:03	01/08/2023 21:07	01-Aug-23	NaN	NaN	NaN	NaN	David Smith	Nathan Patel	John Smith	>90	Split	5
b0a65350-64a5-4603-8b9a-a24a4a145d08	Inbound	Shopzilla Related	General Enquiry	NaN	c7caa804-2525-499e-b202-4c781cb68974	NaN	01/08/2023 23:31	01/08/2023 23:36	01-Aug-23	NaN	NaN	NaN	NaN	Tabitha Ayala	Amelia Tanaka	Michael Lee	31-60	Evening	5
74a2cbbf-497e-4177-b30b-483f99effb13	Outcall	Returns	Return request	NaN	5862d1a1-2081-4e28-ad0a-cec43380ac7d	NaN	02/08/2023 18:14	02/08/2023 18:16	02-Aug-23	NaN	NaN	NaN	NaN	Carla Morgan	Nathan Patel	Emily Chen	0-30	Evening	4
4c28acf4-2ea4-4be8-b8f1-113e676fc8b7	Inbound	Order Related	Delayed	Very bad	55bbace3-eb2e-4f67-aacb-4f8ee8512f75	19/07/2023 23:50	02/08/2023 10:44	02/08/2023 11:14	02-Aug-23	NAGPUR	LifeStyle	434.0	NaN	Stanley Hogan	Harper Wong	Emily Chen	>90	Split	1
e7ec72bb-65e9-4464-9c2b-8ce6b0e82a28	Inbound	Shopzilla Related	General Enquiry	NaN	c7d5eef8-d239-474c-8d24-542db2923b3c	NaN	01/08/2023 10:09	01/08/2023 10:12	01-Aug-23	NaN	NaN	NaN	NaN	Timothy Scott	Zoe Yamamoto	William Kim	On Job Training	Morning	4
68c596f2-4130-42f9-a140-b336261b1629	Inbound	Shopzilla Related	General Enquiry	NaN	c1e10375-7426-4b2a-aca6-17dc6bc9c7a3	NaN	01/08/2023 10:15	01/08/2023 11:21	01-Aug-23	NaN	NaN	NaN	NaN	Shannon Hicks	Aiden Patel	Olivia Tan	>90	Morning	4
6b966653-0900-4d58-8b38-c17cfbdaf000	Inbound	Returns	Reverse Pickup Enquiry	NaN	9e0d7d55-d012-42f7-8e21-634942419386	NaN	02/08/2023 11:26	02/08/2023 11:44	02-Aug-23	NaN	NaN	NaN	NaN	Mark Wilson	Scarlett Chen	John Smith	0-30	Morning	5
130dfefe-1d77-47f9-b759-546f24b6b909	Inbound	Returns	Missing	NaN	8153c646-4940-406f-992c-952ed08e9af8	NaN	02/08/2023 19:56	02/08/2023 20:06	02-Aug-23	NaN	NaN	NaN	NaN	Mark Black	Sophia Sato	John Smith	>90	Afternoon	5
4cc4df74-fcd3-413a-920d-ca3ad2dd0904	Inbound	Returns	Service Centres Related	NaN	4245d6df-49c6-491a-8724-125c4df82e75	06/05/2023 21:41	01/08/2023 09:01	01/08/2023 09:03	01-Aug-23	RANCHI	Electronics	1299.0	NaN	Amy Mendez	Sophia Sato	John Smith	0-30	Morning	5
c54459b3-ffb3-4ffa-a338-6fd179beb6b1	Inbound	Order Related	Installation/demo	Something	0cdaa1f1-02f2-40ec-9134-7cd0f92a6b3e	NaN	01/08/2023 10:00	01/08/2023 10:04	01-Aug-23	NaN	NaN	NaN	NaN	Jennifer May	Zoe Yamamoto	William Kim	On Job Training	Morning	3
deccfb1d-05d4-40b8-8371-15282c91bacb	Inbound	Cancellation	Not Needed	NaN	3f16b782-27cf-4831-b542-cd8bd60c0385	NaN	01/08/2023 21:05	01/08/2023 21:07	01-Aug-23	NaN	NaN	NaN	NaN	Ryan Thompson	Olivia Wang	Emily Chen	31-60	Evening	5
6e51734e-9627-4a12-8062-877c3638627d	Inbound	Order Related	Installation/demo	All good	8a1ae638-4d8c-4a5e-bf1b-eb74f881fc3d	18/07/2023 12:20	02/08/2023 20:03	02/08/2023 20:05	02-Aug-23	NAGPUR	Electronics	15990.0	NaN	David Butler	Olivia Wang	Emily Chen	31-60	Evening	5

9. Dataset After Cleaning

Shape After Cleaning

Rows: 85,907
Columns: 20

Missing Values Before Cleaning

Column	Missing Values
All Columns	0

10. After Cleaning (First 20 Rows)

ID	Call Type	Category	Subcategory	Remarks	Case ID	Timestamp	Start Time	End Time	Date	City	Department	Amount	Balance	Agent 1	Agent 2	Agent 3	Training	Shift	Rating
7e9ae164-6a8b-4521-a2d4-58f7c9fff13f	Outcall	Product Queries	Life Insurance	Good	c27c9bb4-fa36-4140-9f1f-21009254ffdb	09/08/2023 11:55	01/08/2023 11:13	01/08/2023 11:47	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Richard Buchanan	Mason Gupta	Jennifer Nguyen	On Job Training	Morning	5.00
b07ec1b0-f376-43b6-86df-ec03da3b2e16	Outcall	Product Queries	Product Specific Information	Good	d406b0c7-ce17-4654-b9de-f08d421254bd	09/08/2023 11:55	01/08/2023 12:52	01/08/2023 12:54	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Vicki Collins	Dylan Kim	Michael Lee	>90	Morning	5.00
200814dd-27c7-4149-ba2b-bd3af3092880	Inbound	Order Related	Installation/demo	Good	c273368d-b961-44cb-beaf-62d6fd6c00d5	09/08/2023 11:55	01/08/2023 20:16	01/08/2023 20:38	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Duane Norman	Jackson Park	William Kim	On Job Training	Evening	5.00
eb0d3e53-c1ca-42d3-8486-e42c8d622135	Inbound	Returns	Reverse Pickup Enquiry	Good	5aed0059-55a4-4ec6-bb54-97942092020a	09/08/2023 11:55	01/08/2023 20:56	01/08/2023 21:16	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Patrick Flores	Olivia Wang	John Smith	>90	Evening	5.00
ba903143-1e54-406c-b969-46c52f92e5df	Inbound	Cancellation	Not Needed	Good	e8bed5a9-6933-4aff-9dc6-ccefd7dcde59	09/08/2023 11:55	01/08/2023 10:30	01/08/2023 10:32	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Christopher Sanchez	Austin Johnson	Michael Lee	0-30	Morning	5.00
1cfde5b9-6112-44fc-8f3b-892196137a62	Email	Returns	Fraudulent User	Good	a2938961-2833-45f1-83d6-678d9555c603	09/08/2023 11:55	01/08/2023 15:13	01/08/2023 18:39	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Desiree Newton	Emma Park	John Smith	0-30	Morning	5.00
11a3ffd8-1d6b-4806-b198-c60b5934c9bc	Outcall	Product Queries	Product Specific Information	Good	bfcb562b-9a2f-4cca-aa79-fd4e2952f901	09/08/2023 11:55	01/08/2023 15:31	01/08/2023 23:52	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Shannon Hicks	Aiden Patel	Olivia Tan	>90	Morning	5.00
372b51a5-fa19-4a31-a4b8-a21de117d75e	Inbound	Returns	Exchange / Replacement	Very good	88537e0b-5ffa-43f9-bbe2-fe57a0f4e4ae	09/08/2023 11:55	01/08/2023 16:17	01/08/2023 16:23	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Laura Smith	Evelyn Kimura	Jennifer Nguyen	On Job Training	Evening	5.00
6e4413db-4e16-42fc-ac92-2f402e3df03c	Inbound	Returns	Missing	Shopzilla app and it's all coustomer care services is very good service provided all time	e6be9713-13c3-493c-8a91-2137cbbfa7e6	09/08/2023 11:55	01/08/2023 21:03	01/08/2023 21:07	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	David Smith	Nathan Patel	John Smith	>90	Split	5.00
b0a65350-64a5-4603-8b9a-a24a4a145d08	Inbound	Shopzilla Related	General Enquiry	Good	c7caa804-2525-499e-b202-4c781cb68974	09/08/2023 11:55	01/08/2023 23:31	01/08/2023 23:36	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Tabitha Ayala	Amelia Tanaka	Michael Lee	31-60	Evening	5.00
74a2cbbf-497e-4177-b30b-483f99effb13	Outcall	Returns	Return request	Good	5862d1a1-2081-4e28-ad0a-cec43380ac7d	09/08/2023 11:55	02/08/2023 18:14	02/08/2023 18:16	02-Aug-23	HYDERABAD	Electronics	979.00	427.00	Carla Morgan	Nathan Patel	Emily Chen	0-30	Evening	4.00
4c28acf4-2ea4-4be8-b8f1-113e676fc8b7	Inbound	Order Related	Delayed	Very bad	55bbace3-eb2e-4f67-aacb-4f8ee8512f75	09/08/2023 11:55	19/07/2023 23:50	02/08/2023 10:44	02/08/2023 11:14	NAGPUR	LifeStyle	979.00	427.00	Stanley Hogan	Harper Wong	Emily Chen	>90	Split	2.50
e7ec72bb-65e9-4464-9c2b-8ce6b0e82a28	Inbound	Shopzilla Related	General Enquiry	Good	c7d5eef8-d239-474c-8d24-542db2923b3c	09/08/2023 11:55	01/08/2023 10:09	01/08/2023 10:12	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Timothy Scott	Zoe Yamamoto	William Kim	On Job Training	Morning	4.00
68c596f2-4130-42f9-a140-b336261b1629	Inbound	Shopzilla Related	General Enquiry	Good	c1e10375-7426-4b2a-aca6-17dc6bc9c7a3	09/08/2023 11:55	01/08/2023 10:15	01/08/2023 11:21	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Shannon Hicks	Aiden Patel	Olivia Tan	>90	Morning	4.00
6b966653-0900-4d58-8b38-c17cfbdaf000	Inbound	Returns	Reverse Pickup Enquiry	Good	9e0d7d55-d012-42f7-8e21-634942419386	09/08/2023 11:55	02/08/2023 11:26	02/08/2023 11:44	02-Aug-23	HYDERABAD	Electronics	979.00	427.00	Mark Wilson	Scarlett Chen	John Smith	0-30	Morning	5.00
130dfefe-1d77-47f9-b759-546f24b6b909	Inbound	Returns	Missing	Good	8153c646-4940-406f-992c-952ed08e9af8	09/08/2023 11:55	02/08/2023 19:56	02/08/2023 20:06	02-Aug-23	HYDERABAD	Electronics	979.00	427.00	Mark Black	Sophia Sato	John Smith	>90	Afternoon	5.00
4cc4df74-fcd3-413a-920d-ca3ad2dd0904	Inbound	Returns	Service Centres Related	Good	4245d6df-49c6-491a-8724-125c4df82e75	09/08/2023 11:55	06/05/2023 21:41	01/08/2023 09:01	01/08/2023 09:03	RANCHI	Electronics	979.00	427.00	Amy Mendez	Sophia Sato	John Smith	0-30	Morning	5.00
c54459b3-ffb3-4ffa-a338-6fd179beb6b1	Inbound	Order Related	Installation/demo	Something	0cdaa1f1-02f2-40ec-9134-7cd0f92a6b3e	09/08/2023 11:55	01/08/2023 10:00	01/08/2023 10:04	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Jennifer May	Zoe Yamamoto	William Kim	On Job Training	Morning	3..00
deccfb1d-05d4-40b8-8371-15282c91bacb	Inbound	Cancellation	Not Needed	Good	3f16b782-27cf-4831-b542-cd8bd60c0385	09/08/2023 11:55	01/08/2023 21:05	01/08/2023 21:07	01-Aug-23	HYDERABAD	Electronics	979.00	427.00	Ryan Thompson	Olivia Wang	Emily Chen	31-60	Evening	5.00
6e51734e-9627-4a12-8062-877c3638627d	Inbound	Order Related	Installation/demo	All good	8a1ae638-4d8c-4a5e-bf1b-eb74f881fc3d	09/08/2023 11:55	18/07/2023 12:20	02/08/2023 20:03	02/08/2023 20:05	NAGPUR	Electronics	979.00	427.00	David Butler	Olivia Wang	Emily Chen	31-60	Evening	5.00

11. Summary of Cleaning Process

Dataset Loaded: 85,907 rows × 20 columns.
Missing Values: Identified and imputed with Mode (categorical) or Median (numeric).
Duplicate Rows: Checked and none removed.
Outlier Analysis: Conducted using IQR method; numeric outliers highlighted.
Dataset Ready: Cleaned dataset with no missing values, ready for EDA, visualization, or machine learning.