{ "metadata": { "name": "", "signature": "sha256:c269b37473ecc06839518883466dafaf68314693032917c9cdb3099d9cdd8842" }, "nbformat": 3, "nbformat_minor": 0, "worksheets": [ { "cells": [ { "cell_type": "code", "collapsed": false, "input": [ "import numpy as np\n", "from pandas import *\n", "import json\n", "import sqlite3\n", "from pandas import DataFrame, Series\n", "import pandas as pd" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 2 }, { "cell_type": "code", "collapsed": false, "input": [ "path=\"D:/DATA/kampret.db\"" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 3 }, { "cell_type": "code", "collapsed": false, "input": [ "con = sqlite3.connect(path)" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 4 }, { "cell_type": "code", "collapsed": false, "input": [ "data = pandas.read_sql(\"select * from data\",con)\n", "data.head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
_idnametimestampvalue
0 1 HardwareInfoProbe 1.404117e+09 {\"androidId\":\"df03d9eae60fecb3\",\"bluetoothMac\"...
1 2 SmsProbe 1.403883e+09 {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\...
2 3 SmsProbe 1.403883e+09 {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\...
3 4 CallLogProbe 1.404112e+09 {\"_id\":4108,\"date\":1404112293842,\"duration\":29...
4 5 SmsProbe 1.403772e+09 {\"address\":\"01066296348\",\"body\":\"{\\\"ONE_WAY_HA...
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 5, "text": [ " _id name timestamp \\\n", "0 1 HardwareInfoProbe 1.404117e+09 \n", "1 2 SmsProbe 1.403883e+09 \n", "2 3 SmsProbe 1.403883e+09 \n", "3 4 CallLogProbe 1.404112e+09 \n", "4 5 SmsProbe 1.403772e+09 \n", "\n", " value \n", "0 {\"androidId\":\"df03d9eae60fecb3\",\"bluetoothMac\"... \n", "1 {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\... \n", "2 {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\... \n", "3 {\"_id\":4108,\"date\":1404112293842,\"duration\":29... \n", "4 {\"address\":\"01066296348\",\"body\":\"{\\\"ONE_WAY_HA... " ] } ], "prompt_number": 5 }, { "cell_type": "code", "collapsed": false, "input": [ "df = pandas.read_sql(\"select value from data where name='SmsProbe'\",con)" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 6 }, { "cell_type": "code", "collapsed": false, "input": [ "df.head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
value
0 {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\...
1 {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\...
2 {\"address\":\"01066296348\",\"body\":\"{\\\"ONE_WAY_HA...
3 {\"address\":\"0220338500\",\"body\":\"{\\\"ONE_WAY_HAS...
4 {\"address\":\"0625101111\",\"body\":\"{\\\"ONE_WAY_HAS...
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 7, "text": [ " value\n", "0 {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\...\n", "1 {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\...\n", "2 {\"address\":\"01066296348\",\"body\":\"{\\\"ONE_WAY_HA...\n", "3 {\"address\":\"0220338500\",\"body\":\"{\\\"ONE_WAY_HAS...\n", "4 {\"address\":\"0625101111\",\"body\":\"{\\\"ONE_WAY_HAS..." ] } ], "prompt_number": 7 }, { "cell_type": "code", "collapsed": false, "input": [ "a = df['value']" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 8 }, { "cell_type": "code", "collapsed": false, "input": [ "ajson = a[0:10]" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 9 }, { "cell_type": "code", "collapsed": false, "input": [ "type(ajson)" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 10, "text": [ "pandas.core.series.Series" ] } ], "prompt_number": 10 }, { "cell_type": "code", "collapsed": false, "input": [ "records = [json.loads(line) for line in ajson]" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 11 }, { "cell_type": "code", "collapsed": false, "input": [ "records" ], "language": "python", "metadata": {}, "outputs": [ { "metadata": {}, "output_type": "pyout", "prompt_number": 12, "text": [ "[{u'address': u'15444302',\n", " u'body': u'{\"ONE_WAY_HASH\":\"90905ac2fe272168b1488ae1b3bf6bbffeb7511\"}',\n", " u'body-byte-len': 101,\n", " u'body-token-byte-len': u'18-16-9-11-3-9-9-19-',\n", " u'body-token-count': 8,\n", " u'date': 1403883215000L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'service_center': u'+821020911467',\n", " u'status': -1,\n", " u'thread_id': 215,\n", " u'timestamp': 1403883215.0,\n", " u'type': 1},\n", " {u'address': u'15444302',\n", " u'body': u'{\"ONE_WAY_HASH\":\"f78c775ae73d0fb5da596180a699f29ff1f85182\"}',\n", " u'body-byte-len': 48,\n", " u'body-token-byte-len': u'14-15-6-10-',\n", " u'body-token-count': 4,\n", " u'date': 1403883087000L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'service_center': u'+821020911467',\n", " u'status': -1,\n", " u'thread_id': 215,\n", " u'timestamp': 1403883087.0,\n", " u'type': 1},\n", " {u'address': u'01066296348',\n", " u'body': u'{\"ONE_WAY_HASH\":\"132d98dc8c0f0ba2e359bb5a1c7f036ccd15929c\"}',\n", " u'body-byte-len': 136,\n", " u'body-token-byte-len': u'9-3-9-10-12-15-6-12-9-12-9-6-12-',\n", " u'body-token-count': 13,\n", " u'date': 1403772474744L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'status': -1,\n", " u'thread_id': 110,\n", " u'timestamp': 1403772474.744,\n", " u'type': 2},\n", " {u'address': u'0220338500',\n", " u'body': u'{\"ONE_WAY_HASH\":\"4dfc166fec3dc5cf83daa87d859720e6faaf6223\"}',\n", " u'body-byte-len': 98,\n", " u'body-token-byte-len': u'28-21-6-10-9-19-',\n", " u'body-token-count': 6,\n", " u'date': 1403697660000L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'service_center': u'+821020911467',\n", " u'status': -1,\n", " u'thread_id': 63,\n", " u'timestamp': 1403697660.0,\n", " u'type': 1},\n", " {u'address': u'0625101111',\n", " u'body': u'{\"ONE_WAY_HASH\":\"fe566be68dea135669a2efaf136b2b18c1fc7bcb\"}',\n", " u'body-byte-len': 45,\n", " u'body-token-byte-len': u'10-25-1-6-',\n", " u'body-token-count': 4,\n", " u'date': 1403697242000L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'service_center': u'+821020911467',\n", " u'status': -1,\n", " u'thread_id': 214,\n", " u'timestamp': 1403697242.0,\n", " u'type': 1},\n", " {u'address': u'0622395000',\n", " u'body': u'{\"ONE_WAY_HASH\":\"24b4f978833c3628ba178d83407ba5ba568f11f3\"}',\n", " u'body-byte-len': 90,\n", " u'body-token-byte-len': u'24-10-8-6-6-5-5-6-',\n", " u'body-token-count': 8,\n", " u'date': 1403609220000L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'service_center': u'+821020911467',\n", " u'status': -1,\n", " u'thread_id': 15,\n", " u'timestamp': 1403609220.0,\n", " u'type': 1},\n", " {u'address': u'01029738808',\n", " u'body': u'{\"ONE_WAY_HASH\":\"2e3635efbbd1e4bc0d1774b8ecd47f98ebdc7c99\"}',\n", " u'body-byte-len': 9,\n", " u'body-token-byte-len': u'9-',\n", " u'body-token-count': 1,\n", " u'date': 1403540566460L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'status': -1,\n", " u'thread_id': 79,\n", " u'timestamp': 1403540566.46,\n", " u'type': 2},\n", " {u'address': u'01086198051',\n", " u'body': u'{\"ONE_WAY_HASH\":\"a892ae934f68792b7f66d03a28af5be0c88870f9\"}',\n", " u'body-byte-len': 143,\n", " u'body-token-byte-len': u'62-80-',\n", " u'body-token-count': 2,\n", " u'date': 1403540204981L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'status': -1,\n", " u'thread_id': 155,\n", " u'timestamp': 1403540204.981,\n", " u'type': 2},\n", " {u'address': u'01086198051',\n", " u'body': u'{\"ONE_WAY_HASH\":\"6a6afffe36b4495715260c990ad5c88f7fe429f7\"}',\n", " u'body-byte-len': 95,\n", " u'body-token-byte-len': u'19-30-30-13-',\n", " u'body-token-count': 4,\n", " u'date': 1403540164000L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'service_center': u'+821020911467',\n", " u'status': -1,\n", " u'thread_id': 155,\n", " u'timestamp': 1403540164.0,\n", " u'type': 1},\n", " {u'address': u'01086198051',\n", " u'body': u'{\"ONE_WAY_HASH\":\"870a34da45b327927fe51c1e0b0ba74476fc52a4\"}',\n", " u'body-byte-len': 93,\n", " u'body-token-byte-len': u'54-18-19-',\n", " u'body-token-count': 3,\n", " u'date': 1403539805274L,\n", " u'locked': False,\n", " u'protocol': 0,\n", " u'read': True,\n", " u'reply_path_present': False,\n", " u'status': -1,\n", " u'thread_id': 155,\n", " u'timestamp': 1403539805.274,\n", " u'type': 2}]" ] } ], "prompt_number": 12 }, { "cell_type": "code", "collapsed": false, "input": [ "timestamp = [asu['timestamp'] for asu in records if 'timestamp' in asu]\n", "type1 = [asu['type'] for asu in records if 'type' in asu]\n", "status = [asu['status'] for asu in records if 'status' in asu]" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 13 }, { "cell_type": "code", "collapsed": false, "input": [ "dataku = [timestamp,type1,status]\n" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 14 }, { "cell_type": "code", "collapsed": false, "input": [ "dfall = pd.DataFrame(dataku)" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 15 }, { "cell_type": "code", "collapsed": false, "input": [ "dfall" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
0123456789
0 1403883215 1403883087 1.403772e+09 1403697660 1403697242 1403609220 1.403541e+09 1.403540e+09 1403540164 1.403540e+09
1 1 1 2.000000e+00 1 1 1 2.000000e+00 2.000000e+00 1 2.000000e+00
2 -1 -1-1.000000e+00 -1 -1 -1-1.000000e+00-1.000000e+00 -1-1.000000e+00
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 16, "text": [ " 0 1 2 3 4 5 \\\n", "0 1403883215 1403883087 1.403772e+09 1403697660 1403697242 1403609220 \n", "1 1 1 2.000000e+00 1 1 1 \n", "2 -1 -1 -1.000000e+00 -1 -1 -1 \n", "\n", " 6 7 8 9 \n", "0 1.403541e+09 1.403540e+09 1403540164 1.403540e+09 \n", "1 2.000000e+00 2.000000e+00 1 2.000000e+00 \n", "2 -1.000000e+00 -1.000000e+00 -1 -1.000000e+00 " ] } ], "prompt_number": 16 }, { "cell_type": "code", "collapsed": false, "input": [ "dfnew = dfall.T" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 17 }, { "cell_type": "code", "collapsed": false, "input": [ "dfnew.rename(columns={0:'timestamp',1:'type',2:'status'})" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
timestamptypestatus
0 1.403883e+09 1-1
1 1.403883e+09 1-1
2 1.403772e+09 2-1
3 1.403698e+09 1-1
4 1.403697e+09 1-1
5 1.403609e+09 1-1
6 1.403541e+09 2-1
7 1.403540e+09 2-1
8 1.403540e+09 1-1
9 1.403540e+09 2-1
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 26, "text": [ " timestamp type status\n", "0 1.403883e+09 1 -1\n", "1 1.403883e+09 1 -1\n", "2 1.403772e+09 2 -1\n", "3 1.403698e+09 1 -1\n", "4 1.403697e+09 1 -1\n", "5 1.403609e+09 1 -1\n", "6 1.403541e+09 2 -1\n", "7 1.403540e+09 2 -1\n", "8 1.403540e+09 1 -1\n", "9 1.403540e+09 2 -1" ] } ], "prompt_number": 26 } ], "metadata": {} } ] }