Data Cleaning with pandas :The complete Step-by-Step Guide

Data cleaning is most important step for any data analysis. Raw data collected from various sources are often messy, contain inconsistencies, missing values and outliers. Data cleaning and preprocessing aims to identify and rectify this issues to ensure accurate , reliable and meaningful results. That's where pandas becomes the game changer. With its powerful functions and syntax pandas makes easy to identify issues, fix errors , handle missing values and transform raw data into a clean, structured dataset which is ready for analysis.

Below is a sample dataset created for demonstrating end‑to‑end data cleaning steps with pandas.

1 . Load and inspect the data

• Read csv file: df=pd.read_csv("filename.csv")

• df.head()-It is a pandas DataFrame method that returns the first 5 rows of dataset. It is a quick preview tool in pandas

• df.info()-Gives the compact summary of DataFrame in pandas. It helps us to understand number of rows, columns, non-null values and memory of DataFrame.

2. Trim White spaces and Standardize text

• str.strip()-Removes the leading and trailing spaces for all values in a column(Note:It does'nt remove spaces inside the text.

• str.title()-Converts the text into title case meaning meaning the first letter of every word becomes uppercase, and the rest as lowercase.

• str.replace()-It searches for the pattern and replaces characters or substring.

3. Fix Date Formats

• pd.to_datetime()-To clean and standardize date columns in pandas.

Parameter errors='coerce' and dayfirst='True' helps us to safely handle invalid dates, mixed formats, and region‑specific date styles.

• errors='coerce'- Tells the system incase of invalid, corrupted or impossible dates don't crash just convert it to NaT

4. Remove Duplicates

• drop_duplicates()-Using this function with subset ensures that only truly replated employee records are removed while keeping the unique records intact. This tells pandas that if two rows matches on these fields then treat them as duplicates.

5. Handling Missing values

• Missing values are identified using df.isna().sum() function This helped me to check which columns need attention.

• Numeric columns like Age and Performance_Score cannot stay empty because they are used in calculations. To avoid skewing the data, I filled them using the median, which is more robust than the mean.

• categorical columns like Country and JoinDate are replaced with the mode. This ensures the dataset remains clean, consistent, and analysis‑ready.

• The mode() function returns all values that occur most frequently, and indexing with [0] selects the first mode.

6. Standardize Text Columns

Standardized Columns of Department and replaced with IT and HR for better readability.

7. Split Dept_Region into two columns

 it’s common to find multiple pieces of information stored in a single column here Dept_Region are stored in single column.

• str.split('-') : Splits each value at the hyphen (-).

expand=True :  Tells pandas to create separate columns instead of a list.

• Array index [1]   Selects only the second part of the split result — the Region — because the first part (Department) already exists in the dataset.

Since the Department column is already present, I kept only the extracted Region and deleted the original Dept_Region column to avoid duplication and keep the dataset clean.

8. Handling Outliers

one employee (Robert) in dataset had an unusually high salary of ₹9,00,000, while all other salaries were between ₹48,000 and ₹85,000. To prevent this extreme value from distracting the analysis, I used the Interquartile Range (IQR) method, which is one of the most reliable ways to detect outliers.

Step 1: Calculate Q1 and Q3

Pandas automatically sorts the values internally and then calculates:

• Q1 (25th percentile) → the point where 25% of salaries fall below

• Q3 (75th percentile) → the point where 75% of salaries fall below

Using my salary column, pandas computed:

• Q1 = ₹55,000

• Q3 = ₹68,000

These values are calculated using interpolation, which means pandas finds the exact percentile position between the sorted values.

Step 2:Compute the IQR

IQR=Q3−Q1=68,000−55,000=13,000

Step 3: Calculate the Upper Limit

Any salary above this limit is considered an outlier.

Upper Limit=Q3+1.5×IQR

=68,000+1.5×13,000

=68,000+19,500=87,500

So ₹87,500 becomes the maximum allowed salary in the cleaned dataset.

Step 4: Clip the Outlier

Robert’s original salary:

• 9,00,000 → clipped to 87,500

  
  

9. Rearrange columns

• Reordered the columns to follow the natural structure and better readability.

• Converted float values back to integers to remove unnecessary decimal places

• Hide the index for a cleaner display.

These formatting steps ensure the dataset is clean, consistent, and ready for analysis or reporting.

Common Mistakes to avoid during cleaning:

1. Dropping Duplicates too early

2.Rounding values during cleaning

3.Ignoring inconsistent text formats

4. Not converting date columns to datetime

5.Not checking for unrealistic values

Correct Order of Data Cleaning:

1. Inspect raw data: Understanding the structure , Missing values and Anomalies

2.Fix the data types

3. Clean Text columns: Strip spaces, fix casing, standardize categories.

4.Handling Missing values: Use median/mode or other imputation methods and avoid dropping unless necessary.

5.Standardize the text: To ensure uniform labels

6.Removing duplicates

7. Handling Outliers

8. Rounding values: Always preferred as last step after all transformations

9. Final Formatting : Reorder the columns

Data cleaning is not only about correcting errors but also about building confidence in your dataset. When the cleaning process is done properly, every chart, analysis, or model you build becomes more accurate and reliable.

Happy Learning! Keep Exploring