def salary_top10(): data = db.session.query(func.avg((JobModel.salary_lower + JobModel.salary_upper)/2), JobModel.city).group_by(JobModel.city).all() data = sorted(data, reverse=True) x = '城市' y = '薪资' todict = Todict(y, x) return list(map(todict.to_dict,data[:10])), x, y
def position_top10(): data = db.session.query(func.count(JobModel.city), JobModel.city).group_by(JobModel.city).all() data = sorted(data, reverse=True) x = '城市' y = '职位数' todict = Todict(y, x) return list(map(todict.to_dict,data[:10])), x, y
def salary_by_city_and_education(): data = db.session.query(func.avg((JobModel.salary_lower + JobModel.salary_upper)/2), JobModel.education, JobModel.city).group_by(JobModel.city, JobModel.education).all() # data = sorted(data, reverse=True) x = '学历' y = '薪资' z = '城市' todict = Todict(y, x, z) return list(map(todict.to_dict,data)), x, y, z
def quantity_top10(): data = db.session.query(func.count(HouseModel.community), HouseModel.community).group_by( HouseModel.community).all() data = sorted(data, reverse=True) x = '小区' y = '房源数' todict = Todict(y, x) return list(map(todict.to_dict, data[:10])), x, y
def consumption_district_top10(): data = db.session.query(func.avg(RestaurantModel.consumption), RestaurantModel.business_district).group_by( RestaurantModel.business_district).all() data = sorted(data, key=lambda x: x[0], reverse=True) x = '商圈名' y = '人均消费' todict = Todict(y, x) return list(map(todict.to_dict, data[:10])), x, y
def consumption_categories_top10(): data = db.session.query(func.avg(RestaurantModel.consumption), RestaurantModel.classification).group_by( RestaurantModel.classification).all() data = sorted(data, key=lambda x: x[0], reverse=True) x = '餐馆分类' y = '人均消费' todict = Todict(y, x) return list(map(todict.to_dict, data[:10])), x, y
def popular_restaurant(): data = db.session.query( RestaurantModel.taste + RestaurantModel.ambience + RestaurantModel.service, RestaurantModel.title).filter( RestaurantModel.review_count > 500).all() data = sorted(data, key=lambda x: x[0], reverse=True) x = '餐馆名' y = '总分' todict = Todict(y, x) return list(map(todict.to_dict, data[:10])), x, y
def education_stat(): data = db.session.query(func.count(JobModel.education), JobModel.education).group_by(JobModel.education).all() data = sorted(data, reverse=True) x = '学历' y = '统计次数' percent = '占比' df = pd.DataFrame(data, columns=[y, x]) per = round(df[y]/df[y].sum(), 2) df = pd.concat([df,per], axis=1) data = list(df.values) todict = Todict(y, x, percent) return list(map(todict.to_dict,data)), x, y, percent
def house_type_destributed(): data = db.session.query(func.count(HouseModel.house_type), HouseModel.house_type).group_by( HouseModel.house_type).all() data = sorted(data, reverse=True) x = '户型' y = '房源数' percent = '占比' df = pd.DataFrame(data, columns=[y, x]) per = round(df[y] / df[y].sum(), 2) df = pd.concat([df, per], axis=1) data = list(df.values) todict = Todict(y, x, percent) return list(map(todict.to_dict, data)), x, y, percent
def price_type_top10(): data = db.session.query(func.avg(HouseModel.price), HouseModel.house_type, HouseModel.community).group_by( HouseModel.community, HouseModel.house_type).all() data = sorted(data, reverse=True) x = '户型' y = '租金' z = '小区' todict = Todict(y, x, z) df = pd.DataFrame(list(map(todict.to_dict, data))) df = df.sort_values(by=['户型', '租金'], ascending=False) df1 = pd.DataFrame(columns=df.columns) count_type = set(df['户型'].values) for i in count_type: df1 = pd.concat([df1, df[df['户型'] == i].iloc[:10, :]]) data = list(df1.values) return list(map(todict.to_dict, data)), x, y, z
def consumption_compared(): classification = sorted(db.session.query( func.count(RestaurantModel.classification), RestaurantModel.classification).group_by( RestaurantModel.classification).all(), reverse=True) classification = [x[1] for x in classification[:10]] result = [] x = '餐馆分类' y = '人均消费' z = '商圈名' for i in classification: data = db.session.query( func.avg(RestaurantModel.consumption), RestaurantModel.classification, RestaurantModel.business_district).filter( RestaurantModel.classification == i).group_by( RestaurantModel.business_district).all() data = sorted(data, key=lambda x: x[0], reverse=True) todict = Todict(y, x, z) result += list(map(todict.to_dict, data[:10])) return result, x, y, z
def hot_tags(format='png'): from seiya.web.app import config_dict sql = 'SELECT (tags) from jobmodel' df = pd.read_sql(sql=sql, con=config_dict['SQLALCHEMY_DATABASE_URI']) data = df.to_csv(index=0).split() data_list = [] for tag in set(data): data_list.append((data.count(tag), tag)) x = '职位标签' y = '出现次数' data_list = sorted(data_list, reverse=True) todict = Todict(y, x) df = pd.DataFrame(data_list[:10], columns=['出现次数', '职位标签']) # 设置中文字体,如果系统里没有 SimHei 字体,可以替换为其它任何中文字体 mpl.rcParams['font.sans-serif'] = ['SimHei'] # 避免 - 号显示为方块 mpl.rcParams['axes.unicode_minus'] = False # 设置画布宽高,单位为英寸 mpl.rcParams['figure.figsize'] = 11, 5 plt.bar(range(len(df)),df['出现次数'], color='c', tick_label=df['职位标签']) fig = io.BytesIO() plt.savefig(fig, format=format) return list(map(todict.to_dict, data_list[:10])), x, y, fig.getvalue()