Data Cleaning Report
Amazon Sales Dataset: Data Cleaning Report
Chris Hoang · 2026 · dataset from Kaggle ↗
A step-by-step audit of a messy Amazon product-and-reviews export, following the DataCamp Data Cleaning Checklist end to end: data constraints, text and categorical data, uniformity, and missing data. Every check is documented, including the ones that turned up nothing to fix, so the notebook reads as a complete record rather than a highlight reel.
Rows, raw → cleaned
1,465 → 1,351
Columns, raw → cleaned
16 → 18
Duplicate IDs resolved
92
Values imputed
3
The Cleaning Checklist, Step by Step
Data Constraints
Data type constraints
discounted_price, actual_price, discount_percentage, rating, and rating_count all loaded as text, because of embedded ₹ symbols, thousands separators, and % signs.
Stripped the symbols and cast all five columns to float64.
# Price columns: remove currency symbol and thousands separator, then cast to float
for col in ['discounted_price', 'actual_price']:
df[col] = df[col].str.replace('₹', '', regex=False).str.replace(',', '', regex=False).astype(float)
# Percentage column: remove trailing '%', cast to float
df['discount_percentage'] = df['discount_percentage'].str.replace('%', '', regex=False).astype(float)
# Rating: cast to float, coercing the malformed entry found below to NaN instead of erroring
df['rating'] = pd.to_numeric(df['rating'], errors='coerce')
# Rating count: remove thousands separator, coerce to float (handles the 2 missing values found below)
df['rating_count'] = pd.to_numeric(df['rating_count'].str.replace(',', '', regex=False), errors='coerce')Data range constraints
Checked rating in [0, 5], discount_percentage in [0, 100], and both price columns > 0.
All values were in range once parsed. Nothing to clip or drop.
Uniqueness constraints
0 exact duplicate rows. But 92 products appeared on more than one row: investigation showed each row carried a different batch of customer reviews for the same product, not a redundant copy.
Collapsed to one row per product with a per-column aggregation rule: rating averaged, rating_count averaged (a cumulative snapshot, not a per-row delta, so summing would have inflated it roughly 3x), review_id replaced by a summed review_count, free-text review fields concatenated, and near-constant fields (name, category, prices) taken from the first row. Result: 1,465 raw rows collapsed to 1,351 rows, one per product.
# Per-row review count, computed before collapsing (each row's review_id is a comma-separated list)
df['review_count'] = df['review_id'].str.split(',').apply(len)
agg_rules = {
'product_name': 'first',
'category': 'first',
'discounted_price': 'first',
'actual_price': 'first',
'discount_percentage': 'first',
'about_product': 'first',
'img_link': 'first',
'product_link': 'first',
'rating': lambda s: round(s.mean(), 1),
# np.round (not the builtin round) so an all-missing group's NaN mean passes through
# cleanly instead of raising when converting NaN to int
'rating_count': lambda s: np.round(s.mean()),
'review_count': 'sum',
'user_id': lambda s: ','.join(s),
'user_name': lambda s: ','.join(s),
'review_title': lambda s: ','.join(s),
'review_content': lambda s: ','.join(s),
}
df = df.groupby('product_id', as_index=False).agg(agg_rules)Derived column
The kept discount_percentage was worth cross-checking against the final price pair per product.
Added discount_percentage_calculated, computed as (actual_price minus discounted_price) divided by actual_price times 100, rounded to 2 decimals.
Text & Categorical Data
Membership constraints for categorical data
Checked the top-level category segment for spelling/casing inconsistencies.
Found exactly 9 distinct, consistently-spelled categories. No remapping needed.
Length violation for text data
Checked product_id against the fixed 10-character Amazon ASIN format.
All values conform. Nothing to fix.
id_lengths = df['product_id'].str.len()
print(id_lengths.value_counts())
malformed_ids = df[~df['product_id'].str.match(r'^[A-Z0-9]{10}$')]
print(f'product_id values not matching the 10-char ASIN pattern: {len(malformed_ids)}')Inconsistent formatting
Checked for leading/trailing whitespace and inconsistent delimiter use in product_name and category.
None found. Added category_main (just the top-level category) as a convenience column so downstream grouping doesn't require re-parsing the pipe-delimited hierarchy.
Data Uniformity
Unit uniformity (numeric columns)
Verified every price used the same currency symbol before conversion.
No mixed currencies. Ratings consistently 0 to 5, discounts consistently 0 to 100%.
Unit uniformity (date columns)
This dataset has no date/datetime columns.
N/A.
Crossfield validation (numeric columns)
Checked (a) discounted_price never exceeds actual_price, and (b) the stated discount_percentage matches the price-implied value.
Zero violations on both counts.
price_violation = df[df['discounted_price'] > df['actual_price']]
print(f'Rows where discounted_price > actual_price: {len(price_violation)}')
implied_discount_pct = ((df['actual_price'] - df['discounted_price']) / df['actual_price'] * 100).round(0)
discount_pct_mismatch = df[(implied_discount_pct - df['discount_percentage']).abs() > 1]
print(f'Rows where stated discount_percentage differs from the price-implied value by >1pt: {len(discount_pct_mismatch)}')Crossfield validation (date columns)
No date columns to cross-validate.
N/A.
Missing Data
Two columns carried missing or malformed values after type conversion. rating had 1 row with the literal string "|" instead of a number (a scraping artifact; the product had 992 ratings, so it clearly had a real average that failed to scrape), and rating_count had 2 blank rows, though both products had a valid rating, implying the same class of scraping gap.
Classification
Each gap affected only 1 to 2 rows (0.1% of the dataset), isolated to scrape failures rather than a pattern tied to other attributes.
Classified as Missing Completely at Random (MCAR).
Treatment
Every other column for the affected rows was intact.
Imputed both columns with the column median rather than dropping the rows.
rating_median = df['rating'].median()
rating_count_median = df['rating_count'].median()
df['rating'] = df['rating'].fillna(rating_median)
df['rating_count'] = df['rating_count'].fillna(rating_count_median)Summary of Changes
| Checklist item | Finding | Action taken |
|---|---|---|
| Data types | 5 columns stored as text | Converted to float64 |
| Range constraints | All values in valid bounds | None needed |
| Uniqueness | 92 duplicate product_ids (distinct reviews per row) | Collapsed to 1 row/product with per-column aggregation rules |
| Derived column | N/A | Added discount_percentage_calculated |
| Categorical membership | 9 clean top-level categories | None needed |
| Text length | All ASINs valid | None needed |
| Text formatting | No whitespace/delimiter issues | None needed; added category_main |
| Unit uniformity | Single currency, consistent scales | None needed |
| Crossfield validation | Prices & discounts internally consistent | None needed |
| Missing data | 3 cells (1 rating, 2 rating_count), MCAR | Imputed with column median |
Every check above, including the ones that found nothing, runs in the notebook against the raw file, end to end.