NTOCC_Price_Prediciton.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.数据载入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 438,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np # linear algebra\n",
    "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "#train.xlsx： 附件一 test.xlsx：附件二\n",
    "DATA_PATH = 'data'\n",
    "train = pd.read_excel('data/train.xlsx',index = False)\n",
    "test = pd.read_excel('data/test.xlsx', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 411,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 412,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(16016, 1489)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(train), len(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 414,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 413,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['编号', '任务id', '总里程', '业务类型', '需求类型1', '需求类型2', '线路编码', '是否续签', '始发网点',\n",
       "       '目的网点', '计划发车时间', '计划发车日期', '分拨时间', '车辆长度', '车辆吨位', '标的展示策略', '打包类型',\n",
       "       '子包号', '装卸的次数', '装的次数', '运输等级', '卸的次数', '是否需要装卸', '始发地省份名称', '车辆类型分类',\n",
       "       '目的地省份名称', '交易对象', '计划卸货完成时间', '计划卸货等待时长', '计划靠车时间', '计划到达时间', '地区类型',\n",
       "       '地区', '标的_创建时间', '标的_创建日期', '异常状态', '交易模式', '需求紧急程度'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 439,
   "metadata": {},
   "outputs": [],
   "source": [
    "very_bad_cols = ['子包号','装卸的次数','装的次数','卸的次数','是否需要装卸'] #丢弃列\n",
    "bad_cols_inTrain = ['C端议价最低价','B端议价最低价','调价审核完成时间']\n",
    "part_Nan_cols = ['始发地省份名称','目的地省份名称','计划卸车等待时长']  #可以使用均值填充\n",
    "target_cols = ['路线价格（不含税）','线路总成本']\n",
    "id_cols = ['任务id']\n",
    "redundant_cols = ['计划发车日期'] #内容重复\n",
    "digital_cols = ['总里程','车辆长度','车辆吨位','计划卸货等待时长']\n",
    "category_cols = ['业务类型','需求类型1','需求类型2','线路编码','是否续签','始发网点','目的网点','计划发车时间','计划发车日期',\n",
    "                 '分拨时间','标的展示策略','打包类型','子包号','装卸的次数','装的次数','运输等级','卸的次数','是否需要装卸','始发地省份名称','车辆类型分类',\n",
    "                 '目的地省份名称','交易对象','计划卸货完成时间','计划靠车时间','计划到达时间','地区类型','地区','标的_创建时间','标的_创建日期','异常状态',\n",
    "                 '交易模式','需求紧急程度']\n",
    "train_digital_cols = ['总里程','线路价格（不含税）','调价比例','线路指导价（不含税）','实际靠车时间','实际结束时间','实际发车时间','交易开始时间',\n",
    "                      '交易结束时间','计划卸货完成时间','计划卸货等待时长','计划靠车时间','计划到达时间','地区','标的_创建时间','标的_创建日期','C端议价最低价',\n",
    "                      'B端议价最低价','B端议价反馈数量','线路总成本','调价ECP创建时间','调价审核完成时间','最后一次询价时间','C端议价反馈数量','交易成功时长',\n",
    "                      '交易成功时间','交易成功日期','计划发车时间','计划发车日期','分拨时间','实际到车时间']\n",
    "# train_category_cols = "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 数据清洗"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1 丢弃缺失值过多&多余的列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 440,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = train.drop(columns = bad_cols_inTrain)\n",
    "test = test.drop(columns = very_bad_cols)\n",
    "train = train.drop(columns = very_bad_cols)\n",
    "train = train.drop(columns = redundant_cols)\n",
    "test = test.drop(columns = redundant_cols)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 计算执行时间"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 476,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "print(type(train['线路编码']))\n",
    "train['执行时间'] = train['线路编码'] - train['始发网点'] - train['目的网点']\n",
    "#train_exe_time = DataFrame(train_exe_time,columns = '执行时间')\n",
    "test['执行时间'] = test['线路编码'] - test['始发网点'] - test['目的网点']\n",
    "\n",
    "train.info()\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 时间戳"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 442,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#标的_创建时间格式整理\n",
    "ser1 = train['标的_创建日期'].astype(str)\n",
    "ser2 = train['标的_创建时间'].astype(str)\n",
    "train['标的_创建时间点'] = ser1.str.cat(ser2,sep = '  ').apply(pd.to_datetime,format='%Y-%m-%d  %H:%M:%S')\n",
    "# train['标的_创建时间点'] = ser1.str.cat(ser2,sep = '  ')\n",
    "\n",
    "ser3 = test['标的_创建日期'].astype(str)\n",
    "ser4 = test['标的_创建时间'].astype(str)\n",
    "test['标的_创建时间点'] = ser3.str.cat(ser4,sep = '  ').apply(pd.to_datetime,format='%Y-%m-%d  %H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 443,
   "metadata": {},
   "outputs": [],
   "source": [
    "#将所有时间修改为时间戳\n",
    "import time\n",
    "##TRAIN\n",
    "#for prediction\n",
    "train['标的_创建时间点TS'] = train['标的_创建时间点'].apply(lambda x:x.timestamp())\n",
    "train['实际到车时间TS'] = train['实际到车时间'].apply(lambda x:x.timestamp())\n",
    "train['计划发车时间TS'] = train['计划发车时间'].apply(lambda x:x.timestamp())\n",
    "train['计划靠车时间TS'] = train['计划靠车时间'].apply(lambda x:x.timestamp())\n",
    "train['计划到达时间TS'] = train['计划到达时间'].apply(lambda x:x.timestamp())\n",
    "train['实际到车时间TS'] = train['实际到车时间'].apply(lambda x:x.timestamp())\n",
    "train['实际靠车时间TS'] = train['实际靠车时间'].apply(lambda x:x.timestamp())\n",
    "train['实际结束时间TS'] = train['实际结束时间'].apply(lambda x:x.timestamp() if not pd.isnull(x) else 0) #缺失值\n",
    "train['实际发车时间TS'] = train['实际发车时间'].apply(lambda x:x.timestamp())\n",
    "train['交易开始时间TS'] = train['交易开始时间'].apply(lambda x:x.timestamp())\n",
    "train['交易结束时间TS'] = train['交易结束时间'].apply(lambda x:x.timestamp())\n",
    "train['分拨时间TS'] = train['分拨时间'].apply(lambda x:x.timestamp() if not pd.isnull(x) else 0) #缺失值\n",
    "train['计划卸货完成时间TS'] = train['计划卸货完成时间'].apply(lambda x:x.timestamp() if not pd.isnull(x) else 0) #缺失值\n",
    "#for evaluation\n",
    "train['最后一次询价时间TS'] = train['最后一次询价时间'].apply(lambda x:x.timestamp() if not pd.isnull(x) else 0)\n",
    "train['调价ECP创建时间TS'] = train['调价ECP创建时间'].apply(lambda x:x.timestamp() if not pd.isnull(x) else 0)\n",
    "##TEST\n",
    "test['计划发车时间TS'] = test['计划发车时间'].apply(lambda x:x.timestamp())\n",
    "test['分拨时间TS'] = test['分拨时间'].apply(lambda x:x.timestamp())\n",
    "test['计划卸货完成时间TS'] = test['计划卸货完成时间'].apply(lambda x:x.timestamp())\n",
    "test['计划靠车时间TS'] = test['计划靠车时间'].apply(lambda x:x.timestamp())\n",
    "test['计划到达时间TS'] = test['计划到达时间'].apply(lambda x:x.timestamp())\n",
    "test['标的_创建时间点TS'] = test['标的_创建时间点'].apply(lambda x:x.timestamp())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4 缺失值处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 477,
   "metadata": {},
   "outputs": [],
   "source": [
    "#time 均值填充\n",
    "# train['实际结束时间TS'].fillna((train['实际结束时间TS'].mean()), inplace = True)\n",
    "# train['计划卸货完成时间TS'].fillna((train['计划卸货完成时间TS'].mean()), inplace = True)\n",
    "def FillNaTWithAve(name,df):\n",
    "    nonZero = df[name].count() - (df[name] == 0).sum()\n",
    "    mean = sum(df[name])/nonZero\n",
    "    df[name][df[name]==0] = mean\n",
    "\n",
    "FillNaTWithAve('实际结束时间TS', train)\n",
    "FillNaTWithAve('计划卸货完成时间TS', train)\n",
    "FillNaTWithAve('最后一次询价时间TS', train)\n",
    "FillNaTWithAve('调价ECP创建时间TS', train)\n",
    "FillNaTWithAve('分拨时间TS', train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 445,
   "metadata": {},
   "outputs": [],
   "source": [
    "#categorical 按照种类最多的进行填补\n",
    "#pack_type_col_dict = {'单边':0,'人工':1,'周期流向':2,'周期往返':3,}\n",
    "train['打包类型'] = train['打包类型'].apply(lambda x:x if not pd.isnull(x) else '周期流向')\n",
    "train['调价紧急程度'] = train['调价紧急程度'].apply(lambda x:x if not pd.isnull(x) else '常规')\n",
    "train['始发地省份名称'] = train['始发地省份名称'].apply(lambda x:x if not pd.isnull(x) else '广东省')\n",
    "train['目的地省份名称'] = train['目的地省份名称'].apply(lambda x:x if not pd.isnull(x) else '广东省')\n",
    "train['交易模式'] = train['交易模式'].apply(lambda x:x if not pd.isnull(x) else '抢单')\n",
    "\n",
    "test['始发地省份名称'] = test['始发地省份名称'].apply(lambda x:x if not pd.isnull(x) else '广东省')\n",
    "test['目的地省份名称'] = test['目的地省份名称'].apply(lambda x:x if not pd.isnull(x) else '广东省')\n",
    "#numerical 部分使用0填充\n",
    "train['计划卸货等待时长'] = train['计划卸货等待时长'].apply(lambda x:x if not pd.isnull(x) else 0)\n",
    "\n",
    "test['计划卸货等待时长'] = test['计划卸货等待时长'].apply(lambda x:x if not pd.isnull(x) else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 478,
   "metadata": {},
   "outputs": [],
   "source": [
    "#丢弃部分训练样本\n",
    "train.drop(np.where(np.isnan(train['线路总成本']))[0], axis = 0, inplace=True)\n",
    "train.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 479,
   "metadata": {},
   "outputs": [],
   "source": [
    "train.drop(columns = ['index'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.5 去除异常值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 448,
   "metadata": {},
   "outputs": [],
   "source": [
    "#交易成功时长\n",
    "train = train.loc[((train['交易成功时长'] >= 1) & (train['交易成功时长'] < 45000))] #高斯分布去除1% outliers\n",
    "#执行时长\n",
    "train = train.loc[((train['执行时间'] > 0))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. 建立新特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 449,
   "metadata": {},
   "outputs": [],
   "source": [
    "#执行时间的建立见2.2\n",
    "#建立时间差特征\n",
    "train['交易总时长'] = train['交易结束时间TS'] - train['交易开始时间TS']\n",
    "train['司机工作总时长'] = train['实际结束时间TS'] - train['实际发车时间TS']\n",
    "train['取货至目的地计划所用时间'] = train['计划到达时间TS'] - train['计划靠车时间TS']\n",
    "train['标的-分拨时间差'] = train['标的_创建时间点TS'] - train['分拨时间TS']\n",
    "train['发车到目的地计划所用时间'] = train['计划到达时间TS'] - train['计划发车时间TS']\n",
    "train['计划卸货时间'] = train['计划卸货完成时间TS'] - train['计划到达时间TS']\n",
    "train['标的-发车时间差'] = train['计划发车时间TS'] - train['标的_创建时间点TS']\n",
    "train['发车-靠车时间差'] = train['计划发车时间TS'] - train['计划靠车时间TS']\n",
    "\n",
    "test['发车到目的地计划所用时间'] = test['计划到达时间TS'] - test['计划发车时间TS']\n",
    "test['标的-分拨时间差'] = test['标的_创建时间点TS'] - test['分拨时间TS']\n",
    "test['计划卸货时间'] = test['计划卸货完成时间TS'] - test['计划到达时间TS']\n",
    "test['取货至目的地计划所用时间'] = test['计划到达时间TS'] - test['计划靠车时间TS']\n",
    "test['标的-发车时间差'] = test['计划发车时间TS'] - test['标的_创建时间点TS']\n",
    "test['发车-靠车时间差'] = test['计划发车时间TS'] - test['计划靠车时间TS']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.数据相关性分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 450,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "bad_cols_inTrain = ['C端议价最低价','B端议价最低价','调价审核完成时间']\n",
    "part_Nan_cols = ['始发地省份名称','目的地省份名称','计划卸车等待时长']  #可以使用均值填充\n",
    "target_cols = ['路线价格（不含税）','线路总成本','线路指导价（不含税）']\n",
    "id_cols = ['任务id']\n",
    "\n",
    "corr_cols = ['总里程','执行时间','线路编码','调价比例','线路指导价（不含税）','交易开始时间TS','交易结束时间TS',\n",
    "             '计划卸货等待时长','计划靠车时间TS','计划到达时间TS','地区','标的_创建时间点TS', '线路总成本','交易成功时长',\n",
    "                      '交易成功时间TS','计划发车时间TS','计划发车日期TS','分拨时间TS','实际到车时间TS','交易总时长',\n",
    "            '司机工作总时长','取货至目的地计划所用时间','标的-分拨时间差', '发车到目的地计划所用时间', '计划卸货时间', '标的-发车时间差',\n",
    "            '业务类型','需求类型1','需求类型2','是否续签','始发网点','目的网点','标的展示策略','打包类型','运输等级','始发地省份名称','车辆类型分类',\n",
    "                 '目的地省份名称','交易对象','地区类型','地区', '异常状态', '交易模式','需求紧急程度']\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 绘制相关性热力图"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 437,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 中文字体设置-黑体\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像是负号'-'显示为方块的问题\n",
    "sns.set(font='SimHei',font_scale=8)  # 解决Seaborn中文显示问题并调整字体大小\n",
    "\n",
    "figure, ax = plt.subplots(figsize=(100, 100))\n",
    "data = pd.concat([train.drop(columns = ['异常状态','车辆类型分类']), test.drop(columns = ['编号'])]).corr() ** 2\n",
    "#data = np.tril(data, k=-1)\n",
    "data[data==0] = np.nan\n",
    "sns.heatmap(np.sqrt(data), annot=False, cmap='viridis', ax=ax)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 434,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = train.corrwith(train['线路指导价（不含税）']).agg('square')\n",
    "data = data.drop(['线路价格（不含税）','线路总成本','线路指导价（不含税）'])\n",
    "figure, ax = plt.subplots(figsize=(80, 10))\n",
    "data.agg('sqrt').plot.bar(ax=ax, title = '线路指导价（不含税）影响因素')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 433,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = train.corrwith(train['线路价格（不含税）']).agg('square')\n",
    "data = data.drop(['线路价格（不含税）','线路总成本','线路指导价（不含税）'])\n",
    "figure, ax = plt.subplots(figsize=(80, 10))\n",
    "data.agg('sqrt').plot.bar(ax=ax, title = '线路价格（不含税）影响因素')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 432,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = train.corrwith(train['线路总成本']).agg('square')\n",
    "data = data.drop(['线路价格（不含税）','线路总成本','线路指导价（不含税）'])\n",
    "figure, ax = plt.subplots(figsize=(80, 10))\n",
    "data.agg('sqrt').plot.bar(ax=ax, title = '线路总成本影响因素')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 影响调价比例因素\n",
    "#### 用于二次、三次报价"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 431,
   "metadata": {},
   "outputs": [],
   "source": [
    "# train['待接单时长'] = train['计划靠车时间TS'] - train['标的_创建时间点TS']\n",
    "data = train.corrwith(train['调价比例']).agg('square')\n",
    "data = data.drop(['调价比例'])\n",
    "figure, ax = plt.subplots(figsize=(50, 10))\n",
    "data.agg('sqrt').plot.bar(ax=ax, fontsize = 30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 430,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 中文字体设置-黑体\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像是负号'-'显示为方块的问题\n",
    "sns.set(font='SimHei',font_scale=1.5)  # 解决Seaborn中文显示问题并调整字体大小\n",
    "sns.set_style(\"whitegrid\",{\"font.sans-serif\":['simhei', 'Arial']})\n",
    "\n",
    "fig, ax = plt.subplots(nrows=1,ncols=1,figsize=(10,5)) \n",
    "\n",
    "ax = sns.distplot(train['调价比例'],color = 'r', ax = ax)\n",
    "x = ax.lines[0].get_xdata()\n",
    "y = ax.lines[0].get_ydata()\n",
    "maxid = np.argmax(y)\n",
    "# low_quant = np.quantile(x,0.25,interpolation='lower')\n",
    "# high_quant = np.quantile(x, 0.75, interpolation = 'higher')\n",
    "\n",
    "ax.plot(x[maxid], y[maxid], 'bo', ms = 6)\n",
    "ax.plot([x[7],x[54]], [0.1,0.1], 'bo', ms = 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 交易成功时长、总里程、C端议价反馈数量、利润率、线路总成本分布\n",
    "##### 用于评价定价"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 480,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['利润率'] = (train['线路价格（不含税）'] - train['线路总成本']) / train['线路总成本']\n",
    "data = train.corrwith(train['交易成功时长']).agg('square')\n",
    "data = data.drop(['交易成功时长'])\n",
    "figure, ax = plt.subplots(figsize=(80, 10))\n",
    "data.agg('sqrt').plot.bar(ax=ax, title = '交易成功时长影响因素')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 341,
   "metadata": {},
   "outputs": [],
   "source": [
    "train['利润率'] = (train['线路价格（不含税）'] - train['线路总成本']) / train['线路总成本']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.set_style(\"whitegrid\")\n",
    "fig, ((ax1, ax2),(ax3,ax4), (ax5, ax6)) = plt.subplots(nrows=3,ncols=2,figsize=(30,12)) \n",
    "sns.set(font='SimHei',font_scale=2.5)\n",
    "# sns.despine(left = 10000)\n",
    "\n",
    "ax1 = sns.kdeplot(train['交易成功时长'], ax = ax1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 428,
   "metadata": {},
   "outputs": [],
   "source": [
    "# from scipy.stats import norm\n",
    "\n",
    "sns.set_style(\"whitegrid\")\n",
    "fig, ((ax1, ax2),(ax3,ax4), (ax5, ax6)) = plt.subplots(nrows=3,ncols=2,figsize=(30,12)) \n",
    "sns.set(font='SimHei',font_scale=2.5)\n",
    "# sns.despine(left = 10000)\n",
    "\n",
    "ax1 = sns.kdeplot(train['交易成功时长'], ax = ax1)\n",
    "ax2 = sns.kdeplot(train['总里程'], ax = ax2)\n",
    "ax3 = sns.kdeplot(train['C端议价反馈数量'], ax = ax3)\n",
    "ax4 = sns.kdeplot(train['利润率'], ax = ax4)\n",
    "ax5 = sns.kdeplot(train['车辆长度'], ax = ax5)\n",
    "ax6 = sns.kdeplot(train['线路总成本'], ax = ax6)\n",
    "\n",
    "train = train.drop(columns = '利润率')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 427,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "x1 = ax1.lines[0].get_xdata()\n",
    "y1 = ax1.lines[0].get_ydata()\n",
    "maxid1 = np.argmax(y1)\n",
    "print(x1[maxid1],y1[maxid1])\n",
    "\n",
    "x2 = ax2.lines[0].get_xdata()\n",
    "y2 = ax2.lines[0].get_ydata()\n",
    "maxid2 = np.argmax(y2)\n",
    "print(x2[maxid2],y2[maxid2])\n",
    "\n",
    "x3 = ax3.lines[0].get_xdata()\n",
    "y3 = ax3.lines[0].get_ydata()\n",
    "maxid3 = np.argmax(y3)\n",
    "print(x3[maxid3],y3[maxid3])\n",
    "\n",
    "x4 = ax4.lines[0].get_xdata()\n",
    "y4 = ax4.lines[0].get_ydata()\n",
    "maxid4 = np.argmax(y4)\n",
    "print(x4[maxid4],y4[maxid4])\n",
    "\n",
    "x5 = ax5.lines[0].get_xdata()\n",
    "y5 = ax5.lines[0].get_ydata()\n",
    "maxid5 = np.argmax(y5)\n",
    "print(x5[maxid5],y5[maxid5])\n",
    "\n",
    "x6 = ax6.lines[0].get_xdata()\n",
    "y6 = ax6.lines[0].get_ydata()\n",
    "maxid6 = np.argmax(y6)\n",
    "print(x6[maxid6],y6[maxid6])\n",
    "\n",
    "print('最小值：')\n",
    "print(np.max(x1), np.max(x2), np.max(x3), np.max(x4), np.max(x5), np.max(x6))\n",
    "print('最大值：')\n",
    "print(np.min(x1), np.min(x2), np.min(x3), np.min(x4), np.min(x5), np.min(x6))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 371,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = train.drop(columns = '利润率')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. 数据编码与归一化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 452,
   "metadata": {},
   "outputs": [],
   "source": [
    "prediction_cols = ['始发地省份名称', '分拨时间', '运输等级', '计划到达时间', '目的地省份名称', '标的_创建时间', \n",
    "                   '始发网点', '需求紧急程度', '交易模式', '卸的次数', '是否需要装卸', '计划靠车时间', '计划发车日期', '车辆长度', \n",
    "                   '装的次数', '是否续签', '交易对象', '车辆吨位', '目的网点', '异常状态', '计划发车时间', '车辆类型分类', '任务id',\n",
    "                   '需求类型2', '总里程', '打包类型', '标的展示策略', '业务类型', '计划卸货等待时长', '地区', '标的_创建日期', '线路编码', \n",
    "                   '计划卸货完成时间', '需求类型1', '地区类型', '子包号']\n",
    "#目的网点 始发网点 看作digital\n",
    "digital_cols = ['总里程', '线路编码', '始发网点', '目的网点', '车辆长度', '车辆吨位','计划卸货等待时长',\n",
    "               '执行时间','计划发车时间TS', '分拨时间TS', '计划卸货完成时间TS','计划靠车时间TS', '计划到达时间TS',\n",
    "                '标的_创建时间点TS', '发车到目的地计划所用时间', '标的-分拨时间差',\n",
    "       '计划卸货时间', '取货至目的地计划所用时间', '标的-发车时间差', '发车-靠车时间差']\n",
    "category_cols_onehot = ['业务类型A','业务类型B','需求类型1A','需求类型1B','需求类型1C','需求类型2A','需求类型2B','是否续签A','是否续签B','是否续签C','是否续签D',\n",
    "                        '打包类型A','打包类型B','打包类型C','打包类型D','运输等级A','运输等级B','运输等级C','始发地省份名称A','始发地省份名称B',\n",
    "                        '始发地省份名称C','始发地省份名称D','车辆类型分类', '目的地省份名称A','目的地省份名称B','目的地省份名称C','目的地省份名称D','交易对象A','交易对象B','交易对象C',\n",
    "                        '地区类型A','地区类型B','地区A','地区B','地区C','地区D','地区E','地区F', '异常状态', '交易模式A','交易模式B','交易模式C','需求紧急程度A','需求紧急程度B','需求紧急程度C']\n",
    "#目的网点 始发网点 看作category\n",
    "category_cols = ['业务类型','需求类型1','需求类型2','是否续签','打包类型','运输等级','始发地省份名称','车辆类型分类',\n",
    "                 '目的地省份名称','交易对象', '地区类型','地区', '异常状态', '交易模式','需求紧急程度']\n",
    "\n",
    "train_digital_cols = ['总里程','线路价格（不含税）','调价比例','线路指导价（不含税）','实际靠车时间','实际结束时间','实际发车时间','交易开始时间',\n",
    "                      '交易结束时间','计划卸货完成时间','计划卸货等待时长','计划靠车时间','计划到达时间','地区','标的_创建时间','标的_创建日期','C端议价最低价',\n",
    "                      'B端议价最低价','B端议价反馈数量','线路总成本','调价ECP创建时间','调价审核完成时间','最后一次询价时间','C端议价反馈数量','交易成功时长',\n",
    "                      '交易成功时间','交易成功日期','计划发车时间','计划发车日期','分拨时间','实际到车时间']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Digital"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 453,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import MinMaxScaler\n",
    "\n",
    "sacalar = MinMaxScaler()\n",
    "train_digital = sacalar.fit_transform(train[digital_cols])\n",
    "test_digital = sacalar.transform(test[digital_cols])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Categorical"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 483,
   "metadata": {},
   "outputs": [],
   "source": [
    "test['交易模式'] = test['交易模式'].astype(str)\n",
    "train['交易模式'] = train['交易模式'].astype(str)\n",
    "from sklearn.preprocessing import LabelEncoder, OneHotEncoder\n",
    "\n",
    "train_category, test_category = None, None\n",
    "drop_cols = [ ]\n",
    "for col in [var for var in category_cols if var not in drop_cols]:\n",
    "    lbe, ohe = LabelEncoder(), OneHotEncoder()\n",
    "    \n",
    "    lbe.fit(pd.concat([train[col], test[col]]).values.reshape(-1, 1))\n",
    "    train[col] = lbe.transform(train[col])\n",
    "    test[col] = lbe.transform(test[col])\n",
    "    \n",
    "    ohe.fit(pd.concat([train[col], test[col]]).values.reshape(-1, 1))\n",
    "    oht_train = ohe.transform(train[col].values.reshape(-1, 1)).todense()\n",
    "    oht_test = ohe.transform(test[col].values.reshape(-1, 1)).todense()\n",
    "    \n",
    "    if train_category is None:\n",
    "        train_category = oht_train\n",
    "        test_category = oht_test\n",
    "    else:\n",
    "        train_category = np.hstack((train_category, oht_train))\n",
    "        test_category = np.hstack((test_category, oht_test))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 455,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((15201, 20), (1489, 20), (15201, 45), (1489, 45))"
      ]
     },
     "execution_count": 455,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_digital.shape, test_digital.shape, train_category.shape, test_category.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 456,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((15201, 65), (1489, 65))"
      ]
     },
     "execution_count": 456,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_features = np.hstack((train_digital, train_category))\n",
    "test_features = np.hstack((test_digital, test_category))\n",
    "train_features.shape, test_features.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 457,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15201,)"
      ]
     },
     "execution_count": 457,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "target_cols = ['线路价格（不含税）','线路总成本','线路指导价（不含税）']\n",
    "train_labels1 = train[target_cols[0]]\n",
    "train_labels2 = train[target_cols[1]]\n",
    "train_labels3 = train[target_cols[2]]\n",
    "train_labels1.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. 在验证集上预测\n",
    "#### (线路价格:train_labels1 线路总成本: train_labels2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 485,
   "metadata": {},
   "outputs": [],
   "source": [
    "#数据划分，10%为验证集\n",
    "from sklearn.model_selection import train_test_split\n",
    "X_train, X_val, y_train, y_val = train_test_split(train_features, train_labels3, test_size=0.10, random_state=42)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 主成分分析降维"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "保留的特征数：\n",
      "15\n",
      "特征所占比重分别为：\n",
      "[0.30838612 0.15774897 0.10853085 0.09251935 0.07850997 0.06415782\n",
      " 0.04841064 0.03045264 0.02433734 0.01998103 0.0151864  0.01361913\n",
      " 0.00765552 0.00625841 0.00570833]\n",
      "特征所占比重之和：\n",
      "0.9814625268835183\n",
      "新的特征为:\n",
      "(13680, 15)\n"
     ]
    }
   ],
   "source": [
    "from sklearn.decomposition import PCA\n",
    "#pca = PCA(n_components = 'mle')\n",
    "pca = PCA(n_components = 15)\n",
    "X_val_PCA_new = pca.fit_transform(X_val)\n",
    "train_features_PCA_new = pca.fit_transform(train_features)\n",
    "test_features_PCA_new = pca.fit_transform(test_features)\n",
    "X_train_PCA_new = pca.fit_transform(X_train)\n",
    "print('保留的特征数：')\n",
    "print(pca.n_components_)\n",
    "print('特征所占比重分别为：')\n",
    "print(pca.explained_variance_ratio_)\n",
    "print('特征所占比重之和：')\n",
    "print(sum(pca.explained_variance_ratio_))\n",
    "print(\"新的特征为:\")\n",
    "print(X_train_new.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Xgboost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 373,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[16:14:03] WARNING: C:\\Users\\Administrator\\workspace\\xgboost-win64_release_1.1.0\\src\\learner.cc:480: \n",
      "Parameters: { bst:eta, bst:max_depth, silent } might not be used.\n",
      "\n",
      "  This may not be accurate due to some parameters are only used in language bindings but\n",
      "  passed down to XGBoost core.  Or some parameters are not used but slip through this\n",
      "  verification. Please open an issue if you find above cases.\n",
      "\n",
      "\n",
      "MSE: 1049.3706541477445\n",
      "R-Square: 0.9998780641596996\n"
     ]
    }
   ],
   "source": [
    "#Best for label2\n",
    "import xgboost as xgb\n",
    "from sklearn import metrics\n",
    "#dataset\n",
    "dtrain = xgb.DMatrix(X_train, label = y_train)\n",
    "\n",
    "#parameters\n",
    "num_round = 300\n",
    "param = {'bst:max_depth':3, 'bst:eta':1, 'silent':1, 'objective':'reg:squarederror', 'nthread':2 }\n",
    "plst = param.items()\n",
    "\n",
    "#fit the model\n",
    "bst = xgb.train(param, dtrain, num_round)\n",
    "#save the model\n",
    "bst.save_model('0001.model')\n",
    "\n",
    "dtest = xgb.DMatrix(X_val)\n",
    "ypred1 = bst.predict(dtest)\n",
    "\n",
    "val_MSE = metrics.mean_squared_error(y_val, ypred1) #验证集上的auc值\n",
    "print('MSE:',val_MSE)\n",
    "\n",
    "from sklearn.metrics import r2_score\n",
    "r2 = r2_score(y_val, ypred1)\n",
    "print('R-Square:',r2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 460,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[22:38:28] WARNING: C:\\Users\\Administrator\\workspace\\xgboost-win64_release_1.1.0\\src\\learner.cc:480: \n",
      "Parameters: { bst:eta, bst:max_depth, silent } might not be used.\n",
      "\n",
      "  This may not be accurate due to some parameters are only used in language bindings but\n",
      "  passed down to XGBoost core.  Or some parameters are not used but slip through this\n",
      "  verification. Please open an issue if you find above cases.\n",
      "\n",
      "\n",
      "MSE: 39866.3669650506\n",
      "R-Square: 0.9974878166411488\n"
     ]
    }
   ],
   "source": [
    "#Best for label1\n",
    "import xgboost as xgb\n",
    "from sklearn import metrics\n",
    "#dataset\n",
    "dtrain = xgb.DMatrix(X_train, label = y_train)\n",
    "\n",
    "#parameters\n",
    "num_round = 300\n",
    "param = {'bst:max_depth':3, 'bst:eta':1, 'silent':1, 'objective':'reg:squarederror', 'nthread':2 }\n",
    "plst = param.items()\n",
    "\n",
    "#fit the model\n",
    "bst = xgb.train(param, dtrain, num_round)\n",
    "#save the model\n",
    "bst.save_model('0001.model')\n",
    "\n",
    "dtest = xgb.DMatrix(X_val)\n",
    "ypred1 = bst.predict(dtest)\n",
    "\n",
    "val_MSE = metrics.mean_squared_error(y_val, ypred1) #验证集上的auc值\n",
    "print('MSE:',val_MSE)\n",
    "\n",
    "from sklearn.metrics import r2_score\n",
    "r2 = r2_score(y_val, ypred1)\n",
    "print('R-Square:',r2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 线性回归"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 393,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSE: 943.1033187924988\n",
      "R-Square: 0.995927306217744\n"
     ]
    }
   ],
   "source": [
    "#labels2\n",
    "from sklearn.linear_model import LinearRegression\n",
    "\n",
    "clf_after = LinearRegression()\n",
    "clf_after.fit(X_train, y_train)\n",
    "\n",
    "ypred2 = clf_after.predict(X_val)\n",
    "ypred2.shape\n",
    "test_MSE = metrics.mean_squared_error(y_val,ypred)#验证集上的auc值\n",
    "print('MSE:',test_MSE)\n",
    "from sklearn.metrics import r2_score\n",
    "r2 = r2_score(y_val, ypred2)\n",
    "print('R-Square:',r2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 461,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSE: 1527980.0858834076\n",
      "R-Square: 0.9823368036358394\n"
     ]
    }
   ],
   "source": [
    "#labels1\n",
    "from sklearn.linear_model import LinearRegression\n",
    "\n",
    "clf_after = LinearRegression()\n",
    "clf_after.fit(X_train, y_train)\n",
    "\n",
    "ypred2 = clf_after.predict(X_val)\n",
    "ypred2.shape\n",
    "test_MSE = metrics.mean_squared_error(y_val,ypred)#验证集上的auc值\n",
    "print('MSE:',test_MSE)\n",
    "from sklearn.metrics import r2_score\n",
    "r2 = r2_score(y_val, ypred2)\n",
    "print('R-Square:',r2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 随机森林"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 377,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSE: 855.3593863610247\n",
      "R-Square: 0.9999006080786398\n"
     ]
    }
   ],
   "source": [
    "#Best for label2\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "# grid search for the best parameters\n",
    "max_features = {0.2,0.3,0.35,0.4}\n",
    "n_estimators = {80,90,100,120}\n",
    "rf = RandomForestRegressor(random_state=0,max_features = 0.35, n_estimators = 90)\n",
    "rf.fit(X_train,y_train)\n",
    "ypred3 = rf.predict(X_val)\n",
    "test_MSE = metrics.mean_squared_error(y_val,ypred3)#验证集上的auc值\n",
    "print('MSE:',test_MSE)\n",
    "from sklearn.metrics import r2_score\n",
    "r2 = r2_score(y_val, ypred3)\n",
    "print('R-Square:',r2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 467,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSE: 29766.57068474688\n",
      "R-Square: 0.9981242563790715\n"
     ]
    }
   ],
   "source": [
    "#Best for label1 0.2, 100\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "# grid search for the best parameters\n",
    "max_features = {0.2,0.3,0.35,0.4}\n",
    "n_estimators = {80,90,100,120}\n",
    "rf = RandomForestRegressor(random_state=0,max_features = 0.3, n_estimators =100)\n",
    "rf.fit(X_train,y_train)\n",
    "ypred3 = rf.predict(X_val)\n",
    "test_MSE = metrics.mean_squared_error(y_val,ypred3)#验证集上的auc值\n",
    "print('MSE:',test_MSE)\n",
    "from sklearn.metrics import r2_score\n",
    "r2 = r2_score(y_val, ypred3)\n",
    "print('R-Square:',r2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ensemble Model 集成模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 376,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSE: 943.1033187924988\n",
      "R-Square: 0.9998904123197914\n"
     ]
    }
   ],
   "source": [
    "#Ensemble lable2(成本价)\n",
    "ypred = 0.05*ypred1 + 0.05*ypred2 + 0.9*ypred3\n",
    "test_MSE = metrics.mean_squared_error(y_val,ypred)#验证集上的auc值\n",
    "print('MSE:',test_MSE)\n",
    "from sklearn.metrics import r2_score\n",
    "r2 = r2_score(y_val, ypred)\n",
    "print('R-Square:',r2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 470,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MSE: 30736.33815109412\n",
      "R-Square: 0.998063146378929\n"
     ]
    }
   ],
   "source": [
    "#Ensemble lable1(指导价)\n",
    "ypred = 0.2*ypred1 + 0.1*ypred2 + 0.7*ypred3\n",
    "test_MSE = metrics.mean_squared_error(y_val,ypred)#验证集上的auc值\n",
    "print('MSE:',test_MSE)\n",
    "from sklearn.metrics import r2_score\n",
    "r2 = r2_score(y_val, ypred)\n",
    "print('R-Square:',r2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 利用模型预测并保存结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 471,
   "metadata": {},
   "outputs": [],
   "source": [
    "#用全部样本训练模型:总成本 RF\n",
    "#Best for label2\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "rf = RandomForestRegressor(random_state=0,max_features = 0.35, n_estimators = 90)\n",
    "rf.fit(train_features, train_labels2)\n",
    "total_cost = rf.predict(test_features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 474,
   "metadata": {},
   "outputs": [],
   "source": [
    "#用全部样本训练模型:定价 label1 RF\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "rf = RandomForestRegressor(random_state=0,max_features = 0.35, n_estimators = 90)\n",
    "rf.fit(train_features, train_labels1)\n",
    "first_price = rf.predict(test_features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 484,
   "metadata": {},
   "outputs": [],
   "source": [
    "First_price = pd.Series(first_price,name = '预测一次报价')\n",
    "Total_cost = pd.Series(total_cost, name = '预测总成本')\n",
    "First_price.to_csv('First_price.csv')\n",
    "Total_cost.to_csv('Total_cost.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 利用随机森林模型探索特征重要性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 425,
   "metadata": {},
   "outputs": [],
   "source": [
    "#总成本\n",
    "names = ['总里程', '线路编码', '始发网点', '目的网点', '车辆长度', '车辆吨位','计划卸货等待时长',\n",
    "               '执行时间','计划发车时间TS', '分拨时间TS', '计划卸货完成时间TS','计划靠车时间TS', '计划到达时间TS',\n",
    "                '标的_创建时间点TS', '发车到目的地计划所用时间', '标的-分拨时间差',\n",
    "       '计划卸货时间', '取货至目的地计划所用时间', '标的-发车时间差', '发车-靠车时间差','业务类型A','业务类型B','需求类型1A','需求类型1B','需求类型1C',\n",
    "         '需求类型2A','需求类型2B','是否续签A','是否续签B','是否续签C','是否续签D',\n",
    "                        '打包类型A','打包类型B','打包类型C','打包类型D','运输等级A','运输等级B','运输等级C','始发地省份名称A','始发地省份名称B',\n",
    "                        '始发地省份名称C','始发地省份名称D','车辆类型分类', '目的地省份名称A','目的地省份名称B','目的地省份名称C','目的地省份名称D','交易对象A','交易对象B','交易对象C',\n",
    "                        '地区类型A','地区类型B','地区A','地区B','地区C','地区D','地区E','地区F', '异常状态', '交易模式A','交易模式B','交易模式C','需求紧急程度A','需求紧急程度B','需求紧急程度C']\n",
    "Feature_importances = sorted(zip(map(lambda x: round(x, 4), rf.feature_importances_), names), reverse=True)\n",
    "print(Feature_importances)\n",
    "\n",
    "feature_imp_values = sorted(rf.feature_importances_, reverse = True)\n",
    "feature_imp_values\n",
    "Feature_imp_Series = pd.Series(feature_imp_values, index = names)\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 中文字体设置-黑体\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像是负号'-'显示为方块的问题\n",
    "sns.set(font='SimHei',font_scale=1.5)  # 解决Seaborn中文显示问题并调整字体大小\n",
    "sns.set_style(\"whitegrid\",{\"font.sans-serif\":['simhei', 'Arial']})\n",
    "fig, ax = plt.subplots(nrows=1,ncols=1,figsize=(21,4)) \n",
    "\n",
    "Feature_imp_Series.plot.bar(ax=ax, title = '特征重要性')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#指导价\n",
    "names = ['总里程', '线路编码', '始发网点', '目的网点', '车辆长度', '车辆吨位','计划卸货等待时长',\n",
    "               '执行时间','计划发车时间TS', '分拨时间TS', '计划卸货完成时间TS','计划靠车时间TS', '计划到达时间TS',\n",
    "                '标的_创建时间点TS', '发车到目的地计划所用时间', '标的-分拨时间差',\n",
    "       '计划卸货时间', '取货至目的地计划所用时间', '标的-发车时间差', '发车-靠车时间差','业务类型A','业务类型B','需求类型1A','需求类型1B','需求类型1C',\n",
    "         '需求类型2A','需求类型2B','是否续签A','是否续签B','是否续签C','是否续签D',\n",
    "                        '打包类型A','打包类型B','打包类型C','打包类型D','运输等级A','运输等级B','运输等级C','始发地省份名称A','始发地省份名称B',\n",
    "                        '始发地省份名称C','始发地省份名称D','车辆类型分类', '目的地省份名称A','目的地省份名称B','目的地省份名称C','目的地省份名称D','交易对象A','交易对象B','交易对象C',\n",
    "                        '地区类型A','地区类型B','地区A','地区B','地区C','地区D','地区E','地区F', '异常状态', '交易模式A','交易模式B','交易模式C','需求紧急程度A','需求紧急程度B','需求紧急程度C']\n",
    "Feature_importances = sorted(zip(map(lambda x: round(x, 4), rf.feature_importances_), names), reverse=True)\n",
    "print(Feature_importances)\n",
    "\n",
    "feature_imp_values = sorted(rf.feature_importances_, reverse = True)\n",
    "feature_imp_values\n",
    "Feature_imp_Series = pd.Series(feature_imp_values, index = names)\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 中文字体设置-黑体\n",
    "plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像是负号'-'显示为方块的问题\n",
    "sns.set(font='SimHei',font_scale=1.5)  # 解决Seaborn中文显示问题并调整字体大小\n",
    "sns.set_style(\"whitegrid\",{\"font.sans-serif\":['simhei', 'Arial']})\n",
    "fig, ax = plt.subplots(nrows=1,ncols=1,figsize=(21,4)) \n",
    "\n",
    "Feature_imp_Series.plot.bar(ax=ax, title = '特征重要性')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. 基于历史概率分布拟合的定价规则"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 归一化后的影响因素分布 目的网点、执行时间"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "# wait_Time = (train['待接单时长'] - train['待接单时长'].mean()) / train['待接单时长'].std()\n",
    "Des_net =  (train['目的网点'] - train['目的网点'].mean()) / train['目的网点'].std()\n",
    "exe_Time = (train['执行时间'] - train['执行时间'].mean()) / train['执行时间'].std()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 424,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sns.set_style(\"whitegrid\")\n",
    "fig, (ax1, ax2) = plt.subplots(nrows=1,ncols=2,figsize=(20,6)) \n",
    "sns.set(font='SimHei',font_scale=1)\n",
    "# sns.despine(left = 10000)\n",
    "ax1 = sns.kdeplot(exe_Time, ax = ax1)\n",
    "ax2 = sns.kdeplot(Des_net, ax = ax2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 使用CFTool 拟合的多项式模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 320,
   "metadata": {},
   "outputs": [],
   "source": [
    "Des_net =  train['目的网点']\n",
    "exe_Time = train['执行时间']\n",
    "#三次报价计算（加入噪声） f(D,T)\n",
    "def third_price(D,T,length, mu1, mu2, sigma1, sigma2, per):\n",
    "    #单位向量\n",
    "    i_original = []\n",
    "    for i in range(0,length): i_original.append(1)\n",
    "    I = pd.Series(i_original)\n",
    "    d1 = np.random.normal(loc=mu1,scale=sigma1,size=length) # mean : loc std :  scale num : size\n",
    "    d2 = np.random.normal(loc = mu2, scale = sigma2, size = length) # deviations\n",
    "    p = [1.837,-0.2201,0.02774,0.007887,0.005703,-0.001838,-0.0001485,0,0]\n",
    "    DT = [I, D,T, D**2, D*T, T**2, T*D**2,D*T**2, T**3]\n",
    "    third_price = np.dot(p,DT) + per * d1 + (1 - per) * d2\n",
    "    return third_price\n",
    "#  F(D, T)\n",
    "third_price = third_price(Des_net, exe_Time, len(Des_net), 10,1.9,0.08,0.001,0)\n",
    "Third_price = []\n",
    "for i in range(len(third_price)):\n",
    "    if third_price[i] < 0:\n",
    "        Third_price.append(1.1 - third_price[i])\n",
    "    else:\n",
    "        Third_price.append(third_price[i])\n",
    "TPrice = pd.Series(Third_price, name = '调价比例拟合分布')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 423,
   "metadata": {},
   "outputs": [],
   "source": [
    "#比较前后的分布\n",
    "sns.set_style(\"whitegrid\")\n",
    "fig, (ax1,ax2) = plt.subplots(nrows=1,ncols=2,figsize=(16,6)) \n",
    "sns.set(font='SimHei',font_scale=1)\n",
    "# sns.despine(left = 10000)\n",
    "ax1 = sns.kdeplot(TPrice/2.2, ax = ax1)\n",
    "ax2 = sns.kdeplot(train['调价比例'],ax = ax2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算附件2中的三次报价"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 486,
   "metadata": {},
   "outputs": [],
   "source": [
    "Des_net =  test['目的网点']\n",
    "exe_Time = test['执行时间']\n",
    "#三次报价计算（加入噪声） f(D,T)\n",
    "def third_price(D,T,length, mu1, mu2, sigma1, sigma2, per):\n",
    "    #单位向量\n",
    "    i_original = []\n",
    "    for i in range(0,length): i_original.append(1)\n",
    "    I = pd.Series(i_original)\n",
    "    d1 = np.random.normal(loc=mu1,scale=sigma1,size=length) # mean : loc std :  scale num : size\n",
    "    d2 = np.random.normal(loc = mu2, scale = sigma2, size = length) # deviations\n",
    "    p = [1.837,-0.2201,0.02774,0.007887,0.005703,-0.001838,-0.0001485,0,0]\n",
    "    DT = [I, D,T, D**2, D*T, T**2, T*D**2,D*T**2, T**3]\n",
    "    third_price = np.dot(p,DT) + per * d1 + (1 - per) * d2\n",
    "    return third_price\n",
    "#  F(D, T)\n",
    "third_price = third_price(Des_net, exe_Time, len(Des_net), 10,1.9,0.08,0.001,0)\n",
    "Third_price = []\n",
    "for i in range(len(third_price)):\n",
    "    if third_price[i] < 0:\n",
    "        Third_price.append((1.1 - third_price[i])/2.2)\n",
    "    else:\n",
    "        Third_price.append(third_price[i]/2.2)\n",
    "TPrice = pd.Series(Third_price, name = '调价比例拟合分布')\n",
    "\n",
    "TPrice_real = TPrice * Total_cost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 492,
   "metadata": {},
   "outputs": [],
   "source": [
    "TPrice_real.to_csv('Third_price.csv')\n",
    "SPrice_real = TPrice_real*0.8\n",
    "SPrice_real.to_csv('Second_price.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 第二次、三次报价规整"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 488,
   "metadata": {},
   "outputs": [],
   "source": [
    "Price_reg = pd.read_excel('data/附件3：计算结果new.xlsx', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 491,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(Price_reg['任务id'])):\n",
    "    if Price_reg['一次报价'][i] > Price_reg['二次报价'][i] and Price_reg['一次报价'][i] < Price_reg['三次报价'][i]:\n",
    "        Price_reg['二次报价'][i] = Price_reg['三次报价'][i]\n",
    "    if Price_reg['一次报价'][i] > Price_reg['二次报价'][i]:\n",
    "        Price_reg['二次报价'][i] = Price_reg['一次报价'][i]\n",
    "        Price_reg['三次报价'][i] = Price_reg['一次报价'][i]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 490,
   "metadata": {},
   "outputs": [],
   "source": [
    "Price_reg.to_csv('计算结果new.csv')"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}