<jupyter_start><jupyter_text>ContextThe data we will be using through the pratical classes comes from a small relational database whose schema can be seen below:![alt text](../figures/schema.png "Relation database schema")  Reading the Data<jupyter_code># path to database
my_path = os.path.join("..", "data", "datamining.db")

# connect to the database
conn = sqlite3.connect(my_path)

# the query
query = """
select
    age, 
    income, 
    frq, 
    rcn, 
    mnt, 
    clothes, 
    kitchen, 
    small_appliances, 
    toys, 
    house_keeping,
    dependents, 
    per_net_purchase,
    g.gender, 
    e.education, 
    m.status, 
    r.description
from customers as c
    join genders as g on g.id = c.gender_id
    join education_levels as e on e.id = c.education_id
    join marital_status as m on m.id = c.marital_status_id
    join recommendations as r on r.id = c.recommendation_id
order by c.id;
"""

df = pd.read_sql_query(query, conn)<jupyter_output><empty_output><jupyter_text>Make a copy of your original datasetwhy?<jupyter_code>df_original = df.copy()<jupyter_output><empty_output><jupyter_text>Metadata- *id* - The unique identifier of the customer- *age* - The year of birht of the customer- *income* - The income of the customer- *frq* - Frequency: number of purchases made by the customer- *rcn* - Recency: number of days since last customer purchase- *mnt* - Monetary: amount of € spent by the customer in purchases- *clothes* - Number of clothes items purchased by the customer- *kitchen* - Number of kitchen items purchased by the customer- *small_appliances* - Number of small_appliances items purchased by the customer- *toys* - Number of toys items purchased by the customer- *house_keeping* - Number of house_keeping items purchased by the customer- *dependents* - Binary. Whether or not the customer has dependents- *per_net_purchase* - Percentage of purchases made online- *education* - Education level of the customer- *status* - Marital status of the customer- *gender* - Gender of the customer- *description* - Last customer's recommendation description  Problems:- Duplicates?- Data types?- Missing values?- Strange values?- Descriptive statistics? Take a closer look and point out possible problems:(hint: a missing values in pandas is represented with a NaN value)<jupyter_code># replace "" by nans
df.replace("", np.nan, inplace=True)
# check dataset data types again
df.dtypes
# check descriptive statistics again
df.describe(include="all").T
# Define metric and non-metric features. Why?
non_metric_features = ["education", "status", "gender", "dependents", "description"]
metric_features = df.columns.drop(non_metric_features).to_list()<jupyter_output><empty_output><jupyter_text>Fill missing values (Data imputation)How can we fill missing values?<jupyter_code># Creating a copy to apply central tendency measures imputation
df_central = df.copy()
# count of missing values
df_central.isna().sum()
df_central.median()
modes = df_central[non_metric_features].mode().loc[0]
modes
df_central.fillna(df_central.median(), inplace=True)
df_central.fillna(modes, inplace=True)
df_central.isna().sum()  # checking how many NaNs we still have
# Creating new df copy to explore neighbordhood imputation
df_neighbors = df.copy()
# Seeing rows with NaNs
nans_index = df_neighbors.isna().any(axis=1)
df_neighbors[nans_index]
# KNNImputer - only works for numerical varaibles
imputer = KNNImputer(n_neighbors=5, weights="uniform")
df_neighbors[metric_features] = imputer.fit_transform(df_neighbors[metric_features])
# See rows with NaNs imputed
df_neighbors.loc[nans_index, metric_features]
# let's keep the central imputation
df = df_central.copy()<jupyter_output><empty_output><jupyter_text>An overview of our previous data explorationYou can also explore this dataset using the exported `pandas-profiling` report. ![](../figures/exp_analysis/categorical_variables_frequecies.png)![](../figures/exp_analysis/numeric_variables_histograms.png)![](../figures/exp_analysis/numeric_variables_boxplots.png)![](../figures/exp_analysis/pairwise_relationship_of_numerical_variables.png)![](../figures/exp_analysis/correlation_matrix.png)  Outlier removalWhy do we need to remove outliers? Which methods can we use?Let's start by "manually" filtering the dataset's outliers<jupyter_code># This may vary from session to session, and is prone to varying interpretations.
# A simple example is provided below:

filters1 = (
    (df['house_keeping']<=50)
    &
    (df['kitchen']<=40)
    &
    (df['toys']<=35)
    &
    (df['education']!='OldSchool')
)

df_1 = df[filters1]
print('Percentage of data kept after removing outliers:', np.round(df_1.shape[0] / df_original.shape[0], 4))<jupyter_output>Percentage of data kept after removing outliers: 0.9799<jupyter_text>Outlier removal using only the IQR methodWhy should you use/not use this method?<jupyter_code>q25 = df.quantile(.25)
q75 = df.quantile(.75)
iqr = (q75 - q25)

upper_lim = q75 + 1.5 * iqr
lower_lim = q25 - 1.5 * iqr

filters2 = []
for metric in metric_features:
    llim = lower_lim[metric]
    ulim = upper_lim[metric]
    filters2.append(df[metric].between(llim, ulim, inclusive=True))

filters2 = pd.Series(np.all(filters2, 0))
df_2 = df[filters2]
print('Percentage of data kept after removing outliers:', np.round(df_2.shape[0] / df_original.shape[0], 4))<jupyter_output>Percentage of data kept after removing outliers: 0.8242<jupyter_text>What do you think about this percentage?  Combining different outlier methodsMore robust/ consistent outlier detection method:<jupyter_code>df_3 = df[(filters1 | filters2)]
print('Percentage of data kept after removing outliers:', np.round(df_3.shape[0] / df_original.shape[0], 4))
# Get the manual filtering version
df = df_1.copy()<jupyter_output><empty_output><jupyter_text>Feature EngineeringA reminder of our metadata:- *id* - The unique identifier of the customer- *age* - The year of birht of the customer- *income* - The income of the customer- *frq* - Frequency: number of purchases made by the customer- *rcn* - Recency: number of days since last customer purchase- *mnt* - Monetary: amount of € spent by the customer in purchases- *clothes* - Number of clothes items purchased by the customer- *kitchen* - Number of kitchen items purchased by the customer- *small_appliances* - Number of small_appliances items purchased by the customer- *toys* - Number of toys items purchased by the customer- *house_keeping* - Number of house_keeping items purchased by the customer- *dependents* - Binary. Whether or not the customer has dependents- *per_net_purchase* - Percentage of purchases made online- *education* - Education level of the customer- *status* - Marital status of the customer- *gender* - Gender of the customer- *description* - Last customer's recommendation description<jupyter_code>df['birth_year'] = df['age']
df['age'] = datetime.now().year - df['birth_year']

df['spent_online'] = (df['per_net_purchase'] / 100) * df['mnt']
# How can we avoid having as many extreme values in 'rcn'?
print((df['rcn']>100).value_counts())

rcn_t = df['rcn'].copy()
rcn_t.loc[rcn_t>100] = 100

df['rcn'] = rcn_t<jupyter_output>False    8437
True      380
Name: rcn, dtype: int64<jupyter_text>Variable selection: Redundancy VS Relevancy  RedundancyWe already saw our original correlation matrix:![](../figures/exp_analysis/correlation_matrix.png)<jupyter_code># Select variables according to their correlations
df.drop(columns=['birth_year', 'age', 'mnt'], inplace=True)
# Updating metric_features
metric_features.append("spent_online")
metric_features.remove("mnt")
metric_features.remove("age")<jupyter_output><empty_output><jupyter_text>RelevancySelecting variables based on the relevancy of each one to the task. Example: remove uncorrelated variables with the target, stepwise regression, use variables for product clustering, use variables for socio-demographic clustering, ...Variables that aren't correlated with any other variable are often also not relevant. In this case we will not focus on this a lot since we don't have a defined task yet.  Data Normalization<jupyter_code>df_minmax = df.copy()
# Use MinMaxScaler to scale the data
scaler = MinMaxScaler()
scaled_feat = scaler.fit_transform(df_minmax[metric_features])
scaled_feat
# See what the fit method is doing (notice the trailing underscore):
print("Parameters fitted:\n", scaler.data_min_, "\n", scaler.data_max_)
df_minmax[metric_features] = scaled_feat
df_minmax.head()
# Checking max and min of minmaxed variables
df_minmax[metric_features].describe().round(2)
df_standard = df.copy()
scaler = StandardScaler()
scaled_feat = scaler.fit_transform(df_standard[metric_features])
scaled_feat
# See what the fit method is doing (notice the trailing underscore):
print("Parameters fitted:\n", scaler.mean_, "\n", scaler.var_)
df_standard[metric_features] = scaled_feat
df_standard.head()
# Checking mean and variance of standardized variables
df_standard[metric_features].describe().round(2)<jupyter_output><empty_output><jupyter_text>**Important**: What if we had a training and test set? Should we fit a Scaler in both? What about other Sklearn objects?<jupyter_code>df = df_standard.copy()<jupyter_output><empty_output><jupyter_text>One-hot encoding<jupyter_code>df_ohc = df.copy()
# First let's remove status=Whatever
df_ohc.loc[df_ohc['status'] == 'Whatever', 'status'] = df['status'].mode()[0]
# Use OneHotEncoder to encode the categorical features. Get feature names and create a DataFrame 
# with the one-hot encoded categorical features (pass feature names)
ohc = OneHotEncoder(sparse=False, drop="first")
ohc_feat = ohc.fit_transform(df_ohc[non_metric_features])
ohc_feat_names = ohc.get_feature_names()
ohc_df = pd.DataFrame(ohc_feat, index=df_ohc.index, columns=ohc_feat_names)  # Why the index=df_ohc.index?
ohc_df
# Reassigning df to contain ohc variables
df_ohc = pd.concat([df_ohc.drop(columns=non_metric_features), ohc_df], axis=1)
df_ohc.head()
df = df_ohc.copy()<jupyter_output><empty_output><jupyter_text>Dimensionality Reduction<jupyter_code>df_pca = df.copy()<jupyter_output><empty_output><jupyter_text>[A more specific explanation of PCA](https://builtin.com/data-science/step-step-explanation-principal-component-analysis)![](https://builtin.com/sites/default/files/inline-images/Principal%20Component%20Analysis%20second%20principal.gif)<jupyter_code># Use PCA to reduce dimensionality of data
pca = PCA()
pca_feat = pca.fit_transform(df_pca[metric_features])
pca_feat  # What is this output?<jupyter_output><empty_output><jupyter_text>How many Principal Components to retain?<jupyter_code># Output PCA table
pd.DataFrame(
    {"Eigenvalue": pca.explained_variance_,
     "Difference": np.insert(np.diff(pca.explained_variance_), 0, 0),
     "Proportion": pca.explained_variance_ratio_,
     "Cumulative": np.cumsum(pca.explained_variance_ratio_)},
    index=range(1, pca.n_components_ + 1)
)
# figure and axes
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(15, 5))

# draw plots
ax1.plot(pca.explained_variance_, marker=".", markersize=12)
ax2.plot(pca.explained_variance_ratio_, marker=".", markersize=12, label="Proportion")
ax2.plot(np.cumsum(pca.explained_variance_ratio_), marker=".", markersize=12, linestyle="--", label="Cumulative")

# customizations
ax2.legend()
ax1.set_title("Scree Plot", fontsize=14)
ax2.set_title("Variance Explained", fontsize=14)
ax1.set_ylabel("Eigenvalue")
ax2.set_ylabel("Proportion")
ax1.set_xlabel("Components")
ax2.set_xlabel("Components")
ax1.set_xticks(range(0, pca.n_components_, 2))
ax1.set_xticklabels(range(1, pca.n_components_ + 1, 2))
ax2.set_xticks(range(0, pca.n_components_, 2))
ax2.set_xticklabels(range(1, pca.n_components_ + 1, 2))

plt.show()
# Perform PCA again with the number of principal components you want to retain
pca = PCA(n_components=4)
pca_feat = pca.fit_transform(df_pca[metric_features])
pca_feat_names = [f"PC{i}" for i in range(pca.n_components_)]
pca_df = pd.DataFrame(pca_feat, index=df_pca.index, columns=pca_feat_names)  # remember index=df_pca.index
pca_df
# Reassigning df to contain pca variables
df_pca = pd.concat([df_pca, pca_df], axis=1)
df_pca.head()<jupyter_output><empty_output><jupyter_text>How do we interpret each Principal Component (with style)?<jupyter_code>def _color_red_or_green(val):
    if val < -0.45:
        color = 'background-color: red'
    elif val > 0.45:
        color = 'background-color: green'
    else:
        color = ''
    return color

# Interpreting each Principal Component
loadings = df_pca[metric_features + pca_feat_names].corr().loc[metric_features, pca_feat_names]
loadings.style.applymap(_color_red_or_green)
df = df_pca.copy()<jupyter_output><empty_output><jupyter_text>**Some final data preprocessing**<jupyter_code># Do this after checking the new pandas profiling report
df.drop(columns=['PC3'], inplace=True)<jupyter_output><empty_output><jupyter_text>Redo data explorationCheck if the data looks the way you expect it to. - Have you missed some outliers? - Are there still missing values?- Is the data normalized?This is an iterative process. It is likely you will change your preprocessing steps frequently throughout your group work.<jupyter_code>ProfileReport(
    df,
    title='Tugas Customer Data Preprocessed',
    correlations={
        "pearson": {"calculate": True},
        "spearman": {"calculate": False},
        "kendall": {"calculate": False},
        "phi_k": {"calculate": False},
        "cramers": {"calculate": False},
    },
)<jupyter_output><empty_output><jupyter_text>**Is everything as you expect it to be? Save the data for later use.**<jupyter_code>df.to_csv(os.path.join("..", "data", "tugas_preprocessed.csv"), index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11262376237623763, 'fraction_numerical': 0.011138613861386138, 'mean_word_length': 5.733823529411764, 'pattern_counts': {'":': 14, '<': 78, '<?xml version=': 0, '>': 77, 'https://': 2, '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': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '13891', 'license': 'MIT', 'n_tokens_mistral': 4668, 'n_tokens_neox': 4400, 'n_words': 1242, 'path': 'notebooks_solutions/lab08_data_preprocess3.ipynb', 'repo_name': 'gabriel-martins-souza/Data-Mining-21-22', 'size': '13736'}