hive 控制台输入
insert overwrite local directory '/home/badou/hiveout/udata_occupation_rating' row format delimited fields terminated by ','
select occupation,
sum(case rating when 1 then 1 else 0 end) as star1,
sum(case rating when 2 then 1 else 0 end) as star2,
sum(case rating when 3 then 1 else 0 end) as star3,
sum(case rating when 4 then 1 else 0 end) as star4,
sum(case rating when 5 then 1 else 0 end) as star5
from
(select rating,occupation from u_data join u_user on u_data.userid = u_user.userid) t
group by occupation;
python 读取文件并画图
''' 按职业统计各星评价数 udata_occup_rating'''
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
base_dir = r'C:\Users\yyy\Desktop\share_folder'
file = base_dir + '\\udata_occup_rating'
data = pd.read_csv(file,header=None,sep=',',index_col=0)
data.columns = ['star1','star2','star3','star4','star5']
data.index.name='occupation'
data_pro = data.copy()
data_pro['col_sum'] = data_pro[['star1','star2','star3','star4','star5']].apply(lambda x: x.sum(),axis=1)
data_pro[['star1','star2','star3','star4','star5']] = data_pro[['star1','star2','star3','star4','star5']].div(data_pro['col_sum'],axis='rows')
# 比例图
data_pro.drop(['col_sum'],axis=1).plot(kind='bar', stacked=True,alpha=0.5)
data_pro.drop(['col_sum'],axis=1).plot.barh(stacked=True,alpha=0.7)
plt.xlim(0,1.1)
plt.legend(bbox_to_anchor=(1.3,1))
# 原始数据图
data.plot.barh(stacked=True,alpha=0.5)
按比例画图
按原始数据画图
