from customer_retention.analysis.notebook_progress import track_and_export_previous

track_and_export_previous("04_column_deep_dive.ipynb")

import numpy as np
import pandas as pd
import plotly.graph_objects as go
from scipy import stats

from customer_retention.analysis.auto_explorer import ExplorationFindings, RecommendationRegistry
from customer_retention.analysis.visualization import ChartBuilder, console, display_figure, display_table
from customer_retention.core.config.column_config import ColumnType
from customer_retention.core.config.experiments import (
    FINDINGS_DIR,
)
from customer_retention.stages.profiling import (
    CategoricalDistributionAnalyzer,
    DistributionAnalyzer,
    TemporalAnalyzer,
    TemporalGranularity,
    TransformationType,
)
from customer_retention.stages.validation import DataValidator, RuleGenerator

from customer_retention.analysis.auto_explorer import load_notebook_findings

FINDINGS_PATH, _namespace, dataset_name = load_notebook_findings("04_column_deep_dive.ipynb")
print(f"Using: {FINDINGS_PATH}")

findings = ExplorationFindings.load(FINDINGS_PATH)
print(f"\nLoaded findings for {findings.column_count} columns from {findings.source_path}")

# Warn if this is event-level data (should run 01d first)
if findings.is_time_series and "_aggregated" not in FINDINGS_PATH:
    ts_meta = findings.time_series_metadata
    print("\n\u26a0\ufe0f  WARNING: This appears to be EVENT-LEVEL data")
    print(f"   Entity: {ts_meta.entity_column}, Time: {ts_meta.time_column}")
    print("   Recommendation: Run 01d_event_aggregation.ipynb first to create entity-level data")

Using: /Users/Vital/python/CustomerRetention/experiments/runs/retail-e7471284/datasets/customer_retention_retail/findings/customer_retention_retail_aggregated_findings.yaml

Loaded findings for 407 columns from /Users/Vital/python/CustomerRetention/experiments/runs/retail-e7471284/data/bronze/customer_retention_retail_aggregated

# Load data - handle aggregated data (parquet or Delta Lake)
from pathlib import Path

from customer_retention.analysis.auto_explorer.active_dataset_store import load_active_dataset
from customer_retention.stages.temporal import TEMPORAL_METADATA_COLS

# For aggregated data, load directly from the source path
if "_aggregated" in FINDINGS_PATH:
    source_path = Path(findings.source_path)
    if not source_path.is_absolute():
        source_path = Path("..") / source_path
    if source_path.is_dir():
        from customer_retention.integrations.adapters.factory import get_delta
        df = get_delta(force_local=True).read(str(source_path))
    elif source_path.is_file():
        df = pd.read_parquet(source_path)
    else:
        df = load_active_dataset(_namespace, dataset_name)
    data_source = f"aggregated:{source_path.name}"
else:
    # Standard loading for event-level or entity-level data
    df = load_active_dataset(_namespace, dataset_name)
    data_source = dataset_name

print(f"Loaded data from: {data_source}")
print(f"Shape: {df.shape}")

charts = ChartBuilder()

# Initialize recommendation registry for this exploration
registry = RecommendationRegistry()
registry.init_bronze(findings.source_path)

# Find target column for Gold layer initialization
target_col = next((name for name, col in findings.columns.items() if col.inferred_type == ColumnType.TARGET), None)
if target_col:
    registry.init_gold(target_col)

# Find entity column for Silver layer initialization
entity_col = next((name for name, col in findings.columns.items() if col.inferred_type == ColumnType.IDENTIFIER), None)
if entity_col:
    registry.init_silver(entity_col)

print(f"Initialized recommendation registry (Bronze: {findings.source_path})")

Loaded data from: aggregated:customer_retention_retail_aggregated
Shape: (30770, 407)
Initialized recommendation registry (Bronze: /Users/Vital/python/CustomerRetention/experiments/runs/retail-e7471284/data/bronze/customer_retention_retail_aggregated)

validator = DataValidator()
range_rules = RuleGenerator.from_findings(findings)

console.start_section()
console.header("Value Range Validation")

if range_rules:
    range_results = validator.validate_value_ranges(df, range_rules)

    issues_found = []
    for r in range_results:
        detail = f"{r.invalid_values} invalid" if r.invalid_values > 0 else None
        console.check(f"{r.column_name} ({r.rule_type})", r.invalid_values == 0, detail)
        if r.invalid_values > 0:
            issues_found.append(r)

    all_invalid = sum(r.invalid_values for r in range_results)
    if all_invalid == 0:
        console.success("All value ranges valid")
    else:
        console.error(f"Found {all_invalid:,} values outside expected ranges")

        console.info("Examples of invalid values:")
        for r in issues_found[:3]:
            col = r.column_name
            if col in df.columns:
                if r.rule_type == 'binary':
                    invalid_mask = ~df[col].isin([0, 1, np.nan])
                    condition = "value not in [0, 1]"
                elif r.rule_type == 'non_negative':
                    invalid_mask = df[col] < 0
                    condition = "value < 0"
                elif r.rule_type == 'percentage':
                    invalid_mask = (df[col] < 0) | (df[col] > 100)
                    condition = "value < 0 or value > 100"
                elif r.rule_type == 'rate':
                    invalid_mask = (df[col] < 0) | (df[col] > 1)
                    condition = "value < 0 or value > 1"
                else:
                    continue

                invalid_values = df.loc[invalid_mask, col].dropna()
                if len(invalid_values) > 0:
                    examples = invalid_values.head(5).tolist()
                    console.metric(f"  {col}", f"{examples}")

                    # Add filtering recommendation
                    registry.add_bronze_filtering(
                        column=col, condition=condition, action="cap",
                        rationale=f"{r.invalid_values} values violate {r.rule_type} constraint",
                        source_notebook="04_column_deep_dive"
                    )

    console.info("Rules auto-generated from detected column types")
else:
    range_results = []
    console.info("No validation rules generated - no binary/numeric columns detected")

console.end_section()

# Use framework's DistributionAnalyzer for comprehensive analysis
analyzer = DistributionAnalyzer()

numeric_cols = [
    name for name, col in findings.columns.items()
    if col.inferred_type in [ColumnType.NUMERIC_CONTINUOUS, ColumnType.NUMERIC_DISCRETE]
    and name not in TEMPORAL_METADATA_COLS
]

# Analyze all numeric columns using the framework
analyses = analyzer.analyze_dataframe(df, numeric_cols)
recommendations = {col: analyzer.recommend_transformation(analysis)
                   for col, analysis in analyses.items()}

for col_name in numeric_cols:
    col_info = findings.columns[col_name]
    analysis = analyses.get(col_name)
    rec = recommendations.get(col_name)

    print(f"\n{'='*70}")
    print(f"Column: {col_name}")
    print(f"Type: {col_info.inferred_type.value} (Confidence: {col_info.confidence:.0%})")
    print("-" * 70)

    if analysis:
        print("\U0001f4ca Distribution Statistics:")
        print(f"   Mean: {analysis.mean:.3f}  |  Median: {analysis.median:.3f}  |  Std: {analysis.std:.3f}")
        print(f"   Range: [{analysis.min_value:.3f}, {analysis.max_value:.3f}]")
        if analysis.percentiles:
            print(f"   Percentiles: 1%={analysis.percentiles.get('p1', 0):.3f}, 25%={analysis.q1:.3f}, 75%={analysis.q3:.3f}, 99%={analysis.percentiles.get('p99', 0):.3f}")
        print("\n\U0001f4c8 Shape Analysis:")
        skew_label = '(Right-skewed)' if analysis.skewness > 0.5 else '(Left-skewed)' if analysis.skewness < -0.5 else '(Symmetric)'
        print(f"   Skewness: {analysis.skewness:.2f} {skew_label}")
        kurt_label = '(Heavy tails/outliers)' if analysis.kurtosis > 3 else '(Light tails)'
        print(f"   Kurtosis: {analysis.kurtosis:.2f} {kurt_label}")
        print(f"   Zeros: {analysis.zero_count:,} ({analysis.zero_percentage:.1f}%)")
        print(f"   Outliers (IQR): {analysis.outlier_count_iqr:,} ({analysis.outlier_percentage:.1f}%)")

        if rec:
            print(f"\n\U0001f527 Recommended Transformation: {rec.recommended_transform.value}")
            print(f"   Reason: {rec.reason}")
            print(f"   Priority: {rec.priority}")
            if rec.warnings:
                for warn in rec.warnings:
                    print(f"   \u26a0\ufe0f {warn}")

    # Create enhanced histogram with Plotly
    data = df[col_name].dropna()
    fig = go.Figure()

    fig.add_trace(go.Histogram(x=data, nbinsx=50, name='Distribution',
                                marker_color='steelblue', opacity=0.7))

    # Calculate mean and median
    mean_val = data.mean()
    median_val = data.median()

    # Position labels on opposite sides (left/right) to avoid overlap
    # The larger value gets right-justified, smaller gets left-justified
    mean_position = "top right" if mean_val >= median_val else "top left"
    median_position = "top left" if mean_val >= median_val else "top right"

    # Add mean line
    fig.add_vline(
        x=mean_val,
        line_dash="dash",
        line_color="red",
        annotation_text=f"Mean: {mean_val:.2f}",
        annotation_position=mean_position,
        annotation_font_color="red",
        annotation_bgcolor="rgba(255,255,255,0.8)"
    )

    # Add median line
    fig.add_vline(
        x=median_val,
        line_dash="solid",
        line_color="green",
        annotation_text=f"Median: {median_val:.2f}",
        annotation_position=median_position,
        annotation_font_color="green",
        annotation_bgcolor="rgba(255,255,255,0.8)"
    )

    # Add 99th percentile marker if there are outliers
    if analysis and analysis.outlier_percentage > 5 and analysis.percentiles.get('p99') is not None:
        fig.add_vline(x=analysis.percentiles['p99'], line_dash="dot", line_color="orange",
                      annotation_text=f"99th: {analysis.percentiles['p99']:.2f}",
                      annotation_position="top right",
                      annotation_font_color="orange",
                      annotation_bgcolor="rgba(255,255,255,0.8)")

    transform_label = rec.recommended_transform.value if rec else "none"
    fig.update_layout(
        title=f"Distribution: {col_name}<br><sub>Skew: {analysis.skewness:.2f} | Kurt: {analysis.kurtosis:.2f} | Strategy: {transform_label}</sub>",
        xaxis_title=col_name,
        yaxis_title="Count",
        template='plotly_white',
        height=400
    )
    display_figure(fig)

======================================================================
Column: event_count_all_time
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 1.000  |  Median: 1.000  |  Std: 0.014
   Range: [0.000, 2.000]
   Percentiles: 1%=1.000, 25%=1.000, 75%=1.000, 99%=1.000

📈 Shape Analysis:
   Skewness: 47.72 (Right-skewed)
   Kurtosis: 5125.28 (Heavy tails/outliers)
   Zeros: 1 (0.0%)
   Outliers (IQR): 6 (0.0%)

🔧 Recommended Transformation: yeo_johnson
   Reason: High skewness (47.72) with non-positive values
   Priority: high

======================================================================
Column: esent_sum_all_time
Type: numeric_continuous (Confidence: 90%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 28.144  |  Median: 32.000  |  Std: 16.754
   Range: [0.000, 291.000]
   Percentiles: 1%=0.000, 25%=16.000, 75%=42.000, 99%=56.000

📈 Shape Analysis:
   Skewness: -0.05 (Symmetric)
   Kurtosis: 3.67 (Heavy tails/outliers)
   Zeros: 3,400 (11.0%)
   Outliers (IQR): 12 (0.0%)

🔧 Recommended Transformation: none
   Reason: Distribution is approximately normal (skewness: -0.05)
   Priority: low

======================================================================
Column: esent_mean_all_time
Type: numeric_continuous (Confidence: 90%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 28.141  |  Median: 32.000  |  Std: 16.750
   Range: [0.000, 291.000]
   Percentiles: 1%=0.000, 25%=16.000, 75%=42.000, 99%=56.000

📈 Shape Analysis:
   Skewness: -0.05 (Symmetric)
   Kurtosis: 3.67 (Heavy tails/outliers)
   Zeros: 3,399 (11.0%)
   Outliers (IQR): 12 (0.0%)

🔧 Recommended Transformation: none
   Reason: Distribution is approximately normal (skewness: -0.05)
   Priority: low

======================================================================
Column: esent_max_all_time
Type: numeric_continuous (Confidence: 90%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 28.142  |  Median: 32.000  |  Std: 16.750
   Range: [0.000, 291.000]
   Percentiles: 1%=0.000, 25%=16.000, 75%=42.000, 99%=56.000

📈 Shape Analysis:
   Skewness: -0.05 (Symmetric)
   Kurtosis: 3.67 (Heavy tails/outliers)
   Zeros: 3,399 (11.0%)
   Outliers (IQR): 12 (0.0%)

🔧 Recommended Transformation: none
   Reason: Distribution is approximately normal (skewness: -0.05)
   Priority: low

======================================================================
Column: esent_count_all_time
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 1.000  |  Median: 1.000  |  Std: 0.014
   Range: [0.000, 2.000]
   Percentiles: 1%=1.000, 25%=1.000, 75%=1.000, 99%=1.000

📈 Shape Analysis:
   Skewness: 47.72 (Right-skewed)
   Kurtosis: 5125.28 (Heavy tails/outliers)
   Zeros: 1 (0.0%)
   Outliers (IQR): 6 (0.0%)

🔧 Recommended Transformation: yeo_johnson
   Reason: High skewness (47.72) with non-positive values
   Priority: high

======================================================================
Column: eopenrate_sum_all_time
Type: numeric_continuous (Confidence: 90%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 25.554  |  Median: 13.208  |  Std: 29.556
   Range: [0.000, 116.667]
   Percentiles: 1%=0.000, 25%=2.083, 75%=40.000, 99%=100.000

📈 Shape Analysis:
   Skewness: 1.17 (Right-skewed)
   Kurtosis: 0.21 (Light tails)
   Zeros: 7,618 (24.8%)
   Outliers (IQR): 1,181 (3.8%)

🔧 Recommended Transformation: sqrt_transform
   Reason: Moderate skewness (1.17)
   Priority: medium

======================================================================
Column: eopenrate_mean_all_time
Type: numeric_continuous (Confidence: 90%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 25.551  |  Median: 13.208  |  Std: 29.551
   Range: [0.000, 100.000]
   Percentiles: 1%=0.000, 25%=2.083, 75%=40.000, 99%=100.000

📈 Shape Analysis:
   Skewness: 1.17 (Right-skewed)
   Kurtosis: 0.21 (Light tails)
   Zeros: 7,617 (24.8%)
   Outliers (IQR): 1,180 (3.8%)

🔧 Recommended Transformation: sqrt_transform
   Reason: Moderate skewness (1.17)
   Priority: medium

======================================================================
Column: eopenrate_max_all_time
Type: numeric_continuous (Confidence: 90%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 25.554  |  Median: 13.208  |  Std: 29.554
   Range: [0.000, 100.000]
   Percentiles: 1%=0.000, 25%=2.083, 75%=40.000, 99%=100.000

📈 Shape Analysis:
   Skewness: 1.17 (Right-skewed)
   Kurtosis: 0.21 (Light tails)
   Zeros: 7,617 (24.8%)
   Outliers (IQR): 1,181 (3.8%)

🔧 Recommended Transformation: sqrt_transform
   Reason: Moderate skewness (1.17)
   Priority: medium

======================================================================
Column: eopenrate_count_all_time
Type: numeric_discrete (Confidence: 70%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 1.000  |  Median: 1.000  |  Std: 0.014
   Range: [0.000, 2.000]
   Percentiles: 1%=1.000, 25%=1.000, 75%=1.000, 99%=1.000

📈 Shape Analysis:
   Skewness: 47.72 (Right-skewed)
   Kurtosis: 5125.28 (Heavy tails/outliers)
   Zeros: 1 (0.0%)
   Outliers (IQR): 6 (0.0%)

🔧 Recommended Transformation: yeo_johnson
   Reason: High skewness (47.72) with non-positive values
   Priority: high

======================================================================
Column: eclickrate_sum_all_time
Type: numeric_continuous (Confidence: 90%)
----------------------------------------------------------------------
📊 Distribution Statistics:
   Mean: 5.671  |  Median: 0.000  |  Std: 10.552
   Range: [0.000, 100.000]
   Percentiles: 1%=0.000, 25%=0.000, 75%=7.143, 99%=50.000

📈 Shape Analysis:
   Skewness: 3.89 (Right-skewed)
   Kurtosis: 22.91 (Heavy tails/outliers)
   Zeros: 15,452 (50.2%)
   Outliers (IQR): 2,787 (9.1%)

🔧 Recommended Transformation: zero_inflation_handling
   Reason: Zero-inflation (50.2%) combined with high skewness (3.89)
   Priority: high
   ⚠️ Consider creating a binary indicator for zeros plus log transform of non-zero values

# Numerical Feature Statistics Table
if numeric_cols:
    stats_data = []
    for col_name in numeric_cols:
        series = df[col_name].dropna()
        if len(series) > 0:
            stats_data.append({
                "feature": col_name,
                "count": len(series),
                "mean": series.mean(),
                "std": series.std(),
                "min": series.min(),
                "25%": series.quantile(0.25),
                "50%": series.quantile(0.50),
                "75%": series.quantile(0.75),
                "95%": series.quantile(0.95),
                "99%": series.quantile(0.99),
                "max": series.max(),
                "skewness": stats.skew(series),
                "kurtosis": stats.kurtosis(series)
            })

    stats_df = pd.DataFrame(stats_data)

    # Format for display
    display_stats = stats_df.copy()
    for col in ["mean", "std", "min", "25%", "50%", "75%", "95%", "99%", "max"]:
        display_stats[col] = display_stats[col].apply(lambda x: f"{x:.3f}")
    display_stats["skewness"] = display_stats["skewness"].apply(lambda x: f"{x:.3f}")
    display_stats["kurtosis"] = display_stats["kurtosis"].apply(lambda x: f"{x:.3f}")

    print("=" * 80)
    print("NUMERICAL FEATURE STATISTICS")
    print("=" * 80)
    display(display_stats)

================================================================================
NUMERICAL FEATURE STATISTICS
================================================================================

# Build transformation summary table
summary_data = []
for col_name in numeric_cols:
    analysis = analyses.get(col_name)
    rec = recommendations.get(col_name)

    if analysis and rec:
        summary_data.append({
            "Column": col_name,
            "Skewness": f"{analysis.skewness:.2f}",
            "Kurtosis": f"{analysis.kurtosis:.2f}",
            "Zeros %": f"{analysis.zero_percentage:.1f}%",
            "Outliers %": f"{analysis.outlier_percentage:.1f}%",
            "Transform": rec.recommended_transform.value,
            "Priority": rec.priority
        })

        # Add Gold transformation recommendation if not "none"
        if rec.recommended_transform != TransformationType.NONE and registry.gold:
            registry.add_gold_transformation(
                column=col_name,
                transform=rec.recommended_transform.value,
                parameters=rec.parameters,
                rationale=rec.reason,
                source_notebook="04_column_deep_dive"
            )

if summary_data:
    summary_df = pd.DataFrame(summary_data)
    display_table(summary_df)

    # Show how many transformation recommendations were added
    transform_count = sum(1 for r in recommendations.values() if r and r.recommended_transform != TransformationType.NONE)
    if transform_count > 0 and registry.gold:
        print(f"\n✅ Added {transform_count} transformation recommendations to Gold layer")
else:
    console.info("No numeric columns to summarize")

✅ Added 173 transformation recommendations to Gold layer

# Use framework's CategoricalDistributionAnalyzer
cat_analyzer = CategoricalDistributionAnalyzer()

categorical_cols = [
    name for name, col in findings.columns.items()
    if col.inferred_type in [ColumnType.CATEGORICAL_NOMINAL, ColumnType.CATEGORICAL_ORDINAL, ColumnType.CATEGORICAL_CYCLICAL]
    and col.inferred_type != ColumnType.TEXT  # TEXT columns processed separately in 02a
    and name not in TEMPORAL_METADATA_COLS
]

# Analyze all categorical columns
cat_analyses = cat_analyzer.analyze_dataframe(df, categorical_cols)

# Get encoding recommendations
cyclical_cols = [name for name, col in findings.columns.items()
                 if col.inferred_type == ColumnType.CATEGORICAL_CYCLICAL]
cat_recommendations = cat_analyzer.get_all_recommendations(df, categorical_cols, cyclical_columns=cyclical_cols)

for col_name in categorical_cols:
    col_info = findings.columns[col_name]
    analysis = cat_analyses.get(col_name)
    rec = next((r for r in cat_recommendations if r.column_name == col_name), None)

    print(f"\n{'='*70}")
    print(f"Column: {col_name}")
    print(f"Type: {col_info.inferred_type.value} (Confidence: {col_info.confidence:.0%})")
    print("-" * 70)

    if analysis:
        print("\n📊 Distribution Metrics:")
        print(f"   Categories: {analysis.category_count}")
        print(f"   Imbalance Ratio: {analysis.imbalance_ratio:.1f}x (largest/smallest)")
        print(f"   Entropy: {analysis.entropy:.2f} ({analysis.normalized_entropy*100:.0f}% of max)")
        print(f"   Top-1 Concentration: {analysis.top1_concentration:.1f}%")
        print(f"   Top-3 Concentration: {analysis.top3_concentration:.1f}%")
        print(f"   Rare Categories (<1%): {analysis.rare_category_count}")

        # Interpretation
        print("\n📈 Interpretation:")
        if analysis.has_low_diversity:
            print("   ⚠️ LOW DIVERSITY: Distribution dominated by few categories")
        elif analysis.normalized_entropy > 0.9:
            print("   ✓ HIGH DIVERSITY: Categories are relatively balanced")
        else:
            print("   ✓ MODERATE DIVERSITY: Some category dominance but acceptable")

        if analysis.imbalance_ratio > 100:
            print("   🔴 SEVERE IMBALANCE: Rarest category has very few samples")
        elif analysis.is_imbalanced:
            print("   🟡 MODERATE IMBALANCE: Consider grouping rare categories")

        # Recommendations
        if rec:
            print("\n🔧 Recommendations:")
            print(f"   Encoding: {rec.encoding_type.value}")
            print(f"   Reason: {rec.reason}")
            print(f"   Priority: {rec.priority}")

            if rec.preprocessing_steps:
                print("   Preprocessing:")
                for step in rec.preprocessing_steps:
                    print(f"      • {step}")

            if rec.warnings:
                for warn in rec.warnings:
                    print(f"   ⚠️ {warn}")

    # Visualization
    value_counts = df[col_name].value_counts()
    subtitle = f"Entropy: {analysis.normalized_entropy*100:.0f}% | Imbalance: {analysis.imbalance_ratio:.1f}x | Rare: {analysis.rare_category_count}" if analysis else ""
    fig = charts.bar_chart(
        value_counts.head(10).index.tolist(),
        value_counts.head(10).values.tolist(),
        title=f"Top Categories: {col_name}<br><sub>{subtitle}</sub>"
    )
    display_figure(fig)

# Summary table and add recommendations to registry
if cat_analyses:
    print("\n" + "=" * 70)
    print("CATEGORICAL COLUMNS SUMMARY")
    print("=" * 70)
    summary_data = []
    for col_name, analysis in cat_analyses.items():
        rec = next((r for r in cat_recommendations if r.column_name == col_name), None)
        summary_data.append({
            "Column": col_name,
            "Categories": analysis.category_count,
            "Imbalance": f"{analysis.imbalance_ratio:.1f}x",
            "Entropy": f"{analysis.normalized_entropy*100:.0f}%",
            "Top-3 Conc.": f"{analysis.top3_concentration:.1f}%",
            "Rare (<1%)": analysis.rare_category_count,
            "Encoding": rec.encoding_type.value if rec else "N/A"
        })

        # Add encoding recommendation to Gold layer
        if rec and registry.gold:
            registry.add_gold_encoding(
                column=col_name,
                method=rec.encoding_type.value,
                rationale=rec.reason,
                source_notebook="04_column_deep_dive"
            )

    display_table(pd.DataFrame(summary_data))

    if registry.gold:
        print(f"\n✅ Added {len(cat_recommendations)} encoding recommendations to Gold layer")

======================================================================
Column: cohort_quarter
Type: categorical_nominal (Confidence: 70%)
----------------------------------------------------------------------

📊 Distribution Metrics:
   Categories: 41
   Imbalance Ratio: 7221.0x (largest/smallest)
   Entropy: 4.12 (77% of max)
   Top-1 Concentration: 23.5%
   Top-3 Concentration: 46.0%
   Rare Categories (<1%): 22

📈 Interpretation:
   ✓ MODERATE DIVERSITY: Some category dominance but acceptable
   🔴 SEVERE IMBALANCE: Rarest category has very few samples

🔧 Recommendations:
   Encoding: target
   Reason: High cardinality (41 categories) - target or frequency encoding
   Priority: high
   Preprocessing:
      • Group 22 rare categories into 'Other'
   ⚠️ Use stratified sampling to preserve rare category representation
   ⚠️ High cardinality may require regularization with target encoding

======================================================================
CATEGORICAL COLUMNS SUMMARY
======================================================================

✅ Added 1 encoding recommendations to Gold layer

from customer_retention.stages.profiling.temporal_analyzer import TemporalRecommendationType

datetime_cols = [
    name for name, col in findings.columns.items()
    if col.inferred_type == ColumnType.DATETIME
    and name not in TEMPORAL_METADATA_COLS
]

temporal_analyzer = TemporalAnalyzer()

# Store all datetime recommendations grouped by type
feature_engineering_recs = []
modeling_strategy_recs = []
data_quality_recs = []
datetime_summaries = []

for col_name in datetime_cols:
    col_info = findings.columns[col_name]
    print(f"\n{'='*70}")
    print(f"Column: {col_name}")
    print(f"Type: {col_info.inferred_type.value} (Confidence: {col_info.confidence:.0%})")
    print(f"{'='*70}")

    date_series = pd.to_datetime(df[col_name], errors='coerce', format='mixed')
    valid_dates = date_series.dropna()

    print(f"\n📅 Date Range: {valid_dates.min()} to {valid_dates.max()}")
    print(f"   Nulls: {date_series.isna().sum():,} ({date_series.isna().mean()*100:.1f}%)")

    # Basic temporal analysis
    analysis = temporal_analyzer.analyze(date_series)
    print(f"   Auto-detected granularity: {analysis.granularity.value}")
    print(f"   Span: {analysis.span_days:,} days ({analysis.span_days/365:.1f} years)")

    # Growth analysis
    growth = temporal_analyzer.calculate_growth_rate(date_series)
    if growth.get("has_data"):
        print("\n📈 Growth Analysis:")
        print(f"   Trend: {growth['trend_direction'].upper()}")
        print(f"   Overall growth: {growth['overall_growth_pct']:+.1f}%")
        print(f"   Avg monthly growth: {growth['avg_monthly_growth']:+.1f}%")

    # Seasonality analysis
    seasonality = temporal_analyzer.analyze_seasonality(date_series)
    if seasonality.has_seasonality:
        print("\n🔄 Seasonality Detected:")
        print(f"   Peak months: {', '.join(seasonality.peak_periods[:3])}")
        print(f"   Trough months: {', '.join(seasonality.trough_periods[:3])}")
        print(f"   Seasonal strength: {seasonality.seasonal_strength:.2f}")

    # Get recommendations using framework
    other_dates = [c for c in datetime_cols if c != col_name]
    recommendations = temporal_analyzer.recommend_features(date_series, col_name, other_date_columns=other_dates)

    # Group by recommendation type
    col_feature_recs = [r for r in recommendations if r.recommendation_type == TemporalRecommendationType.FEATURE_ENGINEERING]
    col_modeling_recs = [r for r in recommendations if r.recommendation_type == TemporalRecommendationType.MODELING_STRATEGY]
    col_quality_recs = [r for r in recommendations if r.recommendation_type == TemporalRecommendationType.DATA_QUALITY]

    feature_engineering_recs.extend(col_feature_recs)
    modeling_strategy_recs.extend(col_modeling_recs)
    data_quality_recs.extend(col_quality_recs)

    # Display recommendations grouped by type
    if col_feature_recs:
        print("\n🛠️ FEATURES TO CREATE:")
        for rec in col_feature_recs:
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {priority_icon} {rec.feature_name} ({rec.category})")
            print(f"      Why: {rec.reason}")
            if rec.code_hint:
                print(f"      Code: {rec.code_hint}")

    if col_modeling_recs:
        print("\n⚙️ MODELING CONSIDERATIONS:")
        for rec in col_modeling_recs:
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {priority_icon} {rec.feature_name}")
            print(f"      Why: {rec.reason}")

    if col_quality_recs:
        print("\n⚠️ DATA QUALITY ISSUES:")
        for rec in col_quality_recs:
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {priority_icon} {rec.feature_name}")
            print(f"      Why: {rec.reason}")
            if rec.code_hint:
                print(f"      Code: {rec.code_hint}")

    # Standard extractions always available
    print("\n   Standard extractions available: year, month, day, day_of_week, quarter")

    # Store summary
    datetime_summaries.append({
        "Column": col_name,
        "Span (days)": analysis.span_days,
        "Seasonality": "Yes" if seasonality.has_seasonality else "No",
        "Trend": growth.get('trend_direction', 'N/A').capitalize() if growth.get("has_data") else "N/A",
        "Features to Create": len(col_feature_recs),
        "Modeling Notes": len(col_modeling_recs),
        "Quality Issues": len(col_quality_recs)
    })

    # === VISUALIZATIONS ===

    if growth.get("has_data"):
        fig = charts.growth_summary_indicators(growth, title=f"Growth Summary: {col_name}")
        display_figure(fig)

    chart_type = "line" if analysis.granularity in [TemporalGranularity.DAY, TemporalGranularity.WEEK] else "bar"
    fig = charts.temporal_distribution(analysis, title=f"Records Over Time: {col_name}", chart_type=chart_type)
    display_figure(fig)

    fig = charts.temporal_trend(analysis, title=f"Trend Analysis: {col_name}")
    display_figure(fig)

    yoy_data = temporal_analyzer.year_over_year_comparison(date_series)
    if len(yoy_data) > 1:
        fig = charts.year_over_year_lines(yoy_data, title=f"Year-over-Year: {col_name}")
        display_figure(fig)
        fig = charts.year_month_heatmap(yoy_data, title=f"Records Heatmap: {col_name}")
        display_figure(fig)

    if growth.get("has_data"):
        fig = charts.cumulative_growth_chart(growth["cumulative"], title=f"Cumulative Records: {col_name}")
        display_figure(fig)

    fig = charts.temporal_heatmap(date_series, title=f"Day of Week Distribution: {col_name}")
    display_figure(fig)

# === DATETIME SUMMARY ===
if datetime_summaries:
    print("\n" + "=" * 70)
    print("DATETIME COLUMNS SUMMARY")
    print("=" * 70)
    display_table(pd.DataFrame(datetime_summaries))

    # Summary by recommendation type
    print("\n📋 ALL RECOMMENDATIONS BY TYPE:")

    if feature_engineering_recs:
        print(f"\n🛠️ FEATURES TO CREATE ({len(feature_engineering_recs)}):")
        for i, rec in enumerate(feature_engineering_recs, 1):
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {i}. {priority_icon} {rec.feature_name}")

    if modeling_strategy_recs:
        print(f"\n⚙️ MODELING CONSIDERATIONS ({len(modeling_strategy_recs)}):")
        for i, rec in enumerate(modeling_strategy_recs, 1):
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {i}. {priority_icon} {rec.feature_name}: {rec.reason}")

    if data_quality_recs:
        print(f"\n⚠️ DATA QUALITY TO ADDRESS ({len(data_quality_recs)}):")
        for i, rec in enumerate(data_quality_recs, 1):
            priority_icon = "🔴" if rec.priority == "high" else "🟡" if rec.priority == "medium" else "✓"
            print(f"   {i}. {priority_icon} {rec.feature_name}: {rec.reason}")

    # Add recommendations to registry
    added_derived = 0
    added_modeling = 0

    # Add feature engineering recommendations to Silver layer (derived columns)
    if registry.silver:
        for rec in feature_engineering_recs:
            registry.add_silver_derived(
                column=rec.feature_name,
                expression=rec.code_hint or "",
                feature_type=rec.category,
                rationale=rec.reason,
                source_notebook="04_column_deep_dive"
            )
            added_derived += 1

    # Add modeling strategy recommendations to Bronze layer
    seen_strategies = set()
    for rec in modeling_strategy_recs:
        if rec.feature_name not in seen_strategies:
            registry.add_bronze_modeling_strategy(
                strategy=rec.feature_name,
                column=datetime_cols[0] if datetime_cols else "",
                parameters={"category": rec.category},
                rationale=rec.reason,
                source_notebook="04_column_deep_dive"
            )
            seen_strategies.add(rec.feature_name)
            added_modeling += 1

    print(f"\n✅ Added {added_derived} derived column recommendations to Silver layer")
    print(f"✅ Added {added_modeling} modeling strategy recommendations to Bronze layer")

# === TYPE OVERRIDES ===
# Uncomment and modify to override any incorrectly inferred types
TYPE_OVERRIDES = {
    # "column_name": ColumnType.NEW_TYPE,
    # Examples:
    # "is_active": ColumnType.BINARY,
    # "user_id": ColumnType.IDENTIFIER,
    # "satisfaction_level": ColumnType.CATEGORICAL_ORDINAL,
}

if TYPE_OVERRIDES:
    print("Applying type overrides:")
    for col_name, new_type in TYPE_OVERRIDES.items():
        if col_name in findings.columns:
            old_type = findings.columns[col_name].inferred_type.value
            findings.columns[col_name].inferred_type = new_type
            findings.columns[col_name].confidence = 1.0
            findings.columns[col_name].evidence.append("Manually overridden")
            print(f"  {col_name}: {old_type} → {new_type.value}")
else:
    print("No type overrides configured.")
    print("To override a type, add entries to TYPE_OVERRIDES dictionary above.")

No type overrides configured.
To override a type, add entries to TYPE_OVERRIDES dictionary above.

from customer_retention.stages.profiling import SegmentAnalyzer

# Initialize segment analyzer
segment_analyzer = SegmentAnalyzer()

# Find target column if detected
target_col = None
for col_name, col_info in findings.columns.items():
    if col_info.inferred_type == ColumnType.TARGET:
        target_col = col_name
        break

# Run segmentation analysis using numeric features
print("="*70)
print("DATA SEGMENTATION ANALYSIS")
print("="*70)

segmentation = segment_analyzer.analyze(
    df,
    target_col=target_col,
    feature_cols=numeric_cols if numeric_cols else None,
    max_segments=5
)

print("\n🎯 Analysis Results:")
print(f"   Method: {segmentation.method.value}")
print(f"   Detected Segments: {segmentation.n_segments}")
print(f"   Cluster Quality Score: {segmentation.quality_score:.2f}")
if segmentation.target_variance_ratio is not None:
    print(f"   Target Variance Ratio: {segmentation.target_variance_ratio:.2f}")

print("\n📊 Segment Profiles:")
for profile in segmentation.profiles:
    target_info = f" | Target Rate: {profile.target_rate*100:.1f}%" if profile.target_rate is not None else ""
    print(f"   Segment {profile.segment_id}: {profile.size:,} records ({profile.size_pct:.1f}%){target_info}")

# Display recommendation card
fig = charts.segment_recommendation_card(segmentation)
display_figure(fig)

# Display segment overview
fig = charts.segment_overview(segmentation, title="Segment Overview")
display_figure(fig)

# Display feature comparison if we have features
if segmentation.n_segments > 1 and any(p.defining_features for p in segmentation.profiles):
    fig = charts.segment_feature_comparison(segmentation, title="Feature Comparison Across Segments")
    display_figure(fig)

print("\n📝 Rationale:")
for reason in segmentation.rationale:
    print(f"   • {reason}")

======================================================================
DATA SEGMENTATION ANALYSIS
======================================================================

🎯 Analysis Results:
   Method: kmeans
   Detected Segments: 1
   Cluster Quality Score: 0.00
   Target Variance Ratio: 0.00

📊 Segment Profiles:
   Segment 0: 30,770 records (100.0%) | Target Rate: 79.5%

📝 Rationale:
   • Insufficient data for meaningful segmentation

# Save updated findings back to the same file
findings.save(FINDINGS_PATH)
print(f"Updated findings saved to: {FINDINGS_PATH}")

# Save recommendations registry
recommendations_path = FINDINGS_PATH.replace("_findings.yaml", "_recommendations.yaml")
registry.save(recommendations_path)
print(f"Recommendations saved to: {recommendations_path}")

# Summary of recommendations
all_recs = registry.all_recommendations
print("\n📋 Recommendations Summary:")
print(f"   Bronze layer: {len(registry.get_by_layer('bronze'))} recommendations")
print(f"   Silver layer: {len(registry.get_by_layer('silver'))} recommendations")
print(f"   Gold layer: {len(registry.get_by_layer('gold'))} recommendations")
print(f"   Total: {len(all_recs)} recommendations")

Updated findings saved to: /Users/Vital/python/CustomerRetention/experiments/runs/retail-e7471284/datasets/customer_retention_retail/findings/customer_retention_retail_aggregated_findings.yaml
Recommendations saved to: /Users/Vital/python/CustomerRetention/experiments/runs/retail-e7471284/datasets/customer_retention_retail/findings/customer_retention_retail_aggregated_recommendations.yaml

📋 Recommendations Summary:
   Bronze layer: 3 recommendations
   Silver layer: 0 recommendations
   Gold layer: 174 recommendations
   Total: 177 recommendations

Metric	Interpretation	Action
Skewness	Measures asymmetry	\|skew\| > 1: Consider log transform
Kurtosis	Measures tail heaviness	kurt > 10: Cap outliers before transform
Zero %	Percentage of zeros	> 40%: Use zero-inflation handling

Metric	Interpretation	Action
Imbalance Ratio	Largest / Smallest category count	> 10: Consider grouping rare categories
Entropy	Diversity measure (0 = one category, higher = more uniform)	Low entropy: May need stratified sampling
Top-3 Concentration	% of data in top 3 categories	> 90%: Rare categories may cause issues
Rare Category %	Categories with < 1% of data	High %: Group into "Other" category

Recommendation Type	Purpose	Examples
Feature Engineering	Create predictive features from dates	`days_since_signup`, `tenure_years`, `month_sin_cos`
Modeling Strategy	How to structure train/test	Time-based splits when trends detected
Data Quality	Issues to address before modeling	Placeholder dates (1/1/1900) to filter

Metric	Threshold	Action
Skewness	\|skew\| > 1	Log transform
Kurtosis	> 10	Cap outliers first
Zero %	> 40%	Zero-inflation handling

Metric	Threshold	Action
Imbalance Ratio	> 10x	Group rare categories
Entropy	< 50%	Stratified sampling
Rare Categories	> 0	Group into "Other"

Chapter 4: Column Deep Dive¶

4.1 Load Previous Findings¶

4.2 Load Source Data¶

4.3 Value Range Validation¶

VALUE RANGE VALIDATION¶

4.4 Numeric Columns Analysis¶

4.5 Distribution Summary & Transformation Plan¶

4.6 Categorical Columns Analysis¶

4.7 Datetime Columns Analysis¶

4.8 Type Override (Optional)¶

4.9 Data Segmentation Analysis¶

4.10 Save Updated Findings¶

Summary: What We Learned¶

Key Metrics Reference¶

Transformation & Encoding Summary¶

Next Steps¶

	feature	count	mean	std	min	25%	50%	75%	95%	99%	max	skewness	kurtosis
0	event_count_all_time	30770	1.000	0.014	0.000	1.000	1.000	1.000	1.000	1.000	2.000	47.720	5124.445
1	esent_sum_all_time	30770	28.144	16.754	0.000	16.000	32.000	42.000	48.000	56.000	291.000	-0.052	3.669
2	esent_mean_all_time	30769	28.141	16.750	0.000	16.000	32.000	42.000	48.000	56.000	291.000	-0.053	3.672
3	esent_max_all_time	30769	28.142	16.750	0.000	16.000	32.000	42.000	48.000	56.000	291.000	-0.053	3.672
4	esent_count_all_time	30770	1.000	0.014	0.000	1.000	1.000	1.000	1.000	1.000	2.000	47.720	5124.445
...	...	...	...	...	...	...	...	...	...	...	...	...	...
206	ordfreq_cohort_zscore	30769	-0.000	1.000	-0.363	-0.363	-0.363	0.029	1.295	2.843	30.893	10.465	179.508
207	created_delta_hours_vs_cohort_mean	30769	0.000	6446.912	-44789.481	618.519	2778.519	3162.519	3162.519	3162.519	3162.519	-2.877	8.797
208	created_delta_hours_vs_cohort_pct	30769	1.000	2.039	-0.000	0.000	0.121	0.804	5.962	9.395	15.163	2.877	8.797
209	created_delta_hours_cohort_zscore	30769	0.000	1.000	-6.947	0.096	0.431	0.491	0.491	0.491	0.491	-2.877	8.797
210	created_hour_vs_cohort_mean	30769	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	0.000	nan	nan

Column	Skewness	Kurtosis	Zeros %	Outliers %	Transform	Priority
event_count_all_time	47.72	5125.28	0.0%	0.0%	yeo_johnson	high
esent_sum_all_time	-0.05	3.67	11.0%	0.0%	none	low
esent_mean_all_time	-0.05	3.67	11.0%	0.0%	none	low
esent_max_all_time	-0.05	3.67	11.0%	0.0%	none	low
esent_count_all_time	47.72	5125.28	0.0%	0.0%	yeo_johnson	high
eopenrate_sum_all_time	1.17	0.21	24.8%	3.8%	sqrt_transform	medium
eopenrate_mean_all_time	1.17	0.21	24.8%	3.8%	sqrt_transform	medium
eopenrate_max_all_time	1.17	0.21	24.8%	3.8%	sqrt_transform	medium
eopenrate_count_all_time	47.72	5125.28	0.0%	0.0%	yeo_johnson	high
eclickrate_sum_all_time	3.89	22.91	50.2%	9.1%	zero_inflation_handling	high
eclickrate_mean_all_time	3.89	22.91	50.2%	9.1%	zero_inflation_handling	high
eclickrate_max_all_time	3.89	22.91	50.2%	9.1%	zero_inflation_handling	high
eclickrate_count_all_time	47.72	5125.28	0.0%	0.0%	yeo_johnson	high
avgorder_sum_all_time	11.70	548.62	0.0%	5.7%	cap_then_log	high
avgorder_mean_all_time	11.71	548.84	0.0%	5.7%	cap_then_log	high
avgorder_max_all_time	11.71	548.84	0.0%	5.7%	cap_then_log	high
avgorder_count_all_time	47.72	5125.28	0.0%	0.0%	yeo_johnson	high
ordfreq_sum_all_time	10.47	179.54	61.7%	12.1%	zero_inflation_handling	high
ordfreq_mean_all_time	10.47	179.54	61.7%	12.1%	zero_inflation_handling	high
ordfreq_max_all_time	10.47	179.54	61.7%	12.1%	zero_inflation_handling	high
ordfreq_count_all_time	47.72	5125.28	0.0%	0.0%	yeo_johnson	high
paperless_sum_all_time	-0.62	-1.61	35.1%	0.0%	zero_inflation_handling	medium
paperless_count_all_time	47.72	5125.28	0.0%	0.0%	yeo_johnson	high
refill_count_all_time	47.72	5125.28	0.0%	0.0%	yeo_johnson	high
doorstep_count_all_time	47.72	5125.28	0.0%	0.0%	yeo_johnson	high
created_delta_hours_sum_all_time	-2.88	8.80	33.3%	15.1%	zero_inflation_handling	high
created_delta_hours_mean_all_time	-2.86	8.66	32.5%	15.1%	zero_inflation_handling	high
created_delta_hours_max_all_time	-2.86	8.66	32.5%	15.1%	zero_inflation_handling	high
created_delta_hours_count_all_time	-9.12	82.22	1.1%	1.1%	yeo_johnson	high
created_hour_sum_all_time	0.00	0.00	100.0%	0.0%	zero_inflation_handling	medium
created_hour_mean_all_time	0.00	0.00	100.0%	0.0%	zero_inflation_handling	medium
created_hour_max_all_time	0.00	0.00	100.0%	0.0%	zero_inflation_handling	medium
created_hour_count_all_time	-9.41	87.73	1.1%	1.1%	yeo_johnson	high
created_dow_sum_all_time	0.17	-1.21	16.5%	0.0%	none	low
created_dow_mean_all_time	0.16	-1.20	15.6%	0.0%	none	low
created_dow_max_all_time	0.16	-1.20	15.6%	0.0%	none	low
created_dow_count_all_time	-9.41	87.73	1.1%	1.1%	yeo_johnson	high
created_is_weekend_count_all_time	-9.41	87.73	1.1%	1.1%	yeo_johnson	high
firstorder_delta_hours_sum_all_time	-3.62	288.44	44.1%	15.1%	zero_inflation_handling	high
firstorder_delta_hours_mean_all_time	-3.62	288.59	44.1%	15.1%	zero_inflation_handling	high
firstorder_delta_hours_max_all_time	-3.62	288.59	44.1%	15.1%	zero_inflation_handling	high
firstorder_delta_hours_count_all_time	-18.35	1706.33	0.0%	0.1%	yeo_johnson	high
firstorder_hour_sum_all_time	0.00	0.00	100.0%	0.0%	zero_inflation_handling	medium
firstorder_hour_mean_all_time	0.00	0.00	100.0%	0.0%	zero_inflation_handling	medium
firstorder_hour_max_all_time	0.00	0.00	100.0%	0.0%	zero_inflation_handling	medium
firstorder_hour_count_all_time	-18.35	1706.33	0.0%	0.1%	yeo_johnson	high
firstorder_dow_sum_all_time	0.26	-1.17	18.1%	0.0%	none	low
firstorder_dow_mean_all_time	0.26	-1.16	18.0%	0.0%	none	low
firstorder_dow_max_all_time	0.26	-1.17	18.0%	0.0%	none	low
firstorder_dow_count_all_time	-18.35	1706.33	0.0%	0.1%	yeo_johnson	high

Finding	Action
Seasonality	Add cyclical month encoding
Strong trend	Time-based train/test split
Multiple dates	Calculate duration features
Placeholder dates	Filter or flag