{
"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",
" _id | \n",
" name | \n",
" timestamp | \n",
" value | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1 | \n",
" HardwareInfoProbe | \n",
" 1.404117e+09 | \n",
" {\"androidId\":\"df03d9eae60fecb3\",\"bluetoothMac\"... | \n",
"
\n",
" \n",
" 1 | \n",
" 2 | \n",
" SmsProbe | \n",
" 1.403883e+09 | \n",
" {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\... | \n",
"
\n",
" \n",
" 2 | \n",
" 3 | \n",
" SmsProbe | \n",
" 1.403883e+09 | \n",
" {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\... | \n",
"
\n",
" \n",
" 3 | \n",
" 4 | \n",
" CallLogProbe | \n",
" 1.404112e+09 | \n",
" {\"_id\":4108,\"date\":1404112293842,\"duration\":29... | \n",
"
\n",
" \n",
" 4 | \n",
" 5 | \n",
" SmsProbe | \n",
" 1.403772e+09 | \n",
" {\"address\":\"01066296348\",\"body\":\"{\\\"ONE_WAY_HA... | \n",
"
\n",
" \n",
"
\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",
" value | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\... | \n",
"
\n",
" \n",
" 1 | \n",
" {\"address\":\"15444302\",\"body\":\"{\\\"ONE_WAY_HASH\\... | \n",
"
\n",
" \n",
" 2 | \n",
" {\"address\":\"01066296348\",\"body\":\"{\\\"ONE_WAY_HA... | \n",
"
\n",
" \n",
" 3 | \n",
" {\"address\":\"0220338500\",\"body\":\"{\\\"ONE_WAY_HAS... | \n",
"
\n",
" \n",
" 4 | \n",
" {\"address\":\"0625101111\",\"body\":\"{\\\"ONE_WAY_HAS... | \n",
"
\n",
" \n",
"
\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",
" 0 | \n",
" 1 | \n",
" 2 | \n",
" 3 | \n",
" 4 | \n",
" 5 | \n",
" 6 | \n",
" 7 | \n",
" 8 | \n",
" 9 | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1403883215 | \n",
" 1403883087 | \n",
" 1.403772e+09 | \n",
" 1403697660 | \n",
" 1403697242 | \n",
" 1403609220 | \n",
" 1.403541e+09 | \n",
" 1.403540e+09 | \n",
" 1403540164 | \n",
" 1.403540e+09 | \n",
"
\n",
" \n",
" 1 | \n",
" 1 | \n",
" 1 | \n",
" 2.000000e+00 | \n",
" 1 | \n",
" 1 | \n",
" 1 | \n",
" 2.000000e+00 | \n",
" 2.000000e+00 | \n",
" 1 | \n",
" 2.000000e+00 | \n",
"
\n",
" \n",
" 2 | \n",
" -1 | \n",
" -1 | \n",
" -1.000000e+00 | \n",
" -1 | \n",
" -1 | \n",
" -1 | \n",
" -1.000000e+00 | \n",
" -1.000000e+00 | \n",
" -1 | \n",
" -1.000000e+00 | \n",
"
\n",
" \n",
"
\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",
" timestamp | \n",
" type | \n",
" status | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 1.403883e+09 | \n",
" 1 | \n",
" -1 | \n",
"
\n",
" \n",
" 1 | \n",
" 1.403883e+09 | \n",
" 1 | \n",
" -1 | \n",
"
\n",
" \n",
" 2 | \n",
" 1.403772e+09 | \n",
" 2 | \n",
" -1 | \n",
"
\n",
" \n",
" 3 | \n",
" 1.403698e+09 | \n",
" 1 | \n",
" -1 | \n",
"
\n",
" \n",
" 4 | \n",
" 1.403697e+09 | \n",
" 1 | \n",
" -1 | \n",
"
\n",
" \n",
" 5 | \n",
" 1.403609e+09 | \n",
" 1 | \n",
" -1 | \n",
"
\n",
" \n",
" 6 | \n",
" 1.403541e+09 | \n",
" 2 | \n",
" -1 | \n",
"
\n",
" \n",
" 7 | \n",
" 1.403540e+09 | \n",
" 2 | \n",
" -1 | \n",
"
\n",
" \n",
" 8 | \n",
" 1.403540e+09 | \n",
" 1 | \n",
" -1 | \n",
"
\n",
" \n",
" 9 | \n",
" 1.403540e+09 | \n",
" 2 | \n",
" -1 | \n",
"
\n",
" \n",
"
\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": {}
}
]
}