26. Pandas处理分析网站原始访问日志.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas处理分析网站原始访问日志\n",
    "\n",
    "目标：真实项目的实战，探索Pandas的数据处理与分析\n",
    "\n",
    "实例：  \n",
    "数据来源：我自己的wordpress博客http://www.crazyant.net/ 的访问日志    \n",
    "\n",
    "实现步骤：  \n",
    "1、读取数据、清理、格式化  \n",
    "2、统计爬虫spider的访问比例，输出柱状图  \n",
    "3、统计http状态码的访问占比，输出饼图  \n",
    "4、统计按小时、按天的PV/UV流量趋势，输出折线图  \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1、读取数据并清理格式化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "pd.set_option('display.max_colwidth', -1)\n",
    "\n",
    "from pyecharts import options as opts\n",
    "from pyecharts.charts import Bar,Pie,Line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "b'Skipping line 2245: expected 10 fields, saw 16\\nSkipping line 2889: expected 10 fields, saw 14\\nSkipping line 2890: expected 10 fields, saw 14\\nSkipping line 2891: expected 10 fields, saw 13\\nSkipping line 2892: expected 10 fields, saw 13\\nSkipping line 2900: expected 10 fields, saw 11\\nSkipping line 2902: expected 10 fields, saw 11\\nSkipping line 3790: expected 10 fields, saw 14\\nSkipping line 3791: expected 10 fields, saw 14\\nSkipping line 3792: expected 10 fields, saw 13\\nSkipping line 3793: expected 10 fields, saw 13\\nSkipping line 3833: expected 10 fields, saw 11\\nSkipping line 3835: expected 10 fields, saw 11\\nSkipping line 9936: expected 10 fields, saw 16\\n'\n",
      "b'Skipping line 11748: expected 10 fields, saw 11\\nSkipping line 11750: expected 10 fields, saw 11\\n'\n"
     ]
    }
   ],
   "source": [
    "# 读取整个目录，将所有的文件合并到一个dataframe\n",
    "data_dir = \"./datas/crazyant/blog_access_log\"\n",
    "\n",
    "df_list = []\n",
    "\n",
    "import os\n",
    "for fname in os.listdir(f\"{data_dir}\"):\n",
    "    df_list.append(pd.read_csv(f\"{data_dir}/{fname}\", sep=\" \", header=None, error_bad_lines=False))\n",
    "\n",
    "df = pd.concat(df_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>106.11.153.226</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[02/Dec/2019:22:40:18</td>\n",
       "      <td>+0800]</td>\n",
       "      <td>GET /740.html?replytocom=1194 HTTP/1.0</td>\n",
       "      <td>200</td>\n",
       "      <td>13446</td>\n",
       "      <td>-</td>\n",
       "      <td>YisouSpider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>42.156.254.60</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[02/Dec/2019:22:40:23</td>\n",
       "      <td>+0800]</td>\n",
       "      <td>POST /wp-json/wordpress-popular-posts/v1/popular-posts HTTP/1.0</td>\n",
       "      <td>201</td>\n",
       "      <td>55</td>\n",
       "      <td>http://www.crazyant.net/740.html?replytocom=1194</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>106.11.159.254</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[02/Dec/2019:22:40:27</td>\n",
       "      <td>+0800]</td>\n",
       "      <td>GET /576.html HTTP/1.0</td>\n",
       "      <td>200</td>\n",
       "      <td>13461</td>\n",
       "      <td>-</td>\n",
       "      <td>YisouSpider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>106.11.157.254</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[02/Dec/2019:22:40:28</td>\n",
       "      <td>+0800]</td>\n",
       "      <td>GET /?lwfcdw=t9n2d3&amp;oqzohc=m5e7j1&amp;oubyvq=iab6a3&amp;oudmbg=6osqd3 HTTP/1.0</td>\n",
       "      <td>200</td>\n",
       "      <td>10485</td>\n",
       "      <td>-</td>\n",
       "      <td>YisouSpider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>42.156.137.109</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>[02/Dec/2019:22:40:30</td>\n",
       "      <td>+0800]</td>\n",
       "      <td>POST /wp-json/wordpress-popular-posts/v1/popular-posts HTTP/1.0</td>\n",
       "      <td>201</td>\n",
       "      <td>55</td>\n",
       "      <td>http://www.crazyant.net/576.html</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                0  1  2                      3       4  \\\n",
       "0  106.11.153.226  -  -  [02/Dec/2019:22:40:18  +0800]   \n",
       "1  42.156.254.60   -  -  [02/Dec/2019:22:40:23  +0800]   \n",
       "2  106.11.159.254  -  -  [02/Dec/2019:22:40:27  +0800]   \n",
       "3  106.11.157.254  -  -  [02/Dec/2019:22:40:28  +0800]   \n",
       "4  42.156.137.109  -  -  [02/Dec/2019:22:40:30  +0800]   \n",
       "\n",
       "                                                                        5  \\\n",
       "0  GET /740.html?replytocom=1194 HTTP/1.0                                   \n",
       "1  POST /wp-json/wordpress-popular-posts/v1/popular-posts HTTP/1.0          \n",
       "2  GET /576.html HTTP/1.0                                                   \n",
       "3  GET /?lwfcdw=t9n2d3&oqzohc=m5e7j1&oubyvq=iab6a3&oudmbg=6osqd3 HTTP/1.0   \n",
       "4  POST /wp-json/wordpress-popular-posts/v1/popular-posts HTTP/1.0          \n",
       "\n",
       "     6      7                                                 8  \\\n",
       "0  200  13446  -                                                  \n",
       "1  201  55     http://www.crazyant.net/740.html?replytocom=1194   \n",
       "2  200  13461  -                                                  \n",
       "3  200  10485  -                                                  \n",
       "4  201  55     http://www.crazyant.net/576.html                   \n",
       "\n",
       "                                                                                                                                   9  \n",
       "0  YisouSpider                                                                                                                        \n",
       "1  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36  \n",
       "2  YisouSpider                                                                                                                        \n",
       "3  YisouSpider                                                                                                                        \n",
       "4  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>0</th>\n",
       "      <th>3</th>\n",
       "      <th>6</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>106.11.153.226</td>\n",
       "      <td>[02/Dec/2019:22:40:18</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>42.156.254.60</td>\n",
       "      <td>[02/Dec/2019:22:40:23</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>106.11.159.254</td>\n",
       "      <td>[02/Dec/2019:22:40:27</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>106.11.157.254</td>\n",
       "      <td>[02/Dec/2019:22:40:28</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>42.156.137.109</td>\n",
       "      <td>[02/Dec/2019:22:40:30</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                0                      3    6  \\\n",
       "0  106.11.153.226  [02/Dec/2019:22:40:18  200   \n",
       "1  42.156.254.60   [02/Dec/2019:22:40:23  201   \n",
       "2  106.11.159.254  [02/Dec/2019:22:40:27  200   \n",
       "3  106.11.157.254  [02/Dec/2019:22:40:28  200   \n",
       "4  42.156.137.109  [02/Dec/2019:22:40:30  201   \n",
       "\n",
       "                                                                                                                                   9  \n",
       "0  YisouSpider                                                                                                                        \n",
       "1  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36  \n",
       "2  YisouSpider                                                                                                                        \n",
       "3  YisouSpider                                                                                                                        \n",
       "4  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df[[0, 3, 6, 9]].copy()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>ip</th>\n",
       "      <th>stime</th>\n",
       "      <th>status</th>\n",
       "      <th>client</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>106.11.153.226</td>\n",
       "      <td>[02/Dec/2019:22:40:18</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>42.156.254.60</td>\n",
       "      <td>[02/Dec/2019:22:40:23</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>106.11.159.254</td>\n",
       "      <td>[02/Dec/2019:22:40:27</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>106.11.157.254</td>\n",
       "      <td>[02/Dec/2019:22:40:28</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>42.156.137.109</td>\n",
       "      <td>[02/Dec/2019:22:40:30</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ip                  stime  status  \\\n",
       "0  106.11.153.226  [02/Dec/2019:22:40:18  200      \n",
       "1  42.156.254.60   [02/Dec/2019:22:40:23  201      \n",
       "2  106.11.159.254  [02/Dec/2019:22:40:27  200      \n",
       "3  106.11.157.254  [02/Dec/2019:22:40:28  200      \n",
       "4  42.156.137.109  [02/Dec/2019:22:40:30  201      \n",
       "\n",
       "                                                                                                                              client  \n",
       "0  YisouSpider                                                                                                                        \n",
       "1  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36  \n",
       "2  YisouSpider                                                                                                                        \n",
       "3  YisouSpider                                                                                                                        \n",
       "4  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns = [\"ip\", \"stime\", \"status\", \"client\"]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ip        object\n",
       "stime     object\n",
       "status    int64 \n",
       "client    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2、统计spider的比例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>ip</th>\n",
       "      <th>stime</th>\n",
       "      <th>status</th>\n",
       "      <th>client</th>\n",
       "      <th>is_spider</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>106.11.153.226</td>\n",
       "      <td>[02/Dec/2019:22:40:18</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>42.156.254.60</td>\n",
       "      <td>[02/Dec/2019:22:40:23</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>106.11.159.254</td>\n",
       "      <td>[02/Dec/2019:22:40:27</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>106.11.157.254</td>\n",
       "      <td>[02/Dec/2019:22:40:28</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>42.156.137.109</td>\n",
       "      <td>[02/Dec/2019:22:40:30</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ip                  stime  status  \\\n",
       "0  106.11.153.226  [02/Dec/2019:22:40:18  200      \n",
       "1  42.156.254.60   [02/Dec/2019:22:40:23  201      \n",
       "2  106.11.159.254  [02/Dec/2019:22:40:27  200      \n",
       "3  106.11.157.254  [02/Dec/2019:22:40:28  200      \n",
       "4  42.156.137.109  [02/Dec/2019:22:40:30  201      \n",
       "\n",
       "                                                                                                                              client  \\\n",
       "0  YisouSpider                                                                                                                         \n",
       "1  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36   \n",
       "2  YisouSpider                                                                                                                         \n",
       "3  YisouSpider                                                                                                                         \n",
       "4  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36   \n",
       "\n",
       "   is_spider  \n",
       "0  True       \n",
       "1  True       \n",
       "2  True       \n",
       "3  True       \n",
       "4  True       "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"is_spider\"] = df[\"client\"].str.lower().str.contains(\"spider\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    46641\n",
       "True     3637 \n",
       "Name: is_spider, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_spider = df[\"is_spider\"].value_counts()\n",
    "df_spider"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "    <div id=\"c9bd2bcd2d004232b92db8db30eccba0\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "\n",
       "<script>\n",
       "    require(['echarts'], function(echarts) {\n",
       "        var chart_c9bd2bcd2d004232b92db8db30eccba0 = echarts.init(\n",
       "            document.getElementById('c9bd2bcd2d004232b92db8db30eccba0'), 'white', {renderer: 'canvas'});\n",
       "        var option_c9bd2bcd2d004232b92db8db30eccba0 = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"bar\",\n",
       "            \"name\": \"\\u662f\\u5426Spider\",\n",
       "            \"data\": [\n",
       "                46641,\n",
       "                3637\n",
       "            ],\n",
       "            \"barCategoryGap\": \"20%\",\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"\\u662f\\u5426Spider\"\n",
       "            ],\n",
       "            \"selected\": {\n",
       "                \"\\u662f\\u5426Spider\": true\n",
       "            },\n",
       "            \"show\": true\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0\n",
       "    },\n",
       "    \"xAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            },\n",
       "            \"data\": [\n",
       "                \"False\",\n",
       "                \"True\"\n",
       "            ]\n",
       "        }\n",
       "    ],\n",
       "    \"yAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"\\u722c\\u866b\\u8bbf\\u95ee\\u91cf\\u5360\\u6bd4\"\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "        chart_c9bd2bcd2d004232b92db8db30eccba0.setOption(option_c9bd2bcd2d004232b92db8db30eccba0);\n",
       "    });\n",
       "</script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x1a3a836d4c8>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bar = (\n",
    "        Bar()\n",
    "        .add_xaxis([str(x) for x in df_spider.index])\n",
    "        .add_yaxis(\"是否Spider\", df_spider.values.tolist())\n",
    "        .set_global_opts(title_opts=opts.TitleOpts(title=\"爬虫访问量占比\"))\n",
    ")\n",
    "bar.render_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3、访问状态码的数量对比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "status\n",
       "200    41924\n",
       "201    3432 \n",
       "206    70   \n",
       "301    2364 \n",
       "302    23   \n",
       "304    19   \n",
       "400    20   \n",
       "403    92   \n",
       "404    1474 \n",
       "405    12   \n",
       "444    846  \n",
       "500    1    \n",
       "504    1    \n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_status = df.groupby(\"status\").size()\n",
    "df_status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(200, 41924),\n",
       " (201, 3432),\n",
       " (206, 70),\n",
       " (301, 2364),\n",
       " (302, 23),\n",
       " (304, 19),\n",
       " (400, 20),\n",
       " (403, 92),\n",
       " (404, 1474),\n",
       " (405, 12),\n",
       " (444, 846),\n",
       " (500, 1),\n",
       " (504, 1)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(zip(df_status.index, df_status))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "    <div id=\"4412254edcf447b68e65faab50374114\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "\n",
       "<script>\n",
       "    require(['echarts'], function(echarts) {\n",
       "        var chart_4412254edcf447b68e65faab50374114 = echarts.init(\n",
       "            document.getElementById('4412254edcf447b68e65faab50374114'), 'white', {renderer: 'canvas'});\n",
       "        var option_4412254edcf447b68e65faab50374114 = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"pie\",\n",
       "            \"name\": \"\\u72b6\\u6001\\u7801\\u6bd4\\u4f8b\",\n",
       "            \"clockwise\": true,\n",
       "            \"data\": [\n",
       "                {\n",
       "                    \"name\": 200,\n",
       "                    \"value\": 41924\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 201,\n",
       "                    \"value\": 3432\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 206,\n",
       "                    \"value\": 70\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 301,\n",
       "                    \"value\": 2364\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 302,\n",
       "                    \"value\": 23\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 304,\n",
       "                    \"value\": 19\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 400,\n",
       "                    \"value\": 20\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 403,\n",
       "                    \"value\": 92\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 404,\n",
       "                    \"value\": 1474\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 405,\n",
       "                    \"value\": 12\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 444,\n",
       "                    \"value\": 846\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 500,\n",
       "                    \"value\": 1\n",
       "                },\n",
       "                {\n",
       "                    \"name\": 504,\n",
       "                    \"value\": 1\n",
       "                }\n",
       "            ],\n",
       "            \"radius\": [\n",
       "                \"0%\",\n",
       "                \"75%\"\n",
       "            ],\n",
       "            \"center\": [\n",
       "                \"50%\",\n",
       "                \"50%\"\n",
       "            ],\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8,\n",
       "                \"formatter\": \"{b}: {c}\"\n",
       "            },\n",
       "            \"rippleEffect\": {\n",
       "                \"show\": true,\n",
       "                \"brushType\": \"stroke\",\n",
       "                \"scale\": 2.5,\n",
       "                \"period\": 4\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                200,\n",
       "                201,\n",
       "                206,\n",
       "                301,\n",
       "                302,\n",
       "                304,\n",
       "                400,\n",
       "                403,\n",
       "                404,\n",
       "                405,\n",
       "                444,\n",
       "                500,\n",
       "                504\n",
       "            ],\n",
       "            \"selected\": {}\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"item\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"line\"\n",
       "        },\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0\n",
       "    }\n",
       "};\n",
       "        chart_4412254edcf447b68e65faab50374114.setOption(option_4412254edcf447b68e65faab50374114);\n",
       "    });\n",
       "</script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x1a3a836d088>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pie = (\n",
    "        Pie()\n",
    "        .add(\"状态码比例\", list(zip(df_status.index, df_status)))\n",
    "        .set_series_opts(label_opts=opts.LabelOpts(formatter=\"{b}: {c}\"))\n",
    "    )\n",
    "pie.render_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4、实现按小时、按天粒度的流量统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>ip</th>\n",
       "      <th>stime</th>\n",
       "      <th>status</th>\n",
       "      <th>client</th>\n",
       "      <th>is_spider</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>106.11.153.226</td>\n",
       "      <td>[02/Dec/2019:22:40:18</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>42.156.254.60</td>\n",
       "      <td>[02/Dec/2019:22:40:23</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>106.11.159.254</td>\n",
       "      <td>[02/Dec/2019:22:40:27</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>106.11.157.254</td>\n",
       "      <td>[02/Dec/2019:22:40:28</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>42.156.137.109</td>\n",
       "      <td>[02/Dec/2019:22:40:30</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ip                  stime  status  \\\n",
       "0  106.11.153.226  [02/Dec/2019:22:40:18  200      \n",
       "1  42.156.254.60   [02/Dec/2019:22:40:23  201      \n",
       "2  106.11.159.254  [02/Dec/2019:22:40:27  200      \n",
       "3  106.11.157.254  [02/Dec/2019:22:40:28  200      \n",
       "4  42.156.137.109  [02/Dec/2019:22:40:30  201      \n",
       "\n",
       "                                                                                                                              client  \\\n",
       "0  YisouSpider                                                                                                                         \n",
       "1  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36   \n",
       "2  YisouSpider                                                                                                                         \n",
       "3  YisouSpider                                                                                                                         \n",
       "4  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36   \n",
       "\n",
       "   is_spider  \n",
       "0  True       \n",
       "1  True       \n",
       "2  True       \n",
       "3  True       \n",
       "4  True       "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>ip</th>\n",
       "      <th>stime</th>\n",
       "      <th>status</th>\n",
       "      <th>client</th>\n",
       "      <th>is_spider</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>106.11.153.226</td>\n",
       "      <td>2019-12-02 22:40:18</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>42.156.254.60</td>\n",
       "      <td>2019-12-02 22:40:23</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>106.11.159.254</td>\n",
       "      <td>2019-12-02 22:40:27</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>106.11.157.254</td>\n",
       "      <td>2019-12-02 22:40:28</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>42.156.137.109</td>\n",
       "      <td>2019-12-02 22:40:30</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ip               stime  status  \\\n",
       "0  106.11.153.226 2019-12-02 22:40:18  200      \n",
       "1  42.156.254.60  2019-12-02 22:40:23  201      \n",
       "2  106.11.159.254 2019-12-02 22:40:27  200      \n",
       "3  106.11.157.254 2019-12-02 22:40:28  200      \n",
       "4  42.156.137.109 2019-12-02 22:40:30  201      \n",
       "\n",
       "                                                                                                                              client  \\\n",
       "0  YisouSpider                                                                                                                         \n",
       "1  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36   \n",
       "2  YisouSpider                                                                                                                         \n",
       "3  YisouSpider                                                                                                                         \n",
       "4  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36   \n",
       "\n",
       "   is_spider  \n",
       "0  True       \n",
       "1  True       \n",
       "2  True       \n",
       "3  True       \n",
       "4  True       "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"stime\"] = pd.to_datetime(df[\"stime\"].str[1:], format=\"%d/%b/%Y:%H:%M:%S\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>ip</th>\n",
       "      <th>status</th>\n",
       "      <th>client</th>\n",
       "      <th>is_spider</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stime</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2019-12-02 22:40:18</td>\n",
       "      <td>106.11.153.226</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-12-02 22:40:23</td>\n",
       "      <td>42.156.254.60</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-12-02 22:40:27</td>\n",
       "      <td>106.11.159.254</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-12-02 22:40:28</td>\n",
       "      <td>106.11.157.254</td>\n",
       "      <td>200</td>\n",
       "      <td>YisouSpider</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-12-02 22:40:30</td>\n",
       "      <td>42.156.137.109</td>\n",
       "      <td>201</td>\n",
       "      <td>Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 ip  status  \\\n",
       "stime                                         \n",
       "2019-12-02 22:40:18  106.11.153.226  200      \n",
       "2019-12-02 22:40:23  42.156.254.60   201      \n",
       "2019-12-02 22:40:27  106.11.159.254  200      \n",
       "2019-12-02 22:40:28  106.11.157.254  200      \n",
       "2019-12-02 22:40:30  42.156.137.109  201      \n",
       "\n",
       "                                                                                                                                                client  \\\n",
       "stime                                                                                                                                                    \n",
       "2019-12-02 22:40:18  YisouSpider                                                                                                                         \n",
       "2019-12-02 22:40:23  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36   \n",
       "2019-12-02 22:40:27  YisouSpider                                                                                                                         \n",
       "2019-12-02 22:40:28  YisouSpider                                                                                                                         \n",
       "2019-12-02 22:40:30  Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.81 YisouSpider/5.0 Safari/537.36   \n",
       "\n",
       "                     is_spider  \n",
       "stime                           \n",
       "2019-12-02 22:40:18  True       \n",
       "2019-12-02 22:40:23  True       \n",
       "2019-12-02 22:40:27  True       \n",
       "2019-12-02 22:40:28  True       \n",
       "2019-12-02 22:40:30  True       "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index(\"stime\", inplace=True)\n",
    "df.sort_index(inplace=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2019-12-02 22:40:18', '2019-12-02 22:40:23',\n",
       "               '2019-12-02 22:40:27', '2019-12-02 22:40:28',\n",
       "               '2019-12-02 22:40:30', '2019-12-02 22:40:46',\n",
       "               '2019-12-02 22:41:52', '2019-12-02 22:41:52',\n",
       "               '2019-12-02 22:41:55', '2019-12-02 22:42:16',\n",
       "               ...\n",
       "               '2019-12-07 21:30:16', '2019-12-07 21:30:17',\n",
       "               '2019-12-07 21:30:19', '2019-12-07 21:30:20',\n",
       "               '2019-12-07 21:30:21', '2019-12-07 21:30:22',\n",
       "               '2019-12-07 21:30:23', '2019-12-07 21:30:56',\n",
       "               '2019-12-07 21:30:58', '2019-12-07 21:31:02'],\n",
       "              dtype='datetime64[ns]', name='stime', length=50278, freq=None)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>pv</th>\n",
       "      <th>uv</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>stime</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2019-12-02</td>\n",
       "      <td>288</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-12-03</td>\n",
       "      <td>10285</td>\n",
       "      <td>1180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-12-04</td>\n",
       "      <td>13618</td>\n",
       "      <td>1197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-12-05</td>\n",
       "      <td>10485</td>\n",
       "      <td>1152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2019-12-06</td>\n",
       "      <td>9469</td>\n",
       "      <td>1261</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               pv    uv\n",
       "stime                  \n",
       "2019-12-02  288    70  \n",
       "2019-12-03  10285  1180\n",
       "2019-12-04  13618  1197\n",
       "2019-12-05  10485  1152\n",
       "2019-12-06  9469   1261"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 按小时统计\n",
    "#df_pvuv = df.resample(\"H\")[\"ip\"].agg(pv=np.size, uv=pd.Series.nunique)\n",
    "\n",
    "# 按每6个小时统计\n",
    "#df_pvuv = df.resample(\"6H\")[\"ip\"].agg(pv=np.size, uv=pd.Series.nunique)\n",
    "\n",
    "# 按天统计\n",
    "df_pvuv = df.resample(\"D\")[\"ip\"].agg(pv=np.size, uv=pd.Series.nunique)\n",
    "\n",
    "df_pvuv.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'echarts':'https://assets.pyecharts.org/assets/echarts.min'\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n",
       "    <div id=\"3375e16f7e3c45e8a7ca57f0310d594a\" style=\"width:900px; height:500px;\"></div>\n",
       "\n",
       "\n",
       "<script>\n",
       "    require(['echarts'], function(echarts) {\n",
       "        var chart_3375e16f7e3c45e8a7ca57f0310d594a = echarts.init(\n",
       "            document.getElementById('3375e16f7e3c45e8a7ca57f0310d594a'), 'white', {renderer: 'canvas'});\n",
       "        var option_3375e16f7e3c45e8a7ca57f0310d594a = {\n",
       "    \"animation\": true,\n",
       "    \"animationThreshold\": 2000,\n",
       "    \"animationDuration\": 1000,\n",
       "    \"animationEasing\": \"cubicOut\",\n",
       "    \"animationDelay\": 0,\n",
       "    \"animationDurationUpdate\": 300,\n",
       "    \"animationEasingUpdate\": \"cubicOut\",\n",
       "    \"animationDelayUpdate\": 0,\n",
       "    \"color\": [\n",
       "        \"#c23531\",\n",
       "        \"#2f4554\",\n",
       "        \"#61a0a8\",\n",
       "        \"#d48265\",\n",
       "        \"#749f83\",\n",
       "        \"#ca8622\",\n",
       "        \"#bda29a\",\n",
       "        \"#6e7074\",\n",
       "        \"#546570\",\n",
       "        \"#c4ccd3\",\n",
       "        \"#f05b72\",\n",
       "        \"#ef5b9c\",\n",
       "        \"#f47920\",\n",
       "        \"#905a3d\",\n",
       "        \"#fab27b\",\n",
       "        \"#2a5caa\",\n",
       "        \"#444693\",\n",
       "        \"#726930\",\n",
       "        \"#b2d235\",\n",
       "        \"#6d8346\",\n",
       "        \"#ac6767\",\n",
       "        \"#1d953f\",\n",
       "        \"#6950a1\",\n",
       "        \"#918597\"\n",
       "    ],\n",
       "    \"series\": [\n",
       "        {\n",
       "            \"type\": \"line\",\n",
       "            \"name\": \"PV\",\n",
       "            \"connectNulls\": false,\n",
       "            \"symbolSize\": 4,\n",
       "            \"showSymbol\": true,\n",
       "            \"smooth\": false,\n",
       "            \"step\": false,\n",
       "            \"data\": [\n",
       "                [\n",
       "                    \"2019-12-02T00:00:00\",\n",
       "                    288\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-03T00:00:00\",\n",
       "                    10285\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-04T00:00:00\",\n",
       "                    13618\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-05T00:00:00\",\n",
       "                    10485\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-06T00:00:00\",\n",
       "                    9469\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-07T00:00:00\",\n",
       "                    6133\n",
       "                ]\n",
       "            ],\n",
       "            \"hoverAnimation\": true,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"lineStyle\": {\n",
       "                \"width\": 1,\n",
       "                \"opacity\": 1,\n",
       "                \"curveness\": 0,\n",
       "                \"type\": \"solid\"\n",
       "            },\n",
       "            \"areaStyle\": {\n",
       "                \"opacity\": 0\n",
       "            }\n",
       "        },\n",
       "        {\n",
       "            \"type\": \"line\",\n",
       "            \"name\": \"UV\",\n",
       "            \"connectNulls\": false,\n",
       "            \"symbolSize\": 4,\n",
       "            \"showSymbol\": true,\n",
       "            \"smooth\": false,\n",
       "            \"step\": false,\n",
       "            \"data\": [\n",
       "                [\n",
       "                    \"2019-12-02T00:00:00\",\n",
       "                    70\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-03T00:00:00\",\n",
       "                    1180\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-04T00:00:00\",\n",
       "                    1197\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-05T00:00:00\",\n",
       "                    1152\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-06T00:00:00\",\n",
       "                    1261\n",
       "                ],\n",
       "                [\n",
       "                    \"2019-12-07T00:00:00\",\n",
       "                    911\n",
       "                ]\n",
       "            ],\n",
       "            \"hoverAnimation\": true,\n",
       "            \"label\": {\n",
       "                \"show\": true,\n",
       "                \"position\": \"top\",\n",
       "                \"margin\": 8\n",
       "            },\n",
       "            \"lineStyle\": {\n",
       "                \"width\": 1,\n",
       "                \"opacity\": 1,\n",
       "                \"curveness\": 0,\n",
       "                \"type\": \"solid\"\n",
       "            },\n",
       "            \"areaStyle\": {\n",
       "                \"opacity\": 0\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"legend\": [\n",
       "        {\n",
       "            \"data\": [\n",
       "                \"PV\",\n",
       "                \"UV\"\n",
       "            ],\n",
       "            \"selected\": {\n",
       "                \"PV\": true,\n",
       "                \"UV\": true\n",
       "            },\n",
       "            \"show\": true\n",
       "        }\n",
       "    ],\n",
       "    \"tooltip\": {\n",
       "        \"show\": true,\n",
       "        \"trigger\": \"axis\",\n",
       "        \"triggerOn\": \"mousemove|click\",\n",
       "        \"axisPointer\": {\n",
       "            \"type\": \"cross\"\n",
       "        },\n",
       "        \"textStyle\": {\n",
       "            \"fontSize\": 14\n",
       "        },\n",
       "        \"borderWidth\": 0\n",
       "    },\n",
       "    \"xAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            },\n",
       "            \"data\": [\n",
       "                \"2019-12-02T00:00:00\",\n",
       "                \"2019-12-03T00:00:00\",\n",
       "                \"2019-12-04T00:00:00\",\n",
       "                \"2019-12-05T00:00:00\",\n",
       "                \"2019-12-06T00:00:00\",\n",
       "                \"2019-12-07T00:00:00\"\n",
       "            ]\n",
       "        }\n",
       "    ],\n",
       "    \"yAxis\": [\n",
       "        {\n",
       "            \"show\": true,\n",
       "            \"scale\": false,\n",
       "            \"nameLocation\": \"end\",\n",
       "            \"nameGap\": 15,\n",
       "            \"gridIndex\": 0,\n",
       "            \"inverse\": false,\n",
       "            \"offset\": 0,\n",
       "            \"splitNumber\": 5,\n",
       "            \"minInterval\": 0,\n",
       "            \"splitLine\": {\n",
       "                \"show\": false,\n",
       "                \"lineStyle\": {\n",
       "                    \"width\": 1,\n",
       "                    \"opacity\": 1,\n",
       "                    \"curveness\": 0,\n",
       "                    \"type\": \"solid\"\n",
       "                }\n",
       "            }\n",
       "        }\n",
       "    ],\n",
       "    \"title\": [\n",
       "        {\n",
       "            \"text\": \"PVUV\\u6570\\u636e\\u5bf9\\u6bd4\"\n",
       "        }\n",
       "    ]\n",
       "};\n",
       "        chart_3375e16f7e3c45e8a7ca57f0310d594a.setOption(option_3375e16f7e3c45e8a7ca57f0310d594a);\n",
       "    });\n",
       "</script>\n"
      ],
      "text/plain": [
       "<pyecharts.render.display.HTML at 0x1a3a8973f48>"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "line = (\n",
    "        Line()\n",
    "        .add_xaxis(df_pvuv.index.to_list())\n",
    "        .add_yaxis(\"PV\", df_pvuv[\"pv\"].to_list())\n",
    "        .add_yaxis(\"UV\", df_pvuv[\"uv\"].to_list())\n",
    "        .set_global_opts(\n",
    "            title_opts=opts.TitleOpts(title=\"PVUV数据对比\"),\n",
    "            tooltip_opts=opts.TooltipOpts(trigger=\"axis\", axis_pointer_type=\"cross\")\n",
    "        )\n",
    "    )\n",
    "line.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}