<jupyter_start><jupyter_text>User Analytics in the Telecommunication Industry   Import Libraries<jupyter_code>## Import Libraries
import sys
import os
sys.path.append(os.path.abspath(os.path.join('..')))
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np 
from pandas.api.types import is_string_dtype, is_numeric_dtype
%matplotlib inline<jupyter_output><empty_output><jupyter_text>Import Data<jupyter_code>CSV_PATH = "../data/raw/rawData.csv"
# taking a csv file path and reading a dataframe

def read_proccessed_data(csv_path):
    try:    
        df = pd.read_csv(csv_path)
        print("file read as csv")
        return df
    except FileNotFoundError:
        print("file not found")
## getting number of columns, row and column information
def get_data_info(xDR_df: pd.DataFrame):
    
    row_count, col_count = xDR_df.shape
    
    print(f"Number of rows: {row_count}")
    print(f"Number of columns: {col_count}")

    return xDR_df.info()
## basic statistics of each column and see the data at glance
def get_statistics_info(xDR_df: pd.DataFrame):
    
    return xDR_df.describe(include='all')
# reading the extracted tweeter data and getting information

xDR_df = read_proccessed_data(CSV_PATH)
get_data_info(xDR_df)
get_statistics_info(xDR_df)<jupyter_output>file read as csv
Number of rows: 150001
Number of columns: 55
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 150001 entries, 0 to 150000
Data columns (total 55 columns):
 #   Column                                    Non-Null Count   Dtype  
---  ------                                    --------------   -----  
 0   Bearer Id                                 149010 non-null  float64
 1   Start                                     150000 non-null  object 
 2   Start ms                                  150000 non-null  float64
 3   End                                       150000 non-null  object 
 4   End ms                                    150000 non-null  float64
 5   Dur. (ms)                                 150000 non-null  float64
 6   IMSI                                      149431 non-null  float64
 7   MSISDN/Number                             148935 non-null  float64
 8   IMEI                                      149429 non-null  float64
 9   Last Location Name            [...]<jupyter_text>Distinct value counts/frequencies of each column to determine if there are any columns with only a single value/all different values<jupyter_code>pd.DataFrame(xDR_df.apply(lambda x: len(x.value_counts(dropna=False)), axis=0), 
columns=['Unique Value Count']).sort_values(by='Unique Value Count', ascending=True)
sns.set(rc={'figure.figsize':(15.7,8.27)})
sns.heatmap(xDR_df.isnull(),yticklabels=False,cbar=False,cmap='viridis')
def percent_missing(df):

    totalCells = np.product(df.shape)
    missingCount = df.isnull().sum()
    totalMissing = missingCount.sum()
    return round((totalMissing / totalCells) * 100, 2)

print("The Telco Telecom dataset contains", percent_missing(xDR_df), "%", "missing values.")
def percent_missing_for_col(df, col_name: str):
    total_count = len(df[col_name])
    if total_count <= 0:
        return 0.0
    missing_count = df[col_name].isnull().sum()
    
    return round((missing_count / total_count) * 100, 2)
null_percent_df = pd.DataFrame(columns = ['column', 'null_percent'])
columns = xDR_df.columns.values.tolist()
null_percent_df['column'] = columns
null_percent_df['null_percent'] = null_percent_df['column'].map(lambda x: percent_missing_for_col(xDR_df, x))
null_percent_df.sort_values(by=['null_percent'], ascending = False)<jupyter_output><empty_output><jupyter_text>Roughly for 10 data sets column morthan 50 percent fo the data is missing, it looks like we are just missing too much of the data to do something useful with at a basic level so it is better to drop thos columns. For the rest of the data the proportion of missing data is likely small enough for reasonable replacement with some form of imputation.<jupyter_code>#Drop columns with morethan 50% missing values
def drop_columns(xDR_df, columns=[]):
    return xDR_df.drop(columns, axis=1)
columns_to_be_dropped = null_percent_df[null_percent_df['null_percent'] > 50]['column'].to_list()
xDR_df = drop_columns(xDR_df, columns_to_be_dropped)
def percent_missing(df):

    totalCells = np.product(df.shape)
    missingCount = df.isnull().sum()
    totalMissing = missingCount.sum()
    return round((totalMissing / totalCells) * 100, 2)
print("The Telco Telecom dataset contains", percent_missing(xDR_df), "%", "missing values.")<jupyter_output>The Telco Telecom dataset contains 1.02 % missing values.<jupyter_text>Now let us deal with this missing values<jupyter_code>missing_count=xDR_df.isnull().sum() #the count of missing values
value_count=xDR_df.isnull().count() #the count of all values
missing_percentage=round(missing_count/value_count*100,2) #the percentage of missing values
missing_df=pd.DataFrame({'count':missing_count,'percentage':missing_percentage}) #create a datafreame
missing_df_for_plot=missing_df[missing_df.percentage!=0.00]
font = {'family' : 'normal',
        'weight' : 'bold',
        'size'   : 22}

plt.rc('font', **font)
barchart=missing_df_for_plot.plot.bar(y='percentage',figsize=(10,8))
for index, percentage in enumerate(missing_percentage):
    if percentage >0.00:
        barchart.text(index, percentage,"")<jupyter_output>findfont: Font family ['normal'] not found. Falling back to DejaVu Sans.<jupyter_text>For Bearer Id, IMSI, MSISDN, IMEI,Handset Manufacturer,Handset Type  I am going to use forward fill because it is the better way to fill unique columns. Beacuse I think it will have fair distribution of values<jupyter_code>xDR_df['Bearer Id'] = xDR_df['Bearer Id'].fillna(method='ffill')
xDR_df['IMSI'] = xDR_df['IMSI'].fillna(method='ffill')
xDR_df['MSISDN/Number'] = xDR_df['MSISDN/Number'].fillna(method='ffill')
xDR_df['IMEI'] = xDR_df['IMEI'].fillna(method='ffill')
xDR_df['Handset Manufacturer'] = xDR_df['Handset Manufacturer'].fillna(method='ffill')
xDR_df['Handset Type'] = xDR_df['Handset Type'].fillna(method='ffill')
# fill 'Last Location Name' column with unknown 
xDR_df['Last Location Name'] = xDR_df['Last Location Name'].fillna('Uknown')<jupyter_output><empty_output><jupyter_text>For the rest of the columns I am going to use mean to feel the missing values because it is simple.<jupyter_code>#numerical data
xDR_df.fillna(xDR_df.mean(), inplace=True)
def percent_missing(df):

    totalCells = np.product(df.shape)
    missingCount = df.isnull().sum()
    totalMissing = missingCount.sum()
    return round((totalMissing / totalCells) * 100, 2)
print("The Telco Telecom dataset contains", percent_missing(xDR_df), "%", "missing values.")
xDR_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 150001 entries, 0 to 150000
Data columns (total 45 columns):
 #   Column                          Non-Null Count   Dtype  
---  ------                          --------------   -----  
 0   Bearer Id                       150001 non-null  float64
 1   Start                           150000 non-null  object 
 2   Start ms                        150001 non-null  float64
 3   End                             150000 non-null  object 
 4   End ms                          150001 non-null  float64
 5   Dur. (ms)                       150001 non-null  float64
 6   IMSI                            150001 non-null  float64
 7   MSISDN/Number                   150001 non-null  float64
 8   IMEI                            150001 non-null  float64
 9   Last Location Name              150001 non-null  object 
 10  Avg RTT DL (ms)                 150001 non-null  float64
 11  Avg RTT UL (ms)                 150001 non-null  float64
 12  Avg Bearer TP DL[...]<jupyter_text>Remove Duplicate rows<jupyter_code>xDR_df.drop_duplicates(inplace=True)
xDR_df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 150001 entries, 0 to 150000
Data columns (total 45 columns):
 #   Column                          Non-Null Count   Dtype  
---  ------                          --------------   -----  
 0   Bearer Id                       150001 non-null  float64
 1   Start                           150000 non-null  object 
 2   Start ms                        150001 non-null  float64
 3   End                             150000 non-null  object 
 4   End ms                          150001 non-null  float64
 5   Dur. (ms)                       150001 non-null  float64
 6   IMSI                            150001 non-null  float64
 7   MSISDN/Number                   150001 non-null  float64
 8   IMEI                            150001 non-null  float64
 9   Last Location Name              150001 non-null  object 
 10  Avg RTT DL (ms)                 150001 non-null  float64
 11  Avg RTT UL (ms)                 150001 non-null  float64
 12  Avg Bearer TP DL[...]<jupyter_text>Save the processed dataframe to CSV<jupyter_code>xDR_df.to_csv("../data/processed/processed.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1005302695536898, 'fraction_numerical': 0.04396818382677861, 'mean_word_length': 2.6548243843358903, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 19, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '510559', 'license': 'MIT', 'n_tokens_mistral': 3104, 'n_tokens_neox': 2760, 'n_words': 757, 'path': 'notebooks/dataPreProcessing.ipynb', 'repo_name': 'bereketkibru/User_Analytics_in_telecom', 'size': '9052'}