Data is almost never in the format you need it. Real-world datasets arrive with missing values, inconsistent formatting, duplicate rows, mixed data types, and awkward shapes. Data wrangling — the process of cleaning and reshaping raw data into an analysis-ready format — typically consumes 60-80% of a data scientist’s project time. Pandas is the primary Python tool for this work, and mastering it is the single biggest productivity multiplier for working data scientists.
Handling Missing Values
Missing values are the first thing to tackle in any dataset. Pandas represents them as NaN (Not a Number). Your options are to drop rows/columns with missing values, fill them with a statistic (mean, median, mode), or use forward/backward fill for time series. The right strategy depends on why data is missing and how much is missing:
import pandas as pd
import numpy as np
df = pd.read_csv('sales_data.csv')
# Audit missing values
missing_summary = pd.DataFrame({
'missing_count': df.isnull().sum(),
'missing_pct': df.isnull().sum() / len(df) * 100,
'dtype': df.dtypes
}).query('missing_count > 0').sort_values('missing_pct', ascending=False)
print(missing_summary)
# Drop columns missing more than 50% of values
threshold = 0.5
df = df.loc[:, df.isnull().mean() < threshold]
# Fill numeric columns with median (robust to outliers)
numeric_cols = df.select_dtypes(include='number').columns
df[numeric_cols] = df[numeric_cols].fillna(df[numeric_cols].median())
# Fill categorical with mode
cat_cols = df.select_dtypes(include='object').columns
for col in cat_cols:
df[col] = df[col].fillna(df[col].mode()[0])
# Time series: forward fill then backward fill
df_ts = df_ts.sort_values('date').fillna(method='ffill').fillna(method='bfill')
Reshaping: Melt, Pivot, and Stack
Data often arrives in "wide" format (one column per measurement) when you need "long" format (one row per measurement), or vice versa. melt() converts wide to long; pivot_table() converts long to wide:
# Wide-to-long: melt
# Before: columns = ['user_id', 'jan_sales', 'feb_sales', 'mar_sales']
# After: columns = ['user_id', 'month', 'sales']
df_long = df.melt(
id_vars=['user_id'],
value_vars=['jan_sales', 'feb_sales', 'mar_sales'],
var_name='month',
value_name='sales'
)
# Long-to-wide: pivot_table
df_wide = df_long.pivot_table(
index='user_id',
columns='month',
values='sales',
aggfunc='sum',
fill_value=0
)
df_wide.columns.name = None # remove the 'month' axis label
df_wide = df_wide.reset_index()
# Aggregate with groupby
monthly_summary = df.groupby(['region', 'month']).agg(
total_sales=('sales', 'sum'),
avg_order=('order_value', 'mean'),
n_orders=('order_id', 'count'),
unique_customers=('customer_id', 'nunique')
).reset_index()
String Cleaning and Data Type Conversion
Messy string data is one of the most common wrangling challenges. Pandas string methods (accessed via .str) make text cleaning systematic:
# String cleaning pipeline
df['product_name'] = (
df['product_name']
.str.strip() # remove leading/trailing whitespace
.str.lower() # standardise case
.str.replace(r'\s+', ' ', regex=True) # collapse multiple spaces
.str.replace(r'[^\w\s]', '', regex=True) # remove special characters
)
# Extract structured data from strings using regex
# e.g., '1,234.56 USD' -> numeric value
df['price_clean'] = (
df['price_raw']
.str.replace('[,$]', '', regex=True)
.str.strip()
.astype(float)
)
# Parse dates — specify format for speed and correctness
df['order_date'] = pd.to_datetime(df['order_date'], format='%Y-%m-%d', errors='coerce')
df['year'] = df['order_date'].dt.year
df['month'] = df['order_date'].dt.month
df['dow'] = df['order_date'].dt.day_name() # 'Monday', 'Tuesday', etc.
# Convert column types explicitly after cleaning
df['category'] = df['category'].astype('category') # saves memory
df['is_active'] = df['status'].map({'active': True, 'inactive': False})
Merging and Joining DataFrames
Real data science projects almost always require combining data from multiple sources. pd.merge() works like a SQL JOIN. Always validate your merges — unexpected NaN values after a merge indicate key mismatches:
# Validate merge keys before joining
print(f"Orders unique customers: {orders['customer_id'].nunique()}")
print(f"Customers unique IDs: {customers['customer_id'].nunique()}")
print(f"Overlap: {orders['customer_id'].isin(customers['customer_id']).sum()}")
# Merge with suffix for overlapping column names
merged = pd.merge(
orders,
customers,
on='customer_id',
how='left', # keep all orders; unmatched customers get NaN
suffixes=('_order', '_customer'),
validate='many_to_one' # raises error if customer_id isn't unique in customers
)
# Check for unexpected NaN after merge (indicates join key issues)
new_nulls = merged.isnull().sum() - orders.isnull().sum()
print("New NaN after merge (check join keys):")
print(new_nulls[new_nulls > 0])
# Concatenate DataFrames from multiple files
import glob
all_files = glob.glob('data/monthly_*.csv')
df_combined = pd.concat(
[pd.read_csv(f) for f in all_files],
ignore_index=True,
verify_integrity=True # raises error on duplicate indices
)
Frequently Asked Questions
How do I speed up slow Pandas operations?
Use vectorised operations instead of loops — they're 10-100x faster. Avoid iterrows() and apply() on large DataFrames when a vectorised alternative exists. Use astype('category') for low-cardinality string columns to reduce memory. For datasets over 1GB, consider Polars (a faster Pandas alternative) or Dask for out-of-memory data.
How should I handle outliers during wrangling?
First investigate — is the outlier a data error (a customer age of 999) or a real extreme value (a very large order)? For errors, impute or remove. For genuine extremes, consider capping (winsorising) at the 1st/99th percentile, or use robust statistics (median, IQR) instead of mean/std in your analysis. Never remove outliers blindly.
What's the difference between copy() and a view in Pandas?
When you slice a DataFrame, you sometimes get a view (pointing to the original data) and sometimes a copy. Modifying a view raises the SettingWithCopyWarning and may or may not modify the original. To be safe, always call .copy() explicitly when you create a subset you plan to modify: df_subset = df[df['country'] == 'IN'].copy().
