<jupyter_start><jupyter_text>Dependencies<jupyter_code>import pandas as pd
import numpy as np

import xlwings

import pyodbc

import warnings
warnings.filterwarnings('ignore')

from AB import ABMean<jupyter_output><empty_output><jupyter_text>1. Reading the data  From DB (online)<jupyter_code>cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=;"
                      "Database=;"
                      "Uid=;"
                      "Pwd=;"
                      "MARS_Connection=Yes;")
# df = pd.read_sql_query('exec DS_ExperimentData 0, 30', cnxn)<jupyter_output><empty_output><jupyter_text>From .csv (offline)<jupyter_code>df=pd.read_csv('direct.csv')
df.sample(5)
df.shape
df.fillna(0, inplace=True) 

# df['CP'] = df['CP'].astype(int)
# df['OP'] = df['OP'].astype(int)
# df['P'] = df['P'].astype(int)<jupyter_output><empty_output><jupyter_text>2. Preparing the data  Adding aggregate KPIs<jupyter_code>df['Learn'] = df.L + df.UL
df['Social_1'] = df.UC + df.UP + df.DP
df['Social_2'] = df.P + df.OP
df['Social'] = df.Social_1 + df.Social_2
df['Total'] = df.Learn + df.Social
df.head()<jupyter_output><empty_output><jupyter_text>Defining the columns and rows for excel  Simple version<jupyter_code># KPIs = ['CK','L','UL','UP','UC','DP','CP','Learn','Social_1','Social_2','Social', 'Total']

# KPI_rows = [8,10,11,12,13,14,15,17,18,19,20,21]

# KPI_columns = [2,8,15,17,18]

# KPI_positions = {}

# for KPI in KPIs:
#     KPI_positions[KPI] = [[KPI_rows[KPIs.index(KPI)], i] for i in KPI_columns]<jupyter_output><empty_output><jupyter_text>Extended version<jupyter_code>KPIs = ['CK','L','UL','UP','UC','DP','CP', 'P', 'OP', 'F_to', 'F_by', 'Learn','Social_1','Social_2','Social', 'Total']

KPI_rows = [8,10,11,12,13,14,15,16,17,18,19,21,22,23,24,25]

KPI_columns = [2,3,8,9,16,18,19]

KPI_positions = {}

for KPI in KPIs:
    KPI_positions[KPI] = [[KPI_rows[KPIs.index(KPI)], i] for i in KPI_columns]
KPI_positions<jupyter_output><empty_output><jupyter_text>3. Running the experiments: to load the data to excel  Setting the treatment and control data<jupyter_code># data_c = df[df.UserId%2 == 0]
# data_t = df[df.UserId%2 == 1]

data_c = df[df.UserId%2 == 1][df.CK >3]
data_t = df[df.UserId%2 == 0][df.CK >3]<jupyter_output><empty_output><jupyter_text>Final state of the data before going to the excel<jupyter_code># total numbers of users
row_1_1 = len(data_c)
row_1_2 = len(data_t)

# remaining data
### dict of all data
KPIs_to_excel = {}

for KPI in KPIs:
    # empty list for storing each KPIs data
    KPI_info_list = []
    
    # treatment and control data of the KPI in numpy arrays
    data_c_KPI = data_c[KPI].values
    data_t_KPI = data_t[KPI].values
    
    # adding means and proportions to the list
    KPI_info_list.append(data_c_KPI.mean())
    KPI_info_list.append(np.count_nonzero(data_c_KPI)/len(data_c_KPI))

    KPI_info_list.append(data_t_KPI.mean())
    KPI_info_list.append(np.count_nonzero(data_t_KPI)/len(data_t_KPI))

    # results of the experiment
    exp = ABMean(data_c_KPI, data_t_KPI)
    exp.hypothesis_testing()
    # adding p-values
    KPI_info_list.append(exp.p)
    
    # pre experiment stats
    data_control = data_c_KPI
    data_treatment = data_t_KPI
    exp.min_det_effect = np.mean(data_treatment)/np.mean(data_control)-1
    exp.baseline_value = np.mean(data_control)
    exp.baseline_var = np.std(data_control)**2
    exp.treatment_var = np.std(data_treatment)**2
    exp.stat_power_calc()
    # adding power
    KPI_info_list.append(exp.stat_power)
    
    # adding sample sizes
    KPI_info_list.append(exp.sample_size_calculator())
    
    KPIs_to_excel[KPI] = KPI_info_list<jupyter_output>p = 0.4576. We fail to reject the Null hypothesis on significance level: 95%.
p = 0.6994. We fail to reject the Null hypothesis on significance level: 95%.
p = 0.6429. We fail to reject the Null hypothesis on significance level: 95%.
p = 0.0000. We reject the Null hypothesis. The difference is statistically significant on significance level: 95%.
p = 0.0245. We reject the Null hypothesis. The difference is statistically significant on significance level: 95%.
p = 0.2535. We fail to reject the Null hypothesis on significance level: 95%.
p = 0.5507. We fail to reject the Null hypothesis on significance level: 95%.
p = 0.3106. We fail to reject the Null hypothesis on significance level: 95%.
p = 0.6324. We fail to reject the Null hypothesis on significance level: 95%.
p = 0.8589. We fail to reject the Null hypothesis on significance level: 95%.
p = 0.1201. We fail to reject the Null hypothesis on significance level: 95%.
p = 0.6644. We fail to reject the Null hypothesis on significance le[...]<jupyter_text>4. Writing to excel<jupyter_code># wb = xlwings.Book("experiments_means_template.xlsx")
wb = xlwings.Book("experiments_means_template_extended.xlsx")

sheet = wb.sheets[1]
sheet.range(6,2).value = row_1_1
sheet.range(6,8).value = row_1_2


for KPI in KPIs:
    for position in range(len(KPI_positions[KPI])):
        sheet.range(KPI_positions[KPI][position][0], KPI_positions[KPI][position][1]).value = KPIs_to_excel[KPI][position]
wb.save()
wb.close()<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>df[df.UserId%2 == 0].mean()
df[df.UserId%2 == 0].astype(bool).sum(axis=0)
df[df.UserId%2 == 1].astype(bool).sum(axis=0)<jupyter_output><empty_output><jupyter_text>Junk Codes  Reading from DB<jupyter_code>df[df.TreatmentGroup == 0].shape[0]
data_c = df[df.UserId%2 == 0]
data_t = df[df.UserId%2 == 1]
data_c_KPI = data_c["L"].values
data_t_KPI = data_t["L"].values
column_1 = data_c_KPI.mean()
column_2 = data_c_KPI.mean()
exp = ABMean(data_c_KPI, data_t_KPI)
exp.hypothesis_testing()
column_3 = exp.p
data_control = data_c_KPI
data_treatment = data_t_KPI
exp.min_det_effect = np.mean(data_treatment)/np.mean(data_control)-1
exp.baseline_var = np.std(data_control)**2
exp.treatment_var = np.std(data_treatment)**2

exp.stat_power_calc()
column_4 = exp.stat_power
column_4
column_5 = exp.sample_size_calculator()
column_5
exp.pre_experiment_stats()
exp.return_sample_stats()
exp.hypothesis_summary()
number_of_control = df[df.TreatmentGroup == 0].shape[0]
number_of_treatment = df[df.TreatmentGroup == 1].shape[0]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1389597957881302, 'fraction_numerical': 0.036694320357370774, 'mean_word_length': 4.8919172932330826, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 46, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '622807', 'license': 'MIT', 'n_tokens_mistral': 2590, 'n_tokens_neox': 2381, 'n_words': 495, 'path': 'Lectures/Levon/A-B_Testing_End-to-End/A-B_Testing_Automation_New/main.ipynb', 'repo_name': 'lev1khachatryan/ASDS_ML-2', 'size': '6268'}