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("完成行为可视化")