Data Preprocessing

Data Cleaning

Data cleaning tasks

• Fill in missing values
• Identify outliers and smooth out noisy data
• Correct inconsistent data

Missing Data

Data is not always available

• Many tuples have no recorded value for several attributes, such as customers income in sales data

Missing data may be due to

• Equipment malfunction
• Inconsistent with other recorded data and thus deleted
• Data not entered due to misunderstanding
• Certain data may not be considered important at the time of entry
• Not register history of the changes

Missing data may need to be inferred

Missing Data — what to do?

• Ignore the tuple: Usually done when class label is missing (assuming the task in classification — not effective when the percentage of missing values per attribute varies considerably)
• Fill in the missing value manually: Tedious + infeasible?
• Use a global constant to fill in the missing value: E.g., “unknown”, a new class?
• Use the attribute mean to fill in the missing value
• Use the most probable value to fill the missing value: Inference-based such as Bayesian formula or decision tree

# Mean
sum(df['sched_dep_time'])/len(df['sched_dep_time'])

# Median
import statistics
statistics.median(df['sched_dep_time'])

# Mode
import statistics
statistics.mode(df['sched_dep_time'])

Missing Values

There are a number of methods to deal with missing values in the data frame:

import pandas as pd
import numpy as np

# Create a sample DataFrame with missing values
data = {'col1': [1, 2, np.nan, 4, 5],
        'col2': [np.nan, 6, 7, np.nan, 9],
        'col3': [np.nan, np.nan, np.nan, np.nan, np.nan],
        'col4': [10, 11, 12, 13, 14],
        'col5': [15, np.nan, 17, 18, np.nan]}
df = pd.DataFrame(data)

print("Original DataFrame:")
print(df)
print("\n" + "="*30 + "\n")

# 1. dropna(): Drop missing observations (rows with any NaN)
df_dropped = df.dropna()
print("dropna():")
print(df_dropped)
print("\n" + "="*30 + "\n")

# 2. dropna(how='all'): Drop observations where all cells are NA
df_dropped_all = df.dropna(how='all')
print("dropna(how='all'):")
print(df_dropped_all)
print("\n" + "="*30 + "\n")

# 3. dropna(axis=1, how='all'): Drop column if all the values are missing
df_dropped_cols_all = df.dropna(axis=1, how='all')
print("dropna(axis=1, how='all'):")
print(df_dropped_cols_all)
print("\n" + "="*30 + "\n")

# 4. dropna(thresh=3): Drop rows that contain less than 3 non-missing values
df_dropped_thresh = df.dropna(thresh=3)
print("dropna(thresh=3):")
print(df_dropped_thresh)
print("\n" + "="*30 + "\n")

# 5. fillna(0): Replace missing values with zeros
df_filled_zero = df.fillna(0)
print("fillna(0):")
print(df_filled_zero)
print("\n" + "="*30 + "\n")

# 6. isnull(): returns True if the value is missing
df_isnull = df.isnull()
print("isnull():")
print(df_isnull)
print("\n" + "="*30 + "\n")

# 7. notnull(): Returns True for non-missing values
df_notnull = df.notnull()
print("notnull():")
print(df_notnull)

Data Integration

raw_data = {
    'DeptID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Edward'],
    'Age': [25, 38.75, 35, 45, 50],
    'Salary': [50000, 60000, 70000, 67500.0, 90000],
    'City': ['New York', 'Paris', 'Paris', 'London', 'London']
}

df = pd.DataFrame(raw_data)
df

Concatenation (Appending rows from another dataset)

# Create a new DataFrame with additional employee data (including Age column)
extra_data = pd.DataFrame({
    'DeptID': [6, 7],
    'Name': ['Frank', 'Grace'],
    'Age': [28, 32],
    'Salary': [75000, 80000],
    'City': ['Berlin', 'New York']
})

# Append the new rows to the existing DataFrame 'df', aligning columns, and reindexing
df = pd.concat([df, extra_data], ignore_index=True)     # ignore_index=True resets the row index after concatenation.
print(df)

# Create another DataFrame with different structure (no 'Age' column, different DeptIDs)
extra_data = pd.DataFrame({
    'DeptID': [13, 14],
    'Name': ['Frank', 'Grace'],
    'Salary': [75000, 80000],
    'City': ['Berlin', 'New York']
})

# Append this new data to 'df', which will introduce NaN in 'Age' for the new rows
df = pd.concat([df, extra_data], ignore_index=True)    # If columns don't match (Age missing), Pandas fills with NaN automatically
print(df)

Merging on a key (Combining columns based on a common column)

# Create a lookup DataFrame with unique cities and their corresponding countries
city_info = pd.DataFrame({
    'City': ['New York', 'Paris', 'London', 'Berlin'],
    'Country': ['USA', 'France', 'UK', 'Germany']
})

# Perform a left join to add the 'Country' info to each employee based on 'City'
df = df.merge(city_info, on='City', how='left')
print(df)

# Create another city_info DataFrame with duplicate entries for 'London'
city_info = pd.DataFrame({
    'City': ['New York', 'London', 'London', 'Berlin'],
    'Country': ['USA', 'UK2', 'UK', 'Germany']
})

# Merge again: this will create duplicate rows for employees in cities with duplicate matches (e.g., London)
df = df.merge(city_info, on='City', how='left')
print(df)

Joining datasets (Using index-based merging)

# Create extra_info DataFrame mapping DeptID to Department
extra_info = pd.DataFrame({
    'DeptID': [1, 2, 3, 4, 5, 6, 7],
    'Department': ['IT', 'HR', 'Finance', 'Marketing', 'Sales', 'IT', 'HR']
})

# Join extra_info to df using 'DeptID' as index on both sides
df = df.set_index('DeptID').join(extra_info.set_index('DeptID'))
print(df)

# Add a new department entry with DeptID 8 (not yet in df)
extra_info = pd.DataFrame({
    'DeptID': [8],
    'Department': ['IT']
})

# Attempting to join again will raise KeyError if 'DeptID' is no longer a column in df
df = df.set_index("DeptID").join(extra_info.set_index("DeptID"))            
## KeyError: "None of ['DeptID'] are in the columns"
print(df)

# Fix: Either reset index or join directly if index already set
# Example fix:
df = df.join(extra_info.set_index("DeptID"))  # Join using existing index

Data Transformation

Normalization (Scaling values between 0 and 1)

https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.MinMaxScaler.html

from sklearn.preprocessing import MinMaxScaler

raw_data = {
    'ID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Edward'],
    'Age': [25, 38.75, 35, 45, 50],
    'Salary': [50000, 60000, 70000, 67500.0, 90000],
    'City': ['New York', 'Paris', 'Paris', 'London', 'London']
}

df = pd.DataFrame(raw_data)

# Initialize MinMaxScaler to scale numerical features to the range [0, 1]
scaler = MinMaxScaler()

# Apply MinMaxScaler to 'Age' and 'Salary' columns and update them in-place
df[['Age', 'Salary']] = scaler.fit_transform(df[['Age', 'Salary']])

print(df)

Standardization (Scaling to mean 0 and variance 1)

https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.StandardScaler.html

from sklearn.preprocessing import StandardScaler

raw_data = {
    'ID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Edward'],
    'Age': [25, 38.75, 35, 45, 50],
    'Salary': [50000, 60000, 70000, 67500.0, 90000],
    'City': ['New York', 'Paris', 'Paris', 'London', 'London']
}

df = pd.DataFrame(raw_data)

# Initialize StandardScaler (mean = 0, std = 1)
scaler_standard = StandardScaler()

# Standardize 'Age' and 'Salary' columns and replace the original values
df[['Age', 'Salary']] = scaler_standard.fit_transform(df[['Age', 'Salary']])

print(df)

Label Encoding (Converting categorical to numerical labels)

https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.LabelEncoder.html

from sklearn.preprocessing import LabelEncoder

raw_data = {
    'ID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Edward'],
    'Age': [25, 38.75, 35, 45, 50],
    'Salary': [50000, 60000, 70000, 67500.0, 90000],
    'City': ['New York', 'Paris', 'Paris', 'London', 'London']
}

df = pd.DataFrame(raw_data)

# Initialize the LabelEncoder
encoder = LabelEncoder()

# Encode the 'City' column into numeric values (e.g., London → 0, 'New York' → 1, Paris → 2)
df['City'] = encoder.fit_transform(df['City'])

print(df)

One-Hot Encoding (Converting categorical to binary variables)

https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.OneHotEncoder.html

from sklearn.preprocessing import OneHotEncoder

raw_data = {
    'ID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Edward'],
    'Age': [25, 38.75, 35, 45, 50],
    'Salary': [50000, 60000, 70000, 67500.0, 90000],
    'City': ['New York', 'Paris', 'Paris', 'London', 'London']
}

df = pd.DataFrame(raw_data)

# Initialize OneHotEncoder (sparse_output=False gives a dense array)
ohe = OneHotEncoder(sparse_output=False)

# Apply one-hot encoding to the 'City' column
city_encoded = ohe.fit_transform(df[['City']])

# Add the encoded city columns to the original DataFrame with proper column names
df[ohe.get_feature_names_out(['City'])] = city_encoded

# Drop the original 'City' column after encoding
df.drop(columns=['City'], inplace=True)

print(df)

Data Reduction

Principal Component Analysis (PCA) (Dimensionality Reduction)

PCA is a dimensionality reduction technique that transforms correlated features into a smaller set of uncorrelated features called principal components.

It helps in reducing redundant information and improving computational efficiency.

https://scikit-learn.org/stable/modules/generated/sklearn.decomposition.PCA.html

Assumption of PCA

These 3 assumptions behind PCA can be the reasons for which it can perform poorly in particular situations:

• Linearity
• Large variances have important structure
• The principal components are orthogonal

Linearity If the relationship between the variables are not linear there are different solutions to apply. Some of them derive from PCA.

Large variances have important structure Not always a high variance is related with meaningful information.

raw_data = {
    'ID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Edward'],
    'Age': [25, 38.75, 35, 45, 50],
    'Salary': [50000, 60000, 70000, 67500.0, 90000],
    'City': ['New York', 'Paris', 'Paris', 'London', 'London']
}

df = pd.DataFrame(raw_data)

# Initialize PCA to retain only one principal component
pca = PCA(n_components=1)

# Apply PCA on the 'Salary' column to reduce it to a single principal component
df['Salary_PCA'] = pca.fit_transform(df[['Salary']])

# PCA to reduce 2 features (Age and Salary) to 1 principal component
# Apply PCA transformation and add result as a new column
df['Age_Salary_PCA'] = pca.fit_transform(df[['Age', 'Salary']])

print(df)

Note: PCA is more useful when applied to multiple correlated numerical features (e.g., Age, Salary, Years of Experience).

Singular Value Decomposition SVD

Dimensionality reduction technique similar to PCA, actually PCA uses SVD.

It works better for sparse datasets

https://scikit-learn.org/stable/modules/generated/sklearn.decomposition.TruncatedSVDhtml#sklearn.decomposition.TruncatedSVD

import pandas as pd

df = pd.read_csv("https://github.com/andvise/DataAnalyticsDatasets/blob/8e8f6475f49d2a587e4f5c76cdf0b011b22c6ac1/dataset_5000_reviews.csv?raw=true")

# Display the count of unique sentiment values in the dataset
df['Sentiment'].value_counts()

from sklearn.preprocessing import LabelEncoder

y = df['Sentiment']

X = df['Review']

encoder = LabelEncoder()

# Fit and transform the 'Sentiment' column into numeric labels
y = encoder.fit_transform(y)

from sklearn.model_selection import train_test_split

# Split the dataset into training and testing sets (80% training, 20% testing) with stratified sampling
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, stratify=y, random_state=42)

from sklearn import neighbors
from sklearn import metrics
from sklearn import model_selection
import matplotlib.pyplot as plt

from sklearn.decomposition import TruncatedSVD
from sklearn.feature_extraction.text import CountVectorizer

# Initialize CountVectorizer to convert the text data into feature vectors
vec = CountVectorizer()

# Initialize TruncatedSVD to reduce the feature dimensions to 50
svd = TruncatedSVD(n_components=50)

# Apply CountVectorizer to transform training data into numerical form
X_train = vec.fit_transform(X_train)

# Apply TruncatedSVD to reduce the dimensionality of the transformed data
X_train = svd.fit_transform(X_train)

knn = neighbors.KNeighborsClassifier()

knn.fit(X_train, y_train)

Feature Selection using SelectKBest

This method selects the most relevant features by evaluating their statistical significance with respect to the target variable.

The SelectKBest function selects features based on a scoring function (e.g., ANOVA F-statistic).

https://scikit-learn.org/stable/modules/generated/sklearn.feature_selection.SelectKBest.html

from sklearn.feature_selection import SelectKBest, f_classif
import pandas as pd
import numpy as np

raw_data = {
    'ID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Edward'],
    'Age': [25, 38.75, 35, 45, 50],
    'Salary': [50000, 60000, 70000, 67500.0, 90000],
    'City': ['New York', 'Paris', 'Paris', 'London', 'London']
}

df = pd.DataFrame(raw_data)

# Generate a dummy target variable with random binary values (0 or 1)
dummy_target = np.random.randint(0, 2, df.shape[0])  # Generating dummy target variable

# Initialize SelectKBest feature selection with f_classif scoring function and selecting the top 2 features
selector = SelectKBest(score_func=f_classif, k=2)

# Apply feature selection to the 'Age' column and add the selected features to the DataFrame
df['Age_Selected'] = selector.fit_transform(df[['Age']].values, dummy_target)

print(df)

Clustering-based Reduction (Grouping similar records)

Clustering groups similar data points into clusters.

This method is useful for reducing the dataset by representing similar records with cluster labels.

https://scikit-learn.org/stable/modules/generated/sklearn.cluster.KMeans.html

from sklearn.cluster import KMeans

import pandas as pd
import numpy as np

raw_data = {
    'ID': [1, 2, 3, 4, 5],
    'Name': ['Alice', 'Bob', 'Charlie', 'David', 'Edward'],
    'Age': [25, 38.75, 35, 45, 50],
    'Salary': [50000, 60000, 70000, 67500.0, 90000],
    'City': ['New York', 'Paris', 'Paris', 'London', 'London']
}

df = pd.DataFrame(raw_data)

# Generate a dummy target variable with random binary values (0 or 1)
dummy_target = np.random.randint(0, 2, df.shape[0])  # Generating dummy target variable

# Initialize the KMeans clustering model with 2 clusters and a fixed random state for reproducibility
kmeans = KMeans(n_clusters=2, random_state=42)

# Fit the KMeans model on the 'Age_Selected' column and assign the cluster labels to a new column 'Cluster'
df['Cluster'] = kmeans.fit_predict(df[['Age_Selected']])

print(df)