dataanalysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "60\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import time\n",
    "print(pd.get_option('display.max_rows'))\n",
    "#显示所有行 默认为60   None\n",
    "pd.set_option('display.max_rows', 100)\n",
    "\n",
    "#显示所有列\n",
    "#pd.set_option('display.max_columns', None)\n",
    "#设置value的显示长度为100，默认为50\n",
    "pd.set_option('max_colwidth',200)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Todolist\n",
    "## 广告操作数据\n",
    "\n",
    "在清理时的潜在问题，广告状态未知的都是新建广告，而这些数据都删除了，是否会有影响？\n",
    "[目前位置](#训练模型了)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 清洗数据\n",
    "\n",
    "\n",
    "## totalExposureLog.out - 历史曝光日志数据\n",
    "此部分每一条代表一个曝光记录。由于初赛数据规模的限制，这里每个只提供了最终竞价胜出曝光的广告信息。同一条请求，可能会同时曝光多个广告位的广告，因此一条请求id可能对应多条记录。修改拆分思路为每天分成三个时间段，将历史日志拆分。统计曝光量的时候需要结合每天的三个文件进行统计或者统计时也做追加写的方式。\n",
    "\n",
    "为避免数据量过大，历史曝光数据选择了在用户维度（uv）上按 512 分之一进行均匀采样。各字段使用制表符(\\t)分隔，每列的具体含义如下：\n",
    "- 广告请求 id：唯一标识每次请求（每个请求对应一个用户某一时刻，可能多个广告位）\n",
    "- 广告请求时间：该字段为时间戳，即 1970 纪元后经过的浮点秒数\n",
    "- 广告位 id：加密后无业务含义，只区分不同广告位，每个广告位只能曝光特定素材尺寸的广告\n",
    "- 用户 id（即看广告的人）：加密后无业务含义，只区分不同用户，可和后面的用户特征数据中 id 相关联\n",
    "- 曝光广告 id：加密后无业务含义，只区分不同广告，可以和广告特征文件中的广告 id 关联\n",
    "- 曝光广告素材尺寸：枚举型取值，不同广告位对素材的尺寸要求不同，同一个广告位可能适配多个不同尺寸的素材\n",
    "- 曝光广告出价 bid：这里只记录 cpc 出价，非 cpc 广告此处记录折算后的 cpc 价格\n",
    "- 曝光广告 pctr：预估的 pctr，和 bid 相乘得到basic_ecpm\n",
    "- 曝光广告quality_ecpm：将广告质量和用户体验等因素折算成ecpm 的分数，主要影响因素有 pctr/pcvr/窄定向等\n",
    "- 曝光广告totalEcpm：广告排序的分数依据，由 basic_ecpm 和quality_ecpm相加得到\n",
    "\n",
    "起止时间20190216225954——20190319235844\n",
    "for i in range(102):\n",
    "    try:\n",
    "        exposureLogfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\map\\\\totalExposureLog'+str(i)+'.out ',sep='\\t',\n",
    "                              header=None,names=['请求id','请求时间','广告位id','用户id','曝光广告id'\n",
    "                                                            ,'素材尺寸','出价bid','pctr','quality_ecpm','totalEcpm'])\n",
    "        exposureLogdf = pd.DataFrame(exposureLogfile)\n",
    "        exposureLogdf['请求时间']=exposureLogdf['请求时间'].apply(lambda x:time.strftime(\"%Y%m%d%H%M%S\",time.localtime(x))) \n",
    "        print(\"#\"+str(i)+\"文件起止时间\"+exposureLogdf['请求时间'].min()+\"----\"+exposureLogdf['请求时间'].max())\n",
    "    except StopIteration:\n",
    "        break "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 第一个100万数据中有 15427个重复，剩余984573条\n",
    "exposureLogfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\map\\\\totalExposureLog1.out ',sep='\\t',\n",
    "                              header=None,names=['请求id','请求时间','广告位id','用户id','曝光广告id'\n",
    "                                                            ,'素材尺寸','出价bid','pctr','quality_ecpm','totalEcpm']\n",
    "                             ,nrows=1000000)\n",
    "exposureLogdf= pd.DataFrame(exposureLogfile)\n",
    "exposureLogdf['请求时间']=exposureLogdf['请求时间'].apply(lambda x:time.strftime(\"%Y%m%d%H%M%S\",time.localtime(x))) #localtime\n",
    "exposureLogdf.drop_duplicates(keep=False,inplace=True)\n",
    "### 第一个100万数据中有 15427个重复，剩余984573条\n",
    "#exposureLogdf.sort_values(by=[\"曝光广告id\",\"广告位id\",\"出价bid\"])\n",
    "### 请求id有935438个不同的取值，大部分一次请求对应一个广告，只有5万左右的曝光广告涉及到一个请求多个广告\n",
    "### 请求时间 有85284个不同的取值，也就是每个请求时间s内都有10多次不同的访问（并发访问）\n",
    "### 广告位id 251个 不同的取值\n",
    "### 用户id  282381 个不同的取值\n",
    "### 曝光广告id  38392个不同的取值，平均曝光不到3次\n",
    "### 素材尺寸 46 个不同的取值\n",
    "### 出价bid 5540 个不同的取值\n",
    "### pctr  96964个不同的取值\n",
    "### quality_ecpm 98005个不同的取值\n",
    "### totalEcpm 519283个不同的取值\n",
    "print(exposureLogdf.广告位id.unique().size)\n",
    "print(exposureLogdf.用户id.unique().size)\n",
    "print(exposureLogdf.曝光广告id.unique().size)\n",
    "print(exposureLogdf.素材尺寸.unique().size)\n",
    "print(exposureLogdf.出价bid.unique().size)\n",
    "print(exposureLogdf.pctr.unique().size)\n",
    "print(exposureLogdf.quality_ecpm.unique().size)\n",
    "print(exposureLogdf.totalEcpm.unique().size)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "exposureLogfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\totalExposureLog.out ',sep='\\t',\n",
    "                              header=None,names=['请求id','请求时间','广告位id','用户id','曝光广告id'\n",
    "                                                            ,'素材尺寸','出价bid','pctr','quality_ecpm','totalEcpm']\n",
    "                             ,iterator=True,low_memory =False)\n",
    "for i in range(300):\n",
    "    try:\n",
    "        exposureLogdf = exposureLogfile.get_chunk(1000000)\n",
    "        \n",
    "        #去重\n",
    "        exposureLogdf.drop_duplicates(keep='first',inplace=True)        \n",
    "        exposureLogdf['请求时间']=exposureLogdf['请求时间'].apply(lambda x:time.strftime(\"%Y%m%d%H%M%S\",time.localtime(x))) \n",
    "        exposureLogdf['请求时间1']=exposureLogdf['请求时间'].apply(lambda x:(str(x[:8])+'-'+str(int(x[8:10])//8))) \n",
    "        #追加模式，每天分成三个时段0-7 8-15 16-23\n",
    "        for name,group in exposureLogdf.groupby(by=['请求时间1']):\n",
    "            # 追加写模式\n",
    "            group.to_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\map\\\\totalExposureLog' + name+'.csv', \n",
    "                         sep='\\t', header=False, index=False\n",
    "                         ,columns=['请求id','请求时间','广告位id','用户id','曝光广告id' ,'素材尺寸','出价bid','pctr'\n",
    "                                   ,'quality_ecpm','totalEcpm']\n",
    "                         ,mode='a'\n",
    "                        )\n",
    "        print(\"第\"+str(i)+\"次读取完成！\")\n",
    "    except StopIteration:\n",
    "        break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## user_data -- 用户特征数据\n",
    "此部分包含抽样后的用户特征维度（包括上述曝光日志中全部用户），出于脱敏的考虑，这里把所有属性值（包括年龄）都做了随机映射，此部分无需过多关注id背后的业务含义。\n",
    "注：用户特征字段未知的均使用 0 表示。每列特征取值都用加密后的 id 表示，均为随机映射。不同列的 id 取值区间会重复。各字段使用制表符(\\t)分隔，每列的具体业务含义如下：\n",
    "- 用户 id：此处和上面曝光日志文件中的用户 id 关联\n",
    "- 年龄（Age）：每个取值随机映射为[1-N]的唯一 id\n",
    "- 性别(Gender)：男/女\n",
    "- 地域(area)：每个省/市用唯一 id 标识，可能多标签，使用逗号分隔不同 id\n",
    "- 婚恋状态（Status）：单身/已婚等状态，可能去多值，使用逗号分隔\n",
    "- 学历(Education)：博士/硕士/本科/高中/初中/小学\n",
    "- 消费能力（ConsuptionAbility）：高/低m\n",
    "- 设备（device）：IOS/Android,  不区分版本号\n",
    "- 工作状态（work）：在校大学生/商旅人士/政府公职人员/科研教育者/ IT 互联网工作者/医护工作者, 可能取多值，逗号分隔\n",
    "- 连接类型(ConnectionType)：无线/2G/3G/4G\n",
    "- 行为兴趣(behavior)：每个兴趣点一个 id，可多值，逗号分隔"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 用户特征数据 待清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1396718\n"
     ]
    }
   ],
   "source": [
    "userdatafile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\user_data ',sep='\\t',\n",
    "                              header=None,names=['用户id','年龄','性别','地域','婚恋状态'\n",
    "                                                            ,'学历','消费能力','设备','工作状态','连接类型','行为兴趣']\n",
    "                             ,iterator=True,low_memory =False)\n",
    "count=0\n",
    "for i in range(100000):\n",
    "    try:\n",
    "        userdatadf = userdatafile.get_chunk(10000)\n",
    "        count+=userdatadf.shape[0]\n",
    "    except StopIteration:\n",
    "        break\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户id</th>\n",
       "      <th>年龄</th>\n",
       "      <th>性别</th>\n",
       "      <th>学历</th>\n",
       "      <th>消费能力</th>\n",
       "      <th>设备</th>\n",
       "      <th>连接类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.000000e+04</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.00000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>6.970675e+05</td>\n",
       "      <td>523.898500</td>\n",
       "      <td>2.49780</td>\n",
       "      <td>4.759300</td>\n",
       "      <td>2.153600</td>\n",
       "      <td>2.187900</td>\n",
       "      <td>2.812400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>4.045921e+05</td>\n",
       "      <td>281.267209</td>\n",
       "      <td>0.56624</td>\n",
       "      <td>2.086384</td>\n",
       "      <td>0.832031</td>\n",
       "      <td>0.395739</td>\n",
       "      <td>1.022698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>3.310000e+02</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.410058e+05</td>\n",
       "      <td>333.000000</td>\n",
       "      <td>2.00000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>6.929085e+05</td>\n",
       "      <td>522.000000</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.050297e+06</td>\n",
       "      <td>787.000000</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.396536e+06</td>\n",
       "      <td>988.000000</td>\n",
       "      <td>3.00000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>5.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               用户id            年龄           性别            学历          消费能力  \\\n",
       "count  1.000000e+04  10000.000000  10000.00000  10000.000000  10000.000000   \n",
       "mean   6.970675e+05    523.898500      2.49780      4.759300      2.153600   \n",
       "std    4.045921e+05    281.267209      0.56624      2.086384      0.832031   \n",
       "min    3.310000e+02      0.000000      1.00000      1.000000      1.000000   \n",
       "25%    3.410058e+05    333.000000      2.00000      2.000000      1.000000   \n",
       "50%    6.929085e+05    522.000000      3.00000      5.000000      2.000000   \n",
       "75%    1.050297e+06    787.000000      3.00000      6.000000      3.000000   \n",
       "max    1.396536e+06    988.000000      3.00000      8.000000      3.000000   \n",
       "\n",
       "                 设备          连接类型  \n",
       "count  10000.000000  10000.000000  \n",
       "mean       2.187900      2.812400  \n",
       "std        0.395739      1.022698  \n",
       "min        0.000000      1.000000  \n",
       "25%        2.000000      2.000000  \n",
       "50%        2.000000      2.000000  \n",
       "75%        2.000000      4.000000  \n",
       "max        4.000000      5.000000  "
      ]
     },
     "execution_count": 266,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "userdatadf.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## test_sample.dat -- 测试数据集\n",
    "此部分包含需要预估广告的全部设置条件，包含新创建广告、已创建广告、修改过及未修改修改过的广告。预估目标是下一个自然日（0点到24点）的曝光量。历史数据为3月20日（不含）之前，因此对于 20日以后新建广告，其预估目标为预估创建次日，对于已存在广告预估目标为3月21日（周四）\n",
    "\n",
    "第 n+1 天的待预估广告以及对应的设置。各列由制表符(\\t)分隔， 含义说明如下：\n",
    "-  样本 id\n",
    "-  广告 id\n",
    "-  创建时间\n",
    "-  素材尺寸\n",
    "-  广告行业 id\n",
    "-  商品类型\n",
    "-  商品 id\n",
    "-  广告账户 id\n",
    "-  投放时段\n",
    "-  人群定向\n",
    "-  出价（单位分）\n",
    "\n",
    "以上各字段的格式均和训练数据中的广告数据格式一致。\n",
    "由于要评估出价相关性，故测试数据中一条广告 id 会对应多条不同出价的样本。为简化问题，测试数据也要求预估指定广告样本在下一个自然日的曝光量。（此处的曝光量是和训练数据一样 uv 采样后的结果）。为了更准确的评估，此处的测试广告已经剔除掉下一个自然日期间再次修改的广告（如暂停、修改出价、定向等），即认为在预估周期内该广告设置保持不变。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 测试数据   (20290, 11)\n",
    "test_samplefile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\test_sample.dat ',sep='\\t',\n",
    "                              nrows=30000,header=None,names=['样本id','广告id','创建时间','素材尺寸','广告行业id'\n",
    "                                                           ,'商品类型','商品id','广告账户id','投放时间','人群定向'\n",
    "                                                            ,'出价'])\n",
    "test_sampledf = pd.DataFrame(test_samplefile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 拆分投放时间，扩展字段  \n",
    "# 周一到周四，每天不投放的都有 10个  # 周六日 周六不投放的62个   周日不投放的145个\n",
    "test_sampledf=pd.merge(test_sampledf,test_sampledf['投放时间'].str.split(',',expand=True),right_index=True,left_index=True)\n",
    "\n",
    "# 20190321000000 对应时间戳为 1553097600   在此之前的样本有17809 个，需要判断周四是否在投放时间内，也就是第三个，之后是否是全0\n",
    "# 结论：周四不投放的有10个，这10个曝光应该为0  \n",
    "\n",
    "# 10个样本的id为 [ 6008,  6468,  7917,  9962, 10194, 12376, 14849, 15552, 16017,18827]\n",
    "(test_sampledf[(test_sampledf[3]=='0')&(test_sampledf['创建时间']<1553097600)]['样本id']).values\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对于3月21日0点之后新建的广告，预估曝光的投放时间为创建的时候的第二天，第二天不在投放时间的有20个左右，样本id为 \n",
    "\n",
    "[ 1103,  4826,  5548,  5591,  6079,  8175,  9845,  9876, 10238,\n",
    "       11280, 11429, 11590, 11758, 12649, 14507, 15044, 15984, 16640,\n",
    "       17850, 19113, 20289],"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#把时间列标准化时间格式 输出这一天是周中的第几天，Monday=0, Sunday=6\n",
    "test_sampledf['dayofweek']=(pd.to_datetime(test_sampledf['创建时间'],unit ='s')+pd.offsets.Hour(8)+pd.DateOffset(days=1)).dt.dayofweek\n",
    "# +(24+8)*3600  \n",
    "# 5, 6, 4, 0\n",
    "temp=test_sampledf[(test_sampledf['创建时间']>=1553097600)]\n",
    "# temp.dayofweek.unique()\n",
    "temp=temp[((temp[0]=='0') | (temp[1]=='0')| (temp[2]=='0')| (temp[3]=='0')| (temp[4]=='0')| (temp[5]=='0')| (temp[6]=='0'))]\n",
    "temp['out']=temp.apply(lambda x: ('0' if x[x['dayofweek']]=='0' else '1'),axis=1)\n",
    "temp[temp['out']=='0']['样本id'].values  #这20个也要设置曝光为0\n",
    "\n",
    "# 共涉及到三个广告id   77259  571523  667503"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [],
   "source": [
    "#test_sampledf['创建时间'].max() \n",
    "# 最小时间  1433409092 2015/6/4 17:11:32\n",
    "# 最大时间  1553413953  2019/3/24 15:52:33日\n",
    "\n",
    "# 取值种类统计   \n",
    "# 广告id：1954 素材尺寸：20（没有逗号分隔） 广告行业id:125   商品类型:10    \n",
    "# 商品id有：872个值   广告账户id：1104  投放时间:222  人群定向：476个  出价：490 (范围1-20000)\n",
    "# test_sampledf.出价.unique().max() #.size\n",
    "# gp=test_sampledf.groupby(by=['广告id','素材尺寸','广告行业id' ,'商品类型','商品id','广告账户id','投放时间','人群定向'])\n",
    "# 1954 个，证明了同一个广告的 '广告id','素材尺寸','广告行业id' ,'商品类型','商品id','广告账户id' ,'投放时间','人群定向' 不变\n",
    "# gp['广告id','素材尺寸','广告行业id' ,'商品类型','商品id','广告账户id','投放时间','人群定向'].count()\n",
    "\n",
    "# 清空\n",
    "#temp.drop(temp.index,inplace=True)\n",
    "#print(test_sampledf.shape)\n",
    "#print(test_sampledf.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ad_static_feature.out - 广告静态数据\n",
    "此部分除最后一列素材尺寸的缺失值使用空字符表示外，其他列的缺失值是用-1表示。关于逗号连接的多值数据，本文件中只有手册中提到的素材尺寸（即最后一列）是合法的，其他列出现的不合理数据需进行数据清洗。\n",
    "\n",
    "该类广告属性一般从广告创建后无法修改。所有 id 类数据均为加密后随机映射。各列用制表符分隔，含义如下：\n",
    "-  广告 id：和曝光日志中的广告 id 相关联\n",
    "-  创建时间：广告创建时的时间戳\n",
    "-  广告账户 id：广告所在账户的唯一标识，账户结构分为四级：账户——推广计划——广告——素材\n",
    "-  商品 id：广告推广目标的唯一标识，若推广目标是落地页，则该字段为空\n",
    "-  商品类型：广告推广目标的类型，枚举型\n",
    "-  广告行业 id：广告所属的行业类别标识\n",
    "-  素材尺寸：不同广告位对素材的尺寸要求不同，同一个广告可能有多个不同尺寸的素材，用逗号分隔"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分析广告静态数据  735911行数据 \n",
    "adstaticfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_static_feature.out ',sep='\\t',\n",
    "                              nrows=10000000,header=None,names=['广告id','创建时间','广告账号id','商品id','商品类型'\n",
    "                                                            ,'广告行业id','素材尺寸']\n",
    "                          ,low_memory=False)\n",
    "adstaticdf = pd.DataFrame(adstaticfile)\n",
    "#adstaticdf['创建时间']=adstaticdf['创建时间'].apply(lambda x:time.strftime(\"%Y%m%d%H%M%S\",time.gmtime(x))) #localtime\n",
    "#adstaticdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 无重复数据\n",
    "adstaticdf.drop_duplicates(keep='first',inplace=True)\n",
    "\n",
    "#广告行业id 包含,分隔，需要清洗  8191 rows  剩余727720条\n",
    "#adstaticdf['广告行业id'].str.contains(',')\n",
    "adstaticdf=adstaticdf[~adstaticdf['广告行业id'].str.contains(',')]\n",
    "#adstaticdf['广告行业id']=pd.to_numeric(adstaticdf['广告行业id'])\n",
    "adstaticdf['广告行业id']=adstaticdf['广告行业id'].astype('int64')\n",
    "\n",
    "#商品id 包含,分隔，需要清洗  3 rows  剩余 727717 条\n",
    "#adstaticdf[adstaticdf['商品id'].str.contains(',')]\n",
    "adstaticdf=adstaticdf[~adstaticdf['商品id'].str.contains(',')]\n",
    "adstaticdf['商品id']=pd.to_numeric(adstaticdf['商品id'])\n",
    "\n",
    "# 创建时间为0的有 9195 rows 剩余 718522   条  \n",
    "# 最新考虑不能删，作为历史数据考虑把\n",
    "#adstaticdf=adstaticdf[(adstaticdf['创建时间']!=0)]\n",
    "\n",
    "# 素材尺寸为NaN的有 220856  rows 太多了，别删了吧.\n",
    "# 官方说了删除，剩余497666万\n",
    "adstaticdf= adstaticdf[~(adstaticdf['素材尺寸'].isnull())]\n",
    "\n",
    "adstaticdf['创建时间']=adstaticdf['创建时间'].apply(lambda x:time.strftime(\"%Y%m%d%H%M%S\",time.localtime(x)))\n",
    "adstaticdf.sort_values(by=['广告id','创建时间'])\n",
    "adstaticdf.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_static_feature_p.csv\",sep=\"\\t\",\n",
    "                     index=False,encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ad_operation.dat - 广告的操作数据\n",
    "此部分第二列操作时间为0的视为缺失，对于创建操作，创建时间可以在ad_staic_feature.out得到。其他不合理的数据需要进行清洗。\n",
    "\n",
    "记录广告操作的流水数据，以及操作后的属性值。各列使用制表符分隔，含义如下：\n",
    "-  广告 id（同上）\n",
    "-  创建/修改时间: 即广告创建或者修改设置的时间\n",
    "-  操作类型：1-修改，2-新建\n",
    "-  修改字段：1-广告状态，2-出价，3-人群定向，4-广告时段设置\n",
    "-  操作后的字段值：\n",
    "-  广告状态取值：1- 正常，0-失效\n",
    "-  出价：整数（单位分）\n",
    "-  投放时段：字符串。\n",
    "\n",
    "包含 7 个 64 位无符号整型数字（逗号分隔），每个整数分别代表周一到周日的投放时段。该整数转为 2 进制后从低到高 48 位\n",
    "bit 代表全天各时段（半小时为一时间窗口）是否投放，1-投放，0-不投。举例说明，17179865088= 1111111111111111111111000000000000，代表投放时段为 6：00-17：00；281474976710655=111111111111111111111111111111111111111111111111，代表全天投放。\n",
    "\n",
    "- 人群定向：字符串。格式如下：\n",
    "\n",
    "feature_name1:feature_value1,feature_value2|feature_name2:feature_value3,feature_value4|… \n",
    "此处 feature_name 取值同用户属性文件中的各列属性名，feature_value 取值 id 同用户属性文件中的定义，不同feature 用“|”分隔，不同 feature 取值用逗号分隔。\n",
    "广告通过人群定向的设置来召回对应的用户请求，对应的人群规则：不同 feature_name 是求 交 集 ， 同 一 featurename 下 不 同 的 value 求并集 ， 未 定 义 的feature_name 则表示 该维度 不 限 。 举 例 如：定向 设 置 为age:51,62,73,84|gender:1|area:1,3,5 ; 则表示该广告能被“（年龄 id 为51 或 62 或 73 或 84） 且 （性别取值为 1） 且 （地域取值为 1 或 3或 5）”的用户召回（即在这些用户上有曝光机会）。\n",
    "\n",
    "### 读取\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 分析广告操作数据  760866行数据 其中647381为修改，其余为新建，新建的时间为0\n",
    "adoperationfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_operation.dat ',sep='\\t',\n",
    "                             nrows=1000000,header=None,names=['广告id','创建时间','操作类型','修改字段','操作后的状态值']\n",
    "                            ,dtype = {'创建时间' : str})\n",
    "adoperationdf = pd.DataFrame(adoperationfile)\n",
    "#adoperationdf['创建时间']=adoperationdf['创建时间'].astype(str)\n",
    "#adoperationdf.loc[adoperationfile['创建时间']!=0]  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 543,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adoperationdf[adoperationdf['操作类型']==2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除重复值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 把重复的值删除 326 rows/2\n",
    "adoperationdf.drop_duplicates(keep='first',inplace=True)\n",
    "#print(adoperationdf[adoperationdf.duplicated(keep='first')])\n",
    "\n",
    "# 清理创建时间为0、操作类型为修改的 1522 row记录   剩余759181  rows\n",
    "adoperationdf=adoperationdf[~((adoperationdf['创建时间']=='0')&(adoperationdf['操作类型']==1))]\n",
    "\n",
    "# 删除不合法日期 20190230000000  1292 rows  剩余 757889 条\n",
    "adoperationdf=adoperationdf[(adoperationdf['创建时间']!=\"20190230000000\")]\n",
    "\n",
    "# 同一广告、同一创建时间、同一操作类型，同一修改字段修改为不同的值 1202行重复数据,剩余756687 条\n",
    "# 发现这类数据都是密集交替修改状态，之前和之后还有秒级差别的修改记录，重复的删除\n",
    "adoperationdf.drop_duplicates(subset=['广告id','创建时间','操作类型','修改字段'],keep=False,inplace=True)\n",
    "#print(adoperationdf[adoperationdf.duplicated(subset=['广告id','创建时间','操作类型','修改字段'],keep=False)])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 把相关字段修改值转成列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [],
   "source": [
    "#把相关字段修改值转成列\n",
    "adoperationdf['index11']=adoperationdf.apply(lambda x: str(x['广告id'])+'-'+str(x['创建时间'])+'-'+str(x['操作类型']),axis=1)\n",
    "#print(adoperationdf.index11.unique().size)  # 680131个  \n",
    "# 剩余680131个，还需要把这两个再关联起来\n",
    "#adoperationdf.pivot(index='index11', columns='修改字段', values='操作后的状态值')\n",
    "temp=adoperationdf.pivot(index='index11', columns='修改字段', values='操作后的状态值')\n",
    "temp.rename(index=str, columns={1: \"广告状态\", 2: \"出价\",3:\"人群定向\",4:\"投放时间\"},inplace=True) \n",
    "\n",
    "#已经行转列了，可以清理重复数据\n",
    "adoperationdf.drop_duplicates(subset=['广告id','创建时间','操作类型'],keep='last',inplace=True)\n",
    "# 将行转列的数据与原数据合并\n",
    "adoperationdf=pd.merge(adoperationdf,temp,how='left',left_on='index11',right_on='index11')\n",
    "# 清理无用字段\n",
    "adoperationdf.drop(['修改字段','操作后的状态值','index11'],axis=1,inplace=True)\n",
    "\n",
    "adoperationdf.sort_values(['广告id','创建时间'],inplace=True)\n",
    "\n",
    "adoperationdf.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_operation_p.csv\",sep=\"\\t\",\n",
    "                     index=False,encoding=\"utf-8\")\n",
    "#20190216000000\n",
    "#adoperationdf[adoperationdf['创建时间']!=\"0\"]['创建时间'].min()\n",
    "# 20190319235959\n",
    "#adoperationdf['创建时间'].max()\n",
    "temp.drop(temp.index,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 填充缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 413,
   "metadata": {},
   "outputs": [],
   "source": [
    "#adoperationfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_operation_p.csv ',sep='\\t',nrows=1000000,dtype = {'创建时间' : str})\n",
    "#adoperationdf = pd.DataFrame(adoperationfile)\n",
    "# 填充缺失值？？ 有错误，必须按照分组填充，不能跨广告id填充\n",
    "#adoperationdf.fillna(axis=0,method='ffill',inplace=True)\n",
    "# 有问题\n",
    "#for name,group in adoperationdf.groupby(by=['广告id']):\n",
    "#    group.fillna(axis=0,method='ffill',inplace=True)\n",
    "\n",
    "# 按分组划分，笨方法进行添加\n",
    "temp=adoperationdf.groupby(by=['广告id'])\n",
    "tempfill=temp.fillna(axis=0,method='ffill')\n",
    "\n",
    "#tempfill 列顺序乱了，增加一列广告id\n",
    "tempfill['广告id']=adoperationdf['广告id']\n",
    "order = ['广告id', '创建时间',\"操作类型\",\"广告状态\",\"出价\",\"人群定向\",\"投放时间\"]\n",
    "adoperationdf = tempfill[order]\n",
    "adoperationdf.sort_values(by=[\"广告id\",\"创建时间\"],inplace=True)\n",
    "\n",
    "adoperationdf.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_operation_p.csv\",sep=\"\\t\",\n",
    "                     index=False,encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除广告状态无效行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {},
   "outputs": [],
   "source": [
    "#62637  行为空的数据  广告状态为nan的   剩余617494数据\n",
    "#adoperationdf[adoperationdf['广告状态'].isnull()]\n",
    "adoperationdf=adoperationdf[~adoperationdf['广告状态'].isnull()]\n",
    "adoperationdf.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_operation_p1.csv\",sep=\"\\t\",\n",
    "                     index=False,encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 清理频繁修改记录\n",
    "只保留一天的最后一条修改记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按小时为单位统计出 235538条频繁修改记录\n",
    "#adoperationdf['创建时间h']=adoperationdf['创建时间'].apply(lambda x: x[:10])\n",
    "\n",
    "# 训练集需要以天为预估单位 523645 条每天多余两次的修改操作，保留当天最后一次修改记录\n",
    "#在预估第二天时还要看第二天是否有修改记录，第二天有修改记录的需要剔除\n",
    "adoperationdf['创建时间d']=adoperationdf['创建时间'].apply(lambda x: x[:8])\n",
    "#adoperationdf[adoperationdf.duplicated(subset=['广告id','创建时间d','操作类型'],keep=False)]\n",
    "adoperationdf.drop_duplicates(subset=['广告id','创建时间d','操作类型'],keep='last',inplace=True)\n",
    "# 剩余270221 条记录\n",
    "# 清理无用字段 ,'创建时间h'\n",
    "adoperationdf.drop(['创建时间d'],axis=1,inplace=True)\n",
    "adoperationdf.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_operation_p2.csv\",sep=\"\\t\",\n",
    "                     index=False,encoding=\"utf-8\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 查看空值情况\n",
    "# print(adoperationdf.dtypes)\n",
    "# adoperationdf[adoperationdf['广告id']<1]\n",
    "# 查看操作类型是否合法数据  \n",
    "# adoperationdf[~adoperationdf['操作类型'].isin([1, 2])]\n",
    "# adoperationdf[~adoperationdf['修改字段'].isin([1,2,3,4])]\n",
    "# 创建时间是否异常\n",
    "# adoperationdf[adoperationdf['创建时间']==\"20190230000000\"]\n",
    "# adoperationdf[(adoperationdf['创建时间'].str.len()!=1)&(adoperationdf['创建时间'].str.len()!=14)]\n",
    "#创建时间为0的可以在ad_staic_feature.out得到\n",
    "# pd.to_datetime(adoperationdf['创建时间'])\n",
    "#adoperationdf['创建时间1']=pd.to_datetime(adoperationdf['创建时间'],errors=\"ignore\")\n",
    "#adoperationdf[(adoperationdf['创建时间1']==0)&(adoperationdf['创建时间']!=0)]\n",
    "#adoperationdf.drop(['创建时间1'],axis=1,inplace=True)\n",
    "\n",
    "\n",
    "# adoperationdf[adoperationdf['操作后的状态值'].isnull()]\n",
    "# adoperationdf[(adoperationdf['修改字段']==1)&(adoperationdf['操作后的状态值']!=\"0\")&(adoperationdf['操作后的状态值']!=\"1\")]\n",
    "# pd.to_numeric(adoperationdf[(adoperationdf['修改字段']==2)]['操作后的状态值'])\n",
    "#adoperationdf[adoperationdf['操作后的状态值'].str.count(\"\\|\")>0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 构造训练集\n",
    "\n",
    "## 思路\n",
    "首先清洗收据，按照官方给的标准将无效数据剔除掉，上一步已经完成了。\n",
    "根据操作记录表，内连接广告静态数据，只有静态数据没有操作记录的广告舍弃无法用于训练，没有参考特征用于训练。上一步已完成\n",
    "\n",
    "需要把广告操作记录中的最终值聚合到各字段投放时间、人群定向、出价、状态开时间、状态关闭时间、下次变动时间 小于该行createtime的数据中最大的createtime，修改自动类型相同、广告id一致，变动后作为一个新广告进行统计。上一步对该思路进行了调整已完成。\n",
    "\n",
    "## 训练集字段\n",
    "['样本id','广告id','创建时间','素材尺寸','广告行业id','商品类型','商品id','广告账户id','投放时间','人群定向','出价']\n",
    "从20190217到20190319日曝光数量作为label，按天进行训练\n",
    "\n",
    "### 出价\n",
    "特征\n",
    "### 素材尺寸\n",
    " 按照题目说明会有多个，多个的维度如何考虑？多个特征？拆分开，没有的算作0？  该特征对pctr有较大影响。\n",
    "\n",
    "经过计算未发现多值，取值有\n",
    "['40', nan, '30', '34', '1', '64', '14', '20', '41', '54', '48', '55', '33', '12', '32', '37',\n",
    "'25', '63', '66', '4', '16', '11', '56', '44', '31']\n",
    "### 广告账户id、商品id、商品类型、广告行业id\n",
    "作为特征用于训练，可能和广告质量有关，用曝光数据中pctr、ecpm进行训练测试？\n",
    "\n",
    "\n",
    "## 对广告静态数据和操作数据进行整合\n",
    "生成和测试集一样的数据集\n",
    "### 读取清洗过的操作数据和静态数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [],
   "source": [
    "# temp.drop(temp.index,inplace=True)\n",
    "# 读取清洗后的广告操作和静态数据    270221 行操作数据，502153行静态数据\n",
    "adoperationfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_operation_p2.csv ',sep='\\t',\n",
    "                              nrows=1000000,dtype = {'创建时间' : str}\n",
    "                              ,low_memory=False)\n",
    "adoperationdf = pd.DataFrame(adoperationfile) #270221 行数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [],
   "source": [
    "adstaticfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_static_feature_p.csv ',sep='\\t',\n",
    "                              nrows=10000000,dtype = {'创建时间': str},low_memory=False)\n",
    "adstaticdf = pd.DataFrame(adstaticfile)  #502153 行数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 合并静态数据和操作数据，以操作数据为主表\n",
    "清理多余字段，调整字段顺序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 合并静态数据和操作数据，以操作数据为主表，左连接 (680131 , 12)\n",
    "mergedf=pd.merge(adoperationdf, adstaticdf, how='left', left_on='广告id', right_on='广告id')\n",
    "# 对于没有创建时间的操作数据取 静态数据中的时间，之后还没有时间的默认为很久以前\n",
    "mergedf['创建时间']=mergedf.apply(lambda x: (x['创建时间_y'] if x['创建时间_x']=='0' else x['创建时间_x']),axis=1)\n",
    "# 删除多余字段\n",
    "mergedf.drop(['创建时间_x', '创建时间_y'], axis=1,inplace=True)\n",
    "#调整列顺序  ['样本id','广告id','创建时间','素材尺寸','广告行业id','商品类型','商品id','广告账户id','投放时间','人群定向','出价']\n",
    "order = ['广告id', '创建时间','素材尺寸','广告行业id', '商品类型', '商品id', '广告账号id', '出价','人群定向','投放时间', '操作类型', '广告状态']\n",
    "mergedf = mergedf[order]\n",
    "mergedf.sort_values(by=[\"广告id\",\"创建时间\"],inplace=True)\n",
    "\n",
    "#先写入文件以免丢失数据\n",
    "mergedf.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\adoperationleftjoinadstatic.csv\",sep=\"\\t\",\n",
    "                     index=False,encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 清理素材尺寸为空的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 其中 145284 素材尺寸未知 \n",
    "#mergedf[mergedf.素材尺寸.isnull()==True]\n",
    "#mergedf.素材尺寸.unique()\n",
    "\n",
    "# 对合并后的数据进行再次清理，缺失广告静态数据会影响pctr等计算  剩余 218265  \n",
    "mergedf.dropna(subset=['素材尺寸'],inplace=True) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 按日生成训练样本数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "#获取某日可供训练的样本\n",
    "# 需要依次生成 0216到0318的每日可供训练样本（adoperationdf的子集），用于预测次日的曝光数据（0217-0319）\n",
    "#20190216225954——20190319235844\n",
    "\n",
    "predatelist=['20190217000000','20190218000000','20190219000000','20190220000000','20190221000000','20190222000000',\n",
    "             '20190223000000','20190224000000','20190225000000','20190226000000','20190227000000','20190228000000',\n",
    "             '20190301000000','20190302000000','20190303000000','20190304000000','20190305000000','20190306000000',\n",
    "             '20190307000000','20190308000000','20190309000000','20190310000000','20190311000000','20190312000000',\n",
    "             '20190313000000','20190314000000','20190315000000','20190316000000','20190317000000','20190318000000',\n",
    "             '20190319000000','20190320000000']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 194,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in range(len(predatelist)-1):\n",
    "    #预测0217日曝光为例，需要找出在0217零点前已经创建的广告\n",
    "    #adoperationdf[((adoperationdf['创建时间']<'20190217000000')&(adoperationdf['广告状态']=='1'))]\n",
    "    # 如果有多条记录的话，只获取最近的广告设置  按照广告id分组获取最近的一条\n",
    "    temp=mergedf[((mergedf['创建时间']<predatelist[i])\n",
    "                        &(mergedf['广告状态']==1))].groupby(by=['广告id']).tail(1)\n",
    "\n",
    "    # 要求该广告在第二天没有修改记录，否则影响训练\n",
    "    nextdayadlist=mergedf[((mergedf['创建时间']>=predatelist[i]) &\n",
    "                                 (mergedf['创建时间']<predatelist[i+1]) )].广告id.unique()\n",
    "\n",
    "    # 删除第二天有改动广告，不作为训练样本\n",
    "    temp=temp[((~temp['广告id'].isin(nextdayadlist) ))] \n",
    "    # 样本不为空则写入文件中作为训练样本\n",
    "    if not (temp.empty):\n",
    "        # 清理无用字段\n",
    "        temp.drop(['操作类型','广告状态'],axis=1,inplace=True)\n",
    "        temp.reset_index(inplace=True,drop=True)\n",
    "        \n",
    "        #字段顺序\n",
    "        order = ['广告id', '创建时间','素材尺寸','广告行业id','商品类型','商品id','广告账号id','投放时间','人群定向','出价']\n",
    "        temp=temp[order]\n",
    "        \n",
    "        #字段类型 广告行业id 商品类型 商品id 广告账号id是字符串，不带小数点 \n",
    "        temp['广告账号id']=temp['广告账号id'].astype('int64')\n",
    "        temp['商品类型']=temp['商品类型'].astype('int64')\n",
    "        temp['商品id']=temp['商品id'].astype('int64')\n",
    "        temp['广告行业id']=temp['广告行业id'].astype('int64')\n",
    "        temp.to_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\test_sample_' + predatelist[i]+'.csv',\n",
    "                    sep='\\t', header=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 曝光次数 label统计\n",
    "统计在曝光记录中该广告id对应时间范围内的曝光次数，按照天统计每日曝光量。\n",
    "（ 该次数需要按照广告的有效投放时间按照7*24小时换算成天为单位）？这个思路可能不对"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 确认需要将历史曝光日志数据按照时间统计每日曝光次数，用于训练时预测每个广告第二天的曝光量\n",
    "# 对拆分后的曝光数据进行分析\n",
    "# 读取按条数分隔的原始数据\n",
    "# exposureLogfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\map\\\\totalExposureLog2.out ',sep='\\t',\n",
    "#                               header=None,names=['请求id','请求时间','广告位id','用户id','曝光广告id','素材尺寸'\n",
    "#                                                  ,'出价bid','pctr','quality_ecpm','totalEcpm'])\n",
    "# exposureLogdf = pd.DataFrame(exposureLogfile)\n",
    "# exposureLogdf['请求时间']=exposureLogdf['请求时间'].apply(lambda x:time.strftime(\"%Y%m%d%H%M%S\",time.localtime(x))) \n",
    "\n",
    "# 按照时间和广告id排序，统计每个广告的次数\n",
    "#exposureLogdf.sort_values(by=[\"广告位id\",\"曝光广告id\",\"出价bid\"])\n",
    "#exposureLogdf.sort_values(by=[\"请求时间\",\"曝光广告id\"],inplace=True)\n",
    "# gp=exposureLogdf.groupby(by=['曝光广告id','请求时间']) # '素材尺寸',\n",
    "# newdata=gp['曝光广告id','请求时间'].count()\n",
    "\n",
    "# newdata.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\exposureByDay.csv\",sep=\"\\t\",index=False,encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 对曝光数据进行按天汇总"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 677,
   "metadata": {},
   "outputs": [],
   "source": [
    "#temp  #1022807 171\n",
    "#print(exposurestatistics.shape)\n",
    "#temp=temp.reset_index().groupby(by=['曝光广告id','出价'])['请求id'].sum().reset_index()\n",
    "#temp.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\exposureByDay000000.csv\",sep=\"\\t\",header=False,encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 195,
   "metadata": {},
   "outputs": [],
   "source": [
    "#对拆分后的日志文件进行遍历，统计每日广告曝光数据\n",
    "filenameprefix='totalExposureLog'\n",
    "datelist=['20190217','20190218','20190219','20190220','20190221','20190222',\n",
    "             '20190223','20190224','20190225','20190226','20190227','20190228',\n",
    "             '20190301','20190302','20190303','20190304','20190305','20190306',\n",
    "             '20190307','20190308','20190309','20190310','20190311','20190312',\n",
    "             '20190313','20190314','20190315','20190316','20190317','20190318',\n",
    "             '20190319']\n",
    "for datestr in datelist:\n",
    "    filename=filenameprefix+datestr+'-'  \n",
    "    temp.drop(temp.index,inplace=True)\n",
    "    for i in range(3):\n",
    "        exposureLogfile =pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\map\\\\' + filename+str(i)+'.csv',sep='\\t',\n",
    "                                      header=None,names=['请求id','请求时间','广告位id','用户id','曝光广告id' ,'素材尺寸','出价','pctr'\n",
    "                                           ,'quality_ecpm','totalEcpm'])\n",
    "\n",
    "        exposureLogdf=pd.DataFrame(exposureLogfile)\n",
    "        # 曝光广告id和素材尺寸好像是1对1的关系\n",
    "        # 一个广告针对不同的用户有不同的pctr、quality_ecpm 等\n",
    "        # 同一个广告一天多次不同报价的怎么处理？  应该没有影响，毕竟训练样本中已经排除掉这类广告，或者这里增加一个出价统计\n",
    "        exposurestatistics=exposureLogdf.groupby(by=['曝光广告id'])['请求id'].count().reset_index()\n",
    "        #print(exposurestatistics.shape)\n",
    "        if temp.empty:\n",
    "            temp=exposurestatistics\n",
    "        else:\n",
    "            temp=pd.concat([temp,exposurestatistics])\n",
    "    temp=temp.reset_index().groupby(by=['曝光广告id'])['请求id'].sum().reset_index()\n",
    "    temp.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\exposureByDay\"+datestr+\".csv\",sep=\"\\t\",header=False,index=False,encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>曝光广告id</th>\n",
       "      <th>请求id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [曝光广告id, 请求id]\n",
       "Index: []"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 结合训练样本来分析对应label\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp.drop(temp.index,inplace=True) \n",
    "#(exposureLogdf.groupby(by=['曝光广告id','出价','quality_ecpm'])['请求id'].count())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "ok\n"
     ]
    }
   ],
   "source": [
    "# 某日0点之前的广告样本，用于计算某日的广告曝光数据 ,文件名集合\n",
    "predatelist=['20190218000000','20190219000000','20190220000000','20190221000000','20190222000000',\n",
    "             '20190223000000','20190224000000','20190225000000','20190226000000','20190227000000','20190228000000',\n",
    "             '20190301000000','20190302000000','20190303000000','20190304000000','20190305000000','20190306000000',\n",
    "             '20190307000000','20190308000000','20190309000000','20190310000000','20190311000000','20190312000000',\n",
    "             '20190313000000','20190314000000','20190315000000','20190316000000','20190317000000','20190318000000',\n",
    "             '20190319000000']\n",
    "\n",
    "# 曝光统计数据 文件名集合\n",
    "datelist=['20190218','20190219','20190220','20190221','20190222',\n",
    "             '20190223','20190224','20190225','20190226','20190227','20190228',\n",
    "             '20190301','20190302','20190303','20190304','20190305','20190306',\n",
    "             '20190307','20190308','20190309','20190310','20190311','20190312',\n",
    "             '20190313','20190314','20190315','20190316','20190317','20190318',\n",
    "             '20190319']\n",
    "temp1=pd.DataFrame()\n",
    "for i in range(len(predatelist)):\n",
    "    print(i)\n",
    "    samplefilename='test_sample_'+predatelist[i]+'.csv'\n",
    "    test_samplefile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\'+samplefilename,sep='\\t',\n",
    "                                  header=None,names=['样本id','广告id', '创建时间','素材尺寸','广告行业id','商品类型',\n",
    "                                                     '商品id','广告账号id','投放时间','人群定向','出价'])\n",
    "    test_sampledf = pd.DataFrame(test_samplefile)\n",
    "\n",
    "    exposureLogfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\exposureByDay'+datelist[i]+'.csv',sep='\\t',\n",
    "                                  header=None,names=['广告id','曝光次数'])\n",
    "    exposureLogdf = pd.DataFrame(exposureLogfile)\n",
    "    # 将出价合并统计，否则会出现一对多的情况，导致样本变化。已经合并过了\n",
    "    #exposureLogdf=exposureLogdf.groupby(by=['广告id'])['曝光次数'].sum().reset_index()\n",
    "    temp=pd.merge(test_sampledf,exposureLogdf,how='left',left_on='广告id',right_on='广告id')\n",
    "    \n",
    "    # 处理空值\n",
    "    temp['曝光日期']=datelist[i]\n",
    "    #temp['出价_y']=temp.apply(lambda x: ( x['出价_x'] if pd.isnull(x['出价_y']) else x['出价_y']),axis=1)\n",
    "    temp.fillna(0,inplace=True)\n",
    "    if temp1.empty:\n",
    "        temp1=temp           \n",
    "    else:\n",
    "        temp1=pd.concat([temp1,temp])\n",
    "    #temp.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\sample\\\\sample\"+datelist[i]+\".csv\",sep=\"\\t\",header=False,index=False,encoding=\"utf-8\")\n",
    "print('ok')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp1.groupby(by=['样本id','广告id', '创建时间','素材尺寸','广告行业id','商品类型',\n",
    "                  '商品id','广告账号id','投放时间','人群定向','出价'\n",
    "                  ,'曝光日期'])['曝光次数'].sum().reset_index().sort_values(['曝光日期'\n",
    "                                                                     ,'广告id']).to_csv(\n",
    "    \"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\sample\\\\sample_total.csv\",sep=\"\\t\",index=False,encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [],
   "source": [
    "#temp2=temp1.groupby(by=['广告id'])['曝光次数'].sum().reset_index()\n",
    "#emptyadids=temp2[temp2['曝光次数']==0]['广告id'].unique()\n",
    "#temp2=temp1[~ temp1['广告id'].isin(emptyadids)]\n",
    "\n",
    "temp2=temp1[temp1['曝光次数']==0].groupby(by=['广告id'])['曝光次数'].count().reset_index()\n",
    "emptyadids=temp2[temp2['曝光次数']>7]['广告id'].unique()\n",
    "temp2=temp1[~ (temp1['广告id'].isin(emptyadids)&(temp1['曝光次数']==0) )]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>样本id</th>\n",
       "      <th>广告id</th>\n",
       "      <th>创建时间</th>\n",
       "      <th>素材尺寸</th>\n",
       "      <th>广告行业id</th>\n",
       "      <th>商品类型</th>\n",
       "      <th>商品id</th>\n",
       "      <th>广告账号id</th>\n",
       "      <th>出价</th>\n",
       "      <th>曝光次数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>4.725700e+04</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "      <td>47257.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3885.102017</td>\n",
       "      <td>360956.386715</td>\n",
       "      <td>2.019029e+13</td>\n",
       "      <td>28.599192</td>\n",
       "      <td>113.795438</td>\n",
       "      <td>8.913833</td>\n",
       "      <td>10744.965994</td>\n",
       "      <td>15176.620141</td>\n",
       "      <td>123.073026</td>\n",
       "      <td>31.065218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>3034.088904</td>\n",
       "      <td>212057.711681</td>\n",
       "      <td>3.870029e+07</td>\n",
       "      <td>19.049003</td>\n",
       "      <td>67.500158</td>\n",
       "      <td>5.778198</td>\n",
       "      <td>10938.830615</td>\n",
       "      <td>8647.309160</td>\n",
       "      <td>266.181199</td>\n",
       "      <td>247.783678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>332.000000</td>\n",
       "      <td>2.019022e+13</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>16.000000</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1310.000000</td>\n",
       "      <td>176466.000000</td>\n",
       "      <td>2.019023e+13</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>47.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>7749.000000</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3101.000000</td>\n",
       "      <td>357244.000000</td>\n",
       "      <td>2.019031e+13</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>122.000000</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>7610.000000</td>\n",
       "      <td>14999.000000</td>\n",
       "      <td>88.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>6010.000000</td>\n",
       "      <td>542297.000000</td>\n",
       "      <td>2.019031e+13</td>\n",
       "      <td>40.000000</td>\n",
       "      <td>156.000000</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>20523.000000</td>\n",
       "      <td>22673.000000</td>\n",
       "      <td>136.000000</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>11516.000000</td>\n",
       "      <td>736023.000000</td>\n",
       "      <td>2.019032e+13</td>\n",
       "      <td>66.000000</td>\n",
       "      <td>251.000000</td>\n",
       "      <td>18.000000</td>\n",
       "      <td>32911.000000</td>\n",
       "      <td>29742.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>18923.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               样本id           广告id          创建时间          素材尺寸        广告行业id  \\\n",
       "count  47257.000000   47257.000000  4.725700e+04  47257.000000  47257.000000   \n",
       "mean    3885.102017  360956.386715  2.019029e+13     28.599192    113.795438   \n",
       "std     3034.088904  212057.711681  3.870029e+07     19.049003     67.500158   \n",
       "min        0.000000     332.000000  2.019022e+13      1.000000     -1.000000   \n",
       "25%     1310.000000  176466.000000  2.019023e+13     12.000000     47.000000   \n",
       "50%     3101.000000  357244.000000  2.019031e+13     30.000000    122.000000   \n",
       "75%     6010.000000  542297.000000  2.019031e+13     40.000000    156.000000   \n",
       "max    11516.000000  736023.000000  2.019032e+13     66.000000    251.000000   \n",
       "\n",
       "               商品类型          商品id        广告账号id            出价          曝光次数  \n",
       "count  47257.000000  47257.000000  47257.000000  47257.000000  47257.000000  \n",
       "mean       8.913833  10744.965994  15176.620141    123.073026     31.065218  \n",
       "std        5.778198  10938.830615   8647.309160    266.181199    247.783678  \n",
       "min        1.000000     -1.000000     16.000000     10.000000      0.000000  \n",
       "25%        1.000000     -1.000000   7749.000000     50.000000      0.000000  \n",
       "50%       13.000000   7610.000000  14999.000000     88.000000      1.000000  \n",
       "75%       13.000000  20523.000000  22673.000000    136.000000      9.000000  \n",
       "max       18.000000  32911.000000  29742.000000  10000.000000  18923.000000  "
      ]
     },
     "execution_count": 260,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp2.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将30天汇总后曝光次数仍为0的数据删掉\n",
    "temp2.to_csv(\"E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\sample\\\\sample_total_no0.csv\",sep=\"\\t\",index=False,encoding=\"utf-8\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 训练模型了\n",
    "## 先不考虑投放时间、投放人群的影响\n",
    "## 生成对应表格\n",
    "## 按照曝光次数取平均值，然后提交看结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "temp1.groupby(['广告id'])['曝光次数'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "#temp['出价_y']=\n",
    "temp1.sort_values(['广告id','出价_x','出价_y'])\n",
    "temp2=temp1.drop_duplicates(keep='first',subset=['广告id','出价_y','曝光次数'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "d:\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>广告id</th>\n",
       "      <th>出价_y</th>\n",
       "      <th>曝光次数</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>415</td>\n",
       "      <td>300.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>415</td>\n",
       "      <td>300.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>415</td>\n",
       "      <td>300.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>415</td>\n",
       "      <td>300.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>415</td>\n",
       "      <td>300.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>415</td>\n",
       "      <td>600.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>415</td>\n",
       "      <td>600.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>415</td>\n",
       "      <td>600.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>415</td>\n",
       "      <td>600.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>415</td>\n",
       "      <td>600.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>415</td>\n",
       "      <td>600.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>562</td>\n",
       "      <td>31.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>562</td>\n",
       "      <td>200.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>647</td>\n",
       "      <td>31.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>853</td>\n",
       "      <td>127.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>853</td>\n",
       "      <td>128.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>853</td>\n",
       "      <td>150.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>890</td>\n",
       "      <td>145.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>979</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>979</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1114</td>\n",
       "      <td>350.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1158</td>\n",
       "      <td>100.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1158</td>\n",
       "      <td>100.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1216</td>\n",
       "      <td>31.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1216</td>\n",
       "      <td>31.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1216</td>\n",
       "      <td>31.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1265</td>\n",
       "      <td>31.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1280</td>\n",
       "      <td>31.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1280</td>\n",
       "      <td>31.0</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1280</td>\n",
       "      <td>31.0</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1280</td>\n",
       "      <td>31.0</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1280</td>\n",
       "      <td>31.0</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1280</td>\n",
       "      <td>31.0</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1280</td>\n",
       "      <td>31.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1280</td>\n",
       "      <td>31.0</td>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1383</td>\n",
       "      <td>34.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1491</td>\n",
       "      <td>93.0</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1491</td>\n",
       "      <td>93.0</td>\n",
       "      <td>51.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1491</td>\n",
       "      <td>93.0</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1491</td>\n",
       "      <td>93.0</td>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1747</td>\n",
       "      <td>42.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1747</td>\n",
       "      <td>45.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1747</td>\n",
       "      <td>52.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1747</td>\n",
       "      <td>313.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1943</td>\n",
       "      <td>180.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2171</td>\n",
       "      <td>150.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2171</td>\n",
       "      <td>150.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2171</td>\n",
       "      <td>150.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2317</td>\n",
       "      <td>186.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2317</td>\n",
       "      <td>200.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2972</th>\n",
       "      <td>735763</td>\n",
       "      <td>18.0</td>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3412</th>\n",
       "      <td>735763</td>\n",
       "      <td>18.0</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2889</th>\n",
       "      <td>735763</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4368</th>\n",
       "      <td>735763</td>\n",
       "      <td>18.0</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5040</th>\n",
       "      <td>735763</td>\n",
       "      <td>18.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4888</th>\n",
       "      <td>735763</td>\n",
       "      <td>18.0</td>\n",
       "      <td>21.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1568</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>225.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1780</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>202.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5655</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>468.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6777</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>362.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6619</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>263.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8187</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>317.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8174</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>475.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7909</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>351.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8417</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8888</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>125.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9306</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>160.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9302</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>169.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11950</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>192.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11918</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>327.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11745</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>265.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11933</th>\n",
       "      <td>735763</td>\n",
       "      <td>21.0</td>\n",
       "      <td>391.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2069</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2569</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2737</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>254.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2652</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>240.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2973</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>293.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3090</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>366.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3413</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>228.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2890</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>365.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4369</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>455.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5041</th>\n",
       "      <td>735763</td>\n",
       "      <td>23.0</td>\n",
       "      <td>511.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9303</th>\n",
       "      <td>735763</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2570</th>\n",
       "      <td>735763</td>\n",
       "      <td>39.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2974</th>\n",
       "      <td>735763</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5043</th>\n",
       "      <td>735763</td>\n",
       "      <td>54.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9304</th>\n",
       "      <td>735763</td>\n",
       "      <td>67.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2571</th>\n",
       "      <td>735763</td>\n",
       "      <td>69.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4370</th>\n",
       "      <td>735763</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2738</th>\n",
       "      <td>735763</td>\n",
       "      <td>81.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11951</th>\n",
       "      <td>735763</td>\n",
       "      <td>83.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11919</th>\n",
       "      <td>735763</td>\n",
       "      <td>99.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4371</th>\n",
       "      <td>735763</td>\n",
       "      <td>227.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9306</th>\n",
       "      <td>735822</td>\n",
       "      <td>159.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8190</th>\n",
       "      <td>735892</td>\n",
       "      <td>150.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7910</th>\n",
       "      <td>735892</td>\n",
       "      <td>150.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5044</th>\n",
       "      <td>735901</td>\n",
       "      <td>50.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4374</th>\n",
       "      <td>736023</td>\n",
       "      <td>81.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5045</th>\n",
       "      <td>736023</td>\n",
       "      <td>81.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6781</th>\n",
       "      <td>736023</td>\n",
       "      <td>81.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28470 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         广告id   出价_y   曝光次数\n",
       "0         415  300.0    2.0\n",
       "0         415  300.0    3.0\n",
       "0         415  300.0    9.0\n",
       "1         415  300.0    1.0\n",
       "1         415  300.0    6.0\n",
       "1         415  600.0    1.0\n",
       "1         415  600.0    2.0\n",
       "2         415  600.0    7.0\n",
       "2         415  600.0    4.0\n",
       "2         415  600.0    3.0\n",
       "2         415  600.0    6.0\n",
       "1         562   31.0    1.0\n",
       "3         562  200.0    1.0\n",
       "2         647   31.0    1.0\n",
       "11        853  127.0    1.0\n",
       "10        853  128.0    4.0\n",
       "10        853  150.0   14.0\n",
       "1         890  145.0   12.0\n",
       "3         979  100.0    1.0\n",
       "7         979  100.0    2.0\n",
       "2        1114  350.0    4.0\n",
       "18       1158  100.0   16.0\n",
       "15       1158  100.0    7.0\n",
       "7        1216   31.0    2.0\n",
       "11       1216   31.0    9.0\n",
       "18       1216   31.0   12.0\n",
       "2        1265   31.0    1.0\n",
       "12       1280   31.0   17.0\n",
       "12       1280   31.0   24.0\n",
       "14       1280   31.0   41.0\n",
       "14       1280   31.0   31.0\n",
       "12       1280   31.0   26.0\n",
       "16       1280   31.0   40.0\n",
       "14       1280   31.0   30.0\n",
       "21       1280   31.0   32.0\n",
       "23       1383   34.0    7.0\n",
       "15       1491   93.0   57.0\n",
       "17       1491   93.0   51.0\n",
       "16       1491   93.0   52.0\n",
       "17       1491   93.0   49.0\n",
       "25       1747   42.0    1.0\n",
       "22       1747   45.0    2.0\n",
       "27       1747   52.0    3.0\n",
       "28       1747  313.0    1.0\n",
       "17       1943  180.0    1.0\n",
       "31       2171  150.0    6.0\n",
       "25       2171  150.0    4.0\n",
       "26       2171  150.0    2.0\n",
       "18       2317  186.0    1.0\n",
       "6        2317  200.0    1.0\n",
       "...       ...    ...    ...\n",
       "2972   735763   18.0   33.0\n",
       "3412   735763   18.0   22.0\n",
       "2889   735763   18.0   18.0\n",
       "4368   735763   18.0   27.0\n",
       "5040   735763   18.0   16.0\n",
       "4888   735763   18.0   21.0\n",
       "1568   735763   21.0  225.0\n",
       "1780   735763   21.0  202.0\n",
       "5655   735763   21.0  468.0\n",
       "6777   735763   21.0  362.0\n",
       "6619   735763   21.0  263.0\n",
       "8187   735763   21.0  317.0\n",
       "8174   735763   21.0  475.0\n",
       "7909   735763   21.0  351.0\n",
       "8417   735763   21.0  194.0\n",
       "8888   735763   21.0  125.0\n",
       "9306   735763   21.0  160.0\n",
       "9302   735763   21.0  169.0\n",
       "11950  735763   21.0  192.0\n",
       "11918  735763   21.0  327.0\n",
       "11745  735763   21.0  265.0\n",
       "11933  735763   21.0  391.0\n",
       "2069   735763   23.0  194.0\n",
       "2569   735763   23.0  183.0\n",
       "2737   735763   23.0  254.0\n",
       "2652   735763   23.0  240.0\n",
       "2973   735763   23.0  293.0\n",
       "3090   735763   23.0  366.0\n",
       "3413   735763   23.0  228.0\n",
       "2890   735763   23.0  365.0\n",
       "4369   735763   23.0  455.0\n",
       "5041   735763   23.0  511.0\n",
       "9303   735763   27.0    1.0\n",
       "2570   735763   39.0    1.0\n",
       "2974   735763   40.0    1.0\n",
       "5043   735763   54.0    1.0\n",
       "9304   735763   67.0    1.0\n",
       "2571   735763   69.0    1.0\n",
       "4370   735763   74.0    1.0\n",
       "2738   735763   81.0    1.0\n",
       "11951  735763   83.0    1.0\n",
       "11919  735763   99.0    1.0\n",
       "4371   735763  227.0    1.0\n",
       "9306   735822  159.0    2.0\n",
       "8190   735892  150.0    1.0\n",
       "7910   735892  150.0    2.0\n",
       "5044   735901   50.0    2.0\n",
       "4374   736023   81.0    3.0\n",
       "5045   736023   81.0    5.0\n",
       "6781   736023   81.0    2.0\n",
       "\n",
       "[28470 rows x 3 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#temp1.reset_index(inplace=True)\n",
    "#temp2.drop(['样本id','创建时间','素材尺寸','广告行业id','商品类型','商品id','广告账号id','投放时间','人群定向','出价_x'],axis=1,inplace=True)\n",
    "#.drop_duplicates(keep='first',inplace=True)\n",
    "temp2.sort_values(['广告id','出价_y'],inplace=True)\n",
    "temp2[temp2['曝光次数']!=0.0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 包含 7 个 64 位无符号整型数字（逗号分隔），每个整数分别代表周一到周日的投放时段。\n",
    "# 该整数转为 2 进制后从低到高 48 位 bit 代表全天各时段（半小时为一时间窗口）是否投放，1-投放，0-不投。\n",
    "# 举例说明，17179865088= 1111111111111111111111000000000000，代表投放时段为 6：00-17：00；\n",
    "# 281474976710655=111111111111111111111111111111111111111111111111，代表全天投放。\n",
    "#把数字转换成二进制，并拆分出每一位，逆序后形成一个List\n",
    "# t = '17179865088,281474976710655'\n",
    "# print(Period2List (t))\n",
    "def long2bit (n):\n",
    "    pass\n",
    "    #转成二进制,前面再补0到48位\n",
    "    t = bin(n)[2:].zfill(48)\n",
    "    lstRet = list(t)\n",
    "    lstRet.reverse()\n",
    "    return lstRet\n",
    "\n",
    "#把投放时段转换成List,二层; \n",
    "#投放时段：字符串。包含 7 个 64 位无符号整型数字（逗号分隔）\n",
    "#测试：\n",
    "# t =  \"17179865088,17179865088,4294950912,17109865088,17109865088,281474976710655,281474976710655\"\n",
    "# t = '17179865088,281474976710655'\n",
    "#print(Period2List (t))  \n",
    "def Period2List (strPeriod):\n",
    "    pass\n",
    "    lstT = strPeriod.split(',')\n",
    "    lstR = []\n",
    "    for x in lstT:\n",
    "        lstR.append(long2bit(int(x)))\n",
    "    return lstR"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 流量分析\n",
    "对曝光数据进行流量分析，分析每天的每半小时流量情况、各素材尺寸展现的概率、各广告行业的展现概率、各商品类型的展现概率等。。。\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "adstaticfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\ad_static_feature.out ',sep='\\t',\n",
    "                              nrows=10000000,header=None,names=['广告id','创建时间','广告账号id','商品id','商品类型'\n",
    "                                                            ,'广告行业id','素材尺寸']\n",
    "                          ,low_memory=False)\n",
    "adstaticdf = pd.DataFrame(adstaticfile)\n",
    "# 无重复数据\n",
    "adstaticdf.drop_duplicates(keep='first',inplace=True)\n",
    "\n",
    "#广告行业id 包含,分隔，需要清洗  8191 rows  剩余727720条\n",
    "#adstaticdf['广告行业id'].str.contains(',')\n",
    "adstaticdf=adstaticdf[~adstaticdf['广告行业id'].str.contains(',')]\n",
    "#adstaticdf['广告行业id']=pd.to_numeric(adstaticdf['广告行业id'])\n",
    "adstaticdf['广告行业id']=adstaticdf['广告行业id'].astype('int64')\n",
    "\n",
    "#商品id 包含,分隔，需要清洗  3 rows  剩余 727717 条\n",
    "#adstaticdf[adstaticdf['商品id'].str.contains(',')]\n",
    "adstaticdf=adstaticdf[~adstaticdf['商品id'].str.contains(',')]\n",
    "adstaticdf['商品id']=pd.to_numeric(adstaticdf['商品id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [],
   "source": [
    "exposureLogfile = pd.read_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\totalExposureLog.out ',sep='\\t',\n",
    "                              header=None,names=['请求id','请求时间','广告位id','用户id','广告id'\n",
    "                                                            ,'素材尺寸','出价bid','pctr','quality_ecpm','totalEcpm']\n",
    "                             ,iterator=True,low_memory =False)\n",
    "for i in range(120):\n",
    "    try:\n",
    "        exposureLogdf = exposureLogfile.get_chunk(1000000)\n",
    "         #去重\n",
    "        exposureLogdf.drop_duplicates(keep='first',inplace=True)        \n",
    "        exposureLogdf['请求时间']=exposureLogdf['请求时间'].apply(lambda x:time.strftime(\"%Y%m%d%H%M%S\",time.localtime(x))) \n",
    "        exposureLogdf['请求时间1']=exposureLogdf['请求时间'].apply(lambda x:(str(x[:10])+'-'+str(int(x[10:12])//30))) \n",
    "        temp=pd.merge(exposureLogdf,adstaticfile,how='left',left_on='广告id',right_on='广告id')\n",
    "        temp['素材尺寸_x']=temp['素材尺寸_x'].astype(\"str\")\n",
    "\n",
    "        # 统计半小时的总请求数\n",
    "        totalcount=pd.DataFrame()\n",
    "        totalcount=temp.groupby(by=['请求时间1'])['请求id'].nunique().reset_index() #distinct\n",
    "        #total[total['请求时间1']=='2019021623-1']['请求id'].get_values()[0]\n",
    "        totalcount.rename(index=str, columns={\"请求时间1\":\"请求时间\",\"请求id\":\"总次数\"},inplace=True) \n",
    "\n",
    "        # 统计半小时 各素材尺寸的展示次数\n",
    "        scalecount=pd.DataFrame()\n",
    "        scalecount=(temp.groupby(by=['请求时间1','素材尺寸_x'])['请求id'].count()).reset_index() \n",
    "        scalecount.rename(index=str, columns={\"请求时间1\":\"请求时间\",\"素材尺寸_x\": \"类型\",\"请求id\":\"次数\"},inplace=True) \n",
    "        scalecount['类别']='scalecount'\n",
    "\n",
    "        # 统计半小时 各商品id的展示次数\n",
    "        productidcount=pd.DataFrame()\n",
    "        productidcount=temp.groupby(by=['请求时间1','商品id'])['请求id'].count().reset_index()   # -1的需要考虑删除了\n",
    "        productidcount.rename(index=str, columns={\"请求时间1\":\"请求时间\",\"商品id\": \"类型\",\"请求id\":\"次数\"},inplace=True) \n",
    "        productidcount['类别']='productidcount'\n",
    "\n",
    "        # 统计半小时 各商品类型的展示次数\n",
    "        producttypecount=pd.DataFrame()\n",
    "        producttypecount=temp.groupby(by=['请求时间1','商品类型'])['请求id'].count().reset_index() \n",
    "        producttypecount.rename(index=str, columns={\"请求时间1\":\"请求时间\",\"商品类型\": \"类型\",\"请求id\":\"次数\"},inplace=True) \n",
    "        producttypecount['类别']='producttypecount'\n",
    "\n",
    "        # 统计半小时 各广告行业id的展示次数\n",
    "        adindustryidcount=pd.DataFrame()\n",
    "        adindustryidcount=temp.groupby(by=['请求时间1','广告行业id'])['请求id'].count().reset_index()\n",
    "        adindustryidcount.rename(index=str, columns={\"请求时间1\":\"请求时间\",\"广告行业id\": \"类型\",\"请求id\":\"次数\"},inplace=True) \n",
    "        adindustryidcount['类别']='adindustryidcount'\n",
    "        \n",
    "        scalecount=pd.concat([scalecount,productidcount,producttypecount,adindustryidcount])\n",
    "        scalecount=pd.merge(scalecount,totalcount,how='left',left_on=\"请求时间\",right_on=\"请求时间\")\n",
    "        # 追加写模式   columns 请求时间\t类型\t次数\t类别\t总次数\n",
    "        scalecount.to_csv('E:\\\\work\\\\tencent\\\\algo.qq.com_641013010_testa\\\\testA\\\\halfhourExposureLog.csv', \n",
    "                          sep='\\t', header=False, index=False,mode='a')\n",
    "        \n",
    "    except StopIteration:\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### 投放时间\n",
    "可以考虑把7个数都换成2进制统计一共有多少个1，但这样就会丢失黄金时间段的概念，或者把全部二进制都换算成特征维度，交给模型去算系数。\n",
    "7*48个特征，特征值为0-1\n",
    "\n",
    "### 投放人群\n",
    "投放人群统计，按照广告设置的投放人群在用户数据中，统计满足条件的用户数有多少（用户数/用户总人数），访问用户id也会影响到pctr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  },
  "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": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}