In [2]:
import numpy as np
from pandas import *
import json
import sqlite3
from pandas import DataFrame, Series
import pandas as pd
In [3]:
path="D:/DATA/kampret.db"
In [4]:
con = sqlite3.connect(path)
In [5]:
data = pandas.read_sql("select * from data",con)
data.head()
Out[5]:
_id name timestamp value
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...
In [6]:
df = pandas.read_sql("select value from data where name='SmsProbe'",con)
In [7]:
df.head()
Out[7]:
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...
In [8]:
a = df['value']
In [9]:
ajson = a[0:10]
In [10]:
type(ajson)
Out[10]:
pandas.core.series.Series
In [11]:
records = [json.loads(line) for line in ajson]
In [12]:
records
Out[12]:
[{u'address': u'15444302',
  u'body': u'{"ONE_WAY_HASH":"90905ac2fe272168b1488ae1b3bf6bbffeb7511"}',
  u'body-byte-len': 101,
  u'body-token-byte-len': u'18-16-9-11-3-9-9-19-',
  u'body-token-count': 8,
  u'date': 1403883215000L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'service_center': u'+821020911467',
  u'status': -1,
  u'thread_id': 215,
  u'timestamp': 1403883215.0,
  u'type': 1},
 {u'address': u'15444302',
  u'body': u'{"ONE_WAY_HASH":"f78c775ae73d0fb5da596180a699f29ff1f85182"}',
  u'body-byte-len': 48,
  u'body-token-byte-len': u'14-15-6-10-',
  u'body-token-count': 4,
  u'date': 1403883087000L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'service_center': u'+821020911467',
  u'status': -1,
  u'thread_id': 215,
  u'timestamp': 1403883087.0,
  u'type': 1},
 {u'address': u'01066296348',
  u'body': u'{"ONE_WAY_HASH":"132d98dc8c0f0ba2e359bb5a1c7f036ccd15929c"}',
  u'body-byte-len': 136,
  u'body-token-byte-len': u'9-3-9-10-12-15-6-12-9-12-9-6-12-',
  u'body-token-count': 13,
  u'date': 1403772474744L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'status': -1,
  u'thread_id': 110,
  u'timestamp': 1403772474.744,
  u'type': 2},
 {u'address': u'0220338500',
  u'body': u'{"ONE_WAY_HASH":"4dfc166fec3dc5cf83daa87d859720e6faaf6223"}',
  u'body-byte-len': 98,
  u'body-token-byte-len': u'28-21-6-10-9-19-',
  u'body-token-count': 6,
  u'date': 1403697660000L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'service_center': u'+821020911467',
  u'status': -1,
  u'thread_id': 63,
  u'timestamp': 1403697660.0,
  u'type': 1},
 {u'address': u'0625101111',
  u'body': u'{"ONE_WAY_HASH":"fe566be68dea135669a2efaf136b2b18c1fc7bcb"}',
  u'body-byte-len': 45,
  u'body-token-byte-len': u'10-25-1-6-',
  u'body-token-count': 4,
  u'date': 1403697242000L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'service_center': u'+821020911467',
  u'status': -1,
  u'thread_id': 214,
  u'timestamp': 1403697242.0,
  u'type': 1},
 {u'address': u'0622395000',
  u'body': u'{"ONE_WAY_HASH":"24b4f978833c3628ba178d83407ba5ba568f11f3"}',
  u'body-byte-len': 90,
  u'body-token-byte-len': u'24-10-8-6-6-5-5-6-',
  u'body-token-count': 8,
  u'date': 1403609220000L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'service_center': u'+821020911467',
  u'status': -1,
  u'thread_id': 15,
  u'timestamp': 1403609220.0,
  u'type': 1},
 {u'address': u'01029738808',
  u'body': u'{"ONE_WAY_HASH":"2e3635efbbd1e4bc0d1774b8ecd47f98ebdc7c99"}',
  u'body-byte-len': 9,
  u'body-token-byte-len': u'9-',
  u'body-token-count': 1,
  u'date': 1403540566460L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'status': -1,
  u'thread_id': 79,
  u'timestamp': 1403540566.46,
  u'type': 2},
 {u'address': u'01086198051',
  u'body': u'{"ONE_WAY_HASH":"a892ae934f68792b7f66d03a28af5be0c88870f9"}',
  u'body-byte-len': 143,
  u'body-token-byte-len': u'62-80-',
  u'body-token-count': 2,
  u'date': 1403540204981L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'status': -1,
  u'thread_id': 155,
  u'timestamp': 1403540204.981,
  u'type': 2},
 {u'address': u'01086198051',
  u'body': u'{"ONE_WAY_HASH":"6a6afffe36b4495715260c990ad5c88f7fe429f7"}',
  u'body-byte-len': 95,
  u'body-token-byte-len': u'19-30-30-13-',
  u'body-token-count': 4,
  u'date': 1403540164000L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'service_center': u'+821020911467',
  u'status': -1,
  u'thread_id': 155,
  u'timestamp': 1403540164.0,
  u'type': 1},
 {u'address': u'01086198051',
  u'body': u'{"ONE_WAY_HASH":"870a34da45b327927fe51c1e0b0ba74476fc52a4"}',
  u'body-byte-len': 93,
  u'body-token-byte-len': u'54-18-19-',
  u'body-token-count': 3,
  u'date': 1403539805274L,
  u'locked': False,
  u'protocol': 0,
  u'read': True,
  u'reply_path_present': False,
  u'status': -1,
  u'thread_id': 155,
  u'timestamp': 1403539805.274,
  u'type': 2}]
In [13]:
timestamp = [asu['timestamp'] for asu in records if 'timestamp' in asu]
type1 = [asu['type'] for asu in records if 'type' in asu]
status = [asu['status'] for asu in records if 'status' in asu]
In [14]:
dataku = [timestamp,type1,status]
In [15]:
dfall = pd.DataFrame(dataku)
In [16]:
dfall
Out[16]:
0 1 2 3 4 5 6 7 8 9
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
In [17]:
dfnew = dfall.T
In [26]:
dfnew.rename(columns={0:'timestamp',1:'type',2:'status'})
Out[26]:
timestamp type status
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