1. Import Libraries¶
Before performing any data analysis, we need to import the required Python libraries. These libraries provide functionalities for data manipulation, numerical computation, and visualization.
Libraries Used¶
- Pandas (
pd) – Data loading, cleaning, manipulation, and analysis. - NumPy (
np) – Efficient numerical operations and mathematical computations. - Matplotlib (
plt) – Data visualization using charts and plots. - Pathlib – Platform-independent file and directory handling.
Why are libraries important?¶
Instead of implementing algorithms from scratch, Python libraries provide optimized and well-tested implementations, allowing us to focus on solving machine learning problems efficiently.
Learning Outcomes¶
After completing this section, you will be able to:
- Import commonly used Python libraries.
- Understand the purpose of each library.
- Configure display settings for data exploration.
In [1]:
# ==========================================================
# ML Engineering Lab - Data Collection & Data Validation
# Dataset: Olist E-commerce
# ==========================================================
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from pathlib import Path
pd.set_option("display.max_columns", None)
pd.set_option("display.max_rows", 10)
2. Load Dataset¶
Machine Learning projects begin by collecting and loading data from one or more sources. In this lab, we will use the Olist Brazilian E-commerce Dataset, which consists of multiple relational tables such as customers, orders, products, payments, reviews, and sellers.
Data Collection Pipeline¶
CSV Files
↓
Read using Pandas
↓
DataFrame
↓
Exploration & Validation
Why load data into DataFrames?¶
A DataFrame is a two-dimensional tabular data structure provided by Pandas that enables efficient data manipulation.
If a dataset contains
- n rows (records)
- m columns (features)
then the dataset can be represented as
$ D \in \mathbb{R}^{n \times m} $
where
- (n) = number of observations
- (m) = number of attributes
Learning Outcomes¶
- Load CSV files using Pandas.
- Understand structured tabular datasets.
- Explore relational datasets.
In [2]:
DATA_PATH = "/kaggle/input/datasets/organizations/olistbr/brazilian-ecommerce/"
customers = pd.read_csv(DATA_PATH + "olist_customers_dataset.csv")
orders = pd.read_csv(DATA_PATH + "olist_orders_dataset.csv")
products = pd.read_csv(DATA_PATH + "olist_products_dataset.csv")
payments = pd.read_csv(DATA_PATH + "olist_order_payments_dataset.csv")
In [45]:
# TODO
# Load the following datasets
# sellers
# reviews
# order_items
# Write your code below
# Sellers dataset
sellers = pd.read_csv(DATA_PATH + "olist_sellers_dataset.csv")
# Reviews dataset
reviews = pd.read_csv(DATA_PATH + "olist_order_reviews_dataset.csv")
# Order Items dataset
order_items = pd.read_csv(DATA_PATH + "olist_order_items_dataset.csv")
# (Optional) Verify that all datasets have been loaded successfully
print("Customers :", customers.shape)
print("Orders :", orders.shape)
print("Products :", products.shape)
print("Payments :", payments.shape)
print("Sellers :", sellers.shape)
print("Reviews :", reviews.shape)
print("OrderItems:", order_items.shape)
Customers : (99441, 5) Orders : (99441, 8) Products : (32951, 9) Payments : (103886, 5) Sellers : (3095, 4) Reviews : (99224, 7) OrderItems: (112650, 7)
3. First Look at Data¶
Before cleaning or analyzing a dataset, it is essential to understand its structure. Exploratory Data Analysis (EDA) begins with answering questions such as:
- How many rows are present?
- How many columns exist?
- What are the feature names?
- Which columns contain numerical or categorical data?
Common Inspection Functions¶
head()tail()info()describe()
Why is this important?¶
Early inspection helps identify
- missing values
- incorrect data types
- unexpected columns
- potential quality issues
Learning Outcomes¶
- Inspect datasets.
- Interpret DataFrame information.
- Understand feature distributions.
In [46]:
customers.head()
Out[46]:
| customer_id | customer_unique_id | customer_zip_code_prefix | customer_city | customer_state | |
|---|---|---|---|---|---|
| 0 | 06b8999e2fba1a1fbc88172c00ba8bc7 | 861eff4711a542e4b93843c6dd7febb0 | 14409 | franca | SP |
| 1 | 18955e83d337fd6b2def6b18a428ac77 | 290c77bc529b7ac935b93aa66c333dc3 | 9790 | sao bernardo do campo | SP |
| 2 | 4e7b3e00288586ebd08712fdd0374a03 | 060e732b5b29e8181a18229c7b0b2b5e | 1151 | sao paulo | SP |
| 3 | b2b6027bc5c5109e529d4dc6358b12c3 | 259dac757896d24d7702b9acbbff3f3c | 8775 | mogi das cruzes | SP |
| 4 | 4f2d8ab171c80ec8364f7c12e35b23ad | 345ecd01c38d18a9036ed96c73b8d066 | 13056 | campinas | SP |
In [47]:
customers.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 99441 entries, 0 to 99440 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 customer_id 99441 non-null object 1 customer_unique_id 99441 non-null object 2 customer_zip_code_prefix 99441 non-null int64 3 customer_city 99441 non-null object 4 customer_state 99441 non-null object dtypes: int64(1), object(4) memory usage: 3.8+ MB
In [48]:
customers.describe(include="all")
Out[48]:
| customer_id | customer_unique_id | customer_zip_code_prefix | customer_city | customer_state | |
|---|---|---|---|---|---|
| count | 99441 | 99441 | 99441.000000 | 99441 | 99441 |
| unique | 99441 | 96096 | NaN | 4119 | 27 |
| top | 274fa6071e5e17fe303b9748641082c8 | 8d50f5eadf50201ccdcedfb9e2ac8455 | NaN | sao paulo | SP |
| freq | 1 | 17 | NaN | 15540 | 41746 |
| mean | NaN | NaN | 35137.474583 | NaN | NaN |
| ... | ... | ... | ... | ... | ... |
| min | NaN | NaN | 1003.000000 | NaN | NaN |
| 25% | NaN | NaN | 11347.000000 | NaN | NaN |
| 50% | NaN | NaN | 24416.000000 | NaN | NaN |
| 75% | NaN | NaN | 58900.000000 | NaN | NaN |
| max | NaN | NaN | 99990.000000 | NaN | NaN |
11 rows × 5 columns
In [49]:
# TODO
# Display
# first 10 rows of orders
# information of products
# summary statistics of payments
# Display the first 10 rows of the Orders dataset
orders.head(10)
# Display information about the Products dataset
products.info()
# Display summary statistics of the Payments dataset
payments.describe(include="all")
<class 'pandas.core.frame.DataFrame'> RangeIndex: 32951 entries, 0 to 32950 Data columns (total 9 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 product_id 32951 non-null object 1 product_category_name 32341 non-null object 2 product_name_lenght 32341 non-null float64 3 product_description_lenght 32341 non-null float64 4 product_photos_qty 32341 non-null float64 5 product_weight_g 32949 non-null float64 6 product_length_cm 32949 non-null float64 7 product_height_cm 32949 non-null float64 8 product_width_cm 32949 non-null float64 dtypes: float64(7), object(2) memory usage: 2.3+ MB
Out[49]:
| order_id | payment_sequential | payment_type | payment_installments | payment_value | |
|---|---|---|---|---|---|
| count | 103886 | 103886.000000 | 103886 | 103886.000000 | 103886.00000 |
| unique | 99440 | NaN | 5 | NaN | NaN |
| top | fa65dad1b0e818e3ccc5cb0e39231352 | NaN | credit_card | NaN | NaN |
| freq | 29 | NaN | 76795 | NaN | NaN |
| mean | NaN | 1.092679 | NaN | 2.853349 | 154.10038 |
| ... | ... | ... | ... | ... | ... |
| min | NaN | 1.000000 | NaN | 0.000000 | 0.00000 |
| 25% | NaN | 1.000000 | NaN | 1.000000 | 56.79000 |
| 50% | NaN | 1.000000 | NaN | 1.000000 | 100.00000 |
| 75% | NaN | 1.000000 | NaN | 4.000000 | 171.83750 |
| max | NaN | 29.000000 | NaN | 24.000000 | 13664.08000 |
11 rows × 5 columns
4. Dataset Shape¶
The shape of a dataset represents its dimensions. If a dataset contains
- (n) rows
- (m) columns
then
$ Shape=(n,m) $
Example
(99441, 8)
means
- 99,441 records
- 8 features
Why is shape important?¶
The dimensions of the dataset help estimate
- computational cost
- memory usage
- model complexity
Learning Outcomes¶
- Determine dataset dimensions.
- Interpret rows and columns correctly.
In [50]:
print(customers.shape)
(99441, 5)
In [51]:
# TODO
# Print shape of
# orders
# sellers
# reviews
# Shape of Customers dataset
print("Customers:", customers.shape)
# Orders dataset
print("Orders :", orders.shape)
# Sellers dataset
print("Sellers :", sellers.shape)
# Reviews dataset
print("Reviews :", reviews.shape)
Customers: (99441, 5) Orders : (99441, 8) Sellers : (3095, 4) Reviews : (99224, 7)
In [ ]:
import numpy as np
import pandas as pd
def inject_olist_impurities(
df,
dataset_type="customers",
noise_ratio=0.05,
duplicate_ratio=0.02,
outlier_ratio=0.02,
seed=42,
):
"""Injects domain errors, missing values, duplicates, and IQR quartile outliers into Olist datasets."""
np.random.seed(seed)
df_dirty = df.copy()
# Helper: Sample random indices
def get_random_indices(sample_size):
return np.random.choice(
df_dirty.index,
size=min(sample_size, len(df_dirty)),
replace=False,
)
# Helper: Inject extreme upper-bound quartile outliers (Q3 + multiplier * IQR)
def inject_iqr_outlier(col, sample_size, multiplier=10.0):
valid_series = df_dirty[col].dropna()
if valid_series.empty or not pd.api.types.is_numeric_dtype(
valid_series
):
return
q1 = valid_series.quantile(0.25)
q3 = valid_series.quantile(0.75)
iqr = q3 - q1
spread = iqr if iqr > 0 else (valid_series.std() or 100.0)
extreme_val = q3 + (multiplier * spread)
outlier_idx = get_random_indices(sample_size)
df_dirty.loc[outlier_idx, col] = extreme_val
n_total_noise = int(len(df_dirty) * noise_ratio)
n_outliers = int(len(df_dirty) * outlier_ratio)
# 1. Dataset-Specific Domain Errors & Quartile Outliers
if dataset_type == "customers":
n_per_error = n_total_noise // 3
df_dirty.loc[get_random_indices(n_per_error), "customer_state"] = (
np.nan
)
df_dirty.loc[
get_random_indices(n_per_error), "customer_zip_code_prefix"
] = np.nan
city_idx = get_random_indices(n_per_error)
df_dirty.loc[city_idx, "customer_city"] = (
df_dirty.loc[city_idx, "customer_city"].astype(str) + " "
)
inject_iqr_outlier(
"customer_zip_code_prefix", n_outliers, multiplier=10.0
)
elif dataset_type == "orders":
n_per_error = n_total_noise // 2
df_dirty.loc[
get_random_indices(n_per_error), "order_delivered_customer_date"
] = "2010-01-01 00:00:00"
df_dirty.loc[
get_random_indices(n_per_error), "order_status"
] = "lost_in_transit"
elif dataset_type == "products":
n_per_error = n_total_noise // 2
df_dirty.loc[get_random_indices(n_per_error), "product_weight_g"] = (
-500.0
)
df_dirty.loc[
get_random_indices(n_per_error), "product_category_name"
] = np.nan
inject_iqr_outlier("product_weight_g", n_outliers, multiplier=15.0)
inject_iqr_outlier("product_length_cm", n_outliers, multiplier=15.0)
elif dataset_type == "payments":
n_per_error = n_total_noise // 2
df_dirty.loc[get_random_indices(n_per_error), "payment_value"] = -100.0
df_dirty.loc[
get_random_indices(n_per_error), "payment_type"
] = "crypto_token"
inject_iqr_outlier("payment_value", n_outliers, multiplier=15.0)
inject_iqr_outlier("payment_installments", n_outliers, multiplier=10.0)
# 2. Inject Duplicate Rows
if duplicate_ratio > 0:
n_duplicates = int(len(df_dirty) * duplicate_ratio)
if n_duplicates > 0:
dup_indices = get_random_indices(n_duplicates)
duplicate_rows = df_dirty.loc[dup_indices].copy()
df_dirty = pd.concat(
[df_dirty, duplicate_rows], ignore_index=True
)
return df_dirty
# Execution Example:
customers = inject_olist_impurities(
customers, "customers", noise_ratio=0.05, duplicate_ratio=0.02
)
payments = inject_olist_impurities(
payments, "payments", noise_ratio=0.05, duplicate_ratio=0.02
)
products = inject_olist_impurities(
products, "products", noise_ratio=0.05, duplicate_ratio=0.02
)
orders = inject_olist_impurities(
orders, "orders", noise_ratio=0.05, duplicate_ratio=0.02
)
In [ ]:
# Overwrite your original DataFrames with corrupted data
customers = inject_olist_impurities(
customers, dataset_type="customers", noise_ratio=0.1
)
orders = inject_olist_impurities(
orders, dataset_type="orders", noise_ratio=0.1
)
products = inject_olist_impurities(
products, dataset_type="products", noise_ratio=0.1
)
payments = inject_olist_impurities(
payments, dataset_type="payments", noise_ratio=0.1
)
5. Missing Values¶
Real-world datasets often contain missing or unavailable values. Missing values may occur due to
- sensor failures
- incomplete user input
- transmission errors
- unavailable information
Mathematical Representation¶
Suppose
$ D = \{x_1,x_2,\ldots,x_n\} $
A missing value is represented as
$ x_i = \text{NaN} $
The percentage of missing values in a feature is
$ Missing(\%)= \frac{\text{Number of Missing Values}} {\text{Total Number of Records}} \times100 $
Why does it matter?¶
Missing values can
- reduce model accuracy
- introduce bias
- prevent algorithms from training
Learning Outcomes¶
- Detect missing values.
- Calculate missing percentages.
- Decide appropriate handling techniques.
In [52]:
customers.isnull().sum()
Out[52]:
customer_id 0 customer_unique_id 0 customer_zip_code_prefix 0 customer_city 0 customer_state 0 dtype: int64
In [53]:
customers.isnull().sum().sort_values(ascending=False)
Out[53]:
customer_id 0 customer_unique_id 0 customer_zip_code_prefix 0 customer_city 0 customer_state 0 dtype: int64
In [54]:
# TODO
# Find missing values in
# orders
# products
# reviews
# Missing values in Customers dataset
customers.isnull().sum()
# Missing values sorted in descending order
customers.isnull().sum().sort_values(ascending=False)
# Orders dataset
print("Missing Values in Orders")
print(orders.isnull().sum().sort_values(ascending=False))
print("\n" + "="*50 + "\n")
# Products dataset
print("Missing Values in Products")
print(products.isnull().sum().sort_values(ascending=False))
print("\n" + "="*50 + "\n")
# Reviews dataset
print("Missing Values in Reviews")
print(reviews.isnull().sum().sort_values(ascending=False))
Missing Values in Orders order_delivered_customer_date 2965 order_delivered_carrier_date 1783 order_approved_at 160 order_id 0 order_purchase_timestamp 0 order_status 0 customer_id 0 order_estimated_delivery_date 0 dtype: int64 ================================================== Missing Values in Products product_category_name 610 product_description_lenght 610 product_name_lenght 610 product_photos_qty 610 product_weight_g 2 product_height_cm 2 product_length_cm 2 product_width_cm 2 product_id 0 dtype: int64 ================================================== Missing Values in Reviews review_comment_title 87656 review_comment_message 58247 review_id 0 review_score 0 order_id 0 review_creation_date 0 review_answer_timestamp 0 dtype: int64
In [55]:
missing = customers.isnull().mean()*100
missing.sort_values(ascending=False)
Out[55]:
customer_id 0.0 customer_unique_id 0.0 customer_zip_code_prefix 0.0 customer_city 0.0 customer_state 0.0 dtype: float64
In [56]:
# TODO
# Calculate percentage missing values
# for products dataset
missing_products = products.isnull().mean() * 100
missing_products.sort_values(ascending=False)
Out[56]:
product_category_name 1.851234 product_description_lenght 1.851234 product_name_lenght 1.851234 product_photos_qty 1.851234 product_weight_g 0.006070 product_height_cm 0.006070 product_length_cm 0.006070 product_width_cm 0.006070 product_id 0.000000 dtype: float64
In [57]:
customers["customer_city"] = customers["customer_city"].fillna("Unknown")
In [58]:
# TODO
# Replace missing review comments
# with "No Review"
reviews["review_comment_message"] = reviews["review_comment_message"].fillna("No Review")
6. Duplicate Records¶
Duplicate records represent repeated observations in the dataset. These often arise from
- repeated logging
- synchronization failures
- system retries
- human errors
Mathematical Definition¶
Let
$ R_i=(x_1,x_2,\ldots,x_m) $
Two records are duplicates if
$ R_i = R_j $
where
$ i \neq j $
Why remove duplicates?¶
Duplicate samples
- distort statistics
- bias machine learning models
- increase storage requirements
Learning Outcomes¶
- Detect duplicate records.
- Remove redundant observations.
In [59]:
customers.duplicated().sum()
Out[59]:
np.int64(0)
In [60]:
customers = customers.drop_duplicates()
In [61]:
# TODO
# Check duplicates in
# products
# reviews
# sellers
# Products dataset
print("Products :", products.duplicated().sum())
# Reviews dataset
print("Reviews :", reviews.duplicated().sum())
# Sellers dataset
print("Sellers :", sellers.duplicated().sum())
# (Optional) Remove duplicate records
products = products.drop_duplicates()
reviews = reviews.drop_duplicates()
sellers = sellers.drop_duplicates()
Products : 0 Reviews : 0 Sellers : 0
7. Data Types¶
Every feature in a dataset has an associated data type. Common types include
- Integer
- Float
- Boolean
- String
- Datetime
Why is datatype validation important?¶
Incorrect data types can lead to
- computation errors
- visualization issues
- incorrect model inputs
Example
"100"
is a string,
whereas
100
is an integer.
Learning Outcomes¶
- Identify feature data types.
- Convert incorrect data types.
- Parse datetime columns.
In [62]:
orders.dtypes
Out[62]:
order_id object customer_id object order_status object order_purchase_timestamp datetime64[ns] order_approved_at datetime64[ns] order_delivered_carrier_date datetime64[ns] order_delivered_customer_date datetime64[ns] order_estimated_delivery_date datetime64[ns] dtype: object
In [63]:
orders["order_purchase_timestamp"] = pd.to_datetime(
orders["order_purchase_timestamp"]
)
In [64]:
# TODO
# Convert all timestamp columns
# into datetime
# Orders dataset timestamps
orders["order_approved_at"] = pd.to_datetime(
orders["order_approved_at"]
)
orders["order_delivered_carrier_date"] = pd.to_datetime(
orders["order_delivered_carrier_date"]
)
orders["order_delivered_customer_date"] = pd.to_datetime(
orders["order_delivered_customer_date"]
)
orders["order_estimated_delivery_date"] = pd.to_datetime(
orders["order_estimated_delivery_date"]
)
# Reviews dataset timestamp
reviews["review_creation_date"] = pd.to_datetime(
reviews["review_creation_date"]
)
reviews["review_answer_timestamp"] = pd.to_datetime(
reviews["review_answer_timestamp"]
)
# Order Items dataset timestamp
order_items["shipping_limit_date"] = pd.to_datetime(
order_items["shipping_limit_date"]
)
In [65]:
payments[payments["payment_value"] < 0]
Out[65]:
| order_id | payment_sequential | payment_type | payment_installments | payment_value |
|---|
In [66]:
# TODO
# Find
# invalid freight values
# invalid payment values
# Invalid freight values (negative freight charges)
order_items[order_items["freight_value"] < 0]
# Invalid payment values (negative payment amounts)
payments[payments["payment_value"] < 0]
Out[66]:
| order_id | payment_sequential | payment_type | payment_installments | payment_value |
|---|
In [67]:
orders["order_status"].unique()
Out[67]:
array(['delivered', 'invoiced', 'shipped', 'processing', 'unavailable',
'canceled', 'created', 'approved'], dtype=object)
In [68]:
# TODO
# Find unique values
# in customer_state
customers["customer_state"].nunique()
Out[68]:
27
In [69]:
orders["order_status"].value_counts()
Out[69]:
order_status delivered 96478 shipped 1107 canceled 625 unavailable 609 invoiced 314 processing 301 created 5 approved 2 Name: count, dtype: int64
In [70]:
# TODO
# Top 10 customer cities
top_cities = customers["customer_city"].value_counts().head(10)
print(top_cities)
customer_city sao paulo 15540 rio de janeiro 6882 belo horizonte 2773 brasilia 2131 curitiba 1521 campinas 1444 porto alegre 1379 salvador 1245 guarulhos 1189 sao bernardo do campo 938 Name: count, dtype: int64
8. Outlier Detection¶
Outliers are observations that differ significantly from the majority of the data.
Interquartile Range (IQR)¶
$ IQR=Q_3-Q_1 $
where
- (Q_1) = First Quartile
- (Q_3) = Third Quartile
A value is considered an outlier if
$ x<Q_1-1.5(IQR) $
or
$ x>Q_3+1.5(IQR) $
Why detect outliers?¶
Outliers can
- skew averages
- affect regression models
- reduce prediction performance
Learning Outcomes¶
- Visualize outliers.
- Compute IQR.
- Identify abnormal observations.
In [71]:
payments.boxplot(column="payment_value")
plt.show()
In [72]:
Q1 = payments["payment_value"].quantile(0.25)
Q3 = payments["payment_value"].quantile(0.75)
IQR = Q3 - Q1
outliers = payments[
(payments["payment_value"] < Q1 - 1.5*IQR) |
(payments["payment_value"] > Q3 + 1.5*IQR)
]
outliers.head()
Out[72]:
| order_id | payment_sequential | payment_type | payment_installments | payment_value | |
|---|---|---|---|---|---|
| 23 | 4d680edbaa7d3d9bed69532957368a03 | 1 | credit_card | 10 | 353.09 |
| 26 | d0a945f85ba1074b60aac97ade7e240e | 1 | credit_card | 2 | 541.00 |
| 52 | 2e2c60b99754ae1e4d8b18846cfec9f2 | 1 | credit_card | 4 | 542.66 |
| 54 | 95442deb81a5d91c97c0df96b431634a | 1 | boleto | 1 | 368.98 |
| 61 | 3ab4eea6776259ae8dfbabaeffece352 | 1 | credit_card | 8 | 387.80 |
In [73]:
# TODO
# Detect outliers
# in freight_value
Q1 = order_items["freight_value"].quantile(0.25)
Q3 = order_items["freight_value"].quantile(0.75)
IQR = Q3 - Q1
freight_outliers = order_items[
(order_items["freight_value"] < Q1 - 1.5 * IQR) |
(order_items["freight_value"] > Q3 + 1.5 * IQR)
]
# Display the first few outlier records
freight_outliers.head()
Out[73]:
| order_id | order_item_id | product_id | seller_id | shipping_limit_date | price | freight_value | |
|---|---|---|---|---|---|---|---|
| 7 | 000576fe39319847cbb9d288c5617fa6 | 1 | 557d850972a7d6f792fd18ae1400d9b6 | 5996cddab893a4652a15592fb58ab8db | 2018-07-10 12:30:45 | 810.00 | 70.75 |
| 22 | 000f25f4d72195062c040b12dce9a18a | 1 | 1c05e0964302b6cf68ca0d15f326c6ba | 7c67e1448b00f6e969d365cea6b010ab | 2018-03-21 11:10:11 | 119.99 | 44.40 |
| 39 | 0017afd5076e074a48f1f1a4c7bac9c5 | 1 | fe59a1e006df3ac42bf0ceb876d70969 | 25c5c91f63607446a97b143d2d535d31 | 2017-04-24 22:25:19 | 809.10 | 44.29 |
| 59 | 00254baeb6c932b0a8aeead91fbd02b5 | 1 | 18901878788fec7ddc55e64d1ace8187 | e8b4225284fbb02d16f200513f1f395d | 2018-05-14 22:14:46 | 149.90 | 43.11 |
| 73 | 002b430ff89b3a24c31a1170acbbedea | 1 | cc47c0863559499f01b4cad750929d10 | 7a67c85e85bb2ce8582c35f2203ad736 | 2017-06-26 22:10:14 | 199.99 | 65.56 |
9. Schema Validation¶
A schema defines the expected structure of a dataset. It specifies
- feature names
- data types
- constraints
Schema Definition¶
If
$ S=\{c_1,c_2,\ldots,c_m\} $
is the expected schema,
then
$ Columns(D)=S $
must hold for a valid dataset.
Why is schema validation important?¶
Schema validation prevents
- missing columns
- incorrect column names
- incompatible data types
Learning Outcomes¶
- Compare expected and actual schemas.
- Detect schema violations.
In [74]:
expected_columns = [
"order_id",
"customer_id",
"order_status",
"order_purchase_timestamp"
]
In [75]:
missing_columns = set(expected_columns) - set(orders.columns)
missing_columns
Out[75]:
set()
In [76]:
# TODO
# Check whether
# products dataset
# contains all expected columns
expected_columns = [
"order_id",
"customer_id",
"order_status",
"order_purchase_timestamp"
]
missing_columns = set(expected_columns) - set(orders.columns)
print("Missing columns in Orders:", missing_columns)
expected_product_columns = [
"product_id",
"product_category_name",
"product_name_lenght",
"product_description_lenght",
"product_photos_qty",
"product_weight_g",
"product_length_cm",
"product_height_cm",
"product_width_cm"
]
missing_product_columns = set(expected_product_columns) - set(products.columns)
print("Missing columns in Products:", missing_product_columns)
Missing columns in Orders: set() Missing columns in Products: set()
10. Cross-field Validation¶
Cross-field validation ensures logical consistency between multiple columns. Example:
For an e-commerce order, $ Purchase\ Date \leq Delivery\ Date $, must always hold.
If, $ Delivery\ Date < Purchase\ Date $, the record is invalid.
Why is this important?¶
Cross-field validation identifies
- impossible events
- timestamp inconsistencies
- business rule violations
Learning Outcomes¶
- Validate relationships across columns.
- Detect logically inconsistent records.
In [77]:
orders["order_purchase_timestamp"] = pd.to_datetime(
orders["order_purchase_timestamp"]
)
orders["order_delivered_customer_date"] = pd.to_datetime(
orders["order_delivered_customer_date"]
)
invalid = orders[
orders["order_delivered_customer_date"] <
orders["order_purchase_timestamp"]
]
invalid.head()
Out[77]:
| order_id | customer_id | order_status | order_purchase_timestamp | order_approved_at | order_delivered_carrier_date | order_delivered_customer_date | order_estimated_delivery_date |
|---|
In [78]:
# TODO
# Find orders
# where estimated delivery date
# is before purchase date
orders["order_estimated_delivery_date"] = pd.to_datetime(
orders["order_estimated_delivery_date"]
)
invalid_estimated_delivery = orders[
orders["order_estimated_delivery_date"] <
orders["order_purchase_timestamp"]
]
invalid_estimated_delivery.head()
Out[78]:
| order_id | customer_id | order_status | order_purchase_timestamp | order_approved_at | order_delivered_carrier_date | order_delivered_customer_date | order_estimated_delivery_date |
|---|
11. Data Summary Report¶
A validation report summarizes the overall quality of a dataset. Typical metrics include
- Number of rows
- Number of columns
- Missing values
- Duplicate records
- Data types
- Unique values
- Outliers
Why generate a report?¶
A summary report provides a quick overview of data quality before machine learning begins.
Learning Outcomes¶
- Build automated validation reports.
- Assess dataset readiness for ML pipelines.
In [79]:
summary = pd.DataFrame({
"Missing Values": orders.isnull().sum(),
"Data Type": orders.dtypes,
"Unique Values": orders.nunique()
})
summary
Out[79]:
| Missing Values | Data Type | Unique Values | |
|---|---|---|---|
| order_id | 0 | object | 99441 |
| customer_id | 0 | object | 99441 |
| order_status | 0 | object | 8 |
| order_purchase_timestamp | 0 | datetime64[ns] | 98875 |
| order_approved_at | 160 | datetime64[ns] | 90733 |
| order_delivered_carrier_date | 1783 | datetime64[ns] | 81018 |
| order_delivered_customer_date | 2965 | datetime64[ns] | 95664 |
| order_estimated_delivery_date | 0 | datetime64[ns] | 459 |
12. Lab Exercise¶
In [80]:
# ==========================================================
# 🏆 Lab Exercise: Data Collection & Data Validation
# ==========================================================
#
# Objective
# ---------
# Apply the concepts learned in this lab to perform a complete
# data validation workflow on one of the Olist e-commerce datasets.
#
# Choose dataset: reviews
In [81]:
# ✅ Task 1: Load the selected dataset.
#
# ✅ Task 2: Perform initial exploration.
# - Display the first few records.
# - Check dataset shape.
# - View column names and data types.
#
# ✅ Task 3: Identify missing values.
# - Count missing values.
# - Calculate the percentage of missing values.
#
# ✅ Task 4: Detect and remove duplicate records.
#
# ✅ Task 5: Validate the dataset.
# - Verify data types.
# - Check for invalid or inconsistent values.
#
# ✅ Task 6: Detect outliers.
# - Use the IQR method.
# - Visualize using a boxplot.
#
# ✅ Task 7: Generate a Data Quality Report.
# Include:
# • Number of rows
# • Number of columns
# • Missing values
# • Duplicate records
# • Unique values
# • Data types
# • Number of detected outliers
#
# ✅ Task 8: Save the cleaned dataset as a new CSV file.
In [6]:
# ==========================================================
# 🏆 Lab Exercise SOLUTION
# Dataset Selected: Reviews
# ==========================================================
import pandas as pd
import matplotlib.pyplot as plt
# ==========================================================
# Task 1: Load the Dataset
# ==========================================================
DATA_PATH = "/kaggle/input/datasets/organizations/olistbr/brazilian-ecommerce/"
reviews = pd.read_csv(DATA_PATH + "olist_order_reviews_dataset.csv")
# ==========================================================
# Task 2: Initial Exploration
# ==========================================================
print("=" * 50)
print("FIRST 5 RECORDS")
print("=" * 50)
print(reviews.head())
print("\nDataset Shape:")
print(reviews.shape)
print("\nColumn Names:")
print(reviews.columns.tolist())
print("\nData Types:")
print(reviews.dtypes)
# ==========================================================
# Task 3: Missing Values
# ==========================================================
print("\n" + "=" * 50)
print("MISSING VALUES")
print("=" * 50)
missing_count = reviews.isnull().sum()
missing_percentage = reviews.isnull().mean() * 100
print("Missing Value Count:")
print(missing_count)
print("\nMissing Percentage:")
print(missing_percentage.sort_values(ascending=False))
# ==========================================================
# Task 4: Duplicate Records
# ==========================================================
print("\n" + "=" * 50)
print("DUPLICATE RECORDS")
print("=" * 50)
duplicates = reviews.duplicated().sum()
print("Duplicate Records:", duplicates)
reviews = reviews.drop_duplicates()
print("Shape After Removing Duplicates:", reviews.shape)
# ==========================================================
# Task 5: Dataset Validation
# ==========================================================
print("\n" + "=" * 50)
print("DATA VALIDATION")
print("=" * 50)
print("\nData Types:")
print(reviews.dtypes)
# Convert timestamps
reviews["review_creation_date"] = pd.to_datetime(
reviews["review_creation_date"]
)
reviews["review_answer_timestamp"] = pd.to_datetime(
reviews["review_answer_timestamp"]
)
# Check invalid review scores
invalid_scores = reviews[
(reviews["review_score"] < 1) |
(reviews["review_score"] > 5)
]
print("\nInvalid Review Scores:", len(invalid_scores))
# Check invalid timestamps
invalid_dates = reviews[
reviews["review_answer_timestamp"] <
reviews["review_creation_date"]
]
print("Invalid Date Records:", len(invalid_dates))
# ==========================================================
# Task 6: Outlier Detection (IQR Method)
# ==========================================================
print("\n" + "=" * 50)
print("OUTLIER DETECTION")
print("=" * 50)
plt.figure(figsize=(6,4))
reviews.boxplot(column="review_score")
plt.title("Review Score")
plt.show()
Q1 = reviews["review_score"].quantile(0.25)
Q3 = reviews["review_score"].quantile(0.75)
IQR = Q3 - Q1
outliers = reviews[
(reviews["review_score"] < Q1 - 1.5 * IQR) |
(reviews["review_score"] > Q3 + 1.5 * IQR)
]
print("Number of Outliers:", len(outliers))
# ==========================================================
# Task 7: Data Quality Report
# ==========================================================
print("\n" + "=" * 50)
print("DATA QUALITY REPORT")
print("=" * 50)
print("Rows:", reviews.shape[0])
print("Columns:", reviews.shape[1])
print("\nMissing Values:")
print(reviews.isnull().sum().sum())
print("\nDuplicate Records:")
print(reviews.duplicated().sum())
print("\nUnique Values:")
print(reviews.nunique())
print("\nData Types:")
print(reviews.dtypes)
print("\nInvalid Review Scores:", len(invalid_scores))
print("Invalid Date Records:", len(invalid_dates))
print("Outliers (Review Score):", len(outliers))
# ==========================================================
# Task 8: Save the Cleaned Dataset
# ==========================================================
reviews.to_csv("olist_reviews_cleaned.csv", index=False)
print("\n✅ Cleaned dataset saved as 'olist_reviews_cleaned.csv'")
==================================================
FIRST 5 RECORDS
==================================================
review_id order_id \
0 7bc2406110b926393aa56f80a40eba40 73fc7af87114b39712e6da79b0a377eb
1 80e641a11e56f04c1ad469d5645fdfde a548910a1c6147796b98fdf73dbeba33
2 228ce5500dc1d8e020d8d1322874b6f0 f9e4b658b201a9f2ecdecbb34bed034b
3 e64fb393e7b32834bb789ff8bb30750e 658677c97b385a9be170737859d3511b
4 f7c4243c7fe1938f181bec41a392bdeb 8e6bfb81e283fa7e4f11123a3fb894f1
review_score review_comment_title \
0 4 NaN
1 5 NaN
2 5 NaN
3 5 NaN
4 5 NaN
review_comment_message review_creation_date \
0 NaN 2018-01-18 00:00:00
1 NaN 2018-03-10 00:00:00
2 NaN 2018-02-17 00:00:00
3 Recebi bem antes do prazo estipulado. 2017-04-21 00:00:00
4 Parabéns lojas lannister adorei comprar pela I... 2018-03-01 00:00:00
review_answer_timestamp
0 2018-01-18 21:46:59
1 2018-03-11 03:05:13
2 2018-02-18 14:36:24
3 2017-04-21 22:02:06
4 2018-03-02 10:26:53
Dataset Shape:
(99224, 7)
Column Names:
['review_id', 'order_id', 'review_score', 'review_comment_title', 'review_comment_message', 'review_creation_date', 'review_answer_timestamp']
Data Types:
review_id object
order_id object
review_score int64
review_comment_title object
review_comment_message object
review_creation_date object
review_answer_timestamp object
dtype: object
==================================================
MISSING VALUES
==================================================
Missing Value Count:
review_id 0
order_id 0
review_score 0
review_comment_title 87656
review_comment_message 58247
review_creation_date 0
review_answer_timestamp 0
dtype: int64
Missing Percentage:
review_comment_title 88.341530
review_comment_message 58.702532
review_id 0.000000
review_score 0.000000
order_id 0.000000
review_creation_date 0.000000
review_answer_timestamp 0.000000
dtype: float64
==================================================
DUPLICATE RECORDS
==================================================
Duplicate Records: 0
Shape After Removing Duplicates: (99224, 7)
==================================================
DATA VALIDATION
==================================================
Data Types:
review_id object
order_id object
review_score int64
review_comment_title object
review_comment_message object
review_creation_date object
review_answer_timestamp object
dtype: object
Invalid Review Scores: 0
Invalid Date Records: 0
==================================================
OUTLIER DETECTION
==================================================
Number of Outliers: 14575 ================================================== DATA QUALITY REPORT ================================================== Rows: 99224 Columns: 7 Missing Values: 145903 Duplicate Records: 0 Unique Values: review_id 98410 order_id 98673 review_score 5 review_comment_title 4527 review_comment_message 36159 review_creation_date 636 review_answer_timestamp 98248 dtype: int64 Data Types: review_id object order_id object review_score int64 review_comment_title object review_comment_message object review_creation_date datetime64[ns] review_answer_timestamp datetime64[ns] dtype: object Invalid Review Scores: 0 Invalid Date Records: 0 Outliers (Review Score): 14575 ✅ Cleaned dataset saved as 'olist_reviews_cleaned.csv'
In [83]:
# Deliverables
# ------------
# • Cleaned dataset (.csv)
# • Data Quality Report
# • Well-documented notebook with explanations
13. Final Challenge¶
In [84]:
# Create a reusable function:
#
# validate_dataset(df)
#
# that automatically performs all validation checks and
# returns a comprehensive summary report for any dataset.
In [7]:
# ==========================================================
# Reusable Dataset Validation Function
# ==========================================================
import pandas as pd
def validate_dataset(df):
"""
Performs a complete data validation workflow on any Pandas DataFrame.
Parameters
----------
df : pandas.DataFrame
Returns
-------
report : dict
Dictionary containing the data quality summary.
"""
report = {}
# ------------------------------------------------------
# Basic Information
# ------------------------------------------------------
report["Rows"] = df.shape[0]
report["Columns"] = df.shape[1]
# ------------------------------------------------------
# Missing Values
# ------------------------------------------------------
missing_count = df.isnull().sum()
missing_percentage = (df.isnull().mean() * 100).round(2)
report["Missing Values"] = missing_count
report["Missing Percentage"] = missing_percentage
# ------------------------------------------------------
# Duplicate Records
# ------------------------------------------------------
report["Duplicate Records"] = df.duplicated().sum()
# ------------------------------------------------------
# Data Types
# ------------------------------------------------------
report["Data Types"] = df.dtypes
# ------------------------------------------------------
# Unique Values
# ------------------------------------------------------
report["Unique Values"] = df.nunique()
# ------------------------------------------------------
# Invalid Numeric Values
# (Negative values)
# ------------------------------------------------------
invalid_values = {}
numeric_columns = df.select_dtypes(include=["int64", "float64"]).columns
for col in numeric_columns:
invalid_values[col] = (df[col] < 0).sum()
report["Invalid Numeric Values"] = invalid_values
# ------------------------------------------------------
# Outlier Detection (IQR)
# ------------------------------------------------------
outliers = {}
for col in numeric_columns:
Q1 = df[col].quantile(0.25)
Q3 = df[col].quantile(0.75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR
count = ((df[col] < lower) | (df[col] > upper)).sum()
outliers[col] = count
report["Outliers"] = outliers
return report
# ==========================================================
# Example Usage
# ==========================================================
report = validate_dataset(products)
print("=" * 60)
print("DATA QUALITY REPORT")
print("=" * 60)
print(f"Rows: {report['Rows']}")
print(f"Columns: {report['Columns']}")
print(f"Duplicate Records: {report['Duplicate Records']}")
print("\nMissing Values")
print(report["Missing Values"])
print("\nMissing Percentage (%)")
print(report["Missing Percentage"])
print("\nData Types")
print(report["Data Types"])
print("\nUnique Values")
print(report["Unique Values"])
print("\nInvalid Numeric Values")
print(report["Invalid Numeric Values"])
print("\nOutliers")
print(report["Outliers"])
============================================================
DATA QUALITY REPORT
============================================================
Rows: 99224
Columns: 7
Duplicate Records: 0
Missing Values
review_id 0
order_id 0
review_score 0
review_comment_title 87656
review_comment_message 58247
review_creation_date 0
review_answer_timestamp 0
dtype: int64
Missing Percentage (%)
review_id 0.00
order_id 0.00
review_score 0.00
review_comment_title 88.34
review_comment_message 58.70
review_creation_date 0.00
review_answer_timestamp 0.00
dtype: float64
Data Types
review_id object
order_id object
review_score int64
review_comment_title object
review_comment_message object
review_creation_date object
review_answer_timestamp object
dtype: object
Unique Values
review_id 98410
order_id 98673
review_score 5
review_comment_title 4527
review_comment_message 36159
review_creation_date 636
review_answer_timestamp 98248
dtype: int64
Invalid Numeric Values
{'review_score': np.int64(0)}
Outliers
{'review_score': np.int64(14575)}
In [ ]: