Z2u3XHci

1import csv
2import datetime
3import sqlite3
4import os
5
6def date_key(row):
7	return datetime.datetime.strptime(row[0].strip(), "%Y/%m/%d %H:%M:%S")
8
9def read_data_csv(filename):
10	excel_data = []
11	for row in csv.reader(open(filename)):
12		excel_data.append(row)
13	table_headers = excel_data.pop(0) ### removing headers
14	excel_data.sort(key=date_key)
15	return excel_data
16
17
18def create_db_with_dict(data_key,data_values):
19	#data_key = data_keys.replace('-','_') ### Remove special charecters
20	#import pdb; pdb.set_trace()
21	print "="*20
22	print data_key+"    :"+str(len(data_values))+" Records"
23	conn = sqlite3.connect(data_key)
24	c = conn.cursor()
25	c.executescript("DROP TABLE IF EXISTS "+data_key+"; CREATE TABLE "+data_key+" (id INTEGER PRIMARY KEY AUTOINCREMENT,date_time TEXT, applianceName TEXT, tenantName TEXT, localAccCktId INTEGER, localAccCktName TEXT, remoteAccCktId INTEGER, remoteAccCktName TEXT, localSiteId INTEGER, localSiteName TEXT, remoteSiteId INTEGER, remoteSiteName TEXT, fwdClass TEXT, applianceId INTEGER, tenantId INTEGER, delay INTEGER, fwdDelayVar INTEGER, revDelayVar INTEGER, fwdLoss INTEGER, revLoss INTEGER, fwdLossRatio INTEGER, revLossRatio INTEGER, pduLossRatio INTEGER)")
26	conn.commit()
27	for row1 in data_values:
28		row = [w.replace('/', '-') for w in row1]
29		c.execute("INSERT INTO "+data_key+" (date_time, applianceName, tenantName, localAccCktId, localAccCktName, remoteAccCktId, remoteAccCktName, localSiteId, localSiteName, remoteSiteId, remoteSiteName, fwdClass, applianceId, tenantId, delay, fwdDelayVar, revDelayVar, fwdLoss, revLoss, fwdLossRatio, revLossRatio, pduLossRatio) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", row)
30		conn.commit()
31	conn.close()
32
33
34
35
36def get_new_aggr(db_name,time_in_min):
37	conn = sqlite3.connect(db_name)
38	c = conn.cursor()
39	last_id = 0	
40	c.execute("select max(id),min(date_time) from "+db_name+";")
41	total_datas = c.fetchall()
42	total_max_id = total_datas[0][0]
43	while last_id != total_max_id:
44		c.execute("select min(id), max(id) from "+db_name+" where date_time between (select min(date_time) from "+db_name+" where id > "+str(last_id)+") and (select datetime((select min(date_time) from "+db_name+" where id > "+str(last_id)+"), +'"+str(time_in_min)+" minutes'));")
45		id_datas = c.fetchall()
46		print id_datas
47		last_id = id_datas[0][1]
48		#total_max_id = id_datas[0][1]
49		print "Ratio     :: "+str(id_datas[0][0])+":"+str(id_datas[0][1])
50		#import pdb; pdb.set_trace()
51		c.execute("select date_time from "+db_name+" where id >= "+str(id_datas[0][0])+" and id <= "+str(id_datas[0][1])+";")
52		timestamp_list = []
53		for row in c:
54			timestamp_list.append(row)
55		avg_time = get_avg_times(timestamp_list)
56		print "Average_Time   :"+str(avg_time)
57		c.execute("select avg(delay),avg(fwdDelayVar),avg(revDelayVar),avg(fwdLoss),avg(revLoss),avg(fwdLossRatio),avg(revLossRatio),avg(pduLossRatio) from "+db_name+" where id >= "+str(id_datas[0][0])+" and id <= "+str(id_datas[0][1])+";")
58		avg_datas = c.fetchall()
59		write_to_csv_data = []
60		all_dates = []
61		timestamp_list1 = [i[0] for i in timestamp_list]
62		coll_dates = '__'.join(str(x) for x in timestamp_list1)
63		tm = list(avg_datas[0])
64		tm.insert(0,coll_dates)
65		tm.insert(1,avg_time)
66		tm.insert(2,db_name)
67		write_datas_to_csv('avg_analytics.csv',tm)
68		if total_max_id == last_id:
69			break
70	conn.close()
71	cleanup(db_name)
72
73
74
75
76def get_unique_vcpe(data_list):
77	app = []
78	for a in data_list:
79		mm = a[10]
80		if mm not in app:
81			app.append(mm)
82	app = [v for v in app if "controller" not in v ]
83	return app
84
85
86
87def get_data_per_cpe(cpe_name,data_list):
88	data_by_cpe = []
89	for items in data_list:
90		if cpe_name in items:
91			data_by_cpe.append(items)
92	if len(data_by_cpe)%3 != 0:
93		print "exception on records"
94	return data_by_cpe
95
96
97def write_headers_to_csv(filename):
98	headers = ["Date","Avg-Time","appliance_name","AVG-delay","AVG-fwdDelayVar","AVG-RevDelayVar","AVG-fwdLoss","AVG-revLoss","AVG-fwdLossRatio","AVG-revLossRatio","AVG-pduLossRatio"]
99	mp = open(filename,'a')
100	wr1 = csv.writer(mp, dialect='excel')
101	wr1.writerow(headers)
102	mp.close()
103
104
105
106def write_datas_to_csv(filename,datas):
107	print datas
108	#print dates
109	mp = open(filename,'a')
110	wr = csv.writer(mp, dialect='excel')
111	wr.writerow(datas)
112	items = []
113	mp.close()
114
115
116def cleanup(file):
117	os.system('rm -rf '+file)
118
119
120def get_avg_times(times_list):
121	only_times = []
122	#print times_list
123	for date_times in times_list:
124		coll_time = str(date_times).split(' ')
125		coll_times = coll_time[1].replace("',)", ' ')
126		#import pdb; pdb.set_trace()
127		only_times.append(coll_times)
128	#print only_times
129	return str(datetime.timedelta(seconds=sum(map(lambda f: int(f[0])*3600 + int(f[1])*60 + int(f[2]), map(lambda f: f.split(':'), only_times)))/len(only_times)))
130
131
132tm = read_data_csv('AnalyticsData_15122016T18.csv')
133tc = get_unique_vcpe(tm)
134cpe_data_dict = {}
135
136for items in tc:
137 	li = get_data_per_cpe(items,tm)
138 	cpe_data_dict[items] = li
139
140
141cleanup('avg_analytics.csv')
142
143write_headers_to_csv('avg_analytics.csv')
144for k,v in cpe_data_dict.iteritems():
145	k = k.replace('-','_')
146	create_db_with_dict(k,v)
147	get_new_aggr(k,'15')