<jupyter_start><jupyter_text><jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>IMPORTING DATASETS AND CREATING DATAFRAMES<jupyter_code>from google.colab import files
uploaded = files.upload()
import io
df3 = pd.read_csv(io.BytesIO(uploaded['Telcom_dataset3.csv']))
df3
import io
df2 = pd.read_csv(io.BytesIO(uploaded['Telcom_dataset2.csv']))
df2
import io
df1 = pd.read_csv(io.BytesIO(uploaded['Telcom_dataset.csv']))
df1
import io
df4 = pd.read_excel(io.BytesIO(uploaded['CDR_description.xlsx']))
df4
import io
df5 = pd.read_csv(io.BytesIO(uploaded['cells_geo.csv']))
df5
import io
df6 = pd.read_excel(io.BytesIO(uploaded['cells_geo_description.xlsx']))
df6
# Organising and sorting cells_geo dataset
df5 = pd.read_csv("cells_geo.csv", header = 0, index_col = 0, delimiter = ";")
df5<jupyter_output><empty_output><jupyter_text>Dropping duplicates<jupyter_code>df1.drop_duplicates()
df2.drop_duplicates()
df3.drop_duplicates()
df5.drop_duplicates()<jupyter_output><empty_output><jupyter_text>MERGING df1 and df5 on cellid column<jupyter_code>df_merged = df1.merge(df5, how='outer', on=["CELL_ID"], left_index=True, right_index=True)
df_merged


# Eliminating rows with more than 2 missing values
df_merged = df_merged[df_merged.isnull().sum(axis=1) < 2]
df_merged
# Checking the cities with the most usage according to the bill charges
df_xx = df_merged.sort_values(by = 'VALUE', ascending = False)
df_xx
df_aa = df_xx[['VILLES','VALUE']] [(df_xx['VALUE'] > 250)]
df_aa<jupyter_output><empty_output><jupyter_text>MERGING df2 and df5 on cell id column<jupyter_code>df_merged2 = df2.merge(df5, how='outer', on=["CELL_ID"], left_index=True, right_index=True)
df_merged2

# Eliminating rows with more than 2 missing values
df_merged2 = df_merged2[df_merged2.isnull().sum(axis=1) < 2]
df_merged2
# Checking the cities with the most usage according to the bill charges
df_yy = df_merged2.sort_values(by = 'VALUE', ascending = False)
df_yy
df_bb = df_yy[['VILLES','VALUE']] [(df_yy['VALUE'] > 250)]
df_bb<jupyter_output><empty_output><jupyter_text>MERGING df3 and df5 on cell id column<jupyter_code># Rename unmatching columns in df3 to correct format
df13 = df3.rename({'SIET_ID':'SITE_ID', 'CELLID':'CELL_ID'}, axis=1)
df13
# merging the 2 datasets
df_merged3 = df13.merge(df5, how='outer', on=["CELL_ID"], left_index=True, right_index=True)
df_merged3

# Eliminating rows with more than 2 missing values
df_merged3 = df_merged3[df_merged3.isnull().sum(axis=1) < 2]
df_merged3
# Checking the cities with the most usage according to the bill charges
df_zz = df_merged3.sort_values(by = 'VALUE', ascending = False)
df_zz
df_cc = df_zz[['VILLES','VALUE']] [(df_zz['VALUE'] > 250)]
df_cc<jupyter_output><empty_output><jupyter_text>Combining the 3 data frames with cities with the most usage and ordering them according to billing charge descending order<jupyter_code>import pandas as pd
from functools import reduce
data_frames = [df_aa, df_bb, df_cc]
df_mergedxyz = reduce(lambda  left,right: pd.merge(left,right,on=['VILLES'],
                                            how='outer'), data_frames)
df_mergedxyz = df_mergedxyz[df_mergedxyz.isnull().sum(axis=1) < 2]
df_mergedxyz
df99 = df_mergedxyz.groupby(['VILLES']).agg('sum')
df99
df98 = df99.sort_values(by = 'VALUE', ascending = False)
df98<jupyter_output><empty_output><jupyter_text>MOST USED CITIES FOR THE 3 DAYS<jupyter_code>df98.head(5)<jupyter_output><empty_output><jupyter_text>MOST USED CITY<jupyter_code>df98.head(1)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12521246458923513, 'fraction_numerical': 0.026912181303116147, 'mean_word_length': 7.269320843091335, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 40, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '444526', 'license': 'Unlicense', 'n_tokens_mistral': 1425, 'n_tokens_neox': 1327, 'n_words': 278, 'path': 'John_Njagi_MTN_notebook.ipynb', 'repo_name': 'BrianGakungi/MTN-Ivory-Coast-Upgrade-analysis', 'size': '3530'}