Data Cleaning and Transformation

We step into the world of Data Cleaning and Transformation. Real-life data isn't always tidy; it has inconsistencies, missing data points, outliers, and even incorrect data! To extract meaningful insights or build reliable machine learning models, we clean and transform data.

In this session, we handle inconsistencies and outliers and apply various data transformations to enhance its readiness for analysis. Now, let's start this exploratory journey!

Why clean and transform data? Simple: unclean or inconsistent data can skew analysis or predictions. Weather data with missing temperatures, for instance, can lead to misleading climate predictions. The real world is full of such examples of analysis gone awry due to unclean data.

Let's delve into spotting inconsistencies. For instance, XL, X-L, xl represent the same clothing size but are reported differently. Python's pandas library comes in handy here.

import pandas as pd

# hypothetical dataset of clothing sizes
sizes = ['XL', 'S', 'M', 'X-L', 'xl', 'S', 'L', 'XL', 'M']
df = pd.DataFrame(sizes, columns=['Size'])

# Use value_counts() to spot inconsistent values
print(df['Size'].value_counts())

Output:

XL     2
X-L    1
xl     1
S      2
M      2
L      1
dtype: int64

To sort out inconsistencies, replace them with a standard value.

df.replace(['X-L', 'xl'], 'XL', inplace=True)
print(df['Size'].value_counts())

Output:

XL    4
S     2
M     2
L     1
dtype: int64

Scanning for outliers, or exceptional values, is the next step. Outliers can distort the analytical outcome. One common method to detect outliers is using the Interquartile Range (IQR).

As a short reminder, IQR method suggests that any value below $Q_1 - 1.5 \cdot IQR$ and above $Q_3 + 1.5 \cdot IQR$ are considered to be outliers. Where:

• $Q_1$ – The first quartile
• $Q_3$ – The third quartile
• $IQR$ – The Interquartile Range

Let's use the IQR method to identify and filter out outliers in a dataset.

import pandas as pd

# A dataset with an outlier
data = [1, 1.2, 1.1, 1.05, 1.5, 1.4, 9]
df = pd.DataFrame(data, columns=['Values'])

# Calculate Q1 (25th percentile) and Q3 (75th percentile)
Q1 = df['Values'].quantile(0.25)
Q3 = df['Values'].quantile(0.75)

# Calculate IQR
IQR = Q3 - Q1

# Define the acceptable range (1.5 * IQR rule)
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Filter out outliers
no_outliers_df = df[(df['Values'] >= lower_bound) & (df['Values'] <= upper_bound)]
print(no_outliers_df)

Output:

   Values
0    1.00
1    1.20
2    1.10
3    1.05
4    1.50
5    1.40

The value 9 is considered an outlier and is excluded from the filtered dataset.