import pandas as pd
import pymysql
from pyecharts import Bar,Pie,Line,Scatter,Map
TABLE_COMMENTS = '****'
TABLE_USERS = '****'
DATABASE = '****'
conn = pymysql.connect(host='localhost', user='****', passwd='****', db=DATABASE, charset='utf8mb4')
sql_users = 'SELECT id,gender,age,city FROM '+TABLE_USERS
sql_comments = 'SELECT id,time FROM '+TABLE_COMMENTS
comments = pd.read_sql(sql_comments, con=conn)
users = pd.read_sql(sql_users, con=conn)
# 評(píng)論時(shí)間(按天)分布分析
comments_day = comments['time'].dt.date
data = comments_day.id.groupby(comments_day['time']).count()
line = Line('評(píng)論時(shí)間(按天)分布')
line.use_theme('dark')
line.add(
	'',
	data.index.values,
	data.values,
	is_fill=True,
)
line.render(r'./評(píng)論時(shí)間(按天)分布.html')
# 評(píng)論時(shí)間(按小時(shí))分布分析
comments_hour = comments['time'].dt.hour
data = comments_hour.id.groupby(comments_hour['time']).count()
line = Line('評(píng)論時(shí)間(按小時(shí))分布')
line.use_theme('dark')
line.add(
	'',
	data.index.values,
	data.values,
	is_fill=True,
)
line.render(r'./評(píng)論時(shí)間(按小時(shí))分布.html')
# 評(píng)論時(shí)間(按周)分布分析
comments_week = comments['time'].dt.dayofweek
data = comments_week.id.groupby(comments_week['time']).count()
line = Line('評(píng)論時(shí)間(按周)分布')
line.use_theme('dark')
line.add(
	'',
	data.index.values,
	data.values,
	is_fill=True,
)
line.render(r'./評(píng)論時(shí)間(按周)分布.html')
# 用戶年齡分布分析
age = users[users['age']>0]	# 清洗掉年齡小于1的數(shù)據(jù)
age = age.id.groupby(age['age']).count()	# 以年齡值對(duì)數(shù)據(jù)分組
Bar = Bar('用戶年齡分布')
Bar.use_theme('dark')
Bar.add(
	'',
	age.index.values,
	age.values,
	is_fill=True,
)
Bar.render(r'./用戶年齡分布圖.html')	# 生成渲染的html文件
# 用戶地區(qū)分布分析
# 城市code編碼轉(zhuǎn)換
def city_group(cityCode):
    city_map = {
        '11': '北京',
        '12': '天津',
        '31': '上海',
        '50': '重慶',
        '5e': '重慶',
        '81': '香港',
        '82': '澳門(mén)',
        '13': '河北',
        '14': '山西',
        '15': '內(nèi)蒙古',
        '21': '遼寧',
        '22': '吉林',
        '23': '黑龍江',
        '32': '江蘇',
        '33': '浙江',
        '34': '安徽',
        '35': '福建',
        '36': '江西',
        '37': '山東',
        '41': '河南',
        '42': '湖北',
        '43': '湖南',
        '44': '廣東',
        '45': '廣西',
        '46': '海南',
        '51': '四川',
        '52': '貴州',
        '53': '云南',
        '54': '西藏',
        '61': '陜西',
        '62': '甘肅',
        '63': '青海',
        '64': '寧夏',
        '65': '新疆',
        '71': '臺(tái)灣',
        '10': '其他',
    }
    return city_map[cityCode[:2]]
city = users['city'].apply(city_group)
city = city.id.groupby(city['city']).count()
map_ = Map('用戶地區(qū)分布圖')
map_.add(
	'',
	city.index.values,
	city.values,
	maptype='china',
	is_visualmap=True,
	visual_text_color='#000',
	is_label_show=True,
)
map_.render(r'./用戶地區(qū)分布圖.html')

import numpy as np
import pymysql
from snownlp import SnowNLP
from pyecharts import Bar
TABLE_COMMENTS = '****'
DATABASE = '****'
SONGNAME = '****'
def getText():
	conn = pymysql.connect(host='localhost', user='root', passwd='root', db=DATABASE, charset='utf8')
	sql = 'SELECT id,content FROM '+TABLE_COMMENTS
	text = pd.read_sql(sql%(SONGNAME), con=conn)
	return text
def getSemi(text):
	text['content'] = text['content'].apply(lambda x:round(SnowNLP(x).sentiments, 2))
	semiscore = text.id.groupby(text['content']).count()
	bar = Bar('評(píng)論情感得分')
	bar.use_theme('dark')
	bar.add(
		'',
		y_axis = semiscore.values,
		x_axis = semiscore.index.values,
		is_fill=True,
	)
	bar.render(r'情感得分分析.html')
	text['content'] = text['content'].apply(lambda x:1 if x>0.5 else -1)
	semilabel = text.id.groupby(text['content']).count()
	bar = Bar('評(píng)論情感標(biāo)簽')
	bar.use_theme('dark')
	bar.add(
		'',
		y_axis = semilabel.values,
		x_axis = semilabel.index.values,
		is_fill=True,
	)
	bar.render(r'情感標(biāo)簽分析.html')

from wordcloud import WordCloud
import matplotlib.pyplot as plt
plt.style.use('ggplot')
plt.rcParams['axes.unicode_minus'] = False
def getWordcloud(text):
	text = ''.join(str(s) for s in text['content'] if s)
	word_list = jieba.cut(text, cut_all=False)
	stopwords = [line.strip() for line in open(r'./StopWords.txt', 'r').readlines()]	# 導(dǎo)入停用詞
	clean_list = [seg for seg in word_list if seg not in stopwords] #去除停用詞
	clean_text = ''.join(clean_list)
	# 生成詞云
	cloud = WordCloud(
	    font_path = r'C:/Windows/Fonts/msyh.ttc',
	    background_color = 'white',
	    max_words = 800,
	    max_font_size = 64
	)
	word_cloud = cloud.generate(clean_text)
	# 繪制詞云
	plt.figure(figsize=(12, 12))
	plt.imshow(word_cloud)
	plt.axis('off')
	plt.show()
if __name__ == '__main__':
	text = getText()
	getSemi(text)
	getWordcloud(text)