data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 京东JData算法大赛(1)-数据清洗"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "  比赛的题目是高潜用户的购买意向的预测,从机器学习的角度来讲我们可以认为这是一个二分类的任务.那么我们就是尝试去构建自己的正负样本.  \n",
    "  由于我们拿到的是原始数据,里面存在很多噪声,因而第一步我们先要对数据清洗,比如说:  \n",
    "- 去掉只有购买记录的用户(没有可用的历史浏览等记录来预测用户将来的购买意向)  \n",
    "- 去掉浏览量很大而购买量很少的用户(惰性用户或爬虫用户)\n",
    "- 去掉最后5(7)天没有记录(交互)的商品和用户\n",
    "- ......"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "为了能够进行上述清洗,在此首先构造了简单的用户(user)行为特征和商品(item)行为行为特征,对应于两张表user_table和item_table\n",
    "- **user_table**特征包括:  \n",
    "  user_id(用户id),age(年龄),sex(性别),  \n",
    "  user_lv_cd(用户级别),browse_num(浏览数),  \n",
    "  addcart_num(加购数),delcart_num(删购数),  \n",
    "  buy_num(购买数),favor_num(收藏数),  \n",
    "  click_num(点击数),buy_addcart_ratio(购买加购转化率),  \n",
    "  buy_browse_ratio(购买浏览转化率),  \n",
    "  buy_click_ratio(购买点击转化率),  \n",
    "  buy_favor_ratio(购买收藏转化率)  \n",
    "- **item_table**特征包括:  \n",
    "  sku_id(商品id),attr1,attr2,  \n",
    "  attr3,cate,brand,browse_num,  \n",
    "  addcart_num,delcart_num,  \n",
    "  buy_num,favor_num,click_num,  \n",
    "  buy_addcart_ratio,buy_browse_ratio,  \n",
    "  buy_click_ratio,buy_favor_ratio,  \n",
    "  comment_num(评论数),  \n",
    "  has_bad_comment(是否有差评),  \n",
    "  bad_comment_rate(差评率)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "接下来我们将采用python2.7, pandas等工具简单介绍如何实现上述过程."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 定义文件名\n",
    "ACTION_201602_FILE = \"data/JData_Action_201602.csv\"\n",
    "ACTION_201603_FILE = \"data/JData_Action_201603.csv\"\n",
    "ACTION_201603_EXTRA_FILE = \"data/JData_Action_201603_extra.csv\"\n",
    "ACTION_201604_FILE = \"data/JData_Action_201604.csv\"\n",
    "COMMENT_FILE = \"data/JData_Comment.csv\"\n",
    "PRODUCT_FILE = \"data/JData_Product.csv\"\n",
    "USER_FILE = \"data/JData_User.csv\"\n",
    "NEW_USER_FILE = \"data/JData_User_New.csv\"\n",
    "USER_TABLE_FILE = \"data/user_table.csv\"\n",
    "ITEM_TABLE_FILE = \"data/item_table.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 导入相关包\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from collections import Counter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "一般为了快速验证自己的想法我们会从原数据中抽取一部分作为样本，在此每个文件我将抽取10000行记录．  \n",
    "我将原始文件放在data_ori/文件夹下，样本文件放在data/下．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "num_sample = 10000\n",
    "file_list = [ACTION_201602_FILE, ACTION_201603_FILE,\n",
    "             ACTION_201603_EXTRA_FILE, ACTION_201604_FILE,\n",
    "             NEW_USER_FILE, COMMENT_FILE]\n",
    "\n",
    "for fname in file_list:\n",
    "    with open(\"data_ori/\" + fname[5:], 'rb') as fi:\n",
    "        with open(fname, 'wb') as fo:\n",
    "            for i in xrange(num_sample):\n",
    "                fo.write(fi.readline())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "接下来以构建user_table为例,item_table与之类似."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 功能函数: 对每一个user分组的数据进行统计\n",
    "def add_type_count(group):\n",
    "    behavior_type = group.type.astype(int)\n",
    "    # 用户行为类别\n",
    "    type_cnt = Counter(behavior_type)\n",
    "    # 1: 浏览 2: 加购 3: 删除\n",
    "    # 4: 购买 5: 收藏 6: 点击\n",
    "    group['browse_num'] = type_cnt[1]\n",
    "    group['addcart_num'] = type_cnt[2]\n",
    "    group['delcart_num'] = type_cnt[3]\n",
    "    group['buy_num'] = type_cnt[4]\n",
    "    group['favor_num'] = type_cnt[5]\n",
    "    group['click_num'] = type_cnt[6]\n",
    "\n",
    "    return group[['user_id', 'browse_num', 'addcart_num',\n",
    "                  'delcart_num', 'buy_num', 'favor_num',\n",
    "                  'click_num']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由于用户行为数据量较大,一次性读入可能造成内存错误(Memory Error),因而使用pandas的分块(chunk)读取."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#　对action数据进行统计\n",
    "# 根据自己的需求调节chunk_size大小\n",
    "def get_from_action_data(fname, chunk_size=100000):\n",
    "    reader = pd.read_csv(fname, header=0, iterator=True)\n",
    "    chunks = []\n",
    "    loop = True\n",
    "    while loop:\n",
    "        try:\n",
    "            # 只读取user_id和type两个字段\n",
    "            chunk = reader.get_chunk(chunk_size)[[\"user_id\", \"type\"]]\n",
    "            chunks.append(chunk)\n",
    "        except StopIteration:\n",
    "            loop = False\n",
    "            print(\"Iteration is stopped\")\n",
    "    # 将块拼接为pandas dataframe格式\n",
    "    df_ac = pd.concat(chunks, ignore_index=True)\n",
    "    # 按user_id分组，对每一组进行统计\n",
    "    df_ac = df_ac.groupby(['user_id'], as_index=False).apply(add_type_count)\n",
    "    # 将重复的行丢弃\n",
    "    df_ac = df_ac.drop_duplicates('user_id')\n",
    "\n",
    "    return df_ac"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 将各个action数据的统计量进行聚合\n",
    "def merge_action_data():\n",
    "    df_ac = []\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201602_FILE))\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201603_FILE))\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201603_EXTRA_FILE))\n",
    "    df_ac.append(get_from_action_data(fname=ACTION_201604_FILE))\n",
    "\n",
    "    df_ac = pd.concat(df_ac, ignore_index=True)\n",
    "    # 用户在不同action表中统计量求和\n",
    "    df_ac = df_ac.groupby(['user_id'], as_index=False).sum()\n",
    "    #　构造转化率字段\n",
    "    df_ac['buy_addcart_ratio'] = df_ac['buy_num'] / df_ac['addcart_num']\n",
    "    df_ac['buy_browse_ratio'] = df_ac['buy_num'] / df_ac['browse_num']\n",
    "    df_ac['buy_click_ratio'] = df_ac['buy_num'] / df_ac['click_num']\n",
    "    df_ac['buy_favor_ratio'] = df_ac['buy_num'] / df_ac['favor_num']\n",
    "    \n",
    "    # 将大于１的转化率字段置为１(100%)\n",
    "    df_ac.ix[df_ac['buy_addcart_ratio'] > 1., 'buy_addcart_ratio'] = 1.\n",
    "    df_ac.ix[df_ac['buy_browse_ratio'] > 1., 'buy_browse_ratio'] = 1.\n",
    "    df_ac.ix[df_ac['buy_click_ratio'] > 1., 'buy_click_ratio'] = 1.\n",
    "    df_ac.ix[df_ac['buy_favor_ratio'] > 1., 'buy_favor_ratio'] = 1.\n",
    "\n",
    "    return df_ac"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#　从JData_User表中抽取需要的字段\n",
    "def get_from_jdata_user():\n",
    "    df_usr = pd.read_csv(NEW_USER_FILE, header=0)\n",
    "    df_usr = df_usr[[\"user_id\", \"age\", \"sex\", \"user_lv_cd\"]]\n",
    "    return df_usr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "构建user table的main函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Iteration is stopped\n",
      "Iteration is stopped\n",
      "Iteration is stopped\n",
      "Iteration is stopped\n"
     ]
    }
   ],
   "source": [
    "user_base = get_from_jdata_user()\n",
    "user_behavior = merge_action_data()\n",
    "\n",
    "# 连接成一张表，类似于SQL的左连接(left join)\n",
    "user_behavior = pd.merge(user_base, user_behavior, on=['user_id'], how='left')\n",
    "# 保存为user_table.csv\n",
    "user_behavior.to_csv(USER_TABLE_FILE, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "item table的构建过程与user table的类似，这里直接运行我事先写好的脚本．"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Iteration is stopped\n",
      "Iteration is stopped\n",
      "Iteration is stopped\n",
      "Iteration is stopped\n"
     ]
    }
   ],
   "source": [
    "!python create_item_table.py"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据清洗"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54</td>\n",
       "      <td>-1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>102.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>79</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>674.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1075.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>310.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>479.0</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>0.009677</td>\n",
       "      <td>0.006263</td>\n",
       "      <td>0.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>266</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>264.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>515.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>333</td>\n",
       "      <td>-1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>300.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>730.0</td>\n",
       "      <td>0.043478</td>\n",
       "      <td>0.006667</td>\n",
       "      <td>0.002740</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  age  sex  user_lv_cd  browse_num  addcart_num  delcart_num  \\\n",
       "0       54   -1    2           1       102.0          1.0          0.0   \n",
       "1       79    3    2           2       674.0         20.0         15.0   \n",
       "2      100    1    2           3       310.0          5.0          2.0   \n",
       "3      266    0    2           2       264.0          0.0          0.0   \n",
       "4      333   -1    2           4       300.0         46.0          0.0   \n",
       "\n",
       "   buy_num  favor_num  click_num  buy_addcart_ratio  buy_browse_ratio  \\\n",
       "0      0.0        0.0      228.0           0.000000          0.000000   \n",
       "1      0.0       11.0     1075.0           0.000000          0.000000   \n",
       "2      3.0        4.0      479.0           0.600000          0.009677   \n",
       "3      0.0        0.0      515.0                NaN          0.000000   \n",
       "4      2.0        1.0      730.0           0.043478          0.006667   \n",
       "\n",
       "   buy_click_ratio  buy_favor_ratio  \n",
       "0         0.000000              NaN  \n",
       "1         0.000000             0.00  \n",
       "2         0.006263             0.75  \n",
       "3         0.000000              NaN  \n",
       "4         0.002740             1.00  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_usr = pd.read_csv(USER_TABLE_FILE, header=0)\n",
    "\n",
    "# 输出前5行数据\n",
    "df_usr.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>102812</th>\n",
       "      <td>81130</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102942</th>\n",
       "      <td>44318</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>230.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>369.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103155</th>\n",
       "      <td>53654</td>\n",
       "      <td>-1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103462</th>\n",
       "      <td>19566</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103563</th>\n",
       "      <td>75712</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>77.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>173.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        user_id  age  sex  user_lv_cd  browse_num  addcart_num  delcart_num  \\\n",
       "102812    81130    2    0           5         9.0          0.0          0.0   \n",
       "102942    44318    3    0           5       230.0         11.0          3.0   \n",
       "103155    53654   -1    2           5        56.0          0.0          0.0   \n",
       "103462    19566    3    0           5         6.0          0.0          2.0   \n",
       "103563    75712    2    0           5        77.0          1.0          1.0   \n",
       "\n",
       "        buy_num  favor_num  click_num  buy_addcart_ratio  buy_browse_ratio  \\\n",
       "102812      0.0        0.0       13.0                NaN               0.0   \n",
       "102942      0.0        0.0      369.0                0.0               0.0   \n",
       "103155      0.0        0.0       95.0                NaN               0.0   \n",
       "103462      0.0        0.0        9.0                NaN               0.0   \n",
       "103563      0.0        1.0      173.0                0.0               0.0   \n",
       "\n",
       "        buy_click_ratio  buy_favor_ratio  \n",
       "102812              0.0              NaN  \n",
       "102942              0.0              NaN  \n",
       "103155              0.0              NaN  \n",
       "103462              0.0              NaN  \n",
       "103563              0.0              0.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 输出购买数为0的后5条数据\n",
    "df_usr[df_usr['buy_num'] == 0].tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# 删除购买数为0的用户\n",
    "df_usr = df_usr[df_usr['buy_num'] != 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>31,235.000</td>\n",
       "      <td>31,235.000</td>\n",
       "      <td>31,235.000</td>\n",
       "      <td>31,235.000</td>\n",
       "      <td>31,184.000</td>\n",
       "      <td>31,184.000</td>\n",
       "      <td>31,184.000</td>\n",
       "      <td>31,184.000</td>\n",
       "      <td>31,184.000</td>\n",
       "      <td>31,184.000</td>\n",
       "      <td>31,184.000</td>\n",
       "      <td>31,184.000</td>\n",
       "      <td>31,184.000</td>\n",
       "      <td>31,184.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>50,151.790</td>\n",
       "      <td>2.055</td>\n",
       "      <td>0.970</td>\n",
       "      <td>4.348</td>\n",
       "      <td>433.447</td>\n",
       "      <td>13.698</td>\n",
       "      <td>6.377</td>\n",
       "      <td>1.688</td>\n",
       "      <td>2.359</td>\n",
       "      <td>701.879</td>\n",
       "      <td>0.310</td>\n",
       "      <td>0.015</td>\n",
       "      <td>0.025</td>\n",
       "      <td>0.825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>29,580.226</td>\n",
       "      <td>1.220</td>\n",
       "      <td>0.958</td>\n",
       "      <td>0.768</td>\n",
       "      <td>557.339</td>\n",
       "      <td>20.063</td>\n",
       "      <td>11.318</td>\n",
       "      <td>1.561</td>\n",
       "      <td>6.894</td>\n",
       "      <td>911.288</td>\n",
       "      <td>0.305</td>\n",
       "      <td>0.035</td>\n",
       "      <td>0.125</td>\n",
       "      <td>0.317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>3.000</td>\n",
       "      <td>-1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.001</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>24,612.500</td>\n",
       "      <td>2.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>4.000</td>\n",
       "      <td>106.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>161.000</td>\n",
       "      <td>0.091</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.002</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>48,847.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>259.000</td>\n",
       "      <td>8.000</td>\n",
       "      <td>3.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>406.000</td>\n",
       "      <td>0.200</td>\n",
       "      <td>0.006</td>\n",
       "      <td>0.004</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>75,959.500</td>\n",
       "      <td>3.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>551.250</td>\n",
       "      <td>17.000</td>\n",
       "      <td>8.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>893.000</td>\n",
       "      <td>0.429</td>\n",
       "      <td>0.013</td>\n",
       "      <td>0.009</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>103,611.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>5.000</td>\n",
       "      <td>16,038.000</td>\n",
       "      <td>907.000</td>\n",
       "      <td>746.000</td>\n",
       "      <td>51.000</td>\n",
       "      <td>538.000</td>\n",
       "      <td>16,979.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          user_id        age        sex  user_lv_cd  browse_num  addcart_num  \\\n",
       "count  31,235.000 31,235.000 31,235.000  31,235.000  31,184.000   31,184.000   \n",
       "mean   50,151.790      2.055      0.970       4.348     433.447       13.698   \n",
       "std    29,580.226      1.220      0.958       0.768     557.339       20.063   \n",
       "min         3.000     -1.000      0.000       1.000       1.000        0.000   \n",
       "25%    24,612.500      2.000      0.000       4.000     106.000        3.000   \n",
       "50%    48,847.000      2.000      1.000       5.000     259.000        8.000   \n",
       "75%    75,959.500      3.000      2.000       5.000     551.250       17.000   \n",
       "max   103,611.000      5.000      2.000       5.000  16,038.000      907.000   \n",
       "\n",
       "       delcart_num    buy_num  favor_num  click_num  buy_addcart_ratio  \\\n",
       "count   31,184.000 31,184.000 31,184.000 31,184.000         31,184.000   \n",
       "mean         6.377      1.688      2.359    701.879              0.310   \n",
       "std         11.318      1.561      6.894    911.288              0.305   \n",
       "min          0.000      1.000      0.000      0.000              0.001   \n",
       "25%          1.000      1.000      0.000    161.000              0.091   \n",
       "50%          3.000      1.000      0.000    406.000              0.200   \n",
       "75%          8.000      2.000      2.000    893.000              0.429   \n",
       "max        746.000     51.000    538.000 16,979.000              1.000   \n",
       "\n",
       "       buy_browse_ratio  buy_click_ratio  buy_favor_ratio  \n",
       "count        31,184.000       31,184.000       31,184.000  \n",
       "mean              0.015            0.025            0.825  \n",
       "std               0.035            0.125            0.317  \n",
       "min               0.000            0.000            0.002  \n",
       "25%               0.003            0.002            1.000  \n",
       "50%               0.006            0.004            1.000  \n",
       "75%               0.013            0.009            1.000  \n",
       "max               1.000            1.000            1.000  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 输出格式设置，保留三位小数\n",
    "pd.options.display.float_format = '{:,.3f}'.format\n",
    "\n",
    "# 输出user table的统计信息,包括\n",
    "# 总数，均值，方差，最小值，1/4分位数，1/2分位数，3/4分位数，最大值\n",
    "df_usr.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>user_lv_cd</th>\n",
       "      <th>browse_num</th>\n",
       "      <th>addcart_num</th>\n",
       "      <th>delcart_num</th>\n",
       "      <th>buy_num</th>\n",
       "      <th>favor_num</th>\n",
       "      <th>click_num</th>\n",
       "      <th>buy_addcart_ratio</th>\n",
       "      <th>buy_browse_ratio</th>\n",
       "      <th>buy_click_ratio</th>\n",
       "      <th>buy_favor_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>51840</th>\n",
       "      <td>19313</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>9,214.000</td>\n",
       "      <td>191.000</td>\n",
       "      <td>61.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>10,952.000</td>\n",
       "      <td>0.005</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58350</th>\n",
       "      <td>72063</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>6,304.000</td>\n",
       "      <td>79.000</td>\n",
       "      <td>50.000</td>\n",
       "      <td>1.000</td>\n",
       "      <td>19.000</td>\n",
       "      <td>8,503.000</td>\n",
       "      <td>0.013</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.053</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       user_id  age  sex  user_lv_cd  browse_num  addcart_num  delcart_num  \\\n",
       "51840    19313    2    0           5   9,214.000      191.000       61.000   \n",
       "58350    72063    3    2           4   6,304.000       79.000       50.000   \n",
       "\n",
       "       buy_num  favor_num  click_num  buy_addcart_ratio  buy_browse_ratio  \\\n",
       "51840    1.000      1.000 10,952.000              0.005             0.000   \n",
       "58350    1.000     19.000  8,503.000              0.013             0.000   \n",
       "\n",
       "       buy_click_ratio  buy_favor_ratio  \n",
       "51840            0.000            1.000  \n",
       "58350            0.000            0.053  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看购买数为１而浏览数大于8000的用户\n",
    "df_usr[(df_usr['buy_num'] < 2) & (df_usr['browse_num'] > 6000)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    ""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2.0
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}