Python · Data Analysis · Pandas

Pandas
Cheatsheet

A quick reference guide for data wrangling and analysis using Pandas in Python — covering DataFrames, filtering, grouping, merging, and more.

Library: pandas ≥ 2.0

Language: Python 3

Level: Beginner → Intermediate

Topics: 10 sections

📖 View Official Documentation 📦 pandas on PyPI

📦 1. Importing & Setup

The standard convention — always import pandas as pd.

import pandas as pd
import numpy  as np

# Check version
print(pd.__version__)

# Global display options
pd.set_option('display.max_rows', 50)
pd.set_option('display.max_columns', 20)
pd.set_option('display.precision', 2)

🏗️ 2. Creating DataFrames

Build DataFrames from dicts, lists, CSV, Excel, JSON, and more.

From a dictionary

df = pd.DataFrame({
  'name': ['Alice', 'Bob'],
  'age':  [28, 34],
  'score':[88.5, 91.0]
})

From a list of lists

df = pd.DataFrame(
  [['Alice', 28],
   ['Bob',   34]],
  columns=['name', 'age']
)

Read from file

df = pd.read_csv('data.csv')
df = pd.read_excel('data.xlsx')
df = pd.read_json('data.json')
df = pd.read_sql(query, conn)

From a Series / range

s  = pd.Series([1,2,3], name='x')
df = s.to_frame()

# Empty DataFrame
df = pd.DataFrame(
  columns=['a','b','c'])

👀 3. Viewing Data

Quickly inspect shape, types, and sample rows.

Method	What it returns
df.head(n)	First n rows (default 5)
df.tail(n)	Last n rows (default 5)
df.shape	Tuple of (rows, columns)
df.info()	Column names, types, non-null counts, memory
df.describe()	Summary statistics: mean, std, min, max, quartiles
df.dtypes	Data type of each column
df.columns	Column labels as an Index
df.index	Row index
df.value_counts()	Frequency count of unique values in a Series
df.sample(n)	Random n rows

🎯 4. Selecting Data

Use .loc[] for label-based, .iloc[] for position-based selection.

# Single column → Series
df['age']
df.age

# Multiple columns → DataFrame
df[['name', 'age']]

# Rows by label  (loc)
df.loc[0]               # row with index 0
df.loc[0:3]             # rows 0-3 inclusive
df.loc[:, 'age':'score']  # columns age → score
df.loc[df['age'] > 30, ['name', 'score']]

# Rows by position (iloc)
df.iloc[0]               # first row
df.iloc[10:20]           # rows 10-19
df.iloc[:, [0, 2, 4]]   # columns at positions 0, 2, 4

# Single value
df.at[4, 'name']          # by label
df.iat[4, 1]             # by position

🔍 5. Filtering Data

Boolean masks and the .query() method both work great.

# Boolean mask
df[df['age'] > 30]
df[(df['age'] > 25) & (df['score'] >= 90)]

# .query() — cleaner syntax
df.query('age > 30')
df.query('age > 25 and score >= 90')

# isin — match a list
df[df['name'].isin(['Alice', 'Charlie'])]

# String contains (case-insensitive)
df[df['name'].str.contains('ali', case=False)]

# isna / notna
df[df['score'].isna()]
df[df['score'].notna()]

# Top / Bottom n
df.nlargest(5, 'score')
df.nsmallest(5, 'score')

🧹 6. Data Cleaning

Handle nulls, duplicates, types, and column names.

# Missing values
df.isnull().sum()             # count NaNs per column
df.dropna()                   # drop rows with any NaN
df.dropna(subset=['age'])     # only if 'age' is NaN
df.fillna(0)                  # replace NaN with 0
df['age'].fillna(df['age'].mean(), inplace=True)

# Duplicates
df.drop_duplicates()
df.drop_duplicates(subset=['name'], keep='first')

# Rename columns
df.rename(columns={'old_name': 'new_name'})
df.columns = df.columns.str.lower().str.replace(' ', '_')

# Drop columns / rows
df.drop(columns=['unnecessary_col'])
df.drop(index=[0, 1])

# Change data types
df['age'] = df['age'].astype(int)
df['date'] = pd.to_datetime(df['date'])
df['score'] = pd.to_numeric(df['score'], errors='coerce')

# Reset index
df.reset_index(drop=True, inplace=True)

📊 7. Grouping & Aggregation

Split-apply-combine with groupby().

# Basic groupby
df.groupby('category')['sales'].sum()
df.groupby('category')['sales'].mean()

# Multiple aggregations with agg()
df.groupby('category').agg(
  total_sales=('sales', 'sum'),
  avg_sales  =('sales', 'mean'),
  count      =('sales', 'count')
)

# Multi-column groupby
df.groupby(['region', 'category'])['sales'].sum()

# Pivot table
df.pivot_table(
  values='sales', index='region',
  columns='category', aggfunc='sum', fill_value=0
)

# Apply custom function
df.groupby('category').apply(lambda g: g.nlargest(3, 'sales'))

↕️ 8. Sorting

# Sort by column value
df.sort_values('score')                        # ascending
df.sort_values('score', ascending=False)      # descending
df.sort_values(['age', 'score'],
               ascending=[True, False])      # multi-column

# Sort by index
df.sort_index()
df.sort_index(ascending=False)

# Rank values
df['rank'] = df['score'].rank(method='dense', ascending=False)

🔗 9. Merging & Joining

Combine DataFrames with SQL-style joins.

# merge (SQL-style join)
pd.merge(df1, df2, on='id', how='inner')    # inner join
pd.merge(df1, df2, on='id', how='left')     # left join
pd.merge(df1, df2, on='id', how='right')    # right join
pd.merge(df1, df2, on='id', how='outer')    # full outer

# merge on different column names
pd.merge(df1, df2, left_on='user_id',
         right_on='id', how='left')

# concat — stack rows or columns
pd.concat([df1, df2])                         # append rows
pd.concat([df1, df2], axis=1)              # append columns
pd.concat([df1, df2], ignore_index=True)    # reset index

# join on index
df1.join(df2, how='left')

💾 10. Exporting Data

# CSV
df.to_csv('output.csv', index=False)

# Excel
df.to_excel('output.xlsx', index=False,
            sheet_name='Sheet1')

# JSON
df.to_json('output.json', orient='records')

# Parquet (fast columnar format)
df.to_parquet('output.parquet')

# SQL table
df.to_sql('table_name', conn, if_exists='replace',
          index=False)

# Clipboard (paste into Excel)
df.to_clipboard(index=False)