--- title: Pandas date: 2026-07-19 background: bg-[#110750] tags: - data analysis - data manipulation categories: - Python intro: | [Pandas](https://pandas.pydata.org/) 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. plugins: - copyCode --- ## Getting Started {.cols-2} ### Import Convention ```python 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) ``` ### Creating DataFrames | - | - | | --------------------------------------------------------- | --------------------------- | | 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()` | ### Inspecting Data {.row-span-2} | - | - | | --------------- | -------------------------- | | 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` | ### Selecting Data | - | - | | ---------------------- | ------------------------------ | | 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)` | ### Data Cleaning | - | - | | --------------------------------------------- | ------------------------------------ | | 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)` | ### Adding/Removing Data {.row-span-2} | - | - | | -------------------------------------- | ------------------------------- | | 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. | ### Combining Data | - | - | | ----------------------------------------------------- | ----------------------- | | 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)` | ## Advanced Indexing ### MultiIndex / Hierarchical Indexing | - | - | | ----------------------------------------------------- | ----------------------- | | 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])` | ### Categorical Data | - | - | | ----------------------------------------------------- | ----------------------- | | 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()` | ## Time Series Operations ### Date Handling | - | - | | ----------------------------------------------------- | ----------------------- | | 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)` | ### Resampling & Frequency Conversion | - | - | | ----------------------------------------------------- | ----------------------- | | 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 CustomBusinessDay`
`offset = 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)` | ## Transformation Operations ### Reshaping | - | - | | ----------------------------------------------------- | ----------------------- | | 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)` | ### Aggregating Data | - | - | | ----------------------------------------------------- | ----------------------- | | 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())` | ### GroupBy Advanced | - | - | | ----------------------------------------------------- | ----------------------- | | 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()` | ### Window Functions | - | - | | ----------------------------------------------------- | ----------------------- | | 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)` | ### Applying Functions {.row-span-2} | - | - | | ------------------------------------ | ------------------------------------ | | 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(...)` | ### String Operations | - | - | | ------------------------------------ | ------------------------------------ | | 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()` | ## Performance & Memory Optimization ### Performance Settings | - | - | | ------------------------------------ | ------------------------------------ | | 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')` | ### Memory Optimization | - | - | | ------------------------------------ | ------------------------------------ | | 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)` | ## Input/Output {.cols-2} | - | - | | ---------------------------------------------- | ------------------------------------ | | 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')` | ## Common Pitfalls & Best Practices | 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 | ## Deprecations & Version Notes | 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 | ## Official Documentation & Resources - [pandas.pydata.org](https://pandas.pydata.org/) - [pandas GitHub](https://github.com/pandas-dev/pandas) - [PyArrow integration](https://arrow.apache.org/docs/python/pandas.html) - [API Reference](https://pandas.pydata.org/docs/reference/index.html) - [User Guide](https://pandas.pydata.org/docs/user_guide/index.html) ```