Pandas is a powerful data analysis and manipulation library for Python. This cheat sheet provides a comprehensive reference for modern pandas (≥2.2) with emphasis on performance, nullable dtypes, and PyArrow integration.
import pandas as pd
import numpy as np
# Enable future features (Pandas 2.2+)
pd.options.future.infer_string = True # Arrow-backed strings
pd.options.mode.copy_on_write = True # Lazy copying, memory efficient
pd.set_option('display.max_columns', None) # Full display in notebooks
# Optional: suppress performance warnings
import warnings
warnings.filterwarnings('ignore', category=pd.errors.PerformanceWarning)
| - | - |
|---|---|
| From a dictionary | pd.DataFrame({'col1': [1, 2], 'col2': [3, 4]}) |
| From a list of dictionaries | pd.DataFrame([{'a': 1, 'b': 2}, {'a': 3, 'b': 4}]) |
| From a CSV file (optimized) | pd.read_csv('file.csv', engine='pyarrow', dtype_backend='pyarrow') |
| From a Parquet file | pd.read_parquet('file.parquet', engine='pyarrow') |
| From an Excel file | pd.read_excel('file.xlsx', engine='openpyxl') |
| From a SQL query | pd.read_sql('SELECT * FROM table', con, index_col='id', parse_dates=['date']) |
| From a dictionary of arrays | pd.DataFrame.from_dict(data, orient='index') |
| From a list of tuples | pd.DataFrame.from_records([(1,2),(3,4)], columns=['a','b']) |
| Empty DataFrame with columns | pd.DataFrame(columns=['col1','col2']) |
| From a JSON file | pd.read_json('file.json', orient='records', lines=True) |
| From clipboard | pd.read_clipboard() |
| - | - |
|---|---|
| First n rows (default 5) | df.head(10) |
| Last n rows (default 5) | df.tail(10) |
| Number of rows and columns | df.shape |
| Detailed info (memory, dtypes, nulls) | df.info(show_counts=True, memory_usage='deep') |
| Summary statistics for numeric columns | df.describe(percentiles=[.25,.5,.75], include='all') |
| Column names as list | df.columns.tolist() |
| Index object | df.index |
| Data types of columns | df.dtypes |
| Memory usage per column | df.memory_usage(deep=True) |
| Value counts with proportions | df['col'].value_counts(dropna=False, normalize=True) |
| Unique values in column | df['col'].unique() |
| Number of unique values | df['col'].nunique(dropna=False) |
| Random sample of rows | df.sample(n=5, random_state=42) |
| Check if DataFrame is empty | df.empty |
| - | - |
|---|---|
| Select a single column (as Series) | df['col'] or df.col |
| Select multiple columns | df[['col1', 'col2']] |
| Select rows by label index | df.loc[5:10] |
| Select rows by integer position | df.iloc[0:5] |
| Select a specific cell by label | df.at[5, 'col'] |
| Select a specific cell by position | df.iat[0, 1] |
| Filter rows by condition | df[df['col'] > 2] |
| Filter using query string (fast) | df.query('col > 2 and col2 == "foo"') |
| Filter with multiple conditions | df[(df['col']>2) & (df['col2']=='bar')] |
| Select columns by data type | df.select_dtypes(include=['number', 'bool']) |
| Select first row | df.iloc[0] |
| Select last row | df.iloc[-1] |
| Select random row | df.sample(1) |
| - | - |
|---|---|
| Drop rows with any missing values | df.dropna(how='any', axis=0) |
| Drop rows where all values are missing | df.dropna(how='all', axis=0) |
| Drop columns with any missing values | df.dropna(axis=1, how='any') |
| Drop columns where all values are missing | df.dropna(axis=1, how='all') |
| Fill missing values with a scalar | df.fillna(0, inplace=False) |
| Forward fill missing values | df.ffill(axis=0) |
| Backward fill missing values | df.bfill(axis=0) |
| Interpolate missing values | df.interpolate(method='linear', limit_direction='both') |
| Replace values using dict or regex | df.replace({'old': 'new'}, regex=True) |
| Rename columns | df.rename(columns={'old':'new'}) |
| Rename index labels | df.rename(index={0:'first'}) |
| Drop duplicate rows | df.drop_duplicates(subset=['col'], keep='first') |
| Change column data type (nullable int) | df = df.astype({'col': 'Int64'}) |
| Convert to Arrow-backed string dtype | df['col'] = df['col'].astype('string[pyarrow]') |
| Drop columns with variance below threshold | df.loc[:, df.std() > threshold] |
| Clip values to a range | df['col'].clip(lower=0, upper=100) |
| - | - |
|---|---|
| Add a new column by assignment | df['new'] = df['a'] + df['b'] |
| Add multiple new columns | df.assign(new1 = df.a + df.b, new2 = df.a - df.b) |
| Insert column at a specific position | df.insert(2, 'new_col', values) |
| Remove and return a column | df.pop('col') |
| Drop a column | df.drop('col', axis=1, inplace=False) |
| Drop a row by index label | df.drop(0, axis=0) |
| Add a single row | df.loc[len(df)] = [1,2,3] |
| Concatenate DataFrames row-wise | pd.concat([df1, df2], ignore_index=True) |
| Concatenate DataFrames column-wise | pd.concat([df1, df2], axis=1) |
| Concatenate with group keys | pd.concat([df1, df2], keys=['A','B']) |
Note: df.append() is deprecated. Use pd.concat() instead. |
| - | - |
|---|---|
| Merge two DataFrames on a key (inner join) | pd.merge(df1, df2, on='key') |
| Merge two DataFrames (outer join) | pd.merge(df1, df2, on='key', how='outer') |
| Merge two DataFrames (left/right join) | pd.merge(df1, df2, on='key', how='left') |
| Merge on different key columns | pd.merge(df1, df2, left_on='key1', right_on='key2') |
| Merge with validation and metadata | pd.merge(df1, df2, on='key', validate='1:m', suffixes=('_l','_r'), indicator=True) |
| Join DataFrames on index | df1.join(df2, how='inner', lsuffix='_l', rsuffix='_r') |
| Cartesian product (cross join) | df1.join(df2, how='cross') |
| Fill missing values from another DataFrame | df1.combine_first(df2) |
| - | - |
|---|---|
| Create a MultiIndex from tuples | pd.MultiIndex.from_tuples([('A',1), ('A',2), ('B',1), ('B',2)]) |
| Set multiple columns as index | df.set_index(['col1', 'col2']) |
| Select all rows at first level | df.loc[('A', slice(None)), 'x'] |
| Cross-section selection | df.xs('A', level=0) |
| Get values of a specific index level | df.index.get_level_values(0) |
| Pivot a level to columns (unstack) | df.unstack(level=0) |
| Compress columns to index (stack) | df.stack() |
| Swap levels of MultiIndex | df.swaplevel() |
| Sort by a specific index level | df.sort_index(level=1) |
| Reorder index levels | df.reorder_levels([1,0]) |
| - | - |
|---|---|
| Create an ordered categorical column | df['cat'] = pd.Categorical(df['col'], categories=['low','med','high'], ordered=True) |
| Get integer codes for categories | df['cat'].cat.codes |
| Reorder categories | df['cat'].cat.reorder_categories(['high','med','low']) |
| Add new categories | df['cat'].cat.add_categories(['very_high']) |
| Remove unused categories | df['cat'].cat.remove_unused_categories() |
| Group by categorical (skip unobserved) | df.groupby('cat', observed=True).mean() |
| - | - |
|---|---|
| Convert string column to datetime (with format) | df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d') |
| Extract year from datetime | df['year'] = df['date'].dt.year |
| Extract month from datetime | df['month'] = df['date'].dt.month |
| Extract day from datetime | df['day'] = df['date'].dt.day |
| Extract day of week (0=Monday) | df['dayofweek'] = df['date'].dt.dayofweek |
| Extract quarter | df['quarter'] = df['date'].dt.quarter |
| Check if date is weekend | df['is_weekend'] = df['date'].dt.dayofweek >= 5 |
| Set datetime column as index | df.set_index('date', inplace=True) |
| Generate a date range | pd.date_range('2026-01-01', periods=100, freq='D') |
| Add a date offset to each date | df['date'] + pd.DateOffset(months=1) |
| - | - |
|---|---|
| Downsample to monthly frequency (aggregate) | df.resample('M').mean() |
| Upsample to daily frequency (forward fill) | df.resample('D').ffill(limit=2) |
| Custom aggregation during resampling | df.resample('QE-DEC').agg(['sum', 'mean']) |
| Resample with custom label and closed side | df.resample('W-MON', label='right', closed='right') |
| Business quarter frequency | df.resample('BQ').mean() |
| Custom business days (skip holidays) | from pandas.tseries.offsets import CustomBusinessDayoffset = CustomBusinessDay(weekmask='Mon Tue Wed Thu Fri', holidays=['2026-07-04'])df.resample(offset) |
| Change frequency without aggregation (asfreq) | df.asfreq('D', method='ffill', limit=2) |
| Shift values forward/backward | df['shifted'] = df['value'].shift(1) |
| Create lag/lead columns | df['lag_3'] = df['value'].shift(3) |
| - | - |
|---|---|
| Pivot without aggregation (exact reshape) | df.pivot(index='date', columns='variable', values='value') |
| Pivot with aggregation (pivot table) | pd.pivot_table(df, values='value', index='date', columns='var', aggfunc='mean', fill_value=0, margins=True) |
| Melt from wide to long format | df.melt(id_vars=['date'], value_vars=['a','b'], var_name='var', value_name='val') |
| Stack/unstack MultiIndex | df.stack(future_stack=True) / df.unstack() |
| Cross-tabulation with normalization | pd.crosstab(df['col1'], df['col2'], normalize='columns', margins=True, margins_name='Total') |
| Explode list-like column into rows | df.explode('list_column', ignore_index=True) |
| - | - |
|---|---|
| Sum of values | df['col'].sum(skipna=True) |
| Mean of values | df['col'].mean() |
| Count of non-NA values | df['col'].count() |
| Minimum value | df['col'].min() |
| Maximum value | df['col'].max() |
| Standard deviation (sample) | df['col'].std(ddof=1) |
| Variance (sample) | df['col'].var(ddof=1) |
| Quantile (e.g., 75th percentile) | df['col'].quantile(0.75) |
| Group by a column and sum | df.groupby('col1')['col2'].sum() |
| Group by and aggregate multiple columns | df.groupby('col1').agg({'col2': ['sum','mean'], 'col3': 'max'}) |
| Named aggregation (new style) | df.groupby('group').agg(avg_value=('value','mean'), max_date=('date','max'), count=('value','count')) |
| Custom aggregation function | df.groupby('col1').agg(lambda x: x.max() - x.min()) |
| - | - |
|---|---|
| Transform: broadcast result to original rows | df['mean_per_group'] = df.groupby('group')['value'].transform('mean') |
| Filter groups based on condition | df.groupby('group').filter(lambda x: x['value'].sum() > 10) |
| Apply custom function to each group | df.groupby('group').apply(lambda g: g.sort_values('date').head(2), include_groups=False) |
| Pipe transformation after groupby | df.groupby('group').pipe(lambda g: g['value'].transform(lambda x: (x - x.mean()) / x.std())) |
| Groupby with observed categoricals | df.groupby('category', observed=True).mean() |
| - | - |
|---|---|
| Rolling mean with window size 5 | df['col'].rolling(window=5, min_periods=3, center=True).mean() |
| Multiple aggregations on rolling window | df['col'].rolling(3).agg(['sum','mean', lambda x: x.max() - x.min()]) |
| Rolling with custom weights | df['col'].rolling(window=10, win_type='gaussian', weights=lambda x: np.exp(-x**2)).mean() |
| Expanding statistics (all data up to current point) | df['col'].expanding(min_periods=2).std() |
| Exponentially weighted moving average | df['col'].ewm(span=10, adjust=True, ignore_na=False).mean() |
| Exponentially weighted variance | df['col'].ewm(alpha=0.3, min_periods=5).var() |
| Apply custom function to rolling window | df['col'].rolling(3).apply(lambda x: x.sum(), raw=True) |
| - | - |
|---|---|
| Apply function element-wise to a column | df['col'].apply(lambda x: x**2) |
| Apply function row-wise | df.apply(lambda row: row['a'] + row['b'], axis=1) |
| Apply function element-wise to entire DataFrame | df.map(lambda x: x.upper() if isinstance(x,str) else x) (Note: applymap is deprecated) |
| Map values using a dictionary | df['col'].map({'a':1,'b':2}, na_action='ignore') |
| Vectorized string operations | df['col'].str.upper(), df['col'].str.contains('pattern') |
| Vectorized datetime operations | df['date'].dt.year, df['date'].dt.dayofweek |
| Fast arithmetic using eval (numexpr) | df.eval('c = a * b + 2 * d', engine='numexpr') |
| Fast filtering using query (numexpr) | df.query('a > b & c.str.contains("foo")') |
| Chain operations with pipe | df.pipe(lambda d: d.assign(ratio=d.a/d.b)).pipe(...) |
| - | - |
|---|---|
| Convert to uppercase/lowercase | df['col'].str.upper(), df['col'].str.lower() |
| Check if string contains pattern | df['col'].str.contains('pattern', regex=True, na=False) |
| Split string into columns | df['col'].str.split(',', expand=True, n=1) |
| Replace using regular expression | df['col'].str.replace(r'\s+', '_', regex=True) |
| Extract capture groups into columns | df['col'].str.extract(r'(\d+)-(\w+)') |
| Extract all matches | df['col'].str.extractall(r'(\d+)') |
| Find all matches (returns list) | df['col'].str.findall(r'\d+') |
| Concatenate strings with separator | df['col'].str.cat(sep='|') |
| Pad strings to fixed width | df['col'].str.pad(width=10, side='left', fillchar='0') |
| Extract substring | df['col'].str.slice(start=0, stop=5) |
| Get string length | df['col'].str.len() |
| Check start/end of string | df['col'].str.startswith('prefix'), df['col'].str.endswith('.csv') |
| Strip whitespace | df['col'].str.strip(), df['col'].str.lstrip(), df['col'].str.rstrip() |
| - | - |
|---|---|
| Enable copy-on-write (Pandas 2.0+) | pd.options.mode.copy_on_write = True |
| Fast arithmetic with eval | df.eval('c = a * b + 2 * d', engine='numexpr') |
| Fast filtering with query | df.query('a > b and c == "foo"', engine='numexpr') |
| Read large CSV in chunks | for chunk in pd.read_csv('large.csv', chunksize=100000, dtype_backend='pyarrow'): process(chunk) |
| Convert to numpy array (avoid copy) | arr = df[['a','b']].to_numpy(copy=False) |
| Memory map large files | df = pd.read_csv('large.csv', memory_map=True) |
| Use PyArrow backend for CSV | pd.read_csv('file.csv', engine='pyarrow', dtype_backend='pyarrow') |
| - | - |
|---|---|
| Downcast integer columns | def downcast(df): for col in df.select_dtypes(include='integer'): df[col] = pd.to_numeric(df[col], downcast='unsigned') for col in df.select_dtypes(include='float'): df[col] = pd.to_numeric(df[col], downcast='float') return df |
| Convert to categorical if low cardinality | for col in df.select_dtypes(include='object'): if df[col].nunique() / len(df) < 0.05: df[col] = df[col].astype('category') |
| Convert all to Arrow dtypes | df = df.convert_dtypes(dtype_backend='pyarrow') |
| Use sparse arrays for zeros | df['sparse'] = pd.arrays.SparseArray(df['col'], fill_value=0) |
| Diagnose memory usage | df.memory_usage(deep=True) |
| - | - |
|---|---|
| Save to CSV (optimized) | df.to_csv('file.csv', index=False, encoding='utf-8') |
| Load from CSV (optimized) | df = pd.read_csv('file.csv', engine='pyarrow', dtype_backend='pyarrow') |
| Save to Parquet (fast, compressed) | df.to_parquet('file.parquet', engine='pyarrow') |
| Load from Parquet | df = pd.read_parquet('file.parquet', engine='pyarrow') |
| Save to Excel | df.to_excel('file.xlsx', sheet_name='Sheet1', engine='openpyxl') |
| Load from Excel | df = pd.read_excel('file.xlsx', sheet_name=0, engine='openpyxl') |
| Save to JSON (line-delimited) | df.to_json('file.json', orient='records', lines=True) |
| Load from JSON (line-delimited) | df = pd.read_json('file.json', orient='records', lines=True) |
| Save to SQL table | df.to_sql('table', con, if_exists='replace', index=False) |
| Load from SQL query | df = pd.read_sql('SELECT * FROM table', con, index_col='id', parse_dates=['date']) |
| Copy to/from clipboard | df.to_clipboard() / pd.read_clipboard() |
| Save to/from pickle (fast but unsafe) | df.to_pickle('file.pkl') / pd.read_pickle('file.pkl') |
| Save to/from Feather (fast) | df.to_feather('file.feather') / pd.read_feather('file.feather') |
| Save to/from HDF5 | df.to_hdf('file.h5', key='data', mode='w') / pd.read_hdf('file.h5', key='data') |
| Pitfall | Solution |
|---|---|
| Chained indexing (creates views) | Use df.loc[df.a > 0, 'b'] instead of df[df.a > 0]['b'] |
| In-place mutation of large DataFrames | Use inplace=False (default) for method chaining; avoid mutation unless necessary |
| Slicing without copy | Use df.copy() when you need an independent slice |
| Slow datetime parsing | Always specify format in pd.to_datetime() for 10x speedup |
| Memory bloat from object dtype | Use categorical dtype for low cardinality columns (<5% unique) |
| Ambiguous merge relationships | Specify validate in merge ('1:1', '1:m', etc.) to catch errors |
| Groupby with unused categories | Use observed=True to skip unobserved categories in groupby |
Slow loops with apply |
Use vectorized operations when possible (df['a'] + df['b'] vs apply) |
Deprecated append() |
Use pd.concat([df, other]) instead |
Deprecated applymap() |
Use df.map() instead (Pandas 2.2+) |
Missing pd.NA handling |
Check for pd.NA when using nullable dtypes |
| Large CSV without optimization | Enable copy-on-write and use PyArrow backend |
| Version | Deprecated/Removed | Replacement |
|---|---|---|
| 1.4.0 | df.append() |
pd.concat([df, other]) |
| 2.2.0 | df.applymap() |
df.map() |
| 2.0.0 | df.astype('int') with NA |
Use nullable 'Int64' |
| 2.0.0 | Inferred string dtype | Use pd.options.future.infer_string = True |
| 2.0.0 | pd.DataFrame.to_csv() with compression |
Use compression='gzip' explicitly |