4_feature_engineering.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.cluster import k_means\n",
    "import community\n",
    "import networkx as nx\n",
    "from tqdm import tqdm\n",
    "import gc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "mat = np.load('mat.npy') + 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "mat[mat <= 2] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "flat_mat = mat[0]\n",
    "for d in range(1, 31):\n",
    "    flat_mat = np.hstack([flat_mat, mat[d]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def coo_cosine_similarity(input_coo_matrix):\n",
    "    sq = lambda x: x * x.T\n",
    "    output_csr_matrix = input_coo_matrix.tocsr()\n",
    "    sqrt_sum_square_rows = np.array(np.sqrt(sq(output_csr_matrix).sum(axis=1)))[:, 0]\n",
    "    output_csr_matrix.data /= rows_sums_sqrt[input_coo_matrix.row]\n",
    "    return sq(output_csr_matrix)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def csr_cosine_similarity(input_csr_matrix):\n",
    "    similarity = np.dot(input_csr_matrix, input_csr_matrix.T)\n",
    "    square_mag = similarity.diagonal()\n",
    "    inv_square_mag = 1 / square_mag\n",
    "    inv_square_mag[np.isinf(inv_square_mag)] = 0\n",
    "    inv_mag = np.sqrt(inv_square_mag)\n",
    "    return (similarity * inv_mag).T * inv_mag "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/apple/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:4: RuntimeWarning: divide by zero encountered in true_divide\n",
      "  after removing the cwd from sys.path.\n"
     ]
    }
   ],
   "source": [
    "## Generation graph based on the time series similarity\n",
    "cos = csr_cosine_similarity(flat_mat)\n",
    "\n",
    "G = nx.Graph()\n",
    "thres = np.percentile(cos[cos > 0], 75)\n",
    "\n",
    "for i in tqdm(range(15584)):\n",
    "    for j in range(i+1, 15584):\n",
    "        if cos[i][j] > thres:\n",
    "            G.add_edge(i, j, weight=cos[i][j])\n",
    "\n",
    "partition = community.best_partition(G)\n",
    "\n",
    "partition = pd.DataFrame({'link':list(partition.keys()),'sim_group':list(partition.values())})\n",
    "\n",
    "mapp = pd.read_csv('map.csv')\n",
    "mapp = dict(zip(mapp['id'], mapp['link']))\n",
    "\n",
    "partition['link'] = partition['link'].apply(lambda x: mapp[x])\n",
    "\n",
    "partition.to_csv('./data/partition_group.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generation graph based on the latent factor similarity\n",
    "\n",
    "group = k_means(P, 24)\n",
    "\n",
    "group_latent = group[1]\n",
    "\n",
    "mapp_re = {y:x for x,y in mapp.items()}\n",
    "\n",
    "latent_group = pd.DataFrame(np.array([[mapp_re[x] for x in range(len(P))],group_latent]).T)\n",
    "\n",
    "latent_group.columns =  ['link','latent_group']\n",
    "\n",
    "latent_group.to_csv('./data/latent_group.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generation graph based on the spatial topology\n",
    "\n",
    "with open('./data/topo.txt') as f:\n",
    "    content = f.read()\n",
    "\n",
    "content = [x.split('\\t') for x in content.split('\\n')]\n",
    "\n",
    "G = nx.Graph()\n",
    "\n",
    "for row in content:\n",
    "    inr = row[0]\n",
    "    outrs = row[1].split(',')\n",
    "    for outr in outrs:\n",
    "        G.add_edge(inr, outr)\n",
    "\n",
    "partition = community.best_partition(G)\n",
    "\n",
    "topo_group = pd.DataFrame(np.array([list(partition.keys()), list(partition.values())]).T)\n",
    "\n",
    "topo_group.columns = ['link','topo_group']\n",
    "\n",
    "topo_group.to_csv('./data/topo_group.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### Feature 1205\n",
    "for date in range(0, 32):\n",
    "    train = pd.read_csv('./data/traffic/train_table_%02d.gz'%date)\n",
    "\n",
    "    ## 根据原始信息做的特征\n",
    "\n",
    "    train['date'] = date - 1\n",
    "\n",
    "    train['time_diff'] = train['predict_time'] - train['current_time']\n",
    "\n",
    "    train['predict_hour'] = train['predict_time'] // 30\n",
    "\n",
    "    train['week'] = train['date'] % 7\n",
    "\n",
    "    def check_workday(x):\n",
    "        if x in [5,6,12,13,19,20,26,27]:\n",
    "            return 0\n",
    "        else:\n",
    "            return 1\n",
    "\n",
    "    train['workday'] = train['date'].apply(check_workday)\n",
    "\n",
    "    ## 加入路段特征\n",
    "\n",
    "    with open('./data/attr.txt') as f:\n",
    "        content = f.read()\n",
    "\n",
    "    attr = pd.DataFrame([x.split('\\t') for x in content.split('\\n')])[:-1]\n",
    "\n",
    "    attr.columns = ['attr_%d'%i for i in range(9)]\n",
    "\n",
    "    attr = attr.astype(np.float32)\n",
    "\n",
    "    train = pd.merge(left=train, right=attr, left_on = 'link', right_on='attr_0',how='left')\n",
    "\n",
    "    train = train.drop(columns='attr_0')\n",
    "\n",
    "    ## 加入隐含因子\n",
    "\n",
    "    P = np.load('finalP.npy')\n",
    "\n",
    "    Q = np.load('finalQ_pred.npy')\n",
    "\n",
    "    Q_local = Q[720 * (date - 1): 720 * date]\n",
    "\n",
    "    mapp = pd.read_csv('map.csv')\n",
    "    mapp = dict(zip(mapp['link'], mapp['id']))\n",
    "\n",
    "    features = []\n",
    "    pred = []\n",
    "    pmean, qmean = [],[]\n",
    "    pstd, qstd = [],[]\n",
    "    for i,eachrow in train.iterrows():\n",
    "        pred.append(np.dot(P[mapp[int(eachrow['link'])]], Q_local[int(eachrow['predict_time'])]))\n",
    "        pmean.append(np.average(P[mapp[int(eachrow['link'])]]))\n",
    "        qmean.append(np.average(Q_local[int(eachrow['predict_time'])]))\n",
    "        pstd.append(np.std(P[mapp[int(eachrow['link'])]]))\n",
    "        qstd.append(np.std(Q_local[int(eachrow['predict_time'])]))\n",
    "        features.append(np.hstack([P[mapp[int(eachrow['link'])]], Q_local[int(eachrow['predict_time'])]]))\n",
    "\n",
    "    features = pd.DataFrame(features)\n",
    "    features.columns = ['P%d'%x for x in range(64)] + ['Q%d'%x for x in range(64)]\n",
    "    train = pd.concat([train, features], axis = 1)\n",
    "    train['mf_pred'] = pred\n",
    "    train['p_mean'] = pmean\n",
    "    train['q_mean'] = qmean\n",
    "    train['p_std'] = pstd\n",
    "    train['q_std'] = qstd\n",
    "\n",
    "\n",
    "    topo_group = pd.read_csv('./data/topo_group.csv')\n",
    "\n",
    "    latent_group = pd.read_csv('./data/latent_group.csv')\n",
    "\n",
    "    train = pd.merge(left=train, right = topo_group, how='left', on='link')\n",
    "\n",
    "    train = pd.merge(left=train, right = latent_group, how='left', on='link')\n",
    "\n",
    "    ## 抽取昨天的矩阵特征\n",
    "\n",
    "    mat = np.load('mat.npy') + 1\n",
    "\n",
    "    mat_last = mat[date - 1 - 1]\n",
    "\n",
    "    time_average = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "    link_average = np.sum(mat_last, axis = 1) / np.sum(mat_last > 0, axis = 1)\n",
    "\n",
    "    ## 昨天所有统计值\n",
    "    features = []\n",
    "    for i,eachrow in train.iterrows():\n",
    "        link = mapp[eachrow['link']]\n",
    "        time = int(eachrow['predict_time'])\n",
    "        features.append([time_average[time], link_average[link]])\n",
    "    features = pd.DataFrame(features)\n",
    "    features.columns = ['time_avg_lastday','link_avg_lastday']\n",
    "    train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "    ## 昨天目标区域附近统计值\n",
    "    features = []\n",
    "    for i,eachrow in train.iterrows():\n",
    "        link = mapp[eachrow['link']]\n",
    "        time = int(eachrow['predict_time'])\n",
    "        average5 = mat_last[link, time - 5: time+5]\n",
    "        average10 = mat_last[link, time - 10: time+10]\n",
    "        average15 = mat_last[link, time - 15: time+15]\n",
    "        average20 = mat_last[link, time - 20: time+20]\n",
    "        average30 = mat_last[link, time - 30: time+30]\n",
    "        features.append([np.sum(average5) / np.sum(average5 > 0),\n",
    "                         np.sum(average10) / np.sum(average10 > 0),\n",
    "                         np.sum(average15) / np.sum(average15 > 0),\n",
    "                         np.sum(average20) / np.sum(average20 > 0),\n",
    "                         np.sum(average30) / np.sum(average30 > 0),\n",
    "                        ])\n",
    "    features = pd.DataFrame(features)\n",
    "    features.columns = ['target_avg_lastday_5','target_avg_lastday_10',\n",
    "                        'target_avg_lastday_15','target_avg_lastday_20','target_avg_lastday_30']\n",
    "    train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "    ## 抽取前天的特征\n",
    "    mat = np.load('mat.npy') + 1\n",
    "\n",
    "    mat_last = mat[date - 1 - 2]\n",
    "\n",
    "    time_average = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "    link_average = np.sum(mat_last, axis = 1) / np.sum(mat_last > 0, axis = 1)\n",
    "\n",
    "    ## 前天所有统计值\n",
    "    features = []\n",
    "    for i,eachrow in train.iterrows():\n",
    "        link = mapp[eachrow['link']]\n",
    "        time = int(eachrow['predict_time'])\n",
    "        features.append([time_average[time], link_average[link]])\n",
    "    features = pd.DataFrame(features)\n",
    "    features.columns = ['time_avg_beforelastday','link_avg_beforelastday']\n",
    "    train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "    ## 前天目标区域附近统计值\n",
    "    features = []\n",
    "    for i,eachrow in train.iterrows():\n",
    "        link = mapp[eachrow['link']]\n",
    "        time = int(eachrow['predict_time'])\n",
    "        average5 = mat_last[link, time - 5: time+5]\n",
    "        average10 = mat_last[link, time - 10: time+10]\n",
    "        average15 = mat_last[link, time - 15: time+15]\n",
    "        average20 = mat_last[link, time - 20: time+20]\n",
    "        average30 = mat_last[link, time - 30: time+30]\n",
    "        features.append([np.sum(average5) / np.sum(average5 > 0),\n",
    "                         np.sum(average10) / np.sum(average10 > 0),\n",
    "                         np.sum(average15) / np.sum(average15 > 0),\n",
    "                         np.sum(average20) / np.sum(average20 > 0),\n",
    "                         np.sum(average30) / np.sum(average30 > 0),\n",
    "                        ])\n",
    "    features = pd.DataFrame(features)\n",
    "    features.columns = ['target_avg_beforelastday_5','target_avg_beforelastday_10',\n",
    "                        'target_avg_beforelastday_15','target_avg_beforelastday_20','target_avg_beforelastday_30']\n",
    "    train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "    ## 抽取一周前的矩阵特征\n",
    "\n",
    "    mat = np.load('mat.npy') + 1\n",
    "\n",
    "    mat_last = mat[date - 1 - 7]\n",
    "\n",
    "    time_average = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "    link_average = np.sum(mat_last, axis = 1) / np.sum(mat_last > 0, axis = 1)\n",
    "\n",
    "    ## 上周所有统计值\n",
    "    features = []\n",
    "    for i,eachrow in train.iterrows():\n",
    "        link = mapp[eachrow['link']]\n",
    "        time = int(eachrow['predict_time'])\n",
    "        features.append([time_average[time], link_average[link]])\n",
    "    features = pd.DataFrame(features)\n",
    "    features.columns = ['time_avg_lastweek','link_avg_lastweek']\n",
    "    train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "    ## 上周目标区域附近统计值\n",
    "    features = []\n",
    "    for i,eachrow in train.iterrows():\n",
    "        link = mapp[eachrow['link']]\n",
    "        time = int(eachrow['predict_time'])\n",
    "        average5 = mat_last[link, time - 5: time+5]\n",
    "        average10 = mat_last[link, time - 10: time+10]\n",
    "        average15 = mat_last[link, time - 15: time+15]\n",
    "        average20 = mat_last[link, time - 20: time+20]\n",
    "        average30 = mat_last[link, time - 30: time+30]\n",
    "        features.append([np.sum(average5) / np.sum(average5 > 0),\n",
    "                         np.sum(average10) / np.sum(average10 > 0),\n",
    "                         np.sum(average15) / np.sum(average15 > 0),\n",
    "                         np.sum(average20) / np.sum(average20 > 0),\n",
    "                         np.sum(average30) / np.sum(average30 > 0),\n",
    "                        ])\n",
    "    features = pd.DataFrame(features)\n",
    "    features.columns = ['target_avg_lastweek_5','target_avg_lastweek_10',\n",
    "                        'target_avg_lastweek_15','target_avg_lastweek_20','target_avg_lastweek_30']\n",
    "    train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "    ## 提取翌日特征\n",
    "    mat = np.load('mat.npy') + 1\n",
    "\n",
    "    week_index = []\n",
    "    for i in range(-10, 10):\n",
    "        week_index.append(date - 1 + i * 7)\n",
    "    week_index = [x for x in week_index if 0 <= x <= 31]\n",
    "    mat_last = mat[week_index]\n",
    "    mat_last = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "    time_average = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "    link_average = np.sum(mat_last, axis = 1) / np.sum(mat_last > 0, axis = 1)\n",
    "\n",
    "    ## 所有该翌日统计值\n",
    "    features = []\n",
    "    for i,eachrow in train.iterrows():\n",
    "        link = mapp[eachrow['link']]\n",
    "        time = int(eachrow['predict_time'])\n",
    "        features.append([time_average[time], link_average[link]])\n",
    "    features = pd.DataFrame(features)\n",
    "    features.columns = ['time_avg_week','link_avg_week']\n",
    "    train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "    ## 翌日目标区域附近统计值\n",
    "    features = []\n",
    "    for i,eachrow in train.iterrows():\n",
    "        link = mapp[eachrow['link']]\n",
    "        time = int(eachrow['predict_time'])\n",
    "        average5 = mat_last[link, time - 5: time+5]\n",
    "        average10 = mat_last[link, time - 10: time+10]\n",
    "        average15 = mat_last[link, time - 15: time+15]\n",
    "        average20 = mat_last[link, time - 20: time+20]\n",
    "        average30 = mat_last[link, time - 30: time+30]\n",
    "        features.append([np.sum(average5) / np.sum(average5 > 0),\n",
    "                         np.sum(average10) / np.sum(average10 > 0),\n",
    "                         np.sum(average15) / np.sum(average15 > 0),\n",
    "                         np.sum(average20) / np.sum(average20 > 0),\n",
    "                         np.sum(average30) / np.sum(average30 > 0),\n",
    "                        ])\n",
    "    features = pd.DataFrame(features)\n",
    "    features.columns = ['target_avg_week_5','target_avg_week_10',\n",
    "                        'target_avg_week_15','target_avg_week_20','target_avg_week_30']\n",
    "    train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "    train.to_csv('./data/traffic/train_table_%02d_1205.gz'%date)\n",
    "    gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = 32\n",
    "\n",
    "train = pd.read_csv('./test_table.gz')\n",
    "\n",
    "## 根据原始信息做的特征\n",
    "\n",
    "train['date'] = date - 1\n",
    "\n",
    "train['time_diff'] = train['predict_time'] - train['current_time']\n",
    "\n",
    "train['predict_hour'] = train['predict_time'] // 30\n",
    "\n",
    "train['week'] = train['date'] % 7\n",
    "\n",
    "def check_workday(x):\n",
    "    if x in [5,6,12,13,19,20,26,27]:\n",
    "        return 0\n",
    "    else:\n",
    "        return 1\n",
    "\n",
    "train['workday'] = train['date'].apply(check_workday)\n",
    "\n",
    "## 加入路段特征\n",
    "\n",
    "with open('./data/attr.txt') as f:\n",
    "    content = f.read()\n",
    "\n",
    "attr = pd.DataFrame([x.split('\\t') for x in content.split('\\n')])[:-1]\n",
    "\n",
    "attr.columns = ['attr_%d'%i for i in range(9)]\n",
    "\n",
    "attr = attr.astype(np.float32)\n",
    "\n",
    "train = pd.merge(left=train, right=attr, left_on = 'link', right_on='attr_0',how='left')\n",
    "\n",
    "train = train.drop(columns='attr_0')\n",
    "\n",
    "## 加入隐含因子\n",
    "\n",
    "P = np.load('finalP.npy')\n",
    "\n",
    "Q = np.load('finalQ_pred.npy')\n",
    "\n",
    "Q_local = Q[720 * (date - 1): 720 * date]\n",
    "\n",
    "mapp = pd.read_csv('map.csv')\n",
    "mapp = dict(zip(mapp['link'], mapp['id']))\n",
    "\n",
    "features = []\n",
    "pred = []\n",
    "pmean, qmean = [],[]\n",
    "pstd, qstd = [],[]\n",
    "for i,eachrow in train.iterrows():\n",
    "    pred.append(np.dot(P[mapp[int(eachrow['link'])]], Q_local[int(eachrow['predict_time'])]))\n",
    "    pmean.append(np.average(P[mapp[int(eachrow['link'])]]))\n",
    "    qmean.append(np.average(Q_local[int(eachrow['predict_time'])]))\n",
    "    pstd.append(np.std(P[mapp[int(eachrow['link'])]]))\n",
    "    qstd.append(np.std(Q_local[int(eachrow['predict_time'])]))\n",
    "    features.append(np.hstack([P[mapp[int(eachrow['link'])]], Q_local[int(eachrow['predict_time'])]]))\n",
    "\n",
    "features = pd.DataFrame(features)\n",
    "features.columns = ['P%d'%x for x in range(64)] + ['Q%d'%x for x in range(64)]\n",
    "train = pd.concat([train, features], axis = 1)\n",
    "train['mf_pred'] = pred\n",
    "train['p_mean'] = pmean\n",
    "train['q_mean'] = qmean\n",
    "train['p_std'] = pstd\n",
    "train['q_std'] = qstd\n",
    "\n",
    "\n",
    "## 加入link组信息\n",
    "\n",
    "# group = k_means(P, 24)\n",
    "\n",
    "# group_latent = group[1]\n",
    "\n",
    "# mapp_re = {y:x for x,y in mapp.items()}\n",
    "\n",
    "# latent_group = pd.DataFrame(np.array([[mapp_re[x] for x in range(len(P))],group_latent]).T)\n",
    "\n",
    "# latent_group.columns =  ['link','latent_group']\n",
    "\n",
    "# with open('./data/topo.txt') as f:\n",
    "#     content = f.read()\n",
    "\n",
    "# content = [x.split('\\t') for x in content.split('\\n')]\n",
    "\n",
    "# G = nx.Graph()\n",
    "\n",
    "# for row in content:\n",
    "#     inr = row[0]\n",
    "#     outrs = row[1].split(',')\n",
    "#     for outr in outrs:\n",
    "#         G.add_edge(inr, outr)\n",
    "\n",
    "# topo_group = pd.DataFrame(np.array([list(partition.keys()), list(partition.values())]).T)\n",
    "\n",
    "# topo_group.columns = ['link','topo_group']\n",
    "\n",
    "# latent_group.to_csv('./data/latent_group.csv', index=False)\n",
    "\n",
    "# topo_group.to_csv('./data/topo_group.csv', index=False)\n",
    "\n",
    "topo_group = pd.read_csv('./data/topo_group.csv')\n",
    "\n",
    "latent_group = pd.read_csv('./data/latent_group.csv')\n",
    "\n",
    "train = pd.merge(left=train, right = topo_group, how='left', on='link')\n",
    "\n",
    "train = pd.merge(left=train, right = latent_group, how='left', on='link')\n",
    "\n",
    "## 抽取昨天的矩阵特征\n",
    "\n",
    "mat = np.load('mat.npy') + 1\n",
    "\n",
    "mat_last = mat[date - 1 - 1]\n",
    "\n",
    "time_average = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "link_average = np.sum(mat_last, axis = 1) / np.sum(mat_last > 0, axis = 1)\n",
    "\n",
    "## 昨天所有统计值\n",
    "features = []\n",
    "for i,eachrow in train.iterrows():\n",
    "    link = mapp[eachrow['link']]\n",
    "    time = int(eachrow['predict_time'])\n",
    "    features.append([time_average[time], link_average[link]])\n",
    "features = pd.DataFrame(features)\n",
    "features.columns = ['time_avg_lastday','link_avg_lastday']\n",
    "train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "## 昨天目标区域附近统计值\n",
    "features = []\n",
    "for i,eachrow in train.iterrows():\n",
    "    link = mapp[eachrow['link']]\n",
    "    time = int(eachrow['predict_time'])\n",
    "    average5 = mat_last[link, time - 5: time+5]\n",
    "    average10 = mat_last[link, time - 10: time+10]\n",
    "    average15 = mat_last[link, time - 15: time+15]\n",
    "    average20 = mat_last[link, time - 20: time+20]\n",
    "    average30 = mat_last[link, time - 30: time+30]\n",
    "    features.append([np.sum(average5) / np.sum(average5 > 0),\n",
    "                     np.sum(average10) / np.sum(average10 > 0),\n",
    "                     np.sum(average15) / np.sum(average15 > 0),\n",
    "                     np.sum(average20) / np.sum(average20 > 0),\n",
    "                     np.sum(average30) / np.sum(average30 > 0),\n",
    "                    ])\n",
    "features = pd.DataFrame(features)\n",
    "features.columns = ['target_avg_lastday_5','target_avg_lastday_10',\n",
    "                    'target_avg_lastday_15','target_avg_lastday_20','target_avg_lastday_30']\n",
    "train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "## 抽取前天的特征\n",
    "mat = np.load('mat.npy') + 1\n",
    "\n",
    "mat_last = mat[date - 1 - 2]\n",
    "\n",
    "time_average = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "link_average = np.sum(mat_last, axis = 1) / np.sum(mat_last > 0, axis = 1)\n",
    "\n",
    "## 前天所有统计值\n",
    "features = []\n",
    "for i,eachrow in train.iterrows():\n",
    "    link = mapp[eachrow['link']]\n",
    "    time = int(eachrow['predict_time'])\n",
    "    features.append([time_average[time], link_average[link]])\n",
    "features = pd.DataFrame(features)\n",
    "features.columns = ['time_avg_beforelastday','link_avg_beforelastday']\n",
    "train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "## 前天目标区域附近统计值\n",
    "features = []\n",
    "for i,eachrow in train.iterrows():\n",
    "    link = mapp[eachrow['link']]\n",
    "    time = int(eachrow['predict_time'])\n",
    "    average5 = mat_last[link, time - 5: time+5]\n",
    "    average10 = mat_last[link, time - 10: time+10]\n",
    "    average15 = mat_last[link, time - 15: time+15]\n",
    "    average20 = mat_last[link, time - 20: time+20]\n",
    "    average30 = mat_last[link, time - 30: time+30]\n",
    "    features.append([np.sum(average5) / np.sum(average5 > 0),\n",
    "                     np.sum(average10) / np.sum(average10 > 0),\n",
    "                     np.sum(average15) / np.sum(average15 > 0),\n",
    "                     np.sum(average20) / np.sum(average20 > 0),\n",
    "                     np.sum(average30) / np.sum(average30 > 0),\n",
    "                    ])\n",
    "features = pd.DataFrame(features)\n",
    "features.columns = ['target_avg_beforelastday_5','target_avg_beforelastday_10',\n",
    "                    'target_avg_beforelastday_15','target_avg_beforelastday_20','target_avg_beforelastday_30']\n",
    "train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "## 抽取一周前的矩阵特征\n",
    "\n",
    "mat = np.load('mat.npy') + 1\n",
    "\n",
    "mat_last = mat[date - 1 - 7]\n",
    "\n",
    "time_average = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "link_average = np.sum(mat_last, axis = 1) / np.sum(mat_last > 0, axis = 1)\n",
    "\n",
    "## 上周所有统计值\n",
    "features = []\n",
    "for i,eachrow in train.iterrows():\n",
    "    link = mapp[eachrow['link']]\n",
    "    time = int(eachrow['predict_time'])\n",
    "    features.append([time_average[time], link_average[link]])\n",
    "features = pd.DataFrame(features)\n",
    "features.columns = ['time_avg_lastweek','link_avg_lastweek']\n",
    "train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "## 上周目标区域附近统计值\n",
    "features = []\n",
    "for i,eachrow in train.iterrows():\n",
    "    link = mapp[eachrow['link']]\n",
    "    time = int(eachrow['predict_time'])\n",
    "    average5 = mat_last[link, time - 5: time+5]\n",
    "    average10 = mat_last[link, time - 10: time+10]\n",
    "    average15 = mat_last[link, time - 15: time+15]\n",
    "    average20 = mat_last[link, time - 20: time+20]\n",
    "    average30 = mat_last[link, time - 30: time+30]\n",
    "    features.append([np.sum(average5) / np.sum(average5 > 0),\n",
    "                     np.sum(average10) / np.sum(average10 > 0),\n",
    "                     np.sum(average15) / np.sum(average15 > 0),\n",
    "                     np.sum(average20) / np.sum(average20 > 0),\n",
    "                     np.sum(average30) / np.sum(average30 > 0),\n",
    "                    ])\n",
    "features = pd.DataFrame(features)\n",
    "features.columns = ['target_avg_lastweek_5','target_avg_lastweek_10',\n",
    "                    'target_avg_lastweek_15','target_avg_lastweek_20','target_avg_lastweek_30']\n",
    "train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "## 提取翌日特征\n",
    "mat = np.load('mat.npy') + 1\n",
    "\n",
    "week_index = []\n",
    "for i in range(-10, 10):\n",
    "    week_index.append(date - 1 + i * 7)\n",
    "week_index = [x for x in week_index if 0 <= x <= 31]\n",
    "mat_last = mat[week_index]\n",
    "mat_last = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "time_average = np.sum(mat_last, axis = 0) / np.sum(mat_last > 0, axis = 0)\n",
    "\n",
    "link_average = np.sum(mat_last, axis = 1) / np.sum(mat_last > 0, axis = 1)\n",
    "\n",
    "## 所有该翌日统计值\n",
    "features = []\n",
    "for i,eachrow in train.iterrows():\n",
    "    link = mapp[eachrow['link']]\n",
    "    time = int(eachrow['predict_time'])\n",
    "    features.append([time_average[time], link_average[link]])\n",
    "features = pd.DataFrame(features)\n",
    "features.columns = ['time_avg_week','link_avg_week']\n",
    "train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "## 翌日目标区域附近统计值\n",
    "features = []\n",
    "for i,eachrow in train.iterrows():\n",
    "    link = mapp[eachrow['link']]\n",
    "    time = int(eachrow['predict_time'])\n",
    "    average5 = mat_last[link, time - 5: time+5]\n",
    "    average10 = mat_last[link, time - 10: time+10]\n",
    "    average15 = mat_last[link, time - 15: time+15]\n",
    "    average20 = mat_last[link, time - 20: time+20]\n",
    "    average30 = mat_last[link, time - 30: time+30]\n",
    "    features.append([np.sum(average5) / np.sum(average5 > 0),\n",
    "                     np.sum(average10) / np.sum(average10 > 0),\n",
    "                     np.sum(average15) / np.sum(average15 > 0),\n",
    "                     np.sum(average20) / np.sum(average20 > 0),\n",
    "                     np.sum(average30) / np.sum(average30 > 0),\n",
    "                    ])\n",
    "features = pd.DataFrame(features)\n",
    "features.columns = ['target_avg_week_5','target_avg_week_10',\n",
    "                    'target_avg_week_15','target_avg_week_20','target_avg_week_30']\n",
    "train = pd.concat([train, features], axis = 1)\n",
    "\n",
    "train.to_csv('./data/test_table_1205.gz')"
   ]
  }
 ],
 "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.6.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}