<jupyter_start><jupyter_text>Course Project  Project description: You work as an analyst for the telecom operator Megaline. The companyoffers its clients two prepaid plans, Surf and Ultimate. The commercialdepartment wants to know which of the plans is more profitable in order toadjust the advertising budget.  Open, prepare the data files and study the general information<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from scipy import stats as st

megaline_calls = pd.read_csv('/datasets/megaline_calls.csv')
print(megaline_calls.head())
print(megaline_calls.tail())
print(megaline_calls.info())
print(megaline_calls.describe())
dup = megaline_calls.duplicated().sum()
print(dup) # there is no duplicates
megaline_calls['call_date'] = pd.to_datetime(megaline_calls['call_date'])
megaline_calls['duration'] = megaline_calls['duration'].apply(np.ceil) # round sec up to min
print(megaline_calls.info())
megaline_calls
display(megaline_calls.head())
megaline_internet = pd.read_csv('/datasets/megaline_internet.csv')
print(megaline_internet.head())
print(megaline_internet.tail())
print(megaline_internet.info())
print(megaline_internet.describe())
dup = megaline_internet.duplicated().sum()
print(dup)  # there is no duplicates

megaline_internet['session_date'] = pd.to_datetime(megaline_internet['session_date'])

print(megaline_internet.info())
megaline_messages = pd.read_csv('/datasets/megaline_messages.csv')
print(megaline_messages.head())
print(megaline_messages.tail())
print(megaline_messages.info())
print(megaline_messages.describe())
dup = megaline_messages.duplicated().sum()
print(dup)
megaline_messages['message_date'] = pd.to_datetime(megaline_messages['message_date'])
print(megaline_messages.info())
megaline_plans = pd.read_csv('/datasets/megaline_plans.csv')
print(megaline_plans.head())
print(megaline_plans.tail())
print(megaline_plans.info())
print(megaline_plans.describe())
dup = megaline_plans.duplicated().sum()
print(dup) # there is no duplicates

megaline_users = pd.read_csv('/datasets/megaline_users1.csv')
print(megaline_users.head())
print(megaline_users.tail())
print(megaline_users.info())
print(megaline_users.describe())
dup = megaline_users.duplicated().sum()
print(dup) 
print(megaline_users[['first_name','last_name','age']].duplicated().sum())# there is no duplicates<jupyter_output>user_id first_name  last_name  age                                   city  \
0     1000   Anamaria      Bauer   45  Atlanta-Sandy Springs-Roswell, GA MSA   
1     1001     Mickey  Wilkerson   28        Seattle-Tacoma-Bellevue, WA MSA   
2     1002     Carlee    Hoffman   36   Las Vegas-Henderson-Paradise, NV MSA   
3     1003   Reynaldo    Jenkins   52                          Tulsa, OK MSA   
4     1004    Leonila   Thompson   40        Seattle-Tacoma-Bellevue, WA MSA   

     reg_date    tariff churn_date  
0  2018-12-24  ultimate        NaN  
1  2018-08-13      surf        NaN  
2  2018-10-21      surf        NaN  
3  2018-01-28      surf        NaN  
4  2018-05-23      surf        NaN  
     user_id first_name   last_name  age  \
495     1495      Fidel      Sharpe   67   
496     1496      Ariel    Shepherd   49   
497     1497      Donte     Barrera   49   
498     1498       Scot  Williamson   51   
499     1499      Shena     Dickson   37   

                                [...]<jupyter_text>After basic examination of data no missing values were found, date values were converted from object to datetime type, according to Megaline charging plan seconds of calls were rounded up for appropriate further calculation.In order to check if same user regestered more than once by mistake and may has more then one plan I checked registered megaline users table for duplicates only by first name, last name and age, no duplicates were found. No issues that preventing further analysis were detected.  Analyze the data<jupyter_code>megaline_calls['month'] = megaline_calls['call_date'].dt.month

#The number of calls made and minutes used per month:

total_user_calls = megaline_calls.groupby(['user_id', 'month'])['duration'].sum().reset_index()

total_user_calls.rename(columns={'duration': 'minutes_used'}, inplace=True)

total_user_calls1 = megaline_calls.groupby(['user_id', 'month'])['duration'].count().reset_index()

total_user_calls['calls_made'] = total_user_calls1['duration']

print(total_user_calls)

megaline_internet['month'] = megaline_internet['session_date'].dt.month

#The volume of data per month:

total_internet_use = megaline_internet.groupby(['user_id', 'month'])['mb_used'].sum().reset_index()

print(total_internet_use.sort_values('mb_used'))

total_internet_use.rename(columns={'mb_used': 'Gb_used'}, inplace=True) # I renamed the column name 

total_internet_use['Gb_used'] = total_internet_use['Gb_used'].apply(lambda x: int(np.ceil(x / 1024.0))) # Rounded up
      
print(total_internet_use.sort_values('Gb_used'))        

megaline_messages['month'] = megaline_messages['message_date'].dt.month

#The number of text messages sent per month:

total_user_messages = megaline_messages.pivot_table(index = ['user_id', 'month'], values = 'id', aggfunc='count')

total_user_messages = total_user_messages.rename(columns={'id': 'messages_per_user'})

print(total_user_messages)

total1= pd.merge(left = total_user_calls, 
                 right = total_internet_use, how='outer',on=['user_id', 'month']).fillna(0)

users_total=pd.merge(left = total1, 
                right = total_user_messages, how='outer',on=['user_id', 'month']).fillna(0)

print(len(users_total))

users_total<jupyter_output>user_id  month  minutes_used  calls_made
0        1000     12         124.0          16
1        1001      8         182.0          27
2        1001      9         315.0          49
3        1001     10         393.0          65
4        1001     11         426.0          64
...       ...    ...           ...         ...
2253     1498     12         339.0          39
2254     1499      9         346.0          41
2255     1499     10         385.0          53
2256     1499     11         308.0          45
2257     1499     12         496.0          65

[2258 rows x 4 columns]
      user_id  month   mb_used
385      1081      4     92.31
2084     1452     12     92.68
354      1076      7    113.74
869      1182      5    129.84
506      1108     12    233.17
...       ...    ...       ...
1126     1240      8  49950.58
1355     1292      8  51809.35
1359     1292     12  52034.66
569      1121     12  66863.89
1752     1379     12  70931.59

[2277 rows x 3 columns]
      user_id [...]<jupyter_text>In order to calculate the average profit per user I rounded up the monthly mb internet usage and converted it to Gb according to Megaline policy, based on this company logic I treated new users same as existed for monthly plan charge.<jupyter_code>User_costs = pd.merge(users_total, megaline_users[['tariff','user_id','city']], how='outer',on='user_id').fillna(0)

print(User_costs)

def cost(row):
    tariff=row['tariff']
    calls=row['minutes_used']
    text=row['messages_per_user']
    Gb=row['Gb_used']
    calls_cost=0
    text_cost=0
    Gb_cost=0
    total_cost=0
    if tariff=='surf':
        user_cost=20
    else:
        user_cost=70
    if tariff=='surf' and calls-500 > 0:
        calls_cost = (calls-500) * 0.03
    elif tariff=='ultimate' and calls-3000 > 0:
        calls_cost = (calls-3000) * 0.01
    if tariff=='surf' and text-50 > 0:
        text_cost = (text-50) * 0.03
    elif tariff=='ultimate' and text-1000 > 0:
        text_cost = (text-1000) * 0.01
    if tariff=='surf' and Gb-15 > 0:
        Gb_cost = (Gb-15) * 10
    elif tariff=='ultimate' and Gb-30 > 0:
        Gb_cost = (Gb-30) * 7
    total_cost = user_cost + calls_cost + text_cost + Gb_cost
    return total_cost

User_costs['total_cost'] = User_costs.apply(cost, axis=1)

User_costs.sort_values('total_cost')           
User_costs.tail()<jupyter_output><empty_output><jupyter_text>-The number of calls made and minutes used per month for each user were calculated.-The number of text messages sent per month for each user were calculated.-The volume of data per month or each user were calculated.-The monthly profit from each user were calculated and added to User_costs      table.   Describe the customers' behavior<jupyter_code>surf_plan = User_costs[User_costs['tariff'] == 'surf']

ultimate_plan = User_costs[User_costs['tariff'] == 'ultimate']

print(surf_plan)

print(ultimate_plan)

columns_name = ['minutes_used', 'calls_made', 'Gb_used', 'messages_per_user']
bins = 30
for name in columns_name:
    surf_plan.hist(column = name, bins = bins);
    plt.xlabel('surf plan')
    plt.ylabel('amount of data');
    ultimate_plan.hist(column = name, bins = bins);
    plt.xlabel('ultimate plan')
    plt.ylabel('amount of data');
    plt.show()
    
print('SURF PLAN',surf_plan[surf_plan.columns[2:6]].describe())
print()
print('ULTIMATE PLAN',ultimate_plan[ultimate_plan.columns[2:6]].describe())
print()
 # calculating the variance which is also is the square of the standard deviation:
for name in columns_name: 
    print('SURF PLAN',name,'variance is:',surf_plan[name].var())
print()  
for name in columns_name: 
    print('ULTIMATE PLAN',name,'variance is:',ultimate_plan[name].var())
print()    
for name in columns_name: 
    print('SURF PLAN',name,'median is:',surf_plan[name].median())
print()  
for name in columns_name: 
    print('ULTIMATE PLAN',name,'median is:',ultimate_plan[name].median())<jupyter_output>user_id  month  minutes_used  calls_made  Gb_used  messages_per_user  \
1        1001    8.0         182.0        27.0      7.0               30.0   
2        1001    9.0         315.0        49.0     14.0               44.0   
3        1001   10.0         393.0        65.0     22.0               53.0   
4        1001   11.0         426.0        64.0     19.0               36.0   
5        1001   12.0         412.0        56.0     19.0               44.0   
...       ...    ...           ...         ...      ...                ...   
2295     1143    0.0           0.0         0.0      0.0                0.0   
2298     1307    0.0           0.0         0.0      0.0                0.0   
2299     1319    0.0           0.0         0.0      0.0                0.0   
2300     1378    0.0           0.0         0.0      0.0                0.0   
2302     1473    0.0           0.0         0.0      0.0                0.0   

     tariff                                    city  total_cost[...]<jupyter_text>First of all not all users are using the whole options of their plans for example the text messages are mostly unused for both plans and the internet usage is the most wanted, to avoid outliers affect on averages I colculated also the median to find out what amount of data is mostly used by users, according to median and mean calculation most of ultimate users don't really use the full potential of their plan and can easily pass to surf plan for same needs, this issue can be reflected on future companies profit.  Test the hypotheses:The average profit from users of Ultimate and Surf calling plans differs.The average profit from users in NY-NJ area is different from that of theusers from other regions.<jupyter_code># random sample:
sample_surf=surf_plan.sample(n=len(ultimate_plan), random_state=1)

print(sample_surf['total_cost'].var()) 

print(ultimate_plan['total_cost'].var())

# variance are different 

alpha = 0.05 # critical statistical significance
equal_var = False
results = st.ttest_ind(
        sample_surf['total_cost'], 
        ultimate_plan['total_cost'])

print('p-value: ', results.pvalue)

if (results.pvalue < alpha):
        print("We reject the null hypothesis")
else:
        print("We can't reject the null hypothesis") 
        
#<jupyter_output>2925.789845717086
129.15955617707067
p-value:  3.0266873282342404e-08
We reject the null hypothesis<jupyter_text>In order to test the hypothes if the average profit from users of Ultimate and Surf calling plans differs I apply the stats.ttest_ind method.The null hypothesis is that the average profits are equal.In order to make groups equal I took random simple from the larger surf_plan group. Variance calculation of two groups showed that two groups have different variance, inorder to get the most accurate results equal_var param was set to False.According to analysis average profit from users of Ultimate and Surf calling plans are different.<jupyter_code>NY_NJ_users=User_costs[User_costs['city'].str.contains('NY-NJ')]

other_users=User_costs[~User_costs['city'].str.contains('NY-NJ')]

# random sample:
sample_of_other=other_users.sample(n=len(NY_NJ_users), random_state=1)

print(NY_NJ_users['total_cost'].var()) 

print(sample_of_other['total_cost'].var())

alpha = 0.05 # critical statistical significance

results = st.ttest_ind(
        NY_NJ_users['total_cost'], 
        sample_of_other['total_cost'])

print('p-value: ', results.pvalue)

if (results.pvalue < alpha):
        print("We reject the null hypothesis")
else:
        print("We can't reject the null hypothesis")<jupyter_output>1890.786445124416
1922.7523691865608
p-value:  0.08121298043459041
We can't reject the null hypothesis
{'fraction_non_alphanumeric': 0.09931274072955215, 'fraction_numerical': 0.06124915036628653, 'mean_word_length': 2.931710213776722, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 26, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '595269', 'license': 'MIT', 'n_tokens_mistral': 4760, 'n_tokens_neox': 4230, 'n_words': 1159, 'path': 'Telecom_operator_Megaline/telecom.ipynb', 'repo_name': 'BorisAks/Portfolio', 'size': '13241'}