新品特征工程.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 771,
   "id": "28dc652c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd          \n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from datetime import datetime    \n",
    "from pandas import Series \n",
    "from sklearn.metrics import mean_squared_error\n",
    "from math import sqrt\n",
    "from statsmodels.tsa.seasonal import seasonal_decompose\n",
    "import statsmodels\n",
    "import statsmodels.api as sm\n",
    "from statsmodels.tsa.arima_model import ARIMA\n",
    "%matplotlib inline\n",
    "plt.rcParams['font.sans-serif'] = [\"SimHei\"]\n",
    "plt.rcParams[\"axes.unicode_minus\"] = False\n",
    "import seaborn as sns\n",
    "sns.set(style=\"darkgrid\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 772,
   "id": "a024efdf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 一些全局变量 \n",
    "is_need_log = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 773,
   "id": "d7a77895",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data = pd.read_csv(\"./示例数据/order_train2.csv\")\n",
    "test  = pd.read_csv(\"./示例数据/predict_sku1.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 774,
   "id": "97522bcb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_date</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>sales_chan_name</th>\n",
       "      <th>item_price</th>\n",
       "      <th>ord_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015/9/1</td>\n",
       "      <td>104</td>\n",
       "      <td>22069</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>offline</td>\n",
       "      <td>1114.0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015/9/1</td>\n",
       "      <td>104</td>\n",
       "      <td>20028</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>offline</td>\n",
       "      <td>1012.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015/9/2</td>\n",
       "      <td>105</td>\n",
       "      <td>21183</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>online</td>\n",
       "      <td>428.0</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015/9/2</td>\n",
       "      <td>105</td>\n",
       "      <td>20448</td>\n",
       "      <td>308</td>\n",
       "      <td>404</td>\n",
       "      <td>online</td>\n",
       "      <td>962.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015/9/2</td>\n",
       "      <td>104</td>\n",
       "      <td>21565</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>offline</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_date  sales_region_code  item_code  first_cate_code  second_cate_code  \\\n",
       "0   2015/9/1                104      22069              307               403   \n",
       "1   2015/9/1                104      20028              301               405   \n",
       "2   2015/9/2                105      21183              307               403   \n",
       "3   2015/9/2                105      20448              308               404   \n",
       "4   2015/9/2                104      21565              307               403   \n",
       "\n",
       "  sales_chan_name  item_price  ord_qty  \n",
       "0         offline      1114.0       19  \n",
       "1         offline      1012.0       12  \n",
       "2          online       428.0      109  \n",
       "3          online       962.0        3  \n",
       "4         offline      1400.0        3  "
      ]
     },
     "execution_count": 774,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 775,
   "id": "c9c4a9d0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 597694 entries, 0 to 597693\n",
      "Data columns (total 8 columns):\n",
      " #   Column             Non-Null Count   Dtype  \n",
      "---  ------             --------------   -----  \n",
      " 0   order_date         597694 non-null  object \n",
      " 1   sales_region_code  597694 non-null  int64  \n",
      " 2   item_code          597694 non-null  int64  \n",
      " 3   first_cate_code    597694 non-null  int64  \n",
      " 4   second_cate_code   597694 non-null  int64  \n",
      " 5   sales_chan_name    597694 non-null  object \n",
      " 6   item_price         597694 non-null  float64\n",
      " 7   ord_qty            597694 non-null  int64  \n",
      "dtypes: float64(1), int64(5), object(2)\n",
      "memory usage: 36.5+ MB\n"
     ]
    }
   ],
   "source": [
    "sales_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 776,
   "id": "89449d5e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>item_price</th>\n",
       "      <th>ord_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>597694.000000</td>\n",
       "      <td>597694.000000</td>\n",
       "      <td>597694.000000</td>\n",
       "      <td>597694.000000</td>\n",
       "      <td>597694.000000</td>\n",
       "      <td>597694.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>102.941766</td>\n",
       "      <td>21008.015983</td>\n",
       "      <td>304.932094</td>\n",
       "      <td>406.457174</td>\n",
       "      <td>1076.241578</td>\n",
       "      <td>91.650505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.544004</td>\n",
       "      <td>590.896347</td>\n",
       "      <td>2.004195</td>\n",
       "      <td>3.107329</td>\n",
       "      <td>1167.511104</td>\n",
       "      <td>199.843336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>101.000000</td>\n",
       "      <td>20001.000000</td>\n",
       "      <td>301.000000</td>\n",
       "      <td>401.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>102.000000</td>\n",
       "      <td>20518.000000</td>\n",
       "      <td>303.000000</td>\n",
       "      <td>404.000000</td>\n",
       "      <td>598.000000</td>\n",
       "      <td>10.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>103.000000</td>\n",
       "      <td>20976.000000</td>\n",
       "      <td>306.000000</td>\n",
       "      <td>407.000000</td>\n",
       "      <td>883.000000</td>\n",
       "      <td>29.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>105.000000</td>\n",
       "      <td>21521.000000</td>\n",
       "      <td>306.000000</td>\n",
       "      <td>408.000000</td>\n",
       "      <td>1291.000000</td>\n",
       "      <td>101.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>105.000000</td>\n",
       "      <td>22084.000000</td>\n",
       "      <td>308.000000</td>\n",
       "      <td>412.000000</td>\n",
       "      <td>260014.000000</td>\n",
       "      <td>16308.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       sales_region_code      item_code  first_cate_code  second_cate_code  \\\n",
       "count      597694.000000  597694.000000    597694.000000     597694.000000   \n",
       "mean          102.941766   21008.015983       304.932094        406.457174   \n",
       "std             1.544004     590.896347         2.004195          3.107329   \n",
       "min           101.000000   20001.000000       301.000000        401.000000   \n",
       "25%           102.000000   20518.000000       303.000000        404.000000   \n",
       "50%           103.000000   20976.000000       306.000000        407.000000   \n",
       "75%           105.000000   21521.000000       306.000000        408.000000   \n",
       "max           105.000000   22084.000000       308.000000        412.000000   \n",
       "\n",
       "          item_price        ord_qty  \n",
       "count  597694.000000  597694.000000  \n",
       "mean     1076.241578      91.650505  \n",
       "std      1167.511104     199.843336  \n",
       "min         1.000000       1.000000  \n",
       "25%       598.000000      10.000000  \n",
       "50%       883.000000      29.000000  \n",
       "75%      1291.000000     101.000000  \n",
       "max    260014.000000   16308.000000  "
      ]
     },
     "execution_count": 776,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7bfdd6d",
   "metadata": {},
   "source": [
    "# 1. Data cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d71fa102",
   "metadata": {},
   "source": [
    "## Remove outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 777,
   "id": "1423d1df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot: xlabel='item_price'>"
      ]
     },
     "execution_count": 777,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAksAAAEJCAYAAACExWP4AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjYuMCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy89olMNAAAACXBIWXMAAAsTAAALEwEAmpwYAAAYw0lEQVR4nO3de3DU5b3H8U+STQghYgyXapGLcKw46qAHZkRABJ1wyYUYLk2ENtp0KnYyPXgZ5XbwgoNVaGt1pC1VB+fUtAkkBFAy1NozgBKsohRtx1KOJgYIEEKCkHs2+5w/cNdsdvfJhVw27PvFdDa/3+/5Pfs839+zux83u02YMcYIAAAAfoX39QAAAACCGWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWjvYaVFfXyuUyGjIkVmfP1vTGmPoV6uKLmviiJv5RF1/UxD/q4ouaeAsPD9NVVw3qkb7bDUsul5HLZTw/wxd18UVNfFET/6iLL2riH3XxRU16B7+GAwAAsCAsAQAAWBCWAAAALAhLAAAAFoQlAAAAC8ISAACABWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIAFYQkAAMCCsAQAAGBBWAIAALAgLAEAAFg4OtrwySef1MmTp3TllXEaOXK0Fi/O7MlxAQAABIUOh6UjR46orq5epyoqe3I8AAAAQaVzv4YLdygiOq5nRgIAABCE+MwSAACABWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIAFYQkAAMCCsAQAAGBBWAIAALAgLAEAAFgQlgAAACwISwAAABaEJQAAAAvCEgAAgAVhCQAAwIKwBAAAYEFYAgAAsCAsAQAAWBCWAAAALAhLAAAAFoQlAAAAC8ISAACABWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIAFYQkAAMCCsAQAAGBBWAIAALAgLAEAAFgQlgAAACwISwAAABaEJQAAAIsOhaX9+/fJ6XQGPLZ//75uHRQAAECw6FBYev/9vQHD0vvv79X77+/t1kEBAAAEC34NBwAAYEFYAgAAsCAsAQAAWBCWAAAALAhLAAAAFoQlAAAAC8ISAACABWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIAFYQkAAMCCsAQAAGBBWAIAALAgLAEAAFgQlgAAACwISwAAABaEJQAAAAvCEgAAgAVhCQAAwIKwBAAAYEFYAgAAsCAsAQAAWBCWAAAALAhLAAAAFoQlAAAAC8ISAACABWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIAFYQkAAMDCcSknDx8+WAM0QI1q1Cu/fFmNavRsR3zzr0lNilKUWr7513a/Sy455VSUotSkpoDHB2iAmtSkSEWqSU2e+3HfRilKTjnlkMPvcYccnn7c7ZvV7Okv0Pnh3+TJSEV6+mtWs1xy+dxH677d8217zDY/d7smNcnISJJP+2Y1y8j4be9vzuEK97kGrcfpvv9BjkFqcDZ47tN9jr/b1vcz9oZxaqxtVOmJEl0ZE6fztV97zvc3nrBv/jWpSdHh0Wp0NWrYVcPU3ORUdW2VIhShyPBINboadd21Y/X46lX68MMPlPc/OaprqVNMRIzSM5fohRd+qeXLH/Psbz2nuKvi9MxzP9eCBYskSQUFW/XM6v/WqaqTilKUBg6K0YCoKFWeq9SYEdfp8dWrPG2DSUHBVm1Y95xKT5T0+jgXLUzVB/uKPddv8vQp2pq/o8v9dXYura9t62ve3rGu3FewaG9eCA5cp9DU5bA0fPhgxSpWLrkUq1gt0AKN0iiVqUy5ylWUojRf8z37tmu7rtf1Oqqjulf3eu1vVrMmaqIO6qAiFak0pXkdv1t3a7AGq0AFuk23aYZmqExlXtt7tEeHdEhX6Apd0AWv8RSoQPWqV5rSPP045FCzmrVQC73ate4/X/mqV71iFOPTX5OalKEMjdIoz323buMed6UqPcfO67z+V//rNf9CFcoppxZpkVf/TjllZBStaK/2/mqbr3w55fSMp3Uf6Ur37NuiLYHr6xysXOVqgAZ4HS9UoSZogg7rsCZogg7pkHfNj1yc2xIt1p5a3zrs0A7dolt0WIc9tfTMx1WmbdqmC9UX9H19/9v6uL45frxMj2Rny+FyfFufljJt3fwnfVC8X8eOlGmiJuozfaZUpX47p+rteuK/Hvas1VXLHte8ppRvj9du14XaC0pVqgYfH6wnH10hSUH1glpQsFVPPrpCifWJWqLFKjte1mvjXLQwVR/v+8h7Pe0r0KKFqV0KTLa5PPRQlk/75csf09bNf/K55m6Bjr3wwi/7tG6XwjZnXoiDB9cpdIUZY4ytwdmzNVqx4lGdOnVSCotQWGSMoiNatHPrDmUoQ7nKVYYydJ2u85zzkl7SPM3z2leiEuUpT+lK99m/Uzs97/z4O69IRcpWttfPbY9t1EYlKtHveEpUolzlarAGe/oJ1K5t/4Ha5SlPK3TxSdh93/76kuQ5FqjdTu3UMi3zGW+MYnzqEai2/vpouy/Que45244nKtHrtnXN3e1tdUhUovX6L9Myv+c/r+f9nuO+Lu6+/fV51bXxkqQ7j08LuObc6+G9a9/XB58cUk8ZNuwKnTlzocPtJ//nbX7H3dPjlKSRw4cFfAwdqzjT6f5sc/ni2P/51GXMNVdrUcsin/ZbI7ZKUsBjpSdP9WndLoVtzqUnT/XhyIJHZx9DPSHYrlMw1CSYhIeHaciQ2J7pu6snNqpRozTKc9vaOZ3z2TdKo9SgBr/7z+mcKlUZ8LxKVfr83Ha7UpUBx+Pe37qfQO3a9h+oXYMaPNvu+/bXV+tjgdqd0zm/4/VXj0A18tdH233t1dd2vO2tv/nY6tDe9Q90fqBz3NfFVtPSEyUqPVFiXXPu7dITJQomgcbdG+O0PYa6orNzqWup89u+rqXOeqwr9xUs2psXggPXKXR1KCxdeWWcwsPDpbBwhTuiNWrUaA3QAJWpzHPbWpzifPaVqUzRiva7P05xGqqhAc8bqqE+P7fdHqqhAcfj3t+6n0Dt2vYfqF20oj3b7vv211frY4HaxSnO73j91SNQjfz10XZfe/W1HW97628+tjq0d/0DnR/oHPd1sdV0zIjrNGbEddY1594eM+I6BZNA4+6NcdoeQ13R2bnERMT4bR8TEWM91pX7ChbtzQvBgesUui7pnaUCFShCESpQgUpUoha1qEQlqlOdtmmb177t2q6bdbO2a7vP/kY1arzGq0ENKlShz/FpmqYSlahABRqv8Z5jrbfHa7wKVKB4xfuMx/35ndb9DNRA5Svfp13r/vOVr2Y1++3PJZdnn/u+/Y279bFpmuYz/0IVqlGNPv2HKUx1qvNp76+2+cpXnep8+qhXvde+9upbpzqf44Uq1HiN99z6q7mtDju0w3N+i1p85rNN29SghoD1Ubj81n/sDeM8Y9mhHT5zckY69fjqVXp89SrtjHrL53iDGjzzLhpYpMdXr+rOx9Ule3z1KhUNLPIad2+Nc/L0KX5rPnn6lC7119m5pGcu8Xv/6ZlLrMe6cl/Bor15IThwnUJXhz6z9POfr9XRo0fkUoQiouP0HyOHavnyNV7fhmvvW198G45vw4Xyt+G68tmCUPg2XKC68G04vmXVVrB8PieYrlOw1CRY9ORnli4pLEnSCy88K0me7VDDYvVFTXxRE/+oiy9q4h918UVNvAXlB7wBAABCAWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIAFYQkAAMCCsAQAAGBBWAIAALAgLAEAAFgQlgAAACwISwAAABaEJQAAAAvCEgAAgAVhCQAAwIKwBAAAYEFYAgAAsCAsAQAAWBCWAAAALAhLAAAAFoQlAAAAC8ISAACABWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIAFYQkAAMCCsAQAAGBBWAIAALAgLAEAAFgQlgAAACwcHWk0bdpdKin5Qk1O4/cYAADA5apD7yxNnTpdDof/XDV16nRNnTq9WwcFAAAQLPg1HAAAgAVhCQAAwIKwBAAAYEFYAgAAsCAsAQAAWBCWAAAALAhLAAAAFoQlAAAAC8ISAACABWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIAFYQkAAMCCsAQAAGBBWAIAALAgLAEAAFgQlgAAACwISwAAABaEJQAAAAvCEgAAgAVhCQAAwIKwBAAAYEFYAgAAsCAsAQAAWBCWAAAALAhLAAAAFoQlAAAAC8ISAACABWEJAADAgrAEAABgQVgCAACwICwBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIBF58KSy6mWhnM9MxIAAIAg5OhowxtuuEEnT57SlVfGaeTI0T05JgAAgKDR4bC0du1anTlzoSfHAgAAEHT4zBIAAIAFYQkAAMCCsAQAAGBBWAIAALAgLAEAAFgQlgAAACwISwAAABaEJQAAAAvCEgAAgAVhCQAAwIKwBAAAYEFYAgAAsCAsAQAAWBCWAAAALAhLAAAAFoQlAAAAC8ISAACABWEJAADAwtFeg/DwML8/41vUxRc18UVN/KMuvqiJf9TFFzX5Vk/WIswYY3qsdwAAgH6OX8MBAABYEJYAAAAsCEsAAAAWhCUAAAALwhIAAIAFYQkAAMCCsAQAAGBBWAIAALAgLAEAAFh0KCy99dZbSkxMVEJCgnJycnp6TH3ulVdeUVJSkpKSkrR+/XpJ0sqVKzVr1iylpqYqNTVVf/nLXyRJxcXFSklJ0axZs/Tiiy96+vj888+1YMECzZ49W6tXr5bT6eyTuXSXzMxMJSUleeZ/+PDhgOsiVGqydetWTz1SU1M1ceJErV27NmTXSk1NjZKTk3X8+HFJnZ9veXm5lixZojlz5uinP/2pamtrJUnnz5/Xgw8+qLlz52rJkiU6c+ZM70+ui9rWJC8vT8nJyUpJSdHKlSvV1NQk6eJzzsyZMz1rxv146myt+ou2demux0x/rkvrmuzdu9fruWXy5MlaunSppNBZK/5eh/v0OcW049SpU2bmzJmmurra1NbWmpSUFHP06NH2Tuu39u/fb9LT001jY6NpamoymZmZ5p133jHJycnm9OnTXm3r6+vNXXfdZcrKykxzc7PJysoye/bsMcYYk5SUZA4dOmSMMWblypUmJyent6fSbVwul5k6dappbm727Au0LkKlJm39+9//NgkJCebs2bMhuVb+/ve/m+TkZHPTTTeZY8eOdWm+Dz74oHn77beNMca88sorZv369cYYY5555hmzadMmY4wxhYWFZtmyZb07uS5qW5Mvv/zSJCQkmAsXLhiXy2WeeOIJs3nzZmOMMUuXLjWffPKJTx+drVV/0LYuxphue8z017r4q4lbRUWFueeee0xJSYkxJjTWir/X4bfeeqtPn1PafWepuLhYkydPVlxcnGJiYjR79mzt3r27K0GxXxg2bJhWrFihqKgoRUZGaty4cSovL1d5ebnWrFmjlJQUvfzyy3K5XPr00081evRojRw5Ug6HQykpKdq9e7dOnDihhoYG3XrrrZKk+fPn9+uaffnllwoLC9NPfvITzZs3T2+++WbAdREqNWnr6aef1iOPPKLo6OiQXCtbtmzRU089peHDh0tSp+fb3Nysjz76SLNnz/baL0l79uxRSkqKJCk5OVn79u1Tc3Nz70+yk9rWJCoqSk8//bRiY2MVFham733veyovL5ck/eMf/9Crr76qlJQUrV27Vo2NjV2qVX/Qti51dXXd8pjpz3VpW5PW1q9fr4yMDI0ZM0ZSaKwVf6/DpaWlffqc0m5Yqqio0LBhwzzbw4cP1+nTp7tUgP7g+uuv9xS9tLRURUVFuvPOOzV58mQ999xz2rJliw4ePKj8/PyAtWm7f9iwYf26ZufPn9cdd9yhjRs36o033lBubq7Ky8s7NPfLtSatFRcXq6GhQXPnztXZs2dDcq2sW7dOkyZN8mx3dr7V1dWKjY2Vw+Hw2t+2L4fDodjYWFVVVfXGtC5J25qMGDFCU6ZMkSRVVVUpJydH99xzj2pra3XjjTdq+fLlKiws1Pnz5/Wb3/ymS7XqD9rWpbseM/25Lm1r4lZaWqoPP/xQmZmZkhQya8Xf63BYWFifPqe0G5aMMT77wsLC2jut3zt69KiysrK0fPlyjR07Vhs3btSQIUM0cOBA/fCHP9TevXsD1uZyq9ltt92m9evXKyYmRvHx8Vq4cKFefvlln3a2uV9uNWktNzdXP/rRjyRJI0eODOm14tbZ+Xa2DuHh/fe7KadPn9b999+vBQsW6Pbbb9egQYP06quvavTo0XI4HMrKygqpNdNdj5nLrS7Sxc+4LV68WFFRUZIUcmul9evwqFGjfI735nNKu8843/nOd1RZWenZrqio8PtW4eXk448/1gMPPKDHHntMaWlpOnLkiP785z97jhtj5HA4Atam7f4zZ87065odPHhQBw4c8GwbYzRixIgOzf1yrYlbU1OTPvroI919992SFPJrxa2z842Pj1dNTY1aWlq89ksX/wvSfY7T6VRNTY3i4uJ6bzLd6IsvvtB9992ntLQ0ZWdnS7r4IdT8/HxPm0BrpiO16o+66zFzudVFkv76178qMTHRsx1Ka6Xt63BfP6e0G5amTJmiAwcOqKqqSvX19XrnnXc0ffr0Tk+8vzh58qSys7P1i1/8QklJSZIuLsjnnntOX3/9tZqbm5WXl6eEhARNmDBBJSUl+uqrr9TS0qK3335b06dP14gRIzRgwAB9/PHHkqTt27f365pduHBB69evV2Njo2pqalRYWKgNGzb4XRehUhO3I0eOaMyYMYqJiZHEWnHr7HwjIyM1adIkFRUVee2XpLvuukvbt2+XJBUVFWnSpEmKjIzsk3ldipqaGv34xz/WsmXLlJWV5dkfHR2tDRs26NixYzLGKCcnRwkJCV2qVX/UXY+Zy60uVVVVamho0MiRIz37QmWt+Hsd7vPnlI58Mn3nzp0mKSnJzJo1y/z+97/vyCn91rPPPmtuvfVWM2/ePM///vjHP5o333zTzJ071yQkJJgNGzZ42hcXF5uUlBQza9Yss27dOuNyuYwxxnz++edmwYIFZs6cOebRRx81jY2NfTWlbvHiiy+aOXPmmFmzZpk33njDGBN4XYRKTYwxZteuXebhhx/22hfKa2XmzJmeb/N0dr7Hjx83P/jBD8zcuXNNVlaWOXfunDHGmOrqarN06VKTmJho0tPTfb4tFOzcNdm8ebO56aabvJ5bfv3rXxtjjNm9e7fnsbRixQpPTTpbq/6k9VrprsdMf69L65ocPnzYLFq0yKdNKKyVQK/DffmcEmaMn1/sAQAAQBL/D94AAABWhCUAAAALwhIAAIAFYQkAAMCCsAQAAGBBWALQo5YuXapt27Z1+fw9e/bopZde6sYRAUDnEJYABLXPPvtMX3/9dV8PA0AIc/T1AAD0H3l5efrDH/6g8PBwDR06VGvWrNGmTZt07tw5HTt2TDNmzFBmZqZWrFihiooKffe739XZs2fb7bepqUnr1q1TcXGxhgwZohtvvFH19fW67777lJubq5aWFl1xxRX69NNPNWfOHKWnp0uSfvvb36q6ulqrVq3q6akDCGGEJQAdcuDAAb322mvKy8tTfHy8tm3bpuzsbN1yyy1qaGjQrl27JEnZ2dmaMGGCHn74YX311Ve699572+07JydHpaWl2rVrl5qbm7VkyRKNHz9eEyZMUEZGhqqrq/XII4/o3Xff1e9+9zulp6fL5XJp69ateu2113p45gBCHb+GA9Ah7733nhITExUfHy9Jmj9/vk6fPi1JmjhxoqddcXGx5s+fL0kaPXq0br/99nb7/uCDD5ScnKyoqCgNGjQoYMCaOXOmKisr9a9//Uvvvfeerr32Wo0dO/YSZwYAdryzBKBD/P1lJGOMnE6n5w8JS1JYWJhXW4ej/aeZAQMGeG0H+qOWERERysjIUH5+vioqKpSRkdHR4QNAl/HOEoAOmTZtmoqKilRVVSVJKigoUFxcnCIiIrza3XnnncrLy5MklZeX629/+1u7fc+YMUPbtm1TY2OjmpqaPH8pXLoYkJxOp2d70aJFevfdd/XPf/5TCQkJ3TE1ALDinSUAHTJ16lQ98MADuv/+++VyuRQfH69Nmzbp9ddf92r31FNPaeXKlZo7d66uvvpqjR8/vt2+09LSdOzYMaWlpSkmJsbzqz5JuuOOO/Szn/1MkZGRWrNmjYYMGaKbb75Z48aNC/gOFAB0pzDj7711AOhDr7/+uo4eParnn3/e51hVVZUWLlyonJwcXXPNNX0wOgChhneWAPSKxYsXq7a21u+xnJwcxcbGttvHli1b9Ktf/UoPPfQQQQlAr+GdJQAAAAs+4A0AAGBBWAIAALAgLAEAAFgQlgAAACwISwAAABaEJQAAAIv/B/8IYBDlS/P2AAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 720x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAjwAAAEJCAYAAACQUP0ZAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjYuMCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy89olMNAAAACXBIWXMAAAsTAAALEwEAmpwYAAAUQ0lEQVR4nO3df2xV9f3H8ddtb3/YFUVYC1pBwRknbHNETYpAuxEtg7ZgbZ0OvnbCN2gWF5fNNYCAcwTkC0VE1BiyLUuI9TudlSKkATQGiC2Dyn7gvlFDECxQoZUf0h/09rb38/2DcOPl3sstrL23vHk+/rH33HPu/ZzzuYf7zP3h9TjnnAAAAAxLSvQAAAAA+hvBAwAAzCN4AACAeQQPAAAwj+ABAADmETwAAMA8ggcAAJjnjbXCqVPtCgT4X/Uk0tChmTpxoi3Rw7iqMQcDA/OQeMzBwMA8hEtK8uj6678V9fqYwRMIOIJnAGAOEo85GBiYh8RjDgYG5uHS8JYWAAAwj+ABAADmETwAAMA8ggcAAJhH8AAAAPMIHgAAYB7BAwAAzCN4AACAeQQPAAAwj+ABAADmETwAAMA8ggcAAJhH8AAAAPMIHgAAYB7BAwAAzCN4AACAeQQPAAAwj+ABAADmeWOtUFm5TCdOnND3vnenZs4sj8eYAAAA+lTM4GlpaVFzc7Ouu25wHIYDAADQ92K/pZWULCXF7CIAAIABi8/wAAAA8wgeAABgHsEDAADMI3gAAIB5BA8AADCP4AEAAOYRPAAAwDyCBwAAmEfwAAAA8wgeAABgHsEDAADMI3gAAIB5BA8AADCP4AEAAOYRPAAAwDyCBwAAmEfwAAAA8wgeAABgHsEDAADMI3gAAIB5BA8AADCP4AEAAOYRPAAAwDyCBwAAmEfwAAAA8wgeAABgHsEDAADMI3gAAIB5BA8AADCP4AEAAOYRPAAAwDyCBwAAmEfwAAAA8wgeAABgHsEDAADMI3gAAIB5BA8AADCP4AEAAOYRPAAAwDyCBwAAmHdJwVNXt1N1dTv7aywAAAD9wnspK3/44Q5J0oQJef0yGAAAgP7AW1oAAMA8ggcAAJhH8AAAAPMIHgAAYB7BAwAAzCN4AACAeQQPAAAwj+ABAADmETwAAMA8ggcAAJhH8AAAAPMIHgAAYB7BAwAAzCN4AACAeQQPAAAwj+ABAADmETwAAMA8ggcAAJhH8AAAAPMIHgAAYB7BAwAAzCN4AACAeQQPAAAwj+ABAADmETwAAMA8ggcAAJhH8AAAAPMIHgAAYB7BAwAAzCN4AACAeQQPAAAwj+ABAADmETwAAMA8ggcAAJhH8AAAAPMIHgAAYB7BAwAAzCN4AACAeQQPAAAwj+ABAADmeXu74gsvVCpVqepSl15+4SUFFFC3upWmNPnkk1fe4OUudcnJBdc/v86F/01SklKUErLsjtvH6PPPDgSX5ebdq7++vTFsPA+VzdDfdtaHrPfIz/5Llcue16GjB3VLzihVLHxGpaUPRdyfefOe1pvrq9TR06GM5Aw9XD5LK1a8EPF2R9/6nbB1//znP0Tdr+bmM706pvmTckP29cL/Oq90pKmlt1N00eMz+vZb5Wv39erYWJadfW3Ysb7wMXf+8Xuxxx8AXOmiPQ9a5XHOuYut8N9zn9An//ex9u3apzKVaaRGqlGNqlGNJmuyrtW1qla12tSmEpUEL3eoQ8M0TK1qValKg9udX9crr9KVHnZdu9pVrvKQZXfl3RPypPNQ2Qzt3dkQsu1f9BelJaWpJFASXFZ7Ta2WrP6fsCf2efOe1l///L9h933t8Ot05tjXIcurVBVxnJ3qVLe6lanMiPsXK3ryJ+Xq8GeNYdvmKEc/1U+Dl7u8fvn8nWppae31pEY6PtWq1jiN04/0o4seG8uys68Nm6/1Wq9v6VthxypLWcpTXvDxt33HB5c0B+gfWVmDmIcEYw4Ghv90HqI9Dz40+2dXbPQkJXk0dGhm9Otj3oJz8sijMpVplEYpWckapVF6QA/oQ32oURqlUpUqTWkhl1OUopM6qVKVhmx3fl2vvBGvS1Vq2LK/7awPGdLfdtaHbZuhDJUESkKWTTs7TZXLng/bpTfXV0W87xPHvgpbHm2cXnmVprSo+xfL558diLjtF/oi5LKnO+ZNhYl0fEpVqk/1acxjY1mk+UpVasRj1aSmqI8/ALjSRXsefHN9VaKH1m969ZaWk9NIjQxZNlIj9ZW+Cv7tky/s8vm/L9yuN9ddbJlPvrBtT+t0xNs7dPRg2P509HREve8Ll3eqM+K6ners9T5EEum+vnm7l3Jbvb3t8/Nz/nKkY2NZpOMS7Vh98zF6OXMAAANZtOfBjp6OBI2o/8V+hcfjkUceNaoxZHGjGvVtfTv4d5rSwi6nKS3idmlKU7rSo14Xa1mk2x2swRFv75acUWG7lJGcEfW+L1webZzpSr/o/sUSbdt0pV/ybfX2ts/Pz/nLkY6NZZGOS6w5vNw5AICBLNrzYEZyRoJG1P969S0tJ6e39bYO6qB61KODOqga1WiiJuqgDqpa1fLJF3LZL7+GaIiqVR2y3fl1u9Ud8boudYUty827N2Q8uXn3hm3boQ5tSNoQsqz2mlpVLHwmbH8eLp8V8b6HDv922PJo4+xWt3zyRd2/WEbffmvEbW/WzSGXXa8/Vn7x41Otan1X3415bCyLNF9d6op4rG7UjVEffwBwpYv2PPhw+axED63f9OpDy83NLdr87obgt65Slcq3tOL4La3L+XAa39KK7HK/pcUHNQcG5iHxmIOBoS/mwdq3tGJ9aLnXwXP7bd8JLps3b3HfjRAx8Q9M4jEHAwPzkHjMwcDAPIT7z7+lBQAAcIUjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACY572UlSdOzO+vcQAAAPSbSwqeCRPy+mscAAAA/Ya3tAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHmxgyfQIwW64zAUAACA/uGNtUJWVpaSkpI0YsTN8RgPAABAn4sZPBUVCxUIuHiMBQAAoF/wGR4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMwjeAAAgHkEDwAAMI/gAQAA5hE8AADAPIIHAACYR/AAAADzCB4AAGAewQMAAMzzxlohKckTj3EgBuYh8ZiDgYF5SDzmYGBgHkLFOh4e55yL01gAAAASgre0AACAeQQPAAAwj+ABAADmETwAAMA8ggcAAJhH8AAAAPMIHgAAYB7BAwAAzCN4AACAeQRPHJWXl6uwsFAzZszQjBkz9K9//UubNm3StGnTdP/996uqqiq4bn19vYqLi1VQUKAXX3wxuPyTTz5RaWmppkyZooULF6q7u1uS1NTUpFmzZuknP/mJfvGLX6i9vT3u+zeQtbW1qaioSEeOHJHUd8f3zJkzevzxxzV16lTNmjVLLS0tkqSuri5VVFRo6tSpKikp0YEDB+K8xwPThfOwYMECFRQUBM+J9957T1L/z8/V6pVXXlFhYaEKCwu1cuVKSZwLiRBpHjgX4sAhLgKBgJswYYLz+/3BZceOHXM//vGP3alTp1x7e7srLi52+/fvd2fPnnX5+fmusbHR+f1+N2fOHLd9+3bnnHOFhYXuH//4h3POuQULFriqqirnnHOPP/6427x5s3POuVdeecWtXLkyvjs4gP3zn/90RUVFbuzYse7w4cN9enx///vfu3Xr1jnnnNuwYYP71a9+5Zxz7o9//KNbvHixc865PXv2uLKysnjt7oB14Tw451xRUZE7fvx4yHrxmJ+rUV1dnXv44Yedz+dzXV1drry83G3atIlzIc4izcO2bds4F+KAV3ji5PPPP5fH49HcuXM1ffp0vf7666qvr1dubq4GDx6sjIwMTZkyRVu2bNG+fft08803a8SIEfJ6vSouLtaWLVt09OhRdXZ26oc//KEk6cEHH9SWLVvk9/vV0NCgKVOmhCzHOW+99ZZ+97vfKTs7W5L69Phu375dxcXFkqSioiLt3LlTfr9f27dv1/Tp0yVJ99xzj06dOqWmpqY47/nAcuE8dHR0qKmpSYsXL1ZxcbHWrl2rQCAQl/m5GmVlZWn+/PlKTU1VSkqKbr31Vh06dIhzIc4izUNTUxPnQhzE/LV09I0zZ85o/Pjxeu6559TZ2any8nJNnTpVWVlZwXWys7O1b98+NTc3hy0/fvx42PKsrCwdP35cp06dUmZmprxeb8hynLNs2bKQy315fL+5jdfrVWZmpk6ePBnxto4dO6Ybb7yx3/ZzoLtwHk6cOKHc3FwtWbJEGRkZeuKJJ/T2228rIyOj3+dn2LBh/b27A85tt90W/PvQoUOqra3Vo48+yrkQZ5Hm4Y033tCePXs4F/oZr/DEybhx47Ry5UplZGRoyJAhKisr09q1a8PW83g8chF+wP5yliOy/j6+SUmRT6toy69WI0aM0KuvvqqhQ4fqmmuu0aOPPqodO3YkbH6uFvv379ecOXM0b948jRw5Mux6zoX4+OY8jB49mnMhDq6uvU2gjz76SLt27Qpeds4pJydHX331VXBZc3OzsrOzNWzYsF4tb2lpUXZ2toYMGaK2tjb19PSELEdkfXl8s7Ozg9t0d3erra1NgwcPVnZ2dsiHApmTcJ999pm2bt0avOyck9frjcv8XK327t2rxx57TE8//bRKSko4FxLkwnngXIgPgidOWltbtXLlSvl8PrW1tWnDhg2qrKzUrl27dPLkSZ09e1bbtm1TXl6e7rzzTh08eFBffPGFenp6tHnzZuXl5SknJ0dpaWnau3evJKmmpkZ5eXlKSUnR3Xffrdra2pDliKwvj29+fr5qamokSbW1tbr77ruVkpKi/Px8bdy4UdK52E1LS7tqX8KPxjmn559/Xl9//bX8fr/efPNN3X///XGZn6vRl19+qSeffFKrVq1SYWGhJM6FRIg0D5wL8eFxkV4DQ79Ys2aNtm7dqkAgoJkzZ+rnP/+5Nm3apHXr1snv96usrExz586VJO3atUvLly+Xz+dTfn6+FixYII/Ho08//VSLFi1Se3u7xowZo+XLlys1NVVHjx7V/PnzdeLECd1www1avXq1rrvuugTv8cAyefJkrV+/XjfddFOfHd/Tp09r/vz5Onz4sAYNGqRVq1bppptuks/n07PPPqt///vfSk1N1dKlSzV27NhEH4IB4ZvzUFVVpaqqKnV3d6ugoEC//e1vJfXd4z/a/FyNli5dqurq6pC3sR555BHdcsstnAtxFG0eAoEA50I/I3gAAIB5vKUFAADMI3gAAIB5BA8AADCP4AEAAOYRPAAAwDyCB4Ak6eOPP9ZTTz2lffv26dlnn030cEKcHxsAXC6+lg4gxDvvvKOtW7dq3bp1iR4KAPQZggeAJGn37t1auHChuru71draqoKCAi1fvlwffPCBXnvtNfn9fqWnp2vevHkaN26cXn75ZTU2Nurw4cNqbm7WD37wA02YMEE1NTU6cuSIKioqVFRUdNH7nDx5su677z599NFHam1t1ezZszVz5kzt3r1by5YtU0ZGhjo6OlRRUaEVK1Zo8+bNam9v19KlS/X3v/9dycnJuu+++/TrX/9afr9fq1atUkNDg3p6ejRmzBgtWrRImZmZcTqCAAYyfi0dQFB6errmzJmjrVu3avny5Tp06JBefPFFrV+/Xtdff73279+v2bNna9u2bZLO/SbQxo0blZKSory8PA0fPlxVVVV6//33VVlZGTN4JKmzs1PV1dU6fvy4HnjgAd11112Szv244vvvv6+cnBzt3r07uP7atWvl8/lUW1urnp4ezZkzR3v27FFDQ4OSk5P1zjvvyOPxaPXq1Vq1apWee+65fjlWAK4sBA+AqOrq6tTc3KzHHnssuMzj8aixsVGSdO+992rQoEGSzv044aRJkyRJI0eO1OnTp3t1HzNnzpTH49Hw4cM1adIk1dXVaezYsbrhhhuUk5MTtn59fb0WLFig5ORkJScn6/XXX5ckVVZWqrW1VfX19ZIkv9+voUOHXu6uAzCG4AEQVSAQ0Pjx47VmzZrgsi+//FLZ2dl67733lJqaGrK+13vp/6R8c5tAIKCkpHPfpcjIyIi6vsfjCRlPenq6AoGAnnnmGeXn50uS2tvb5fP5Lnk8AGziW1oAQiQnJ6u7u1uSlJubq7q6Oh04cECStGPHDk2fPr1PQ+L8Lzg3NTWprq4u+OvO0YwfP14bNmxQIBBQV1eXnnrqKTU0NGjixImqqqpSV1eXAoGAFi9erNWrV/fZOAFc2XiFB0CIcePGac2aNXryySf16quvasmSJfrNb34j55y8Xq9ee+21qK++XI4jR47owQcfVGdnpxYtWqTRo0erpaUl6vq//OUvtWzZMs2YMUM9PT2aNm2aCgoKlJeXpxUrVqikpEQ9PT264447NH/+/D4bJ4ArG9/SApAwkydP1ksvvaTvf//7iR4KAON4hQdAv3n33Xf1pz/9KeJ1xcXFcR4NgKsZr/AAAADz+NAyAAAwj+ABAADmETwAAMA8ggcAAJhH8AAAAPMIHgAAYN7/AxaReljjX+yEAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 720x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 销售量\n",
    "plt.figure(figsize=(10,4))\n",
    "plt.xlim(-100, 20000)\n",
    "flierprops = dict(marker='o', markerfacecolor='purple', markersize=6,\n",
    "                  linestyle='none', markeredgecolor='black')\n",
    "sns.boxplot(x=sales_data.ord_qty, flierprops=flierprops)\n",
    "\n",
    "# 价格\n",
    "plt.figure(figsize=(10,4))\n",
    "plt.xlim(sales_data.item_price.min(), sales_data.item_price.max()*1.1)\n",
    "sns.boxplot(x=sales_data.item_price, flierprops=flierprops)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 778,
   "id": "f3ba4beb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "260014.0"
      ]
     },
     "execution_count": 778,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.item_price.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0b7cdb7",
   "metadata": {},
   "source": [
    "- 去除销量大于4000的以及价格大于30000的\n",
    "    - 其实这里还需要验证一下去除的那几条记录，是不是好的，因为可能有些商品就是卖这么多或者怎样\n",
    "    - 验证一下test集里有没有出现"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 779,
   "id": "2b74a57d",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_data = sales_data[(sales_data.item_price < 30000 )& (sales_data.ord_qty < 4000)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 780,
   "id": "1ed7582e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_date</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>sales_chan_name</th>\n",
       "      <th>item_price</th>\n",
       "      <th>ord_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015/9/1</td>\n",
       "      <td>104</td>\n",
       "      <td>22069</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>offline</td>\n",
       "      <td>1114.0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015/9/1</td>\n",
       "      <td>104</td>\n",
       "      <td>20028</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>offline</td>\n",
       "      <td>1012.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015/9/2</td>\n",
       "      <td>105</td>\n",
       "      <td>21183</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>online</td>\n",
       "      <td>428.0</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015/9/2</td>\n",
       "      <td>105</td>\n",
       "      <td>20448</td>\n",
       "      <td>308</td>\n",
       "      <td>404</td>\n",
       "      <td>online</td>\n",
       "      <td>962.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015/9/2</td>\n",
       "      <td>104</td>\n",
       "      <td>21565</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>offline</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_date  sales_region_code  item_code  first_cate_code  second_cate_code  \\\n",
       "0   2015/9/1                104      22069              307               403   \n",
       "1   2015/9/1                104      20028              301               405   \n",
       "2   2015/9/2                105      21183              307               403   \n",
       "3   2015/9/2                105      20448              308               404   \n",
       "4   2015/9/2                104      21565              307               403   \n",
       "\n",
       "  sales_chan_name  item_price  ord_qty  \n",
       "0         offline      1114.0       19  \n",
       "1         offline      1012.0       12  \n",
       "2          online       428.0      109  \n",
       "3          online       962.0        3  \n",
       "4         offline      1400.0        3  "
      ]
     },
     "execution_count": 780,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3360481b",
   "metadata": {},
   "source": [
    "## 数据类型处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 781,
   "id": "c36924e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 时间 object->datetime格式\n",
    "sales_data[\"order_date\"]=sales_data[\"order_date\"].apply(pd.to_datetime,format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 782,
   "id": "d4ecf444",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 线上0 线下1\n",
    "dic = {\"offline\":1,\"online\":0}\n",
    "sales_data['sales_chan_name'] = sales_data['sales_chan_name'].map(dic)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 783,
   "id": "a1e602d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([20001, 20002, 20003, ..., 22082, 22083, 22084]),\n",
       " array([  13,  694, 2767, ...,  159,  311,  468]))"
      ]
     },
     "execution_count": 783,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 单独的item代码，以及其对应的记录条数\n",
    "train_item = np.unique(sales_data[\"item_code\"],return_counts=True)\n",
    "train_item"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "833c2454",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "87701d6f",
   "metadata": {},
   "source": [
    "## 检验这四类code是否一一对应\n",
    "对后续如何处理很关键"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98116680",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8897938c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a0f310b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "08431a5b",
   "metadata": {},
   "source": [
    "- 简单总结\n",
    "   - 一级分类和二级分类是一对多的\n",
    "   - 每个商品只有一个类别，不会对用多种\n",
    "   - 1421 种商品中，只有 332 种商品是只在一个地区销售的，其他的都在多个地区销售"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03df2f16",
   "metadata": {},
   "source": [
    "## 编码label_encoding\n",
    "- 将地区、item_code、类别进行顺序编码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 784,
   "id": "ea93feb8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_date</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>sales_chan_name</th>\n",
       "      <th>item_price</th>\n",
       "      <th>ord_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>104</td>\n",
       "      <td>22069</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>1114.0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>104</td>\n",
       "      <td>20028</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>1</td>\n",
       "      <td>1012.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>105</td>\n",
       "      <td>21183</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>0</td>\n",
       "      <td>428.0</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>105</td>\n",
       "      <td>20448</td>\n",
       "      <td>308</td>\n",
       "      <td>404</td>\n",
       "      <td>0</td>\n",
       "      <td>962.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>104</td>\n",
       "      <td>21565</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_date  sales_region_code  item_code  first_cate_code  second_cate_code  \\\n",
       "0 2015-09-01                104      22069              307               403   \n",
       "1 2015-09-01                104      20028              301               405   \n",
       "2 2015-09-02                105      21183              307               403   \n",
       "3 2015-09-02                105      20448              308               404   \n",
       "4 2015-09-02                104      21565              307               403   \n",
       "\n",
       "   sales_chan_name  item_price  ord_qty  \n",
       "0                1      1114.0       19  \n",
       "1                1      1012.0       12  \n",
       "2                0       428.0      109  \n",
       "3                0       962.0        3  \n",
       "4                1      1400.0        3  "
      ]
     },
     "execution_count": 784,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6ef4ed32",
   "metadata": {},
   "source": [
    "这里test有train中没有的商品类别（没有更新）\n",
    "包括\n",
    "> y contains previously unseen labels: [20011, 20025, 20198, 20273, 20324, 20401, 20449, 20456, 20469, 20503, 20549, 20701, 20721, 20736, 20750, 20793, 20841, 20869, 20951, 20999, 21085, 21184, 21393, 21416, 21523, 21606, 21667, 21687, 21823, 21867, 21879, 21954, 22075]\n",
    "\n",
    "所以就先不对item code进行编码了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "741f97ba",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 785,
   "id": "93d24b41",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_date</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>sales_chan_name</th>\n",
       "      <th>item_price</th>\n",
       "      <th>ord_qty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>104</td>\n",
       "      <td>22069</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>1114.0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>104</td>\n",
       "      <td>20028</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>1</td>\n",
       "      <td>1012.0</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>105</td>\n",
       "      <td>21183</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>0</td>\n",
       "      <td>428.0</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>105</td>\n",
       "      <td>20448</td>\n",
       "      <td>308</td>\n",
       "      <td>404</td>\n",
       "      <td>0</td>\n",
       "      <td>962.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>104</td>\n",
       "      <td>21565</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_date  sales_region_code  item_code  first_cate_code  second_cate_code  \\\n",
       "0 2015-09-01                104      22069              307               403   \n",
       "1 2015-09-01                104      20028              301               405   \n",
       "2 2015-09-02                105      21183              307               403   \n",
       "3 2015-09-02                105      20448              308               404   \n",
       "4 2015-09-02                104      21565              307               403   \n",
       "\n",
       "   sales_chan_name  item_price  ord_qty  \n",
       "0                1      1114.0       19  \n",
       "1                1      1012.0       12  \n",
       "2                0       428.0      109  \n",
       "3                0       962.0        3  \n",
       "4                1      1400.0        3  "
      ]
     },
     "execution_count": 785,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b040271",
   "metadata": {},
   "source": [
    "# 2. Preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df98009b",
   "metadata": {},
   "source": [
    "- 标签平滑处理：取对数，其实应该放在不同的时间精度不同，比如按月预测的话，应该先按月合成再取对数\n",
    "- 按照月份递增的顺序，创建一个包含月份、地区和商品的组合的矩阵df。\n",
    "    - Item_cnt_day（ord_qty）被求和为Item_cnt_month。\n",
    "- product()函数也可以用于求取一个可迭代对象中自己各个元素的全排列组合\n",
    "\n",
    "- 因为每一个月，每个region种所出售的item情况不同，很多时候可能是0（没有这个记录），但是这都要参与到我们的建模中。所以我们要先将所有的情况都列出来。然后再与现有数据合并，缺失的数据就填补为0，这样构建数据集"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6303889",
   "metadata": {},
   "source": [
    "- ？？？\n",
    "- 这里暂时按月合成的时候，先忽略线上、线下的因素，全部一股脑合并先，需要补充eda，对不同商品线上线下销售情况进行EDA，再进行分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "abff876f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 786,
   "id": "e1f13693",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 生成一列月份编号\n",
    "sales_data[\"month\"] = sales_data.order_date.dt.month\n",
    "sales_data[\"year\"] = sales_data.order_date.dt.year\n",
    "sales_data[\"date_block_num\"] = (sales_data[\"year\"]-sales_data[\"year\"].min())*12+sales_data[\"month\"]-9 # 减8是因为第一个月是9月份\n",
    "\n",
    "# 输出结果\n",
    "del sales_data[\"month\"],sales_data[\"year\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7e78511",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "59a295d9",
   "metadata": {},
   "source": [
    "尝试直接剔除104地区\n",
    "实际上应该不需要"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "130f989b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 787,
   "id": "af7a93dd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.20708608627319336"
      ]
     },
     "execution_count": 787,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from itertools import product\n",
    "import time\n",
    "ts = time.time()\n",
    "matrix = []\n",
    "cols  = [\"date_block_num\",\"sales_region_code\", \"item_code\"]\n",
    "for i in range(40):\n",
    "    sales = sales_data[sales_data.date_block_num == i]\n",
    "    region_list = sales.sales_region_code.unique() # （不）剔除了104地区\n",
    "    item_list = sales.item_code.unique()\n",
    "    matrix.append( np.array(list( product( [i],region_list , item_list) ), dtype = np.int16) )\n",
    "\n",
    "matrix = pd.DataFrame( np.vstack(matrix), columns = cols ) # 堆叠\n",
    "matrix[\"date_block_num\"] = matrix[\"date_block_num\"].astype(np.int8)\n",
    "matrix[\"sales_region_code\"] = matrix[\"sales_region_code\"].astype(np.int8)\n",
    "matrix[\"item_code\"] = matrix[\"item_code\"].astype(np.int16)\n",
    "matrix.sort_values( cols, inplace = True )\n",
    "time.time()- ts "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 788,
   "id": "688cc900",
   "metadata": {},
   "outputs": [],
   "source": [
    "# add revenue to train df 收入\n",
    "sales_data[\"revenue\"] = sales_data[\"item_price\"] * sales_data[\"ord_qty\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 789,
   "id": "b2fb3d5d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.13352608680725098"
      ]
     },
     "execution_count": 789,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "group = sales_data.groupby( [\"date_block_num\", \"sales_region_code\", \"item_code\"] ).agg( {\"ord_qty\": [\"sum\"]} )\n",
    "group.columns = [\"item_cnt_month\"]\n",
    "group.reset_index( inplace = True) # 这一步重置索引，直接把层级索引转换为列\n",
    "matrix = pd.merge( matrix, group, on = cols, how = \"left\" ) # 转换后，二者就可以merge了\n",
    "matrix[\"item_cnt_month\"] = matrix[\"item_cnt_month\"].fillna(0).astype(np.float16) # 填补缺失值\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 790,
   "id": "f4222e92",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>item_cnt_month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20003</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20006</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20007</td>\n",
       "      <td>3092.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20008</td>\n",
       "      <td>308.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20009</td>\n",
       "      <td>260.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   date_block_num  sales_region_code  item_code  item_cnt_month\n",
       "0               0                101      20003             0.0\n",
       "1               0                101      20006             0.0\n",
       "2               0                101      20007          3092.0\n",
       "3               0                101      20008           308.0\n",
       "4               0                101      20009           260.0"
      ]
     },
     "execution_count": 790,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# matrix就是每月、每个地区、每个商品的销量\n",
    "matrix.head()  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de81c6a1",
   "metadata": {},
   "source": [
    "- 为第40个月设置一下test集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 791,
   "id": "77a66e3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 对测试集添加一个月份列，同时设置一下三列的数据格式\n",
    "test[\"date_block_num\"] = 40\n",
    "test[\"date_block_num\"] = test[\"date_block_num\"].astype(np.int8)\n",
    "test[\"sales_region_code\"] = test.sales_region_code.astype(np.int8)\n",
    "test[\"item_code\"] = test.item_code.astype(np.int16)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "45c15bc0",
   "metadata": {},
   "source": [
    "- 合并训练集和测试集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 792,
   "id": "ab2cba33",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>item_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>22069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>20028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>21183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>308</td>\n",
       "      <td>404</td>\n",
       "      <td>20448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>21565</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   first_cate_code  second_cate_code  item_code\n",
       "0              307               403      22069\n",
       "1              301               405      20028\n",
       "2              307               403      21183\n",
       "3              308               404      20448\n",
       "4              307               403      21565"
      ]
     },
     "execution_count": 792,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "map_cate_df = sales_data[[\"first_cate_code\",\"second_cate_code\",\"item_code\"]].drop_duplicates()\n",
    "map_cate_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 793,
   "id": "be0d090e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# matrix.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 794,
   "id": "69b494f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 给matrix补充上类别属性\n",
    "matrix = pd.merge(matrix, map_cate_df, on = [\"item_code\"], how = \"left\")\n",
    "matrix[\"first_cate_code\"] = matrix[\"first_cate_code\"].astype(np.int16)\n",
    "matrix[\"second_cate_code\"] = matrix[\"second_cate_code\"].astype(np.int16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 795,
   "id": "b30d72a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# matrix.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 796,
   "id": "5ebac8fe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>item_cnt_month</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20007</td>\n",
       "      <td>3092.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20008</td>\n",
       "      <td>308.0</td>\n",
       "      <td>303</td>\n",
       "      <td>401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20009</td>\n",
       "      <td>260.0</td>\n",
       "      <td>306</td>\n",
       "      <td>407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107691</th>\n",
       "      <td>39</td>\n",
       "      <td>105</td>\n",
       "      <td>22080</td>\n",
       "      <td>16.0</td>\n",
       "      <td>306</td>\n",
       "      <td>407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107692</th>\n",
       "      <td>39</td>\n",
       "      <td>105</td>\n",
       "      <td>22081</td>\n",
       "      <td>7.0</td>\n",
       "      <td>303</td>\n",
       "      <td>401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107693</th>\n",
       "      <td>39</td>\n",
       "      <td>105</td>\n",
       "      <td>22082</td>\n",
       "      <td>0.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107694</th>\n",
       "      <td>39</td>\n",
       "      <td>105</td>\n",
       "      <td>22083</td>\n",
       "      <td>1124.0</td>\n",
       "      <td>303</td>\n",
       "      <td>401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107695</th>\n",
       "      <td>39</td>\n",
       "      <td>105</td>\n",
       "      <td>22084</td>\n",
       "      <td>0.0</td>\n",
       "      <td>302</td>\n",
       "      <td>408</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>107696 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date_block_num  sales_region_code  item_code  item_cnt_month  \\\n",
       "0                    0                101      20003             0.0   \n",
       "1                    0                101      20006             0.0   \n",
       "2                    0                101      20007          3092.0   \n",
       "3                    0                101      20008           308.0   \n",
       "4                    0                101      20009           260.0   \n",
       "...                ...                ...        ...             ...   \n",
       "107691              39                105      22080            16.0   \n",
       "107692              39                105      22081             7.0   \n",
       "107693              39                105      22082             0.0   \n",
       "107694              39                105      22083          1124.0   \n",
       "107695              39                105      22084             0.0   \n",
       "\n",
       "        first_cate_code  second_cate_code  \n",
       "0                   301               405  \n",
       "1                   307               403  \n",
       "2                   305               412  \n",
       "3                   303               401  \n",
       "4                   306               407  \n",
       "...                 ...               ...  \n",
       "107691              306               407  \n",
       "107692              303               401  \n",
       "107693              305               412  \n",
       "107694              303               401  \n",
       "107695              302               408  \n",
       "\n",
       "[107696 rows x 6 columns]"
      ]
     },
     "execution_count": 796,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 797,
   "id": "42cf3c0a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.006335020065307617"
      ]
     },
     "execution_count": 797,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 合并训练和测试\n",
    "ts = time.time()\n",
    "# cols  = [\"date_block_num\",\"sales_region_code\", \"item_code\"]\n",
    "\n",
    "matrix = pd.concat([matrix, test], ignore_index=True, sort=False, keys=cols) # 按三列，添加？\n",
    "matrix.fillna( 0, inplace = True )\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 798,
   "id": "20a9ca0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>item_cnt_month</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20007</td>\n",
       "      <td>3092.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20008</td>\n",
       "      <td>308.0</td>\n",
       "      <td>303</td>\n",
       "      <td>401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20009</td>\n",
       "      <td>260.0</td>\n",
       "      <td>306</td>\n",
       "      <td>407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110310</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22066</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110311</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22072</td>\n",
       "      <td>0.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110312</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22075</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110313</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22083</td>\n",
       "      <td>0.0</td>\n",
       "      <td>303</td>\n",
       "      <td>401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110314</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22084</td>\n",
       "      <td>0.0</td>\n",
       "      <td>302</td>\n",
       "      <td>408</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110315 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date_block_num  sales_region_code  item_code  item_cnt_month  \\\n",
       "0                    0                101      20003             0.0   \n",
       "1                    0                101      20006             0.0   \n",
       "2                    0                101      20007          3092.0   \n",
       "3                    0                101      20008           308.0   \n",
       "4                    0                101      20009           260.0   \n",
       "...                ...                ...        ...             ...   \n",
       "110310              40                105      22066             0.0   \n",
       "110311              40                105      22072             0.0   \n",
       "110312              40                105      22075             0.0   \n",
       "110313              40                105      22083             0.0   \n",
       "110314              40                105      22084             0.0   \n",
       "\n",
       "        first_cate_code  second_cate_code  \n",
       "0                   301               405  \n",
       "1                   307               403  \n",
       "2                   305               412  \n",
       "3                   303               401  \n",
       "4                   306               407  \n",
       "...                 ...               ...  \n",
       "110310              307               403  \n",
       "110311              305               412  \n",
       "110312              307               403  \n",
       "110313              303               401  \n",
       "110314              302               408  \n",
       "\n",
       "[110315 rows x 6 columns]"
      ]
     },
     "execution_count": 798,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matrix"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58615d0b",
   "metadata": {},
   "source": [
    "取对数，+1是因为销量有可能为0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 799,
   "id": "c9e51412",
   "metadata": {},
   "outputs": [],
   "source": [
    "matrix.to_csv('./中间数据/matrix.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 800,
   "id": "58d4f19c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 销量取对数\n",
    "import math\n",
    "if is_need_log:\n",
    "    matrix[\"item_cnt_month\"] = list(map(lambda x : x if x== np.NaN else math.log(x+1,2),matrix[\"item_cnt_month\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0442ddfb",
   "metadata": {},
   "source": [
    "#  3. Feature Engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40d13b7a",
   "metadata": {},
   "source": [
    "## 3.1 Add lag features to matrix df."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 801,
   "id": "ae5b5a9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a lag feature function\n",
    "def lag_feature( df,lags, cols ):\n",
    "    for col in cols:\n",
    "        print(col)\n",
    "        tmp = df[[\"date_block_num\", \"sales_region_code\",\"item_code\",col ]]\n",
    "        for i in lags:\n",
    "            shifted = tmp.copy()\n",
    "            shifted.columns = [\"date_block_num\", \"sales_region_code\",\"item_code\", col + \"_lag_\"+str(i)]\n",
    "            shifted.date_block_num = shifted.date_block_num + i\n",
    "            df = pd.merge(df, shifted, on=[\"date_block_num\", \"sales_region_code\",\"item_code\"], how='left')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 802,
   "id": "4492c110",
   "metadata": {},
   "outputs": [],
   "source": [
    "def lag_cate_feature( df,lags, cols ):\n",
    "    for col in cols:\n",
    "        print(col)\n",
    "        tmp = df[[\"date_block_num\", \"sales_region_code\",\"second_cate_code\",col ]]\n",
    "        for i in lags:\n",
    "            shifted = tmp.copy()\n",
    "            shifted.columns = [\"date_block_num\", \"sales_region_code\",\"second_cate_code\", col + \"_lag_\"+str(i)]\n",
    "            shifted.date_block_num = shifted.date_block_num + i\n",
    "            df = pd.merge(df, shifted, on=[\"date_block_num\", \"sales_region_code\",\"second_cate_code\"], how='left')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d551ce2b",
   "metadata": {},
   "source": [
    "- Add item_cnt_month lag features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "619e53d2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 803,
   "id": "ce12809a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "item_cnt_month\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.10276460647583008"
      ]
     },
     "execution_count": 803,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "matrix = lag_feature( matrix, [1,2,3], [\"item_cnt_month\"] ) # 添加该商店的该商品一个月、两个月、三个月的销量\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7902376e",
   "metadata": {},
   "source": [
    "- Add the previous month's average item_cnt. 上个月的平均销量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 804,
   "id": "256ccd7f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date_avg_item_cnt\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.07983016967773438"
      ]
     },
     "execution_count": 804,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "# 计算每个月的平均销量（这个月的所有大杂烩）\n",
    "group = matrix.groupby( [\"date_block_num\"] ).agg({\"item_cnt_month\" : [\"mean\"]})\n",
    "group.columns = [\"date_avg_item_cnt\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "# 在matrix中添加  前一个月的平均销量 作为一个lag feature\n",
    "matrix = pd.merge(matrix, group, on = [\"date_block_num\"], how = \"left\")\n",
    "matrix.date_avg_item_cnt = matrix[\"date_avg_item_cnt\"].astype(np.float16)\n",
    "matrix = lag_feature( matrix, [1], [\"date_avg_item_cnt\"] )\n",
    "matrix.drop( [\"date_avg_item_cnt\"], axis = 1, inplace = True )\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bde4eee4",
   "metadata": {},
   "source": [
    "- Add lag values of item_cnt_month for month / item_id."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 805,
   "id": "920a962f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date_item_avg_item_cnt\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.16592192649841309"
      ]
     },
     "execution_count": 805,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "# 计算每个月该商品的平均销量（这个月的该商品）\n",
    "group = matrix.groupby(['date_block_num', 'item_code']).agg({'item_cnt_month': ['mean']})\n",
    "group.columns = [ 'date_item_avg_item_cnt' ]\n",
    "group.reset_index(inplace=True)\n",
    "\n",
    "# 添加 前三个月该商品的平均销量\n",
    "matrix = pd.merge(matrix, group, on=['date_block_num','item_code'], how='left')\n",
    "matrix.date_item_avg_item_cnt = matrix['date_item_avg_item_cnt'].astype(np.float16)\n",
    "matrix = lag_feature(matrix, [1,2,3], ['date_item_avg_item_cnt'])\n",
    "matrix.drop(['date_item_avg_item_cnt'], axis=1, inplace=True)\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e874b14",
   "metadata": {},
   "source": [
    "- Add lag values for item_cnt_month for every month / region combination."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 806,
   "id": "22a65673",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date_region_avg_item_cnt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-806-1f9a7b212985>:8: UserWarning: Pandas doesn't allow columns to be created via a new attribute name - see https://pandas.pydata.org/pandas-docs/stable/indexing.html#attribute-access\n",
      "  matrix.date_avg_item_cnt = matrix[\"date_region_avg_item_cnt\"].astype(np.float16)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.20051908493041992"
      ]
     },
     "execution_count": 806,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "# 计算 该地区的平均销量\n",
    "group = matrix.groupby( [\"date_block_num\",\"sales_region_code\"] ).agg({\"item_cnt_month\" : [\"mean\"]})\n",
    "group.columns = [\"date_region_avg_item_cnt\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = pd.merge(matrix, group, on = [\"date_block_num\",\"sales_region_code\"], how = \"left\")\n",
    "matrix.date_avg_item_cnt = matrix[\"date_region_avg_item_cnt\"].astype(np.float16)\n",
    "matrix = lag_feature( matrix, [1,2,3], [\"date_region_avg_item_cnt\"] )\n",
    "matrix.drop( [\"date_region_avg_item_cnt\"], axis = 1, inplace = True )\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2369b88c",
   "metadata": {},
   "source": [
    "- Add lag values for item_cnt_month for month/region/item."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 807,
   "id": "18bf6c9d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date_region_item_avg_item_cnt\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-807-4c9cee418e72>:8: UserWarning: Pandas doesn't allow columns to be created via a new attribute name - see https://pandas.pydata.org/pandas-docs/stable/indexing.html#attribute-access\n",
      "  matrix.date_avg_item_cnt = matrix[\"date_region_item_avg_item_cnt\"].astype(np.float16)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.24795794486999512"
      ]
     },
     "execution_count": 807,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "# 计算每月该地区、该商品的平均销量\n",
    "group = matrix.groupby( [\"date_block_num\",\"sales_region_code\",\"item_code\"] ).agg({\"item_cnt_month\" : [\"mean\"]})\n",
    "group.columns = [\"date_region_item_avg_item_cnt\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = pd.merge(matrix, group, on = [\"date_block_num\",\"sales_region_code\",\"item_code\"], how = \"left\")\n",
    "matrix.date_avg_item_cnt = matrix[\"date_region_item_avg_item_cnt\"].astype(np.float16)\n",
    "matrix = lag_feature( matrix, [1,2,3], [\"date_region_item_avg_item_cnt\"] )\n",
    "matrix.drop( [\"date_region_item_avg_item_cnt\"], axis = 1, inplace = True )\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db025b26",
   "metadata": {},
   "source": [
    "- Add lag values for item_cnt_month for month/region/item second_cate(subtype)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 808,
   "id": "da595deb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date_region_subtype_avg_item_cnt\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.22355890274047852"
      ]
     },
     "execution_count": 808,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "# 计算每月该地区、该子类商品的 销售量\n",
    "group = matrix.groupby(['date_block_num', 'sales_region_code', 'second_cate_code']).agg({'item_cnt_month': ['mean']})\n",
    "group.columns = ['date_region_subtype_avg_item_cnt']\n",
    "group.reset_index(inplace=True)\n",
    "\n",
    "# 添加上一月的\n",
    "matrix = pd.merge(matrix, group, on=['date_block_num', 'sales_region_code', 'second_cate_code'], how='left')\n",
    "matrix.date_region_subtype_avg_item_cnt = matrix['date_region_subtype_avg_item_cnt'].astype(np.float16)\n",
    "matrix = lag_feature(matrix, [1,2,3], ['date_region_subtype_avg_item_cnt'])\n",
    "matrix.drop(['date_region_subtype_avg_item_cnt'], axis=1, inplace=True)\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd3d0d2a",
   "metadata": {},
   "source": [
    "- 其实还可以补充一个大类商品的销售情况（有需要再说吧）"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e988a3d0",
   "metadata": {},
   "source": [
    "## 3.2 add price trend feature\n",
    "* Add average item price on to matix df. \n",
    "* Add lag values of item price per month.\n",
    "* Add delta price values - how current month average pirce relates to global average.\n",
    "\n",
    "前面算的一直是销量，这里开始添加的特征是价格\n",
    "虽然要求预测的是销量，但是价格与销量息息相关，也是重要的特征\n",
    "\n",
    "Price trend features，最后只有一个'delta_price_lag'特征，具体看代码注释"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 809,
   "id": "9d0bb94d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date_block_num', 'sales_region_code', 'item_code', 'item_cnt_month',\n",
       "       'first_cate_code', 'second_cate_code', 'item_cnt_month_lag_1',\n",
       "       'item_cnt_month_lag_2', 'item_cnt_month_lag_3',\n",
       "       'date_avg_item_cnt_lag_1', 'date_item_avg_item_cnt_lag_1',\n",
       "       'date_item_avg_item_cnt_lag_2', 'date_item_avg_item_cnt_lag_3',\n",
       "       'date_region_avg_item_cnt_lag_1', 'date_region_avg_item_cnt_lag_2',\n",
       "       'date_region_avg_item_cnt_lag_3', 'date_region_item_avg_item_cnt_lag_1',\n",
       "       'date_region_item_avg_item_cnt_lag_2',\n",
       "       'date_region_item_avg_item_cnt_lag_3',\n",
       "       'date_region_subtype_avg_item_cnt_lag_1',\n",
       "       'date_region_subtype_avg_item_cnt_lag_2',\n",
       "       'date_region_subtype_avg_item_cnt_lag_3'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 809,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matrix.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 810,
   "id": "0467b8bd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_date</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>sales_chan_name</th>\n",
       "      <th>item_price</th>\n",
       "      <th>ord_qty</th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>104</td>\n",
       "      <td>22069</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>1114.0</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>21166.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-09-01</td>\n",
       "      <td>104</td>\n",
       "      <td>20028</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>1</td>\n",
       "      <td>1012.0</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>12144.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>105</td>\n",
       "      <td>21183</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>0</td>\n",
       "      <td>428.0</td>\n",
       "      <td>109</td>\n",
       "      <td>0</td>\n",
       "      <td>46652.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>105</td>\n",
       "      <td>20448</td>\n",
       "      <td>308</td>\n",
       "      <td>404</td>\n",
       "      <td>0</td>\n",
       "      <td>962.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2886.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-09-02</td>\n",
       "      <td>104</td>\n",
       "      <td>21565</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>1</td>\n",
       "      <td>1400.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4200.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_date  sales_region_code  item_code  first_cate_code  second_cate_code  \\\n",
       "0 2015-09-01                104      22069              307               403   \n",
       "1 2015-09-01                104      20028              301               405   \n",
       "2 2015-09-02                105      21183              307               403   \n",
       "3 2015-09-02                105      20448              308               404   \n",
       "4 2015-09-02                104      21565              307               403   \n",
       "\n",
       "   sales_chan_name  item_price  ord_qty  date_block_num  revenue  \n",
       "0                1      1114.0       19               0  21166.0  \n",
       "1                1      1012.0       12               0  12144.0  \n",
       "2                0       428.0      109               0  46652.0  \n",
       "3                0       962.0        3               0   2886.0  \n",
       "4                1      1400.0        3               0   4200.0  "
      ]
     },
     "execution_count": 810,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 811,
   "id": "e4f96073",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date_item_avg_item_price\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1.364837884902954"
      ]
     },
     "execution_count": 811,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "# 添加该商品的平均价格\n",
    "group = sales_data.groupby( [\"item_code\"] ).agg({\"item_price\": [\"mean\"]})\n",
    "group.columns = [\"item_avg_item_price\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = matrix.merge( group, on = [\"item_code\"], how = \"left\" )\n",
    "matrix[\"item_avg_item_price\"] = matrix.item_avg_item_price.astype(np.float16)\n",
    "\n",
    "\n",
    "\n",
    "# 添加该商品每月的平均价格，前三个月的\n",
    "group = sales_data.groupby( [\"date_block_num\",\"item_code\"] ).agg( {\"item_price\": [\"mean\"]} )\n",
    "group.columns = [\"date_item_avg_item_price\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = matrix.merge(group, on = [\"date_block_num\",\"item_code\"], how = \"left\")\n",
    "matrix[\"date_item_avg_item_price\"] = matrix.date_item_avg_item_price.astype(np.float16)\n",
    "lags = [1, 2, 3]\n",
    "matrix = lag_feature( matrix, lags, [\"date_item_avg_item_price\"] ) # 添加价格\n",
    "for i in lags: # 添加价格趋势\n",
    "    matrix[\"delta_price_lag_\" + str(i) ] = (matrix[\"date_item_avg_item_price_lag_\" + str(i)]- matrix[\"item_avg_item_price\"] )/ matrix[\"item_avg_item_price\"]\n",
    "\n",
    "\n",
    "def select_trends(row) :\n",
    "    for i in lags:\n",
    "        if row[\"delta_price_lag_\" + str(i)]:\n",
    "            return row[\"delta_price_lag_\" + str(i)]\n",
    "    return 0\n",
    "\n",
    "# 这个函数看起来像是，该商品有上一个月的趋势，就取上一个月的。有上上个月的，就取上上个月的。。。如果前三个月都没有销售的价格，那么就取0\n",
    "matrix[\"delta_price_lag\"] = matrix.apply(select_trends, axis = 1)\n",
    "matrix[\"delta_price_lag\"] = matrix.delta_price_lag.astype( np.float16 )\n",
    "matrix[\"delta_price_lag\"].fillna( 0 ,inplace = True)\n",
    "\n",
    "# 删除该商品的平均价格，商品的每月平均价格，和每月价格趋势。最后仅仅保留了一个delta_price_lag\n",
    "features_to_drop = [\"item_avg_item_price\", \"date_item_avg_item_price\"]\n",
    "for i in lags:\n",
    "    features_to_drop.append(\"date_item_avg_item_price_lag_\" + str(i) )\n",
    "    features_to_drop.append(\"delta_price_lag_\" + str(i) )\n",
    "matrix.drop(features_to_drop, axis = 1, inplace = True)\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58508ba6",
   "metadata": {},
   "source": [
    "* Add total shop revenue per month to matix df. \n",
    "* Add lag values of revenue per month.\n",
    "* Add delta revenue values - how current month revenue relates to global average.\n",
    "\n",
    "我们已经知道validation set和test set都会出现train set所没有的新商品，借助商店revenue趋势特征可以帮助算法预测新商品在现有店铺的销量\n",
    "\n",
    "该商店的特征同上，但是泰迪杯没有这样的商店列，所以可以用大类或者小类的商品销售趋势替代\n",
    "暂时用的是细类，其实可以优化为细类和地区的组合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 812,
   "id": "62622529",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>item_cnt_month</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>item_cnt_month_lag_1</th>\n",
       "      <th>item_cnt_month_lag_2</th>\n",
       "      <th>item_cnt_month_lag_3</th>\n",
       "      <th>date_avg_item_cnt_lag_1</th>\n",
       "      <th>...</th>\n",
       "      <th>date_region_avg_item_cnt_lag_1</th>\n",
       "      <th>date_region_avg_item_cnt_lag_2</th>\n",
       "      <th>date_region_avg_item_cnt_lag_3</th>\n",
       "      <th>date_region_item_avg_item_cnt_lag_1</th>\n",
       "      <th>date_region_item_avg_item_cnt_lag_2</th>\n",
       "      <th>date_region_item_avg_item_cnt_lag_3</th>\n",
       "      <th>date_region_subtype_avg_item_cnt_lag_1</th>\n",
       "      <th>date_region_subtype_avg_item_cnt_lag_2</th>\n",
       "      <th>date_region_subtype_avg_item_cnt_lag_3</th>\n",
       "      <th>delta_price_lag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20007</td>\n",
       "      <td>3092.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20008</td>\n",
       "      <td>308.0</td>\n",
       "      <td>303</td>\n",
       "      <td>401</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20009</td>\n",
       "      <td>260.0</td>\n",
       "      <td>306</td>\n",
       "      <td>407</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110310</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22066</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>7224.0</td>\n",
       "      <td>10536.0</td>\n",
       "      <td>5556.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>337.75</td>\n",
       "      <td>481.0</td>\n",
       "      <td>900.5</td>\n",
       "      <td>7224.0</td>\n",
       "      <td>10536.0</td>\n",
       "      <td>5556.0</td>\n",
       "      <td>433.750</td>\n",
       "      <td>815.00</td>\n",
       "      <td>1711.00</td>\n",
       "      <td>0.091797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110311</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22072</td>\n",
       "      <td>0.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "      <td>488.0</td>\n",
       "      <td>1842.0</td>\n",
       "      <td>1082.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>337.75</td>\n",
       "      <td>481.0</td>\n",
       "      <td>900.5</td>\n",
       "      <td>488.0</td>\n",
       "      <td>1842.0</td>\n",
       "      <td>1082.0</td>\n",
       "      <td>278.250</td>\n",
       "      <td>451.50</td>\n",
       "      <td>687.00</td>\n",
       "      <td>-0.006069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110312</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22075</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110313</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22083</td>\n",
       "      <td>0.0</td>\n",
       "      <td>303</td>\n",
       "      <td>401</td>\n",
       "      <td>1124.0</td>\n",
       "      <td>1344.0</td>\n",
       "      <td>7420.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>337.75</td>\n",
       "      <td>481.0</td>\n",
       "      <td>900.5</td>\n",
       "      <td>1124.0</td>\n",
       "      <td>1344.0</td>\n",
       "      <td>7420.0</td>\n",
       "      <td>217.375</td>\n",
       "      <td>187.75</td>\n",
       "      <td>660.00</td>\n",
       "      <td>-0.006493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110314</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22084</td>\n",
       "      <td>0.0</td>\n",
       "      <td>302</td>\n",
       "      <td>408</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>337.75</td>\n",
       "      <td>481.0</td>\n",
       "      <td>900.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>197.125</td>\n",
       "      <td>248.00</td>\n",
       "      <td>499.25</td>\n",
       "      <td>-0.010872</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>110315 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date_block_num  sales_region_code  item_code  item_cnt_month  \\\n",
       "0                    0                101      20003             0.0   \n",
       "1                    0                101      20006             0.0   \n",
       "2                    0                101      20007          3092.0   \n",
       "3                    0                101      20008           308.0   \n",
       "4                    0                101      20009           260.0   \n",
       "...                ...                ...        ...             ...   \n",
       "110310              40                105      22066             0.0   \n",
       "110311              40                105      22072             0.0   \n",
       "110312              40                105      22075             0.0   \n",
       "110313              40                105      22083             0.0   \n",
       "110314              40                105      22084             0.0   \n",
       "\n",
       "        first_cate_code  second_cate_code  item_cnt_month_lag_1  \\\n",
       "0                   301               405                   NaN   \n",
       "1                   307               403                   NaN   \n",
       "2                   305               412                   NaN   \n",
       "3                   303               401                   NaN   \n",
       "4                   306               407                   NaN   \n",
       "...                 ...               ...                   ...   \n",
       "110310              307               403                7224.0   \n",
       "110311              305               412                 488.0   \n",
       "110312              307               403                   NaN   \n",
       "110313              303               401                1124.0   \n",
       "110314              302               408                   0.0   \n",
       "\n",
       "        item_cnt_month_lag_2  item_cnt_month_lag_3  date_avg_item_cnt_lag_1  \\\n",
       "0                        NaN                   NaN                      NaN   \n",
       "1                        NaN                   NaN                      NaN   \n",
       "2                        NaN                   NaN                      NaN   \n",
       "3                        NaN                   NaN                      NaN   \n",
       "4                        NaN                   NaN                      NaN   \n",
       "...                      ...                   ...                      ...   \n",
       "110310               10536.0                5556.0                   300.75   \n",
       "110311                1842.0                1082.0                   300.75   \n",
       "110312                   NaN                   NaN                      NaN   \n",
       "110313                1344.0                7420.0                   300.75   \n",
       "110314                   0.0                  11.0                   300.75   \n",
       "\n",
       "        ...  date_region_avg_item_cnt_lag_1  date_region_avg_item_cnt_lag_2  \\\n",
       "0       ...                             NaN                             NaN   \n",
       "1       ...                             NaN                             NaN   \n",
       "2       ...                             NaN                             NaN   \n",
       "3       ...                             NaN                             NaN   \n",
       "4       ...                             NaN                             NaN   \n",
       "...     ...                             ...                             ...   \n",
       "110310  ...                          337.75                           481.0   \n",
       "110311  ...                          337.75                           481.0   \n",
       "110312  ...                             NaN                             NaN   \n",
       "110313  ...                          337.75                           481.0   \n",
       "110314  ...                          337.75                           481.0   \n",
       "\n",
       "        date_region_avg_item_cnt_lag_3  date_region_item_avg_item_cnt_lag_1  \\\n",
       "0                                  NaN                                  NaN   \n",
       "1                                  NaN                                  NaN   \n",
       "2                                  NaN                                  NaN   \n",
       "3                                  NaN                                  NaN   \n",
       "4                                  NaN                                  NaN   \n",
       "...                                ...                                  ...   \n",
       "110310                           900.5                               7224.0   \n",
       "110311                           900.5                                488.0   \n",
       "110312                             NaN                                  NaN   \n",
       "110313                           900.5                               1124.0   \n",
       "110314                           900.5                                  0.0   \n",
       "\n",
       "        date_region_item_avg_item_cnt_lag_2  \\\n",
       "0                                       NaN   \n",
       "1                                       NaN   \n",
       "2                                       NaN   \n",
       "3                                       NaN   \n",
       "4                                       NaN   \n",
       "...                                     ...   \n",
       "110310                              10536.0   \n",
       "110311                               1842.0   \n",
       "110312                                  NaN   \n",
       "110313                               1344.0   \n",
       "110314                                  0.0   \n",
       "\n",
       "        date_region_item_avg_item_cnt_lag_3  \\\n",
       "0                                       NaN   \n",
       "1                                       NaN   \n",
       "2                                       NaN   \n",
       "3                                       NaN   \n",
       "4                                       NaN   \n",
       "...                                     ...   \n",
       "110310                               5556.0   \n",
       "110311                               1082.0   \n",
       "110312                                  NaN   \n",
       "110313                               7420.0   \n",
       "110314                                 11.0   \n",
       "\n",
       "        date_region_subtype_avg_item_cnt_lag_1  \\\n",
       "0                                          NaN   \n",
       "1                                          NaN   \n",
       "2                                          NaN   \n",
       "3                                          NaN   \n",
       "4                                          NaN   \n",
       "...                                        ...   \n",
       "110310                                 433.750   \n",
       "110311                                 278.250   \n",
       "110312                                     NaN   \n",
       "110313                                 217.375   \n",
       "110314                                 197.125   \n",
       "\n",
       "        date_region_subtype_avg_item_cnt_lag_2  \\\n",
       "0                                          NaN   \n",
       "1                                          NaN   \n",
       "2                                          NaN   \n",
       "3                                          NaN   \n",
       "4                                          NaN   \n",
       "...                                        ...   \n",
       "110310                                  815.00   \n",
       "110311                                  451.50   \n",
       "110312                                     NaN   \n",
       "110313                                  187.75   \n",
       "110314                                  248.00   \n",
       "\n",
       "        date_region_subtype_avg_item_cnt_lag_3  delta_price_lag  \n",
       "0                                          NaN         0.000000  \n",
       "1                                          NaN         0.000000  \n",
       "2                                          NaN         0.000000  \n",
       "3                                          NaN         0.000000  \n",
       "4                                          NaN         0.000000  \n",
       "...                                        ...              ...  \n",
       "110310                                 1711.00         0.091797  \n",
       "110311                                  687.00        -0.006069  \n",
       "110312                                     NaN         0.000000  \n",
       "110313                                  660.00        -0.006493  \n",
       "110314                                  499.25        -0.010872  \n",
       "\n",
       "[110315 rows x 23 columns]"
      ]
     },
     "execution_count": 812,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 813,
   "id": "5c7ddaf1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "delta_revenue\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.19178986549377441"
      ]
     },
     "execution_count": 813,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "group = sales_data.groupby( [\"date_block_num\",\"second_cate_code\"] ).agg({\"revenue\": [\"sum\"] })\n",
    "group.columns = [\"date_subtype_revenue\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = matrix.merge( group , on = [\"date_block_num\", \"second_cate_code\"], how = \"left\" )\n",
    "matrix['date_subtype_revenue'] = matrix['date_subtype_revenue'].astype(np.float32)\n",
    "\n",
    "group = group.groupby([\"second_cate_code\"]).agg({ \"date_block_num\":[\"mean\"] })\n",
    "group.columns = [\"subtype_avg_revenue\"]\n",
    "group.reset_index(inplace = True )\n",
    "\n",
    "matrix = matrix.merge( group, on = [\"second_cate_code\"], how = \"left\" )\n",
    "matrix[\"subtype_avg_revenue\"] = matrix.subtype_avg_revenue.astype(np.float32)\n",
    "matrix[\"delta_revenue\"] = (matrix['date_subtype_revenue'] - matrix['subtype_avg_revenue']) / matrix['subtype_avg_revenue']\n",
    "matrix[\"delta_revenue\"] = matrix[\"delta_revenue\"]. astype(np.float32)\n",
    "\n",
    "matrix = lag_feature(matrix, [1], [\"delta_revenue\"])\n",
    "matrix[\"delta_revenue_lag_1\"] = matrix[\"delta_revenue_lag_1\"].astype(np.float32)\n",
    "matrix.drop( [\"date_subtype_revenue\", \"subtype_avg_revenue\", \"delta_revenue\"] ,axis = 1, inplace = True)\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e05bc5ac",
   "metadata": {},
   "source": [
    "- 添加一些线上线下特征\n",
    "- 添加一些销量趋势特征\n",
    "- 尝试对商品进行聚类\n",
    "   - 做一些这一类都有的特征\n",
    "   - 或者按类建模预测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 814,
   "id": "7167ca31",
   "metadata": {},
   "outputs": [],
   "source": [
    "# sales_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 815,
   "id": "a8d8d2e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date_item_avg_item_cnt_\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1.3601388931274414"
      ]
     },
     "execution_count": 815,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "# 添加该商品的平均销量\n",
    "# 以下为了避免重复，使用了date_item_avg_item_cnt_，最后多加了一个_\n",
    "group = sales_data.groupby( [\"item_code\"] ).agg({\"ord_qty\": [\"mean\"]})\n",
    "group.columns = [\"item_avg_item_cnt\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = matrix.merge( group, on = [\"item_code\"], how = \"left\" )\n",
    "matrix[\"item_avg_item_cnt\"] = matrix.item_avg_item_cnt.astype(np.float16)\n",
    "\n",
    "\n",
    "\n",
    "# 添加该商品每月的平均销量，前三个月的\n",
    "group = sales_data.groupby( [\"date_block_num\",\"item_code\"] ).agg({\"ord_qty\": [\"mean\"]})\n",
    "group.columns = [\"date_item_avg_item_cnt_\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = matrix.merge(group, on = [\"date_block_num\",\"item_code\"], how = \"left\")\n",
    "matrix[\"date_item_avg_item_cnt_\"] = matrix.date_item_avg_item_cnt_.astype(np.float16)\n",
    "lags = [1, 2, 3]\n",
    "matrix = lag_feature( matrix, lags, [\"date_item_avg_item_cnt_\"] ) # 添加销量\n",
    "for i in lags: # 添加销量趋势\n",
    "    matrix[\"delta_cnt_lag_\" + str(i) ] = (matrix[\"date_item_avg_item_cnt__lag_\" + str(i)]- matrix[\"item_avg_item_cnt\"] )/ matrix[\"item_avg_item_cnt\"]\n",
    "# 注意这里有两个__,为了不与别的特征名字重复\n",
    "    \n",
    "def select_trends(row) :\n",
    "    for i in lags:\n",
    "        if row[\"delta_cnt_lag_\" + str(i)]:\n",
    "            return row[\"delta_cnt_lag_\" + str(i)]\n",
    "    return 0\n",
    "\n",
    "# 这个函数看起来像是，该商品有上一个月的趋势，就取上一个月的。有上上个月的，就取上上个月的。。。如果前三个月都没有销售的价格，那么就取0\n",
    "matrix[\"delta_cnt_lag\"] = matrix.apply(select_trends, axis = 1)\n",
    "matrix[\"delta_cnt_lag\"] = matrix.delta_cnt_lag.astype( np.float16 )\n",
    "matrix[\"delta_cnt_lag\"].fillna( 0 ,inplace = True)\n",
    "\n",
    "# 删除该商品的平均价格，商品的每月平均价格，和每月价格趋势。最后仅仅保留了一个delta_cnt_lag\n",
    "features_to_drop = [\"item_avg_item_cnt\", \"date_item_avg_item_cnt_\"]\n",
    "for i in lags:\n",
    "    features_to_drop.append(\"date_item_avg_item_cnt__lag_\" + str(i) )\n",
    "    features_to_drop.append(\"delta_cnt_lag_\" + str(i) )\n",
    "matrix.drop(features_to_drop, axis = 1, inplace = True)\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 816,
   "id": "a9a0d0fe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date_item_avg_item_cnt_\n"
     ]
    }
   ],
   "source": [
    "# 尝试添加一下一年前同月的销量特征\n",
    "ts = time.time()\n",
    "# 添加该商品的平均销量\n",
    "# 以下为了避免重复，使用了date_item_avg_item_cnt_，最后多加了一个_\n",
    "group = sales_data.groupby( [\"second_cate_code\"] ).agg({\"ord_qty\": [\"mean\"]})\n",
    "group.columns = [\"item_avg_item_cnt\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = matrix.merge( group, on = [\"second_cate_code\"], how = \"left\" )\n",
    "matrix[\"item_avg_item_cnt\"] = matrix.item_avg_item_cnt.astype(np.float16)\n",
    "\n",
    "\n",
    "\n",
    "# 添加该商品每月的平均销量，前三个月的\n",
    "group = sales_data.groupby( [\"date_block_num\",\"second_cate_code\"] ).agg({\"ord_qty\": [\"mean\"]})\n",
    "group.columns = [\"date_item_avg_item_cnt_\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = matrix.merge(group, on = [\"date_block_num\",\"second_cate_code\"], how = \"left\")\n",
    "matrix[\"date_item_avg_item_cnt_\"] = matrix.date_item_avg_item_cnt_.astype(np.float16)\n",
    "lags = [11, 12, 13]\n",
    "matrix = lag_feature( matrix, lags, [\"date_item_avg_item_cnt_\"] ) # 添加销量\n",
    "for i in lags: # 添加销量趋势\n",
    "    matrix[\"delta_cnt_lag_\" + str(i) ] = (matrix[\"date_item_avg_item_cnt__lag_\" + str(i)]- matrix[\"item_avg_item_cnt\"] )/ matrix[\"item_avg_item_cnt\"]\n",
    "# 注意这里有两个__,为了不与别的特征名字重复\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 817,
   "id": "443f98ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date_block_num', 'sales_region_code', 'item_code', 'item_cnt_month',\n",
       "       'first_cate_code', 'second_cate_code', 'item_cnt_month_lag_1',\n",
       "       'item_cnt_month_lag_2', 'item_cnt_month_lag_3',\n",
       "       'date_avg_item_cnt_lag_1', 'date_item_avg_item_cnt_lag_1',\n",
       "       'date_item_avg_item_cnt_lag_2', 'date_item_avg_item_cnt_lag_3',\n",
       "       'date_region_avg_item_cnt_lag_1', 'date_region_avg_item_cnt_lag_2',\n",
       "       'date_region_avg_item_cnt_lag_3', 'date_region_item_avg_item_cnt_lag_1',\n",
       "       'date_region_item_avg_item_cnt_lag_2',\n",
       "       'date_region_item_avg_item_cnt_lag_3',\n",
       "       'date_region_subtype_avg_item_cnt_lag_1',\n",
       "       'date_region_subtype_avg_item_cnt_lag_2',\n",
       "       'date_region_subtype_avg_item_cnt_lag_3', 'delta_price_lag',\n",
       "       'delta_revenue_lag_1', 'delta_cnt_lag', 'item_avg_item_cnt',\n",
       "       'date_item_avg_item_cnt_', 'date_item_avg_item_cnt__lag_11',\n",
       "       'date_item_avg_item_cnt__lag_12', 'date_item_avg_item_cnt__lag_13',\n",
       "       'delta_cnt_lag_11', 'delta_cnt_lag_12', 'delta_cnt_lag_13'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 817,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matrix.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 818,
   "id": "4e10d5c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "delta_subtype_cnt\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "6.984795093536377"
      ]
     },
     "execution_count": 818,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 类别价格一个月前和历史同期变化的趋势\n",
    "ts = time.time()\n",
    "group = sales_data.groupby( [\"date_block_num\",\"second_cate_code\"] ).agg({\"ord_qty\": [\"mean\"] })\n",
    "group.columns = [\"date_subtype_cnt\"]\n",
    "group.reset_index(inplace = True)\n",
    "\n",
    "matrix = matrix.merge( group , on = [\"date_block_num\", \"second_cate_code\"], how = \"left\" )\n",
    "matrix['date_subtype_cnt'] = matrix['date_subtype_cnt'].astype(np.float32)\n",
    "\n",
    "group = group.groupby([\"second_cate_code\"]).agg({ \"date_block_num\":[\"mean\"] })\n",
    "group.columns = [\"subtype_avg_cnt\"]\n",
    "group.reset_index(inplace = True )\n",
    "\n",
    "matrix = matrix.merge( group, on = [\"second_cate_code\"], how = \"left\" )\n",
    "matrix[\"subtype_avg_cnt\"] = matrix.subtype_avg_cnt.astype(np.float32)\n",
    "matrix[\"delta_subtype_cnt\"] = (matrix['date_subtype_cnt'] - matrix['subtype_avg_cnt']) / matrix['subtype_avg_cnt']\n",
    "matrix[\"delta_subtype_cnt\"] = matrix[\"delta_subtype_cnt\"]. astype(np.float32)\n",
    "\n",
    "# matrix = lag_feature(matrix, [1], [\"delta_subtype_cnt\"])\n",
    "# matrix[\"delta_subtype_cnt_lag_1\"] = matrix[\"delta_subtype_cnt_lag_1\"].astype(np.float32)\n",
    "\n",
    "matrix = lag_cate_feature(matrix, [12], [\"delta_subtype_cnt\"])\n",
    "matrix[\"delta_subtype_cnt_lag_12\"] = matrix[\"delta_subtype_cnt_lag_12\"].astype(np.float32)\n",
    "\n",
    "matrix.drop( [\"date_subtype_cnt\", \"subtype_avg_cnt\", \"delta_subtype_cnt\"] ,axis = 1, inplace = True)\n",
    "\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 819,
   "id": "cc2f4748",
   "metadata": {},
   "outputs": [],
   "source": [
    "matrix.drop_duplicates(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 820,
   "id": "24f6dfda",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 尝试添加一下一年前同月的销量特征\n",
    "# ts = time.time()\n",
    "# # 添加该商品的平均销量\n",
    "# # 以下为了避免重复，使用了date_item_avg_item_cnt_，最后多加了一个_\n",
    "# group = sales_data.groupby( [\"second_cate_code\"] ).agg({\"ord_qty\": [\"mean\"]})\n",
    "# group.columns = [\"item_avg_item_cnt\"]\n",
    "# group.reset_index(inplace = True)\n",
    "\n",
    "# matrix = matrix.merge( group, on = [\"second_cate_code\"], how = \"left\" )\n",
    "# matrix[\"item_avg_item_cnt\"] = matrix.item_avg_item_cnt.astype(np.float16)\n",
    "\n",
    "\n",
    "\n",
    "# # 添加该商品每月的平均销量，前三个月的\n",
    "# group = sales_data.groupby( [\"date_block_num\",\"second_cate_code\"] ).agg({\"ord_qty\": [\"mean\"]})\n",
    "# group.columns = [\"date_item_avg_item_cnt_\"]\n",
    "# group.reset_index(inplace = True)\n",
    "\n",
    "# matrix = matrix.merge(group, on = [\"date_block_num\",\"second_cate_code\"], how = \"left\")\n",
    "# matrix[\"date_item_avg_item_cnt_\"] = matrix.date_item_avg_item_cnt_.astype(np.float16)\n",
    "# lags = [11, 12, 13]\n",
    "# matrix = lag_feature( matrix, lags, [\"date_item_avg_item_cnt_\"] ) # 添加销量\n",
    "# for i in lags: # 添加销量趋势\n",
    "#     matrix[\"delta_cnt_lag_\" + str(i) ] = (matrix[\"date_item_avg_item_cnt__lag_\" + str(i)]- matrix[\"item_avg_item_cnt\"] )/ matrix[\"item_avg_item_cnt\"]\n",
    "# # 注意这里有两个__,为了不与别的特征名字重复\n",
    "    \n",
    "# def select_trends(row) :\n",
    "#     for i in lags:\n",
    "#         if row[\"delta_cnt_lag_\" + str(i)]:\n",
    "#             return row[\"delta_cnt_lag_\" + str(i)]\n",
    "\n",
    "# # 该商品有上一个月的趋势，就取上一个月的。有上上个月的，就取上上个月的。。。如果前三个月都没有销售的价格，那么就取0\n",
    "# matrix[\"delta_cnt_lag__12\"] = matrix.apply(select_trends, axis = 1)\n",
    "# matrix[\"delta_cnt_lag__12\"] = matrix.delta_cnt_lag__12.astype( np.float16 )\n",
    "# matrix[\"delta_cnt_lag__12\"].fillna( 0 ,inplace = True)\n",
    "\n",
    "# # 删除该商品的平均价格，商品的每月平均价格，和每月价格趋势。最后仅仅保留了一个delta_cnt_lag__12\n",
    "# features_to_drop = [\"item_avg_item_cnt\", \"date_item_avg_item_cnt_\"]\n",
    "# for i in lags:\n",
    "#     features_to_drop.append(\"date_item_avg_item_cnt__lag_\" + str(i) )\n",
    "#     features_to_drop.append(\"delta_cnt_lag_\" + str(i) )\n",
    "# matrix.drop(features_to_drop, axis = 1, inplace = True)\n",
    "# time.time() - ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2594932b",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f6744c5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "13cbc8df",
   "metadata": {},
   "source": [
    "- 添加线上线下特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 821,
   "id": "fe916c83",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date_block_num', 'sales_region_code', 'item_code', 'item_cnt_month',\n",
       "       'first_cate_code', 'second_cate_code', 'item_cnt_month_lag_1',\n",
       "       'item_cnt_month_lag_2', 'item_cnt_month_lag_3',\n",
       "       'date_avg_item_cnt_lag_1', 'date_item_avg_item_cnt_lag_1',\n",
       "       'date_item_avg_item_cnt_lag_2', 'date_item_avg_item_cnt_lag_3',\n",
       "       'date_region_avg_item_cnt_lag_1', 'date_region_avg_item_cnt_lag_2',\n",
       "       'date_region_avg_item_cnt_lag_3', 'date_region_item_avg_item_cnt_lag_1',\n",
       "       'date_region_item_avg_item_cnt_lag_2',\n",
       "       'date_region_item_avg_item_cnt_lag_3',\n",
       "       'date_region_subtype_avg_item_cnt_lag_1',\n",
       "       'date_region_subtype_avg_item_cnt_lag_2',\n",
       "       'date_region_subtype_avg_item_cnt_lag_3', 'delta_price_lag',\n",
       "       'delta_revenue_lag_1', 'delta_cnt_lag', 'item_avg_item_cnt',\n",
       "       'date_item_avg_item_cnt_', 'date_item_avg_item_cnt__lag_11',\n",
       "       'date_item_avg_item_cnt__lag_12', 'date_item_avg_item_cnt__lag_13',\n",
       "       'delta_cnt_lag_11', 'delta_cnt_lag_12', 'delta_cnt_lag_13',\n",
       "       'delta_subtype_cnt_lag_12'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 821,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "matrix.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44869c9f",
   "metadata": {},
   "source": [
    "- Add month and number of days in each month to matrix df.\n",
    "\n",
    "添加月份、和该月有多少天的两个特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 822,
   "id": "8560c680",
   "metadata": {},
   "outputs": [],
   "source": [
    "matrix[\"month\"] = (matrix[\"date_block_num\"]+9) % 12 # 9个月开始\n",
    "days = pd.Series([31,28,31,30,31,30,31,31,30,31,30,31])\n",
    "matrix[\"days\"] = matrix[\"month\"].map(days).astype(np.int8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccd9b5c6",
   "metadata": {},
   "source": [
    "- 是否为促销月"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 823,
   "id": "bf346137",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1月、6月、10月标记为促销月\n",
    "matrix['is_promotion_month'] = ((matrix['month'] % 12 == 1) | (matrix['month'] % 12 == 6) | (matrix['month'] % 12 == 11)).astype(int)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f742e616",
   "metadata": {},
   "source": [
    "- 添加线上线下特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1596fd5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 824,
   "id": "a4449a70",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 对新的DataFrame进行分组，并计算每个分组的销售渠道编码的众数\n",
    "grouped = sales_data.groupby(['date_block_num', 'sales_region_code', 'item_code', 'first_cate_code', 'second_cate_code'])\n",
    "chan_mode = grouped['sales_chan_name'].apply(lambda x: x.mode()[0]).reset_index()\n",
    "chan_mode_1 = chan_mode.copy()\n",
    "chan_mode_1.rename(columns={'sales_chan_name': 'date_item_chan_class'}, inplace=True)\n",
    "del  chan_mode_1[\"date_block_num\"]\n",
    "del  chan_mode_1[\"first_cate_code\"]\n",
    "del  chan_mode_1[\"second_cate_code\"]\n",
    "chan_mode_1 = chan_mode_1.drop_duplicates()\n",
    "# 将计算得到的销售渠道特征与matrix合并\n",
    "matrix = pd.merge(matrix, chan_mode_1, on=['sales_region_code', 'item_code'], how='left')\n",
    "\n",
    "# 对于有些啥也没有的新品，可以根据细类和地区的组合给他敷上\n",
    "chan_mode_2 = chan_mode.copy()\n",
    "chan_mode_2.rename(columns={'sales_chan_name': 'new_date_item_chan_class'}, inplace=True)\n",
    "del  chan_mode_2[\"date_block_num\"]\n",
    "del  chan_mode_2[\"first_cate_code\"]\n",
    "del  chan_mode_2[\"item_code\"]\n",
    "\n",
    "chan_mode_2 = chan_mode_2.drop_duplicates()\n",
    "# 将计算得到的销售渠道特征与matrix合并\n",
    "matrix = pd.merge(matrix, chan_mode_2, on=['sales_region_code', 'second_cate_code'], how='left')\n",
    "\n",
    "# 假设第 i 列为 new_date_item_chan_class，第 j 列为 date_item_chan_class\n",
    "i = 'new_date_item_chan_class'\n",
    "j = 'date_item_chan_class'\n",
    "\n",
    "# 判断 date_item_chan_class 是否为空值\n",
    "is_na = matrix[j].isna()\n",
    "\n",
    "# 使用 where 函数按照需求合并两列数据\n",
    "matrix[j] = np.where(is_na, matrix[i], matrix[j])\n",
    "del matrix[\"new_date_item_chan_class\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6331a7cd",
   "metadata": {},
   "source": [
    "Add the month of each shop and item first sale.\n",
    "\n",
    "'item_subtype_last_sale'和'item_subtype_sale'用以纪录距离最近一次销售之间隔了几个月，通过它可以和之前月份的数据建立关联。'item_subtype_first_sale'和'item_first_sale'则是用于表示新商品的特征\n",
    "\n",
    "该商品从第一个销售到现在有几个月了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 825,
   "id": "fb01e770",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.025172948837280273"
      ]
     },
     "execution_count": 825,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "matrix[\"item_subtype_first_sale\"] = matrix[\"date_block_num\"] - matrix.groupby([\"item_code\",\"second_cate_code\"])[\"date_block_num\"].transform('min')\n",
    "matrix[\"item_first_sale\"] = matrix[\"date_block_num\"] - matrix.groupby([\"item_code\"])[\"date_block_num\"].transform('min')\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7a4de36",
   "metadata": {},
   "source": [
    "# 新品特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 826,
   "id": "db10fe68",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         1\n",
       "1         1\n",
       "2         1\n",
       "3         1\n",
       "4         1\n",
       "         ..\n",
       "270380    1\n",
       "270381    0\n",
       "270382    0\n",
       "270383    0\n",
       "270384    0\n",
       "Name: is_brand_new, Length: 270385, dtype: int64"
      ]
     },
     "execution_count": 826,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 该月该商品是否为新品\n",
    "matrix['is_brand_new'] = (matrix['item_first_sale']  < 5).astype(int)\n",
    "matrix['is_brand_new']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 827,
   "id": "9cf18519",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>item_cnt_month</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>item_cnt_month_lag_1</th>\n",
       "      <th>item_cnt_month_lag_2</th>\n",
       "      <th>item_cnt_month_lag_3</th>\n",
       "      <th>date_avg_item_cnt_lag_1</th>\n",
       "      <th>...</th>\n",
       "      <th>delta_cnt_lag_12</th>\n",
       "      <th>delta_cnt_lag_13</th>\n",
       "      <th>delta_subtype_cnt_lag_12</th>\n",
       "      <th>month</th>\n",
       "      <th>days</th>\n",
       "      <th>is_promotion_month</th>\n",
       "      <th>date_item_chan_class</th>\n",
       "      <th>item_subtype_first_sale</th>\n",
       "      <th>item_first_sale</th>\n",
       "      <th>is_brand_new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>301</td>\n",
       "      <td>405</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>101</td>\n",
       "      <td>20006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>31</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270342</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22030</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>37.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.874252</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270377</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22072</td>\n",
       "      <td>0.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "      <td>488.0</td>\n",
       "      <td>1842.0</td>\n",
       "      <td>1082.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.499974</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270378</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22072</td>\n",
       "      <td>0.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "      <td>488.0</td>\n",
       "      <td>1842.0</td>\n",
       "      <td>1082.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.499974</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270379</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22075</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.874252</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270380</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22075</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.874252</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>72836 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date_block_num  sales_region_code  item_code  item_cnt_month  \\\n",
       "0                    0                101      20003             0.0   \n",
       "1                    0                101      20003             0.0   \n",
       "2                    0                101      20003             0.0   \n",
       "3                    0                101      20003             0.0   \n",
       "4                    0                101      20006             0.0   \n",
       "...                ...                ...        ...             ...   \n",
       "270342              40                105      22030             0.0   \n",
       "270377              40                105      22072             0.0   \n",
       "270378              40                105      22072             0.0   \n",
       "270379              40                105      22075             0.0   \n",
       "270380              40                105      22075             0.0   \n",
       "\n",
       "        first_cate_code  second_cate_code  item_cnt_month_lag_1  \\\n",
       "0                   301               405                   NaN   \n",
       "1                   301               405                   NaN   \n",
       "2                   301               405                   NaN   \n",
       "3                   301               405                   NaN   \n",
       "4                   307               403                   NaN   \n",
       "...                 ...               ...                   ...   \n",
       "270342              307               403                  37.0   \n",
       "270377              305               412                 488.0   \n",
       "270378              305               412                 488.0   \n",
       "270379              307               403                   NaN   \n",
       "270380              307               403                   NaN   \n",
       "\n",
       "        item_cnt_month_lag_2  item_cnt_month_lag_3  date_avg_item_cnt_lag_1  \\\n",
       "0                        NaN                   NaN                      NaN   \n",
       "1                        NaN                   NaN                      NaN   \n",
       "2                        NaN                   NaN                      NaN   \n",
       "3                        NaN                   NaN                      NaN   \n",
       "4                        NaN                   NaN                      NaN   \n",
       "...                      ...                   ...                      ...   \n",
       "270342                  34.0                   0.0                   300.75   \n",
       "270377                1842.0                1082.0                   300.75   \n",
       "270378                1842.0                1082.0                   300.75   \n",
       "270379                   NaN                   NaN                      NaN   \n",
       "270380                   NaN                   NaN                      NaN   \n",
       "\n",
       "        ...  delta_cnt_lag_12  delta_cnt_lag_13  delta_subtype_cnt_lag_12  \\\n",
       "0       ...               NaN               NaN                       NaN   \n",
       "1       ...               NaN               NaN                       NaN   \n",
       "2       ...               NaN               NaN                       NaN   \n",
       "3       ...               NaN               NaN                       NaN   \n",
       "4       ...               NaN               NaN                       NaN   \n",
       "...     ...               ...               ...                       ...   \n",
       "270342  ...               NaN               NaN                  6.874252   \n",
       "270377  ...               NaN               NaN                  3.499974   \n",
       "270378  ...               NaN               NaN                  3.499974   \n",
       "270379  ...               NaN               NaN                  6.874252   \n",
       "270380  ...               NaN               NaN                  6.874252   \n",
       "\n",
       "        month  days  is_promotion_month  date_item_chan_class  \\\n",
       "0           9    31                   0                   1.0   \n",
       "1           9    31                   0                   1.0   \n",
       "2           9    31                   0                   0.0   \n",
       "3           9    31                   0                   0.0   \n",
       "4           9    31                   0                   1.0   \n",
       "...       ...   ...                 ...                   ...   \n",
       "270342      1    28                   1                   1.0   \n",
       "270377      1    28                   1                   0.0   \n",
       "270378      1    28                   1                   0.0   \n",
       "270379      1    28                   1                   0.0   \n",
       "270380      1    28                   1                   1.0   \n",
       "\n",
       "        item_subtype_first_sale  item_first_sale  is_brand_new  \n",
       "0                             0                0             1  \n",
       "1                             0                0             1  \n",
       "2                             0                0             1  \n",
       "3                             0                0             1  \n",
       "4                             0                0             1  \n",
       "...                         ...              ...           ...  \n",
       "270342                        3                3             1  \n",
       "270377                        4                4             1  \n",
       "270378                        4                4             1  \n",
       "270379                        0                0             1  \n",
       "270380                        0                0             1  \n",
       "\n",
       "[72836 rows x 41 columns]"
      ]
     },
     "execution_count": 827,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_new_df = matrix[matrix['is_brand_new']==1]\n",
    "brand_new_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 828,
   "id": "08410f51",
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(brand_new_df.columns)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "470e21d7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 829,
   "id": "7f8f7562",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加该类产品第一次销售的平均销量\n",
    "# 筛选出所有在第38个月之前首次出现的商品，并计算首次销量\n",
    "first_sale = brand_new_df[brand_new_df['date_block_num'] < 38].groupby(['sales_region_code', 'second_cate_code']).agg({'date_block_num': 'min', 'item_cnt_month': 'sum'}).reset_index()\n",
    "first_sale = first_sale.rename(columns={'date_block_num': 'first_month', 'item_cnt_month': 'first_sale'})\n",
    "\n",
    "# 计算每个商品的首次平均销量\n",
    "mean_first_sale = first_sale.groupby(['sales_region_code', 'second_cate_code'])['first_sale'].mean().reset_index()\n",
    "mean_first_sale = mean_first_sale[np.isfinite(mean_first_sale['first_sale'])]\n",
    "mean_first_sale[\"mean_first_sale\"]  = mean_first_sale[\"first_sale\"]\n",
    "del mean_first_sale[\"first_sale\"]\n",
    "# 将首次平均销量添加到matrix表格中对应的行中\n",
    "brand_new_df = brand_new_df.merge(mean_first_sale, on=['sales_region_code', 'second_cate_code'], how='left')\n",
    "brand_new_df['mean_first_sale'] = brand_new_df['mean_first_sale'].fillna(0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 830,
   "id": "95b7b22d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6435"
      ]
     },
     "execution_count": 830,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(brand_new_df['mean_first_sale']!=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e51ff0f6",
   "metadata": {},
   "source": [
    "Delete first three months from matrix. They don't have lag values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 831,
   "id": "754b950a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.016761064529418945"
      ]
     },
     "execution_count": 831,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ts = time.time()\n",
    "brand_new_df = brand_new_df[brand_new_df[\"date_block_num\"] > 12]\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 832,
   "id": "cc7403d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>39190</th>\n",
       "      <th>39191</th>\n",
       "      <th>39192</th>\n",
       "      <th>39193</th>\n",
       "      <th>39194</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>date_block_num</th>\n",
       "      <td>1.300000e+01</td>\n",
       "      <td>1.300000e+01</td>\n",
       "      <td>1.300000e+01</td>\n",
       "      <td>1.300000e+01</td>\n",
       "      <td>1.300000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sales_region_code</th>\n",
       "      <td>1.010000e+02</td>\n",
       "      <td>1.010000e+02</td>\n",
       "      <td>1.010000e+02</td>\n",
       "      <td>1.010000e+02</td>\n",
       "      <td>1.010000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_code</th>\n",
       "      <td>2.001500e+04</td>\n",
       "      <td>2.001500e+04</td>\n",
       "      <td>2.002100e+04</td>\n",
       "      <td>2.002100e+04</td>\n",
       "      <td>2.004200e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_cnt_month</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>7.300000e+01</td>\n",
       "      <td>7.300000e+01</td>\n",
       "      <td>4.300000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>first_cate_code</th>\n",
       "      <td>3.080000e+02</td>\n",
       "      <td>3.080000e+02</td>\n",
       "      <td>3.050000e+02</td>\n",
       "      <td>3.050000e+02</td>\n",
       "      <td>3.060000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>second_cate_code</th>\n",
       "      <td>4.040000e+02</td>\n",
       "      <td>4.040000e+02</td>\n",
       "      <td>4.120000e+02</td>\n",
       "      <td>4.120000e+02</td>\n",
       "      <td>4.070000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_cnt_month_lag_1</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>5.700000e+01</td>\n",
       "      <td>5.700000e+01</td>\n",
       "      <td>7.300000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_cnt_month_lag_2</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2.490000e+02</td>\n",
       "      <td>2.490000e+02</td>\n",
       "      <td>1.980000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_cnt_month_lag_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.520000e+02</td>\n",
       "      <td>4.520000e+02</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_avg_item_cnt_lag_1</th>\n",
       "      <td>3.155000e+02</td>\n",
       "      <td>3.155000e+02</td>\n",
       "      <td>3.155000e+02</td>\n",
       "      <td>3.155000e+02</td>\n",
       "      <td>3.155000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_item_avg_item_cnt_lag_1</th>\n",
       "      <td>1.039844e+01</td>\n",
       "      <td>1.039844e+01</td>\n",
       "      <td>3.240625e+01</td>\n",
       "      <td>3.240625e+01</td>\n",
       "      <td>1.677500e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_item_avg_item_cnt_lag_2</th>\n",
       "      <td>1.400000e+01</td>\n",
       "      <td>1.400000e+01</td>\n",
       "      <td>1.350000e+02</td>\n",
       "      <td>1.350000e+02</td>\n",
       "      <td>2.695000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_item_avg_item_cnt_lag_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.486250e+02</td>\n",
       "      <td>1.486250e+02</td>\n",
       "      <td>1.048125e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_region_avg_item_cnt_lag_1</th>\n",
       "      <td>2.727500e+02</td>\n",
       "      <td>2.727500e+02</td>\n",
       "      <td>2.727500e+02</td>\n",
       "      <td>2.727500e+02</td>\n",
       "      <td>2.727500e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_region_avg_item_cnt_lag_2</th>\n",
       "      <td>3.897500e+02</td>\n",
       "      <td>3.897500e+02</td>\n",
       "      <td>3.897500e+02</td>\n",
       "      <td>3.897500e+02</td>\n",
       "      <td>3.897500e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_region_avg_item_cnt_lag_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.306250e+02</td>\n",
       "      <td>2.306250e+02</td>\n",
       "      <td>2.306250e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_region_item_avg_item_cnt_lag_1</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>5.700000e+01</td>\n",
       "      <td>5.700000e+01</td>\n",
       "      <td>7.300000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_region_item_avg_item_cnt_lag_2</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>2.490000e+02</td>\n",
       "      <td>2.490000e+02</td>\n",
       "      <td>1.980000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_region_item_avg_item_cnt_lag_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.520000e+02</td>\n",
       "      <td>4.520000e+02</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_region_subtype_avg_item_cnt_lag_1</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>5.185000e+02</td>\n",
       "      <td>5.185000e+02</td>\n",
       "      <td>4.045000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_region_subtype_avg_item_cnt_lag_2</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>5.470000e+02</td>\n",
       "      <td>5.470000e+02</td>\n",
       "      <td>7.865000e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_region_subtype_avg_item_cnt_lag_3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.522500e+02</td>\n",
       "      <td>2.522500e+02</td>\n",
       "      <td>4.017500e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delta_price_lag</th>\n",
       "      <td>4.205322e-02</td>\n",
       "      <td>4.205322e-02</td>\n",
       "      <td>-9.284973e-03</td>\n",
       "      <td>-9.284973e-03</td>\n",
       "      <td>-1.345062e-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delta_revenue_lag_1</th>\n",
       "      <td>4.362204e+06</td>\n",
       "      <td>4.362204e+06</td>\n",
       "      <td>3.117494e+06</td>\n",
       "      <td>3.117494e+06</td>\n",
       "      <td>1.362260e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delta_cnt_lag</th>\n",
       "      <td>1.444092e-01</td>\n",
       "      <td>1.444092e-01</td>\n",
       "      <td>-4.331055e-01</td>\n",
       "      <td>-4.331055e-01</td>\n",
       "      <td>-1.580811e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_avg_item_cnt</th>\n",
       "      <td>1.101250e+02</td>\n",
       "      <td>1.101250e+02</td>\n",
       "      <td>8.300000e+01</td>\n",
       "      <td>8.300000e+01</td>\n",
       "      <td>1.056250e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_item_avg_item_cnt_</th>\n",
       "      <td>1.395000e+02</td>\n",
       "      <td>1.395000e+02</td>\n",
       "      <td>1.102500e+02</td>\n",
       "      <td>1.102500e+02</td>\n",
       "      <td>1.412500e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_item_avg_item_cnt__lag_11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_item_avg_item_cnt__lag_12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_item_avg_item_cnt__lag_13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delta_cnt_lag_11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delta_cnt_lag_12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delta_cnt_lag_13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delta_subtype_cnt_lag_12</th>\n",
       "      <td>6.882833e+00</td>\n",
       "      <td>6.882833e+00</td>\n",
       "      <td>3.574311e+00</td>\n",
       "      <td>3.574311e+00</td>\n",
       "      <td>5.966377e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <td>1.000000e+01</td>\n",
       "      <td>1.000000e+01</td>\n",
       "      <td>1.000000e+01</td>\n",
       "      <td>1.000000e+01</td>\n",
       "      <td>1.000000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>days</th>\n",
       "      <td>3.000000e+01</td>\n",
       "      <td>3.000000e+01</td>\n",
       "      <td>3.000000e+01</td>\n",
       "      <td>3.000000e+01</td>\n",
       "      <td>3.000000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_promotion_month</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_item_chan_class</th>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_subtype_first_sale</th>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_first_sale</th>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "      <td>3.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_brand_new</th>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean_first_sale</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               39190         39191  \\\n",
       "date_block_num                          1.300000e+01  1.300000e+01   \n",
       "sales_region_code                       1.010000e+02  1.010000e+02   \n",
       "item_code                               2.001500e+04  2.001500e+04   \n",
       "item_cnt_month                          0.000000e+00  0.000000e+00   \n",
       "first_cate_code                         3.080000e+02  3.080000e+02   \n",
       "second_cate_code                        4.040000e+02  4.040000e+02   \n",
       "item_cnt_month_lag_1                    0.000000e+00  0.000000e+00   \n",
       "item_cnt_month_lag_2                    0.000000e+00  0.000000e+00   \n",
       "item_cnt_month_lag_3                             NaN           NaN   \n",
       "date_avg_item_cnt_lag_1                 3.155000e+02  3.155000e+02   \n",
       "date_item_avg_item_cnt_lag_1            1.039844e+01  1.039844e+01   \n",
       "date_item_avg_item_cnt_lag_2            1.400000e+01  1.400000e+01   \n",
       "date_item_avg_item_cnt_lag_3                     NaN           NaN   \n",
       "date_region_avg_item_cnt_lag_1          2.727500e+02  2.727500e+02   \n",
       "date_region_avg_item_cnt_lag_2          3.897500e+02  3.897500e+02   \n",
       "date_region_avg_item_cnt_lag_3                   NaN           NaN   \n",
       "date_region_item_avg_item_cnt_lag_1     0.000000e+00  0.000000e+00   \n",
       "date_region_item_avg_item_cnt_lag_2     0.000000e+00  0.000000e+00   \n",
       "date_region_item_avg_item_cnt_lag_3              NaN           NaN   \n",
       "date_region_subtype_avg_item_cnt_lag_1  0.000000e+00  0.000000e+00   \n",
       "date_region_subtype_avg_item_cnt_lag_2  0.000000e+00  0.000000e+00   \n",
       "date_region_subtype_avg_item_cnt_lag_3           NaN           NaN   \n",
       "delta_price_lag                         4.205322e-02  4.205322e-02   \n",
       "delta_revenue_lag_1                     4.362204e+06  4.362204e+06   \n",
       "delta_cnt_lag                           1.444092e-01  1.444092e-01   \n",
       "item_avg_item_cnt                       1.101250e+02  1.101250e+02   \n",
       "date_item_avg_item_cnt_                 1.395000e+02  1.395000e+02   \n",
       "date_item_avg_item_cnt__lag_11                   NaN           NaN   \n",
       "date_item_avg_item_cnt__lag_12                   NaN           NaN   \n",
       "date_item_avg_item_cnt__lag_13                   NaN           NaN   \n",
       "delta_cnt_lag_11                                 NaN           NaN   \n",
       "delta_cnt_lag_12                                 NaN           NaN   \n",
       "delta_cnt_lag_13                                 NaN           NaN   \n",
       "delta_subtype_cnt_lag_12                6.882833e+00  6.882833e+00   \n",
       "month                                   1.000000e+01  1.000000e+01   \n",
       "days                                    3.000000e+01  3.000000e+01   \n",
       "is_promotion_month                      0.000000e+00  0.000000e+00   \n",
       "date_item_chan_class                    1.000000e+00  1.000000e+00   \n",
       "item_subtype_first_sale                 2.000000e+00  2.000000e+00   \n",
       "item_first_sale                         2.000000e+00  2.000000e+00   \n",
       "is_brand_new                            1.000000e+00  1.000000e+00   \n",
       "mean_first_sale                         0.000000e+00  0.000000e+00   \n",
       "\n",
       "                                               39192         39193  \\\n",
       "date_block_num                          1.300000e+01  1.300000e+01   \n",
       "sales_region_code                       1.010000e+02  1.010000e+02   \n",
       "item_code                               2.002100e+04  2.002100e+04   \n",
       "item_cnt_month                          7.300000e+01  7.300000e+01   \n",
       "first_cate_code                         3.050000e+02  3.050000e+02   \n",
       "second_cate_code                        4.120000e+02  4.120000e+02   \n",
       "item_cnt_month_lag_1                    5.700000e+01  5.700000e+01   \n",
       "item_cnt_month_lag_2                    2.490000e+02  2.490000e+02   \n",
       "item_cnt_month_lag_3                    4.520000e+02  4.520000e+02   \n",
       "date_avg_item_cnt_lag_1                 3.155000e+02  3.155000e+02   \n",
       "date_item_avg_item_cnt_lag_1            3.240625e+01  3.240625e+01   \n",
       "date_item_avg_item_cnt_lag_2            1.350000e+02  1.350000e+02   \n",
       "date_item_avg_item_cnt_lag_3            1.486250e+02  1.486250e+02   \n",
       "date_region_avg_item_cnt_lag_1          2.727500e+02  2.727500e+02   \n",
       "date_region_avg_item_cnt_lag_2          3.897500e+02  3.897500e+02   \n",
       "date_region_avg_item_cnt_lag_3          2.306250e+02  2.306250e+02   \n",
       "date_region_item_avg_item_cnt_lag_1     5.700000e+01  5.700000e+01   \n",
       "date_region_item_avg_item_cnt_lag_2     2.490000e+02  2.490000e+02   \n",
       "date_region_item_avg_item_cnt_lag_3     4.520000e+02  4.520000e+02   \n",
       "date_region_subtype_avg_item_cnt_lag_1  5.185000e+02  5.185000e+02   \n",
       "date_region_subtype_avg_item_cnt_lag_2  5.470000e+02  5.470000e+02   \n",
       "date_region_subtype_avg_item_cnt_lag_3  2.522500e+02  2.522500e+02   \n",
       "delta_price_lag                        -9.284973e-03 -9.284973e-03   \n",
       "delta_revenue_lag_1                     3.117494e+06  3.117494e+06   \n",
       "delta_cnt_lag                          -4.331055e-01 -4.331055e-01   \n",
       "item_avg_item_cnt                       8.300000e+01  8.300000e+01   \n",
       "date_item_avg_item_cnt_                 1.102500e+02  1.102500e+02   \n",
       "date_item_avg_item_cnt__lag_11                   NaN           NaN   \n",
       "date_item_avg_item_cnt__lag_12                   NaN           NaN   \n",
       "date_item_avg_item_cnt__lag_13                   NaN           NaN   \n",
       "delta_cnt_lag_11                                 NaN           NaN   \n",
       "delta_cnt_lag_12                                 NaN           NaN   \n",
       "delta_cnt_lag_13                                 NaN           NaN   \n",
       "delta_subtype_cnt_lag_12                3.574311e+00  3.574311e+00   \n",
       "month                                   1.000000e+01  1.000000e+01   \n",
       "days                                    3.000000e+01  3.000000e+01   \n",
       "is_promotion_month                      0.000000e+00  0.000000e+00   \n",
       "date_item_chan_class                    1.000000e+00  1.000000e+00   \n",
       "item_subtype_first_sale                 3.000000e+00  3.000000e+00   \n",
       "item_first_sale                         3.000000e+00  3.000000e+00   \n",
       "is_brand_new                            1.000000e+00  1.000000e+00   \n",
       "mean_first_sale                         0.000000e+00  0.000000e+00   \n",
       "\n",
       "                                               39194  \n",
       "date_block_num                          1.300000e+01  \n",
       "sales_region_code                       1.010000e+02  \n",
       "item_code                               2.004200e+04  \n",
       "item_cnt_month                          4.300000e+01  \n",
       "first_cate_code                         3.060000e+02  \n",
       "second_cate_code                        4.070000e+02  \n",
       "item_cnt_month_lag_1                    7.300000e+01  \n",
       "item_cnt_month_lag_2                    1.980000e+02  \n",
       "item_cnt_month_lag_3                    0.000000e+00  \n",
       "date_avg_item_cnt_lag_1                 3.155000e+02  \n",
       "date_item_avg_item_cnt_lag_1            1.677500e+02  \n",
       "date_item_avg_item_cnt_lag_2            2.695000e+02  \n",
       "date_item_avg_item_cnt_lag_3            1.048125e+02  \n",
       "date_region_avg_item_cnt_lag_1          2.727500e+02  \n",
       "date_region_avg_item_cnt_lag_2          3.897500e+02  \n",
       "date_region_avg_item_cnt_lag_3          2.306250e+02  \n",
       "date_region_item_avg_item_cnt_lag_1     7.300000e+01  \n",
       "date_region_item_avg_item_cnt_lag_2     1.980000e+02  \n",
       "date_region_item_avg_item_cnt_lag_3     0.000000e+00  \n",
       "date_region_subtype_avg_item_cnt_lag_1  4.045000e+02  \n",
       "date_region_subtype_avg_item_cnt_lag_2  7.865000e+02  \n",
       "date_region_subtype_avg_item_cnt_lag_3  4.017500e+02  \n",
       "delta_price_lag                        -1.345062e-02  \n",
       "delta_revenue_lag_1                     1.362260e+07  \n",
       "delta_cnt_lag                          -1.580811e-01  \n",
       "item_avg_item_cnt                       1.056250e+02  \n",
       "date_item_avg_item_cnt_                 1.412500e+02  \n",
       "date_item_avg_item_cnt__lag_11                   NaN  \n",
       "date_item_avg_item_cnt__lag_12                   NaN  \n",
       "date_item_avg_item_cnt__lag_13                   NaN  \n",
       "delta_cnt_lag_11                                 NaN  \n",
       "delta_cnt_lag_12                                 NaN  \n",
       "delta_cnt_lag_13                                 NaN  \n",
       "delta_subtype_cnt_lag_12                5.966377e+00  \n",
       "month                                   1.000000e+01  \n",
       "days                                    3.000000e+01  \n",
       "is_promotion_month                      0.000000e+00  \n",
       "date_item_chan_class                    1.000000e+00  \n",
       "item_subtype_first_sale                 3.000000e+00  \n",
       "item_first_sale                         3.000000e+00  \n",
       "is_brand_new                            1.000000e+00  \n",
       "mean_first_sale                         0.000000e+00  "
      ]
     },
     "execution_count": 832,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_new_df.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 833,
   "id": "d3e4b594",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_block_num</th>\n",
       "      <th>sales_region_code</th>\n",
       "      <th>item_code</th>\n",
       "      <th>item_cnt_month</th>\n",
       "      <th>first_cate_code</th>\n",
       "      <th>second_cate_code</th>\n",
       "      <th>item_cnt_month_lag_1</th>\n",
       "      <th>item_cnt_month_lag_2</th>\n",
       "      <th>item_cnt_month_lag_3</th>\n",
       "      <th>date_avg_item_cnt_lag_1</th>\n",
       "      <th>...</th>\n",
       "      <th>delta_cnt_lag_13</th>\n",
       "      <th>delta_subtype_cnt_lag_12</th>\n",
       "      <th>month</th>\n",
       "      <th>days</th>\n",
       "      <th>is_promotion_month</th>\n",
       "      <th>date_item_chan_class</th>\n",
       "      <th>item_subtype_first_sale</th>\n",
       "      <th>item_first_sale</th>\n",
       "      <th>is_brand_new</th>\n",
       "      <th>mean_first_sale</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39190</th>\n",
       "      <td>13</td>\n",
       "      <td>101</td>\n",
       "      <td>20015</td>\n",
       "      <td>0.0</td>\n",
       "      <td>308</td>\n",
       "      <td>404</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>315.50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.882833</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39191</th>\n",
       "      <td>13</td>\n",
       "      <td>101</td>\n",
       "      <td>20015</td>\n",
       "      <td>0.0</td>\n",
       "      <td>308</td>\n",
       "      <td>404</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>315.50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.882833</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39192</th>\n",
       "      <td>13</td>\n",
       "      <td>101</td>\n",
       "      <td>20021</td>\n",
       "      <td>73.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "      <td>57.0</td>\n",
       "      <td>249.0</td>\n",
       "      <td>452.0</td>\n",
       "      <td>315.50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.574311</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39193</th>\n",
       "      <td>13</td>\n",
       "      <td>101</td>\n",
       "      <td>20021</td>\n",
       "      <td>73.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "      <td>57.0</td>\n",
       "      <td>249.0</td>\n",
       "      <td>452.0</td>\n",
       "      <td>315.50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.574311</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39194</th>\n",
       "      <td>13</td>\n",
       "      <td>101</td>\n",
       "      <td>20042</td>\n",
       "      <td>43.0</td>\n",
       "      <td>306</td>\n",
       "      <td>407</td>\n",
       "      <td>73.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>315.50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.966377</td>\n",
       "      <td>10</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72831</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22030</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>37.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.874252</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72832</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22072</td>\n",
       "      <td>0.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "      <td>488.0</td>\n",
       "      <td>1842.0</td>\n",
       "      <td>1082.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.499974</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72833</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22072</td>\n",
       "      <td>0.0</td>\n",
       "      <td>305</td>\n",
       "      <td>412</td>\n",
       "      <td>488.0</td>\n",
       "      <td>1842.0</td>\n",
       "      <td>1082.0</td>\n",
       "      <td>300.75</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.499974</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72834</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22075</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.874252</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72835</th>\n",
       "      <td>40</td>\n",
       "      <td>105</td>\n",
       "      <td>22075</td>\n",
       "      <td>0.0</td>\n",
       "      <td>307</td>\n",
       "      <td>403</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.874252</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>33646 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       date_block_num  sales_region_code  item_code  item_cnt_month  \\\n",
       "39190              13                101      20015             0.0   \n",
       "39191              13                101      20015             0.0   \n",
       "39192              13                101      20021            73.0   \n",
       "39193              13                101      20021            73.0   \n",
       "39194              13                101      20042            43.0   \n",
       "...               ...                ...        ...             ...   \n",
       "72831              40                105      22030             0.0   \n",
       "72832              40                105      22072             0.0   \n",
       "72833              40                105      22072             0.0   \n",
       "72834              40                105      22075             0.0   \n",
       "72835              40                105      22075             0.0   \n",
       "\n",
       "       first_cate_code  second_cate_code  item_cnt_month_lag_1  \\\n",
       "39190              308               404                   0.0   \n",
       "39191              308               404                   0.0   \n",
       "39192              305               412                  57.0   \n",
       "39193              305               412                  57.0   \n",
       "39194              306               407                  73.0   \n",
       "...                ...               ...                   ...   \n",
       "72831              307               403                  37.0   \n",
       "72832              305               412                 488.0   \n",
       "72833              305               412                 488.0   \n",
       "72834              307               403                   0.0   \n",
       "72835              307               403                   0.0   \n",
       "\n",
       "       item_cnt_month_lag_2  item_cnt_month_lag_3  date_avg_item_cnt_lag_1  \\\n",
       "39190                   0.0                   0.0                   315.50   \n",
       "39191                   0.0                   0.0                   315.50   \n",
       "39192                 249.0                 452.0                   315.50   \n",
       "39193                 249.0                 452.0                   315.50   \n",
       "39194                 198.0                   0.0                   315.50   \n",
       "...                     ...                   ...                      ...   \n",
       "72831                  34.0                   0.0                   300.75   \n",
       "72832                1842.0                1082.0                   300.75   \n",
       "72833                1842.0                1082.0                   300.75   \n",
       "72834                   0.0                   0.0                     0.00   \n",
       "72835                   0.0                   0.0                     0.00   \n",
       "\n",
       "       ...  delta_cnt_lag_13  delta_subtype_cnt_lag_12  month  days  \\\n",
       "39190  ...               0.0                  6.882833     10    30   \n",
       "39191  ...               0.0                  6.882833     10    30   \n",
       "39192  ...               0.0                  3.574311     10    30   \n",
       "39193  ...               0.0                  3.574311     10    30   \n",
       "39194  ...               0.0                  5.966377     10    30   \n",
       "...    ...               ...                       ...    ...   ...   \n",
       "72831  ...               0.0                  6.874252      1    28   \n",
       "72832  ...               0.0                  3.499974      1    28   \n",
       "72833  ...               0.0                  3.499974      1    28   \n",
       "72834  ...               0.0                  6.874252      1    28   \n",
       "72835  ...               0.0                  6.874252      1    28   \n",
       "\n",
       "       is_promotion_month  date_item_chan_class  item_subtype_first_sale  \\\n",
       "39190                   0                   1.0                        2   \n",
       "39191                   0                   1.0                        2   \n",
       "39192                   0                   1.0                        3   \n",
       "39193                   0                   1.0                        3   \n",
       "39194                   0                   1.0                        3   \n",
       "...                   ...                   ...                      ...   \n",
       "72831                   1                   1.0                        3   \n",
       "72832                   1                   0.0                        4   \n",
       "72833                   1                   0.0                        4   \n",
       "72834                   1                   0.0                        0   \n",
       "72835                   1                   1.0                        0   \n",
       "\n",
       "       item_first_sale  is_brand_new  mean_first_sale  \n",
       "39190                2             1              0.0  \n",
       "39191                2             1              0.0  \n",
       "39192                3             1              0.0  \n",
       "39193                3             1              0.0  \n",
       "39194                3             1              0.0  \n",
       "...                ...           ...              ...  \n",
       "72831                3             1              0.0  \n",
       "72832                4             1              0.0  \n",
       "72833                4             1              0.0  \n",
       "72834                0             1              0.0  \n",
       "72835                0             1              0.0  \n",
       "\n",
       "[33646 rows x 42 columns]"
      ]
     },
     "execution_count": 833,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_new_df.fillna(0)  #需要吗"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39896f60",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 834,
   "id": "cbb3eeb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 删除全部为0的列\n",
    "brand_new_df = brand_new_df.loc[:, (brand_new_df != 0).any(axis=0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12d7d9ef",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 835,
   "id": "e0bab618",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date_block_num', 'sales_region_code', 'item_code', 'item_cnt_month',\n",
       "       'first_cate_code', 'second_cate_code', 'item_cnt_month_lag_1',\n",
       "       'item_cnt_month_lag_2', 'item_cnt_month_lag_3',\n",
       "       'date_avg_item_cnt_lag_1', 'date_item_avg_item_cnt_lag_1',\n",
       "       'date_item_avg_item_cnt_lag_2', 'date_item_avg_item_cnt_lag_3',\n",
       "       'date_region_avg_item_cnt_lag_1', 'date_region_avg_item_cnt_lag_2',\n",
       "       'date_region_avg_item_cnt_lag_3', 'date_region_item_avg_item_cnt_lag_1',\n",
       "       'date_region_item_avg_item_cnt_lag_2',\n",
       "       'date_region_item_avg_item_cnt_lag_3',\n",
       "       'date_region_subtype_avg_item_cnt_lag_1',\n",
       "       'date_region_subtype_avg_item_cnt_lag_2',\n",
       "       'date_region_subtype_avg_item_cnt_lag_3', 'delta_price_lag',\n",
       "       'delta_revenue_lag_1', 'delta_cnt_lag', 'item_avg_item_cnt',\n",
       "       'date_item_avg_item_cnt_', 'date_item_avg_item_cnt__lag_11',\n",
       "       'date_item_avg_item_cnt__lag_12', 'date_item_avg_item_cnt__lag_13',\n",
       "       'delta_cnt_lag_11', 'delta_cnt_lag_12', 'delta_cnt_lag_13',\n",
       "       'delta_subtype_cnt_lag_12', 'month', 'days', 'is_promotion_month',\n",
       "       'date_item_chan_class', 'item_subtype_first_sale', 'item_first_sale',\n",
       "       'is_brand_new', 'mean_first_sale'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 835,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_new_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 838,
   "id": "d53a1c2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "brand_new_df = brand_new_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 839,
   "id": "ea4b4064",
   "metadata": {},
   "outputs": [],
   "source": [
    "brand_new_df.to_csv(\"./中间数据/新品月度数据无编码_12.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3771a8b8",
   "metadata": {},
   "source": [
    "# 3.Modelling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 631,
   "id": "dcf8e035",
   "metadata": {},
   "outputs": [],
   "source": [
    "import gc\n",
    "import pickle\n",
    "from xgboost import XGBRegressor\n",
    "from matplotlib.pylab import rcParams\n",
    "rcParams['figure.figsize'] = 12, 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 632,
   "id": "439ece37",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'LabelEncoder' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-632-5b7fc9b5d135>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# 补充一下编码\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;31m# itemcode\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mLE2\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mLabelEncoder\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      4\u001b[0m \u001b[0mmatrix\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"item_code\"\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mLE2\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfit_transform\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmatrix\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mitem_code\u001b[0m \u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNameError\u001b[0m: name 'LabelEncoder' is not defined"
     ]
    }
   ],
   "source": [
    "# 补充一下编码\n",
    "# itemcode\n",
    "LE2 = LabelEncoder()\n",
    "matrix[\"item_code\"] = LE2.fit_transform(matrix.item_code )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "485758c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = matrix.copy()  # 使用copy，不要直接=\n",
    "del matrix\n",
    "gc.collect() # 释放内存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e364c10",
   "metadata": {},
   "outputs": [],
   "source": [
    "data.date_block_num"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf9f2d7d",
   "metadata": {},
   "source": [
    "Use month 38、39 as validation for training."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59b2a958",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train = data[data.date_block_num < 37].drop(['item_cnt_month'], axis=1)\n",
    "Y_train = data[data.date_block_num < 37]['item_cnt_month']\n",
    "X_valid = data[data.date_block_num.isin([37,38])].drop(['item_cnt_month'], axis=1)\n",
    "Y_valid = data[data.date_block_num.isin([37,38])]['item_cnt_month']\n",
    "X_test = data[data.date_block_num == 39].drop(['item_cnt_month'], axis=1)\n",
    "\n",
    "\n",
    "# 最后是要剔除的\n",
    "Y_test = data[data.date_block_num == 39]['item_cnt_month']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9562456a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Y_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0845bb4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 测一下数据分布，不知道这里是否需要归一化\n",
    "plt.figure(figsize=(10,4))\n",
    "plt.xlim(-100, 3000)\n",
    "flierprops = dict(marker='o', markerfacecolor='purple', markersize=6,\n",
    "                  linestyle='none', markeredgecolor='black')\n",
    "sns.boxplot(x=Y_train, flierprops=flierprops)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c742c107",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 划分完就删删删\n",
    "del data\n",
    "gc.collect();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09043138",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts = time.time()\n",
    "\n",
    "model = XGBRegressor(\n",
    "    max_depth=10,\n",
    "    n_estimators=1000,\n",
    "    min_child_weight=0.5, \n",
    "    colsample_bytree=0.8, \n",
    "    subsample=0.8, \n",
    "    eta=0.1,\n",
    "#     tree_method='gpu_hist',\n",
    "    seed=42)\n",
    "\n",
    "\n",
    "model.fit(\n",
    "    X_train, \n",
    "    Y_train, \n",
    "    eval_metric=\"rmse\", \n",
    "    eval_set=[(X_train, Y_train), (X_valid, Y_valid)], \n",
    "    verbose=True, \n",
    "    early_stopping_rounds = 20)\n",
    "\n",
    "time.time() - ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "113ebf29",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_valid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "52303f16",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19f91eac",
   "metadata": {},
   "outputs": [],
   "source": [
    "Y_valid_pred = model.predict(X_valid) # 验证集的预测y\n",
    "Y_test_pred = model.predict(X_test) # 预测集的预测y\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10d92fb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 这里取决于我们要检查的是预测集还是验证集的效果\n",
    "\n",
    "# 如果是验证集\n",
    "# y_pred = Y_valid_pred\n",
    "# Y_valid = Y_valid\n",
    "\n",
    "# 如果是测试集\n",
    "X_valid = X_test\n",
    "Y_pred = Y_test_pred\n",
    "Y_valid = Y_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4461e7d5",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "submission = pd.DataFrame({\n",
    "#     \"ID\": Y_valid.index, \n",
    "    \"date_block_num\":X_valid.date_block_num,\n",
    "    \"sales_region_code\":X_valid.sales_region_code,\n",
    "    \"item_code\":X_valid.item_code,\n",
    "    \"first_cate_code\":X_valid.first_cate_code,\n",
    "    \"second_cate_code\":X_valid.second_cate_code,\n",
    "    \"item_cnt_month_valid\": Y_valid,\n",
    "    \"item_cnt_month_pred\": Y_pred\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e34250de",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 剔除所有item_cnt_month_valid为0的\n",
    "submission = submission[submission[\"item_cnt_month_valid\"] != 0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12e9fd30",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 还原编码\n",
    "submission[\"sales_region_code\"] = LE1.inverse_transform(submission[\"sales_region_code\"])\n",
    "submission[\"first_cate_code\"] = LE3.inverse_transform(submission[\"first_cate_code\"])\n",
    "submission[\"second_cate_code\"] = LE4.inverse_transform(submission[\"second_cate_code\"])\n",
    "submission[\"item_code\"] = LE2.inverse_transform(submission[\"item_code\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b71e818d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 把对数转换回来\n",
    "if is_need_log:\n",
    "    submission[\"item_cnt_month_valid\"] = list(map(lambda x : 2**x-1,submission[\"item_cnt_month_valid\"] ))\n",
    "    submission[\"item_cnt_month_pred\"] = list(map(lambda x : 2**x-1,submission[\"item_cnt_month_pred\"] ))\n",
    "# 填补负值\n",
    "submission[\"item_cnt_month_pred\"] =list(map(lambda x : x if x>0 else 0.1,submission[\"item_cnt_month_pred\"] ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6821b3ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "submission.to_csv('xgb_submission.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe2e87d1",
   "metadata": {},
   "source": [
    "用tweedie指标评价"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be677969",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.metrics import mean_tweedie_deviance\n",
    "\n",
    "# 计算模型在两列数据上的Tweedie偏差\n",
    "tweedie_variance_power = 1.5\n",
    "tweedie_dev = mean_tweedie_deviance(submission[\"item_cnt_month_valid\"], submission[\"item_cnt_month_pred\"], power=tweedie_variance_power)\n",
    "    \n",
    "# 打印Tweedie偏差值\n",
    "print(\"Tweedie deviance:\", tweedie_dev)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8b2d5e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5336444",
   "metadata": {},
   "outputs": [],
   "source": [
    "from xgboost import plot_importance\n",
    "\n",
    "def plot_features(booster, figsize):    \n",
    "    fig, ax = plt.subplots(1,1,figsize=figsize)\n",
    "    return plot_importance(booster=booster, ax=ax)\n",
    "\n",
    "plot_features(model, (10,14))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5f06b78",
   "metadata": {},
   "source": [
    "最后其实是没有这步的，前期我们检验一下有没有过拟合"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d3085c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Y_test_pred =  model.predict(X_test)\n",
    "\n",
    "# # 计算模型在两列数据上的Tweedie偏差\n",
    "# tweedie_variance_power = 1\n",
    "# tweedie_dev = mean_tweedie_deviance(Y_test_pred, Y_test, power=tweedie_variance_power)\n",
    "\n",
    "# # 打印Tweedie偏差值\n",
    "# print(\"Tweedie deviance:\", tweedie_dev)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33551347",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}