<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># CODE HERE<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()
# Check each step of these commands
modes = df_central[non_metric_features].mode().loc[0]
modes
# Fill NaNs on df_central
# CODE HERE
# 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 variables. Fill NaNs on df_neighbors
imputer = KNNImputer(n_neighbors=5, weights="uniform")
# CODE HERE
# See rows with NaNs imputed
# CODE HERE
# 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:

filters = (
    # CODE HERE
)

df_1 = df[filters]
print('Percentage of data kept after removing outliers:', np.round(df_1.shape[0] / df_original.shape[0], 4))<jupyter_output><empty_output><jupyter_text>Outlier removal using only the IQR methodWhy should you use/not use this method?<jupyter_code># Compute the IQR
# CODE HERE

# Compute upper and lower limit (lower_limit = Q1 -1.5*IQR | upper_limit = Q3 + 1.5*IQR)
# CODE HERE

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

df_2 = df[np.all(filters, 0)]
print('Percentage of data kept after removing outliers:', np.round(df_2.shape[0] / df_original.shape[0], 4))
# Get the manual filtering version
df = df_1.copy()<jupyter_output><empty_output><jupyter_text>Feature extractionA 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># Try to pull as many features as you can; You can always drop them later on
# You can start by converting the 'age' variable to the actual age
# You can also store the birth year in another feature

# CODE HERE<jupyter_output><empty_output><jupyter_text>Variable transformYou may change the values of observations for a give variable:<jupyter_code># Remember the 'rcn' variable had an odd behavior? 
# This is the time to fix that

# CODE HERE<jupyter_output><empty_output><jupyter_text>Handling highly correlated variablesWe already saw our original correlation matrix:![](../figures/exp_analysis/correlation_matrix.png)<jupyter_code># Select variables according to their correlations

# CODE HERE<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>
{'fraction_non_alphanumeric': 0.09353287012292892, 'fraction_numerical': 0.002939604489577766, 'mean_word_length': 4.884433962264151, 'pattern_counts': {'":': 10, '<': 41, '<?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': '60151', 'license': 'MIT', 'n_tokens_mistral': 2320, 'n_tokens_neox': 2160, 'n_words': 809, 'path': 'notebooks/lab06_data_preprocess.ipynb', 'repo_name': 'luitelgaurav/Data-Mining-21-22', 'size': '7484'}