PV and UV .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['2016-03-26 00', 17]]"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import pymysql\n",
    "from datetime import datetime\n",
    "\n",
    "\n",
    "def act_log_data():\n",
    "    conn = pymysql.connect(host='***',port=***,user='***',\n",
    "                           passwd='*****',db='***',charset='utf8')\n",
    "    cur = conn.cursor(pymysql.cursors.SSCursor)\n",
    "    cur.execute('''select * from * where time BETWEEN '1458921600' and '1458921604'\n",
    "                ''')#1459094399\n",
    "    act_data = [[row[8], row[10], row[2], row[3]] for row in cur]\n",
    "    df = pd.DataFrame(act_data, columns=['uuid', 'uid', 'url', 'date'])\n",
    "    return df\n",
    "\n",
    "\n",
    "def pv(df):\n",
    "    grouped = df.groupby(['date', 'url'])\n",
    "    pv_d = {}\n",
    "    for k, v in grouped:\n",
    "        pv_d.setdefault(k[0],{})[k[1]] = len(v['uuid'].tolist())\n",
    "            \n",
    "    pv = [[datetime.fromtimestamp(date).strftime('%Y-%m-%d %H'), \n",
    "               pv_d[date][k]] for date in pv_d \n",
    "                                  for k in pv_d[date]]\n",
    "    df = pd.DataFrame(pv, columns = ['date', 'pv'])\n",
    "    return df\n",
    "\n",
    "\n",
    "def uv(df):\n",
    "    grouped = df.groupby(['date', 'url'])\n",
    "    uv_d = {}\n",
    "    for k, v in grouped:\n",
    "        if v['uid'].tolist() == ['']:\n",
    "            continue\n",
    "        uv_d.setdefault(k[0],{})[k[1]] = len(v['uid'].tolist())\n",
    "            \n",
    "    uv = [[datetime.fromtimestamp(date).strftime('%Y-%m-%d %H'), \n",
    "               uv_d[date][k]] for date in uv_d \n",
    "                                  for k in uv_d[date]]\n",
    "    df = pd.DataFrame(uv, columns = ['date', 'uv'])\n",
    "    return df\n",
    "\n",
    "\n",
    "def time_pv(df_pv):\n",
    "    grouped = df_pv.groupby('date')\n",
    "    time_pv = [[k, v['pv'].sum()] for k, v in grouped]\n",
    "    return time_pv\n",
    "\n",
    "\n",
    "def time_uv(df_uv):\n",
    "    grouped = df_uv.groupby('date')\n",
    "    time_uv = [[k, v['uv'].sum()] for k, v in grouped]\n",
    "    return time_uv\n",
    "\n",
    "\n",
    "#time_pv(pv(act_log_data()))\n",
    "time_uv(uv(act_log_data()))"
   ]
  }
 ],
 "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.4.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}