Data Cleaning and Preparation: Handling Missing Data - Data Transformation: Removing
Duplicates, Transforming Data Using a Function or Mapping, Replacing Values, Detecting
and Filtering OutliersPlotting with pandas: Line Plots, Bar Plots, Histograms and Density
Plots, Scatter or Point Plots.
Data Cleaning and Preparation:
Data Cleaning: The process of correcting or removing inaccurate, corrupted, or
irrelevant data.
Data Preparation (or Preprocessing): Transforming raw data into a structured,
usable format for analysis or modelling.
Goal: Ensure the dataset is accurate, consistent, complete, and ready for analysis.
Handling Missing Values
Missing data occurs when some values are missing or not collected properly and
these missing values are represented as:
What Are Missing Values?
Missing values occur when data is not recorded or unavailable for some observations.
Common representations:
NaN (Not a Number in Python)
NULL (SQL)
Empty strings ("")
Special placeholders like -9999 or unknown
Ex:
import pandas as pd
import numpy as np
d = {'First_Score': [100, 90, [Link], 95],
'Second_Score': [30, 45, 56, [Link]],
'Third_Score': [[Link], 40, 80, 98]}
df = [Link](d)
res = [Link]()
print(res)
output:
First_Score Second_Score Third_Score
0 False False True
1 False False False
2 True False False
3 False True False
Data transformation:
Data transformation in data science is the essential process of converting raw data
from various sources into a clean, consistent, and usable format for analysis and model
building. It involves cleaning, structuring, and restructuring data using techniques like
normalization, aggregation, and feature engineering to improve its quality
Types:
1. Simple Data Transformations involve basic tasks like cleansing, standardization,
aggregation, and filtering used to prepare data for analysis
2. Complex Data Transformations it performs advanced tasks like integration, migration,
replication, and improvement. They require techniques such as data modeling,
mapping, and validation, and are used to prepare data for machine learning
Uses of data transformation:
Data transformation works on the simple objective of extracting data from a source,
converting it into a usable format and then delivering the converted data to the destination
system.
Removing duplicates:
In data science, removing duplicates is a key data cleaning step using functions
like drop_duplicates() in Python's Pandas library, which can operate on all columns or a
specified subset. Other tools like SQL use ROW_NUMBER() or COUNT() to identify and
remove duplicates, ensuring data integrity and improving query performance.
In Python (Pandas)
Remove duplicates from the entire DataFrame:
Use df.drop_duplicates(inplace=True) to modify the DataFrame in place,
or df_no_duplicates = df.drop_duplicates() to create a new DataFrame with duplicates
removed.
Remove duplicates based on specific columns:
Use the subset parameter, for example, df.drop_duplicates(subset=['column1', 'column2'],
inplace=True) to remove duplicates based on unique combinations of those columns
In SQL
Delete all rows where the row number is greater than 1, leaving only one instance of each
duplicate.
Alternatively, use a COUNT() function to identify duplicate rows and then delete them.
Ex: identifying duplicates
ID Name Age
1 Ravi 25
2 Giri 30
2 Giri 30
3 Mohan 35
Program:
import pandas as pd
df = [Link]({
'ID': [1, 2, 2, 3],
'Name': ['Ravi', 'Giri', 'Giri', 'Mohan'],
'Age': [25, 30, 30, 35]
})
# Check duplicates
print([Link]()) # Boolean mask (True = duplicate)
print([Link]().sum()) # Count of duplicates
output:
0 False
1 False
2 True
3 False
dtype: bool
1
Transforming Data Using a Function or Mapping in data science
Data transformation using a function or mapping in data science involves converting
raw data into a suitable format for analysis and modeling. This process is crucial for ensuring
data quality, consistency, and compatibility with various tools and algorithms.
Data transfer using functions:
In data science, transforming data using a function or mapping refers to modifying the data
to improve analysis, modeling, or interpretation.
In data science, functions are fundamental for transferring and manipulating data
through various stages of analysis. This transfer can occur in several ways, primarily by
passing data as arguments to functions and receiving processed data as return values.
Ex:
import pandas as pd
def process_data(dataframe, column_name):
# Perform operations on the 'dataframe' using 'column_name'
processed_column = dataframe[column_name] * 2
return processed_column
data = {'A': [1, 2, 3], 'B': [4, 5, 6]}
df = [Link](data)
result1 = process_data(df, 'A')
result2 = process_data(df, 'B')
print(result1)
print(result2)
Output:
PS C: \Demo> py [Link]
0 2
1 4
2 6
Name: A, dtype: int64
0 8
1 10
2 12
Name: B, dtype: int64
Transforming Data Using a Function or Mapping:
Transforming data using a function or mapping is a fundamental process in data
science, enabling the modification of raw data into a more suitable format for analysis,
modeling, or integration. This includes applying specific operations to individual data points
or entire datasets.
Ex:
Using Functions for Data Transformation:
Functions are reusable blocks of code that perform a specific task. In data transformation,
functions can be applied to:
Feature Engineering: Creating new features from existing ones. For example, calculating the
ratio of two columns or extracting the day of the week from a timestamp.
Python
import pandas as pd
df = [Link]({'price': [100, 150, 200], 'quantity': [2, 3, 1]})
df['total_cost'] = df['price'] * df['quantity']
print(df)
Data Cleaning: Handling missing values, outliers, or inconsistent data. For instance,
replacing missing values with the mean or median, or standardizing text data.
Python
import numpy as np
data = [Link]([1, 2, [Link], 4])
data_filled = [Link]([Link]())
print(data_filled)
Using Mapping for Data Transformation:
Mapping involves establishing a correspondence between values in one dataset and
values in another, or between different representations of the same data.
Ex:
# Using map to transform values
df = [Link]({'Grade': ['A', 'B', 'C']})
grade_map = {'A': 90, 'B': 80, 'C': 70}
df['Score'] = df['Grade'].map(grade_map)
print(df)
output:
Grade Score
0 A 90
1 B 80
2 C 70
Replacing Values
Replacing values in data science is a common and essential task, particularly during
data cleaning and preprocessing. It involves substituting specific values within a dataset with
other values, often to handle missing data, correct errors, standardize categories, or
transform variables for analysis.
Ex:
import pandas as pd
df = [Link]({'col1': [1, 2, 3, 2], 'col2': ['A', 'B', 'C', 'A']})
print("before replace")
print(df)
[Link](2, 5, inplace=True) # Replace all occurrences of 2 with 5
[Link]({'A': 'X', 'B': 'Y'}, inplace=True) # Replace 'A' with 'X' and 'B' with 'Y'
print("After replacement")
print(df)
output:
before replace
col1 col2
0 1 A
1 2 B
2 3 C
3 2 A
After replacement
col1 col2
0 1 X
1 5 Y
2 3 C
3 5 X
Detecting and Filtering Outliers
Detecting and filtering outliers in data science involves using statistical methods like
Z-score and the Interquartile Range (IQR), and visualization techniques such as box plots and
scatter plots. Machine learning algorithms like Isolation Forest and DBSCAN are used for
more complex datasets, while filtering involves either removing outliers or using robust
methods like Winsorisation or imputation.
Need for Detect Outliers?
Improve model accuracy: Outliers can skew model training, especially for algorithms
like linear regression or k-means.
Enhance data quality: Cleaning data ensures more reliable results.
Prevent misleading insights: A few extreme values can distort statistical summaries.
Methods to Detect Outliers
1. Statical methods
2. Visualization based methods
Statistical methods
Z-score : Identifies outliers as data points that are a certain number of standard
deviations away from the mean, typically three. This is most effective for data that is
normally distributed.
Interquartile Range (IQR) : Defines outliers as data points that fall below
Q1−1.5×IQRcap Q 1 minus 1.5 cross cap I cap Q cap R
𝑄1−1.5×𝐼𝑄𝑅
Visualization techniques
Box Plots : Visually represent the distribution of data, with outliers often shown as
individual points beyond the "whiskers".
Scatter Plots : Useful for two-dimensional data, scatter plots can help identify points
that do not follow the general pattern of the data cluster.
Histograms : These can show outliers as isolated bars far from the main body of the
distribution.
Example using IQR:
Q1 = df['Age'].quantile(0.25)
Q3 = df['Age'].quantile(0.75)
IQR = Q3 - Q1
# Filtering outliers
filtered_df = df[(df['Age'] >= Q1 - 1.5 * IQR) & (df['Age'] <= Q3 + 1.5 * IQR)]
Plotting with Pandas (and Matplotlib)
Pandas, a fundamental library in data science for Python, offer a convenient and
efficient way to create various plots directly from DataFrame and Series objects. These
plotting functionalities are built on top of Matplotlib, acting as a user-friendly wrapper that
simplifies the visualization process.
Plotting with Pandas:
import pandas as pd
import [Link] as plt
# Sample data
df = [Link]({
'Year': [2020, 2021, 2022],
'Sales': [100, 150, 200],
'Profit': [20, 30, 50]
})
Line Plot
A line plot is a fundamental data visualization tool in data science used to display
information as a series of data points called "markers" connected by straight line segments.
It’s especially useful for showing trends over time (i.e., time series data).
Ex:
import pandas as pd
import [Link] as plt
# Create a DataFrame
df = [Link]({
"Year": [2018, 2019, 2020, 2021, 2022],
"Sales": [100, 120, 90, 140, 160]
})
# Line plot directly from DataFrame
[Link](x="Year", y="Sales", kind="line", marker="o", color="green", title="Sales Over Years")
[Link]()
Outut:
Bar Plot
A bar plot uses rectangular bars to represent the magnitude of values for different
categories.
The length/height of the bar is proportional to the data value.
Useful for comparison across categories.
Examples in Data Science:
Sales per region
Number of customers in different age groups
Average scores across subjects
Frequency of categories in a dataset
Ex:
import pandas as pd
import [Link] as plt
# Create DataFrame
df = [Link]({
"Category": ["A", "B", "C", "D"],
"Value": [23, 17, 35, 29]
})
# Bar plot
[Link](x="Category", y="Value", kind="bar", color="orange", legend=False)
[Link]("Bar Plot Example")
[Link]()
Output:
[Link](x='Year', y='Sales', kind='bar')
[Link]('Sales Bar Chart')
[Link]()
Histogram and Density Plot
Histograms and density plots are data visualization tools used to show the
distribution of a numeric variable. A histogram uses bars to represent the frequency of data
points within specific bins, while a density plot uses a smooth curve to estimate the
probability distribution of the data. Density plots are useful for comparing distributions
across categories
Histogram
A bar chart that groups data into consecutive, non-overlapping intervals called
"bins". It shows the height of each bar indicates the count or frequency of data points that
fall into that bin
Density plot
A smoothed, continuous curve created using a method called Kernel Density
Estimation (KDE). It shows the height of the curve at any point represents the probability
density, with peaks indicating where values are most concentrated.
Ex:
# Histogram
df['Sales'].plot(kind='hist', bins=5)
[Link]('Sales Histogram')
[Link]()
# Density plot (KDE)
df['Sales'].plot(kind='kde')
[Link]('Sales Density Plot')
[Link]()
Scatter or Point Plot
In data science, a scatter plot, also known as a scattergram or scatter chart, is a
fundamental data visualization tool used to display the relationship between two numerical
variables. Each point on the plot represents an individual data point, with its position
determined by the values of the two variables on the horizontal (x) and vertical (y) axes.
Ex:
[Link](kind='scatter', x='Sales', y='Profit')
[Link]('Sales vs Profit')
[Link]()