Master Pandas From Scratch

Learn Pandas fundamentals, Series, DataFrames, data selection, filtering, cleaning, sorting, grouping, merging, reshaping, string operations, time series, data analysis, performance optimization, visualization, and practical projects through structured lessons.

COURSE SYLLABUS
1. Pandas Introduction and Environment Setup
Introduction to Pandas Installation and Environment Setup
2. Pandas Core Data Structures
Data Structures Overview Pandas Series DataFrames Indexes, Rows and Columns Shape, Size and Dimensions
3. Pandas Data Input and Output
Data Input and Output Overview Reading CSV, Excel and JSON Files Writing and Exporting Files
4. Initial Data Inspection
Data Inspection Overview Head and Tail Data Info and Types Descriptive Statistics
5. Selection and Indexing
Selection and Indexing Overview Column Selection loc and iloc Index Management
6. Filtering and Conditional Selection
Filtering Overview Boolean Filtering Multiple Conditions isin, between and str.contains
7. Basic Data Cleaning
Data Cleaning Overview Missing Values Duplicate Data Rename and Replace Data Type Conversion
8. Vectorized Operations and Sorting
Operations and Sorting Overview Column Operations Sorting Values and Indexes Sum, Mean, Max and Min
9. GroupBy and Aggregation
GroupBy Overview Grouping Data Aggregation Functions Group Filtering Group Transform
10. Merging, Joining and Concatenating
Merge, Join and Concat Overview Concatenation Merging DataFrames Joining by Index
11. Reshaping and Pivoting
Reshaping Overview Pivot Tables Melting Data Stack and Unstack Cross Tabulation
12. String and Categorical Data
String and Categorical Overview String Operations Categorical Data One-Hot Encoding
13. Time Series Analysis
Time Series Overview DateTime Operations Time-Based Indexes Resampling Data Shift and Rolling Windows
14. Custom Functions and Mapping
Apply and Mapping Overview Apply and Lambda Functions Map Operations Element-Wise Operations
15. Performance and Large Datasets
Performance Overview Memory Optimization Processing Files in Chunks PyArrow, Dask and Polars
16. Pandas Data Visualization
Visualization Overview Basic Plotting Histograms, Lines, Scatter and Box Plots Matplotlib and Seaborn

Data Cleaning in Pandas

Learn how to identify, inspect, and prepare messy datasets using Pandas data cleaning techniques.

1. Introduction to Data Cleaning

Data cleaning is the process of identifying and correcting problems in a dataset before performing analysis. Real-world datasets may contain missing values, duplicate records, inconsistent text, incorrect data types, and invalid values.

Pandas provides several methods to inspect and clean DataFrames. Cleaning data improves consistency, reduces errors, and helps produce more reliable analysis results.

Common Data Quality Problems

Problem Example Typical Solution
Missing values Age = NaN isna(), dropna(), fillna()
Duplicate records The same customer appears twice. duplicated(), drop_duplicates()
Inconsistent text "pune", "Pune", " PUNE " str.strip(), str.title()
Incorrect data types Age stored as text astype(), to_numeric()
Invalid values Negative age Validation rules and filtering
Raw Dataset Inspect Data Quality Missing Values Detect and handle Duplicates Detect and remove Types and Text Standardize values Cleaned Dataset

A typical data cleaning workflow begins with inspection and ends with validation of the cleaned dataset.

2. Create a Dataset with Common Data Problems

The following DataFrame contains missing ages, duplicate records, inconsistent city names, and numeric values stored as text.

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "Name": [
        "Samadhan",
        "Pradnya",
        "Samiksha",
        "Rahul",
        "Samadhan",
        "Neha"
    ],
    "Age": [22, np.nan, 24, 21, 22, -5],
    "City": [
        " pune ",
        "Mumbai",
        "PUNE",
        "Nashik",
        " pune ",
        "Mumbai"
    ],
    "Salary": [
        "45000",
        "52000",
        None,
        "48000",
        "45000",
        "30000"
    ]
})

print(df)

Expected Output:

       Name   Age     City Salary
0  Samadhan  22.0    pune   45000
1   Pradnya   NaN   Mumbai  52000
2  Samiksha  24.0     PUNE   None
3     Rahul  21.0   Nashik  48000
4  Samadhan  22.0    pune   45000
5      Neha  -5.0   Mumbai  30000
Important: A value such as -5 may be technically valid for a numeric column but invalid according to the business rules for age. Data cleaning should consider both data types and domain rules.

3. Inspect the Dataset Before Cleaning

Always inspect the dataset before modifying it. This helps identify its structure, data types, missing values, and potential quality problems.

View the First Rows

print(df.head())

Check Column Data Types

print(df.dtypes)

Expected Output:

Name       object
Age       float64
City       object
Salary     object
dtype: object

The Salary column is stored as an object because it contains text values and a missing entry. Converting it to a numeric type will make numeric operations easier.

Get a Dataset Summary

df.info()

The info() method displays the number of rows, column names, non-null counts, data types, and memory usage. Use it to identify columns that contain missing values.

Count Missing Values

print(df.isna().sum())

Expected Output:

Name      0
Age       1
City      0
Salary    1
dtype: int64

4. Handle Missing Values

Missing values occur when information is unavailable or was not recorded. Pandas provides methods to detect, remove, or replace missing entries.

Example 1: Identify Missing Values

print(df[df["Age"].isna()])

Expected Output:

      Name  Age    City Salary
1  Pradnya  NaN  Mumbai  52000

Example 2: Fill Missing Age with the Median

df["Age"] = df["Age"].fillna(
    df["Age"].median()
)

print(df["Age"])

Expected Output:

0    22.0
1    22.0
2    24.0
3    21.0
4    22.0
5    -5.0
Name: Age, dtype: float64

The median is calculated from the available age values. It replaces the missing age without changing the other values. The invalid negative age still requires separate validation.

Example 3: Remove Rows with Missing Values

Use dropna() when removing incomplete rows is appropriate for the analysis.

complete_df = df.dropna()

print(complete_df)

The method returns a DataFrame without rows containing missing values. In this example, the missing age has already been filled, so rows with missing Salary values are removed.

Best Practice: Do not automatically remove all missing values. Consider why values are missing and whether dropping rows would discard important information.

5. Detect and Remove Duplicate Records

Duplicate records can inflate counts, distort totals, and affect analytical results. Pandas provides duplicated() to identify duplicates and drop_duplicates() to remove them.

Example 1: Find Duplicate Rows

print(df.duplicated())

The result is a Boolean Series. A value of True indicates that a row duplicates an earlier row according to the selected columns and duplicate-detection settings.

Example 2: Display Duplicate Records

duplicates = df[
    df.duplicated(keep=False)
]

print(duplicates)

The keep=False argument marks every member of a duplicate group as True rather than keeping one occurrence unmarked.

Example 3: Remove Duplicate Rows

clean_df = df.drop_duplicates()

print(clean_df)

By default, drop_duplicates() keeps the first occurrence of each duplicate row and removes subsequent copies.

Example 4: Detect Duplicates Using Specific Columns

duplicate_names = df[
    df.duplicated(subset=["Name"], keep=False)
]

print(duplicate_names[["Name", "City"]])

This example identifies rows that share a Name value. However, names alone may not uniquely identify a person. Choose columns that reflect the actual business definition of a duplicate record.

6. Standardize Text Values

Text values can contain leading spaces, trailing spaces, or inconsistent capitalization. Standardizing text makes grouping, filtering, and comparisons more reliable.

Example 1: Remove Extra Spaces

df["City"] = df["City"].str.strip()

print(df["City"])

The strip() string method removes whitespace from the beginning and end of each string.

Example 2: Standardize Capitalization

df["City"] = df["City"].str.title()

print(df["City"])

Expected Output:

0      Pune
1    Mumbai
2      Pune
3    Nashik
4      Pune
5    Mumbai
Name: City, dtype: object

The city values are now consistently formatted. This is especially useful when the same category has been entered in different capitalization styles.

Note: Capitalization rules depend on the data. For standardized codes or case-sensitive identifiers, use an appropriate normalization strategy instead of applying title case blindly.

7. Convert Columns to the Correct Data Types

Correct data types make calculations and comparisons more reliable. A numeric value stored as text should be converted before performing numerical analysis.

Example 1: Convert Salary to Numeric

df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

print(df["Salary"].dtype)

Expected Output:

float64

The errors="coerce" option converts invalid numeric entries into missing values instead of raising an exception. Inspect the resulting missing values before deciding how to handle them.

Example 2: Convert a Column to Integer

df["Marks"] = pd.Series(
    [85, 68, 92, 76, 55, 70],
    dtype="int64"
)

print(df["Marks"].dtype)

Expected Output:

int64

Ordinary integer types cannot represent missing values. If an integer column must retain missing entries, Pandas nullable integer types such as Int64 may be useful.

8. Identify and Handle Invalid Values

A value can have the correct data type and still be invalid. For example, a negative age may be a valid integer but violate the rules of a student or customer dataset.

Example 1: Find Invalid Ages

invalid_ages = df[
    (df["Age"] < 0) |
    (df["Age"] > 120)
]

print(invalid_ages[["Name", "Age"]])

This filter identifies ages outside the example's permitted range. The correct range should always be chosen according to the requirements of the dataset.

Example 2: Replace Invalid Ages with Missing Values

df.loc[
    (df["Age"] < 0) | (df["Age"] > 120),
    "Age"
] = np.nan

print(df["Age"])

The invalid values are replaced with missing values so they can be reviewed and handled using an appropriate missing-data strategy.

Important: Do not replace invalid values automatically without considering their meaning. In some datasets, invalid records should be flagged, corrected from a trusted source, or excluded.

9. Rename Columns for Consistency

Clear and consistent column names make DataFrame operations easier to understand. You can use rename() to change selected column names.

df = df.rename(
    columns={
        "Name": "StudentName",
        "City": "CityName"
    }
)

print(df.columns.tolist())

Expected Output:

['StudentName', 'Age', 'CityName', 'Salary']

Renaming columns is useful when imported datasets contain unclear names, inconsistent naming conventions, or spaces that make code harder to maintain.

10. Validate the Cleaned Dataset

Data cleaning should finish with validation. Check the cleaned DataFrame again to confirm that the intended changes were applied.

Check Remaining Missing Values

print(df.isna().sum())

Check Remaining Duplicates

print(df.duplicated().sum())

Inspect Data Types

print(df.dtypes)

Check Basic Statistics

print(df.describe(include="all"))

Validation helps identify issues that remain after cleaning. Some missing values may be intentional, and not every duplicate or unusual value is necessarily an error. Review results in the context of the dataset's requirements.

11. Complete Data Cleaning Example

The following example demonstrates a simple cleaning workflow on a fresh copy of the sample dataset. It removes duplicate records, standardizes city names, converts Salary to numeric, and marks invalid ages as missing.

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "Name": [
        "Samadhan",
        "Pradnya",
        "Samiksha",
        "Rahul",
        "Samadhan",
        "Neha"
    ],
    "Age": [22, np.nan, 24, 21, 22, -5],
    "City": [
        " pune ",
        "Mumbai",
        "PUNE",
        "Nashik",
        " pune ",
        "Mumbai"
    ],
    "Salary": [
        "45000",
        "52000",
        None,
        "48000",
        "45000",
        "30000"
    ]
})

# 1. Standardize city names
df["City"] = df["City"].str.strip().str.title()

# 2. Convert Salary to a numeric column
df["Salary"] = pd.to_numeric(
    df["Salary"],
    errors="coerce"
)

# 3. Mark invalid ages as missing
df.loc[
    (df["Age"] < 0) | (df["Age"] > 120),
    "Age"
] = np.nan

# 4. Remove exact duplicate rows
df = df.drop_duplicates()

# 5. Inspect the cleaned data
print(df)
print(df.isna().sum())
print(df.dtypes)

This example deliberately leaves missing values in place rather than filling them with arbitrary values. The appropriate strategy for missing ages or salaries depends on the analysis requirements and the meaning of the data.

12. Data Cleaning Best Practices

  • Inspect the original dataset before making changes.
  • Keep an unchanged copy of important source data.
  • Understand why values are missing before filling or removing them.
  • Check duplicate records using meaningful identifying columns.
  • Standardize text only when the transformation is appropriate.
  • Validate values against domain-specific rules.
  • Convert data types only after inspecting the actual values.
  • Recheck missing values, duplicates, and data types after cleaning.

13. Practical Exercises

Exercise 1: Missing Values

Create a DataFrame with missing age values and count the missing entries in every column.

Hint: Use isna().sum().

Exercise 2: Duplicate Records

Identify duplicate rows and create another DataFrame without exact duplicate records.

Hint: Use duplicated() and drop_duplicates().

Exercise 3: Text Standardization

Standardize city values such as " pune ", "PUNE", and "Pune" into a consistent format.

Hint: Use str.strip() and str.title().

Exercise 4: Numeric Conversion

Convert a Salary column containing numeric strings into a numeric data type.

Hint: Use pd.to_numeric().

Exercise 5: Data Validation

Identify records with negative ages and replace those values with missing values for further review.

Hint: Use loc[], a Boolean condition, and np.nan.

14. Quick Revision Questions

  1. What is data cleaning, and why is it important?
  2. Which Pandas method identifies missing values?
  3. What is the difference between dropna() and fillna()?
  4. How can duplicate records be identified and removed?
  5. How do str.strip() and str.title() help standardize text?
  6. What does pd.to_numeric(errors="coerce") do?
  7. Why should invalid values be checked against domain rules?
  8. Why is validation important after cleaning a DataFrame?

Summary

Data cleaning prepares datasets for reliable analysis by identifying missing values, duplicate records, inconsistent text, incorrect data types, and invalid values. Pandas provides methods such as isna(), fillna(), dropna(), duplicated(), drop_duplicates(), rename(), and to_numeric() to handle common data quality issues.

A reliable cleaning workflow begins with inspection, applies appropriate transformations, and finishes with validation. Cleaning decisions should follow the meaning of the data and the requirements of the analysis rather than relying on automatic changes without review.