# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     name: python3
# ---

# + [markdown] id="view-in-github" colab_type="text"
# <a href="https://colab.research.google.com/github/Raphael-Blaize/python-project-for-MTN-Cote-d-Ivoire--to-help-in-upgrade-of-its-infrastructure-strategy/blob/main/Raph's_MTN_Cote_d'Ivoire_cleaning_and_analysis.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>

# + [markdown] id="9gXfuwwD2dhE"
# MTN Cote d'Ivoire would like to upgrade its technology infrastructure for its mobile users in Ivory Coast. Studying the given dataset, how does MTN Cote d'Ivoire go about the upgrade of its infrastructure strategy within the given cities?
#
#
#
#

# + [markdown] id="qbM-FwmA-16g"
# # IMPORTING THE DATASETS INTO THE ENVIRONMENT 

# + colab={"base_uri": "https://localhost:8080/", "height": 532} id="P5zPZoRQ2jMz" outputId="882c7eb8-79ce-49e8-c5d3-7fc7d811441e"
import pandas as pd
import numpy as np


#Importing my cells_geo dataset, seprating by (;) with index_col set to zero in-order to pick the column names as they
#are in the original dataset
geo= pd.read_csv("cells_geo.csv", delimiter=";", index_col=0)
geo

#choosing 10 values
geo.head(10)

# + colab={"base_uri": "https://localhost:8080/", "height": 719} id="XVdlcU8yBcWT" outputId="c0a45cec-2eff-4da2-93ae-bd9d7efe4dc8"
#Importing my Telcom 1 Dataset
Tel1= pd.read_csv("Telcom_dataset.csv")
Tel1

#choosing the first 10 
Tel1.head(10)

# + colab={"base_uri": "https://localhost:8080/", "height": 719} id="weZ6GnNpAjYT" outputId="1aacbd25-23fc-4b09-d31d-e7729bb05708"
#Importing my Telcom 2 Dataset 
Tel2= pd.read_csv("Telcom_dataset2.csv")
Tel2

#choosing the first 10 
Tel1.head(10)

# + colab={"base_uri": "https://localhost:8080/", "height": 719} id="OPW5bzTFAs_S" outputId="10073270-8af4-4823-a7b9-69eadcfc50aa"
#Importing my Telcom 3 Dataset
Tel3= pd.read_csv("Telcom_dataset3.csv")
Tel3

#choosing the first 10 
Tel1.head(10)

# + [markdown] id="MwEjpRFt-cqd"
# # **INFORMATION SECTION ON THE DATASETS**

# + colab={"base_uri": "https://localhost:8080/"} id="kHLvwP4YGjxR" outputId="a30ad711-b3db-48dc-ec27-bb1131f765f7"
#Cheking the information on the datasest
# Getting to understand the Data types and the entries of the datasets 
geo.info()

print("***************************************************************")
Tel1.info()

print("***************************************************************")
Tel2.info()

print("***************************************************************")
Tel3.info()

# + [markdown] id="stP0WEgB-tbm"
# # **CHECKING THE NULL VALUES IN THE DATASETS**

# + colab={"base_uri": "https://localhost:8080/"} id="lV74RnriHUnJ" outputId="2ebff3ee-b3e3-4a01-fa22-666b8c1eb2bb"
#checking if my cells geo dataset has any null values
geo.isnull().values.any()

# + colab={"base_uri": "https://localhost:8080/"} id="UCJ-iCMABhE-" outputId="97449ced-25f4-4bd2-b8cb-b7bb52030691"
#checking how many missing values are in my cell_geo dataset in the columns 
geo.isnull().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="xaarlmUh_jF2" outputId="d8eb7fee-2714-4ae6-e6f1-6266537542a6"
#cheking null values in my telcom 1 dataset 
Tel1.isnull().values.any()

# + colab={"base_uri": "https://localhost:8080/"} id="5FGOp8iHB3-S" outputId="bcc911d5-219e-4fb4-92d2-84dc391f9ecc"
#checking how many missing values are in my telcome1 dataset 
Tel1.isnull().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="vGKMNhD3CEMV" outputId="732195ed-4e5f-4593-dae4-fc5e042dcb28"
#cheking null values in my telcom21 dataset 
Tel2.isnull().values.any()

# + colab={"base_uri": "https://localhost:8080/"} id="Pe6QgHIPCEmL" outputId="dd9b2b71-b802-40cf-9f84-d3b0a63520b9"
#checking how many missing values are in my telcome 2 dataset 
Tel2.isnull().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="lqUOFYdKCE37" outputId="14deb8fc-c13f-459c-c0c1-33dde3978e7c"
#checking how many missing values are in my telcome 3 dataset 
Tel3.isnull().values.any()

# + colab={"base_uri": "https://localhost:8080/"} id="9MLKgr4KCFFK" outputId="9fe9bb69-8d75-4472-f77e-c21c03f2eddf"
#checking how many missing values are in my telcome 3 dataset 
Tel3.isnull().sum()

# + [markdown] id="4ovHIf8bNY2y"
# # **CLEANING MY DATA**

# + [markdown] id="-pBINrnF-CLn"
#  1 .**Taking care of missing data**

# + colab={"base_uri": "https://localhost:8080/", "height": 694} id="dds2rfPjWb3X" outputId="b97b0992-15a7-4b0d-bfe9-20f0d89d3870"
# taking care of missing data in my cells_geo dataset by replacing the nan with .
geo2 = geo.replace(np.nan, ".", regex = True)
geo2

# + colab={"base_uri": "https://localhost:8080/"} id="mGf88KEF3FGf" outputId="78ad6276-4397-4333-a80d-2241e6e966ba"
#checking if their any missing value in my geo2 dataset
geo2.isnull().sum()

# + colab={"base_uri": "https://localhost:8080/", "height": 779} id="kzDsEjnKYxaH" outputId="3fb52040-e295-4d74-e07a-d6578deabb6c"
#Taking care of missing data in my Telcom 1 Dataset 
Tel1up = Tel1.replace(np.nan, ".", regex=True)
Tel1up

# + colab={"base_uri": "https://localhost:8080/"} id="nDu9TDS83wTD" outputId="372af9b6-ddf0-4d5b-9ade-29b09293e087"
#checking if their any missing value in my Tel1up dataset
Tel1up.isnull().sum()

# + colab={"base_uri": "https://localhost:8080/", "height": 394} id="8amsM9-5YxpG" outputId="875ffe97-22e8-4187-a9ff-8928def8307c"
#Taking care of missing data in my Telcom 2 Dataset 
Tel2up = Tel2.replace(np.nan, ".", regex=True)
Tel2up.head()

# + colab={"base_uri": "https://localhost:8080/"} id="GyrYZwlu32-Y" outputId="5108f2ec-2736-4ee6-f2b3-51172ac8711d"
#checking if their any missing value in my Tel2up dataset
Tel2up.isnull().sum()

# + colab={"base_uri": "https://localhost:8080/", "height": 779} id="7mX8gGkXYx6G" outputId="6ff24243-3694-4e52-89a8-26a8a3897d4f"
#Taking care of missing data in my Telcom 3 Dataset 
Tel3up = Tel3.replace(np.nan, ".", regex=True)
Tel3up

# + colab={"base_uri": "https://localhost:8080/"} id="mUvhW2nh36fe" outputId="3f7e89c7-7e68-45b9-e7a6-1080fdab1603"
#checking if their any missing value in my Tel3up dataset
Tel3up.isnull().sum()

# + [markdown] id="F7v8edL7-STz"
# 2. **Taking care of duplicate values**

# + colab={"base_uri": "https://localhost:8080/"} id="87znnG0kZ8s8" outputId="09fc4671-c59f-42bd-b86e-0b04125fdfba"
#cheking for duplicate values in my cell_geo dataset 
geo2.duplicated().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="LRxk5M8l4Y4i" outputId="2d65297e-650b-4250-d09a-1d27c31d6c35"
#dropping the duplicates in my geo2 dataset and cheking if there any duplicates left 
geo3 = geo2.drop_duplicates()
geo3.duplicated().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="zPag2cJIxWXI" outputId="e064438d-5b03-42a2-b988-ec632c78167a"
#cheking for duplicate values in my Tel1 dataset 
#dropping the duplicates
Tel1up.duplicated().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="GNdGnqtR8gI2" outputId="2602dac7-7c69-418a-e80c-cee678f256d5"
#dropping the duplicates in my tel1up dataset and cheking if there any duplicates left 
Tel1upd = Tel1up.drop_duplicates()
Tel1upd.duplicated().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="vFFB_bDyxWfY" outputId="75ceb966-62e8-4f78-fecd-427a2cf25175"
#cheking for duplicate values in my Tel2 dataset 
Tel2up.duplicated().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="tJRk3fLL8xhX" outputId="eb35e31a-2cc9-4993-b7db-713e897d80b9"
#dropping the duplicates in my Tel2up  dataset and cheking if there any duplicates left 
Tel2upd = Tel2up.drop_duplicates()
Tel2upd.duplicated().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="3Hrz3BTrxWrD" outputId="94fcef1e-bf45-4a21-f2b3-311f1ef63f9f"
#cheking for duplicate values in my Tel3 dataset 
#dropping the duplicates
Tel3up.duplicated().sum()

# + colab={"base_uri": "https://localhost:8080/"} id="QZ2Y8NMq893t" outputId="c1770108-1e00-48dd-94a3-bf75dceee3be"
#dropping the duplicates in my Tel3up dataset and cheking if there any duplicates left 
Tel3upd = Tel3up.drop_duplicates()
Tel3upd.duplicated().sum()

# + [markdown] id="6DYvQrLI_Fzs"
# 3. **Dropping unnecessary column in the dataset**
#

# + colab={"base_uri": "https://localhost:8080/"} id="nhsFpvle_QJl" outputId="176b51f8-c119-489e-fd01-382eb4be3a03"
#cheking the columns in my geo3 dataset 
geo3.head()

# Columns such as Site_code, latitude, longitude will not help in seeing where the cells were most used in the cities we can go ahead 
#and drop these column 
geo3.drop(columns=["LONGITUDE", "LATITUDE", "CELL_ID"], axis=1, inplace=True)

# + colab={"base_uri": "https://localhost:8080/", "height": 419} id="EljRFflQAX9H" outputId="4f736b68-3e82-4c0f-a86a-d337ac4cdd1a"
#Cheking to see whether the columns have been deleted
geo3

#Renaming my site_code column to match all the other datasets 
geo4 = geo3.rename(columns={'SITE_CODE': 'SITE_ID'})
geo4                 

# + [markdown] id="1POcgkJDL7CU"
#   b) **. Dropping in Telcome datasets**

# + colab={"base_uri": "https://localhost:8080/"} id="jrlAnCw6Hv9G" outputId="703253d0-c71f-4b95-d9eb-d1f034f94bf6"
#cheking the columns in my tel1 dataset 
Tel1upd.head()

#Columns such as DW_A_NUMBER_INT,	DW_B_NUMBER_INT,	COUNTRY_A,	COUNTRY_B,	CELL_ID,	SITE_ID are not needed to find out which cells were most used in the cities
Tel1upd.drop(columns= ["DW_A_NUMBER_INT",	"DW_B_NUMBER_INT",	"COUNTRY_A",	"COUNTRY_B", "CELL_ID"], axis= 1, inplace= True)

# + colab={"base_uri": "https://localhost:8080/", "height": 419} id="ACJFbV8wJoew" outputId="8196f18e-5863-4653-94e6-8571c5ca7448"
#Cheking to see whether the columns have been deleted
Tel1upd
#renaming the cell id column to match the other telecome datasets 
Tel1upda = Tel1upd.rename(columns={'PRODUTC': 'PRODUCT'})
Tel1upda     

# + colab={"base_uri": "https://localhost:8080/"} id="Sh_NxoxGKHwr" outputId="58ad2b9f-d86c-4ffd-e0db-2c774f58b50e"
#cheking the columns in my tel1 dataset 
Tel2upd.head()

#Columns such as DW_A_NUMBER,	DW_B_NUMBER,	COUNTRY_A,	COUNTRY_B,	CELL_ID,	SITE_ID are not needed to find out which cells were most used in the cities
Tel2upd.drop(columns= ["DW_A_NUMBER",	"DW_B_NUMBER",	"COUNTRY_A",	"COUNTRY_B", "CELL_ID"], axis= 1, inplace= True)

# + colab={"base_uri": "https://localhost:8080/", "height": 419} id="2wS8Npf0KeqT" outputId="bfbfa8f6-fcdf-49d6-e330-2c5483fe06da"
#Cheking to see whether the columns have been deleted
Tel2upd

# + colab={"base_uri": "https://localhost:8080/"} id="MBNPRfz_Kx9C" outputId="222e3bb3-90d0-452f-9400-0f16872e884a"
#cheking the columns in my tel1 dataset 
Tel3upd.head()

#Columns such as DW_A_NUMBER,	DW_B_NUMBER,	COUNTRY_A,	COUNTRY_B,	CELL_ID,	SITE_ID are not needed to find out which cells were most used in the cities
Tel3upd.drop(columns= ["DW_A_NUMBER_INT",	"DW_B_NUMBER_INT",	"COUNTRY_A",	"COUNTRY_B", "CELLID"], axis= 1, inplace= True)

# + colab={"base_uri": "https://localhost:8080/", "height": 419} id="WeuPDpfgLAk0" outputId="622252e1-92d0-4619-cf6e-b6a88f3dc527"
#Cheking to see whether the columns have been deleted
Tel3upd
#renaming the cell id column to match the other telecome datasets 
Tel3upda = Tel3upd.rename(columns={'SIET_ID': 'SITE_ID'})
Tel3upda                  

# + [markdown] id="0E7nkkDyQr-0"
# # **MERGING DATASETS**

# + colab={"base_uri": "https://localhost:8080/", "height": 745} id="Zq5rdOcRQ0Ax" outputId="c42d51a8-2a62-4fd0-d0b0-00e7bec7d46a"
#Merging Datasests Together 
# Merging my geo_cells dataset to my tel1 dataset with the column SITE_ID as the common column 
merge1 = geo4.merge(Tel1upda, how="left", on = 'SITE_ID')
#Merging my merge to the Telcome 2 dataset with the same common column
merge2 = merge1.merge(Tel2upd, how="left", on = 'SITE_ID')
#Merging my merge2 to the Telcome 3 dataset with the same common column
MTN = merge2.merge(Tel3upda, how="left", on="SITE_ID")
MTN

# + [markdown] id="WQMYEVHzvM6u"
# a) **Taking care of unnecessary columns, missing data and duplicate values**

# + colab={"base_uri": "https://localhost:8080/", "height": 745} id="4EwyBUNgh3BI" outputId="852b3d33-54a6-4fe6-821a-389d1c7a368d"
#Dropping columns that are not needed
MTN.drop(columns=["SITE_ID", "CELL_ON_SITE_y", "PRODUCT_x", "VALUE_x", 'CELL_ON_SITE_x',	'PRODUCT_y',	'VALUE_y',	'DATE_TIME_x', 'DATE_TIME_y'], axis=0, inplace=True)
MTN

# + id="BZoOaJlzrQp3"
#Cheking the total of rows with missing data
MTN.isnull().sum()
#dropping rows that contain missing data
mtnf= MTN.dropna()

# + colab={"base_uri": "https://localhost:8080/", "height": 289} id="H2XzBheWuVDK" outputId="ca6b43d5-40ad-4215-cfdc-038d6cf49eb6"
# Taking care of duplicate values in the dataset
mtnf.duplicated().sum()
MTNfinal = mtnf.drop_duplicates()
MTNfinal.head()

# + colab={"base_uri": "https://localhost:8080/"} id="tn9uxxwwvtBl" outputId="ee6f9540-24be-4f5a-dab7-410842e2bc1b"
#Looking at the information of my final dataset 
MTNfinal.info()

# + [markdown] id="VnfFqY3HoB8N"
# # EXPORTING TO CSV
# ### Exporting to csv to do the visualisation on tableau
#

# + id="Xn57ndvboBBh"
MTNfinal.to_csv("MTNfinal.csv")

# + [markdown] id="xikT4faUzSKR"
# # **DERIVING NEW ATTRIBUTES/DATA ANALYSIS**
#
#
#
# 1. Which Cities had the highest billing?
# 2. The city with the most cells?
# 2. Which cities were the most used during business and home hours?
# 3. Most used city for the three days?
# 4. Which product is the most used in the cities
# 5. Which city had the most value in product
# 6. Which city had the most cells on site?
#
#
#
#
#

# + colab={"base_uri": "https://localhost:8080/", "height": 450} id="YDOlo29Y8rE-" outputId="4d0a5782-19ff-4616-bdad-bdd9e2b9bcdc"
#Descriptive statistics between our city and Values column 
MTNfinal['VALUE'].groupby(MTNfinal['VILLES']).describe()

# + colab={"base_uri": "https://localhost:8080/", "height": 450} id="PEw7lqYwsJhN" outputId="932e6613-00a5-4880-cef5-1c9772563fa4"
#Descriptive statistics between our city and product column 
MTNfinal['PRODUCT'].groupby(MTNfinal['VILLES']).describe()

# + colab={"base_uri": "https://localhost:8080/"} id="uX_TeioHzYEi" outputId="e5f49dca-15e5-437f-e110-cdaf5765bdb3"
#Cheking which cities was used the most using the Value column
#in description the value column is the billing price so the city with the highest billing price probably had the most users
Cityv = MTNfinal['VILLES'].groupby(MTNfinal['VALUE'])
MTNfinal

# Displaying the max value of the each city
Cityv.max().tail()

# + colab={"base_uri": "https://localhost:8080/"} id="7MtN0AVG5tax" outputId="2c60437d-82bf-411a-f58e-33c9b5730d5e"
# Finding which cities were the most used during business and home hours 
cityh = MTNfinal["VILLES"].groupby(MTNfinal["DATETIME"])
MTNfinal

#Displaying the most used city during the different hours
cityh.max().tail()

# + colab={"base_uri": "https://localhost:8080/"} id="jK6AT0xl9pwp" outputId="66bdac64-2652-4a08-a0a5-1cece504baf8"
#Finding the city with the least cells 
cityp = MTNfinal["VILLES"].groupby(MTNfinal["CELL_ON_SITE"])
MTNfinal

#Displaying the city with least cells
cityp.min().head()

# + colab={"base_uri": "https://localhost:8080/"} id="9dSUqgi-A3d3" outputId="675b4df4-c8cd-4f23-b880-ab16b57fbe4a"
#Stats to find the most used city in the three days
freq = MTNfinal["VILLES"].value_counts() 
print(freq)

# + colab={"base_uri": "https://localhost:8080/", "height": 142} id="iOsKlvquEpe6" outputId="c3e939aa-f64f-4ba6-eca4-479a033712e2"
#Which product is the most used in the cities
MTNfinal['VILLES'].groupby(MTNfinal['PRODUCT']).describe()

# + colab={"base_uri": "https://localhost:8080/"} id="wtwVSaQHY6sa" outputId="27ea99fe-51e1-47cb-c9ca-f0d649b9e9af"
freq = MTNfinal["PRODUCT"].value_counts() 
print(freq)

StarcoderdataJupyterScriptsDedupFiltered