Customer datasets are among the most corruption-prone data types in practice, they are built from multiple input sources, populated by users with varying levels of care, and rarely subject to real-time validation. The result is typically a mix of structural problems: wrongly typed fields, placeholder values, inconsistent formatting, invalid entries, and missing data that can sometimes be inferred and sometimes cannot. This project took a raw customer dataset through a comprehensive cleaning pipeline using Python and Pandas, transforming it from an unreliable source into a dataset fit for analysis and model development.
The objective was to systematically resolve every category of data quality issue present in the dataset ; duplicates, incorrect types, placeholder values, negative entries, formatting inconsistencies, and missing geographic data ,while preserving as much information as possible and documenting the reasoning behind each decision.
The dataset contained customer-level records spanning demographic fields (age, marital status, education), financial attributes (income, credit score, average monthly spend), contact information (phone number, email), location data (city, state, zip code), and behavioural variables (product type, purchase frequency, employment years, default status, and date columns including last payment, account creation, and last login).
The cleaning pipeline was executed in Python using the Pandas library within Google Colab. The approach followed a structured sequence: inspection → deduplication → type correction and value standardisation → contact data cleaning → location imputation → categorical field correction → financial data repair. Each step was verified before proceeding, ensuring the pipeline was both reproducible and auditable.
The project opened with df.head() and df.info() to establish a clear picture of the dataset’s structure before any modifications were made. This scoping step identified data types, surfaced immediate inconsistencies, and flagged columns with missing values providing the evidence base for every cleaning decision that followed. Inspecting before acting is a foundational discipline that prevents introducing new errors while fixing existing ones.
Code:
import pandas as pd
df= pd.read_csv('/content/dirty_dataset.csv')
df.head()
Duplicate records were removed using df.drop_duplicates(), ensuring each customer is represented exactly once. Duplicate rows in customer datasets are particularly damaging, they inflate record counts, distort aggregate metrics, and can introduce bias in any model trained on the data. Removing them early prevents these downstream effects from compounding through subsequent cleaning steps.
Code:
df.info()
df.drop_duplicates()
This was the most extensive step, addressing type and value issues across multiple columns:
Age: Negative values likely caused by data entry errors were corrected by converting them to absolute values. The column was then cast to a numeric type, ensuring mathematical operations on age produce valid results.
Income: A mixed-format entry (‘35k’) was standardised to its numeric equivalent (35,000) before the column was cast to integer. This kind of non-numeric string in a numeric field will silently break aggregations if not caught and corrected.
Education: Spelling inconsistencies (‘HighSchool’ vs ‘High School’) were unified to a single standard form, preventing the same category from appearing as two distinct values in filters and group-bys.
Marital Status: Typographical errors (‘Divorsed’) were corrected to ‘Divorced’ and the column was converted to a categorical type, reducing memory usage and making value-set validation easier.
Credit Score, Employment Years, Default Status: Placeholder values (‘ERROR’) were replaced with None (interpreted as NaN by Pandas) and the columns were converted to appropriate numeric types. Leaving ‘ERROR’ strings in numeric columns causes silent type coercion failures and corrupts statistical summaries.
Date Columns (last_payment_date, account_created, last_login): All three columns were converted from generic object types to datetime64[ns], enabling proper date arithmetic, chronological sorting, and time-based feature engineering in downstream analysis.
Code:
print(df['age'].unique())
""" There happens to be some negative values in the age column, which is likly an entrying error.
I corrected that error and also change column age to numeric data type"""
df['age']= df['age'].abs()
df['age']=df['age'].apply(pd.to_numeric)
df['age']
df['income'].unique()
""" There was an error in this column. A customer happened to enter a mixed character (35k) for their income.
I convert it to numeric data and also coverted the column to numeric"""
df['income']= df['income'].replace('35k',35000)
df['income']= df['income'].astype(int)
df['income'].info()
df['education'].unique()
""" In the education column there seems to be an
incosistency in the way customer's education level was entered('HighSchool'&'High School')."""
df['education'].replace('HighSchool','High School', inplace=True)
df['education'].unique()
df['marital_status'].unique()
'''There is inconsistency in the way 'Divorced' is been spelt in the column.
I am going to correct that and also change the column type to categorical.'''
df['marital_status']= df['marital_status'].replace('Divorsed','Divorced')
df['marital_status']= df['marital_status'].astype('category')
df['marital_status']
df['credit_score'].unique()
### In the credit_score column there is a placeholder(Error), which i am going to change to 'nan'to keep the column standardize
df['credit_score']= df['credit_score'].replace('ERROR',None)
df['credit_score']= df['credit_score'].apply(pd.to_numeric)
df['employment_years'].unique()
##In employment_years and default_status column there are placeholders (Error), which i am going to change to 'nan' to keep the columns standardize
df[['employment_years','default_status']]= df[['employment_years','default_status']].replace('ERROR',None)
df[['employment_years','default_status']]= df[['employment_years','default_status']].apply(pd.to_numeric)
df[['employment_years','default_status']]
df['last_payment_date'].unique()
df['account_created'].unique()
df['last_login'].unique()
""" Since last_payment_date,account_created and last_login all have the same format(datetime),
and there isn't any error in each columns, i will just change the datatype to it correct datetype"""
df[['last_payment_date','account_created','last_login']]= df[['last_payment_date','account_created','last_login']].apply(pd.to_datetime)
df[['last_payment_date','account_created','last_login']]
The phone number column required the most intricate cleaning logic in the entire pipeline, involving four sequential transformations:
First, all special characters like hyphens, dots, parentheses, and spaces were stripped to produce a raw digit string. Second, numbers with fewer than 7 digits were prefixed with a country code (‘5550’) to standardise short entries. Third, numbers exceeding 7 digits after the initial cleanup were set to None, as they could not be reliably interpreted. Finally, valid 7-digit numbers were reformatted with a standard hyphen separator (e.g., 555-0123) for consistency.
This four-stage approach reflects a critical principle in contact data cleaning: not all invalid phone numbers fail the same way, and a single transformation cannot fix them all. Each failure mode requires its own targeted rule.
Code:
df['phone_number'].unique()
# There seems to be some inconsistency in the phone_number pattern. I am going correct that.
## First, reduce the number to all digit with no special characters
import re
df['phone_number']= df['phone_number'].astype(str)
df['phone_number']= df['phone_number'].map(lambda x: re.sub(r'[-.()\s]','', x))
df['phone_number'].unique()
""" We still have incosistency in the column. A row doesn't have the country code, while another has more than 7 digit.
I am going to be adding the country code to the ones with no country code, and dropping the number with more than seven digit"""
df['phone_number']= df['phone_number'].map(lambda x: '5550' + x if len(x) < 7 else x)
df['phone_number']= df['phone_number'].map(lambda x: None if len(x) > 7 else x )
df['phone_number'] = df['phone_number'].map(lambda x: x[:3] + '-' + x[3:] if x is not None and len(x) == 7 else x)
df['phone_number'].unique()
Email addresses ending with ‘@’ indicating a missing domain were corrected by appending email.com. While this does not guarantee deliverability, it ensures the field is structurally valid and prevents downstream string operations (domain extraction, format validation) from failing on malformed entries. Addresses with complete domains were left unchanged.
Code:
df['email'].unique()
""" In the email column there appear to be a common pattern (name.name@email.com).
Some of the email address are missing the domain, i will be adding the domain to the ones missing a domain."""
df['email']= df['email'].map(lambda x: x + 'email.com' if x.endswith('@') else x)
df['email'].unique()
Location data required three distinct treatments across its sub-fields:
City: Converted to a categorical type for memory efficiency and to enforce a bounded value set.
State: Missing state values were imputed using a city-to-state mapping; for example, ‘Fresno’ → ‘CA’, ‘Miami’ → ‘FL’. This domain-knowledge imputation recovers state values without introducing uncertainty, as the mapping is deterministic for unambiguous city names.
Zip Code: Two distinct corruption types were addressed: leading zeros dropped during numeric import (e.g., ‘04101’ becoming ‘4101’) and values enclosed in erroneous quotes. Both were corrected to produce consistent 5-digit zero-padded strings the standard US zip code format.
Code:
df['city'].unique()
## They don't seem to be any issue with the city column. I will just correct the datatype
df['city']= df['city'].astype('category')
df['city']
df['state'].unique()
## There seems to be missing values in the state column. Since i have a city column, i can easily fill the nulls up.
# The city with missing state is 'Fresno' and 'Miami'. I will be filling them with there respective state CA and FL
df.loc[(df['city'] == 'Fresno') & (df['state'].isnull()), 'state'] = 'CA'
df.loc[(df['city'] == 'Miami') & (df['state'].isnull()), 'state'] = 'FL'
df['state'].astype(str)
df['zip_code'].unique()
""" I noticed an inconsistency in zip_code column.I noticed all zip codes are 5 digits, except one.
I then decided to confirm the city with that zipcode and realized there was a missing leading zero, so i will add that.
Also a customer entered their zip code in qoute, i will also correct that"""
df['zip_code']= df['zip_code'].str.strip('""')
df['zip_code']= df['zip_code'].map(lambda x: '0' + x if len(x) < 5 else x)
df['zip_code']
Invalid entries in product_type including the placeholder ‘Unknown’ and a numeric entry (‘3’) were replaced with None and the column was converted to categorical. In purchase_frequency, a numeric outlier (‘30’) was corrected to ‘monthly’, a clear data entry error where a frequency count was entered instead of a frequency label before the column was also made categorical. Both corrections required domain judgment: the appropriate replacement value was inferred from the context of what the field is meant to represent.
Code:
df['product_type'].unique()
## There is a placeholder in product type column and also an invalid entry (3). I will just change them to null
df['product_type']= df['product_type'].replace(['Unknown','3'],None)
df['product_type']= df['product_type'].astype('category')
df['product_type']
Negative values in the avg_monthly_spend column were converted to absolute values, following the same logic applied to the age column in Step 3. Negative spending figures are not meaningful, they most likely represent data entry sign errors and their absolute values are the intended amounts. This approach recovers the data rather than discarding it, preserving record completeness.
Code:
df['purchase_frequency'].unique()
## A customer happened to enter 30 instead of monthly, so i will correct that
df['purchase_frequency']= df['purchase_frequency'].replace('30','monthly')
df['purchase_frequency']= df['purchase_frequency'].astype('category')
df['purchase_frequency']
df['avg_monthly_spend'].unique()
## There are negative values in the column,which i don't believe is possible. So i will be correcting that.
df['avg_monthly_spend']= df['avg_monthly_spend'].abs()
df['avg_monthly_spend'].unique()
This cleaning project addressed ten distinct categories of data quality failure across a single customer dataset: duplicates, incorrect types, placeholder strings, negative numeric values, mixed-format entries, spelling inconsistencies, malformed contact data, incomplete geographic fields, invalid categoricals, and date format issues. The breadth of issues is typical of real-world customer data and the pipeline’s value lies not just in fixing them, but in fixing them in the right order with the right technique for each.
Several decisions in this project reflect analytical maturity beyond basic cleaning. The phone number pipeline handled four different failure modes independently rather than applying a single blunt transformation. State imputation used deterministic city-based inference rather than statistical filling. The purchase frequency correction required understanding what the field should contain, not just what it did contain. And the absolute value approach for negative numerics prioritised data recovery over deletion.
The result is a dataset where every remaining value is either original, demonstrably corrected, or transparently imputed, a dataset that is not just cleaner, but analytically trustworthy.