钢卷仓储吞吐量趋势预测-源码.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy import stats\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from datetime import date, timedelta, datetime\n",
    "import xgboost as xgb\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "rootpath = r\"/home/bingo/桌面/sales_forecasting/steel_storage_throughput_prediction\"\n",
    "os.chdir(rootpath)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 训练集整理函数\n",
    "- selectData:加载原始数据，剔除无效数据\n",
    "- loadData:加载数据整理为时间表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 清洗数据\n",
    "- 预测任务\n",
    "    - 1.按照两大类货品类型（冷卷、热卷），分别预测未来4个周钢铁的周入库量和周出库量（重量）；\n",
    "    - 2.按照两大类货品类型（冷卷、热卷），分别预测未来i天的日入库量和日出库量（重量）。\n",
    "- 只有产品名称是热卷和圆钢为热卷类型，其余均为冷卷类型\n",
    "- 整理目标：\n",
    "    - 仅筛选出包含冷卷和热卷两种货品的数据集，添加新列表示冷卷或热卷\n",
    "    - 丢弃，数量=0而重量>0 重量=0而数量>0的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_item(dataset, target_col, item_list, new_col=None):\n",
    "    df = pd.DataFrame(columns=dataset.columns)\n",
    "    for item in item_list:\n",
    "        temp = dataset[dataset[target_col] == item]\n",
    "        df = pd.concat([df, temp], axis=0)\n",
    "    if new_col:\n",
    "        for key, item in new_col.items():\n",
    "            df[key] = item\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def selectData(inpath, extpath):\n",
    "    # 读取原始数据\n",
    "    df_train_in = pd.read_csv(inpath)\n",
    "    df_train_ext = pd.read_csv(extpath)\n",
    "    # 剔除无效数据\n",
    "    print(\"df_train_in shape: \", df_train_in.shape)\n",
    "    dropind = df_train_in[df_train_in[\"r2019\"] == 0].index\n",
    "    df_train_in = df_train_in.drop(dropind)\n",
    "    dropind = df_train_in[df_train_in[\"r2018\"] == 0].index\n",
    "    df_train_in = df_train_in.drop(dropind)\n",
    "    print(\"df_train_in shape: \", df_train_in.shape)\n",
    "\n",
    "    print(\"df_train_ext shape: \", df_train_ext.shape)\n",
    "    dropind = df_train_ext[df_train_ext[\"c2019\"] == 0].index\n",
    "    df_train_ext = df_train_ext.drop(dropind)\n",
    "    dropind = df_train_ext[df_train_ext[\"c2018\"] == 0].index\n",
    "    df_train_ext = df_train_ext.drop(dropind)\n",
    "    print(\"df_train_ext shape: \", df_train_ext.shape)\n",
    "\n",
    "    # 设置种类\n",
    "    cool_col = {\"type\": \"冷卷\", \"type_id\": 0}\n",
    "    hot_col = {\"type\": \"热卷\", \"type_id\": 1}\n",
    "\n",
    "    hot_in = [\"热卷\", \"圆钢\"]\n",
    "    cool_in = set(df_train_in[\"r2017\"].unique()) - set(hot_in)\n",
    "\n",
    "    train_in_hot = get_item(df_train_in, \"r2017\", hot_in, hot_col)\n",
    "    train_in_cool = get_item(df_train_in, \"r2017\", cool_in, cool_col)\n",
    "    train_in_both = pd.concat([train_in_hot, train_in_cool], axis=0)\n",
    "    del train_in_hot, train_in_cool\n",
    "\n",
    "    hot_ext = [\"热卷\", \"圆钢\"]\n",
    "    cool_ext = set(df_train_ext[\"c2017\"].unique()) - set(hot_ext)\n",
    "\n",
    "    train_ext_hot = get_item(df_train_ext, \"c2017\", hot_ext, hot_col)\n",
    "    train_ext_cool = get_item(df_train_ext, \"c2017\", cool_ext, cool_col)\n",
    "    train_ext_both = pd.concat([train_ext_hot, train_ext_cool], axis=0)\n",
    "    del train_ext_cool, train_ext_hot\n",
    "\n",
    "    # 出入库数据集样本数\n",
    "    print(\"train_in samples: \", train_in_both.shape)\n",
    "    print(\"train_ext samples: \", train_ext_both.shape)\n",
    "\n",
    "    # 保存\n",
    "    train_in_both.to_csv(\"data/train_in.csv\", index=False)\n",
    "    train_ext_both.to_csv(\"data/train_ext.csv\", index=False)\n",
    "    \n",
    "    print(\"selecting data Done!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 数据转换\n",
    "- 整理目标\n",
    "    - 转换为储户id-产品id-日期格式三层索引,重量为训练值，做为训练集格式\n",
    "    - 无记录时间记出入库重量为0\n",
    "    - 补齐中断的缺失时间，时间范围为(2014-02-24,2018-01-28)\n",
    "    - 转换为储户id-产品id-日期格式三层索引,数量为训练值，做为训练集格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def loadData(in_path, ext_path, endtime=\"2018-1-28\"):\n",
    "    starttime = date(2014, 2, 24)\n",
    "    endtime = datetime.strptime(endtime,\"%Y-%m-%d\")\n",
    "    # 进口重量训练集\n",
    "    dtypes = {\n",
    "        \"r2014\": str,\n",
    "        \"r2015\": int,\n",
    "        \"r2019\": float,\n",
    "        \"type_id\": int,\n",
    "    }\n",
    "    use_cols = [0, 1, 5, 10]\n",
    "    train_in = pd.read_csv(in_path, dtype=dtypes, usecols=use_cols)\n",
    "\n",
    "    train_in[\"date\"] = pd.to_datetime(\n",
    "        train_in[\"r2014\"].apply(lambda x: x[2:10]))\n",
    "\n",
    "    # 选择数据列，更改列名\n",
    "    select_col = [\"date\", \"r2015\", \"r2019\", \"type_id\"]\n",
    "    new_names = [\"date\", \"store_id\", \"sales\", \"type_id\"]\n",
    "    train_in = train_in[select_col]\n",
    "\n",
    "    train_in.columns = new_names\n",
    "\n",
    "    # 补全2014-2-24 2018-01-28日期内，缺失的时间及记录\n",
    "    temp = train_in.groupby([\"store_id\", \"type_id\", \"date\"])[\n",
    "        [\"sales\"]].sum().unstack(level=-1).fillna(0)\n",
    "    temp.columns = temp.columns.get_level_values(1)\n",
    "\n",
    "    # 2014-02-24为周一，2018-01-28为周日\n",
    "    train_in_all = pd.DataFrame(temp, index=temp.index,\n",
    "                                columns=pd.date_range(starttime, endtime))\n",
    "    train_in_all.fillna(0, inplace=True)\n",
    "\n",
    "    # 进口数量训练集\n",
    "    dtypes = {\n",
    "        \"r2014\": str,\n",
    "        \"r2015\": int,\n",
    "        \"r2018\": int,\n",
    "        \"type_id\": int,\n",
    "    }\n",
    "    use_cols = [0, 1, 4, 10]\n",
    "    train_in = pd.read_csv(in_path, dtype=dtypes, usecols=use_cols)\n",
    "\n",
    "    train_in[\"date\"] = pd.to_datetime(\n",
    "        train_in[\"r2014\"].apply(lambda x: x[2:10]))\n",
    "\n",
    "    # 选择数据列，更改列名\n",
    "    select_col = [\"date\", \"r2015\", \"r2018\", \"type_id\"]\n",
    "    new_names = [\"date\", \"store_id\", \"nums\", \"type_id\"]\n",
    "    train_in = train_in[select_col]\n",
    "\n",
    "    train_in.columns = new_names\n",
    "\n",
    "    # 补全2014-2-24 2018-01-28日期内，缺失的时间及记录\n",
    "    temp = train_in.groupby([\"store_id\", \"type_id\", \"date\"])[\n",
    "        [\"nums\"]].sum().unstack(level=-1).fillna(0)\n",
    "    temp.columns = temp.columns.get_level_values(1)\n",
    "\n",
    "    # 2014-02-24为周一，2018-01-28为周日\n",
    "    train_in_all_nums = pd.DataFrame(temp, index=temp.index,\n",
    "                                     columns=pd.date_range(starttime, endtime))\n",
    "    train_in_all_nums.fillna(0, inplace=True)\n",
    "\n",
    "    # 出口重量训练集\n",
    "    dtypes = {\n",
    "        \"c2014\": str,\n",
    "        \"c2015\": int,\n",
    "        \"c2019\": float,\n",
    "        \"type_id\": int,\n",
    "    }\n",
    "    use_cols = [0, 1, 5, 10]\n",
    "    train_ext = pd.read_csv(ext_path, dtype=dtypes, usecols=use_cols)\n",
    "\n",
    "    train_ext[\"date\"] = pd.to_datetime(train_ext[\"c2014\"].\n",
    "                                       apply(lambda x: \"\".join(list(filter(str.isdigit, x))[:8])))\n",
    "\n",
    "    select_col = [\"date\", \"c2015\", \"c2019\", \"type_id\"]\n",
    "    new_names = [\"date\", \"store_id\", \"sales\", \"type_id\"]\n",
    "\n",
    "    train_ext = train_ext[select_col]\n",
    "    train_ext.columns = new_names\n",
    "\n",
    "    temp = train_ext.groupby([\"store_id\", \"type_id\", \"date\"])[\n",
    "        [\"sales\"]].sum().unstack(level=-1).fillna(0)\n",
    "    temp.columns = temp.columns.get_level_values(1)\n",
    "\n",
    "    train_ext_all = pd.DataFrame(temp, index=temp.index,\n",
    "                                 columns=pd.date_range(starttime, endtime))\n",
    "    train_ext_all.fillna(0, inplace=True)\n",
    "\n",
    "    # 出口数量集\n",
    "    dtypes = {\n",
    "        \"c2014\": str,\n",
    "        \"c2015\": int,\n",
    "        \"c2018\": int,\n",
    "        \"type_id\": int,\n",
    "    }\n",
    "    use_cols = [0, 1, 4, 10]\n",
    "    train_ext = pd.read_csv(ext_path, dtype=dtypes, usecols=use_cols)\n",
    "\n",
    "    train_ext[\"date\"] = pd.to_datetime(train_ext[\"c2014\"].\n",
    "                                       apply(lambda x: \"\".join(list(filter(str.isdigit, x))[:8])))\n",
    "\n",
    "    select_col = [\"date\", \"c2015\", \"c2018\", \"type_id\"]\n",
    "    new_names = [\"date\", \"store_id\", \"nums\", \"type_id\"]\n",
    "    train_ext = train_ext[select_col]\n",
    "    train_ext.columns = new_names\n",
    "\n",
    "    temp = train_ext.groupby([\"store_id\", \"type_id\", \"date\"])[\n",
    "        [\"nums\"]].sum().unstack(level=-1).fillna(0)\n",
    "    temp.columns = temp.columns.get_level_values(1)\n",
    "\n",
    "    train_ext_all_nums = pd.DataFrame(temp, index=temp.index,\n",
    "                                      columns=pd.date_range(starttime, endtime))\n",
    "    train_ext_all_nums.fillna(0, inplace=True)\n",
    "    \n",
    "    # 保存数据\n",
    "    train_in_all.to_csv(\"data/train_in_all.csv\")\n",
    "    train_ext_all.to_csv(\"data/train_ext_all.csv\")\n",
    "    train_in_all_nums.to_csv(\"data/train_in_all_nums.csv\")\n",
    "    train_ext_all_nums.to_csv(\"data/train_ext_all_nums.csv\")\n",
    "    \n",
    "    print(\"loading data Done!\")\n",
    "    return train_in_all, train_ext_all, train_in_all_nums, train_ext_all_nums"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "in1 shape:  (332937, 9)\n",
      "in2 shape:  (14713, 9)\n",
      "in3 shape:  (347650, 9)\n"
     ]
    }
   ],
   "source": [
    "old_in = r\"data_org/trainData_IN.csv\"\n",
    "old_ext = r\"data_org/trainData_EXT.csv\"\n",
    "new_in = r\"data_org/testData4_IN.csv\"\n",
    "new_ext = r\"data_org/testData4_EXT.csv\"\n",
    "# 合并的原始数据集位置\n",
    "inpath = r\"data_org/train_IN.csv\"\n",
    "extpath = r\"data_org/train_EXT.csv\"\n",
    "\n",
    "in1 = pd.read_csv(old_in)\n",
    "ext1 = pd.read_csv(old_ext)\n",
    "in2 = pd.read_csv(new_in)\n",
    "ext2 = pd.read_csv(new_ext)\n",
    "\n",
    "in3 = pd.concat([in1, in2], axis=0)\n",
    "ext3 = pd.concat([ext1, ext2], axis=0)\n",
    "in3.to_csv(inpath, index=False)\n",
    "ext3.to_csv(extpath, index=False)\n",
    "print(\"in1 shape: \",in1.shape)\n",
    "print(\"in2 shape: \", in2.shape)\n",
    "print(\"in3 shape: \", in3.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_train_in shape:  (347650, 9)\n",
      "df_train_in shape:  (346906, 9)\n",
      "df_train_ext shape:  (425405, 9)\n",
      "df_train_ext shape:  (425359, 9)\n",
      "train_in samples:  (346906, 11)\n",
      "train_ext samples:  (425359, 11)\n",
      "selecting data Done!\n",
      "loading data Done!\n",
      "train_in_all shape:  (1386, 1575)\n"
     ]
    }
   ],
   "source": [
    "# 合并的原始数据集位置\n",
    "inpath = r\"data_org/train_IN.csv\"\n",
    "extpath = r\"data_org/train_EXT.csv\"\n",
    "# 训练数据集位置\n",
    "in_path = \"data/train_in.csv\"\n",
    "ext_path = \"data/train_ext.csv\"\n",
    "endtime = \"2018-6-17\"\n",
    "\n",
    "selectData(inpath, extpath)\n",
    "train_in_all, train_ext_all, train_in_all_nums, train_ext_all_nums = loadData(\n",
    "    in_path, ext_path, endtime=endtime)\n",
    "print(\"train_in_all shape: \", train_in_all.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 特征工程\n",
    "- 滑窗选取时间窗，提取时间窗范围的统计量作为特征\n",
    "- 有大量稀疏值，可以选择合并，可有效降低误差\n",
    "- 数据存在异方差性，取log1p减少异方差，可降低误差\n",
    "- 滑动平均平滑原始数据，可降低误差\n",
    "- 周数据：截取时间段->/1000->ewm->merge->np.log1p->resample\n",
    "    - 周数据预测使用累计方式计算，然后逐次相减\n",
    "    - resample代表按1周、2周、3周、4周重采样\n",
    "- 天数据：截取时间段->/1000->ewm->merge->np.log1p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 合并含有大量0值的行\n",
    "def merge_zeros_cols(df, merge_end=500, merge_step=50):\n",
    "    freq = df.columns.freqstr\n",
    "    notzeros = np.count_nonzero(df, axis=1)\n",
    "    merge_list = np.arange(0,merge_end+1,merge_step)\n",
    "    df = df.reset_index()\n",
    "    df_ = df[notzeros>=merge_end].set_index(\"type_id\")\n",
    "    for i, value in enumerate(merge_list[:-1]):\n",
    "        ind = (notzeros>=value)&(notzeros<merge_list[i+1])\n",
    "        tmp = df.iloc[ind,:]\n",
    "        tmp = tmp.groupby(\"type_id\").sum(axis=0)\n",
    "        tmp[\"store_id\"] = merge_list[i+1]\n",
    "        df_ = pd.concat([df_,tmp],axis=0)\n",
    "    df_ = df_.reset_index().set_index([\"store_id\",\"type_id\"])\n",
    "    df_.columns = pd.DatetimeIndex(df_.columns,freq=freq)\n",
    "    return df_"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 周数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_train_in shape:  (1386, 1575)\n",
      "df_train_ext_shape:  (1383, 1575)\n",
      "df_train_in_nums shape:  (1386, 1575)\n",
      "df_train_ext_nums shape:  (1383, 1575)\n"
     ]
    }
   ],
   "source": [
    "span=28\n",
    "\n",
    "df_train_in = pd.read_csv(\"data/train_in_all.csv\").set_index([\"store_id\",\"type_id\"])/1000\n",
    "df_train_in.columns = pd.to_datetime(df_train_in.columns)\n",
    "df_train_in.columns.name = \"date\"\n",
    "\n",
    "df_train_ext = pd.read_csv(\"data/train_ext_all.csv\").set_index([\"store_id\",\"type_id\"])/1000\n",
    "df_train_ext.columns = pd.to_datetime(df_train_ext.columns)\n",
    "df_train_ext.columns.name = \"date\"\n",
    "\n",
    "df_train_in_nums = pd.read_csv(\"data/train_in_all_nums.csv\").set_index([\"store_id\",\"type_id\"])/1000\n",
    "df_train_in_nums.columns = pd.to_datetime(df_train_in_nums.columns)\n",
    "df_train_in_nums.columns.name = \"date\"\n",
    "\n",
    "df_train_ext_nums = pd.read_csv(\"data/train_ext_all_nums.csv\").set_index([\"store_id\",\"type_id\"])/1000\n",
    "df_train_ext_nums.columns = pd.to_datetime(df_train_ext_nums.columns)\n",
    "df_train_ext_nums.columns.name = \"date\"\n",
    "\n",
    "df_train_in = df_train_in.ewm(span=span, axis=1).mean()\n",
    "df_train_ext = df_train_ext.ewm(span=span, axis=1).mean()\n",
    "df_train_in_nums = df_train_in_nums.ewm(span=span, axis=1).mean()\n",
    "df_train_ext_nums = df_train_ext_nums.ewm(span=span, axis=1).mean()\n",
    "\n",
    "print(\"df_train_in shape: \", df_train_in.shape)\n",
    "print(\"df_train_ext_shape: \", df_train_ext.shape)\n",
    "print(\"df_train_in_nums shape: \", df_train_in_nums.shape)\n",
    "print(\"df_train_ext_nums shape: \", df_train_ext_nums.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_train_in_W shape:  (650, 225)\n",
      "df_train_ext_W shape:  (641, 225)\n",
      "df_train_in_nums_W shape:  (650, 225)\n",
      "df_train_ext_nums_W shape:  (641, 225)\n"
     ]
    }
   ],
   "source": [
    "merge_end = 600 \n",
    "merge_step = 600 \n",
    "\n",
    "tmp_in = merge_zeros_cols(df_train_in, merge_end, merge_step).transpose()\n",
    "df_train_in_transpose = np.log1p(tmp_in)\n",
    "\n",
    "tmp_ext = merge_zeros_cols(df_train_ext, merge_end, merge_step).transpose()\n",
    "df_train_ext_transpose = np.log1p(tmp_ext)\n",
    "\n",
    "tmp_in_nums = merge_zeros_cols(df_train_in_nums, merge_end, merge_step).transpose()\n",
    "df_train_in_nums_transpose = tmp_in_nums  \n",
    "\n",
    "tmp_ext_nums = merge_zeros_cols(df_train_ext_nums, merge_end, merge_step).transpose()\n",
    "df_train_ext_nums_transpose = tmp_ext_nums  \n",
    "\n",
    "# 按周重采样\n",
    "df_train_in_W = df_train_in_transpose.resample(\n",
    "    \"W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_in_2W = df_train_in_transpose.resample(\n",
    "    \"2W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_in_3W = df_train_in_transpose.resample(\n",
    "    \"3W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_in_4W = df_train_in_transpose.resample(\n",
    "    \"4W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "\n",
    "df_train_ext_W = df_train_ext_transpose.resample(\n",
    "    \"W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_ext_2W = df_train_ext_transpose.resample(\n",
    "    \"2W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_ext_3W = df_train_ext_transpose.resample(\n",
    "    \"3W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_ext_4W = df_train_ext_transpose.resample(\n",
    "    \"4W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "\n",
    "# 数量重采样\n",
    "df_train_in_nums_W = df_train_in_nums_transpose.resample(\n",
    "    \"W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_in_nums_2W = df_train_in_nums_transpose.resample(\n",
    "    \"2W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_in_nums_3W = df_train_in_nums_transpose.resample(\n",
    "    \"3W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_in_nums_4W = df_train_in_nums_transpose.resample(\n",
    "    \"4W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "\n",
    "df_train_ext_nums_W = df_train_ext_nums_transpose.resample(\n",
    "    \"W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_ext_nums_2W = df_train_ext_nums_transpose.resample(\n",
    "    \"2W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_ext_nums_3W = df_train_ext_nums_transpose.resample(\n",
    "    \"3W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "df_train_ext_nums_4W = df_train_ext_nums_transpose.resample(\n",
    "    \"4W\", closed=\"right\", label=\"right\").mean().transpose()\n",
    "\n",
    "print(\"df_train_in_W shape: \", df_train_in_W.shape)\n",
    "print(\"df_train_ext_W shape: \", df_train_ext_W.shape)\n",
    "print(\"df_train_in_nums_W shape: \", df_train_in_nums_W.shape)\n",
    "print(\"df_train_ext_nums_W shape: \", df_train_ext_nums_W.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 天数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_train_in shape:  (1386, 1575)\n",
      "df_train_ext_shape:  (1383, 1575)\n",
      "df_train_in_nums shape:  (1386, 1575)\n",
      "df_train_ext_nums shape:  (1383, 1575)\n"
     ]
    }
   ],
   "source": [
    "span=56\n",
    "\n",
    "df_train_in = pd.read_csv(\"data/train_in_all.csv\").set_index([\"store_id\",\"type_id\"])/1000\n",
    "df_train_in.columns = pd.to_datetime(df_train_in.columns)\n",
    "df_train_in.columns.name = \"date\"\n",
    "\n",
    "df_train_ext = pd.read_csv(\"data/train_ext_all.csv\").set_index([\"store_id\",\"type_id\"])/1000\n",
    "df_train_ext.columns = pd.to_datetime(df_train_ext.columns)\n",
    "df_train_ext.columns.name = \"date\"\n",
    "\n",
    "df_train_in_nums = pd.read_csv(\"data/train_in_all_nums.csv\").set_index([\"store_id\",\"type_id\"])/1000\n",
    "df_train_in_nums.columns = pd.to_datetime(df_train_in_nums.columns)\n",
    "df_train_in_nums.columns.name = \"date\"\n",
    "\n",
    "df_train_ext_nums = pd.read_csv(\"data/train_ext_all_nums.csv\").set_index([\"store_id\",\"type_id\"])/1000\n",
    "df_train_ext_nums.columns = pd.to_datetime(df_train_ext_nums.columns)\n",
    "df_train_ext_nums.columns.name = \"date\"\n",
    "\n",
    "df_train_in = df_train_in.ewm(span=span, axis=1).mean()\n",
    "df_train_ext = df_train_ext.ewm(span=span, axis=1).mean()\n",
    "df_train_in_nums = df_train_in_nums.ewm(span=span, axis=1).mean()\n",
    "df_train_ext_nums = df_train_ext_nums.ewm(span=span, axis=1).mean()\n",
    "\n",
    "print(\"df_train_in shape: \", df_train_in.shape)\n",
    "print(\"df_train_ext_shape: \", df_train_ext.shape)\n",
    "print(\"df_train_in_nums shape: \", df_train_in_nums.shape)\n",
    "print(\"df_train_ext_nums shape: \", df_train_ext_nums.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_train_in shape:  (1120, 1575)\n",
      "df_train_ext_shape:  (1118, 1575)\n"
     ]
    }
   ],
   "source": [
    "merge_end = 200 \n",
    "merge_step = 200 \n",
    "\n",
    "df_train_in = merge_zeros_cols(df_train_in, merge_end, merge_step)\n",
    "df_train_ext = merge_zeros_cols(df_train_ext, merge_end, merge_step)\n",
    "\n",
    "# 再直接取对数\n",
    "df_train_in = np.log1p(df_train_in)\n",
    "df_train_ext = np.log1p(df_train_ext)\n",
    "\n",
    "# 数量处理\n",
    "df_train_in_nums = merge_zeros_cols(df_train_in_nums, merge_end, merge_step)\n",
    "df_train_ext_nums = merge_zeros_cols(df_train_ext_nums, merge_end, merge_step)\n",
    "\n",
    "print(\"df_train_in shape: \", df_train_in.shape)\n",
    "print(\"df_train_ext_shape: \", df_train_ext.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 特征子集抽取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def prepare_dataset_byDay(df, dt, dnums=None, addType=False, is_train=True):\n",
    "    x = {}\n",
    "    n = 11\n",
    "    times = [3,7,14,21,35,56] \n",
    "    for i in times:\n",
    "        tmp = get_span(df, dt, i-1, i)\n",
    "        x[\"sales_%s_sum\"%(i)] = tmp.sum(axis=1).values\n",
    "        x[\"sales_%s_mean\"%(i)] = tmp.mean(axis=1).values\n",
    "        x[\"sales_%s_sum_decay\"%(i)] = \\\n",
    "        (tmp*np.power(0.999,np.arange(i)[::-1])).sum(axis=1).values\n",
    "        x[\"sales_%s_mean_decay\"%(i)] = \\\n",
    "        (tmp*np.power(0.999,np.arange(i)[::-1])).mean(axis=1).values\n",
    "        \n",
    "        tmp = get_span(dnums, dt, i-1, i)\n",
    "        x[\"nums_%s_sum_decay\"%(i)] = \\\n",
    "        (tmp*np.power(0.9,np.arange(i)[::-1])).sum(axis=1).values\n",
    "        x[\"nums_%s_mean_decay\"%(i)] = \\\n",
    "        (tmp*np.power(0.9,np.arange(i)[::-1])).mean(axis=1).values\n",
    "\n",
    "    for i in times:\n",
    "        tmp = get_span(df, dt, i-1, i)\n",
    "        x[\"sales_%s_count\"%(i)] = np.count_nonzero(tmp.values, axis=1) \n",
    "        x[\"no_sales_%s_count\"%(i)] = i - x[\"sales_%s_count\"%(i)]\n",
    "        \n",
    "    for i in times:\n",
    "        tmp = get_span(df, dt, i-1, i)\n",
    "        x[\"diff_%s_mean\"%(i)] = tmp.diff(axis=1).mean(axis=1).values\n",
    "        x[\"std_%s\"%(i)] = tmp.std(axis=1).values\n",
    "        \n",
    "        tmp = get_span(dnums, dt, i-1, i)\n",
    "        x[\"nums_diff_%s_mean\"%(i)] = tmp.diff(axis=1).mean(axis=1).values\n",
    "        x[\"nums_std_%s\"%(i)] = tmp.std(axis=1).values\n",
    "        \n",
    "    for i in times:\n",
    "        tmp = get_span(df, dt+timedelta(days=-14), i-1, i)\n",
    "        x['diff_%s_mean_2' %(i)] = tmp.diff(axis=1).mean(axis=1).values\n",
    "        x['mean_%s_decay_2' %(i)] = \\\n",
    "        (tmp * np.power(0.9, np.arange(i)[::-1])).mean(axis=1).values #sum\n",
    "        \n",
    "        tmp = get_span(dnums, dt+timedelta(days=-14), i-1, i)\n",
    "        x[\"nums_diff_%s_mean_2\"%(i)] = tmp.diff(axis=1).mean(axis=1).values\n",
    "        x['nums_mean_%s_decay_2' %(i)] = \\\n",
    "        (tmp * np.power(0.9, np.arange(i)[::-1])).mean(axis=1).values #sum        \n",
    "    \n",
    "    for i in times:\n",
    "        tmp = get_span(df, dt, i-1, i)\n",
    "        x['has_sales_days_in_last_%s' %(i)] = (tmp > 0).sum(axis=1).values\n",
    "        x[\"last_sales_days_in_last_%s\"%(i)] =\\\n",
    "        i - ((tmp>0)*np.arange(i)).max(axis=1).values\n",
    "        x[\"first_sales_days_in_last_%s\"%(i)] =\\\n",
    "        ((tmp>0)*np.arange(i,0,-1)).max(axis=1).values\n",
    "        \n",
    "    for i in np.arange(7):                  \n",
    "        x[\"nums_mean_4_dow%s\"%(i)] = get_span(dnums, dt, 28-i, 4, freq=\"7D\").mean(axis=1).values\n",
    "        x[\"nums_mean_8_dow%s\"%(i)] = get_span(dnums, dt, 56-i, 8, freq=\"7D\").mean(axis=1).values\n",
    "        x[\"nums_mean_12_dow%s\"%(i)] = get_span(dnums, dt, 84-i, 12, freq=\"7D\").mean(axis=1).values\n",
    "        \n",
    "    for i in np.arange(0,31):\n",
    "        x[\"day_%s\"%(i)] = get_span(df, dt, i, 1).values.ravel()\n",
    "    \n",
    "    if addType:\n",
    "        x = pd.DataFrame(x)\n",
    "        x[\"type\"] = df.reset_index()[\"type_id\"]\n",
    "    else:\n",
    "        x = pd.DataFrame(x)\n",
    "    \n",
    "    if is_train:\n",
    "        y = df[pd.date_range(dt+timedelta(1),periods=7)].values\n",
    "        return x,y\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Preparing dataset...\n",
      "x_train_in :  (112000, 167)\n",
      "y_train_in :  (112000, 7)\n",
      "x_train_ext :  (111800, 167)\n",
      "y_train_ext :  (111800, 7)\n"
     ]
    }
   ],
   "source": [
    "def get_span(df, dt, minus, periods, freq=\"D\"):\n",
    "    return df[pd.date_range(dt-timedelta(days=int(minus)), periods=periods, freq=freq)]\n",
    "\n",
    "n = 5\n",
    "t2018 = date(2018, 1, 14)+timedelta(28*n)\n",
    "num_days = 20*5\n",
    "\n",
    "print(\"Preparing dataset...\")\n",
    "\n",
    "x_in, y_in = [], []\n",
    "x_ext, y_ext = [], []\n",
    "addType = True\n",
    "\n",
    "for i in range(num_days):\n",
    "    delta = timedelta(days=i)\n",
    "\n",
    "    x_tmp_in, y_tmp_in = prepare_dataset_byDay(\n",
    "        df_train_in, t2018-delta, df_train_in_nums, addType=addType)\n",
    "    x_tmp_ext, y_tmp_ext = prepare_dataset_byDay(\n",
    "        df_train_ext, t2018-delta, df_train_ext_nums, addType=addType)\n",
    "\n",
    "    x_in.append(x_tmp_in)\n",
    "    y_in.append(y_tmp_in)\n",
    "    x_ext.append(x_tmp_ext)\n",
    "    y_ext.append(y_tmp_ext)\n",
    "\n",
    "x_train_in = pd.concat(x_in, axis=0)\n",
    "y_train_in = np.concatenate(y_in, axis=0)\n",
    "x_train_ext = pd.concat(x_ext, axis=0)\n",
    "y_train_ext = np.concatenate(y_ext, axis=0)\n",
    "\n",
    "print(\"x_train_in : \", x_train_in.shape)\n",
    "print(\"y_train_in : \", y_train_in.shape)\n",
    "print(\"x_train_ext : \", x_train_ext.shape)\n",
    "print(\"y_train_ext : \", y_train_ext.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "x_val_in shape:  (1120, 167)\n",
      "x_test_in shape:  (1120, 167)\n"
     ]
    }
   ],
   "source": [
    "# 验证集 2018-1-21\n",
    "valdate = date(2018, 1, 21) + timedelta(28*n)\n",
    "x_val_in, y_val_in = prepare_dataset_byDay(\n",
    "    df_train_in,  valdate, df_train_in_nums, addType=addType)\n",
    "x_val_ext, y_val_ext = prepare_dataset_byDay(\n",
    "    df_train_ext,  valdate, df_train_ext_nums, addType=addType)\n",
    "print(\"x_val_in shape: \", x_val_in.shape)\n",
    "\n",
    "# 测试集 2018-1-28\n",
    "# testdate = date(2018,1,28)\n",
    "testdate = date(2018, 1, 28) + timedelta(28*n)\n",
    "x_test_in = prepare_dataset_byDay(\n",
    "    df_train_in, testdate, df_train_in_nums,  is_train=False, addType=addType)\n",
    "x_test_ext = prepare_dataset_byDay(\n",
    "    df_train_ext, testdate, df_train_ext_nums, is_train=False, addType=addType)\n",
    "print(\"x_test_in shape: \", x_test_in.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def prepare_dataset_byWeek(df, dt, dnums=None, step=7, addType=False, is_train=True):\n",
    "    x = {}\n",
    "    n = 11\n",
    "    times = [1, 3, 5, 7, 10]\n",
    "    freq = df.columns.freqstr\n",
    "    if not freq:\n",
    "        if step==14: freq = \"2W-SUN\"\n",
    "        elif step==21: freq=\"3W-SUN\"\n",
    "        else: freq=\"4W-SUN\"\n",
    "            \n",
    "    for i in times:  \n",
    "        tmp = get_span(df, dt, i*step, i, freq=freq)\n",
    "        x[\"sales_%s_sum\"%(i)] = tmp.sum(axis=1).values\n",
    "        x[\"sales_%s_mean\"%(i)] = tmp.mean(axis=1).values\n",
    "        x[\"sales_%s_sum_decay\" % (i)] = \\\n",
    "            (tmp*np.power(0.999, np.arange(i)[::-1])).sum(axis=1).values\n",
    "        x[\"sales_%s_mean_decay\" % (i)] = \\\n",
    "            (tmp*np.power(0.999, np.arange(i)[::-1])).mean(axis=1).values\n",
    "\n",
    "        tmp = get_span(dnums, dt, i*step, i, freq=freq)\n",
    "        x[\"nums_%s_sum_decay\" % (i)] = \\\n",
    "            (tmp*np.power(0.9, np.arange(i)[::-1])).sum(axis=1).values\n",
    "        x[\"nums_%s_mean_decay\" % (i)] = \\\n",
    "            (tmp*np.power(0.9, np.arange(i)[::-1])).mean(axis=1).values\n",
    "\n",
    "    for i in times:  \n",
    "        tmp = get_span(df, dt, i*step, i, freq=freq)\n",
    "        x[\"sales_%s_count\" % (i)] = np.count_nonzero(tmp.values, axis=1)\n",
    "        x[\"no_sales_%s_count\" % (i)] = i - x[\"sales_%s_count\" % (i)]\n",
    "\n",
    "    for i in times:  \n",
    "        tmp = get_span(df, dt, i*step, i, freq=freq)\n",
    "        x[\"diff_%s_mean\" % (i)] = tmp.diff(axis=1).mean(axis=1).values\n",
    "        x[\"std_%s\" % (i)] = tmp.std(axis=1).values\n",
    "\n",
    "        tmp = get_span(dnums, dt, i*step, i, freq=freq)\n",
    "        x[\"nums_diff_%s_mean\" % (i)] = tmp.diff(axis=1).mean(axis=1).values\n",
    "        x[\"nums_std_%s\" % (i)] = tmp.std(axis=1).values\n",
    "\n",
    "    for i in times:  \n",
    "        n = step if step % 14 or 14 % step else 14\n",
    "        tmp = get_span(df, dt+timedelta(days=-n), i*step, i, freq=freq)\n",
    "        x['diff_%s_mean_2' % (i)] = tmp.diff(axis=1).mean(axis=1).values\n",
    "        x['mean_%s_decay_2' % (i)] = \\\n",
    "            (tmp * np.power(0.9, np.arange(i)\n",
    "                            [::-1])).mean(axis=1).values  # sum\n",
    "\n",
    "        tmp = get_span(dnums, dt+timedelta(days=-n), i*step, i, freq=freq)\n",
    "        x[\"nums_diff_%s_mean_2\" % (i)] = tmp.diff(axis=1).mean(axis=1).values\n",
    "        x['nums_mean_%s_decay_2' % (i)] = \\\n",
    "            (tmp * np.power(0.9, np.arange(i)\n",
    "                            [::-1])).mean(axis=1).values  \n",
    "\n",
    "    for i in times:  \n",
    "        tmp = get_span(df, dt, i*step, i, freq=freq)\n",
    "        x['has_sales_days_in_last_%s' % (i)] = (tmp > 0).sum(axis=1).values\n",
    "        x[\"last_sales_days_in_last_%s\" % (i)] =\\\n",
    "            i - ((tmp > 0)*np.arange(i)).max(axis=1).values\n",
    "        x[\"first_sales_days_in_last_%s\" % (i)] =\\\n",
    "            ((tmp > 0)*np.arange(i, 0, -1)).max(axis=1).values\n",
    "\n",
    "    for i in np.arange(1, 8):  \n",
    "        x[\"day_%s\" % (i)] = get_span(\n",
    "            df, dt, i*step, 1, freq=freq).values.ravel()\n",
    "\n",
    "    if addType:\n",
    "        x = pd.DataFrame(x)\n",
    "        x[\"type\"] = df.reset_index()[\"type_id\"]\n",
    "    else:\n",
    "        x = pd.DataFrame(x)\n",
    "\n",
    "    if is_train:\n",
    "        y = df[pd.date_range(dt, periods=1, freq=freq)].values\n",
    "        return x, y\n",
    "    return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getDatas(df_in, df_ext, df_in_nums=None, df_ext_nums=None, dt=\"2018-01-21\", step=7, num_days=int(15), is_train=True, addType=True):\n",
    "    print(\"Preparing dataset...step\", step)\n",
    "    if isinstance(dt, str):\n",
    "        dt = datetime.strptime(dt, \"%Y-%m-%d\")\n",
    "    num_days = num_days\n",
    "    x_in, y_in = [], []\n",
    "    x_ext, y_ext = [], []\n",
    "    for i in range(num_days):\n",
    "        delta = timedelta(days=i*step)\n",
    "        if is_train:\n",
    "            x_tmp_in, y_tmp_in = prepare_dataset_byWeek(\n",
    "                df_in, dt-delta, df_in_nums, step=step, addType=addType, is_train=is_train)\n",
    "            x_tmp_ext, y_tmp_ext = prepare_dataset_byWeek(\n",
    "                df_ext, dt-delta, df_ext_nums, step=step, addType=addType, is_train=is_train)\n",
    "        else:\n",
    "            x_tmp_in = prepare_dataset_byWeek(\n",
    "                df_in, dt-delta, df_in_nums, step=step, addType=addType, is_train=is_train)\n",
    "            x_tmp_ext = prepare_dataset_byWeek(\n",
    "                df_ext, dt-delta, df_ext_nums, step=step, addType=addType, is_train=is_train)\n",
    "            y_tmp_in = []\n",
    "            y_tmp_ext = []\n",
    "\n",
    "        x_in.append(x_tmp_in)\n",
    "        y_in.append(y_tmp_in)\n",
    "        x_ext.append(x_tmp_ext)\n",
    "        y_ext.append(y_tmp_ext)\n",
    "\n",
    "    x_train_in = pd.concat(x_in, axis=0)\n",
    "    y_train_in = np.concatenate(y_in, axis=0)\n",
    "    x_train_ext = pd.concat(x_ext, axis=0)\n",
    "    y_train_ext = np.concatenate(y_ext, axis=0)\n",
    "    return x_train_in, y_train_in, x_train_ext, y_train_ext"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Preparing dataset...step 7\n",
      "Preparing dataset...step 14\n",
      "Preparing dataset...step 21\n",
      "Preparing dataset...step 28\n",
      "x_train_in_W shape:  (9750, 103)\n",
      "x_train_in_2W shape:  (9750, 103)\n",
      "Preparing dataset...step 7\n",
      "Preparing dataset...step 14\n",
      "Preparing dataset...step 21\n",
      "Preparing dataset...step 28\n",
      "x_val_in_W shape:  (650, 103)\n",
      "x_val_in_2W shape:  (650, 103)\n",
      "x_val_ext_2W shape:  (641, 1)\n",
      "Preparing dataset...step 7\n",
      "Preparing dataset...step 14\n",
      "Preparing dataset...step 21\n",
      "Preparing dataset...step 28\n",
      "x_test_in_W shape:  (650, 103)\n",
      "x_test_in_2W shape:  (641, 103)\n"
     ]
    }
   ],
   "source": [
    "def get_span(df, dt, minus, periods, freq=\"D\"):\n",
    "    return df[pd.date_range(dt-timedelta(days=int(minus)), periods=periods, freq=freq)]\n",
    "\n",
    "i = -2\n",
    "train_date = [\n",
    "    df_train_in_W.columns[i],\n",
    "    df_train_in_2W.columns[i],\n",
    "    df_train_in_3W.columns[i],\n",
    "    df_train_in_4W.columns[i]\n",
    "]\n",
    "\n",
    "# 训练集\n",
    "x_train_in_W, y_train_in_W, x_train_ext_W, y_train_ext_W = getDatas(\n",
    "    df_train_in_W, df_train_ext_W, df_train_in_nums_W, df_train_ext_nums_W, train_date[0], 7)\n",
    "\n",
    "x_train_in_2W, y_train_in_2W, x_train_ext_2W, y_train_ext_2W = getDatas(\n",
    "    df_train_in_2W, df_train_ext_2W, df_train_in_nums_2W, df_train_ext_nums_2W, train_date[1], 14)\n",
    "\n",
    "x_train_in_3W, y_train_in_3W, x_train_ext_3W, y_train_ext_3W = getDatas(\n",
    "    df_train_in_3W, df_train_ext_3W, df_train_in_nums_3W, df_train_ext_nums_3W, train_date[2], 21)\n",
    "\n",
    "x_train_in_4W, y_train_in_4W, x_train_ext_4W, y_train_ext_4W = getDatas(\n",
    "    df_train_in_4W, df_train_ext_4W, df_train_in_nums_4W, df_train_ext_nums_4W, train_date[3], 28)\n",
    "print(\"x_train_in_W shape: \", x_train_in_W.shape)\n",
    "print(\"x_train_in_2W shape: \", x_train_in_2W.shape)\n",
    "\n",
    "# 验证集\n",
    "i=-1\n",
    "val_date = [\n",
    "    df_train_in_W.columns[i],\n",
    "    df_train_in_2W.columns[i],\n",
    "    df_train_in_3W.columns[i],\n",
    "    df_train_in_4W.columns[i]\n",
    "]  \n",
    "x_val_in_W, y_val_in_W, x_val_ext_W, y_val_ext_W = getDatas(\n",
    "    df_train_in_W, df_train_ext_W, df_train_in_nums_W, df_train_ext_nums_W, val_date[0], 7, 1)\n",
    "\n",
    "x_val_in_2W, y_val_in_2W, x_val_ext_2W, y_val_ext_2W = getDatas(\n",
    "    df_train_in_2W, df_train_ext_2W,  df_train_in_nums_2W, df_train_ext_nums_2W, val_date[1], 14, 1)\n",
    "\n",
    "x_val_in_3W, y_val_in_3W, x_val_ext_3W, y_val_ext_3W = getDatas(\n",
    "    df_train_in_3W, df_train_ext_3W, df_train_in_nums_3W, df_train_ext_nums_3W, val_date[2], 21, 1)\n",
    "\n",
    "x_val_in_4W, y_val_in_4W, x_val_ext_4W, y_val_ext_4W = getDatas(\n",
    "    df_train_in_4W, df_train_ext_4W, df_train_in_nums_4W, df_train_ext_nums_4W, val_date[3], 28, 1)\n",
    "\n",
    "print(\"x_val_in_W shape: \", x_val_in_W.shape)\n",
    "print(\"x_val_in_2W shape: \", x_val_in_2W.shape)\n",
    "print(\"x_val_ext_2W shape: \", y_val_ext_2W.shape)\n",
    "\n",
    "# 测试集\n",
    "test_date = []\n",
    "steps = [7,14,21,28]\n",
    "for i in range(4):\n",
    "    test_date.append(val_date[i] + timedelta(steps[i]))\n",
    "\n",
    "x_test_in_W, y_test_in_W, x_test_ext_W, y_test_ext_W = getDatas(\n",
    "    df_train_in_W, df_train_ext_W, df_train_in_nums_W, df_train_ext_nums_W, test_date[0], 7, 1, False)\n",
    "\n",
    "x_test_in_2W, y_test_in_2W, x_test_ext_2W, y_test_ext_2W = getDatas(\n",
    "    df_train_in_2W, df_train_ext_2W, df_train_in_nums_2W, df_train_ext_nums_2W, test_date[1], 14, 1, False)\n",
    "\n",
    "x_test_in_3W, y_test_in_3W, x_test_ext_3W, y_test_ext_3W = getDatas(\n",
    "    df_train_in_3W, df_train_ext_3W, df_train_in_nums_3W, df_train_ext_nums_3W, test_date[2], 21, 1, False)\n",
    "\n",
    "x_test_in_4W, y_test_in_4W, x_test_ext_4W, y_test_ext_4W = getDatas(\n",
    "    df_train_in_4W, df_train_ext_4W, df_train_in_nums_4W, df_train_ext_nums_4W, test_date[3], 28, 1, False)\n",
    "print(\"x_test_in_W shape: \", x_test_in_W.shape)\n",
    "print(\"x_test_in_2W shape: \", x_test_ext_2W.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "x_train_in_Wall = [x_train_in_W, x_train_in_2W, x_train_in_3W, x_train_in_4W]\n",
    "y_train_in_Wall = np.concatenate(\n",
    "    [y_train_in_W, y_train_in_2W, y_train_in_3W, y_train_in_4W], axis=1)\n",
    "x_train_ext_Wall = [x_train_ext_W, x_train_ext_2W,\n",
    "                    x_train_ext_3W, x_train_ext_4W]\n",
    "y_train_ext_Wall = np.concatenate(\n",
    "    [y_train_ext_W, y_train_ext_2W, y_train_ext_3W, y_train_ext_4W], axis=1)\n",
    "\n",
    "x_val_in_Wall = [x_val_in_W, x_val_in_2W, x_val_in_3W, x_val_in_4W]\n",
    "y_val_in_Wall = np.concatenate(\n",
    "    [y_val_in_W, y_val_in_2W, y_val_in_3W, y_val_in_4W], axis=1)\n",
    "x_val_ext_Wall = [x_val_ext_W, x_val_ext_2W, x_val_ext_3W, x_val_ext_4W]\n",
    "y_val_ext_Wall = np.concatenate(\n",
    "    [y_val_ext_W, y_val_ext_2W, y_val_ext_3W, y_val_ext_4W], axis=1)\n",
    "\n",
    "x_test_in_Wall = [x_test_in_W, x_test_in_2W, x_test_in_3W, x_test_in_4W]\n",
    "x_test_ext_Wall = [x_test_ext_W, x_test_ext_2W, x_test_ext_3W, x_test_ext_4W]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 算法建模\n",
    "- 天粒度预测\n",
    "- 周粒度预测"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 细粒度-1天1个模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "params1 = {\n",
    "    \"booster\": \"gblinear\",\n",
    "    \"eta\": 0.005,\n",
    "    \"alpha\": 0,\n",
    "    \"lambda\": 1,\n",
    "    \"nthread\": 4,\n",
    "    \"objective\": \"reg:linear\",\n",
    "    \"eval_metric\": \"rmse\",\n",
    "    \"silent\": 1,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================================================\n",
      "step 1\n",
      "2018-12-29 22:28:55.591979\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.396163\tdval-rmse:0.395486\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[500]\tdtrain-rmse:0.010502\tdval-rmse:0.008498\n",
      "[699]\tdtrain-rmse:0.010498\tdval-rmse:0.008486\n",
      "==================================================\n",
      "step 2\n",
      "2018-12-29 22:29:37.379147\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.39615\tdval-rmse:0.395368\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[500]\tdtrain-rmse:0.01092\tdval-rmse:0.009125\n",
      "[699]\tdtrain-rmse:0.010916\tdval-rmse:0.009114\n",
      "==================================================\n",
      "step 3\n",
      "2018-12-29 22:30:18.852348\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.396139\tdval-rmse:0.395372\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[500]\tdtrain-rmse:0.011335\tdval-rmse:0.009063\n",
      "[699]\tdtrain-rmse:0.011331\tdval-rmse:0.009052\n",
      "==================================================\n",
      "step 4\n",
      "2018-12-29 22:31:00.272398\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.396129\tdval-rmse:0.395306\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[500]\tdtrain-rmse:0.01174\tdval-rmse:0.009339\n",
      "[699]\tdtrain-rmse:0.011736\tdval-rmse:0.009328\n",
      "==================================================\n",
      "step 5\n",
      "2018-12-29 22:31:41.817697\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.39612\tdval-rmse:0.395243\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[500]\tdtrain-rmse:0.012134\tdval-rmse:0.010086\n",
      "[699]\tdtrain-rmse:0.01213\tdval-rmse:0.010075\n",
      "==================================================\n",
      "step 6\n",
      "2018-12-29 22:32:23.234350\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.396111\tdval-rmse:0.395346\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[500]\tdtrain-rmse:0.012512\tdval-rmse:0.009816\n",
      "[699]\tdtrain-rmse:0.012508\tdval-rmse:0.009806\n",
      "==================================================\n",
      "step 7\n",
      "2018-12-29 22:33:04.803953\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.396106\tdval-rmse:0.395516\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[500]\tdtrain-rmse:0.012871\tdval-rmse:0.009717\n",
      "[699]\tdtrain-rmse:0.012867\tdval-rmse:0.009708\n"
     ]
    }
   ],
   "source": [
    "# 一天一个模型\n",
    "boosting_round = [100,200,400]\n",
    "MAX_ROUNDS = np.sum(boosting_round)\n",
    "etas = [0.01, 0.001, 0.0001]\n",
    "eta_ = np.ones(MAX_ROUNDS)\n",
    "eta_list = [np.tile(eta, rounds) for rounds, eta in zip(boosting_round, etas)]\n",
    "eta_list = np.concatenate(eta_list, axis=0)\n",
    "\n",
    "if not os.path.exists(r\"./model/model1\"):\n",
    "    os.mkdir(r\"./model/model1\")\n",
    "        \n",
    "for i in range(7):\n",
    "    print(\"=\"*50)\n",
    "    print(\"step %d\"%(i+1))\n",
    "    print(datetime.now())\n",
    "    print(\"=\"*50)\n",
    "    dtrain = xgb.DMatrix(\n",
    "        data=np.concatenate([x_train_in.values,x_train_ext.values],axis=0),\n",
    "        label=np.concatenate([y_train_in[:,i], y_train_ext[:,i]], axis=0)\n",
    "    )\n",
    "    \n",
    "    dval = xgb.DMatrix(\n",
    "        data=np.concatenate([x_val_in.values,x_val_ext.values], axis=0),\n",
    "        label=np.concatenate([y_val_in[:,i], y_val_ext[:,i]], axis=0)\n",
    "    )\n",
    "    \n",
    "    bst = xgb.train(\n",
    "        params1,\n",
    "        dtrain,\n",
    "        num_boost_round=MAX_ROUNDS,\n",
    "        evals=[(dtrain,\"dtrain\"),(dval,\"dval\")],\n",
    "        early_stopping_rounds=300,\n",
    "        verbose_eval=500,\n",
    "        callbacks=[xgb.callback.reset_learning_rate(list(eta_list))]\n",
    "    )\n",
    "\n",
    "    bst.save_model(r\"./model/model1/bst_byday\"+str(i+1)+\".model\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 粗粒度-4个累积模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Training and Predicting models...\n"
     ]
    }
   ],
   "source": [
    "print(\"Training and Predicting models...\")\n",
    "params_w4 = {\n",
    "    \"booster\":\"gblinear\",\n",
    "    \"eta\":0.005,\n",
    "    \"alpha\":0,\n",
    "    \"lambda\":1,\n",
    "    \"nthread\":4,\n",
    "    \"objective\":\"reg:linear\",\n",
    "    \"eval_metric\":\"rmse\",\n",
    "    \"silent\":1\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==================================================\n",
      "step 1\n",
      "2018-12-29 22:33:46.522929\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.416029\tdval-rmse:0.41575\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[699]\tdtrain-rmse:0.0353\tdval-rmse:0.0325\n",
      "==================================================\n",
      "step 2\n",
      "2018-12-29 22:33:53.468409\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.416229\tdval-rmse:0.415548\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[699]\tdtrain-rmse:0.039435\tdval-rmse:0.044357\n",
      "==================================================\n",
      "step 3\n",
      "2018-12-29 22:33:59.051657\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.415886\tdval-rmse:0.415353\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[699]\tdtrain-rmse:0.040131\tdval-rmse:0.051377\n",
      "==================================================\n",
      "step 4\n",
      "2018-12-29 22:34:03.551629\n",
      "==================================================\n",
      "[0]\tdtrain-rmse:0.415601\tdval-rmse:0.41538\n",
      "Multiple eval metrics have been passed: 'dval-rmse' will be used for early stopping.\n",
      "\n",
      "Will train until dval-rmse hasn't improved in 300 rounds.\n",
      "[699]\tdtrain-rmse:0.042391\tdval-rmse:0.057413\n"
     ]
    }
   ],
   "source": [
    "boosting_round = [100,200, 400]\n",
    "MAX_ROUNDS = np.sum(boosting_round)\n",
    "etas = [0.01, 0.001, 0.0001] \n",
    "eta_ = np.ones(MAX_ROUNDS)\n",
    "eta_list = [np.tile(eta, rounds) for rounds, eta in zip(boosting_round, etas)]\n",
    "eta_list = np.concatenate(eta_list, axis=0)\n",
    "\n",
    "if not os.path.exists(r\"./model/model1\"):\n",
    "        os.mkdir(r\"./model/model1\")\n",
    "        \n",
    "for i in range(4):\n",
    "    print(\"=\"*50)\n",
    "    print(\"step %d\"%(i+1))\n",
    "    print(datetime.now())\n",
    "    print(\"=\"*50)\n",
    "    dtrain_w4 = xgb.DMatrix(\n",
    "        data=np.concatenate([x_train_in_Wall[i].values, x_train_ext_Wall[i].values],axis=0),\n",
    "        label=np.concatenate([y_train_in_Wall[:,i], y_train_ext_Wall[:,i]], axis=0)\n",
    "    )\n",
    "    \n",
    "    dval_w4 = xgb.DMatrix(\n",
    "        data=np.concatenate([x_val_in_Wall[i].values,x_val_ext_Wall[i].values], axis=0),\n",
    "        label=np.concatenate([y_val_in_Wall[:,i], y_val_ext_Wall[:,i]], axis=0)\n",
    "    )\n",
    "        \n",
    "    bst_w4 = xgb.train(\n",
    "        params_w4,\n",
    "        dtrain_w4,\n",
    "        num_boost_round=MAX_ROUNDS,\n",
    "        evals=[(dtrain_w4,\"dtrain\"),(dval_w4,\"dval\")],\n",
    "        early_stopping_rounds=300,\n",
    "        verbose_eval=1000,\n",
    "        callbacks=[xgb.callback.reset_learning_rate(list(eta_list))]\n",
    "    )\n",
    "    \n",
    "    bst_w4.save_model(r\"./model/model1/bst_byweek\"+str(i+1)+\".model\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 预测提交"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "def prediction(test, path=\"./model/model1\",mode=\"wd\"):\n",
    "    if mode==\"wd\":\n",
    "        week_ext, week_in, day_ext, day_in = [], [], [], []\n",
    "        results = [week_ext, week_in, day_ext, day_in]\n",
    "        bases = [\"bst_byweek%s.model\",\"bst_byweek%s.model\",\"bst_byday%s.model\",\"bst_byday%s.model\"]\n",
    "        steps = [0,0,7,7]\n",
    "        for step, res, base, data in zip(steps, results, bases, test):\n",
    "            dtest = xgb.DMatrix(data.values)\n",
    "            for i in range(step):\n",
    "                model = os.path.join(path, base%(i+1))\n",
    "                print(model)\n",
    "                bst = xgb.Booster()\n",
    "                bst.load_model(model)\n",
    "                res.append(bst.predict(dtest))\n",
    "    else:\n",
    "        W, W2, W3, W4 = [], [], [], []\n",
    "        results = [W, W2, W3, W4]\n",
    "        bases = [\"bst_byweek%s.model\"]*4\n",
    "        steps = range(4)\n",
    "        for step, res, base, data in zip(steps, results, bases, test):\n",
    "            dtest = xgb.DMatrix(data.values)\n",
    "            model = os.path.join(path, base%(step+1))\n",
    "            print(model)\n",
    "            bst = xgb.Booster()\n",
    "            bst.load_model(model)\n",
    "            res.append(bst.predict(dtest))\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getLastW(data):\n",
    "    d1 = np.array(data).squeeze().transpose()\n",
    "    d2 = np.expm1(d1)*np.array([7,14,21,28]) \n",
    "    d3 = np.diff(d2, axis=1)\n",
    "    d2[:,1:]= d3\n",
    "    d2 = np.clip(d2,0,None)\n",
    "    res = np.log1p(d2).transpose()\n",
    "    return res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def makeSubmission(preds, inds, steps=[4,4,7,7], filename=\"./submission/result.csv\"):\n",
    "    stack = []\n",
    "    for ind,pred,step in zip(inds, preds, steps):\n",
    "        pred = np.array(pred).transpose()\n",
    "        tmp = pd.DataFrame(np.expm1(pred),index=ind) \n",
    "        tmp = tmp.reset_index().drop(\"store_id\",axis=1)\n",
    "        tmp = tmp.groupby(\"type_id\").sum().values[::-1]\n",
    "        tmp = tmp.reshape(2*step)\n",
    "        stack.append(tmp)\n",
    "    stack_pred = np.concatenate(stack, axis=0)\n",
    "    stack_pred[:16] = stack_pred[:16]*1000\n",
    "    stack_pred[16:] = stack_pred[16:]*1000\n",
    "    result = pd.read_csv(\"./submission/result_org.csv\")\n",
    "    result[\"VALUE\"] = stack_pred\n",
    "    result.to_csv(filename,float_format=\"%.4f\",index=False)\n",
    "    print(\"done!\")\n",
    "    return stack_pred"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 使用"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "./model/model1/bst_byweek1.model\n",
      "./model/model1/bst_byweek2.model\n",
      "./model/model1/bst_byweek3.model\n",
      "./model/model1/bst_byweek4.model\n",
      "./model/model1/bst_byweek1.model\n",
      "./model/model1/bst_byweek2.model\n",
      "./model/model1/bst_byweek3.model\n",
      "./model/model1/bst_byweek4.model\n",
      "./model/model1/bst_byday1.model\n",
      "./model/model1/bst_byday2.model\n",
      "./model/model1/bst_byday3.model\n",
      "./model/model1/bst_byday4.model\n",
      "./model/model1/bst_byday5.model\n",
      "./model/model1/bst_byday6.model\n",
      "./model/model1/bst_byday7.model\n",
      "./model/model1/bst_byday1.model\n",
      "./model/model1/bst_byday2.model\n",
      "./model/model1/bst_byday3.model\n",
      "./model/model1/bst_byday4.model\n",
      "./model/model1/bst_byday5.model\n",
      "./model/model1/bst_byday6.model\n",
      "./model/model1/bst_byday7.model\n",
      "done!\n"
     ]
    }
   ],
   "source": [
    "x_test_in_Wall = [x_test_in_W, x_test_in_2W, x_test_in_3W, x_test_in_4W]\n",
    "x_test_ext_Wall = [x_test_ext_W, x_test_ext_2W, x_test_ext_3W, x_test_ext_4W]\n",
    "test = [x_test_ext_W, x_test_in_W, x_test_ext, x_test_in]\n",
    "\n",
    "mpath = './model/model1'\n",
    "res_ext_W = prediction(x_test_ext_Wall,path=mpath,mode=\"w\")\n",
    "res_in_W = prediction(x_test_in_Wall,path=mpath, mode=\"w\")\n",
    "res = prediction(test,path=mpath)\n",
    "\n",
    "# 求各周\n",
    "res1 = getLastW(res_ext_W)\n",
    "res2 = getLastW(res_in_W)\n",
    "results = [res1,res2,res[2],res[3]]\n",
    "\n",
    "inds = [df_train_ext_W.index, df_train_in_W.index, df_train_ext.index, df_train_in.index]\n",
    "\n",
    "# 取exp\n",
    "pred1 = makeSubmission(results, inds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:anaconda3]",
   "language": "python",
   "name": "conda-env-anaconda3-py"
  },
  "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.6.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "270px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}