<jupyter_start><jupyter_text>Reading the dataThis is necessary for IBM WATSON<jupyter_code># The code was removed by Watson Studio for sharing.<jupyter_output><empty_output><jupyter_text>Checking for missing values<jupyter_code>na_mask = df.isna()
print(na_mask.sum())
print("Number of rows with more than 2 missing values: {}".format((na_mask.sum(axis=1)>=2).sum()))<jupyter_output>customer_id         0
country_name        0
age                 0
customer_name       0
is_subscriber       0
subscriber_type    72
num_streams        46
dtype: int64
Number of rows with more than 2 missing values: 1<jupyter_text>Making a copy without misssing values to be able to analyze features relations<jupyter_code>df_1 = (df.drop(index = na_mask[na_mask.sum(axis=1) >= 1].index
                ,columns = ["customer_name"]))
df_1["is_subscriber"] = df_1["is_subscriber"].astype(int)
df_1.head()
sns.set(style="ticks", color_codes=True)
sns.pairplot(df_1,vars=["age","num_streams"],hue = "subscriber_type")
df_3 = pd.get_dummies(df_1)
corr = df_3[df_3.columns[1:]].corr()
# Generate a mask for the upper triangle
mask = np.triu(np.ones_like(corr, dtype=bool))
# Set up the matplotlib figure
f, ax = plt.subplots(figsize=(11, 9))
# Generate a custom diverging colorma
cmap = sns.diverging_palette(220, 10, as_cmap=True)
# Draw the heatmap with the mask and correct aspect ratio
sns.heatmap(corr, mask=mask, cmap=cmap, vmax=.3, center=0,
                square=True, linewidths=.5, cbar_kws={"shrink": .5})<jupyter_output><empty_output><jupyter_text>Some conclusions- In singapore there are less subscribers and num_streams (negative correlation)- num_streams has a positive correlation with is_subscriber- Age doesn't matter  Decisions:- Fill missing values on num_streams with the num_streams mean of the category (country,is_subscriber,subscriber_type)- Fill missing values on subscriber_type comparing the num_streams value with the num_streams mean of each subscriber_type according to (country,is_subscriber)- Delete the only row that has missing values on both columns<jupyter_code># drop row with 2 missing values
df_new = df.drop(na_mask[(na_mask.sum(axis=1)>=2)].index)
#mean of num_streams by categories
df_2 = pd.pivot_table(df_new,index=["country_name","is_subscriber","subscriber_type"],values = ["num_streams"],aggfunc="mean")
df_2<jupyter_output><empty_output><jupyter_text>Fill missing values on num_streams<jupyter_code>df_new_temp = df_new.join(df_2,on =["country_name","is_subscriber","subscriber_type"], rsuffix='_mean')
df_new_temp["num_streams"].fillna(value = df_new_temp["num_streams_mean"],inplace=True)
df_new_temp
#mean of num_streams by subscriber_type, considering (country,is_subscriber)
df_x = pd.pivot_table(df_new,index=["country_name","is_subscriber"],values ="num_streams",
                      columns=pd.Grouper(key="subscriber_type"),aggfunc="mean")
df_x<jupyter_output><empty_output><jupyter_text>Fill missing values on subscriber_typeUsing the category that has the closest num_streams mean to the real value of num_streams, considering subscriber_type and country<jupyter_code>df_new_temp_2 = (df_new_temp.join(df_x,on =["country_name","is_subscriber"], rsuffix='_mean_streams'))
#df_new_temp_2["num_streams"].fillna(value = df_new_temp["num_streams_mean"],inplace=True)
arr = abs(np.array([df_new_temp_2["num_streams"]-df_new_temp_2["aavail_basic"],
         df_new_temp_2["num_streams"]-df_new_temp_2["aavail_premium"],
        df_new_temp_2["num_streams"]-df_new_temp_2["aavail_unlimited"]]).T)

columns = np.array(["aavail_basic","aavail_premium","aavail_unlimited"])
closest = np.argmin(arr,axis=1)
df_new_temp_2["subscriber_type"].fillna(value = pd.Series(columns[closest]),inplace = True)
df_final = (df_new_temp_2.copy()
           .drop(columns= ["num_streams_mean","aavail_basic","aavail_premium","aavail_unlimited"]))
df_final<jupyter_output><empty_output><jupyter_text>Checking for missing values<jupyter_code>na_mask = df_final.isna()
print(na_mask.sum())<jupyter_output>customer_id        0
country_name       0
age                0
customer_name      0
is_subscriber      0
subscriber_type    0
num_streams        0
dtype: int64<jupyter_text>Important Visualizations- Percentage of non_subscribers per country- num_streams statistics on new dataset vs old dataset- Percentage for each subscriber_type on new dataset vs old dataset<jupyter_code>print(df["num_streams"].describe())
print(df_final["num_streams"].describe())<jupyter_output>count    954.000000
mean      17.780922
std        4.752115
min        1.000000
25%       15.000000
50%       19.000000
75%       21.000000
max       29.000000
Name: num_streams, dtype: float64
count    999.000000
mean      17.749453
std        4.664484
min        1.000000
25%       15.000000
50%       18.701087
75%       21.000000
max       29.000000
Name: num_streams, dtype: float64<jupyter_text>There has not been a significant change on num_streams statistics<jupyter_code>df_final["is_subscriber"] = df_final["is_subscriber"].astype(int)
df_frequency =pd.pivot_table(df_final,index="country_name",values = "is_subscriber",aggfunc="mean")
df_frequency["not_subscriber"] = 1-df_frequency["is_subscriber"]
df_frequency.T.plot.pie(figsize=(10,10),title = "Subscribers percentage per country",legend = False,subplots=True)<jupyter_output><empty_output><jupyter_text>In singapore there is a significant bigger portion of non subscribers<jupyter_code>df_frequency_2 = df_final["subscriber_type"]
df_frequency_2 = df_frequency_2.value_counts()
df_frequency_2.plot.pie()
#df_frequency_2.T.plot.pie(figsize=(10,10),title = "Subscribers percentage per country",legend = False,subplots=True)
df_frequency_2 = df["subscriber_type"]
df_frequency_2 = df_frequency_2.value_counts()
df_frequency_2.plot.pie()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11286992429456297, 'fraction_numerical': 0.03888506538196834, 'mean_word_length': 6.1150550795593634, 'pattern_counts': {'":': 1, '<': 38, '<?xml version=': 0, '>': 41, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '534480', 'license': 'Apache-2.0', 'n_tokens_mistral': 2159, 'n_tokens_neox': 2006, 'n_words': 409, 'path': 'ETL Y EDA/MissingValues_EDA.ipynb', 'repo_name': 'franciscoandrades/Portafolio', 'size': '5812'}