Pandas Cheat Sheet: 20 Operations CSV to Insights

The Problem With Most Pandas Tutorials

You install pandas, load a CSV, and immediately hit SettingWithCopyWarning. Or your datetime column parses as strings. Or groupby() returns something that isn’t a DataFrame and you don’t know why.

Most cheat sheets show you the syntax but skip the part where real data is messy and pandas has opinions about how you should use it. This guide covers 20 operations I actually use when exploring a new dataset — the ones that matter between “CSV loaded” and “I understand what’s in here.”

I’m using a realistic scenario: analyzing user activity logs from a fictional web app. The data has missing timestamps, mixed data types, and some rows that shouldn’t exist. Exactly the kind of mess you’ll see in practice.

Load and Inspect: The First 60 Seconds

Start by actually looking at what you loaded. Don’t assume the CSV is clean.

import pandas as pd
import numpy as np

# Explicit dtype parsing saves headaches later
df = pd.read_csv(
    'user_activity.csv',
    parse_dates=['timestamp'],
    dtype={'user_id': str, 'event_type': 'category'},
    na_values=['', 'NULL', 'N/A']
)

# First five rows — but also check the tail
print(df.head())
print(df.tail())

# Shape: (rows, columns)
print(f"Loaded {df.shape[0]} rows, {df.shape[1]} columns")

The parse_dates parameter handles timestamp parsing at load time. Doing it later with pd.to_datetime() works but you’re converting the entire column twice.

Operation 1: df.info() shows dtypes, non-null counts, and memory usage. If a numeric column shows as object, something’s wrong.

df.info()
# Output:
# <class 'pandas.core.frame.DataFrame'>
# RangeIndex: 125847 entries, 0 to 125846
# Data columns (total 5 columns):
#  #   Column      Non-Null Count   Dtype         
# ---  ------      --------------   -----         
#  0   user_id     125847 non-null  object        
#  1   timestamp   125103 non-null  datetime64[ns]
#  2   event_type  125847 non-null  category      
#  3   duration    119234 non-null  float64       
#  4   value       125847 non-null  int64         

Notice timestamp has 744 missing values. That’s going to matter.

Filter and Select: Get the Subset You Care About

Operation 2: Boolean indexing is how you filter rows. The syntax looks weird at first but you get used to it.

# Events longer than 30 seconds
long_events = df[df['duration'] > 30]

# Multiple conditions: use & (and) or | (or), wrap each in parentheses
recent_long = df[(df['duration'] > 30) & (df['timestamp'] > '2025-01-01')]

# Inverse: events that are NOT page views
not_pageviews = df[df['event_type'] != 'page_view']

Operation 3: .loc[] and .iloc[] for explicit row/column selection.

# .loc uses labels (row index, column names)
df.loc[0:5, ['user_id', 'event_type']]  # first 6 rows, two columns

# .iloc uses integer positions
df.iloc[0:5, 0:2]  # same thing, positional

Why both exist: .loc is inclusive on the end (0:5 includes row 5), .iloc is exclusive (standard Python slicing). I use .loc 90% of the time because column names are clearer than positions.

Operation 4: .isin() for categorical filters.

# Only login and logout events
auth_events = df[df['event_type'].isin(['login', 'logout'])]

# Users from a specific cohort
cohort_users = ['user_123', 'user_456', 'user_789']
cohort_data = df[df['user_id'].isin(cohort_users)]

Handle Missing Data: Don’t Ignore NaNs

Operation 5: Check where nulls are.

# Count of nulls per column
print(df.isnull().sum())

# Percentage of nulls
print(df.isnull().sum() / len(df) * 100)

# Rows with ANY null
rows_with_nulls = df[df.isnull().any(axis=1)]

In this dataset, duration is null for 6,613 rows (5.3%). Those are probably instant events like clicks where duration doesn’t apply.

Operation 6: Drop or fill nulls.

# Drop rows where timestamp is null (can't analyze events with no time)
df_clean = df.dropna(subset=['timestamp'])

# Fill duration nulls with 0 (assume instant events)
df['duration'] = df['duration'].fillna(0)

# Forward fill: propagate last valid value (useful for time series)
df['status'] = df['status'].fillna(method='ffill')

Be explicit about subset in dropna(). Dropping rows with ANY null (df.dropna()) often deletes half your data.

Group and Aggregate: Where Insights Happen

Operation 7: groupby() is the workhorse. It returns a GroupBy object, not a DataFrame — you need to apply an aggregation.

# Events per user
events_per_user = df.groupby('user_id').size()
print(events_per_user.head())

# Multiple aggregations
user_stats = df.groupby('user_id').agg({
    'event_type': 'count',      # total events
    'duration': ['mean', 'sum'], # avg and total duration
    'value': 'sum'               # total value
})

The multi-level column names (('duration', 'mean')) are annoying. Flatten them:

user_stats.columns = ['_'.join(col).strip() for col in user_stats.columns.values]
user_stats.reset_index(inplace=True)

Operation 8: value_counts() for frequency tables.

# Most common event types
print(df['event_type'].value_counts())

# As percentages
print(df['event_type'].value_counts(normalize=True))

This is faster than groupby('event_type').size() and returns a Series sorted by frequency.

Operation 9: Time-based grouping with pd.Grouper.

# Events per day
daily_events = df.groupby(pd.Grouper(key='timestamp', freq='D')).size()

# Events per hour
hourly_events = df.groupby(pd.Grouper(key='timestamp', freq='H')).size()

The freq parameter uses pandas offset aliases: 'D' (day), 'H' (hour), 'W' (week), 'M' (month end), 'MS' (month start). I always forget which is which and have to check the docs.

Sort and Rank: Find Extremes

Operation 10: sort_values() for ordering.

# Longest events first
df_sorted = df.sort_values('duration', ascending=False)

# Multi-column sort: by user, then by timestamp
df_sorted = df.sort_values(['user_id', 'timestamp'])

# Top 10 longest events
top_10 = df.nlargest(10, 'duration')

nlargest() and nsmallest() are faster than sort_values().head() for getting extremes.

Operation 11: Ranking within groups.

# Rank events by duration within each user (1 = longest)
df['duration_rank'] = df.groupby('user_id')['duration'].rank(ascending=False, method='dense')

# First event for each user
first_events = df[df['duration_rank'] == 1]

The method parameter handles ties: 'dense' (1, 2, 2, 3), 'min' (1, 2, 2, 4), 'average' (1, 2.5, 2.5, 4). I default to 'dense' because it’s easier to interpret.

Derive New Columns: Feature Engineering Basics

Operation 12: Create columns with simple assignment.

# Hour of day from timestamp
df['hour'] = df['timestamp'].dt.hour

# Day of week (0=Monday, 6=Sunday)
df['dow'] = df['timestamp'].dt.dayofweek

# Boolean flag
df['is_weekend'] = df['dow'].isin([5, 6])

The .dt accessor unlocks datetime operations. Without it you get an AttributeError.

Operation 13: Apply custom functions.

# Categorize duration
def duration_category(seconds):
    if pd.isnull(seconds) or seconds < 5:
        return 'instant'
    elif seconds < 30:
        return 'short'
    elif seconds < 300:
        return 'medium'
    else:
        return 'long'

df['duration_cat'] = df['duration'].apply(duration_category)

apply() is slow on large DataFrames because it’s essentially a Python loop. Vectorized operations (using numpy or pandas built-ins) are 10-100x faster. But for quick exploration, apply() is fine.

Operation 14: Binning continuous variables.

# Bin duration into quartiles
df['duration_quartile'] = pd.qcut(df['duration'], q=4, labels=['Q1', 'Q2', 'Q3', 'Q4'])

# Bin into fixed intervals
df['duration_bin'] = pd.cut(df['duration'], bins=[0, 10, 60, 300, np.inf], labels=['0-10s', '10-60s', '1-5m', '5m+'])

qcut splits by quantile (equal counts per bin), cut splits by value (equal bin widths). Use qcut for skewed distributions.

Merge and Join: Combine Multiple DataFrames

Operation 15: merge() for SQL-style joins.

# Load user metadata
users = pd.read_csv('users.csv')

# Left join: keep all events, add user info where available
df_merged = df.merge(users, on='user_id', how='left')

# Inner join: only events with matching users
df_inner = df.merge(users, on='user_id', how='inner')

The how parameter: 'left' (keep all left rows), 'right' (keep all right rows), 'inner' (only matches), 'outer' (all rows from both). I use 'left' 80% of the time.

Operation 16: concat() for stacking DataFrames.

# Combine January and February data
jan = pd.read_csv('jan.csv')
feb = pd.read_csv('feb.csv')
combined = pd.concat([jan, feb], ignore_index=True)

ignore_index=True resets the index to 0, 1, 2… instead of preserving original indices (which would have duplicates).

Pivot and Reshape: Change Data Layout

Operation 17: pivot_table() for cross-tabulations.

# Average duration by event type and hour
pivot = df.pivot_table(
    values='duration',
    index='event_type',
    columns='hour',
    aggfunc='mean'
)

This creates a matrix where rows are event types, columns are hours (0-23), and cells are average duration. Missing combinations show as NaN.

Operation 18: Melt wide data to long format.

# If you have columns like 'jan_sales', 'feb_sales', 'mar_sales'
# wide = pd.DataFrame({'product': ['A', 'B'], 'jan': [100, 200], 'feb': [110, 210]})
# long = wide.melt(id_vars='product', var_name='month', value_name='sales')

I rarely use melt() in exploratory work, but it’s essential for certain visualizations (e.g., seaborn prefers long format).

Quick Statistics: Understand Distributions

Operation 19: describe() for summary stats.

print(df['duration'].describe())
# Output:
# count    125847.000000
# mean         23.456789
# std          67.890123
# min           0.000000
# 25%           2.000000
# 50%          12.000000
# 75%          35.000000
# max        1823.000000

The max duration is 1823 seconds (30 minutes). That’s probably an outlier — someone left a tab open.

For categorical columns, describe() shows different stats:

print(df['event_type'].describe())
# count        125847
# unique            8
# top      page_view
# freq          67234

Operation 20: Correlation matrix.

# Pairwise correlations for numeric columns
corr = df[['duration', 'value', 'hour']].corr()
print(corr)

In this dataset, duration and value have correlation $\rho = 0.42$, suggesting longer sessions have higher value. The correlation coefficient $\rho$ is bounded by $-1 \leq \rho \leq 1$, where $\rho = 0$ means no linear relationship.

For a more robust measure when outliers are present, use Spearman rank correlation:

spearman_corr = df[['duration', 'value']].corr(method='spearman')

The SettingWithCopyWarning Nobody Explains

You’ll see this eventually:

# This triggers the warning
long_events = df[df['duration'] > 30]
long_events['duration_minutes'] = long_events['duration'] / 60
# SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame

What happened: long_events might be a view (reference) to the original df, not a copy. Modifying it could accidentally change df. Pandas can’t always tell, so it warns.

The fix: use .copy() explicitly.

long_events = df[df['duration'] > 30].copy()
long_events['duration_minutes'] = long_events['duration'] / 60  # No warning

Alternatively, use .loc[] for the assignment:

df.loc[df['duration'] > 30, 'duration_minutes'] = df['duration'] / 60

I just use .copy() everywhere to avoid thinking about it. Memory is cheap; debugging is expensive.

When Pandas Gets Slow

Pandas is fast enough for most exploratory work, but it’s single-threaded and loads everything into RAM. On this 125k-row dataset, operations are instant. At 10M rows, groupby() starts taking seconds. At 100M rows, you’ll run out of memory.

If you hit that wall, I covered Polars vs Pandas benchmarks on larger datasets. Polars uses lazy evaluation and parallel execution, cutting query time by 5-10x on the same hardware. But pandas is still the default for a reason — the ecosystem, documentation, and Stack Overflow coverage are unmatched.

For this beginner cheat sheet, stick with pandas. You’ll know when you’ve outgrown it.

What I’d Do Differently

If I were starting over, I’d learn the .query() method earlier. It’s cleaner than boolean indexing for complex filters:

# Instead of this
df[(df['duration'] > 30) & (df['event_type'] == 'page_view')]

# You can write this
df.query('duration > 30 and event_type == "page_view"')

It’s slightly slower (compiles the string expression), but the readability win is worth it.

I’d also use .pipe() more for chaining transformations. Instead of:

df = pd.read_csv('data.csv')
df = df.dropna()
df = df[df['duration'] > 0]
df['log_duration'] = np.log(df['duration'])

You can write:

df = (
    pd.read_csv('data.csv')
    .dropna()
    .query('duration > 0')
    .assign(log_duration=lambda x: np.log(x['duration']))
)

This is called method chaining. It’s harder to debug (intermediate steps aren’t saved), but it reads like a pipeline.

And if you’re doing this on a regular basis, grab Python for Data Analysis by Wes McKinney — the pandas creator wrote it, so it’s the canonical reference.

FAQ

Q: Should I use inplace=True to save memory?

No. It doesn’t actually save memory in most cases (pandas often makes a copy internally anyway), and it makes code harder to debug. Explicit assignment (df = df.dropna()) is clearer.

Q: What’s the difference between size(), count(), and len()?

size() counts all rows including NaNs. count() counts non-null values. len(df) is the number of rows. For groupby, size() returns a Series, count() returns a DataFrame with counts per column.

Q: How do I read Excel files?

Use pd.read_excel('file.xlsx', sheet_name='Sheet1'). Requires the openpyxl library (pip install openpyxl). If the Excel file is large (>10MB), export to CSV first — read_csv() is 10x faster.

Why These 20 Operations Matter

These aren’t the only pandas functions. The library has hundreds of methods. But in my experience, 80% of exploratory work uses this core set: load, inspect, filter, group, join, and derive new columns.

Once you’re comfortable with these, the rest of pandas is just variations. Need to resample time series? It’s groupby() with a time offset. Need to normalize columns? It’s apply() with a lambda. The mental model stays the same.

Start here. Build intuition with real messy data. The advanced stuff will make sense when you need it.