# NAME = ANKIT (ankitthalor72@gmail.
com)
import pandas as pd
import [Link] as plt
import [Link] as px
import seaborn as sns
df = pd.read_csv('Comcast_telecom_complaints_data.csv')
df
Filing on
Ticket Customer Received Zip
Date Date_month_year Time City State Status Behalf of
# Complaint Via code
Someone
22-
Comcast Cable 3:53:50 Customer
0 250635 04- 22-Apr-15 Abingdon Maryland 21009 Closed No
Internet Speeds PM Care Call
15
Payment disappear - 04-
10:22:56
1 223441 service got 08- 04-Aug-15 Internet Acworth Georgia 30102 Closed No
AM
disconnected 15
18-
9:55:47
2 242732 Speed and Service 04- 18-Apr-15 Internet Acworth Georgia 30101 Closed Yes
AM
15
Comcast Imposed a 05-
11:59:35
3 277946 New Usage Cap of 07- 05-Jul-15 Internet Acworth Georgia 30101 Open Yes
AM
300GB that ... 15
Comcast not working 26-
1:25:26
4 307175 and no service to 05- 26-May-15 Internet Acworth Georgia 30101 Solved No
PM
boot 15
... ... ... ... ... ... ... ... ... ... ... ...
04-
9:13:18 Customer
2219 213550 Service Availability 02- 04-Feb-15 Youngstown Florida 32466 Closed No
AM Care Call
15
Comcast Monthly 06-
1:24:39 Customer
2220 318775 Billing for Returned 02- 06-Feb-15 Ypsilanti Michigan 48197 Solved No
PM Care Call
Modem 15
06-
complaint about 5:28:41
2221 331188 09- 06-Sep-15 Internet Ypsilanti Michigan 48197 Solved No
comcast PM
15
Extremely 23-
11:13:30 Customer
2222 360489 unsatisfied Comcast 06- 23-Jun-15 Ypsilanti Michigan 48197 Solved No
PM Care Call
customer 15
24-
Comcast, Ypsilanti 10:28:33 Customer
2223 363614 06- 24-Jun-15 Ypsilanti Michigan 48198 Open Yes
MI Internet Speed PM Care Call
15
2224 rows × 11 columns
[Link]
<bound method [Link] of Ticket # Customer Complaint Date \
0 250635 Comcast Cable Internet Speeds 22-04-15
1 223441 Payment disappear - service got disconnected 04-08-15
2 242732 Speed and Service 18-04-15
3 277946 Comcast Imposed a New Usage Cap of 300GB that ... 05-07-15
4 307175 Comcast not working and no service to boot 26-05-15
... ... ... ...
2219 213550 Service Availability 04-02-15
2220 318775 Comcast Monthly Billing for Returned Modem 06-02-15
2221 331188 complaint about comcast 06-09-15
2222 360489 Extremely unsatisfied Comcast customer 23-06-15
2223 363614 Comcast, Ypsilanti MI Internet Speed 24-06-15
Date_month_year Time Received Via City State \
0 22-Apr-15 3:53:50 PM Customer Care Call Abingdon Maryland
1 04-Aug-15 10:22:56 AM Internet Acworth Georgia
2 18-Apr-15 9:55:47 AM Internet Acworth Georgia
3 05-Jul-15 11:59:35 AM Internet Acworth Georgia
4 26-May-15 1:25:26 PM Internet Acworth Georgia
... ... ... ... ... ...
2219 04-Feb-15 9:13:18 AM Customer Care Call Youngstown Florida
2220 06-Feb-15 1:24:39 PM Customer Care Call Ypsilanti Michigan
2221 06-Sep-15 5:28:41 PM Internet Ypsilanti Michigan
2222 23-Jun-15 11:13:30 PM Customer Care Call Ypsilanti Michigan
2223 24-Jun-15 10:28:33 PM Customer Care Call Ypsilanti Michigan
Zip code Status Filing on Behalf of Someone
0 21009 Closed No
1 30102 Closed No
2 30101 Closed Yes
3 30101 Open Yes
4 30101 Solved No
... ... ... ...
2219 32466 Closed No
2220 48197 Solved No
2221 48197 Solved No
2222 48197 Solved No
2223 48198 Open Yes
[2224 rows x 11 columns]>
#1
df = pd.read_csv('Comcast_telecom_complaints_data.csv')
df['Date_month_year'] = pd.to_datetime(df['Date_month_year'], format='%d-%b-%y')
df['Month'] = df['Date_month_year'].dt.to_period('M')
df['Day'] = df['Date_month_year'].[Link]
monthly_trend = [Link]('Month').size()
daily_trend = [Link]('Day').size()
[Link](figsize=(12, 6))
monthly_trend.plot(kind='line', marker='o')
[Link]('Monthly Trend of Complaints')
[Link]('Month')
[Link]('Number of Complaints')
[Link](True)
[Link]()
[Link](figsize=(12, 6))
daily_trend.plot(kind='line', marker='o')
[Link]('Daily Trend of Complaints')
[Link]('Day')
[Link]('Number of Complaints')
[Link](True)
[Link]()
#2
df = pd.read_csv('Comcast_telecom_complaints_data.csv')
raw_counts = df['Customer Complaint'].value_counts().reset_index()
raw_counts.columns = ['Complaint Type', 'Frequency']
def categorize_complaint(complaint):
complaint = [Link]()
if 'data cap' in complaint or 'data usage' in complaint:
return 'Comcast Data Cap'
elif 'billing' in complaint or 'overcharge' in complaint:
return 'Billing Issues'
elif 'speed' in complaint or 'throttling' in complaint:
return 'Internet Speed/Service Quality'
elif 'outage' in complaint or 'disconnect' in complaint:
return 'Service Outages/Disconnections'
elif 'customer service' in complaint or 'poor service' in complaint:
return 'Poor Customer Service'
elif 'equipment' in complaint or 'modem' in complaint:
return 'Equipment Rental Fees'
else:
return 'Other'
df['Complaint Category'] = df['Customer Complaint'].apply(categorize_complaint)
category_counts = df['Complaint Category'].value_counts().reset_index()
category_counts.columns = ['Complaint Type', 'Frequency']
print(category_counts)
Complaint Type Frequency
0 Other 1381
1 Billing Issues 310
2 Internet Speed/Service Quality 217
3 Comcast Data Cap 184
4 Poor Customer Service 70
5 Service Outages/Disconnections 34
6 Equipment Rental Fees 28
#3
[Link](figsize=(10, 6))
[Link](category_counts['Complaint Type'], category_counts['Frequency'], color='skyblue')
[Link]('Frequency of Complaint Types')
[Link]('Complaint Type')
[Link]('Frequency')
[Link](rotation=45, ha='right')
plt.tight_layout()
[Link]()
#4
df = pd.read_csv('Comcast_telecom_complaints_data.csv')
df['Complaint Status'] = df['Status'].apply(lambda x: 'Open' if x in ['Open', 'Pending'] else 'Closed')
print(df[['Ticket #', 'Status', 'Complaint Status']].head())
status_counts = df['Complaint Status'].value_counts()
print(status_counts)
Ticket # Status Complaint Status
0 250635 Closed Closed
1 223441 Closed Closed
2 242732 Closed Closed
3 277946 Open Open
4 307175 Solved Closed
Complaint Status
Closed 1707
Open 517
Name: count, dtype: int64
#5
df = pd.read_csv('Comcast_telecom_complaints_data.csv')
state_status = [Link](['State', 'Status']).size().unstack().fillna(0)
state_status.plot(kind='bar', stacked=True, figsize=(14, 8))
[Link]('State-wise Status of Complaints')
[Link]('State')
[Link]('Number of Complaints')
[Link](rotation=45)
[Link](title='Status')
plt.tight_layout()
[Link]()
#6
state_complaints = df['State'].value_counts()
max_state = state_complaints.idxmax()
max_complaints = state_complaints.max()
print(f"The state with the maximum complaints is {max_state} with {max_complaints} complaints.")
The state with the maximum complaints is Georgia with 288 complaints.
#7
unresolved = df[df['Status'].isin(['Open', 'Pending'])]
unresolved_counts = unresolved['State'].value_counts()
total_counts = df['State'].value_counts()
unresolved_percentage = (unresolved_counts / total_counts) * 100
max_unresolved_state = unresolved_percentage.idxmax()
max_unresolved_percentage = unresolved_percentage.max()
print(f"The state with the highest percentage of unresolved complaints is {max_unresolved_state} with {max_unresolved
The state with the highest percentage of unresolved complaints is Kansas with 50.00% unresolved complaints.
#8
resolved = df[df['Status'].isin(['Closed', 'Solved'])]
resolved_counts = resolved['Received Via'].value_counts()
total_counts = df['Received Via'].value_counts()
resolved_percentage = (resolved_counts / total_counts) * 100
print("Percentage of resolved complaints:")
print(resolved_percentage)
Percentage of resolved complaints:
Received Via
Customer Care Call 77.211796
Internet 76.289593
Name: count, dtype: float64