Comcast Telecom Complaint Type

DESCRIPTION
Comcast is an American global telecommunication company. The firm has been providing terrible
customer service. They continue to fall short despite repeated promises to improve. Only last
month (October 2016) the authority fined them a $2.3 million, after receiving over 1000
consumer complaints.The existing database will serve as a repository of public customer
complaints filed against Comcast.It will help to pin down what is wrong with Comcast's
customer service.

Data Dictionary
- Ticket #: Ticket number assigned to each complaint
- Customer Complaint: Description of complaint
- Date: Date of complaint
- Time: Time of complaint
- Received Via: Mode of communication of the complaint
- City: Customer city
- State: Customer state
- Zipcode: Customer zip
- Status: Status of complaint
- Filing on behalf of someone

Analysis Task
To perform these tasks, you can use any of the different Python libraries such as NumPy,
SciPy, Pandas, scikit-learn, matplotlib, and BeautifulSoup.

- Import data into Python environment.

- Provide the trend chart for the number of complaints at monthly and daily granularity
levels.
- Provide a table with the frequency of complaint types.
- Which complaint types are maximum i.e., around internet, network issues, or across any
other domains.
- Create a new categorical variable with value as Open and Closed. Open & Pending is to
be categorized as Open and Closed & Solved is to be categorized as Closed.
- Provide state wise status of complaints in a stacked bar chart. Use the categorized
variable from Q3.
- Provide insights on:
* Which state has the maximum complaints
* Which state has the highest percentage of unresolved complaints
- Provide the percentage of complaints resolved till date, which were received through
the Internet and customer care calls.

In [1]: # Import necessary libraries

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

In [2]: #To ignore warnings

warnings.simplefilter('ignore')

Analysis Task 1
- Import data into python environment

In [3]: # Importing Data using pandas

comcast = pd.read_csv("Comcast_telecom_complaints_data.csv")
comcast

Out[3]:
Filing on
Ticket Received Zip
Customer Complaint Date Date_month_year Time City State Status Behalf of
# Via code
Someone

22-
Comcast Cable Internet 3:53:50 Customer
0 250635 04- 22-Apr-15 Abingdon Maryland 21009 Closed No
Speeds PM Care Call
15

04-
Payment disappear - service 10:22:56
1 223441 08- 04-Aug-15 Internet Acworth Georgia 30102 Closed No
got disconnected AM
15

18-
9:55:47
2 242732 Speed and Service 04- 18-Apr-15 Internet Acworth Georgia 30101 Closed Yes
AM
15

05-
Comcast Imposed a New 11:59:35
3 277946 07- 05-Jul-15 Internet Acworth Georgia 30101 Open Yes
Usage Cap of 300GB that ... AM
15

26-
Comcast not working and no 1:25:26
4 307175 05- 26-May-15 Internet Acworth Georgia 30101 Solved No
service to boot PM
15

... ... ... ... ... ... ... ... ... ... ... ...

04-
9:13:18 Customer
2219 213550 Service Availability 02- 04-Feb-15 Youngstown Florida 32466 Closed No
AM Care Call
15

06-
Comcast Monthly Billing for 1:24:39 Customer
2220 318775 02- 06-Feb-15 Ypsilanti Michigan 48197 Solved No
Returned Modem PM Care Call
15

06-
5:28:41
2221 331188 complaint about comcast 09- 06-Sep-15 Internet Ypsilanti Michigan 48197 Solved No
PM
15

23-
Extremely unsatisfied 11:13:30 Customer
2222 360489 06- 23-Jun-15 Ypsilanti Michigan 48197 Solved No
Comcast customer PM Care Call
15

24-
Comcast, Ypsilanti MI Internet 10:28:33 Customer
2223 363614 06- 24-Jun-15 Ypsilanti Michigan 48198 Open Yes
Speed PM Care Call
15

2224 rows × 11 columns

In [4]: # Dataset shape

comcast.shape

Out[4]: (2224, 11)

In [5]: # Dataset description

comcast.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2224 entries, 0 to 2223
Data columns (total 11 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Ticket # 2224 non-null object
1 Customer Complaint 2224 non-null object
2 Date 2224 non-null object
3 Date_month_year 2224 non-null object
4 Time 2224 non-null object
5 Received Via 2224 non-null object
6 City 2224 non-null object
7 State 2224 non-null object
8 Zip code 2224 non-null int64
9 Status 2224 non-null object
10 Filing on Behalf of Someone 2224 non-null object
dtypes: int64(1), object(10)
memory usage: 191.2+ KB

In [6]: # Converting column names to lowercase and removing whitespaces for easy access
comcast.columns = comcast.columns.str.lower().str.replace(" ","_")

In [7]: # Converting date column to datetime object and retrieving month .

comcast['date'] = pd.to_datetime(comcast['date_month_year'])
comcast['month_no'] = comcast['date'].dt.month
comcast['month_name'] = comcast['date'].dt.month

In [8]: # Converting month no. to month names

month_dict = {1:"Jan",2:"Feb",3:"Mar",4:"Apr",5:"May",6:"Jun",7:"Jul",8:"Aug",9:"Sep",10:"Oct",11:"Nov"
,12:"Dec"}
comcast['month_name'] = comcast['month_name'].apply(lambda x: month_dict[x])
comcast.insert(3,'month',comcast['month_name'])
comcast.drop(columns=['month_name'],inplace=True)
comcast

Out[8]:
ticket_# customer_complaint date month date_month_year time received_via city state zip_code status filing

Comcast Cable 2015- 3:53:50 Customer

0 250635 Apr 22-Apr-15 Abingdon Maryland 21009 Closed
Internet Speeds 04-22 PM Care Call

Payment disappear -
2015- 10:22:56
1 223441 service got Aug 04-Aug-15 Internet Acworth Georgia 30102 Closed
08-04 AM
disconnected

2015- 9:55:47
2 242732 Speed and Service Apr 18-Apr-15 Internet Acworth Georgia 30101 Closed
04-18 AM

Comcast Imposed a
2015- 11:59:35
3 277946 New Usage Cap of Jul 05-Jul-15 Internet Acworth Georgia 30101 Open
07-05 AM
300GB that ...

Comcast not working

2015- 1:25:26
4 307175 and no service to May 26-May-15 Internet Acworth Georgia 30101 Solved
05-26 PM
boot

... ... ... ... ... ... ... ... ... ... ... ...

2015- 9:13:18 Customer

2219 213550 Service Availability Feb 04-Feb-15 Youngstown Florida 32466 Closed
02-04 AM Care Call

Comcast Monthly
2015- 1:24:39 Customer
2220 318775 Billing for Returned Feb 06-Feb-15 Ypsilanti Michigan 48197 Solved
02-06 PM Care Call
Modem

complaint about 2015- 5:28:41

2221 331188 Sep 06-Sep-15 Internet Ypsilanti Michigan 48197 Solved
comcast 09-06 PM

Extremely unsatisfied 2015- 11:13:30 Customer

2222 360489 Jun 23-Jun-15 Ypsilanti Michigan 48197 Solved
Comcast customer 06-23 PM Care Call

Comcast, Ypsilanti MI 2015- 10:28:33 Customer

2223 363614 Jun 24-Jun-15 Ypsilanti Michigan 48198 Open
Internet Speed 06-24 PM Care Call

2224 rows × 13 columns

In [ ]:

Analysis Task 2
- trend chart for the number of complaints at monthly and daily granularity levels.

In [9]: # Monthly complaint counts

month_complaints = comcast.groupby('month_no')['customer_complaint'].count()
month_complaints = month_complaints.to_frame()
month_complaints = month_complaints.sort_index()
month_complaints

Out[9]:
customer_complaint

month_no

1 55

2 59

3 45

4 375

5 317

6 1046

7 49

8 67

9 55

10 53

11 38

12 65

In [10]: # Trend chart for monthly complaint counts

plt.figure(figsize=(10,5))
plt.plot(month_complaints.index,month_complaints['customer_complaint'],marker='o',color='orange',marker
facecolor = 'black')
plt.xticks([1,2,3,4,5,6,7,8,9,10,11,12],['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','N
ov','Dec'])
txt = {'weight':'bold'}
plt.title("Monthly Complaint Trend Chart",fontdict=txt)
plt.xlabel('Month',fontdict=txt,labelpad=30)
plt.ylabel('Count',fontdict=txt,labelpad=30)
plt.show()

June month has the highest number of complaints from customers

In [11]: # Daily complaint counts

date_complaints = comcast.groupby('date')['customer_complaint'].count()
date_complaints = date_complaints.to_frame()
date_complaints.head(10)

Out[11]:
customer_complaint

date

2015-01-04 18

2015-01-05 12

2015-01-06 25

2015-02-04 27

2015-02-05 7

2015-02-06 25

2015-03-04 15

2015-03-05 5

2015-03-06 25

2015-04-04 12

In [12]: # Trend chart for daily complaint counts

plt.figure(figsize=(20,10))
plt.plot(date_complaints.index,date_complaints['customer_complaint'],marker='o',color='orange',markerfa
cecolor='black')
plt.xticks(rotation=90)
txt = {'weight':'bold'}
plt.title("Daily Complaint Trend Chart",fontdict=txt)
plt.xlabel('Date',fontdict=txt,labelpad=30)
plt.ylabel('Count',fontdict=txt,labelpad=30)
plt.show()

In [13]: # Highest number of complaints is on month June

print(date_complaints.loc['2015-06-24'])

customer_complaint 218
Name: 2015-06-24 00:00:00, dtype: int64

June 24 th of 2015 has number of highest customer complaints

In [ ]:

Analysis Task 3
- Provide a table with the frequency of complaint types.

In [14]: comcast['customer_complaint'] = comcast['customer_complaint'].str.lower()

comcast['complaint_type'] = 0

In [15]: # Define loop to group complaints based on types

for i in comcast['customer_complaint']:
if "data cap" in i:
comcast['complaint_type'][comcast[comcast['customer_complaint']==i].index] = "data cap complain
t"
elif "speed" in i:
comcast['complaint_type'][comcast[comcast['customer_complaint']==i].index] = "internet speed co
mplaint"
elif "bill" in i:
comcast['complaint_type'][comcast[comcast['customer_complaint']==i].index] = "billing complain
t"
elif "internet" in i:
comcast['complaint_type'][comcast[comcast['customer_complaint']==i].index] = "internet complain
t"
elif "service" in i:
comcast['complaint_type'][comcast[comcast['customer_complaint']==i].index] = "service complain
t"
elif "comcast" in i:
comcast['complaint_type'][comcast[comcast['customer_complaint']==i].index] = "comcast complaint
s"
else:
comcast['complaint_type'][comcast[comcast['customer_complaint']==i].index] = i

In [16]: # Frequency Table for top 15 complaints

top15_complaints = comcast['complaint_type'].value_counts().head(15).to_frame()
top15_complaints['count'] = top15_complaints['complaint_type']
top15_complaints['complaint_type'] = top15_complaints.index
top15_complaints['count'].to_frame()

Out[16]:
count

comcast complaints 558

internet complaint 370

billing complaint 366

service complaint 273

internet speed complaint 191

data cap complaint 150

availabilty 4

unfair pricing 4

throttling 3

monopoly 3

fraud 2

xfinity 2

cable 2

charged for data not actually used 2

continued slowness for almost 3 weeks 2

Comcast complaints are the highest complaint type registered followed by that is internet complaint.

In [17]: # Barplot for the above frequency Table

plt.figure(figsize=(15,10))
sns.barplot(x='complaint_type',y='count',data=top15_complaints)
plt.xticks(rotation=90)
txt = {'weight':'bold'}
plt.title("Complaint Types and No. of complaints",fontdict=txt)
plt.xlabel('Complaint Type',fontdict=txt,labelpad=30)
plt.ylabel('Count',fontdict=txt,labelpad=30)
for i in range(15):
plt.annotate(top15_complaints['count'][i],xy=(i-0.2,top15_complaints['count'][i]+10),size=13)
plt.show()

In [ ]:

Analysis Task 4
- Create a new categorical variable with value as Open and Closed. Open & Pending is to be
categorized as Open and Closed & Solved is to be categorized as Closed.

In [18]: # Create a dummy categorical variable

comcast['open_or_closed']=0

In [19]: # Define for loop for categorizing the complaint staus

for i in comcast['status']:
if i in ["Open","Pending"]:
comcast['open_or_closed'][comcast[comcast['status']==i].index] = "Open"
else:
comcast['open_or_closed'][comcast[comcast['status']==i].index] = "Closed"

In [20]: # Frequency Table

comcast['open_or_closed'].value_counts().to_frame()

Out[20]:
open_or_closed

Closed 1707

Open 517

Table shows that most of the complaints registered has been resolved

In [ ]:

Analysis Task 5
- Provide state wise status of complaints in a stacked bar chart. Use the categorized
variable from Q3.

In [21]: # Stacked Barchart to represent state wise complaint counts and its status
comcast.groupby(['state','open_or_closed']).size().unstack().plot.bar(figsize=(20,10),stacked=True,colo
r=['darkgreen','sandybrown'])
txt = {'weight':'bold'}
plt.title("Stacked Barplot for Different States",fontdict=txt)
plt.xlabel('State',fontdict=txt,labelpad=20)
plt.ylabel('Count',fontdict=txt,labelpad=30)
plt.show()

Stacked Barchart shows state wise complaints that has been resolved and unresolved

In [ ]:

Analysis Task 6
- Provide insights on:
* Which state has the maximum complaints
* Which state has the highest percentage of unresolved complaints

In [22]: # State wise no. of complaints registered

s = comcast['state'].value_counts().to_frame()
s['complaint_count'] = s['state']
s['state'] = s.index
s.head(10)

Out[22]:
state complaint_count

Georgia Georgia 288

Florida Florida 240

California California 220

Illinois Illinois 164

Tennessee Tennessee 143

Pennsylvania Pennsylvania 130

Michigan Michigan 115

Washington Washington 98

Colorado Colorado 80

Maryland Maryland 78

In [23]: # Barplot for the above frequency table

plt.figure(figsize=(18,10))
sns.barplot(x='state',y='complaint_count',data=s.head(10),palette='Set2')
for i in range(10):
plt.annotate(s['complaint_count'][i],xy=(i-0.1,s['complaint_count'][i]+3),size=15)
txt = {'weight':'bold'}
plt.title("States and No. of complaints",fontdict=txt)
plt.xlabel('State',fontdict=txt,labelpad=30)
plt.ylabel('Count',fontdict=txt,labelpad=30)
plt.xticks(rotation=90)
plt.show()

Georgia has the maximum number of complaints registered

In [24]: # Bar Plot to represent The states with its Unresolved complaint percentage
high = comcast.groupby(['state','open_or_closed']).size().unstack()
high.fillna(0,inplace=True)
high['unresolved_percent'] = (high['Open']/(high['Open']+high['Closed']))*100
high = high.sort_values('unresolved_percent',ascending=False)
plt.figure(figsize=(15,8))
high['unresolved_percent'].head(10).plot.bar(color='darkolivegreen')
txt = {'weight':'bold'}
plt.title("State and Unresolved Complaint Percentage",fontdict=txt)
plt.xlabel('State',fontdict=txt,labelpad=30)
plt.ylabel('Unresolved Complaint Percentage',fontdict=txt,labelpad=30)
plt.show()

Kansas has the highest percentage of unresolved complaints than other states

In [ ]:

Analysis Task 7
- Provide the percentage of complaints resolved till date, which were received through the
Internet and customer care calls.

In [25]: # Finding the Resolved complaint percentage for various mediums

resolved = comcast.groupby(['received_via','open_or_closed']).size().unstack()
resolved['resolved_percentage'] = (resolved['Closed']/(resolved['Closed']+resolved['Open']))*100
resolved

Out[25]:
open_or_closed Closed Open resolved_percentage

received_via

Customer Care Call 864 255 77.211796

Internet 843 262 76.289593

77.2 % of Complaints recieved through Customer care calls were resolved

76.3 % of Complaints recieved through Internet were resolved

In [ ]: