{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.12.12","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":7391,"databundleVersionId":44328,"sourceType":"competition"}],"dockerImageVersionId":31234,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Corporación Favorita Grocery Sales Forecasting\n## Part 3: Feature Engineering (Winner's Solution)\n\nThis notebook implements feature engineering based on the **1st place solution**.\n\n**Winner's Key Insights:**\n- Use only 2017 data for training\n- Statistical features with different time windows are crucial\n- Holidays and some key combinations were NOT useful\n\n**Memory Optimization:**\n- Process data in chunks\n- Use efficient dtypes\n- Clear memory frequently\n- Only load necessary columns","metadata":{"_uuid":"cd12e7f4-df10-4dd3-b1fd-da1eb6ef9314","_cell_guid":"9f483b96-fe1c-4023-9faf-91d0f25138bd","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"markdown","source":"## 1. Setup & Configuration","metadata":{"_uuid":"6a9bea28-8802-44ab-b77e-8ab487ad94e0","_cell_guid":"fe51d973-7d36-4b45-9a4c-0cff0250a1b4","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"import os\nimport gc\nimport time\nimport warnings\nfrom pathlib import Path\nfrom datetime import datetime, timedelta\n\nimport numpy as np\nimport pandas as pd\nfrom tqdm.auto import tqdm\n\nwarnings.filterwarnings('ignore')\n\nprint(\"✓ Libraries imported\")","metadata":{"_uuid":"79a6b0bb-f989-4300-a64c-c164f7748d89","_cell_guid":"0161d612-94da-4e78-9b98-140b86a7f885","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"class Config:\n    \"\"\"Configuration based on winner's solution\"\"\"\n    \n    # Paths\n    INPUT_PATH = Path('/kaggle/input/favorita-grocery-sales-forecasting')\n    OUTPUT_PATH = Path('/kaggle/working')\n    \n    # Date ranges (Winner's approach: only 2017 data)\n    # Training windows\n    TRAIN_START_1 = '2017-05-31'  # Option 1: Longer training\n    TRAIN_START_2 = '2017-06-14'  # Option 2: Shorter training\n    TRAIN_END = '2017-07-19'\n    \n    # Validation window\n    VAL_START = '2017-07-26'\n    VAL_END = '2017-08-10'\n    \n    # Test period (competition requirement: 16 days)\n    TEST_START = '2017-08-16'\n    TEST_END = '2017-08-31'\n    \n    # Feature extraction - need historical data before train start\n    FEATURE_START = '2017-01-01'  # For 140-day lookback features\n    \n    # Time windows for statistical features (Winner's approach)\n    NEAREST_DAYS = [1, 3, 5, 7, 14, 30, 60, 140]\n    EQUAL_WINDOWS_1 = [1] * 16  # 16 windows of 1 day each\n    EQUAL_WINDOWS_7 = [7] * 20  # 20 windows of 7 days each\n    \n    # Memory settings\n    CHUNK_SIZE = 5_000_000\n    \n    RANDOM_STATE = 42\n\nConfig.OUTPUT_PATH.mkdir(parents=True, exist_ok=True)\nprint(f\"Feature extraction period: {Config.FEATURE_START} onwards\")\nprint(f\"Training period: {Config.TRAIN_START_1} to {Config.TRAIN_END}\")\nprint(f\"Validation period: {Config.VAL_START} to {Config.VAL_END}\")","metadata":{"_uuid":"ce1e4573-60b3-4162-b8ad-7f0e80a91ec6","_cell_guid":"58268e2b-53f3-4916-acaf-3a686f943c2f","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 2. Data Loading (Memory Efficient - 2017 Only)","metadata":{"_uuid":"22dd6ff0-3869-4d9c-9641-705fd9465ee4","_cell_guid":"8d8ac1e3-be82-4ca7-a571-b0bbe57f6e2a","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"def load_2017_train_data():\n    \"\"\"\n    Load only 2017 training data for memory efficiency.\n    Winner's insight: Only 2017 data is useful for this competition.\n    \"\"\"\n    print(\"\\n\" + \"=\"*60)\n    print(\"📊 LOADING 2017 TRAINING DATA\")\n    print(\"=\"*60)\n    \n    import subprocess\n    \n    # Get file path\n    train_path = Config.INPUT_PATH / 'train.csv'\n    if not train_path.exists():\n        archive_path = Config.INPUT_PATH / 'train.csv.7z'\n        if archive_path.exists():\n            print(\"Extracting train.csv.7z...\")\n            subprocess.run(['7z', 'x', str(archive_path), f'-o{Config.OUTPUT_PATH}', '-y'],\n                          capture_output=True)\n            train_path = Config.OUTPUT_PATH / 'train.csv'\n    \n    # Define dtypes for memory efficiency\n    dtypes = {\n        'id': 'int32',\n        'store_nbr': 'int8',\n        'item_nbr': 'int32',\n        'unit_sales': 'float32',\n        'onpromotion': 'object'\n    }\n    \n    print(f\"\\nLoading data from {Config.FEATURE_START} onwards...\")\n    start_time = time.time()\n    \n    # Load in chunks, filter to 2017 only\n    chunks = []\n    chunk_iter = pd.read_csv(\n        train_path,\n        dtype=dtypes,\n        parse_dates=['date'],\n        chunksize=Config.CHUNK_SIZE\n    )\n    \n    feature_start = pd.to_datetime(Config.FEATURE_START)\n    \n    for i, chunk in enumerate(tqdm(chunk_iter, desc=\"Loading chunks\")):\n        # Filter to 2017 data only\n        chunk = chunk[chunk['date'] >= feature_start]\n        \n        if len(chunk) > 0:\n            # Process onpromotion\n            chunk['onpromotion'] = chunk['onpromotion'].map(\n                {'True': 1, 'False': 0, True: 1, False: 0}\n            ).fillna(0).astype('int8')\n            \n            # Winner's preprocessing: fill negative unit_sales with 0\n            chunk['unit_sales'] = chunk['unit_sales'].clip(lower=0)\n            \n            chunks.append(chunk)\n        \n        # Memory management\n        if (i + 1) % 20 == 0:\n            gc.collect()\n    \n    print(\"\\nConcatenating chunks...\")\n    train = pd.concat(chunks, ignore_index=True)\n    del chunks\n    gc.collect()\n    \n    # Optimize dtypes\n    train['store_nbr'] = train['store_nbr'].astype('int8')\n    train['item_nbr'] = train['item_nbr'].astype('int32')\n    train['unit_sales'] = train['unit_sales'].astype('float32')\n    \n    elapsed = time.time() - start_time\n    print(f\"\\n✓ Loaded {len(train):,} rows in {elapsed:.1f}s\")\n    print(f\"  Date range: {train['date'].min()} to {train['date'].max()}\")\n    print(f\"  Memory: {train.memory_usage(deep=True).sum() / 1024**3:.2f} GB\")\n    \n    return train\n\n\ndef load_auxiliary_data():\n    \"\"\"Load stores, items, and other auxiliary data\"\"\"\n    print(\"\\n\" + \"=\"*60)\n    print(\"📊 LOADING AUXILIARY DATA\")\n    print(\"=\"*60)\n    \n    import subprocess\n    \n    def get_path(filename):\n        path = Config.INPUT_PATH / filename\n        if not path.exists():\n            archive = Config.INPUT_PATH / f\"{filename}.7z\"\n            if archive.exists():\n                subprocess.run(['7z', 'x', str(archive), f'-o{Config.OUTPUT_PATH}', '-y'],\n                              capture_output=True)\n                return Config.OUTPUT_PATH / filename\n        return path\n    \n    # Load stores\n    stores = pd.read_csv(get_path('stores.csv'))\n    stores['store_nbr'] = stores['store_nbr'].astype('int8')\n    stores['cluster'] = stores['cluster'].astype('int8')\n    print(f\"✓ Stores: {len(stores)} rows\")\n    \n    # Load items\n    items = pd.read_csv(get_path('items.csv'))\n    items['item_nbr'] = items['item_nbr'].astype('int32')\n    items['class'] = items['class'].astype('int16')\n    items['perishable'] = items['perishable'].astype('int8')\n    print(f\"✓ Items: {len(items)} rows\")\n    \n    # Load test data\n    test = pd.read_csv(get_path('test.csv'), parse_dates=['date'])\n    test['store_nbr'] = test['store_nbr'].astype('int8')\n    test['item_nbr'] = test['item_nbr'].astype('int32')\n    test['onpromotion'] = test['onpromotion'].map(\n        {'True': 1, 'False': 0, True: 1, False: 0}\n    ).fillna(0).astype('int8')\n    print(f\"✓ Test: {len(test)} rows\")\n    \n    return stores, items, test","metadata":{"_uuid":"0924ed88-d9ca-4a64-ac43-60f3c91fc4e1","_cell_guid":"aa8a9146-7b5e-4368-8d9c-73ae1b659c73","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Load data\ntrain = load_2017_train_data()\nstores, items, test = load_auxiliary_data()","metadata":{"_uuid":"89a62c59-5846-439f-b241-2569ce9042a0","_cell_guid":"52986470-76f2-4c60-b895-9a62741bcb1f","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 3. Create Base DataFrame for Features","metadata":{"_uuid":"507510a3-3e02-450c-9606-35b799afdcbe","_cell_guid":"13d91453-251b-4f6a-a11f-6fc4223253db","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"def create_base_dataframe(train, test, stores, items):\n    \"\"\"\n    Create base dataframe with category features.\n    Winner's basic features: store, item, family, class, cluster\n    \"\"\"\n    print(\"\\n\" + \"=\"*60)\n    print(\"🏗️ CREATING BASE DATAFRAME\")\n    print(\"=\"*60)\n    \n    # Get unique store-item combinations from train and test\n    print(\"Getting unique store-item combinations...\")\n    \n    train_pairs = train[['store_nbr', 'item_nbr']].drop_duplicates()\n    test_pairs = test[['store_nbr', 'item_nbr']].drop_duplicates()\n    all_pairs = pd.concat([train_pairs, test_pairs]).drop_duplicates()\n    \n    print(f\"  Train pairs: {len(train_pairs):,}\")\n    print(f\"  Test pairs: {len(test_pairs):,}\")\n    print(f\"  Unique pairs: {len(all_pairs):,}\")\n    \n    # Merge with store info\n    print(\"\\nMerging store information...\")\n    base = all_pairs.merge(stores[['store_nbr', 'type', 'cluster', 'state']], on='store_nbr')\n    \n    # Merge with item info\n    print(\"Merging item information...\")\n    base = base.merge(items[['item_nbr', 'family', 'class', 'perishable']], on='item_nbr')\n    \n    # Create encoded features\n    print(\"\\nEncoding categorical features...\")\n    \n    # Store type encoding\n    type_map = {'A': 0, 'B': 1, 'C': 2, 'D': 3, 'E': 4}\n    base['store_type'] = base['type'].map(type_map).astype('int8')\n    \n    # Family encoding\n    family_map = {f: i for i, f in enumerate(base['family'].unique())}\n    base['family_encoded'] = base['family'].map(family_map).astype('int8')\n    \n    # State encoding\n    state_map = {s: i for i, s in enumerate(base['state'].unique())}\n    base['state_encoded'] = base['state'].map(state_map).astype('int8')\n    \n    # Optimize dtypes\n    base['store_nbr'] = base['store_nbr'].astype('int8')\n    base['item_nbr'] = base['item_nbr'].astype('int32')\n    base['cluster'] = base['cluster'].astype('int8')\n    base['class'] = base['class'].astype('int16')\n    base['perishable'] = base['perishable'].astype('int8')\n    \n    print(f\"\\n✓ Base dataframe created: {len(base):,} store-item pairs\")\n    print(f\"  Columns: {list(base.columns)}\")\n    \n    return base, family_map, state_map","metadata":{"_uuid":"d9378b5f-7349-4b48-87dc-099278338611","_cell_guid":"a2f51586-f90b-403b-b4f4-cc4444ec8c0d","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"base_df, family_map, state_map = create_base_dataframe(train, test, stores, items)","metadata":{"_uuid":"520bc1ba-b477-4dbf-b058-f4cbfb79310d","_cell_guid":"863daaae-b1d0-4a41-af6e-fbd356df551b","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 4. Statistical Feature Functions (Winner's Approach)","metadata":{"_uuid":"5e29ad6f-2293-4351-a45a-f419adef23c7","_cell_guid":"7bdb101b-3408-4263-8105-31e97128ebe1","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"def compute_lag_features(df, group_cols, target_col, date_col, ref_date, windows):\n    \"\"\"\n    Compute statistical features for specified windows.\n    \n    Winner's approach:\n    - Windows: [1,3,5,7,14,30,60,140]\n    - Methods: mean, median, max, min, std\n    \n    Args:\n        df: DataFrame with historical data\n        group_cols: Columns to group by (e.g., ['store_nbr', 'item_nbr'])\n        target_col: Target column for statistics\n        date_col: Date column name\n        ref_date: Reference date for calculating lags\n        windows: List of window sizes in days\n    \n    Returns:\n        DataFrame with lag features\n    \"\"\"\n    ref_date = pd.to_datetime(ref_date)\n    group_key = '_'.join(group_cols)\n    \n    # Filter data before reference date\n    hist = df[df[date_col] < ref_date].copy()\n    \n    if len(hist) == 0:\n        return None\n    \n    # Calculate days before reference\n    hist['days_ago'] = (ref_date - hist[date_col]).dt.days\n    \n    features_list = []\n    \n    for window in windows:\n        # Filter to window\n        window_data = hist[hist['days_ago'] <= window]\n        \n        if len(window_data) == 0:\n            continue\n        \n        # Compute statistics\n        stats = window_data.groupby(group_cols)[target_col].agg([\n            'mean', 'std', 'min', 'max', 'median', 'count'\n        ]).reset_index()\n        \n        # Rename columns\n        stats.columns = group_cols + [\n            f'{group_key}_{target_col}_mean_{window}d',\n            f'{group_key}_{target_col}_std_{window}d',\n            f'{group_key}_{target_col}_min_{window}d',\n            f'{group_key}_{target_col}_max_{window}d',\n            f'{group_key}_{target_col}_median_{window}d',\n            f'{group_key}_{target_col}_count_{window}d'\n        ]\n        \n        features_list.append(stats)\n    \n    if not features_list:\n        return None\n    \n    # Merge all windows\n    result = features_list[0]\n    for stats in features_list[1:]:\n        result = result.merge(stats, on=group_cols, how='outer')\n    \n    return result\n\n\ndef compute_days_since_last(df, group_cols, date_col, ref_date):\n    \"\"\"\n    Compute days since last appearance for each group.\n    Winner's feature: days since last appearance\n    \"\"\"\n    ref_date = pd.to_datetime(ref_date)\n    hist = df[df[date_col] < ref_date].copy()\n    \n    if len(hist) == 0:\n        return None\n    \n    # Get last date for each group\n    last_dates = hist.groupby(group_cols)[date_col].max().reset_index()\n    last_dates['days_since_last'] = (ref_date - last_dates[date_col]).dt.days\n    \n    group_key = '_'.join(group_cols)\n    last_dates = last_dates.rename(columns={'days_since_last': f'{group_key}_days_since_last'})\n    \n    return last_dates[group_cols + [f'{group_key}_days_since_last']]\n\n\ndef compute_zero_ratio(df, group_cols, target_col, date_col, ref_date, window):\n    \"\"\"\n    Compute ratio of zero sales in a window.\n    Winner's target: zeros\n    \"\"\"\n    ref_date = pd.to_datetime(ref_date)\n    hist = df[df[date_col] < ref_date].copy()\n    hist['days_ago'] = (ref_date - hist[date_col]).dt.days\n    \n    window_data = hist[hist['days_ago'] <= window]\n    \n    if len(window_data) == 0:\n        return None\n    \n    # Compute zero ratio\n    zero_stats = window_data.groupby(group_cols).apply(\n        lambda x: (x[target_col] == 0).sum() / len(x)\n    ).reset_index()\n    \n    group_key = '_'.join(group_cols)\n    zero_stats.columns = group_cols + [f'{group_key}_zero_ratio_{window}d']\n    \n    return zero_stats","metadata":{"_uuid":"9c2adcfe-b8a8-4564-abbc-00318ed09d90","_cell_guid":"78c60af3-fc10-43a8-80b9-786c4d7badc9","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 5. Memory-Efficient Feature Extraction","metadata":{"_uuid":"9ae42acf-1a40-4a32-b985-9d490dc9d718","_cell_guid":"0bca9d0e-e716-4aa4-9bee-06bce96f4944","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"def extract_features_for_date(train, ref_date, base_df):\n    \"\"\"\n    Extract all features for a single reference date.\n    Memory-efficient: processes one date at a time.\n    \n    Winner's keys:\n    - store x item (primary)\n    - item only\n    - store x class\n    \"\"\"\n    ref_date = pd.to_datetime(ref_date)\n    \n    # Start with base features\n    features = base_df.copy()\n    \n    # Filter historical data (only what we need)\n    cutoff_date = ref_date - timedelta(days=150)  # Max window is 140 days + buffer\n    hist = train[(train['date'] >= cutoff_date) & (train['date'] < ref_date)].copy()\n    \n    if len(hist) == 0:\n        print(f\"  ⚠ No historical data for {ref_date}\")\n        return features\n    \n    # === KEY 1: store x item ===\n    print(f\"    Computing store_item features...\")\n    \n    # Lag features for unit_sales\n    for window in [1, 3, 5, 7, 14, 30, 60, 140]:\n        window_hist = hist[hist['date'] >= ref_date - timedelta(days=window)]\n        if len(window_hist) > 0:\n            stats = window_hist.groupby(['store_nbr', 'item_nbr'])['unit_sales'].agg([\n                'mean', 'std', 'median'\n            ]).reset_index()\n            stats.columns = ['store_nbr', 'item_nbr', \n                           f'si_sales_mean_{window}d',\n                           f'si_sales_std_{window}d', \n                           f'si_sales_median_{window}d']\n            features = features.merge(stats, on=['store_nbr', 'item_nbr'], how='left')\n        gc.collect()\n    \n    # Promotion features\n    for window in [7, 14, 30]:\n        window_hist = hist[hist['date'] >= ref_date - timedelta(days=window)]\n        if len(window_hist) > 0:\n            promo_stats = window_hist.groupby(['store_nbr', 'item_nbr'])['onpromotion'].agg(['mean', 'sum']).reset_index()\n            promo_stats.columns = ['store_nbr', 'item_nbr',\n                                  f'si_promo_mean_{window}d',\n                                  f'si_promo_sum_{window}d']\n            features = features.merge(promo_stats, on=['store_nbr', 'item_nbr'], how='left')\n    \n    # Days since last sale\n    last_sale = hist.groupby(['store_nbr', 'item_nbr'])['date'].max().reset_index()\n    last_sale['si_days_since_last'] = (ref_date - last_sale['date']).dt.days\n    features = features.merge(last_sale[['store_nbr', 'item_nbr', 'si_days_since_last']], \n                             on=['store_nbr', 'item_nbr'], how='left')\n    \n    # Zero sales ratio\n    for window in [7, 14, 30]:\n        window_hist = hist[hist['date'] >= ref_date - timedelta(days=window)]\n        if len(window_hist) > 0:\n            zero_stats = window_hist.groupby(['store_nbr', 'item_nbr']).apply(\n                lambda x: (x['unit_sales'] == 0).mean()\n            ).reset_index()\n            zero_stats.columns = ['store_nbr', 'item_nbr', f'si_zero_ratio_{window}d']\n            features = features.merge(zero_stats, on=['store_nbr', 'item_nbr'], how='left')\n    \n    gc.collect()\n    \n    # === KEY 2: item only ===\n    print(f\"    Computing item features...\")\n    \n    for window in [7, 14, 30, 60]:\n        window_hist = hist[hist['date'] >= ref_date - timedelta(days=window)]\n        if len(window_hist) > 0:\n            item_stats = window_hist.groupby('item_nbr')['unit_sales'].agg([\n                'mean', 'std', 'median'\n            ]).reset_index()\n            item_stats.columns = ['item_nbr',\n                                 f'item_sales_mean_{window}d',\n                                 f'item_sales_std_{window}d',\n                                 f'item_sales_median_{window}d']\n            features = features.merge(item_stats, on='item_nbr', how='left')\n    \n    gc.collect()\n    \n    # === KEY 3: store x class ===\n    print(f\"    Computing store_class features...\")\n    \n    # Need to add class to historical data\n    hist_with_class = hist.merge(items[['item_nbr', 'class']], on='item_nbr', how='left')\n    \n    for window in [7, 14, 30]:\n        window_hist = hist_with_class[hist_with_class['date'] >= ref_date - timedelta(days=window)]\n        if len(window_hist) > 0:\n            sc_stats = window_hist.groupby(['store_nbr', 'class'])['unit_sales'].agg([\n                'mean', 'median'\n            ]).reset_index()\n            sc_stats.columns = ['store_nbr', 'class',\n                               f'sc_sales_mean_{window}d',\n                               f'sc_sales_median_{window}d']\n            features = features.merge(sc_stats, on=['store_nbr', 'class'], how='left')\n    \n    del hist_with_class\n    gc.collect()\n    \n    return features","metadata":{"_uuid":"2ae447be-0434-41c0-af83-ddbc1caecdc7","_cell_guid":"c603df6e-8836-4452-8552-70799c231f2c","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"def create_training_features(train, test, base_df, date_range, include_target=True):\n    \"\"\"\n    Create features for a range of dates.\n    Memory-efficient batch processing.\n    \"\"\"\n    print(\"\\n\" + \"=\"*60)\n    print(f\"🔧 CREATING FEATURES FOR {len(date_range)} DATES\")\n    print(\"=\"*60)\n    \n    all_features = []\n    \n    for i, ref_date in enumerate(tqdm(date_range, desc=\"Processing dates\")):\n        print(f\"\\n  [{i+1}/{len(date_range)}] Processing {ref_date}...\")\n        \n        # Extract features\n        features = extract_features_for_date(train, ref_date, base_df)\n        features['date'] = ref_date\n        \n        # Add day of week (Winner: only for some models)\n        features['dayofweek'] = pd.to_datetime(ref_date).dayofweek\n        \n        # Add target if training data\n        if include_target:\n            day_data = train[train['date'] == ref_date][['store_nbr', 'item_nbr', 'unit_sales', 'onpromotion']]\n            features = features.merge(day_data, on=['store_nbr', 'item_nbr'], how='left')\n            features['unit_sales'] = features['unit_sales'].fillna(0)\n            features['onpromotion'] = features['onpromotion'].fillna(0)\n        else:\n            # For test data, get promotion from test\n            day_test = test[test['date'] == ref_date][['store_nbr', 'item_nbr', 'onpromotion', 'id']]\n            features = features.merge(day_test, on=['store_nbr', 'item_nbr'], how='inner')\n        \n        all_features.append(features)\n        \n        # Memory management\n        gc.collect()\n    \n    print(\"\\nCombining all dates...\")\n    result = pd.concat(all_features, ignore_index=True)\n    del all_features\n    gc.collect()\n    \n    print(f\"✓ Created features: {result.shape}\")\n    return result","metadata":{"_uuid":"c99aca5a-98e7-4589-a434-6464719c1227","_cell_guid":"1df22360-640d-48fe-8304-db3dbbed82c5","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 6. Difference Features (Winner's Equal Windows)","metadata":{"_uuid":"e3f7ec3f-c133-4275-9332-723e12bfdea3","_cell_guid":"f7d72d5e-5d3e-4538-9760-9a64d8630d75","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"def add_difference_features(df):\n    \"\"\"\n    Add difference features between adjacent time windows.\n    Winner's approach: difference of mean value between adjacent time windows\n    \"\"\"\n    print(\"\\n  Adding difference features...\")\n    \n    # Differences for store_item sales\n    if 'si_sales_mean_7d' in df.columns and 'si_sales_mean_14d' in df.columns:\n        df['si_sales_diff_7_14'] = df['si_sales_mean_7d'] - df['si_sales_mean_14d']\n    \n    if 'si_sales_mean_14d' in df.columns and 'si_sales_mean_30d' in df.columns:\n        df['si_sales_diff_14_30'] = df['si_sales_mean_14d'] - df['si_sales_mean_30d']\n    \n    if 'si_sales_mean_30d' in df.columns and 'si_sales_mean_60d' in df.columns:\n        df['si_sales_diff_30_60'] = df['si_sales_mean_30d'] - df['si_sales_mean_60d']\n    \n    # Differences for item sales\n    if 'item_sales_mean_7d' in df.columns and 'item_sales_mean_14d' in df.columns:\n        df['item_sales_diff_7_14'] = df['item_sales_mean_7d'] - df['item_sales_mean_14d']\n    \n    if 'item_sales_mean_14d' in df.columns and 'item_sales_mean_30d' in df.columns:\n        df['item_sales_diff_14_30'] = df['item_sales_mean_14d'] - df['item_sales_mean_30d']\n    \n    # Ratio features (relative change)\n    if 'si_sales_mean_7d' in df.columns and 'si_sales_mean_30d' in df.columns:\n        df['si_sales_ratio_7_30'] = df['si_sales_mean_7d'] / (df['si_sales_mean_30d'] + 1)\n    \n    return df","metadata":{"_uuid":"5e2ea0ad-bd96-4ea7-bb42-822a32c19aa4","_cell_guid":"1ee2af83-73a7-401e-b0b4-f84ce1ffe24e","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 7. Execute Feature Engineering Pipeline","metadata":{"_uuid":"2af82fba-5c02-43d8-9462-fc5f0b426b46","_cell_guid":"851e2197-8aa1-40f6-b2cc-7bfbb04c6e6b","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"# Define date ranges based on winner's solution\ntrain_dates_1 = pd.date_range(Config.TRAIN_START_1, Config.TRAIN_END, freq='D')\ntrain_dates_2 = pd.date_range(Config.TRAIN_START_2, Config.TRAIN_END, freq='D')\nval_dates = pd.date_range(Config.VAL_START, Config.VAL_END, freq='D')\ntest_dates = pd.date_range(Config.TEST_START, Config.TEST_END, freq='D')\n\nprint(\"Date ranges:\")\nprint(f\"  Train Option 1: {train_dates_1[0].date()} to {train_dates_1[-1].date()} ({len(train_dates_1)} days)\")\nprint(f\"  Train Option 2: {train_dates_2[0].date()} to {train_dates_2[-1].date()} ({len(train_dates_2)} days)\")\nprint(f\"  Validation: {val_dates[0].date()} to {val_dates[-1].date()} ({len(val_dates)} days)\")\nprint(f\"  Test: {test_dates[0].date()} to {test_dates[-1].date()} ({len(test_dates)} days)\")","metadata":{"_uuid":"4c95bbe2-f1c5-4f75-8c99-09a00f4c1d43","_cell_guid":"44f73e5f-cfc3-447b-bd36-c23daece69ae","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Create training features (using shorter training window for memory efficiency)\n# You can switch to train_dates_1 for the longer window if memory allows\nprint(\"\\n\" + \"=\"*70)\nprint(\"🏋️ CREATING TRAINING FEATURES\")\nprint(\"=\"*70)\n\ntrain_features = create_training_features(\n    train, test, base_df, \n    train_dates_2,  # Using shorter window (Option 2)\n    include_target=True\n)\n\n# Add difference features\ntrain_features = add_difference_features(train_features)\n\nprint(f\"\\n✓ Training features shape: {train_features.shape}\")\nprint(f\"  Memory: {train_features.memory_usage(deep=True).sum() / 1024**3:.2f} GB\")","metadata":{"_uuid":"5b48f18b-d66d-4b67-948d-a0545550b6f5","_cell_guid":"f31ef596-7832-4cb0-a149-c72ce86db772","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Create validation features\nprint(\"\\n\" + \"=\"*70)\nprint(\"🔍 CREATING VALIDATION FEATURES\")\nprint(\"=\"*70)\n\nval_features = create_training_features(\n    train, test, base_df,\n    val_dates,\n    include_target=True\n)\n\nval_features = add_difference_features(val_features)\n\nprint(f\"\\n✓ Validation features shape: {val_features.shape}\")","metadata":{"_uuid":"c7e543a9-35bd-479d-b26b-1849513b5075","_cell_guid":"b505aabd-4114-42b7-88c6-12eaa4bf3328","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Free up memory before test features\ngc.collect()\n\n# Create test features\nprint(\"\\n\" + \"=\"*70)\nprint(\"🧪 CREATING TEST FEATURES\")\nprint(\"=\"*70)\n\ntest_features = create_training_features(\n    train, test, base_df,\n    test_dates,\n    include_target=False\n)\n\ntest_features = add_difference_features(test_features)\n\nprint(f\"\\n✓ Test features shape: {test_features.shape}\")","metadata":{"_uuid":"e50d1a81-7a66-4e76-85e3-2cfc74a4917a","_cell_guid":"0b748047-bf92-420d-9f5f-9b16e9d34b1f","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 8. Fill Missing Values & Final Processing","metadata":{"_uuid":"e9f4fa82-3ba8-4ab5-bc3d-98c2068ba796","_cell_guid":"4141b74d-65f4-4ae7-93cd-1e8a6337c275","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"def fill_missing_values(df, is_train=True):\n    \"\"\"\n    Fill missing values.\n    Winner's approach: fill missing with 0\n    \"\"\"\n    print(\"\\nFilling missing values...\")\n    \n    # Get numeric columns\n    numeric_cols = df.select_dtypes(include=[np.number]).columns.tolist()\n    \n    # Remove target from fill if present\n    if 'unit_sales' in numeric_cols and is_train:\n        numeric_cols.remove('unit_sales')\n    \n    # Fill with 0 (Winner's approach)\n    for col in numeric_cols:\n        if df[col].isna().sum() > 0:\n            df[col] = df[col].fillna(0)\n    \n    return df\n\n\ndef get_feature_columns(df):\n    \"\"\"Get list of feature columns (exclude metadata)\"\"\"\n    exclude_cols = ['id', 'date', 'store_nbr', 'item_nbr', 'unit_sales', \n                   'type', 'state', 'family']\n    \n    feature_cols = [c for c in df.columns if c not in exclude_cols]\n    return feature_cols","metadata":{"_uuid":"9450714b-0148-4c3c-8c06-bc6d63364bc6","_cell_guid":"c37050e3-86cf-4700-a5c1-7175055dbd69","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Fill missing values\ntrain_features = fill_missing_values(train_features, is_train=True)\nval_features = fill_missing_values(val_features, is_train=True)\ntest_features = fill_missing_values(test_features, is_train=False)\n\n# Get feature columns\nfeature_cols = get_feature_columns(train_features)\nprint(f\"\\n✓ Number of features: {len(feature_cols)}\")\nprint(f\"\\nFeature columns:\")\nfor i, col in enumerate(feature_cols):\n    print(f\"  {i+1:2d}. {col}\")","metadata":{"_uuid":"8be6898a-4389-4cbd-86b9-190e5f65d9f5","_cell_guid":"485e8e33-2dd9-4590-9078-da4bd6327d6c","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 9. Save Processed Data","metadata":{"_uuid":"b51d75a6-3ad5-4c50-9c79-3e24f7e5a15d","_cell_guid":"2b64fb35-849b-43c1-9674-b2e6ed61b4d0","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"def save_features(df, filename):\n    \"\"\"Save features to parquet file\"\"\"\n    filepath = Config.OUTPUT_PATH / filename\n    df.to_parquet(filepath, index=False, compression='snappy')\n    size_mb = filepath.stat().st_size / 1024**2\n    print(f\"✓ Saved {filename} ({size_mb:.1f} MB)\")\n    return filepath","metadata":{"_uuid":"400626e5-0756-451f-8d7b-a7a7ff70036d","_cell_guid":"3ac2827a-fca6-4b9f-9489-f4d1f0e7541b","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Save all datasets\nprint(\"\\n\" + \"=\"*60)\nprint(\"💾 SAVING FEATURE DATASETS\")\nprint(\"=\"*60)\n\nsave_features(train_features, 'train_features.parquet')\nsave_features(val_features, 'val_features.parquet')\nsave_features(test_features, 'test_features.parquet')\n\n# Save feature column list\nfeature_info = {\n    'feature_cols': feature_cols,\n    'family_map': family_map,\n    'state_map': state_map\n}\n\nimport pickle\nwith open(Config.OUTPUT_PATH / 'feature_info.pkl', 'wb') as f:\n    pickle.dump(feature_info, f)\nprint(\"✓ Saved feature_info.pkl\")","metadata":{"_uuid":"d171f222-6a6c-4c0a-ae88-830136d8a2ae","_cell_guid":"0333a0e2-6b44-4f48-a787-e222e150d28f","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 10. Feature Summary","metadata":{"_uuid":"58412f8f-63c8-45e8-ab66-813efb901581","_cell_guid":"3fc08b3e-242d-4a76-b94d-d17db1a430b9","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"print(\"\\n\" + \"=\"*70)\nprint(\"📋 FEATURE ENGINEERING SUMMARY\")\nprint(\"=\"*70)\n\nprint(f\"\"\"\nDataset Sizes:\n  Training:   {train_features.shape[0]:,} rows × {train_features.shape[1]} columns\n  Validation: {val_features.shape[0]:,} rows × {val_features.shape[1]} columns\n  Test:       {test_features.shape[0]:,} rows × {test_features.shape[1]} columns\n\nFeature Categories (based on Winner's solution):\n  \n  1. BASIC FEATURES:\n     - store_nbr, item_nbr (IDs)\n     - store_type, cluster, state_encoded\n     - family_encoded, class, perishable\n     - dayofweek\n\n  2. STORE × ITEM FEATURES (primary key):\n     - Sales statistics: mean, std, median for [1,3,5,7,14,30,60,140] days\n     - Promotion statistics: mean, sum for [7,14,30] days\n     - Days since last sale\n     - Zero sales ratio for [7,14,30] days\n\n  3. ITEM FEATURES:\n     - Sales statistics: mean, std, median for [7,14,30,60] days\n\n  4. STORE × CLASS FEATURES:\n     - Sales statistics: mean, median for [7,14,30] days\n\n  5. DIFFERENCE FEATURES:\n     - Sales differences between adjacent windows (7-14, 14-30, 30-60)\n     - Sales ratio (7d / 30d)\n\n  Winner's Notes:\n  ✗ Holidays - NOT useful\n  ✗ cluster × item, store × family - NOT useful\n  ✓ Only 2017 data used\n  ✓ Gap between public/private LB smaller with 16-day model\n\"\"\")","metadata":{"_uuid":"b2d6181c-f770-436b-bcb6-5659e14d6e78","_cell_guid":"360a0ce8-630c-4378-8860-9c8b80bc5e1e","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Display sample features\nprint(\"\\n--- Sample Training Features ---\")\nprint(train_features.head())\n\nprint(\"\\n--- Feature Statistics ---\")\nprint(train_features[feature_cols[:10]].describe())","metadata":{"_uuid":"6ae9a3ed-752f-409e-8d60-0a946e6c2735","_cell_guid":"cc7d5afc-8543-44d6-b04b-5eaa56289984","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"## 11. Clean Up","metadata":{"_uuid":"5617a3b1-ef53-48bd-b5dc-c2e4d2629854","_cell_guid":"e3ec520b-eda9-436a-a6fe-27a076c08d33","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}},{"cell_type":"code","source":"# Clean up to free memory for model training\ndel train\ngc.collect()\n\nprint(\"\\n✓ Memory cleaned. Ready for model training!\")\nprint(f\"\\nSaved files in {Config.OUTPUT_PATH}:\")\nfor f in Config.OUTPUT_PATH.glob('*.parquet'):\n    print(f\"  • {f.name}\")\nfor f in Config.OUTPUT_PATH.glob('*.pkl'):\n    print(f\"  • {f.name}\")","metadata":{"_uuid":"14de3758-e65d-48a4-8a4c-a1f1322c633a","_cell_guid":"deafe510-100c-4c49-b918-7f0bf0e1187f","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"---\n## Next Steps\n\n1. **Model Training**: Use LightGBM/XGBoost with the created features\n2. **Multiple Models**: Train different models with different date ranges\n3. **Ensemble**: Combine predictions from multiple models\n4. **Evaluation**: Use NWRMSLE metric","metadata":{"_uuid":"a662ca17-bc15-44c4-9b8e-e7d70f1db9fd","_cell_guid":"cffe93c6-44c9-4926-9013-423885787da3","trusted":true,"collapsed":false,"jupyter":{"outputs_hidden":false}}}]}