def show_user_class(user_id):
"""
歌曲类别比重绘制
:return:
"""
print("开始绘制用户曲风偏爱")
sql = connect_sql.SQL()
user_data = sql.get_recoder_byUid(user_id)
tag1_data = user_data.query("tag==1") # 单曲循环
tag2_data = user_data.query("tag==2") # 片段播放
tag3_data = user_data.query("tag==3") # 评论时长+次数
tag4_data = user_data.query("tag==4") # 点赞评论
tag5_data = user_data.query("tag==5") # 收藏歌曲
res = []
res.append(get_class_other(tag1_data))
res.append(get_tag2_class(tag2_data))
res.append(get_class_other(tag3_data))
res.append(get_class_other(tag4_data))
res.append(get_class_other(tag5_data))
result = [0,0,0,0,0,0,0]
for item in res:
for i in range(0,7):
result[i]+=item[i]
for i in range(0,7):
result[i]=round(result[i],2)
lable = ["古风","古典","电子","民谣","流行","说唱","摇滚"]
data = []
for i in range(0,7):
data.append((lable[i],result[i]))
# visualization.pyga_pie_class(result,user_id,"曲风偏爱比重")
# visualization.pygal_bar_class(result,user_id,"收听曲风数据")
ct.draw_pie_like(data,user_id)
ct.draw_bar_like(data,user_id)
print("完成曲风比重绘制")
def process_data2(user_id):
data_set = []
sql = connect_sql.SQL()
user_data = sql.get_recoder_byUid(user_id)
tag_data = [0]
for i in range(1,6):
tag_data.append(user_data.query("tag=="+str(i)))
# 行为统计
data = []
lable = [" ","循环播放","片段播放","查看评论","点赞评论","收藏歌曲"]
for i in range(1,6):
tmp = work_tag_times(tag_data[i])
if i == 5:
tmp = tmp/10
data.append((lable[i],tmp))
data_set.append(data)
# 偏爱统计
data = update_user_message(user_id)
data_set.append(data)
# 播放方式统计
tag1_data = work_tag_plays(tag_data[1])
tag2_data = work_tag_plays(tag_data[2])
# 播放方式
data = [tag2_data[0], tag1_data[0]]
data_set.append(data)
# 片段频率统计
data = tag2_data[1]
data_set.append(data)
return data_set
def download_user_music(user_id):
sql = connect_sql.SQL()
user_data = sql.get_recoder_byUid(user_id)
songdata = user_data.iloc[:,2:3]
cnt = 0
for row in songdata.itertuples():
song_id=str(getattr(row, 'song_id'))
if sql.judgesong(song_id) == None:
print("下载中..",cnt)
save_mp3 = "E:\\musiclib\\mp3\\"+str(song_id)+".mp3"
fg1 = crawl_song.download_song(save_mp3,song_id)
if fg1 == False:
continue
save_lrc = "E:\\musiclib\\lrc\\"+str(song_id)+".txt"
crawl_song.download_lrc(save_lrc,song_id)
save_wav = "E:\\musiclib\\wav\\"+str(song_id)+".wav"
fg2 = music_classify.mp3_to_wav(save_mp3, save_wav)
if fg2 == False:
continue
# print(song_id)
cnt+=1
sql.insert_song([song_id,'name','author','song_lrc','song_url'])
print("cnt:",cnt)
def update_user_message(user_id):
sql = connect_sql.SQL()
user_data = sql.get_user(user_id)
user_data = user_data.iloc[:, :]
lable = ["antique","classical","electronic","folk","pop","rap","rock"]
update_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
valuse = []
for row in user_data.itertuples():
for item in lable:
valuse.append(getattr(row,item))
valuse.append(update_time)
for row in user_data.itertuples():
valuse.append(getattr(row,"songs"))
# print(user_data)
lasttime = getattr(user_data,"lasttime")
# print(lasttime)
new_recoder = sql.get_recoder_bydatetime(user_id,lasttime)
print(new_recoder)
tag_data = [0]
for i in range(1, 6):
tag_data.append(new_recoder.query("tag==" + str(i)))
# print(new_recoder)
# 新纪录 音乐分类 更新到原有的 values
# 偏爱统计
res = []
for i in range(1, 6):
if i != 2:
res.append(get_class_other(tag_data[i]))
else:
res.append(get_tag2_class(tag_data[i]))
result = [0, 0, 0, 0, 0, 0, 0]
for item in res:
for i in range(0, 7):
result[i] += item[i]
for i in range(0, 7):
result[i] = round(result[i], 2)
lable = ["古风", "古典", "电子", "民谣", "流行", "说唱", "摇滚"]
data = []
for i in range(0,7):
all=float(result[i])+float(valuse[i])
valuse[i] = all
data.append((lable[i], all))
valuse[8]=update_cloud(user_id,valuse[8],tag_data[1],tag_data[2])
sql.update_user(user_id,valuse)
return data
def process_data(user_id):
data_set = []
sql = connect_sql.SQL()
user_data = sql.get_recoder_byUid(user_id)
tag_data = [0]
for i in range(1,6):
tag_data.append(user_data.query("tag=="+str(i)))
# 行为统计
data = []
lable = [" ","循环播放","片段播放","查看评论","点赞评论","收藏歌曲"]
for i in range(1,6):
tmp = work_tag_times(tag_data[i])
if i == 5:
tmp = tmp/10
data.append((lable[i],tmp))
data_set.append(data)
# 偏爱统计
res = []
for i in range(1,6):
if i != 2:
res.append(get_class_other(tag_data[i]))
else:
res.append(get_tag2_class(tag_data[i]))
result = [0, 0, 0, 0, 0, 0, 0]
for item in res:
for i in range(0, 7):
result[i] += item[i]
for i in range(0, 7):
result[i] = round(result[i], 2)
lable = ["古风", "古典", "电子", "民谣", "流行", "说唱", "摇滚"]
data = []
for i in range(0, 7):
data.append((lable[i], result[i]))
data_set.append(data)
# 播放方式统计
tag1_data = work_tag_plays(tag_data[1])
tag2_data = work_tag_plays(tag_data[2])
# 播放方式
data = [tag2_data[0], tag1_data[0]]
data_set.append(data)
# 片段频率统计
data = tag2_data[1]
data_set.append(data)
return data_set
def insert_op():
sql = connect_sql.SQL()
# 输入的信息
song = song_id.get()
cnt = times.get()
values = []
values.append(user_id)
values.append(song)
values.append(5)
values.append(0)
values.append(0)
values.append(10)
values.append("message")
values.append(datetime)
sql.insert_recoder(values)
tk.messagebox.showinfo(title='提示', message='插入成功')
window_tag.destroy()
def show_user_cloud(user_id):
"""
用户词云
:param user_id:
:return:
"""
print("开始绘制用户词云")
sql = connect_sql.SQL()
user_data = sql.get_recoder_byUid(user_id)
tag1_data = user_data.query("tag==1")
tag2_data = user_data.query("tag==2")
work_tag1_lrc(user_id, tag1_data)
work_tag2_lrc(user_id, tag2_data)
user_lrc_file = "user_data/cloud_" + str(user_id) + ".txt"
user_cloud_file = "cloud_" + str(user_id) + ".jpg"
visualization.draw_cloud(user_lrc_file,user_cloud_file)
# 数据清理
with open(user_lrc_file,'w',encoding='utf-8') as f:
f.write("")
f.close()
print("完成词云绘制")
def show_user_playhobby(user_id):
"""
可视化播放习惯
:param user_id:
:return:
"""
print("开始绘制用户听歌习惯")
sql = connect_sql.SQL()
user_data = sql.get_recoder_byUid(user_id)
tag1_data = user_data.query("tag==1")
tag2_data = user_data.query("tag==2")
tag1_data = work_tag_plays(tag1_data)
tag2_data = work_tag_plays(tag2_data)
plays = [tag2_data[0],tag1_data[0]]
playcounts = tag2_data[1]
# visualization.pygal_line_playhobby(playcounts,user_id,"播放片段")
# visualization.pygal_bar_plays(plays,user_id,"听歌方式")
# visualization.pyga_pie_plays(plays,user_id,"听歌方式比重")
ct.draw_line_clip(playcounts,user_id,"片段播放习惯")
ct.draw_bar_plays(plays,user_id,"播放方式")
print("完成播放方式绘制")
def show_user_reconder(user_id):
"""
行为占比绘制
:param user_id:
:return:
"""
print("开始用户行为可视化")
data = []
lable = ["循环播放","片段播放","查看评论","点赞评论","收藏歌曲"]
sql = connect_sql.SQL()
user_data = sql.get_recoder_byUid(user_id)
tag1_data = user_data.query("tag==1") # 单曲循环
data.append((lable[0],work_tag_times(tag1_data)))
tag2_data = user_data.query("tag==2") # 片段播放
data.append((lable[1], work_tag_times(tag2_data)))
tag3_data = user_data.query("tag==3") # 评论时长+次数
data.append((lable[2], work_tag_times(tag3_data)))
tag4_data = user_data.query("tag==4") # 点赞评论
data.append((lable[3], work_tag_times(tag4_data)))
tag5_data = user_data.query("tag==5") # 收藏歌曲
data.append((lable[4], work_tag_times(tag5_data)/10))
ct.draw_bar_recoder(data,user_id)
ct.draw_pie_recoder(data,user_id)
print("完成行为可视化")
