Data Cleaning Script Generator Output 1. Data Cleaning Strategy Dataset Overview The dataset is an e-commerce customer transaction dataset containing: Customer information Customer ID Customer Name Email Address Location Transaction information Purchase Date Product Category Product Details Payment Method Transaction Amount Identified Data Quality Issues Issue Cleaning Action Duplicate transaction records Detect and remove duplicate rows Missing customer information Apply validation rules and handle missing values Incorrect numerical values Convert and validate numeric columns Inconsistent date formats Standardize all dates into a unified format Text inconsistencies Normalize capitalization and spacing Invalid emails Detect and flag incorrect formats Missing categories Fill or classify unknown values Data type mismatches Convert columns into correct formats --- 2. Required Libraries import pandas as pd import numpy as np import re from datetime import datetime --- 3. Load Excel Dataset file_path = "customer_transactions.xlsx" df = pd.read_excel(file_path) print("Original Dataset:") print(df.head()) --- 4. Initial Data Inspection print(df.info()) print("\nMissing Values:") print(df.isnull().sum()) print("\nDuplicate Records:") print(df.duplicated().sum()) --- 5. Remove Duplicate Records df = df.drop_duplicates() print("Duplicates removed successfully.") --- 6. Standardize Column Names df.columns = ( df.columns .str.strip() .str.lower() .str.replace(" ", "_") ) print(df.columns) Example transformation: Before: Customer Name Purchase Date Payment Method After: customer_name purchase_date payment_method --- 7. Clean Text Columns text_columns = [ "customer_name", "product_category", "location", "payment_method" ] for column in text_columns: df[column] = ( df[column] .astype(str) .str.strip() .str.title() ) Example: Before: john smith ELECTRONICS cash payment After: John Smith Electronics Cash Payment --- 8. Validate Email Addresses def validate_email(email): pattern = r'^[\w\.-]+@[\w\.-]+\.\w+$' return bool(re.match(pattern, str(email))) df["email_valid"] = df["email"].apply(validate_email) Invalid emails can be reviewed: invalid_emails = df[df["email_valid"] == False] print(invalid_emails) --- 9. Handle Missing Values Numeric Columns numeric_columns = [ "price" ] for column in numeric_columns: df[column] = pd.to_numeric( df[column], errors="coerce" ) df[column].fillna( df[column].median(), inplace=True ) --- Text Columns text_columns = [ "customer_name", "product_category", "location" ] for column in text_columns: df[column].fillna( "Unknown", inplace=True ) --- 10. Standardize Date Formats df["purchase_date"] = pd.to_datetime( df["purchase_date"], errors="coerce" ) df["purchase_date"] = ( df["purchase_date"] .dt.strftime("%Y-%m-%d") ) Output format: 2026-07-23 --- 11. Validate Transaction Amounts df["price"] = pd.to_numeric( df["price"], errors="coerce" ) df = df[ df["price"] >= 0 ] Removes: Negative prices Invalid numbers Corrupted values --- 12. Detect Outliers Q1 = df["price"].quantile(0.25) Q3 = df["price"].quantile(0.75) IQR = Q3 - Q1 lower_limit = Q1 - (1.5 * IQR) upper_limit = Q3 + (1.5 * IQR) outliers = df[ (df["price"] < lower_limit) | (df["price"] > upper_limit) ] print(outliers) --- 13. Create Data Quality Report quality_report = pd.DataFrame({ "Column": df.columns, "Missing Values": df.isnull().sum().values, "Unique Values": df.nunique().values, "Data Type": df.dtypes.astype(str).values }) print(quality_report) --- 14. Export Clean Dataset output_file = "clean_customer_transactions.xlsx" df.to_excel( output_file, index=False ) print( "Clean dataset exported successfully." ) --- 15. Final Automated Cleaning Pipeline def clean_customer_dataset(input_file, output_file): df = pd.read_excel(input_file) # Remove duplicates df.drop_duplicates(inplace=True) # Normalize columns df.columns = ( df.columns .str.strip() .str.lower() .str.replace(" ", "_") ) # Clean text for column in df.select_dtypes( include="object" ).columns: df[column] = ( df[column] .astype(str) .str.strip() .str.title() ) # Convert dates if "purchase_date" in df.columns: df["purchase_date"] = pd.to_datetime( df["purchase_date"], errors="coerce" ) # Fill missing values for column in df.columns: if df[column].dtype == "object": df[column].fillna( "Unknown", inplace=True ) else: df[column].fillna( df[column].median(), inplace=True ) # Export result df.to_excel( output_file, index=False ) return df clean_customer_dataset( "customer_transactions.xlsx", "clean_customer_transactions.xlsx" ) --- Implementation Notes Designed for messy Excel sales datasets. Uses pandas for scalable data processing. Keeps the workflow reusable for future files. Adds validation before reporting or analytics. Can be extended into an automated ETL pipeline. --- Recommended Improvements 1. Add automated data profiling using: Great Expectations Pandera 2. Connect directly to: SQL databases Cloud storage Business intelligence tools 3. Add logging: import logging logging.basicConfig( filename="data_cleaning.log", level=logging.INFO ) 4. Schedule automatic cleaning with: Apache Airflow Cron Jobs Cloud Functions --- Final Result The generated workflow converts a raw, inconsistent customer transaction dataset into a structured, validated, analysis-ready dataset suitable for: Sales dashboards Business intelligence reporting Machine learning preparation Customer analytics Automated data pipelines