Data Cleansing Pipeline for Messy Sales Exports

What you will learn

• Deduplicate records based on primary identifier columns using drop_duplicates()
• Clean currency and formatted text fields using vectorized regex str.replace()
• Parse heterogeneous date strings using pd.to_datetime(format='mixed')
• Handle missing categorical values cleanly using fillna('Unknown').str.strip().str.title()

What you need first

import pandas as pd
s = pd.Series(['₹1,200.50'])
print(s.str.replace(r'[^0-9.]', '', regex=True).iloc[0])

1200.50

import pandas as pd
df = pd.DataFrame({'id': [1, 1], 'cat': [None, 'Books']})
print(len(df.drop_duplicates(subset=['id']).fillna('Unknown')))

1

Architecture and data flow

The data

A raw vendor sales DataFrame containing unformatted amounts, mixed date styles, and duplicate records.

Step by step

Read each step, run its code, then check your output against the one shown.

1. 01

   Initialize raw messy DataFrame and inspect dirty fields

   We initialize the raw vendor dataset in Pandas with realistic data defects: currency symbols, commas, trailing whitespace, duplicated order ID 3, and mixed date styles ('2024-03-01' and '02/03/2024'). We print the raw DataFrame shape and column data types to inspect the starting state. In enterprise data pipelines, inspecting raw schema types before transformation prevents unexpected type coercion errors in downstream aggregation layers. We verify that each column exhibits expected dirty patterns and observe that string formatting characters prevent direct mathematical operations on monetary amounts.

   PythonStep 1Copy

   import pandas as pd
   
   raw = pd.DataFrame({
       "order_id": [1, 2, 3, 3, 4],
       "amount": ["₹1,200.50", " ₹850.00 ", "₹2,100.00", "₹2,100.00", None],
       "category": ["electronics", " Apparel ", None, None, "books"],
       "date": ["2024-03-01", "02/03/2024", "2024-03-03", "2024-03-03", "2024-03-04"]
   })
   print("Raw rows:", len(raw))
   print("Raw columns:", list(raw.columns))

   Show outputGuess what it prints first

   Raw rows: 5
   Raw columns: ['order_id', 'amount', 'category', 'date']

   Common mistake

   Assuming dirty input files have correct numeric dtypes without verifying object column representations first.

   How you will know

   You will see raw rows equal to 5 and columns matching order_id, amount, category, and date.

2. 02

   Attempt naive direct float conversion

   Wrong turn first: this step shows the mistake before the fix

   Here is the naive approach developers often try first: immediately calling raw['amount'].astype(float). Running this operation crashes Python with a ValueError because Pandas cannot convert the rupee symbol, comma separators, and whitespace strings directly into IEEE floating-point numbers. We wrap this naive attempt in a try-except block to demonstrate why string sanitization must precede numeric casting. Calling astype directly on unformatted string representations fails because commas, symbols, and whitespace cannot be parsed as floating-point numbers without dedicated regular expression preprocessing and explicit numeric coercion.

   PythonStep 2Copy

   import pandas as pd
   
   raw = pd.DataFrame({
       "order_id": [1, 2, 3, 3, 4],
       "amount": ["₹1,200.50", " ₹850.00 ", "₹2,100.00", "₹2,100.00", None],
       "category": ["electronics", " Apparel ", None, None, "books"],
       "date": ["2024-03-01", "02/03/2024", "2024-03-03", "2024-03-03", "2024-03-04"]
   })
   try:
       raw["amount"].astype(float)
       print("Direct conversion succeeded")
   except Exception as e:
       print("Naive conversion failed:", type(e).__name__)

   Show outputGuess what it prints first

   Naive conversion failed: ValueError

   Common mistake

   Attempting to cast string columns containing currency symbols directly to float without regex stripping.

   How you will know

   Observe that direct float conversion raises a ValueError due to formatting characters.

3. 03

   Deduplicate records by primary key order_id

   We eliminate duplicate records by calling drop_duplicates with subset=['order_id'] and keep='first'. We chain the .copy() method to ensure the resulting cleaned DataFrame owns its own independent memory buffer, completely preventing unexpected SettingWithCopyWarning warnings when modifying columns in subsequent pipeline steps. The raw dataset is reduced from 5 rows to 4 distinct orders, cleanly removing the duplicate transmission of order 3. Explicitly creating an isolated copy prevents hidden memory fragmentation and ensures that downstream transformations execute deterministically without triggering warning messages or corrupting shared pointer state.

   PythonStep 3Copy

   import pandas as pd
   
   raw = pd.DataFrame({
       "order_id": [1, 2, 3, 3, 4],
       "amount": ["₹1,200.50", " ₹850.00 ", "₹2,100.00", "₹2,100.00", None],
       "category": ["electronics", " Apparel ", None, None, "books"],
       "date": ["2024-03-01", "02/03/2024", "2024-03-03", "2024-03-03", "2024-03-04"]
   })
   cleaned = raw.drop_duplicates(subset=["order_id"]).copy()
   print("Deduplicated rows:", len(cleaned))
   print("Order IDs:", list(cleaned["order_id"]))

   Show outputGuess what it prints first

   Deduplicated rows: 4
   Order IDs: [1, 2, 3, 4]

   Common mistake

   Omitting .copy() after filtering or dropping duplicates, triggering SettingWithCopy warnings on future mutations.

   How you will know

   Output shows exactly 4 unique order IDs: [1, 2, 3, 4].

4. 04

   Sanitize currency strings and cast to numeric floats

   We clean the amount column using the vectorized regex replacement .str.replace(r'[^0-9.]', '', regex=True), which strips away rupee symbols, commas, and spaces. We then convert the sanitized strings into numeric floats using pd.to_numeric with errors='coerce', and fill missing null entries with 0.0. Calculating the total sum confirms verified net revenue of 4,150.50 rupees across the four orders. Using vectorized regular expressions replaces expensive Python loops.

   PythonStep 4Copy

   import pandas as pd
   
   raw = pd.DataFrame({
       "order_id": [1, 2, 3, 3, 4],
       "amount": ["₹1,200.50", " ₹850.00 ", "₹2,100.00", "₹2,100.00", None],
       "category": ["electronics", " Apparel ", None, None, "books"],
       "date": ["2024-03-01", "02/03/2024", "2024-03-03", "2024-03-03", "2024-03-04"]
   })
   cleaned = raw.drop_duplicates(subset=["order_id"]).copy()
   cleaned["amount"] = (
       cleaned["amount"]
       .astype(str)
       .str.replace(r"[^0-9.]", "", regex=True)
   )
   cleaned["amount"] = pd.to_numeric(cleaned["amount"], errors="coerce").fillna(0.0)
   print("Sanitized amounts:", list(cleaned["amount"]))
   print("Total revenue:", float(cleaned["amount"].sum()))

   Show outputGuess what it prints first

   Sanitized amounts: [1200.5, 850.0, 2100.0, 0.0]
   Total revenue: 4150.5

   Common mistake

   Using errors='raise' in pd.to_numeric, which halts pipeline execution on null or unexpected token values.

   How you will know

   Output shows numeric floats [1200.5, 850.0, 2100.0, 0.0] and total revenue of 4150.5.

5. 05

   Standardize heterogeneous dates and missing categories

   In the final step, we standardize dates and categories. We parse the mixed date strings ('2024-03-01' and European '02/03/2024') into uniform ISO dates using pd.to_datetime with format='mixed'. For categories, we fill null values with 'Unknown', strip extraneous padding, and apply title case. The final DataFrame is clean, properly typed, and ready for automated BI dashboard loading. Mixed date format parsing ensures that heterogeneous date styles unify into standard ISO strings, while categorical imputation guarantees that downstream BI filters operate without missing value errors.

   PythonStep 5Copy

   import pandas as pd
   
   raw = pd.DataFrame({
       "order_id": [1, 2, 3, 3, 4],
       "amount": ["₹1,200.50", " ₹850.00 ", "₹2,100.00", "₹2,100.00", None],
       "category": ["electronics", " Apparel ", None, None, "books"],
       "date": ["2024-03-01", "02/03/2024", "2024-03-03", "2024-03-03", "2024-03-04"]
   })
   cleaned = raw.drop_duplicates(subset=["order_id"]).copy()
   cleaned["amount"] = (
       cleaned["amount"]
       .astype(str)
       .str.replace(r"[^0-9.]", "", regex=True)
   )
   cleaned["amount"] = pd.to_numeric(cleaned["amount"], errors="coerce").fillna(0.0)
   cleaned["date"] = pd.to_datetime(cleaned["date"], format="mixed").dt.strftime("%Y-%m-%d")
   cleaned["category"] = cleaned["category"].fillna("Unknown").str.strip().str.title()
   
   for _, row in cleaned.iterrows():
       print(f"Order {int(row['order_id'])}: {row['date']} | {row['category']} | {row['amount']:.2f}")

   Show outputGuess what it prints first

   Order 1: 2024-03-01 | Electronics | 1200.50
   Order 2: 2024-02-03 | Apparel | 850.00
   Order 3: 2024-03-03 | Unknown | 2100.00
   Order 4: 2024-03-04 | Books | 0.00

   Common mistake

   Failing to pass format='mixed' to pd.to_datetime when handling files that mix slash and hyphen date styles.

   How you will know

   Output prints 4 cleanly formatted rows with standardized dates, capitalized categories, and float amounts.

Build it yourself

Write your solution below, run it in your browser, and pass every check.

What the result means

Interpretation

The data cleansing pipeline successfully transforms dirty, unformatted vendor exports into an enterprise-ready dimensional dataset. Deduplicating order_id eliminates duplicate transmissions, while regular expression stripping and numeric coercion recover 4,150.50 rupees in verified transaction volume. Mixed date parsing and categorical imputation ensure that BI ingestion jobs run autonomously without manual intervention.

The message you would send

Hi Vendor Operations Team, the automated Pandas data cleansing pipeline is deployed. It automatically resolves currency formatting, duplicate orders, and mixed date styles across partner CSV exports. Automated dashboard loads have resumed, and the 10 lakh rupee vendor settlement backlog has cleared.

Put it on your CV

Resume or LinkedIn bullet

Engineered an automated data cleansing pipeline in Pandas using vectorized regex, mixed date parsing, and duplicate handling.

README for GitHub

# Data Cleansing Pipeline for Messy Sales Exports

An enterprise Pandas data cleansing pipeline standardizing messy CSV exports for automated BI dashboards and data warehouses.

## Features
- Deduplication by primary key using drop_duplicates()
- Vectorized regex currency symbol stripping and numeric casting
- Heterogeneous date standardization using pd.to_datetime(format='mixed')
- Null categorical value imputation and string trimming

Keep going

• LessonPandas cleaning and types
• Case studyTidy a Messy Signup List
• Case studyShopKart Corrupt JSON Payloads