pandas-performance

# pandas - Performance & Memory Management Standard pandas code is often memory-hungry and slow. This sub-skill provides the techniques to make pandas 10x faster and use 5x less RAM by understanding its internal architecture (BlockManager and Arrow backend). ## When to Use - Your DataFrame is larger than 1GB and causes RAM pressure. - `pd.read_csv` is taking too long to load data. - Row-wise operations (`apply`, `iterrows`) are creating bottlenecks. - You need to perform complex joins or lookups on millions of rows. - Preparing data for high-performance ML models. ## Reference Documentation - **Official Performance Guide**: https://pandas.pydata.org/docs/user_guide/enhancingperf.html - **Scaling to Large Data**: https://pandas.pydata.org/docs/user_guide/scale.html - **Search patterns**: `df.memory_usage`, `pd.to_numeric(downcast=...)`, `pd.Categorical`, `DataFrame.eval()` ## Core Principles ### RAM is the Bottleneck Pandas usually creates copies of data during operations. To handle large data, you must minimize copies and use the most efficient bit-width for your data types. ### Vectorization vs. Loops - **Level 1 (Best)**: Built-in NumPy/Pandas vectorized functions. - **Level 2 (Good)**: `df.eval()` or `df.query()` for complex math. - **Level 3 (Average)**: `np.vectorize` or `df.apply()` (only if logic is complex). - **Level 4 (Worst)**: `iterrows()` or `itertuples()`. ## Memory Optimization Patterns ### 1. The "Downcasting" Workflow Standard integer and float columns use 64 bits by default. Most scientific data fits in 16 or 32 bits. ```python import pandas as pd import numpy as np def optimize_memory(df): start_mem = df.memory_usage().sum() / 1024**2 for col in df.columns: col_type = df[col].dtype if col_type != object: c_min = df[col].min() c_max = df[col].max() if str(col_type)[:3] == 'int': if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max: df[col] = df[col].astype(np.int8) elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max: df[col] = df[col].astype(np.int16) else: if c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max: df[col] = df[col].astype(np.float32) else: # Convert low-cardinality strings to Categorical num_unique = df[col].nunique() if num_unique / len(df) < 0.5: df[col] = df[col].astype('category') end_mem = df.memory_usage().sum() / 1024**2 print(f'Memory reduced by {100 * (start_mem - end_mem) / start_mem:.1f}%') return df ``` ### 2. Modern PyArrow Backend (Pandas 2.0+) Use the Arrow backend for massive speedups in string operations and faster loading. ```python # Load with Arrow engine for 2-3x speedup df = pd.read_csv("data.csv", engine="pyarrow", dtype_backend="pyarrow") ``` ## Speed Optimization Patterns ### 1. Vectorizing Complex "If-Else" (Instead of .apply) Instead of calling a Python function for every row: ```python # ❌ SLOW: # df['status'] = df.apply(lambda x: 'High' if x['val'] > 100 else 'Low', axis=1) # ✅ FAST: df['status'] = np.where(df['val'] > 100, 'High', 'Low') # ✅ FAST (Multiple conditions): conditions = [ (df['val'] > 100), (df['val'] > 50) & (df['val'] <= 100), (df['val'] <= 50) ] choices = ['High', 'Medium', 'Low'] df['status'] = np.select(conditions, choices, default='Unknown') ``` ### 2. High-Speed Lookups If you need to map values from a dictionary/other table millions of times: ```python # ❌ SLOW: df.merge() or df['id'].map(large_dict) # ✅ FAST: Use a Series as a lookup table with index lookup_table = pd.Series(data=values, index=keys) result = lookup_table.reindex(df['target_ids']).values ``` ## Efficient I/O ### 1. Parquet with Filtering (Predicate Pushdown) Never use CSV for large data storage. Use Parquet. ```python # Save as partitioned parquet df.to_parquet('data_dir', partition_cols=['year', 'month']) # Load only specific columns and rows (Fast) df_subset = pd.read_parquet('data_dir', columns=['price', 'id'], filters=[('year', '==', 2023)]) ``` ### 2. Chunking for Memory-Limited Systems If the file is 50GB and you have 16GB RAM: ```python # Process in chunks of 100k rows chunk_size = 100_000 for chunk in pd.read_csv("massive.csv", chunksize=chunk_size): # Perform aggregation summary = chunk.groupby('id')['value'].sum() # Save or update a running total ``` ## Critical Rules for Performance ### ✅ DO - **Use In-place operations sparingly** - Contrary to myth, `inplace=True` often creates internal copies anyway. Focus on dtypes instead. - **Sort Index for Slicing** - If you slice a large DataFrame by index, ensure it is sorted: `df.sort_index(inplace=True)`. This turns an O(N) operation into O(log N). - **Use pd.to_datetime with format** - Specifying the format (`%Y-%m-%d`) is much faster than automatic parsing. - **Leverage .eval()** - For complex arithmetic like `(A + B) / (C * D)`, `df.eval()` is faster and more memory-efficient as it uses numexpr. ### ❌ DON'T - **Never iterate with iterrows()** - It converts each row into a Series object, which is incredibly slow. - **Avoid object dtypes** - Any column with object dtype (usually strings) is a pointer to a Python object, which is memory-intensive. Use `category` or `string[pyarrow]`. - **Don't use append() in a loop** - It creates a full copy of the DataFrame every time. Collect data in a list and use `pd.concat()`. ## Anti-Patterns (NEVER) ```python # ❌ BAD: Growing a DataFrame row by row df = pd.DataFrame() for data in large_source: df = pd.concat([df, pd.DataFrame([data])]) # ❌ Disaster for performance! # ✅ GOOD: List of dicts to DataFrame data_list = [] for data in large_source: data_list.append(data) df = pd.DataFrame(data_list) # ❌ BAD: Manual string formatting # df['name'].apply(lambda x: f"USER_{x}") # ✅ GOOD: Vectorized string accessor df['name'] = "USER_" + df['name'].astype(str) ``` ## Practical Workflows ### 1. Identifying Memory Hogs ```python # Get detailed memory breakdown (including object overhead) print(df.memory_usage(deep=True)) # Identify columns with too many unique strings (bad for 'category') for col in df.select_dtypes(include=['object']): print(f"{col}: {df[col].nunique() / len(df):.2%}") ``` ### 2. Fast Deduplication of 10M+ Rows ```python # Using sorting + shift is often faster than drop_duplicates df = df.sort_values(['id', 'timestamp']) mask = (df['id'] != df['id'].shift()) df_unique = df[mask] ``` ### 3. Merging with Multi-Index ```python # If you join on multiple columns, setting them as an index # and using join() can be 5x faster than merge() df1.set_index(['key1', 'key2'], inplace=True) df2.set_index(['key1', 'key2'], inplace=True) result = df1.join(df2, how='inner') ``` This sub-skill turns pandas from a prototyping tool into a high-performance engine capable of handling industrial-scale scientific data.