{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.11.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":100356,"databundleVersionId":12062942,"sourceType":"competition"}],"dockerImageVersionId":31089,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"execution":{"iopub.status.busy":"2025-07-09T19:07:04.201647Z","iopub.execute_input":"2025-07-09T19:07:04.201881Z","iopub.status.idle":"2025-07-09T19:07:06.162721Z","shell.execute_reply.started":"2025-07-09T19:07:04.201860Z","shell.execute_reply":"2025-07-09T19:07:06.161793Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"#!/usr/bin/env python\n# coding: utf-8\n\n# # 25Spr. UC Quant Competition - Complete Working Solution\n\nimport pandas as pd\nimport numpy as np\nfrom sklearn.model_selection import train_test_split, KFold\nfrom sklearn.preprocessing import StandardScaler\nfrom sklearn.linear_model import Ridge\nfrom sklearn.ensemble import RandomForestRegressor\nfrom sklearn.metrics import mean_squared_error, mean_absolute_error\nfrom sklearn.feature_selection import SelectKBest, f_regression\nimport xgboost as xgb\nimport lightgbm as lgb\nfrom scipy import stats\nimport gc\nimport warnings\nwarnings.filterwarnings('ignore')\n\n# Set display options\npd.set_option('display.max_columns', 50)\npd.set_option('display.max_rows', 100)\n\n# Set random seed\nnp.random.seed(42)\n\nprint(\"=\" * 80)\nprint(\"25SPR. UC QUANT COMPETITION - COMPLETE WORKING SOLUTION\")\nprint(\"=\" * 80)\n\n# ## 1. Load All Datasets\n\nprint(\"\\n[STEP 1] Loading datasets...\")\nprint(\"-\" * 50)\n\ncustomers = pd.read_excel('/kaggle/input/25-spr-uc-quant/Customers.xlsx')\nsuppliers = pd.read_excel('/kaggle/input/25-spr-uc-quant/Suppliers.xlsx')\ndataset = pd.read_csv('/kaggle/input/25-spr-uc-quant/dataset.csv')\ndataset_extra = pd.read_csv('/kaggle/input/25-spr-uc-quant/dataset_extra.csv')\nsubmission_example = pd.read_csv('/kaggle/input/25-spr-uc-quant/submission_example.csv')\n\nprint(f\"✓ Main dataset shape: {dataset.shape}\")\nprint(f\"✓ Extra dataset shape: {dataset_extra.shape}\")\nprint(f\"✓ Customers shape: {customers.shape}\")\nprint(f\"✓ Suppliers shape: {suppliers.shape}\")\nprint(f\"✓ Submission example shape: {submission_example.shape}\")\n\n# Examine submission example format\nprint(\"\\n[SUBMISSION EXAMPLE]\")\nprint(f\"Columns: {list(submission_example.columns)}\")\nprint(f\"First 5 rows:\")\nprint(submission_example.head())\nprint(f\"\\nData types:\")\nprint(submission_example.dtypes)\n\n# Check target distribution\nprint(f\"\\nTarget info:\")\nprint(f\"  Non-null targets: {dataset['target'].notna().sum()}\")\nprint(f\"  Null targets: {dataset['target'].isna().sum()}\")\n\n# ## 2. Create Supply Chain Features\n\ndef create_supply_chain_features(df, customers_df, suppliers_df):\n    \"\"\"Create supply chain network features\"\"\"\n    print(\"\\n[SUPPLY CHAIN FEATURES] Creating features...\")\n    df = df.copy()\n    \n    # Initialize counts\n    customer_counts = {}\n    supplier_counts = {}\n    \n    # Count connections for each stock\n    for col in customers_df.columns[1:]:  # Skip 'Local_Code'\n        count = customers_df[col].notna().sum()\n        if count > 0:\n            customer_counts[col] = count\n    \n    for col in suppliers_df.columns[1:]:\n        count = suppliers_df[col].notna().sum()\n        if count > 0:\n            supplier_counts[col] = count\n    \n    # Map to dataframe\n    df['n_customers'] = df['S_INFO_WINDCODE'].map(customer_counts).fillna(0)\n    df['n_suppliers'] = df['S_INFO_WINDCODE'].map(supplier_counts).fillna(0)\n    df['total_connections'] = df['n_customers'] + df['n_suppliers']\n    df['customer_supplier_ratio'] = df['n_customers'] / (df['n_suppliers'] + 1)\n    \n    max_conn = df['total_connections'].max()\n    df['supply_chain_importance'] = df['total_connections'] / (max_conn + 1)\n    \n    print(\"✓ Created supply chain features\")\n    return df\n\n# ## 3. Create Time Series Features\n\ndef create_time_series_features(df, extra_df):\n    \"\"\"Create time series features efficiently\"\"\"\n    print(\"\\n[TIME SERIES FEATURES] Processing extra dataset...\")\n    \n    # Calculate basic statistics per stock\n    print(\"Calculating statistics per stock...\")\n    \n    # Group by stock and calculate features\n    ts_features = extra_df.groupby('S_INFO_WINDCODE').agg({\n        'S_DQ_PCTCHANGE': ['mean', 'std', 'min', 'max', 'count'],\n        'S_DQ_VOLUME': ['mean', 'std'],\n        'S_DQ_AMOUNT': ['mean', 'std']\n    })\n    \n    # Flatten column names\n    ts_features.columns = ['_'.join(col).strip() for col in ts_features.columns.values]\n    ts_features = ts_features.reset_index()\n    \n    # Calculate additional features\n    print(\"Calculating momentum and volatility features...\")\n    momentum_features = []\n    \n    for stock in extra_df['S_INFO_WINDCODE'].unique():\n        stock_data = extra_df[extra_df['S_INFO_WINDCODE'] == stock].sort_values('TRADE_DT')\n        pct_changes = stock_data['S_DQ_PCTCHANGE'].values\n        \n        features = {'S_INFO_WINDCODE': stock}\n        \n        # Volatility measures\n        if len(pct_changes) >= 2:\n            features['pct_range_90'] = np.percentile(pct_changes, 95) - np.percentile(pct_changes, 5)\n        else:\n            features['pct_range_90'] = 0\n            \n        if len(pct_changes) > 3:\n            features['pct_skew'] = stats.skew(pct_changes)\n        else:\n            features['pct_skew'] = 0\n            \n        # Momentum\n        features['momentum_5d'] = np.sum(pct_changes[-5:]) if len(pct_changes) >= 5 else 0\n        features['momentum_20d'] = np.sum(pct_changes[-20:]) if len(pct_changes) >= 20 else 0\n        \n        momentum_features.append(features)\n    \n    momentum_df = pd.DataFrame(momentum_features)\n    \n    # Merge all time series features\n    ts_features = ts_features.merge(momentum_df, on='S_INFO_WINDCODE', how='left')\n    \n    # Merge with main dataframe\n    df = df.merge(ts_features, on='S_INFO_WINDCODE', how='left')\n    \n    # Fill missing values\n    ts_cols = [col for col in ts_features.columns if col != 'S_INFO_WINDCODE']\n    for col in ts_cols:\n        df[col] = df[col].fillna(0)\n    \n    print(f\"✓ Added {len(ts_cols)} time series features\")\n    return df\n\n# Apply features\ndataset = create_supply_chain_features(dataset, customers, suppliers)\ndataset = create_time_series_features(dataset, dataset_extra)\n\n# Clean up memory\ndel customers, suppliers, dataset_extra\ngc.collect()\n\n# ## 4. Feature Engineering\n\nprint(\"\\n[FEATURE ENGINEERING] Creating derived features...\")\n\n# Small epsilon to avoid division by zero\neps = 1e-10\n\n# Flow features\ntotal_flow = (np.abs(dataset['Net_Flow_ExLarge']) + np.abs(dataset['Net_Flow_Large']) + \n              np.abs(dataset['Net_Flow_Med']) + np.abs(dataset['Net_Flow_Small']) + eps)\n\ndataset['large_flow_dominance'] = (dataset['Net_Flow_ExLarge'] + dataset['Net_Flow_Large']) / total_flow\ndataset['flow_imbalance'] = ((dataset['Net_Flow_ExLarge'] + dataset['Net_Flow_Large']) - \n                             (dataset['Net_Flow_Med'] + dataset['Net_Flow_Small'])) / total_flow\n\n# Risk metrics\ndataset['sharpe_ratio'] = dataset['Return_1M_Past'] / (dataset['Volatility'] + eps)\ndataset['volatility_scaled'] = dataset['Volatility'] / (dataset['S_DQ_PCTCHANGE_std'] + eps)\n\n# Fundamental ratios\ndataset['earnings_yield'] = dataset['EST_EPS'] / (np.abs(dataset['NET_PROFIT']) + 1)\ndataset['profit_margin'] = dataset['NET_PROFIT'] / (np.abs(dataset['EST_OPER_PROFIT']) + 1)\ndataset['roe_stability'] = dataset['EST_ROE'] / (dataset['S_DQ_PCTCHANGE_std'] + eps)\n\n# Network-weighted features\ndataset['network_roe'] = dataset['EST_ROE'] * dataset['supply_chain_importance']\ndataset['network_eps'] = dataset['EST_EPS'] * dataset['supply_chain_importance']\n\n# Transformations\ndataset['log_volatility'] = np.log1p(np.abs(dataset['Volatility']))\ndataset['sqrt_connections'] = np.sqrt(dataset['total_connections'])\n\nprint(\"✓ Created derived features\")\n\n# ## 5. Handle Missing Values\n\nprint(\"\\n[MISSING VALUES] Handling missing values...\")\n\n# Get feature columns\nnumeric_cols = dataset.select_dtypes(include=[np.number]).columns.tolist()\nfeature_cols = [col for col in numeric_cols if col not in ['target', 'TRADE_DT']]\n\nprint(f\"Number of features: {len(feature_cols)}\")\n\n# Fill missing values\nfor col in feature_cols:\n    if dataset[col].isnull().any():\n        if col in ['n_customers', 'n_suppliers', 'total_connections']:\n            dataset[col] = dataset[col].fillna(0)\n        else:\n            median_val = dataset[col].median()\n            dataset[col] = dataset[col].fillna(median_val if not pd.isna(median_val) else 0)\n\nprint(\"✓ Missing values handled\")\n\n# ## 6. Model Training with Cross-Validation\n\nprint(\"\\n[MODEL TRAINING] Training models with cross-validation...\")\n\n# Since all data has targets, we'll use cross-validation\nX = dataset[feature_cols].values\ny = dataset['target'].values\n\nprint(f\"Data shape: X={X.shape}, y={y.shape}\")\n\n# Feature selection\nprint(\"\\nSelecting best features...\")\nk_features = min(40, len(feature_cols))\nselector = SelectKBest(f_regression, k=k_features)\nX_selected = selector.fit_transform(X, y)\nselected_features = [feature_cols[i] for i in range(len(feature_cols)) if selector.get_support()[i]]\n\nprint(f\"Selected {len(selected_features)} features\")\n\n# Scale features\nscaler = StandardScaler()\nX_scaled = scaler.fit_transform(X_selected)\n\n# Define models\nmodels = {\n    'Ridge': Ridge(alpha=1.0, random_state=42),\n    'RandomForest': RandomForestRegressor(\n        n_estimators=50,  # Reduced for speed\n        max_depth=10,\n        min_samples_split=20,\n        random_state=42,\n        n_jobs=-1\n    ),\n    'XGBoost': xgb.XGBRegressor(\n        n_estimators=50,  # Reduced for speed\n        learning_rate=0.1,\n        max_depth=6,\n        subsample=0.8,\n        random_state=42,\n        n_jobs=-1,\n        verbosity=0\n    ),\n    'LightGBM': lgb.LGBMRegressor(\n        n_estimators=50,  # Reduced for speed\n        learning_rate=0.1,\n        num_leaves=31,\n        subsample=0.8,\n        random_state=42,\n        n_jobs=-1,\n        verbosity=-1\n    )\n}\n\n# Cross-validation\nprint(\"\\nPerforming 3-fold cross-validation...\")\nkf = KFold(n_splits=3, shuffle=True, random_state=42)\ncv_scores = {}\n\nfor name, model in models.items():\n    print(f\"\\nEvaluating {name}...\")\n    fold_scores = []\n    \n    for fold, (train_idx, val_idx) in enumerate(kf.split(X_scaled)):\n        X_train, X_val = X_scaled[train_idx], X_scaled[val_idx]\n        y_train, y_val = y[train_idx], y[val_idx]\n        \n        model.fit(X_train, y_train)\n        y_pred = model.predict(X_val)\n        mse = mean_squared_error(y_val, y_pred)\n        fold_scores.append(mse)\n        \n        print(f\"  Fold {fold+1}: MSE = {mse:.6f}\")\n    \n    cv_scores[name] = np.mean(fold_scores)\n    print(f\"  Average MSE: {cv_scores[name]:.6f}\")\n\n# Calculate ensemble weights\nmse_values = list(cv_scores.values())\nweights = 1 / np.array(mse_values)\nweights = weights / weights.sum()\n\nprint(\"\\nEnsemble weights:\")\nfor name, weight in zip(models.keys(), weights):\n    print(f\"  {name}: {weight:.4f}\")\n\n# ## 7. Train Final Models\n\nprint(\"\\n[FINAL TRAINING] Training models on full dataset...\")\n\nfinal_models = {}\nfinal_predictions = np.zeros(len(X_scaled))\n\nfor name, model in models.items():\n    print(f\"Training final {name}...\")\n    \n    # Create fresh model instance\n    if name == 'Ridge':\n        final_model = Ridge(alpha=1.0, random_state=42)\n    elif name == 'RandomForest':\n        final_model = RandomForestRegressor(\n            n_estimators=50, max_depth=10, min_samples_split=20,\n            random_state=42, n_jobs=-1\n        )\n    elif name == 'XGBoost':\n        final_model = xgb.XGBRegressor(\n            n_estimators=50, learning_rate=0.1, max_depth=6,\n            subsample=0.8, random_state=42, n_jobs=-1, verbosity=0\n        )\n    else:  # LightGBM\n        final_model = lgb.LGBMRegressor(\n            n_estimators=50, learning_rate=0.1, num_leaves=31,\n            subsample=0.8, random_state=42, n_jobs=-1, verbosity=-1\n        )\n    \n    # Train on full data\n    final_model.fit(X_scaled, y)\n    final_models[name] = final_model\n    \n    # Add weighted predictions\n    predictions = final_model.predict(X_scaled)\n    final_predictions += weights[list(models.keys()).index(name)] * predictions\n\n# Add predictions to dataset\ndataset['prediction'] = final_predictions\n\n# ## 8. Create Submission\n\nprint(\"\\n[SUBMISSION] Creating submission file...\")\n\n# Get unique stocks\nunique_stocks = sorted(dataset['S_INFO_WINDCODE'].unique())\nprint(f\"Total unique stocks: {len(unique_stocks)}\")\n\n# Create mapping for first 1800 stocks\nstock_to_id = {stock: idx + 1 for idx, stock in enumerate(unique_stocks[:1800])}\n\n# Aggregate predictions by stock\nprint(\"Aggregating predictions by stock...\")\nstock_predictions = dataset.groupby('S_INFO_WINDCODE').agg({\n    'prediction': ['mean', 'median', 'std', 'count'],\n    'target': 'mean'  # Also get actual target mean for comparison\n}).reset_index()\n\n# Flatten columns\nstock_predictions.columns = ['S_INFO_WINDCODE', 'pred_mean', 'pred_median', \n                             'pred_std', 'pred_count', 'target_mean']\n\n# Add stock IDs\nstock_predictions['stock_id'] = stock_predictions['S_INFO_WINDCODE'].map(stock_to_id)\n\n# Filter to stocks with IDs\nstock_predictions_valid = stock_predictions[stock_predictions['stock_id'].notna()].copy()\n\nprint(f\"Stocks with valid IDs: {len(stock_predictions_valid)}\")\n\n# Create submission\nsubmission = pd.DataFrame({\n    'S_INFO_WINDCODE': stock_predictions_valid['stock_id'].astype(int),\n    'target': stock_predictions_valid['pred_mean']\n})\n\n# Handle missing stocks\nif len(submission) < 1800:\n    missing_ids = set(range(1, 1801)) - set(submission['S_INFO_WINDCODE'])\n    \n    # Use global mean as default\n    global_mean = dataset['target'].mean()\n    print(f\"\\nAdding {len(missing_ids)} missing stocks with global mean: {global_mean:.6f}\")\n    \n    missing_df = pd.DataFrame({\n        'S_INFO_WINDCODE': sorted(list(missing_ids)),\n        'target': global_mean\n    })\n    \n    submission = pd.concat([submission, missing_df], ignore_index=True)\n\n# Sort by stock ID first\nsubmission = submission.sort_values('S_INFO_WINDCODE').reset_index(drop=True)\n\n# Add rank column based on target values\n# IMPORTANT: Assuming higher predicted target = better stock = lower rank (1 is best)\n# If the competition expects opposite ranking, change ascending=False to ascending=True\nsubmission['rank'] = submission['target'].rank(method='first', ascending=False).astype(int)\n\n# Alternative ranking approaches (commented out):\n# For percentile ranking: submission['rank'] = submission['target'].rank(pct=True)\n# For dense ranking: submission['rank'] = submission['target'].rank(method='dense', ascending=False)\n# For average ranking: submission['rank'] = submission['target'].rank(method='average', ascending=False)\n\n# Verify rank column\nprint(f\"\\nRank statistics:\")\nprint(f\"  Min rank: {submission['rank'].min()}\")\nprint(f\"  Max rank: {submission['rank'].max()}\")\nprint(f\"  Unique ranks: {submission['rank'].nunique()}\")\n\n# Double-check: show stocks with highest and lowest predicted targets\nprint(\"\\nStocks with highest predicted targets (should have lowest ranks):\")\nprint(submission.nlargest(5, 'target')[['S_INFO_WINDCODE', 'target', 'rank']])\n\nprint(\"\\nStocks with lowest predicted targets (should have highest ranks):\")\nprint(submission.nsmallest(5, 'target')[['S_INFO_WINDCODE', 'target', 'rank']])\n\n# Save submission\n# Ensure columns are in the correct order\nsubmission = submission[['S_INFO_WINDCODE', 'target', 'rank']]\n\n# Final sort by S_INFO_WINDCODE to ensure order\nsubmission = submission.sort_values('S_INFO_WINDCODE').reset_index(drop=True)\n\nsubmission.to_csv('submission.csv', index=False)\n\nprint(\"\\n✅ Submission saved with columns:\", list(submission.columns))\n\n# ## 9. Summary and Validation\n\nprint(\"\\n[VALIDATION] Submission validation...\")\nprint(f\"Submission shape: {submission.shape}\")\nprint(f\"Submission columns: {list(submission.columns)}\")\nprint(f\"Unique stock IDs: {submission['S_INFO_WINDCODE'].nunique()}\")\nprint(f\"Stock ID range: {submission['S_INFO_WINDCODE'].min()} to {submission['S_INFO_WINDCODE'].max()}\")\n\n# Check for duplicates\nduplicates = submission['S_INFO_WINDCODE'].duplicated().sum()\nprint(f\"Duplicate stock IDs: {duplicates}\")\n\n# Statistics\nprint(f\"\\nTarget statistics:\")\nprint(f\"  Mean: {submission['target'].mean():.6f}\")\nprint(f\"  Std: {submission['target'].std():.6f}\")\nprint(f\"  Min: {submission['target'].min():.6f}\")\nprint(f\"  Max: {submission['target'].max():.6f}\")\n\nprint(f\"\\nRank statistics:\")\nprint(f\"  Min: {submission['rank'].min()}\")\nprint(f\"  Max: {submission['rank'].max()}\")\nprint(f\"  Unique ranks: {submission['rank'].nunique()}\")\n\n# Show top ranked stocks\nprint(\"\\nTop 10 ranked stocks (best predictions):\")\ntop_10 = submission.nsmallest(10, 'rank')[['S_INFO_WINDCODE', 'target', 'rank']]\nprint(top_10)\n\n# Show bottom ranked stocks\nprint(\"\\nBottom 10 ranked stocks (worst predictions):\")\nbottom_10 = submission.nlargest(10, 'rank')[['S_INFO_WINDCODE', 'target', 'rank']]\nprint(bottom_10)\n\nprint(\"\\nFirst 10 rows of submission:\")\nprint(submission.head(10))\n\n# Compare with training target distribution\nprint(\"\\nComparison with training data:\")\nprint(f\"  Training target mean: {dataset['target'].mean():.6f}\")\nprint(f\"  Submission target mean: {submission['target'].mean():.6f}\")\n\n# ## 10. Feature Importance (Optional)\n\nprint(\"\\n[FEATURE IMPORTANCE] Top features from Random Forest:\")\nrf_model = final_models['RandomForest']\nimportances = rf_model.feature_importances_\nfeature_importance = pd.DataFrame({\n    'feature': selected_features,\n    'importance': importances\n}).sort_values('importance', ascending=False)\n\nprint(\"\\nTop 15 most important features:\")\nfor idx, row in feature_importance.head(15).iterrows():\n    print(f\"  {row['feature']}: {row['importance']:.4f}\")\n\nprint(\"\\n[FINAL VALIDATION]\")\nprint(\"Required columns check:\")\nrequired_cols = ['S_INFO_WINDCODE', 'target', 'rank']\nfor col in required_cols:\n    if col in submission.columns:\n        print(f\"  ✅ {col}: present\")\n    else:\n        print(f\"  ❌ {col}: MISSING!\")\n\nprint(\"\\nData types:\")\nprint(submission.dtypes)\n\nprint(\"\\nSubmission shape:\", submission.shape)\nprint(\"Expected shape: (1800, 3)\")\n\n# Verify all ranks are unique and in correct range\nassert submission['rank'].min() == 1, \"Minimum rank should be 1\"\nassert submission['rank'].max() == 1800, \"Maximum rank should be 1800\"\nassert submission['rank'].nunique() == 1800, \"All ranks should be unique\"\nprint(\"✅ All rank validations passed!\")\n\nprint(\"\\n\" + \"=\" * 80)\nprint(\"COMPLETE! Submission saved to 'submission.csv'\")\nprint(\"=\" * 80)","metadata":{"trusted":true},"outputs":[],"execution_count":null}]}