PANDAS
CHEAT SHEET.

Import & Setup

import pandas as pd
import numpy as np

Creating Data Structures

Series

s = pd.Series([1, 2, 3, 4, 5])
s = pd.Series([1, 2, 3], index=['a', 'b', 'c'])
s = pd.Series({'a': 1, 'b': 2, 'c': 3})

DataFrame

df = pd.DataFrame({
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Age': [25, 30, 35],
    'City': ['Delhi', 'Mumbai', 'Bangalore']
})

df = pd.DataFrame(data, columns=['col1', 'col2'], index=['row1', 'row2'])

Loading Data

# CSV
df = pd.read_csv('file.csv')
df = pd.read_csv('file.csv', index_col=0)
df = pd.read_csv('file.csv', usecols=['col1', 'col2'])
df = pd.read_csv('file.csv', dtype={'col1': str})

# Excel
df = pd.read_excel('file.xlsx', sheet_name='Sheet1')

# JSON
df = pd.read_json('file.json')

# SQL
import sqlite3
conn = sqlite3.connect('db.sqlite')
df = pd.read_sql_query('SELECT * FROM table', conn)

# Clipboard
df = pd.read_clipboard()

# Dictionary / List
df = pd.DataFrame.from_dict(data)
df = pd.DataFrame.from_records(list_of_dicts)

# Save
df.to_csv('output.csv', index=False)
df.to_excel('output.xlsx', index=False)
df.to_json('output.json')

Data Inspection

# View data
df.head()       # first 5 rows
df.head(10)     # first 10
df.tail()       # last 5 rows
df.sample(5)    # random 5 rows

# Summary
df.info()       # data types & non‑null counts
df.describe()   # statistics for numeric columns
df.describe(include='object')  # for categorical

# Shape
df.shape        # (rows, columns)
df.columns      # column names
df.index        # row index
df.dtypes       # data types

# Value counts
df['column'].value_counts()
df['column'].value_counts(normalize=True)  # proportions

Selecting & Filtering

Column Selection

df['col']                # single column → Series
df[['col1', 'col2']]     # multiple columns → DataFrame
df.col                   # (if column name is valid)

Row Selection (loc / iloc)

# iloc – position‑based
df.iloc[0]           # first row
df.iloc[0:5]         # rows 0‑4
df.iloc[:, 0]        # first column
df.iloc[0:5, 0:2]    # rows 0‑4, columns 0‑1

# loc – label‑based
df.loc[0]            # row with index 0
df.loc[0:5]          # rows 0‑5
df.loc[:, 'col']     # column by name
df.loc[0:5, ['col1', 'col2']]

Boolean Filtering

df[df['Age'] > 25]
df[(df['Age'] > 25) & (df['City'] == 'Delhi')]
df[(df['Age'] > 25) | (df['City'] == 'Delhi')]

# isin
df[df['City'].isin(['Delhi', 'Mumbai'])]

# string methods
df[df['Name'].str.startswith('A')]
df[df['Name'].str.contains('li')]

# notna / isnull
df[df['Age'].notna()]
df[df['Age'].isnull()]

query

df.query('Age > 25')
df.query('Age > 25 and City == "Delhi"')
df.query('Age in [25, 30, 35]')

Adding / Modifying Columns

df['new_col'] = [1, 2, 3]
df['new_col'] = df['col1'] + df['col2']
df['new_col'] = df['col'].apply(lambda x: x * 2)
df['new_col'] = np.where(df['Age'] > 30, 'Senior', 'Junior')

# Insert at specific position
df.insert(2, 'new_col', [1, 2, 3])

# Assign (multiple columns)
df = df.assign(new_col1 = df['col1'] * 2,
               new_col2 = df['col2'] + 1)

# Rename columns
df.rename(columns={'old': 'new'}, inplace=True)
df.rename(columns={'col1': 'Name', 'col2': 'Age'}, inplace=True)

# Drop columns
df.drop('col', axis=1, inplace=True)
df.drop(['col1', 'col2'], axis=1, inplace=True)

# Drop rows
df.drop(0, axis=0, inplace=True)       # drop row 0
df.drop([0, 1, 2], axis=0, inplace=True)

Sorting

df.sort_values('col')                          # ascending
df.sort_values('col', ascending=False)             # descending
df.sort_values(['col1', 'col2'])                   # multiple columns
df.sort_index()                                    # sort by index

Grouping & Aggregation

groupby

df.groupby('col')                         # group by column
df.groupby('col')['other'].mean()         # groupby + aggregation
df.groupby('col').agg({
    'col1': 'mean',
    'col2': ['min', 'max'],
    'col3': lambda x: x.sum()
})

# Multiple aggregations (named)
df.groupby('col').agg(
    avg_score=('score', 'mean'),
    max_score=('score', 'max'),
    count=('score', 'size')
)

Pivot Tables

pd.pivot_table(df, values='value', index='row', columns='col', aggfunc='mean')
pd.pivot_table(df, values='value', index='row', columns='col', aggfunc=['sum', 'mean'], fill_value=0)

Aggregation Functions

df['col'].mean()       # average
df['col'].sum()        # sum
df['col'].min()        # minimum
df['col'].max()        # maximum
df['col'].std()        # standard deviation
df['col'].var()        # variance
df['col'].median()     # median
df['col'].quantile(0.75)  # 75th percentile
df['col'].count()      # non‑null count
df['col'].nunique()    # number of unique values
df['col'].unique()     # unique values

Merging & Joining

merge

pd.merge(df1, df2, on='key')
pd.merge(df1, df2, on=['key1', 'key2'])
pd.merge(df1, df2, left_on='left_key', right_on='right_key')

# Join types
pd.merge(df1, df2, on='key', how='inner')   # default
pd.merge(df1, df2, on='key', how='left')    # left join
pd.merge(df1, df2, on='key', how='right')   # right join
pd.merge(df1, df2, on='key', how='outer')   # full outer

# Suffixes for duplicate columns
pd.merge(df1, df2, on='key', suffixes=('_left', '_right'))

concat

# Row‑wise (stack vertically)
pd.concat([df1, df2])                # defaults axis=0
pd.concat([df1, df2], ignore_index=True)

# Column‑wise (stack horizontally)
pd.concat([df1, df2], axis=1)
pd.concat([df1, df2], axis=1, join='inner')

join

df1.join(df2)                             # join on index
df1.join(df2, on='key')                  # join on column
df1.join(df2, how='left', lsuffix='_l', rsuffix='_r')

Handling Missing Data

# Check for missing
df.isnull()
df.isnull().sum()
df.isnull().sum().sum()

# Drop missing
df.dropna()                     # drop rows with any NaN
df.dropna(how='all')            # drop rows where all NaN
df.dropna(thresh=2)             # drop rows with < 2 non‑null
df.dropna(subset=['col1', 'col2'])  # drop rows with NaN in specific columns
df.dropna(axis=1)               # drop columns with NaN

# Fill missing
df.fillna(0)                    # fill with 0
df.fillna('missing')            # fill with string
df.fillna(df.mean())            # fill with mean
df.fillna(method='ffill')       # forward fill
df.fillna(method='bfill')       # backward fill
df.fillna({'col1': 0, 'col2': 'unknown'})  # per column

# Interpolate
df.interpolate()                # linear interpolation

Applying Functions

# apply (row/column)
df['col'].apply(lambda x: x * 2)
df.apply(lambda row: row['col1'] + row['col2'], axis=1)  # row‑wise
df.apply(np.mean, axis=0)       # column‑wise

# applymap (element‑wise)
df.applymap(lambda x: x * 2)

# map (Series only)
df['col'].map({'old': 'new', 'old2': 'new2'})
df['col'].map(lambda x: x * 2)

# replace
df.replace(0, 100)              # replace all 0s with 100
df.replace({'col1': 0}, 100)    # replace in specific column
df.replace({'old': 'new'})      # replace values

Data Types

# Change data type
df['col'] = df['col'].astype(str)
df['col'] = df['col'].astype('int64')
df['col'] = pd.to_numeric(df['col'], errors='coerce')
df['col'] = pd.to_datetime(df['col'])
df['col'] = pd.to_timedelta(df['col'])

# Categorical
df['col'] = df['col'].astype('category')
df['col'].cat.codes          # integer codes

Time Series

# Convert to datetime
df['date'] = pd.to_datetime(df['date'])
df['date'] = pd.to_datetime('2024-01-01')

# Set as index
df.set_index('date', inplace=True)

# Resample
df.resample('D').mean()      # daily
df.resample('W').sum()       # weekly
df.resample('M').max()       # monthly
df.resample('Q').agg(['mean', 'sum'])  # quarterly

# Shift / Lag
df['shifted'] = df['col'].shift(1)   # previous row
df['diff'] = df['col'].diff()        # difference
df['pct_change'] = df['col'].pct_change()  # percent change

# Rolling window
df['rolling_mean'] = df['col'].rolling(7).mean()
df['expanding_mean'] = df['col'].expanding().mean()

Reshaping

melt (wide → long)

df_melted = pd.melt(df, id_vars=['id'], value_vars=['col1', 'col2'])
df_melted = pd.melt(df, id_vars=['id'], var_name='variable', value_name='value')

pivot (long → wide)

df_pivoted = df.pivot(index='row', columns='col', values='value')
df_pivoted = df.pivot_table(index='row', columns='col', values='value', aggfunc='mean')

stack / unstack

df_stacked = df.stack()      # columns → rows
df_unstacked = df.unstack()  # rows → columns

Common Data Cleaning

# Remove duplicates
df.drop_duplicates()                     # drop duplicate rows
df.drop_duplicates(subset=['col1'])      # based on specific columns
df.drop_duplicates(keep='last')          # keep last occurrence

# Strip whitespace
df['col'] = df['col'].str.strip()

# String operations
df['col'].str.lower()
df['col'].str.upper()
df['col'].str.len()
df['col'].str.split(' ')
df['col'].str.contains('pattern')
df['col'].str.extract(r'(\d+)')

# Filter by regex
df[df['col'].str.match(r'^[A-Z]')]

Performance Tips

• Use vectorised operations over loops – they are much faster.
• Use inplace=True to avoid copying data.
• Use df.query() for complex filters.
• Use df.eval() for complex expressions.
• Use df.memory_usage() to check memory usage.
• Use df.astype({'col': 'category'}) to reduce memory.
• Use df.to_numpy() for faster NumPy operations.