Loading and Cleaning Data with Pandas

Why Data Cleaning Matters

Real-world data is messy: missing values, inconsistent capitalization, mixed date formats, stray whitespace, and duplicate rows are the norm, not the exception. Data scientists routinely spend the majority of a project on loading and cleaning data before any modeling begins. Pandas is the workhorse library for this work in Python.

Loading Data

Pandas can read many formats. The most common is CSV:

import pandas as pd

df = pd.read_csv('sales.csv')

Useful parameters:

• sep — delimiter (e.g. ';' or '\t')
• na_values — extra strings to treat as missing (e.g. ['NA', '-', 'unknown'])
• parse_dates — columns to parse as datetimes
• dtype — force column types
• usecols — load only needed columns

df = pd.read_csv('sales.csv', na_values=['-', 'n/a'], parse_dates=['order_date'])

Other readers: pd.read_excel(), pd.read_json(), pd.read_parquet(), pd.read_sql().

Inspecting the Data

Always look before you clean:

df.head()        # first rows
df.shape         # (rows, columns)
df.info()        # dtypes and non-null counts
df.describe()    # summary stats for numeric columns
df.isna().sum()  # missing values per column
df['city'].value_counts()  # category frequencies

info() and isna().sum() are your fastest signals of data-quality problems.

Handling Missing Values

First understand why values are missing, then choose a strategy:

• Drop: df.dropna() removes rows; df.dropna(axis=1) removes columns. Use subset= and thresh= for control.
• Impute: fill with a sensible value.

df['age'] = df['age'].fillna(df['age'].median())
df['category'] = df['category'].fillna('unknown')

Use the median for skewed numerics, the mean for symmetric data, and the mode or a placeholder for categories. Avoid dropping data carelessly—you may lose valuable signal.

Fixing Inconsistent Formats

Text is a common offender:

df['city'] = df['city'].str.strip().str.lower()
df['city'] = df['city'].replace({'ny': 'new york', 'nyc': 'new york'})

Convert types explicitly:

df['price'] = pd.to_numeric(df['price'], errors='coerce')
df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')

Using errors='coerce' turns unparseable values into NaN/NaT so you can handle them deliberately.

Removing Duplicates

df.duplicated().sum()      # how many duplicate rows
df = df.drop_duplicates()  # remove exact duplicates
df = df.drop_duplicates(subset=['customer_id'], keep='last')

A Practical Cleaning Workflow

1. Load with the right parameters.
2. Inspect shape, types, and missingness.
3. Standardize column names: df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_').
4. Fix data types.
5. Handle missing values.
6. Standardize text/categories.
7. Remove duplicates.
8. Validate the result with another info()/describe().

Keep cleaning steps in a reproducible script or notebook so the same raw data always produces the same clean output.