py_script/idl_ad_pub_station_stats.py

#encoding=utf-8
#author:wdw110
#功能：统计电视台的各类型数量和收视率

from __future__ import division
import re
import math
import time
import datetime
import numpy as np
from fty_util.common import Mysql

tv_data = {}
tv_data2 = {}
tv_station = {}
tv_station_type = {}
channel_type = {}
result_rate = []
result_type = []
result_channel = []

conn = Mysql.createOfflineConn()

sql = "select tv_name,channel,audience_rating,tv_date from odl.ad_television where theater_attribute='黄金剧场'"
data = Mysql.selectAll(sql, conn=conn)

sql_tv = "select tv_id,tv_name,theme,second_type,decade,first_type from odl.ad_tv_lib where is_use=1"
tmp_data = Mysql.selectAll(sql_tv, conn=conn)
for i in range(len(tmp_data)):
	tv_id = tmp_data[i][0]
	tv_name = tmp_data[i][1]
	theme = tmp_data[i][2]
	type2 = tmp_data[i][3]
	decade = tmp_data[i][4]
	type1 = tmp_data[i][5]
	if type1 and type2:
		tv_data[tv_name] = [tv_id,theme,type2,decade]
		tv_data2[tv_name] = [tv_id,type1,type2]

#按月统计电视台的收视率
for i in range(len(data)):
	tv_name = data[i][0]
	channel = data[i][1]
	aud_rating = data[i][2]
	tv_date = datetime.datetime.strftime(data[i][3],'%Y-%m')

	tv_station.setdefault(channel,{})
	tv_station[channel].setdefault(tv_date,[])
	tv_station[channel][tv_date].append(aud_rating)

	channel_type.setdefault(channel,{})
	channel_type[channel].setdefault(tv_date,{})

	if tv_data2.get(tv_name):
		tv_arr = tv_data2[tv_name][1:-1]
		for level in range(len(tv_arr)):
			channel_type[channel][tv_date].setdefault(level,{})
			ty = tv_arr[level]
			if ty:
				type_arr = ty.split(u' ')
				for tt in type_arr:
					if len(tt):
						channel_type[channel][tv_date][level].setdefault(tt,[])
						channel_type[channel][tv_date][level][tt].append(aud_rating)

for channel,value in channel_type.items():
	for tv_date in value:
		date = datetime.datetime.strptime(tv_date,'%Y-%m').date()
		val = value[tv_date]
		for level,v_obj in val.items():
			for k,v in v_obj.items():
				avg = sum(v)/len(v)
				result_channel.append((channel,k,avg,level+1,date))

for channel,value in tv_station.items():
	for tv_date in value:
		tmp_arr = value[tv_date]
		avg_rating = sum(tmp_arr)/len(tmp_arr)
		date = datetime.datetime.strptime(tv_date,'%Y-%m').date()
		result_rate.append((channel,avg_rating,date))

#按类型统计电视台播放电视剧数量
for i in range(len(data)):
	tv_name = data[i][0]
	channel = data[i][1]
	tv_date = datetime.datetime.strftime(data[i][3],'%Y-%m')

	if tv_data.get(tv_name):
		tv_id = tv_data[tv_name][0]
		type1 = tv_data[tv_name][1]
		type2 = tv_data[tv_name][2]
		decade = tv_data[tv_name][3]
		type_arr = type2.split(u' ') if type2 else []
		tv_station_type.setdefault(channel,{})
		tv_station_type[channel].setdefault(tv_date,{"type1":{},"type2":{}})
		tv_station_type[channel][tv_date]['type1'].setdefault(type1,{})
		tv_station_type[channel][tv_date]['type1'][type1][tv_id] = decade
		for t2 in type_arr:
			if len(t2):
				tv_station_type[channel][tv_date]['type2'].setdefault(t2,{})
				tv_station_type[channel][tv_date]['type2'][t2][tv_id] = decade

for channel,value in tv_station_type.items():
	for tv_date in value:
		type1_obj = value[tv_date]['type1']
		type2_obj = value[tv_date]['type2']
		date = datetime.datetime.strptime(tv_date,'%Y-%m').date()
		for t1,v1 in type1_obj.items():
			for con in v1:
				t1_arr = [channel,t1,'1',con,date,v1[con]]
				result_type.append(t1_arr)
		for t2,v2 in type2_obj.items():
			for con in v2:
				t2_arr = [channel,t2,'2',con,date,v2[con]]
				result_type.append(t2_arr)

delete = 'truncate table idl.ad_pub_station_rate_stats'
Mysql.execute(delete, conn=conn)

sql_rate = 'insert into idl.ad_pub_station_rate_stats(channel,avg_rating,date) values(%s,%s,%s)'
for i in range(int(len(result_rate)/1000)+1):
	tmp = result_rate[i*1000:(i+1)*1000]
	Mysql.insertMany(sql_rate, tmp, conn=conn)

delete = 'truncate table idl.ad_pub_station_type_stats'
Mysql.execute(delete, conn=conn)

sql_type = 'insert into idl.ad_pub_station_type_stats(channel,type,level,tv_id,date,decade) values(%s,%s,%s,%s,%s,%s)'
for i in range(int(len(result_type)/1000)+1):
	tmp = result_type[i*1000:(i+1)*1000]
	Mysql.insertMany(sql_type, tmp, conn=conn)

delete = 'truncate table idl.ad_pub_station_type_rate'
Mysql.execute(delete, conn=conn)

sql_channel = 'insert into idl.ad_pub_station_type_rate(channel,type,avg_rating,level,date) values(%s,%s,%s,%s,%s)'
for i in range(int(len(result_channel)/1000)+1):
	tmp = result_channel[i*1000:(i+1)*1000]
	Mysql.insertMany(sql_channel, tmp, conn=conn)

Mysql.close(conn)