THE PROBLEM
Raw e-commerce data is messy. Blanks mean different things in different columns, the same city or state is spelled several ways, and dates and codes sit in the wrong data types. If none of that is fixed, totals get split and the analysis gives wrong answers.
The Work
I took a raw Amazon sales file of 128,975 order lines and 24 columns and cleaned it in Python with Pandas. No rows were removed.
Structure
Dropped two junk columns: leftover row numbers, and an unnamed column that only ever held "False"
Standardized every column name to lowercase with underscores
Checked for duplicate rows (none found)
Missing values, handled by what a blank actually means
Blank amount (7,795 rows) became 0, because it most likely means a cancelled order with no sale
Blank promotion (49,153 rows) became "No Promotion"
Blank courier status, fulfilled-by, currency and location fields became "Unknown"
Standardization
Fixed inconsistent casing in categories, states and cities
States: fixed typos, short codes and old names (Rajshthan and Rj became Rajasthan, Orissa became Odisha). The list went from 48 unique values to 37
Cities: merged duplicates such as Bangalore into Bengaluru and Gurgaon into Gurugram, which had been splitting city totals by thousands of orders
Data types
Converted the date column from text to a real date (0 failed conversions)
Converted postal codes from numbers to text, because they are labels, not values to calculate with
I checked the work by comparing counts before and after. For example, Bengaluru's 11,898 orders plus Bangalore's 1,362 matched the new total of 13,260.
THE RESULT
A clean file with zero missing values, ready for SQL analysis and a Power BI dashboard.
LIMITATION
About 7,000 rare city spellings remain. I standardized only the high-volume cities, since the rest have too few orders to affect results.
TOOLS
Python, Pandas, Jupyter Notebook
1 like · 1 view · Nov 22, 2026