-- coding: utf-8 --
import os
import datetime
import pymysql
import matplotlib.pyplot as plt
from pylab import mpl
import time
import xlwt
import xlrd
import threading
import pandas as pd
import glob
from matplotlib.font_manager import FontProperties
import sys
import platform
from shutil import copy
from concurrent.futures import ThreadPoolExecutor
from concurrent.futures import as_completed
from DBUtils.PooledDB import PooledDB
from future.types import no
def parse_datetime(s):
try:
if s.find(' ') != -1:
return datetime.datetime.strptime(s, "%Y-%m-%d %H:%M:%S")
else:
return datetime.datetime.strptime(s, "%Y-%m-%d")
except:
print ("string '%s' is not a valid date or datetime")
def datetime_toString(dt):
return dt.strftime("%Y-%m-%d %H:%M:%S")
def datetime_toTimestamp(dt):
return time.mktime(dt.timetuple())
def timestamp_toString(sp):
return time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(sp))
POOL = PooledDB(
creator=pymysql, # 使用链接数据库的模块
maxconnections=11, # 连接池允许的最大连接数，0和None表示不限制连接数
mincached=3, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
maxcached=6, # 链接池中最多闲置的链接，0和None不限制
maxshared=4, # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制
setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
ping=0,
# ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
host='',
port=,
user='',
password='',
database='',
charset='utf8'
)
def execute_sql(sql_cmd):
conn = POOL.connection()
cursor = conn.cursor()
cursor.execute(sql_cmd)
results = cursor.fetchall()
conn.close()
db = pymysql.connect(host='61.164.208.174',user='root',passwd='root',db='db_rcld_zj_statistics',port=8116,charset='utf8')
cursor = db.cursor()
cursor.execute(sql_cmd)
results = cursor.fetchall()
db.close()
return results
通过日期区间和渔船设备号查找数据
def get_signal_info_by_device_no(from_date, to_date,device_no):
print ("Partition ship data by device_no")
process_day = from_date
signal_change_points= []
data_time_temp1 = 0
data_time_temp2 = 0
signal_change_point_temp1 = 0
signal_change_point_temp2 = 0
pre_temp_dts =[]
temp_dts =[]
GPRS_TIME = 0
LBS_TIME = 0
GPRS_loss_num = 0
GPRS_loss_point = []
GPRS_fact_num = 0
LBS_fact_num = 0
LBS_loss_num =0
LBS_loss_point = []
singnal_strength_sum = 0
cpu_tempreture_sum = 0
libv_count = 0
libv_sum = 0.0
pvbv_sum = 0.0
pvbv_count = 0
flag = 0 #用于日期递增
flag1 = 0 #判断信号频率转换的标志位
flag2 = 1 #判断GPRS转为北斗信号后的频率为2分钟一次还是5分钟一次的标志位
flag3 = 1 #判断第12标志位为1时出现10分钟一次的GPRS信号
while process_day <= to_date:
table_name = process_day.strftime("t_acq_data_%Y%m%d")
get_ship_data_by_date_cmd = ("select ALARM_STATUS,ACQ_TIME,DATA_TYPE,SIGNAL_STRENGTH,
CPU_TEMPRETURE,LIBV,PVBV from %s where DEVICE_NO = %s" % (table_name,device_no))
pre_temp_dts = execute_sql(get_ship_data_by_date_cmd)
if pre_temp_dts == () or pre_temp_dts == None:
print('--------------device_no为'+device_no+'在'+str(process_day.date())+'这天没有数据')
else:
for pre_temp_dt in pre_temp_dts:
temp_dts.append({'ALARM_STATUS': str(pre_temp_dt[0]), 'ACQ_TIME': pre_temp_dt[1],
'DATA_TYPE': pre_temp_dt[2],'SIGNAL_STRENGTH': pre_temp_dt[3],'CPU_TEMPRETURE': pre_temp_dt[4],
'LIBV': pre_temp_dt[5],'PVBV': pre_temp_dt[6]})
flag +=1
process_day += datetime.timedelta(1)
if (temp_dts) == []:
print('--------------device_no为'+device_no+'在这几天都没有数据')
else:
if (int(temp_dts[0].get('ALARM_STATUS'),2) & int(8192)):
data_type_begin = 2
flag1 = 4
else:
data_type_begin = 1
data_time_temp1 = datetime_toTimestamp(temp_dts[0].get('ACQ_TIME'))
signal_change_point_temp1 = data_time_temp1
signal_change_points.append(timestamp_toString(data_time_temp1))
for temp_dt in temp_dts:
libv_count += 1
libv_sum += temp_dt.get('LIBV')
times = str(temp_dt.get('ACQ_TIME'))
seconds = (int(times[11])*10+int(times[12]))*3600+(int(times[14])*10+\
int(times[15]))*60+(int(times[17])*10+int(times[18]))
if seconds >= (6*3600) and seconds <= (18*3600):
pvbv_count += 1
pvbv_sum += temp_dt.get('PVBV')
singnal_strength_sum += temp_dt.get('SIGNAL_STRENGTH')
cpu_tempreture_sum += temp_dt.get('CPU_TEMPRETURE')
data_time_temp2 = datetime_toTimestamp(temp_dt.get('ACQ_TIME'))#下一个点的时间以秒数计算
if (2 if int(temp_dt.get('ALARM_STATUS'),2) & int(8192) else 1) == 1:#判断是否GPRS信号
GPRS_fact_num += 1
if flag1 == 4:
flag2 = 1
signal_change_points.append(timestamp_toString(data_time_temp2))
signal_change_point_temp2 = data_time_temp2
LBS_TIME += signal_change_point_temp2 - signal_change_point_temp1
signal_change_point_temp1 = signal_change_point_temp2
if (int(temp_dt.get('ALARM_STATUS'),2) & int(32)):#5分钟一次
flag1 =1
elif (int(temp_dt.get('ALARM_STATUS'),2) & int(2048)):#10分钟一次
flag1 =2
else:#2分钟一次
flag1 =3
elif (int(temp_dt.get('ALARM_STATUS'),2) & int(32)):#5分钟一次
if (flag1 == 1) and (data_time_temp2-data_time_temp1) > (5*60+60):
GPRS_loss_num += (round((data_time_temp2-data_time_temp1)/60/5)-1)
GPRS_loss_point.append(datetime_toString(temp_dt.get('ACQ_TIME')))
flag1 =1
elif (int(temp_dt.get('ALARM_STATUS'),2) & int(2048)):#10分钟一次
if (flag1 == 2) and (flag3 == 1) and (data_time_temp2-data_time_temp1) < (10*60+60) and (data_time_temp2-data_time_temp1) > (10*60-60):
print('--------------------警告：设备号为'+device_no+'第12标志位为1时出现10分钟一次的GPRS信号----------------------------')
flag3 = 2
if (flag1 == 2) and (data_time_temp2-data_time_temp1) > (10*60+120):
GPRS_loss_num += (round((data_time_temp2-data_time_temp1)/60/10)-1)
GPRS_loss_point.append(datetime_toString(temp_dt.get('ACQ_TIME')))
flag1 =2
else:#2分钟一次
if (flag1 == 3) and (data_time_temp2-data_time_temp1) > (2*60+40):
GPRS_loss_num += (round((data_time_temp2-data_time_temp1)/60/2)-1)
GPRS_loss_point.append(datetime_toString(temp_dt.get('ACQ_TIME')))
flag1 =3
elif (2 if int(temp_dt.get('ALARM_STATUS'),2) & int(8192) else 1) == 2:#判断是否北斗信号
LBS_fact_num += 1
if not (flag1 == 4):
signal_change_points.append(timestamp_toString(data_time_temp2))
signal_change_point_temp2 = data_time_temp2
GPRS_TIME += (signal_change_point_temp2 - signal_change_point_temp1)
signal_change_point_temp1 = signal_change_point_temp2
else:
if flag2 == 1:
if (data_time_temp2-data_time_temp1) > (5*60-40) and (data_time_temp2-data_time_temp1) < (5*60+40):
flag2 = 2#标志2分钟一次变为5分钟一次
else:
if (data_time_temp2-data_time_temp1) > (2*60+40):
LBS_loss_num += (round((data_time_temp2-data_time_temp1)/60/2)-1)
LBS_loss_point.append(datetime_toString(temp_dt.get('ACQ_TIME')))
elif flag2 == 2 and (data_time_temp2-data_time_temp1) > (5*60+60):
LBS_loss_num += (round((data_time_temp2-data_time_temp1)/60/5)-1)
LBS_loss_point.append(datetime_toString(temp_dt.get('ACQ_TIME')))
flag1 = 4
elif int(temp_dt.get('ALARM_STATUS'),2) & int(49152):
print('-------------------存在报警信息-------------------------------')
data_time_temp1=data_time_temp2
signal_change_point_temp2 = datetime_toTimestamp(temp_dts[-1].get('ACQ_TIME'))
signal_change_points.append(timestamp_toString(signal_change_point_temp2))
if not flag1 == 4:
GPRS_TIME += signal_change_point_temp2-signal_change_point_temp1
else:
LBS_TIME += signal_change_point_temp2-signal_change_point_temp1
print('GPRS_fact_num:'+ str(GPRS_fact_num))
print('GPRS_loss_num:'+ str(GPRS_loss_num))
print('LBS_fact_num:'+ str(LBS_fact_num))
print('LBS_loss_num:'+ str(LBS_loss_num))
print('GPRS_TIME:'+ str(GPRS_TIME))
print('LBS_TIME:'+ str(LBS_TIME))
print('signal_change_points:'+ str(signal_change_points))
print('GPRS_loss_point:'+ str(GPRS_loss_point))
print('LBS_loss_point:'+ str(LBS_loss_point))
if GPRS_fact_num == 0:
gprs_success = None
else:
gprs_success = GPRS_fact_num/(GPRS_fact_num + GPRS_loss_num)
if LBS_fact_num == 0:
lbs_success = None
else:
lbs_success = LBS_fact_num/(LBS_fact_num + LBS_loss_num)
if GPRS_TIME + LBS_TIME == 0:
gprs_time_percent = None
lbs_time_percent = None
else:
gprs_time_percent = GPRS_TIME/(GPRS_TIME + LBS_TIME)
lbs_time_percent = LBS_TIME/(GPRS_TIME + LBS_TIME)
if libv_count == 0:
libv_average = None
else:
libv_average = libv_sum/libv_count
if pvbv_count == 0:
pvbv_average = None
else:
pvbv_average = pvbv_sum/pvbv_count
return gprs_success,lbs_success,gprs_time_percent,lbs_time_percent,libv_average, pvbv_average
def get_device_nos(dev_type):
get_device_nos_by_date_cmd = ("select distinct(DEVICE_NO) from t_device where DEVICE_TYPE = %s" % (dev_type))
temp_device_nos = execute_sql(get_device_nos_by_date_cmd)
device_nos=[]
for temp_device_no in temp_device_nos:
device_nos.append(temp_device_no[0])
return device_nos
def chunks(device_nos, n):
for i in range(0, len(device_nos), n):
yield device_nos[i:i + n]
def get_excel(device_nos,num):
path = '.'
os.chdir(path)
tmp_path = './tmp'
if not os.path.exists(tmp_path):
os.mkdir(tmp_path)
outexcel_path = os.path.join(tmp_path, '设备数据报表%s.xls' % (num))
flag_excel = 1
workbook = xlwt.Workbook(encoding='utf-8')
booksheet = workbook.add_sheet('Sheet 1', cell_overwrite_ok=True)
c = ['设备NO', '统计数据时间区间','GPRS成功率', 'GPRS占比', '北斗成功率','北斗占比','平均锂电池电压','平均光伏电池电压','设备代数']
#用于初步处理后的数据存储
data_toexcel = []
for i in range(9):
booksheet.write(0, i, c[i])
for device_no in device_nos:
print('---------------------------------------------------------------------------------------------------------')
print('ship which is now handled is device_no:'+ str(device_no))
gprs_success,lbs_success,gprs_time_percent,lbs_time_percent,libv_average,pvbv_average = get_signal_info_by_device_no(from_date, to_date,device_no)
data_toexcel.append({"DEVICE_NO": device_no, "COUNT_TIME_INTERVAL": "%s-%s" % (from_date,to_date), "GPRS_SUCCESS": gprs_success, "GPRS_TIME_PERCENT": gprs_time_percent,\
"LBS_SUCCESS": lbs_success,"LBS_TIME_PERCENT": lbs_time_percent, "DEVICE_TYPE": '三代', "LIBV_AVERAGE": libv_average,"PVBV_AVERAGE": pvbv_average})
booksheet.write(flag_excel, 0, data_toexcel[flag_excel-1].get('DEVICE_NO'))
booksheet.write(flag_excel, 1, data_toexcel[flag_excel-1].get('COUNT_TIME_INTERVAL'))
booksheet.write(flag_excel, 2, data_toexcel[flag_excel-1].get('GPRS_SUCCESS'))
booksheet.write(flag_excel, 3, data_toexcel[flag_excel-1].get('GPRS_TIME_PERCENT'))
booksheet.write(flag_excel, 4, data_toexcel[flag_excel-1].get('LBS_SUCCESS'))
booksheet.write(flag_excel, 5, data_toexcel[flag_excel-1].get('LBS_TIME_PERCENT'))
booksheet.write(flag_excel, 6, data_toexcel[flag_excel-1].get('LIBV_AVERAGE'))
booksheet.write(flag_excel, 7, data_toexcel[flag_excel-1].get('PVBV_AVERAGE'))
booksheet.write(flag_excel, 8, data_toexcel[flag_excel-1].get('DEVICE_TYPE'))
flag_excel += 1
print('-----------------------flag_excel:'+str(flag_excel))
workbook.save(outexcel_path)
def excels_toOne_excel(dest_path=None, path= '.'):
filearray=[]
filelocation="./tmp/"
for filename in glob.glob(filelocation+"*.xls"):
filearray.append(filename)
res=pd.read_excel(filearray[0])
for i in range(1,len(filearray)):
A=pd.read_excel(filearray[i])
res=pd.concat([res,A],ignore_index=True)
print(res.index)
os.chdir(path)
if dest_path is None:
dest_path = os.path.join(path, '%s_%s' % (from_date.date(), to_date.date()))
if not os.path.exists(dest_path):
os.mkdir(dest_path)
outfn = os.path.join(dest_path, u"渔船设备报表.xlsx")
writer = pd.ExcelWriter(outfn)
res.to_excel(writer,'sheet1')
writer.save()
"""
@summary:
根据平台类型处理字体库
linux: 复制字体库到系统字体目录，并手动载入使用
windows: 设置matplot的字体参数即可
"""
def prepare_png_font():
font = None
if platform.system() == 'Windows':
mpl.rcParams['font.sans-serif'] = ['FangSong'] # 指定默认字体
else:
fontFile = '/usr/share/fonts/SimHei.ttf'
if not os.path.exists(fontFile) and os.path.exists('./SimHei.ttf'):
copy('./SimHei.ttf', fontFile)
font = FontProperties(fname=fontFile,size=17)
return font
def draw_signal(dev_type, from_date, to_date,dest_path=None,path='.'):
fname = os.path.join(path, './%s_%s/渔船设备报表.xlsx' % (from_date.date(), to_date.date()))
book = xlrd.open_workbook(fname)
try:
sheet=book.sheet_by_name("sheet1")
except:
print ("在文件%s中没有找到sheet1，读取文件数据失败,要不你换换表格的名字？" %fname)
nrows = sheet.nrows
gprs_success_count_100=0
gprs_success_count_90=0
gprs_success_count_80=0
gprs_success_count_70=0
gprs_success_count_60=0
gprs_success_count_50=0
gprs_success_count_40=0
gprs_success_count_30=0
gprs_success_count_20=0
gprs_success_count_10=0
gprs_success_count_0=0
gprs_success = 0.0
gprs_time_count_100=0
gprs_time_count_90=0
gprs_time_count_80=0
gprs_time_count_70=0
gprs_time_count_60=0
gprs_time_count_50=0
gprs_time_count_40=0
gprs_time_count_30=0
gprs_time_count_20=0
gprs_time_count_10=0
gprs_time_count_0=0
gprs_time_percent = 0.0
for j in range(1,nrows-1):
if not sheet.cell(j,3).value == '':
gprs_success = sheet.cell(j,3).value
else:
gprs_success = ''
if not gprs_success == '':
if(gprs_success == 1.0):
gprs_success_count_100+=1
if(gprs_success < 1.0 and gprs_success >= 0.9):
gprs_success_count_90+=1
if(gprs_success < 0.9 and gprs_success>=0.8):
gprs_success_count_80+=1
if(gprs_success< 0.8 and gprs_success>=0.7):
gprs_success_count_70+=1
if(gprs_success<0.7 and gprs_success>=0.6):
gprs_success_count_60+=1
if(gprs_success< 0.6 and gprs_success>=0.5):
gprs_success_count_50+=1
if(gprs_success<0.5 and gprs_success>= 0.4):
gprs_success_count_40+=1
if(gprs_success<0.4 and gprs_success>=0.3):
gprs_success_count_30+=1
if(gprs_success<0.3 and gprs_success>=0.2):
gprs_success_count_20+=1
if(gprs_success<0.2 and gprs_success>=0.1):
gprs_success_count_10+=1
if(gprs_success<0.1):
gprs_success_count_0+=1
if not sheet.cell(j,4).value == '':
gprs_time_percent = sheet.cell(j,4).value
else:
gprs_time_percent = ''
if not gprs_time_percent == '':
if(gprs_time_percent == 1.0):
gprs_time_count_100+=1
if(gprs_time_percent < 1.0 and gprs_time_percent >= 0.9):
gprs_time_count_90+=1
if(gprs_time_percent < 0.9 and gprs_time_percent>=0.8):
gprs_time_count_80+=1
if(gprs_time_percent< 0.8 and gprs_time_percent>=0.7):
gprs_time_count_70+=1
if(gprs_time_percent<0.7 and gprs_time_percent>=0.6):
gprs_time_count_60+=1
if(gprs_time_percent< 0.6 and gprs_time_percent>=0.5):
gprs_time_count_50+=1
if(gprs_time_percent<0.5 and gprs_time_percent>= 0.4):
gprs_time_count_40+=1
if(gprs_time_percent<0.4 and gprs_time_percent>=0.3):
gprs_time_count_30+=1
if(gprs_time_percent<0.3 and gprs_time_percent>=0.2):
gprs_time_count_20+=1
if(gprs_time_percent<0.2 and gprs_time_percent>=0.1):
gprs_time_count_10+=1
if(gprs_time_percent<0.1):
gprs_time_count_0+=1
gprs_name_list = ['100%','90%','80%','70%','60%','50%','40%','30%','20%','10%','<10%']
gprs_num_list = [gprs_success_count_100,gprs_success_count_90,gprs_success_count_80,gprs_success_count_70,gprs_success_count_60,gprs_success_count_50,gprs_success_count_40,gprs_success_count_30,gprs_success_count_20,gprs_success_count_10,gprs_success_count_0]
gprs_num_list1 = [gprs_time_count_100,gprs_time_count_90,gprs_time_count_80,gprs_time_count_70,gprs_time_count_60,gprs_time_count_50,gprs_time_count_40,gprs_time_count_30,gprs_time_count_20,gprs_time_count_10,gprs_time_count_0]
x =list(range(len(gprs_num_list)))
total_width, n = 0.8, 2
width = total_width / n
myfont = matplotlib.font_manager.FontProperties(fname=r'C:/Windows/Fonts/simsun.ttc',size=14) # 这一行
mpl.rcParams['font.sans-serif'] = ['FangSong'] # 指定默认字体
kwargs = {}
font = prepare_png_font()
if font:
kwargs['fontproperties'] = font
#指定输出图片的尺寸
plt.figure(figsize=(14, 7))
# 设置标题
plt.title(u"渔船GPRS信号统计 设备类型：%s 时间段：%s ~ %s" % (dev_type,from_date.date(), to_date.date()), **kwargs)
b1 = plt.bar(x, gprs_num_list, width=width,label='成功率',tick_label = gprs_name_list,fc = 'lightskyblue')
for i in range(len(x)):
x[i] = x[i] + width
b2 = plt.bar(x, gprs_num_list1, width=width, label=u'占比',fc = 'yellowgreen')
plt.legend()
plt.xlabel('百分比')
plt.ylabel('船只个数')
for rect in b1+b2:
h = rect.get_height()
plt.text(rect.get_x()+rect.get_width()/2,h,'%d'%int (h),ha ='center',va='bottom')
# change to work path
os.chdir(path)
if dest_path is None:
dest_path = os.path.join(path, '%s_%s' % (from_date.date(), to_date.date()))
if not os.path.exists(path):
os.mkdir(path)
if not os.path.exists(dest_path):
os.mkdir(dest_path)
outfn1 = os.path.join(dest_path, u"%s_%s渔船GPRS信号统计.png" % (from_date.date(), to_date.date()))
plt.savefig(outfn1,dpi=300)
plt.show()
plt.close(0)
lbs_success_count_100=0
lbs_success_count_90=0
lbs_success_count_80=0
lbs_success_count_70=0
lbs_success_count_60=0
lbs_success_count_50=0
lbs_success_count_40=0
lbs_success_count_30=0
lbs_success_count_20=0
lbs_success_count_10=0
lbs_success_count_0=0
lbs_success_100_lists_by_device_no=[]
lbs_success_90_lists_by_device_no=[]
lbs_success_80_lists_by_device_no=[]
lbs_success_70_lists_by_device_no=[]
lbs_success_60_lists_by_device_no=[]
lbs_success_50_lists_by_device_no=[]
lbs_success_40_lists_by_device_no=[]
lbs_success_30_lists_by_device_no= []
lbs_success_20_lists_by_device_no= []
lbs_success_10_lists_by_device_no=[]
lbs_success_0_lists_by_device_no= []
lbs_success = 0.0
lbs_time_count_100=0
lbs_time_count_90=0
lbs_time_count_80=0
lbs_time_count_70=0
lbs_time_count_60=0
lbs_time_count_50=0
lbs_time_count_40=0
lbs_time_count_30=0
lbs_time_count_20=0
lbs_time_count_10=0
lbs_time_count_0=0
lbs_time_percent = 0.0
for j in range(1,nrows-1):
if not sheet.cell(j,5).value == '':
lbs_success = sheet.cell(j,5).value
else:
lbs_success = ''
if not lbs_success == '':
if(lbs_success == 1.0):
lbs_success_count_100+=1
lbs_success_100_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success < 1.0 and lbs_success >= 0.9):
lbs_success_count_90+=1
lbs_success_90_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success < 0.9 and lbs_success>=0.8):
lbs_success_count_80+=1
lbs_success_80_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success< 0.8 and lbs_success>=0.7):
lbs_success_count_70+=1
lbs_success_70_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success<0.7 and lbs_success>=0.6):
lbs_success_count_60+=1
lbs_success_60_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success< 0.6 and lbs_success>=0.5):
lbs_success_count_50+=1
lbs_success_50_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success<0.5 and lbs_success>= 0.4):
lbs_success_count_40+=1
lbs_success_40_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success<0.4 and lbs_success>=0.3):
lbs_success_count_30+=1
lbs_success_30_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success<0.3 and lbs_success>=0.2):
lbs_success_count_20+=1
lbs_success_20_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success<0.2 and lbs_success>=0.1):
lbs_success_count_10+=1
lbs_success_10_lists_by_device_no.append(sheet.cell(j,0).value)
if(lbs_success<0.1):
lbs_success_count_0+=1
lbs_success_0_lists_by_device_no.append(sheet.cell(j,0).value)
if not sheet.cell(j,6).value == '':
lbs_time_percent = sheet.cell(j,6).value
else:
lbs_time_percent = ''
if not lbs_time_percent == '':
if(lbs_time_percent == 1.0):
lbs_time_count_100+=1
if(lbs_time_percent < 1.0 and lbs_time_percent >= 0.9):
lbs_time_count_90+=1
if(lbs_time_percent < 0.9 and lbs_time_percent>=0.8):
lbs_time_count_80+=1
if(lbs_time_percent< 0.8 and lbs_time_percent>=0.7):
lbs_time_count_70+=1
if(lbs_time_percent<0.7 and lbs_time_percent>=0.6):
lbs_time_count_60+=1
if(lbs_time_percent< 0.6 and lbs_time_percent>=0.5):
lbs_time_count_50+=1
if(lbs_time_percent<0.5 and lbs_time_percent>= 0.4):
lbs_time_count_40+=1
if(lbs_time_percent<0.4 and lbs_time_percent>=0.3):
lbs_time_count_30+=1
if(lbs_time_percent<0.3 and lbs_time_percent>=0.2):
lbs_time_count_20+=1
if(lbs_time_percent<0.2 and lbs_time_percent>=0.1):
lbs_time_count_10+=1
if(lbs_time_percent<0.1):
lbs_time_count_0+=1
lbs_name_list = ['100%','90%','80%','70%','60%','50%','40%','30%','20%','10%','<10%']
lbs_num_list = [lbs_success_count_100,lbs_success_count_90,lbs_success_count_80,lbs_success_count_70,lbs_success_count_60,lbs_success_count_50,lbs_success_count_40,lbs_success_count_30,lbs_success_count_20,lbs_success_count_10,lbs_success_count_0]
lbs_num_list1 = [lbs_time_count_100,lbs_time_count_90,lbs_time_count_80,lbs_time_count_70,lbs_time_count_60,lbs_time_count_50,lbs_time_count_40,lbs_time_count_30,lbs_time_count_20,lbs_time_count_10,lbs_time_count_0]
x =list(range(len(lbs_num_list)))
total_width, n = 0.8, 2
width = total_width / n
#指定输出图片的尺寸
plt.figure(figsize=(14, 7))
# 设置标题
plt.title(u"渔船北斗信号统计 设备类型：%s 时间段：%s ~ %s" % (dev_type,from_date.date(), to_date.date()), **kwargs)
b1 = plt.bar(x, lbs_num_list, width=width,label='成功率',tick_label = lbs_name_list,fc = 'lightskyblue')
for i in range(len(x)):
x[i] = x[i] + width
b2 = plt.bar(x, lbs_num_list1, width=width, label=u'占比',fc = 'yellowgreen')
plt.legend()
plt.xlabel('百分比')
plt.ylabel('船只个数')
for rect in b1+b2:
h = rect.get_height()
plt.text(rect.get_x()+rect.get_width()/2,h,'%d'%int (h),ha ='center',va='bottom')
# change to work path
outfn2 = os.path.join(dest_path, u"%s_%s渔船北斗信号统计.png" % (from_date.date(), to_date.date()))
plt.savefig(outfn2,dpi=300)
plt.show()
plt.close(0)
libv_average_count_6=0
libv_average_count_5=0
libv_average_count_4=0
libv_average_count_3=0
libv_average_count_2=0
libv_average_count_1=0
libv_average_count_0=0
libv_average_6_lists_by_device_no=[]
libv_average_5_lists_by_device_no=[]
libv_average_4_lists_by_device_no=[]
libv_average_3_lists_by_device_no=[]
libv_average_2_lists_by_device_no=[]
libv_average_1_lists_by_device_no=[]
libv_average_0_lists_by_device_no=[]
libv_average = 0.0
pvbv_average_count_3=0
pvbv_average_count_2_5=0
pvbv_average_count_2=0
pvbv_average_count_1_5=0
pvbv_average_count_1=0
pvbv_average_count_0_5=0
pvbv_average_count_0=0
pvbv_average_3_lists_by_device_no=[]
pvbv_average_2_5_lists_by_device_no=[]
pvbv_average_2_lists_by_device_no=[]
pvbv_average_1_5_lists_by_device_no=[]
pvbv_average_1_lists_by_device_no=[]
pvbv_average_0_5_lists_by_device_no=[]
pvbv_average_0_lists_by_device_no=[]
pvbv_average = 0.0
for j in range(1,nrows-1):
if not sheet.cell(j,7).value == '':
libv_average = sheet.cell(j,7).value
else:
libv_average = ''
if not libv_average == '':
if(libv_average>= 4.1):
libv_average_count_6+= 1
libv_average_6_lists_by_device_no.append(sheet.cell(j,0).value)
if(libv_average < 4.1 and libv_average >=4.0):
libv_average_count_5+= 1
libv_average_5_lists_by_device_no.append(sheet.cell(j,0).value)
if(libv_average < 4.0 and libv_average >=3.9):
libv_average_count_4+= 1
libv_average_4_lists_by_device_no.append(sheet.cell(j,0).value)
if(libv_average <3.9 and libv_average >=3.8):
libv_average_count_3+= 1
libv_average_3_lists_by_device_no.append(sheet.cell(j,0).value)
if(libv_average < 3.8 and libv_average >=3.7):
libv_average_count_2 += 1
libv_average_2_lists_by_device_no.append(sheet.cell(j,0).value)
if(libv_average < 3.7 and libv_average >= 3.6):
libv_average_count_1 += 1
libv_average_1_lists_by_device_no.append(sheet.cell(j,0).value)
if(libv_average < 3.6):
libv_average_count_0 += 1
libv_average_0_lists_by_device_no.append(sheet.cell(j,0).value)
if not sheet.cell(j,8).value == '':
pvbv_average = sheet.cell(j,8).value
else:
pvbv_average = ''
if not pvbv_average == '':
if(pvbv_average < 2.75 and pvbv_average >= 2.25):
pvbv_average_count_3+= 1
pvbv_average_3_lists_by_device_no.append(sheet.cell(j,1).value)
if(pvbv_average < 2.25 and pvbv_average >= 1.75):
pvbv_average_count_2_5+= 1
pvbv_average_2_5_lists_by_device_no.append(sheet.cell(j,1).value)
if(pvbv_average < 1.75 and pvbv_average >= 1.25):
pvbv_average_count_2+= 1
pvbv_average_2_lists_by_device_no.append(sheet.cell(j,1).value)
if(pvbv_average < 1.25 and pvbv_average >= 0.75):
pvbv_average_count_1_5+= 1
pvbv_average_1_5_lists_by_device_no.append(sheet.cell(j,1).value)
if(pvbv_average < 0.75 and pvbv_average >= 0.65):
pvbv_average_count_1 += 1
pvbv_average_1_lists_by_device_no.append(sheet.cell(j,1).value)
if(pvbv_average < 0.65 and pvbv_average >= 0.45):
pvbv_average_count_0_5 += 1
pvbv_average_0_5_lists_by_device_no.append(sheet.cell(j,0).value)
if(pvbv_average < 0.45 and pvbv_average >= 0.0):
pvbv_average_count_0 += 1
pvbv_average_0_lists_by_device_no.append(sheet.cell(j,1).value)
print('pvbv_average_count_0_5------------'+str(pvbv_average_count_0_5))
print(pvbv_average_0_5_lists_by_device_no)
lbs_name_list = ['大于4.1(2.5)','4.0-4.1(2.0)','3.9-4.0(1.5)','3.8-3.9(1.0)','3.7-3.8(0.7)','3.6-3.7(0.5)','小于3.6(小于0.45)']
lbs_num_list = [libv_average_count_6,libv_average_count_5,libv_average_count_4,libv_average_count_3,libv_average_count_2,libv_average_count_1,libv_average_count_0]
lbs_num_list1 = [pvbv_average_count_3,pvbv_average_count_2_5,pvbv_average_count_2,pvbv_average_count_1_5,pvbv_average_count_1,pvbv_average_count_0_5,pvbv_average_count_0]
x =list(range(len(lbs_num_list)))
total_width, n = 0.8, 2
width = total_width / n
#指定输出图片的尺寸
plt.figure(figsize=(14, 7))
# 设置标题
plt.title(u"电池电压统计 设备类型：%s 时间段：%s ~ %s" % (dev_type,from_date.date(), to_date.date()), **kwargs)
b1 = plt.bar(x, lbs_num_list, width=width,label='锂电池电压',tick_label = lbs_name_list,fc = 'lightskyblue')
for i in range(len(x)):
x[i] = x[i] + width
b2 = plt.bar(x, lbs_num_list1, width=width, label=u'光伏电池电压',fc = 'yellowgreen')
plt.legend()
plt.xlabel('电压值')
plt.ylabel('船只数')
for rect in b1+b2:
h = rect.get_height()
plt.text(rect.get_x()+rect.get_width()/2,h,'%d'%int (h),ha ='center',va='bottom')
# change to work path
outfn2 = os.path.join(dest_path, u"%s_%s渔船电池电压统计.png" % (from_date.date(), to_date.date()))
plt.savefig(outfn2,dpi=300)
plt.show()
plt.close(0)
def get_parser():
from optparse import OptionParser
parser = OptionParser("Usage: %prog [options] from_date to_date ")
parser.add_option("-l", "--use-local", dest="use_local", default=False,
action="store_true", help="whether to use local data for processing")
parser.add_option("-p", "--working-path", dest="path", default='.',
help="working path to save or load data, default: ./")
parser.add_option("-i", "--device-no", dest="device_nos", default=[], action='append',
help="ship identities, seperated by comma, string like 211,222,232")
parser.add_option("-r", "--replace", dest="replace", default=False,
action="store_true", help="to replace any result already done")
return parser
if name == 'main':
sys.argv = ['', '2018-10-26', '2018-10-28']
parser = get_parser()
(options, argv) = parser.parse_args(sys.argv)
from_date = parse_datetime(argv[1])
to_date = parse_datetime(argv[2])
dev_type = 3
device_nos = get_device_nos(dev_type)
threads=[]
num = 1
for i in list(chunks(device_nos, int(len(device_nos)/12))):
threads.append(threading.Thread(target=get_excel,args=(i,num) ))
num += 1
for thread in threads:
thread.start()
for thread in threads:
thread.join()
excels_toOne_excel()
draw_signal(dev_type, from_date, to_date)
