How to Transform my Excel Data into Business Solutions with AI?

import pandas as pd
import numpy as np
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
from sklearn.decomposition import PCA
import matplotlib.pyplot as plt
import seaborn as sns

class AICustomerSegmenter:
    def __init__(self):
        self.clustering_model = None
        self.scaler = None
        self.important_features = None
        self.segment_descriptions = {}
        
    def prepare_customer_data(self, customer_df):
        """
        Prepares customer data for automated segmentation
        """
        # Create behavioral features
        features = customer_df.copy()
        
        # Customer value metrics
        features['total_value'] = features['total_purchases']
        features['purchase_frequency'] = features['num_orders']
        features['average_ticket'] = features['total_value'] / features['purchase_frequency']
        
        # Temporal metrics
        features['days_since_first_purchase'] = (
            pd.to_datetime('today') - pd.to_datetime(features['first_purchase_date'])
        ).dt.days
        
        features['days_since_last_purchase'] = (
            pd.to_datetime('today') - pd.to_datetime(features['last_purchase_date'])
        ).dt.days
        
        # Loyalty and engagement metrics
        features['customer_age'] = features['days_since_first_purchase'] / 365
        features['recency'] = 1 / (features['days_since_last_purchase'] + 1)
        features['purchase_intensity'] = features['purchase_frequency'] / features['customer_age']
        
        # Product diversity metrics
        features['product_diversity'] = features['unique_products_purchased']
        features['brand_loyalty'] = features['main_brand_purchases'] / features['purchase_frequency']
        
        # Seasonal metrics (if available)
        if 'q1_sales' in features.columns:
            features['seasonality'] = np.std([
                features['q1_sales'], features['q2_sales'],
                features['q3_sales'], features['q4_sales']
            ], axis=0)
        
        # Select numerical features for clustering
        numerical_features = [
            'total_value', 'purchase_frequency', 'average_ticket',
            'recency', 'purchase_intensity', 'product_diversity',
            'brand_loyalty', 'customer_age'
        ]
        
        # Filter available features
        available_features = [col for col in numerical_features if col in features.columns]
        
        X = features[available_features].fillna(0)
        
        return X, available_features
    
    def find_optimal_segments(self, X, max_segments=10):
        """
        Uses elbow method to find optimal number of segments
        """
        inertias = []
        K_range = range(2, max_segments + 1)
        
        for k in K_range:
            kmeans = KMeans(n_clusters=k, random_state=42, n_init=10)
            kmeans.fit(X)
            inertias.append(kmeans.inertia_)
        
        # Find "elbow" using second derivative differences
        differences = np.diff(inertias)
        second_differences = np.diff(differences)
        optimal_k = np.argmax(second_differences) + 3  # +3 because we start at k=2
        
        return optimal_k, inertias
    
    def segment_customers(self, customer_df, num_segments=None):
        """
        Performs automatic customer segmentation
        """
        # Prepare data
        X, features = self.prepare_customer_data(customer_df)
        
        # Normalize data
        self.scaler = StandardScaler()
        X_normalized = self.scaler.fit_transform(X)
        
        # Find optimal number of segments if not specified
        if num_segments is None:
            num_segments, _ = self.find_optimal_segments(X_normalized)
        
        # Perform clustering
        self.clustering_model = KMeans(n_clusters=num_segments, random_state=42, n_init=10)
        segments = self.clustering_model.fit_predict(X_normalized)
        
        # Add segments to original dataframe
        result = customer_df.copy()
        result['segment'] = segments
        
        # Analyze characteristics of each segment
        self.important_features = features
        self._analyze_segments(X, segments, features)
        
        return result
    
    def _analyze_segments(self, X, segments, features):
        """
        Analyzes and describes characteristics of each segment
        """
        analysis_df = X.copy()
        analysis_df['segment'] = segments
        
        for segment in np.unique(segments):
            segment_data = analysis_df[analysis_df['segment'] == segment]
            
            # Calculate segment statistics
            statistics = {
                'size': len(segment_data),
                'percentage': len(segment_data) / len(analysis_df) * 100
            }
            
            # Compare with global average
            for feature in features:
                if feature in segment_data.columns:
                    segment_value = segment_data[feature].mean()
                    global_value = analysis_df[feature].mean()
                    difference_pct = ((segment_value - global_value) / global_value) * 100
                    
                    statistics[feature] = {
                        'value': segment_value,
                        'global_difference_pct': difference_pct
                    }
            
            # Generate automatic description
            description = self._generate_segment_description(statistics, segment)
            self.segment_descriptions[segment] = {
                'statistics': statistics,
                'description': description
            }
    
    def _generate_segment_description(self, statistics, segment):
        """
        Generates automatic segment description based on its characteristics
        """
        descriptions = []
        
        # Analyze customer value
        if 'total_value' in statistics:
            value_diff = statistics['total_value']['global_difference_pct']
            if value_diff > 50:
                descriptions.append("High value")
            elif value_diff < -30:
                descriptions.append("Low value")
        
        # Analyze frequency
        if 'purchase_frequency' in statistics:
            freq_diff = statistics['purchase_frequency']['global_difference_pct']
            if freq_diff > 30:
                descriptions.append("High frequency")
            elif freq_diff < -30:
                descriptions.append("Low frequency")
        
        # Analyze recency
        if 'recency' in statistics:
            rec_diff = statistics['recency']['global_difference_pct']
            if rec_diff > 20:
                descriptions.append("Active customers")
            elif rec_diff < -50:
                descriptions.append("Dormant customers")
        
        # Analyze loyalty
        if 'brand_loyalty' in statistics:
            loy_diff = statistics['brand_loyalty']['global_difference_pct']
            if loy_diff > 20:
                descriptions.append("Brand loyal")
        
        # Segment name based on characteristics
        if not descriptions:
            name = f"Segment {segment + 1}"
        else:
            name = " - ".join(descriptions[:2])  # Maximum 2 main characteristics
        
        return name
    
    def visualize_segments(self, X):
        """
        Creates visualizations of identified segments
        """
        if self.clustering_model is None:
            print("Must run segment_customers() first")
            return
        
        # Reduce dimensionality for visualization
        pca = PCA(n_components=2)
        X_pca = pca.fit_transform(self.scaler.transform(X))
        
        # Create segment plots
        plt.figure(figsize=(12, 8))
        
        # Main segment plot
        plt.subplot(2, 2, 1)
        scatter = plt.scatter(X_pca[:, 0], X_pca[:, 1], 
                            c=self.clustering_model.labels_, 
                            cmap='viridis', alpha=0.7)
        plt.title('Customer Segmentation (PCA)')
        plt.xlabel(f'PC1 ({pca.explained_variance_ratio_[0]:.1%} variance)')
        plt.ylabel(f'PC2 ({pca.explained_variance_ratio_[1]:.1%} variance)')
        plt.colorbar(scatter, label='Segment')
        
        # Segment distribution
        plt.subplot(2, 2, 2)
        unique_segments, counts = np.unique(self.clustering_model.labels_, return_counts=True)
        plt.pie(counts, labels=[f'Seg {i+1}' for i in unique_segments], autopct='%1.1f%%')
        plt.title('Segment Distribution')
        
        # Heatmap of features by segment
        plt.subplot(2, 1, 2)
        X_with_segments = X.copy()
        X_with_segments['segment'] = self.clustering_model.labels_
        
        # Calculate means by segment
        segment_means = X_with_segments.groupby('segment').mean()
        
        # Normalize for heatmap
        normalized_means = (segment_means - segment_means.mean()) / segment_means.std()
        
        sns.heatmap(normalized_means.T, annot=True, cmap='RdYlBu_r', center=0)
        plt.title('Feature Profile by Segment')
        plt.xlabel('Segment')
        plt.ylabel('Features')
        
        plt.tight_layout()
        plt.show()
    
    def generate_segmentation_report(self):
        """
        Generates detailed segmentation report
        """
        if not self.segment_descriptions:
            return "No segmentation data available"
        
        report = "\n=== CUSTOMER SEGMENTATION REPORT ==="
        
        for segment, info in self.segment_descriptions.items():
            report += f"\n\nSEGMENT {segment + 1}: {info['description']}"
            report += f"\nSize: {info['statistics']['size']} customers ({info['statistics']['percentage']:.1f}%)"
            
            report += "\nDistinctive characteristics:"
            for feature, data in info['statistics'].items():
                if isinstance(data, dict) and abs(data['global_difference_pct']) > 20:
                    direction = "↑" if data['global_difference_pct'] > 0 else "↓"
                    report += f"\n  • {feature}: {direction} {abs(data['global_difference_pct']):.0f}% vs average"
        
        return report
    
    def recommend_strategies(self):
        """
        Generates marketing recommendations for each segment
        """
        recommendations = {}
        
        for segment, info in self.segment_descriptions.items():
            strategies = []
            stats = info['statistics']
            
            # Value-based strategies
            if 'total_value' in stats and stats['total_value']['global_difference_pct'] > 50:
                strategies.append("VIP program with exclusive benefits")
                strategies.append("Cross-selling premium products")
            
            # Frequency-based strategies
            if 'purchase_frequency' in stats and stats['purchase_frequency']['global_difference_pct'] < -30:
                strategies.append("Reactivation campaigns")
                strategies.append("Volume discounts to incentivize purchases")
            
            # Recency-based strategies
            if 'recency' in stats and stats['recency']['global_difference_pct'] < -50:
                strategies.append("Win-back email marketing")
                strategies.append("Special 'We miss you' offers")
            
            recommendations[segment] = strategies if strategies else ["Generic marketing with retention focus"]
        
        return recommendations

# Example usage with simulated data
np.random.seed(42)

# Simulate customer base
n_customers = 1000
example_customers = pd.DataFrame({
    'customer_id': range(1, n_customers + 1),
    'total_purchases': np.random.lognormal(6, 1, n_customers),
    'num_orders': np.random.poisson(5, n_customers) + 1,
    'unique_products_purchased': np.random.poisson(3, n_customers) + 1,
    'main_brand_purchases': np.random.poisson(3, n_customers) + 1,
    'first_purchase_date': pd.date_range('2022-01-01', periods=n_customers, freq='D'),
    'last_purchase_date': pd.date_range('2024-01-01', periods=n_customers, freq='D')
})

# Create segmenter
segmenter = AICustomerSegmenter()

# Perform segmentation
segmented_customers = segmenter.segment_customers(example_customers)

# Generate reports
report = segmenter.generate_segmentation_report()
recommendations = segmenter.recommend_strategies()

print(report)
print("\n=== MARKETING RECOMMENDATIONS ===")
for segment, strategies in recommendations.items():
    print(f"\nSegment {segment + 1}:")
    for strategy in strategies:
        print(f"  • {strategy}")

import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import requests
from datetime import datetime, timedelta

class SMEDataExtractor:
    def __init__(self):
        self.consolidated_data = {}
        self.metadata = {}
        
    def extract_multiple_excel(self, excel_files):
        """
        Extracts and consolidates data from multiple Excel files
        """
        combined_data = {}
        
        for file_name, config in excel_files.items():
            try:
                # Read Excel with specific configuration
                df = pd.read_excel(
                    config['path'],
                    sheet_name=config.get('sheet', 0),
                    header=config.get('header_row', 0)
                )
                
                # Clean column names
                df.columns = df.columns.str.strip().str.lower().str.replace(' ', '_')
                
                # Add source metadata
                df['data_source'] = file_name
                df['extraction_date'] = datetime.now()
                
                combined_data[file_name] = df
                
                print(f"✅ Extracted {file_name}: {len(df)} rows, {len(df.columns)} columns")
                
            except Exception as e:
                print(f"❌ Error extracting {file_name}: {str(e)}")
        
        return combined_data
    
    def extract_database(self, connection_string, queries):
        """
        Extracts data from SQL database
        """
        engine = create_engine(connection_string)
        db_data = {}
        
        for query_name, sql in queries.items():
            try:
                df = pd.read_sql(sql, engine)
                df['data_source'] = 'database'
                df['extraction_date'] = datetime.now()
                
                db_data[query_name] = df
                print(f"✅ Query {query_name}: {len(df)} records")
                
            except Exception as e:
                print(f"❌ Error in query {query_name}: {str(e)}")
        
        return db_data
    
    def extract_marketing_api(self, api_configurations):
        """
        Extracts data from marketing APIs (Google Analytics, Facebook, etc.)
        """
        marketing_data = {}
        
        for platform, config in api_configurations.items():
            if platform == 'google_analytics':
                marketing_data[platform] = self._extract_google_analytics(config)
            elif platform == 'facebook_ads':
                marketing_data[platform] = self._extract_facebook_ads(config)
            # Add more platforms as needed
        
        return marketing_data
    
    def _extract_google_analytics(self, config):
        """
        Simulates Google Analytics extraction
        (In real implementation, use Google Analytics Reporting API)
        """
        # GA data simulation
        dates = pd.date_range(
            start=datetime.now() - timedelta(days=90),
            end=datetime.now(),
            freq='D'
        )
        
        df = pd.DataFrame({
            'date': dates,
            'sessions': np.random.poisson(100, len(dates)),
            'users': np.random.poisson(80, len(dates)),
            'conversions': np.random.poisson(5, len(dates)),
            'revenue': np.random.normal(500, 100, len(dates))
        })
        
        df['data_source'] = 'google_analytics'
        return df
    
    def _extract_facebook_ads(self, config):
        """
        Simulates Facebook Ads extraction
        """
        dates = pd.date_range(
            start=datetime.now() - timedelta(days=30),
            end=datetime.now(),
            freq='D'
        )
        
        df = pd.DataFrame({
            'date': dates,
            'impressions': np.random.poisson(1000, len(dates)),
            'clicks': np.random.poisson(50, len(dates)),
            'spend': np.random.normal(25, 5, len(dates)),
            'conversions': np.random.poisson(2, len(dates))
        })
        
        df['data_source'] = 'facebook_ads'
        return df
    
    def consolidate_data(self, all_sources):
        """
        Consolidates data from multiple sources into unified structures
        """
        consolidated = {
            'sales': [],
            'customers': [],
            'marketing': [],
            'financial': []
        }
        
        for source, data in all_sources.items():
            for table, df in data.items():
                # Classify data by type
                if any(col in df.columns for col in ['sale', 'order', 'invoice']):
                    consolidated['sales'].append(df)
                elif any(col in df.columns for col in ['client', 'customer', 'user']):
                    consolidated['customers'].append(df)
                elif any(col in df.columns for col in ['impressions', 'clicks', 'conversion']):
                    consolidated['marketing'].append(df)
                elif any(col in df.columns for col in ['amount', 'price', 'cost']):
                    consolidated['financial'].append(df)
        
        # Combine DataFrames for each category
        for category, df_list in consolidated.items():
            if df_list:
                try:
                    combined_df = pd.concat(df_list, ignore_index=True, sort=False)
                    consolidated[category] = combined_df
                    print(f"✅ Consolidated {category}: {len(combined_df)} records")
                except Exception as e:
                    print(f"❌ Error consolidating {category}: {str(e)}")
                    consolidated[category] = pd.DataFrame()
            else:
                consolidated[category] = pd.DataFrame()
        
        self.consolidated_data = consolidated
        return consolidated
    
    def generate_quality_report(self):
        """
        Generates consolidated data quality report
        """
        report = "\n=== DATA QUALITY REPORT ==="
        
        for category, df in self.consolidated_data.items():
            if len(df) > 0:
                report += f"\n\n{category.upper()}:"
                report += f"\n  • Records: {len(df):,}"
                report += f"\n  • Columns: {len(df.columns)}"
                report += f"\n  • Missing values: {df.isnull().sum().sum():,} ({df.isnull().sum().sum()/df.size*100:.1f}%)"
                report += f"\n  • Duplicates: {df.duplicated().sum():,}"
                
                # Data period
                date_cols = [col for col in df.columns if 'date' in col.lower()]
                if date_cols:
                    date_col = date_cols[0]
                    try:
                        df[date_col] = pd.to_datetime(df[date_col])
                        min_date = df[date_col].min()
                        max_date = df[date_col].max()
                        report += f"\n  • Period: {min_date.date()} to {max_date.date()}"
                    except:
                        pass
        
        return report

# Example configuration for extraction
example_config = {
    'excel_files': {
        'sales': {
            'path': 'sales_2024.xlsx',
            'sheet': 'Data',
            'header_row': 0
        },
        'customers': {
            'path': 'customer_base.xlsx',
            'sheet': 'Customers',
            'header_row': 1
        }
    },
    'marketing_apis': {
        'google_analytics': {
            'property_id': 'GA_PROPERTY_ID',
            'credentials': 'path/to/credentials.json'
        },
        'facebook_ads': {
            'access_token': 'FB_ACCESS_TOKEN',
            'account_id': 'FB_ACCOUNT_ID'
        }
    }
}

# Using the extractor
extractor = SMEDataExtractor()

# Simulate extraction (in real case, use actual files and APIs)
excel_data = {
    'sales': pd.DataFrame({
        'date': pd.date_range('2024-01-01', periods=100),
        'customer_id': np.random.randint(1, 50, 100),
        'product': np.random.choice(['A', 'B', 'C'], 100),
        'quantity': np.random.randint(1, 10, 100),
        'price': np.random.normal(50, 10, 100)
    }),
    'customers': pd.DataFrame({
        'customer_id': range(1, 51),
        'name': [f'Customer {i}' for i in range(1, 51)],
        'segment': np.random.choice(['Premium', 'Standard', 'Basic'], 50)
    })
}

marketing_data = {
    'google_analytics': extractor._extract_google_analytics({}),
    'facebook_ads': extractor._extract_facebook_ads({})
}

# Consolidate all sources
all_sources = {
    'excel': excel_data,
    'marketing': marketing_data
}

consolidated_data = extractor.consolidate_data(all_sources)
quality_report = extractor.generate_quality_report()

print(quality_report)

import pandas as pd
import numpy as np
from sklearn.ensemble import RandomForestRegressor, IsolationForest
from sklearn.preprocessing import LabelEncoder
from sklearn.model_selection import train_test_split
from sklearn.metrics import mean_absolute_error, r2_score
import matplotlib.pyplot as plt
import seaborn as sns
from scipy import stats

class AutomaticAnalyzer:
    def __init__(self):
        self.insights = []
        self.important_correlations = []
        self.predictive_models = {}
        self.detected_anomalies = []
        
    def automatic_exploratory_analysis(self, df, target=None):
        """
        Performs automatic exploratory data analysis of tabular data
        """
        insights = []
        
        # Basic dataset information
        insights.append(f"Dataset: {len(df)} rows, {len(df.columns)} columns")
        
        # Data types analysis
        numeric = df.select_dtypes(include=[np.number]).columns.tolist()
        categorical = df.select_dtypes(include=['object']).columns.tolist()
        
        insights.append(f"Numeric variables: {len(numeric)}")
        insights.append(f"Categorical variables: {len(categorical)}")
        
        # Detect missing values
        missing_pct = (df.isnull().sum() / len(df) * 100).round(2)
        missing_variables = missing_pct[missing_pct > 0].sort_values(ascending=False)
        
        if len(missing_variables) > 0:
            insights.append(f"Variables with missing data: {len(missing_variables)}")
            for var, pct in missing_variables.head(3).items():
                insights.append(f"  • {var}: {pct}% missing")
        
        # Distribution analysis
        for col in numeric[:5]:  # Analyze top 5 numeric variables
            skewness = stats.skew(df[col].dropna())
            if abs(skewness) > 1:
                direction = "positive" if skewness > 0 else "negative"
                insights.append(f"Variable '{col}' has {direction} skewness (skew: {skewness:.2f})")
        
        # Detect variables with low variance
        for col in numeric:
            cv = df[col].std() / df[col].mean() if df[col].mean() != 0 else 0
            if cv < 0.1:
                insights.append(f"Variable '{col}' has low variability (CV: {cv:.3f})")
        
        # Cardinality analysis for categorical variables
        for col in categorical:
            unique_count = df[col].nunique()
            total_count = len(df[col].dropna())
            if unique_count / total_count > 0.5:
                insights.append(f"Variable '{col}' has high cardinality ({unique_count} unique values)")
        
        return insights
    
    def detect_important_correlations(self, df, threshold=0.7):
        """
        Detects strong correlations between variables
        """
        numeric = df.select_dtypes(include=[np.number]).columns
        
        if len(numeric) < 2:
            return []
        
        corr_matrix = df[numeric].corr()
        correlations = []
        
        # Find strong correlations
        for i in range(len(corr_matrix.columns)):
            for j in range(i+1, len(corr_matrix.columns)):
                var1 = corr_matrix.columns[i]
                var2 = corr_matrix.columns[j]
                corr_val = corr_matrix.iloc[i, j]
                
                if abs(corr_val) >= threshold:
                    correlations.append({
                        'variable_1': var1,
                        'variable_2': var2,
                        'correlation': corr_val,
                        'strength': 'Very strong' if abs(corr_val) >= 0.9 else 'Strong'
                    })
        
        # Sort by correlation strength
        correlations.sort(key=lambda x: abs(x['correlation']), reverse=True)
        
        self.important_correlations = correlations
        return correlations
    
    def detect_anomalies(self, df, contamination=0.1):
        """
        Detects anomalies in data using Isolation Forest
        """
        numeric = df.select_dtypes(include=[np.number]).columns
        
        if len(numeric) == 0:
            return []
        
        # Prepare data for anomaly detection
        X = df[numeric].fillna(df[numeric].median())
        
        # Apply Isolation Forest
        iso_forest = IsolationForest(contamination=contamination, random_state=42)
        anomalies = iso_forest.fit_predict(X)
        
        # Identify anomalous records
        anomalous_indices = df.index[anomalies == -1].tolist()
        
        anomalies_info = []
        for idx in anomalous_indices[:10]:  # Top 10 anomalies
            record = df.loc[idx]
            anomalies_info.append({
                'index': idx,
                'unusual_values': {}
            })
            
            # Identify which variables contribute to the anomaly
            for col in numeric:
                value = record[col]
                if pd.notna(value):
                    percentile = stats.percentileofscore(df[col].dropna(), value)
                    if percentile > 95 or percentile < 5:
                        anomalies_info[-1]['unusual_values'][col] = {
                            'value': value,
                            'percentile': percentile
                        }
        
        self.detected_anomalies = anomalies_info
        return anomalies_info
    
    def create_automatic_predictive_model(self, df, target_variable):
        """
        Creates automatic predictive model for target variable
        """
        if target_variable not in df.columns:
            return None
        
        # Prepare features
        features = []
        for col in df.columns:
            if col != target_variable:
                if df[col].dtype in ['int64', 'float64']:
                    features.append(col)
                elif df[col].dtype == 'object' and df[col].nunique() < 50:
                    features.append(col)
        
        if len(features) == 0:
            return None
        
        # Prepare dataset
        X = df[features].copy()
        y = df[target_variable].copy()
        
        # Encode categorical variables
        label_encoders = {}
        for col in X.columns:
            if X[col].dtype == 'object':
                le = LabelEncoder()
                X[col] = le.fit_transform(X[col].astype(str))
                label_encoders[col] = le
        
        # Handle missing values
        X = X.fillna(X.median())
        y = y.fillna(y.median())
        
        # Split data
        X_train, X_test, y_train, y_test = train_test_split(
            X, y, test_size=0.2, random_state=42
        )
        
        # Train Random Forest model
        model = RandomForestRegressor(n_estimators=100, random_state=42)
        model.fit(X_train, y_train)
        
        # Evaluate model
        y_pred = model.predict(X_test)
        mae = mean_absolute_error(y_test, y_pred)
        r2 = r2_score(y_test, y_pred)
        
        # Feature importance
        importances = pd.DataFrame({
            'feature': features,
            'importance': model.feature_importances_
        }).sort_values('importance', ascending=False)
        
        model_result = {
            'model': model,
            'label_encoders': label_encoders,
            'features': features,
            'mae': mae,
            'r2': r2,
            'importances': importances,
            'accuracy_pct': max(0, (1 - mae/y.std()) * 100)
        }
        
        self.predictive_models[target_variable] = model_result
        return model_result
    
    def generate_automatic_insights(self, df, target_variables=None):
        """
        Generates complete automatic insights from dataset
        """
        complete_insights = []
        
        # Exploratory analysis
        exploratory_insights = self.automatic_exploratory_analysis(df)
        complete_insights.extend(exploratory_insights)
        
        # Important correlations
        correlations = self.detect_important_correlations(df)
        if correlations:
            complete_insights.append("\nIMPORTANT CORRELATIONS:")
            for corr in correlations[:5]:
                complete_insights.append(
                    f"• {corr['variable_1']} and {corr['variable_2']}: "
                    f"{corr['correlation']:.3f} ({corr['strength'].lower()})")
        
        # Anomalies
        anomalies = self.detect_anomalies(df)
        if anomalies:
            complete_insights.append(f"\nDETECTED ANOMALIES: {len(anomalies)} unusual records")
        
        # Predictive models
        if target_variables:
            complete_insights.append("\nPREDICTIVE MODELS:")
            for target_var in target_variables:
                if target_var in df.columns:
                    model_info = self.create_automatic_predictive_model(df, target_var)
                    if model_info:
                        complete_insights.append(
                            f"• {target_var}: Accuracy {model_info['accuracy_pct']:.1f}% "
                            f"(R² = {model_info['r2']:.3f})")
                        
                        # Top 3 most important variables
                        top_vars = model_info['importances'].head(3)
                        for _, row in top_vars.iterrows():
                            complete_insights.append(
                                f"  - {row['feature']}: {row['importance']:.3f} importance")
        
        return "\n".join(complete_insights)
    
    def create_automatic_dashboard(self, df):
        """
        Creates automatic visual dashboard
        """
        numeric = df.select_dtypes(include=[np.number]).columns
        
        if len(numeric) < 2:
            print("Insufficient numeric variables to create dashboard")
            return
        
        fig, axes = plt.subplots(2, 2, figsize=(15, 12))
        
        # Distributions of main variables
        if len(numeric) >= 1:
            df[numeric[0]].hist(bins=30, ax=axes[0,0], alpha=0.7)
            axes[0,0].set_title(f'Distribution of {numeric[0]}')
            axes[0,0].set_xlabel(numeric[0])
            axes[0,0].set_ylabel('Frequency')
        
        # Correlations
        if len(numeric) >= 2:
            corr_matrix = df[numeric].corr()
            sns.heatmap(corr_matrix, annot=True, cmap='coolwarm', center=0, ax=axes[0,1])
            axes[0,1].set_title('Correlation Matrix')
        
        # Boxplot to detect outliers
        if len(numeric) >= 1:
            df[numeric[:5]].boxplot(ax=axes[1,0])
            axes[1,0].set_title('Outlier Detection')
            axes[1,0].tick_params(axis='x', rotation=45)
        
        # Time series if there's a date
        date_cols = [col for col in df.columns if 'date' in col.lower() or 'date' in col.lower()]
        if date_cols and len(numeric) >= 1:
            try:
                df_temp = df.copy()
                df_temp[date_cols[0]] = pd.to_datetime(df_temp[date_cols[0]])
                df_temp = df_temp.sort_values(date_cols[0])
                
                axes[1,1].plot(df_temp[date_cols[0]], df_temp[numeric[0]])
                axes[1,1].set_title(f'Temporal evolution of {numeric[0]}')
                axes[1,1].tick_params(axis='x', rotation=45)
            except:
                axes[1,1].text(0.5, 0.5, 'Could not create temporal chart', 
                              ha='center', va='center', transform=axes[1,1].transAxes)
        
        plt.tight_layout()
        plt.show()
        return fig

# Usage example
np.random.seed(42)

# Create example dataset
example_df = pd.DataFrame({
    'date': pd.date_range('2023-01-01', periods=365),
    'sales': np.random.lognormal(mean=6, sigma=0.5, size=365),
    'customers': np.random.poisson(lam=50, size=365),
    'marketing_spend': np.random.normal(loc=1000, scale=200, size=365),
    'temperature': np.random.normal(loc=15, scale=10, size=365),
    'day_of_week': np.random.choice(['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'], 365),
    'category': np.random.choice(['A', 'B', 'C'], 365)
})

# Introduce some correlations and anomalies
example_df.loc[50:100, 'sales'] *= 2  # Anomaly in sales
example_df['revenue'] = example_df['sales'] * 1.2 + np.random.normal(0, 50, 365)  # Correlation with sales

# Create analyzer
analyzer = AutomaticAnalyzer()

# Perform complete analysis
insights = analyzer.generate_automatic_insights(
    example_df, 
    target_variables=['sales', 'revenue']
)

print("=== AUTOMATIC DATA ANALYSIS ===")
print(insights)

# Create dashboard
fig = analyzer.create_automatic_dashboard(example_df)

# Example of automatic analysis using simple Python libraries
import pandas as pd
import numpy as np
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split
from sklearn.metrics import accuracy_score, classification_report
import matplotlib.pyplot as plt

class SimpleExcelAnalyzer:
    """
    Simplified class for Excel analysis that can be 
    used by users with basic Python knowledge
    """
    
    def __init__(self, excel_file):
        """
        Initializes the analyzer with an Excel file
        """
        try:
            self.data = pd.read_excel(excel_file)
            print(f"✅ File loaded: {len(self.data)} rows, {len(self.data.columns)} columns")
            print(f"Available columns: {list(self.data.columns)}")
        except Exception as e:
            print(f"❌ Error loading file: {e}")
            self.data = None
    
    def automatic_summary(self):
        """
        Generates automatic data summary
        """
        if self.data is None:
            return "No data loaded"
        
        summary = "\n=== AUTOMATIC SUMMARY ==="
        
        # Basic information
        summary += f"\nRows: {len(self.data):,}"
        summary += f"\nColumns: {len(self.data.columns)}"
        
        # Data types
        numeric = self.data.select_dtypes(include=[np.number]).columns.tolist()
        text = self.data.select_dtypes(include=['object']).columns.tolist()
        
        summary += f"\nNumeric variables: {len(numeric)}"
        summary += f"\nText variables: {len(text)}"
        
        # Missing values
        missing = self.data.isnull().sum()
        if missing.sum() > 0:
            summary += "\n\nMissing values:"
            for col, count in missing[missing > 0].items():
                pct = (count / len(self.data)) * 100
                summary += f"\n  • {col}: {count} ({pct:.1f}%)"
        
        # Basic statistics for numeric variables
        if numeric:
            summary += "\n\nNumeric variable statistics:"
            for col in numeric[:3]:  # Only first 3
                stats = self.data[col].describe()
                summary += f"\n  • {col}: Average {stats['mean']:.2f}, Range {stats['min']:.2f} - {stats['max']:.2f}"
        
        return summary
    
    def find_correlations(self, threshold=0.5):
        """
        Finds important correlations between variables
        """
        numeric = self.data.select_dtypes(include=[np.number]).columns
        
        if len(numeric) < 2:
            return "Not enough numeric variables to calculate correlations"
        
        correlations = self.data[numeric].corr()
        
        # Search for strong correlations
        results = "\n=== IMPORTANT CORRELATIONS ==="
        found = False
        
        for i in range(len(correlations.columns)):
            for j in range(i+1, len(correlations.columns)):
                var1 = correlations.columns[i]
                var2 = correlations.columns[j]
                corr_val = correlations.iloc[i, j]
                
                if abs(corr_val) >= threshold:
                    found = True
                    direction = "positive" if corr_val > 0 else "negative"
                    strength = "very strong" if abs(corr_val) >= 0.8 else "strong"
                    results += f"\n• {var1} and {var2}: {corr_val:.3f} ({direction} {strength} correlation)"
        
        if not found:
            results += f"\nNo correlations above {threshold} found"
        
        return results
    
    def predict_variable(self, target, use_all_variables=True):
        """
        Creates simple predictive model for a target variable
        """
        if target not in self.data.columns:
            return f"Variable '{target}' does not exist in the data"
        
        # Prepare data
        if use_all_variables:
            # Use all numeric variables except the target
            features = self.data.select_dtypes(include=[np.number]).columns.tolist()
            if target in features:
                features.remove(target)
        else:
            # User can specify which variables to use
            features = []  # Logic for manual selection would be implemented
        
        if len(features) == 0:
            return "No predictor variables available"
        
        # Clean data
        clean_data = self.data[features + [target]].dropna()
        
        if len(clean_data) < 50:
            return "Insufficient data to create predictive model"
        
        X = clean_data[features]
        y = clean_data[target]
        
        # Split data
        X_train, X_test, y_train, y_test = train_test_split(
            X, y, test_size=0.2, random_state=42
        )
        
        # Train simple model
        model = RandomForestClassifier(n_estimators=50, random_state=42)
        model.fit(X_train, y_train)
        
        # Evaluate
        y_pred = model.predict(X_test)
        accuracy = accuracy_score(y_test, y_pred)
        
        # Variable importance
        importances = pd.DataFrame({
            'variable': features,
            'importance': model.feature_importances_
        }).sort_values('importance', ascending=False)
        
        result = f"\n=== PREDICTIVE MODEL FOR '{target}' ==="
        result += f"\nModel accuracy: {accuracy:.1%}"
        result += "\nMost important variables:"
        
        for _, row in importances.head(5).iterrows():
            result += f"\n  • {row['variable']}: {row['importance']:.3f}"
        
        if accuracy > 0.7:
            result += "\n\n✅ Model has good accuracy for predictions"
        elif accuracy > 0.5:
            result += "\n\n⚠️ Model has moderate accuracy, use with caution"
        else:
            result += "\n\n❌ Model has low accuracy, review data or variables"
        
        return result
    
    def create_automatic_charts(self):
        """
        Creates automatic data charts
        """
        numeric = self.data.select_dtypes(include=[np.number]).columns.tolist()
        
        if len(numeric) == 0:
            print("No numeric variables to chart")
            return
        
        # Determine chart layout
        num_charts = min(4, len(numeric))
        if num_charts == 1:
            fig, axes = plt.subplots(1, 1, figsize=(8, 6))
            axes = [axes]
        elif num_charts == 2:
            fig, axes = plt.subplots(1, 2, figsize=(12, 5))
        else:
            fig, axes = plt.subplots(2, 2, figsize=(12, 10))
            axes = axes.flatten()
        
        # Create charts
        for i, col in enumerate(numeric[:num_charts]):
            # Histogram
            self.data[col].hist(bins=20, ax=axes[i], alpha=0.7)
            axes[i].set_title(f'Distribution of {col}')
            axes[i].set_xlabel(col)
            axes[i].set_ylabel('Frequency')
            
            # Add statistics to chart
            mean = self.data[col].mean()
            median = self.data[col].median()
            axes[i].axvline(mean, color='red', linestyle='--', label=f'Mean: {mean:.2f}')
            axes[i].axvline(median, color='green', linestyle='--', label=f'Median: {median:.2f}')
            axes[i].legend()
        
        plt.tight_layout()
        plt.show()
        
        return fig
    
    def complete_analysis(self):
        """
        Executes complete automatic analysis
        """
        if self.data is None:
            return "No data to analyze"
        
        print(self.automatic_summary())
        print(self.find_correlations())
        
        # Try to create predictive model for first numeric variable
        numeric = self.data.select_dtypes(include=[np.number]).columns.tolist()
        if numeric:
            print(self.predict_variable(numeric[0]))
        
        # Create charts
        self.create_automatic_charts()
        
        return "Complete analysis finished"

# Simplified usage example
if __name__ == "__main__":
    # Create example data and save as Excel
    example_data = pd.DataFrame({
        'sales': np.random.normal(1000, 200, 300),
        'marketing': np.random.normal(500, 100, 300),
        'customers': np.random.poisson(50, 300),
        'satisfaction': np.random.choice(['High', 'Medium', 'Low'], 300),
        'month': np.random.choice(range(1, 13), 300)
    })
    
    example_data.to_excel('analysis_example.xlsx', index=False)
    
    # Use the simple analyzer
    analyzer = SimpleExcelAnalyzer('analysis_example.xlsx')
    result = analyzer.complete_analysis()
    
    print("\n" + "="*50)
    print("USAGE EXAMPLE COMPLETED")
    print("="*50)