Fix Pandas Memory Issues with 5GB+ Datasets in 20 Minutes

import pandas as pd
import numpy as np

# Personal note: Learned this after crashing my system 4 times
def analyze_csv_memory(filepath, sample_rows=10000):
    """Sample CSV and recommend optimal dtypes"""
    
    # Read small sample first
    sample = pd.read_csv(filepath, nrows=sample_rows)
    
    print(f"Sample memory usage: {sample.memory_usage(deep=True).sum() / 1024**2:.2f} MB")
    
    # Analyze each column
    dtype_recommendations = {}
    
    for col in sample.columns:
        col_type = sample[col].dtype
        
        if col_type == 'object':
            # Check if it should be categorical
            unique_ratio = len(sample[col].unique()) / len(sample[col])
            if unique_ratio < 0.5:
                dtype_recommendations[col] = 'category'
                print(f"{col}: object → category (saves ~70%)")
            else:
                dtype_recommendations[col] = 'object'
                
        elif col_type == 'int64':
            # Downcast integers
            col_min, col_max = sample[col].min(), sample[col].max()
            if col_min >= 0 and col_max <= 255:
                dtype_recommendations[col] = 'uint8'
            elif col_min >= -128 and col_max <= 127:
                dtype_recommendations[col] = 'int8'
            elif col_min >= -32768 and col_max <= 32767:
                dtype_recommendations[col] = 'int16'
            else:
                dtype_recommendations[col] = 'int32'
            print(f"{col}: int64 → {dtype_recommendations[col]}")
            
        elif col_type == 'float64':
            # Check if float32 has enough precision
            dtype_recommendations[col] = 'float32'
            print(f"{col}: float64 → float32 (saves 50%)")
    
    return dtype_recommendations

# Watch out: Large sample_rows can still crash on huge files
dtypes = analyze_csv_memory('gold_trades.csv', sample_rows=10000)

def load_optimized_csv(filepath, dtypes, chunksize=500000):
    """Load massive CSV without crashing"""
    
    chunks = []
    chunk_count = 0
    
    # Personal note: 500k rows is sweet spot for 16GB RAM
    for chunk in pd.read_csv(
        filepath,
        dtype=dtypes,
        chunksize=chunksize,
        parse_dates=['timestamp'],  # Parse dates during load
        low_memory=False
    ):
        chunk_count += 1
        
        # Process each chunk if needed
        # Example: Filter outliers before concatenating
        chunk = chunk[chunk['price'] > 0]
        
        chunks.append(chunk)
        
        # Memory check
        current_mem = sum(c.memory_usage(deep=True).sum() for c in chunks) / 1024**3
        print(f"Chunk {chunk_count}: {current_mem:.2f} GB in memory")
        
        # Watch out: Don't let chunks accumulate beyond RAM
        if current_mem > 12:  # Leave 4GB for system
            print("⚠️  Approaching memory limit - consider processing chunks separately")
            break
    
    print(f"\nCombining {len(chunks)} chunks...")
    df = pd.concat(chunks, ignore_index=True)
    
    return df

# Load the beast
df = load_optimized_csv('gold_trades.csv', dtypes, chunksize=500000)

print(f"\n✓ Loaded {len(df):,} rows")
print(f"Memory usage: {df.memory_usage(deep=True).sum() / 1024**3:.2f} GB")

def optimize_categoricals(df, threshold=0.5):
    """Convert columns to categorical if unique ratio is low"""
    
    optimized_cols = []
    
    for col in df.select_dtypes(include=['object']).columns:
        unique_ratio = df[col].nunique() / len(df)
        
        if unique_ratio < threshold:
            original_mem = df[col].memory_usage(deep=True) / 1024**2
            df[col] = df[col].astype('category')
            new_mem = df[col].memory_usage(deep=True) / 1024**2
            
            savings = ((original_mem - new_mem) / original_mem) * 100
            optimized_cols.append(col)
            
            print(f"{col}: {original_mem:.1f}MB → {new_mem:.1f}MB (saved {savings:.0f}%)")
    
    return df, optimized_cols

# Apply categorical optimization
df, optimized = optimize_categoricals(df, threshold=0.5)

print(f"\n✓ Optimized {len(optimized)} columns")
print(f"Final memory: {df.memory_usage(deep=True).sum() / 1024**3:.2f} GB")

def validate_and_save(df, original_filepath, output_format='parquet'):
    """Verify optimization didn't corrupt data"""
    
    # Quick validation checks
    print("Running validation...")
    
    # Check for unexpected NaN introduction
    nan_counts = df.isna().sum()
    if nan_counts.any():
        print("⚠️  NaN values found:")
        print(nan_counts[nan_counts > 0])
    
    # Check numeric ranges
    for col in df.select_dtypes(include=[np.number]).columns:
        if df[col].min() < 0 and 'uint' in str(df[col].dtype):
            print(f"❌ Error: {col} has negative values but dtype is unsigned")
            return False
    
    print("✓ Validation passed")
    
    # Save in compressed format
    if output_format == 'parquet':
        output_file = original_filepath.replace('.csv', '_optimized.parquet')
        df.to_parquet(output_file, compression='snappy', index=False)
        
        # Compare file sizes
        import os
        original_size = os.path.getsize(original_filepath) / 1024**3
        new_size = os.path.getsize(output_file) / 1024**3
        
        print(f"\n📁 File size: {original_size:.2f}GB → {new_size:.2f}GB")
        print(f"💾 Disk space saved: {original_size - new_size:.2f}GB")
        print(f"📂 Saved to: {output_file}")
    
    return True

# Validate and save
validate_and_save(df, 'gold_trades.csv', output_format='parquet')