<jupyter_start><jupyter_text>**DEFINING THE QUESTION****Describe the Data Analysis Question**Understand the mobile usage in Ivory Coast . Ivory Coast also known as Côte d'Ivoire is a country in West Africa. Deciding on which city to select will assist to inform strategy to be used by MTN Cote d'Ivoire in upgrading its technology infrastructure.**Define Metric for Success** The main objective of this project is to improve Telecommunication Services across mobile users in Ivory coast and drive more revenue to the MTN Cote d'Ivoire telecommunication company.For objective to be meet we have to pinpoint the following:Which city has the highest user traffic ?Which city has the lowest user traffic ?Which products are offered by MTN ?Which MTN product is mostly used per city ?Which MTN product is not mostly used per city ?Which time of the day there is more traffic coming from users?Which time of the day there is less traffic from the users ?Which Cell has high coverage ?Which Cell has low coverage ?Which Cell is receiving the most traffic ?Which areas support the Fibre optic ?**Understanding the context**MTN Côte d'Ivoire, a leading telecom company is looking forward to upgrading its technology infrastructure for its mobile users in Ivory Coast. As a data scientist, I am charged with the responsibility for assisting MTN to implement a suitable upgrading strategy within given cities by understanding mobile usage in them.**Recording the experimental design**Import LibrariesLoad datasetsClean Datasets by:Changing the column names,Change to lower case,Changing Column Title for datasets,Drop unnecessary columns,Check for missing data,Check for duplicates,Drop duplicate if present,Concatenating the three datasets,Merge telcom dataset with geo data on site code attribute,Explore cities with highest traffic per region and per zone,Upon identifying high traffic cities, explore which product usage per city,Explore peak hours per day in high traffic cities,Splitting date and time, **Data Analysis** **Dependencies**At this stage we  import libraries that we rely on so as to develop a solution for this problem.Some of these libraries are :NumPy / pandas; assist us to treat and explore the data, and execute vector and matrix operations.matplotlib / seaborn; a tool that will help to plot the information so we can visualize it in various ways and have a better understanding of it.Plotly; assist us in plotting data in a fancy way.sklearn; Will provide all necessary tools to train our models and test them afterwards.<jupyter_code>#import python libraries
import csv
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import plotly as py
import plotly.graph_objs as go
from sklearn.cluster import KMeans
import warnings
warnings.filterwarnings("ignore")<jupyter_output><empty_output><jupyter_text>**Loading our Datasets**<jupyter_code>#Download and upload the data
#To load the Datasets
#To Preview the dataset
df=pd.read_csv('/content/Telcom_dataset.csv')
df.head()
#To load the second dataset
#To preview the dataset
df2=pd.read_csv('/content/Telcom_dataset2.csv')
df2.head()
#To load the third dataset
#To preview the dataset

df3=pd.read_csv('/content/Telcom_dataset3.csv')
df3.head()
#To load fourth dataset
#To preview the dataset
url='/content/cells_geo.csv'
df4= pd.read_csv(url, sep=";", encoding='utf-8') 
df4.head()


#Drop unnamed column from fourth dataset
df4.drop('Unnamed: 0', axis=1, inplace=True)
#preview the first five rows after getting rid off unnamed column
df4.head()
#To load the fifth dataset
#To preview the dataset

url='/content/cells_geo_description.xlsx'
df5=pd.read_excel(url, sheet_name=0, header=0)
df5


#To the sixth dataset
#To preview the dataset

url='/content/CDR_description.xlsx'
df6=pd.read_excel(url, sheet_name=0, header=0)
df6<jupyter_output><empty_output><jupyter_text>**Data Exploration**In this stage we are doing a little bit of data exploration, checking for null values, object data types and other things we might consider in order to keep our data clean and well structured. Also, normalization or will be undertaken in this stage.<jupyter_code>#change column names 
#Change to lower case
#preview column names


df.columns = map(str.lower, df.columns)
df.columns


columns = ['product', 'bill',	'datetime','cell_on_site','caller_anonymized', 'recipient_anonymized', 'country_a','country_b','cell_id', 'site_id']<jupyter_output><empty_output><jupyter_text>*Rename attribute names*<jupyter_code>#Rename attribute names for dataset 1
#preview dataset 1

df.columns = columns
df.head()
#Rename attribute names for dataset 2
#preview dataset

df2.columns=columns
df2.head()

df3.columns=columns
df3.head()<jupyter_output><empty_output><jupyter_text>*Drop unnecessary columns*Attribute values of country_a and country_b are similar making it difficult to classify our data. Also, cell_on_site column is not of great importance since we can get it from cell_id.<jupyter_code>#Drop unnecessary attributes

drop = ['cell_on_site' , 'country_a' , 'country_b']
df.drop(drop, axis=1, inplace=True)
df2.drop(drop, axis=1, inplace=True)
df3.drop(drop,axis=1,inplace=True)


# preview dataset 1
df.head()

# preview dataset 2
df2.head()
#preview dataset 3
df3.head()<jupyter_output><empty_output><jupyter_text>*Check for shape and information of the datasets*<jupyter_code># Shape of datasets df1
df.shape
# check for information of df
df.info()
# shape of dataset df2
df2.shape
# check for information of df2
df2.info()
# shape of dataset df3
df3.shape
# check for information of df3
df3.info()
# shape of dataset df4
df4.shape
# check for information of df4
df4.info()
# shape of dataset df5
df5.shape
# check for information of df5
df5.info
# shape of dataset df6
df6.shape
# check for information of df6
df6.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 10 entries, 0 to 9
Data columns (total 3 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   Column name  10 non-null     object
 1   Description  10 non-null     object
 2   Format       10 non-null     object
dtypes: object(3)
memory usage: 368.0+ bytes<jupyter_text>*check for datatypes and columns*<jupyter_code># check for datatypes of dataset df
df.dtypes
# check for columns for column names of df
df.columns

# check for datatypes of dataset df2
df2.dtypes 
# check for column names of df2
df.columns
# check for datatypes of df3
df3.dtypes
# check for column names
df3.dtypes
# check for datatypes of df4
df4.dtypes
# check for column names
df4.columns
#check for datatypes
df5.dtypes
# check column names
df5.columns
# check for column names
df6.dtypes
#check for column names
df6.columns<jupyter_output><empty_output><jupyter_text>Check for missing values and handling null values<jupyter_code># check for null values in dataset df1
df.isnull().sum().sort_values(ascending=False)

df.isnull().sum().sort_values(ascending=False)
# check for null values in  dataset df2
df2.isnull().sum().sort_values(ascending=False)
# check for null values in dataset df3
df3.isnull().sum().sort_values(ascending=False)
# check for null values in dataset df4
df4.isnull().sum().sort_values(ascending=False)
#Check for null values in dataset df5
df5.isnull().sum().sort_values(ascending=False)
# check for null values in dataset df6
df6.isnull().sum().sort_values(ascending=False)<jupyter_output><empty_output><jupyter_text>Checking for duplicates<jupyter_code># checking for duplicate values in dataset df
df.duplicated().value_counts()




# checking for duplicate values in dataset df2
df2.duplicated().value_counts()
# checking for duplicate values in dataset df3
df3.duplicated().value_counts()
# checking for duplicate values in dataset df4
df4.duplicated().value_counts()
# checking for duplicated values in dataset df5
df5.duplicated().value_counts()
# checking for duplicated values in dataset df6
df6.duplicated().value_counts()<jupyter_output><empty_output><jupyter_text>Dropping Duplicated Values<jupyter_code># Get rid off duplicated values by dropping
df4.drop_duplicates(inplace=True)
df5.drop_duplicates(inplace=True)
df6.drop_duplicates(inplace=True)<jupyter_output><empty_output><jupyter_text>*Concatenating the three datasets*Join Telcom dataset1 , Telecom dataset2 and Telecom dataset3 using concatenation<jupyter_code># join the three tables
join=pd.concat( [df, df2, df3] , ignore_index=1)
join
# preview the first five records in the joined table
join.head()
#preview the last five records in the joined table
join.tail()<jupyter_output><empty_output><jupyter_text>*Join the remaining datasets*<jupyter_code>#join the remaining datasets
#check the sample
join_all= pd.concat([df4,df5,df6], axis=0, join='outer',ignore_index=True)
join_all.head()





# preview the tail values
join_all.tail()
# change the column names to lower case
#preview the table
join_all.columns = map(str.lower, join_all.columns)
join_all.columns
# Check shape of df4,df5,df6 which are joined together
join_all.shape<jupyter_output><empty_output><jupyter_text>*Drop columns with Null values*<jupyter_code># Drop columns with null values
drop = ['column name' , 'description' , 'format' ]
join_all.drop(drop, axis=1, inplace=True)
join_all<jupyter_output><empty_output><jupyter_text>*Drop all rows that contains null values*<jupyter_code># Drop all rows that have null values
join_all.drop(join_all.tail().index,inplace=True) 
join_all

# Drop the last row which contains null value
join_all.drop(join_all.tail(1).index,inplace=True) 
join_all

# Rename the columns
columns = ['villes' ,'status' ,	'localisation' , 'decoup_zone',	'zone_name','longitude',	'latitude',	'region', 'area' ,	'cell_id'	,'site_code']
join_all.columns=columns
join_all<jupyter_output><empty_output><jupyter_text>*Merge the DataFrames*<jupyter_code>merged_df = pd.merge(join, join_all, how='inner', left_on=['site_id'], right_on=['site_code'])
merged_df
merged_df.shape<jupyter_output><empty_output><jupyter_text>*Which Regions are having highest traffic*<jupyter_code>#Regions with highest traffic
merged_df['region'].value_counts()<jupyter_output><empty_output><jupyter_text>*which areas high traffic is rising from*<jupyter_code>#Areas with highest traffic
merged_df['area'].value_counts()<jupyter_output><empty_output><jupyter_text>which zones has highest traffic<jupyter_code>#which Regions have highest frequency of call
merged_df['zone_name'].value_counts()<jupyter_output><empty_output><jupyter_text>Traffics coming from Villes<jupyter_code>#villes with highest traffic
merged_df['villes'].value_counts()<jupyter_output><empty_output><jupyter_text>*Cost returns *<jupyter_code>#sum of all return  per region 
cost_region = merged_df.groupby(['region' ])['bill'].sum().reset_index()
cost_region
#sum of all returns from zone
cost_zone = merged_df.groupby(['zone_name'])['bill'].sum().reset_index()
cost_zone
# sum of returns from villes
cost_villes = merged_df.groupby(['villes'])['bill'].sum().reset_index()
cost_villes
# Total returns in Ivory cost
cost_all = merged_df.groupby(['product', 'area', 'region' ,'villes', 'decoup_zone' ,'zone_name' ])['bill'].sum().reset_index()
cost_all<jupyter_output><empty_output><jupyter_text>*Cost of initiating a connection*<jupyter_code># cost of initiating a connection
cost_connection = merged_df.groupby(['product' , 'caller_anonymized', 'recipient_anonymized' ])['bill'].sum().reset_index()
cost_connection<jupyter_output><empty_output><jupyter_text>**Visualization**At this juncture we visualize our data and plot important information so we can see or even visualize a different values that our data has and its behaviour. To achieve that, we are only going to consider the following features or attributes: Regions, product, bills, zone_name etcDensity estimation of values using distplotThe distplot() function combines the matplotlib hist function with the seaborn kdeplot() and rugplot() functions *what are products that are mostly used ?*<jupyter_code>#plot product parameters
product = sns.countplot(x="product", data=df, palette="husl" , hue = "product" )
#calculating voice, sms , data observations
print(df['product'].value_counts())
# Regions with the highest traffic
merged_df['region'].value_counts().plot(kind='barh',   figsize=(12,12))
# area with the highest traffic
merged_df['area'].value_counts().plot(kind='barh',   figsize=(12,12))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09349102904497546, 'fraction_numerical': 0.011103065411537534, 'mean_word_length': 6.337662337662338, 'pattern_counts': {'":': 0, '<': 89, '<?xml version=': 0, '>': 89, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '132694', 'license': 'MIT', 'n_tokens_mistral': 3904, 'n_tokens_neox': 3802, 'n_words': 1312, 'path': 'Moringa_Data_Science_Prep_W3_Independent_Project_2021_16_Dennis_Kiarie__Python_Data_Analysis.ipynb', 'repo_name': 'Dennis-Kiarie/MTN-Cote-d-Ivoire-go-about-the-upgrade-of-its-infrastructure-strategy', 'size': '12429'}