def analysis_rent(): unit_list = session.query(Tenement.unit, func.count(Tenement.unit)).group_by(Tenement.unit).all() clean_unit = [] for i in unit_list: if i[1] >= 10: clean_unit.append(i[0]) data = [] for unit in clean_unit: data += [{'unit': i[0], 'rent': i[1], 'name': i[2]} for i in session.query(Tenement.unit, Tenement.rent, Tenement.title).filter(Tenement.unit==unit, Tenement.title!='整租').group_by(Tenement.unit, Tenement.rent).order_by(Tenement.rent.desc()).limit(10).all()] return data
def analysis_director(): director_list = session.query( Movie.director, func.count(Movie.director)).group_by( Movie.director).order_by(func.count( Movie.director).desc()).limit(16).all() data = [{'name': i[0], 'count': i[1]} for i in director_list] return data
def analysis_top10_wage(): salary_list = session.query( Job.city, func.avg((Job.salary_upper + Job.salary_lower) / 2)).group_by( Job.city).order_by( func.avg( (Job.salary_upper + Job.salary_lower) / 2).desc()).all() data = [{'name': i[0], 'salary': float(i[1])} for i in salary_list][:10] return data
def analysis_education(): all_education = session.query(Job.education, func.count( Job.education)).group_by(Job.education).all() total = sum([i[1] for i in all_education]) data = [{ 'item': i[0], 'count': i[1], 'percent': round(i[1] / total, 2) } for i in all_education] return data
def analysis_top10_estate(): estate_list = session.query( Tenement.title, func.count(Tenement.title)).group_by( Tenement.title).order_by( func.count(Tenement.title).desc()).limit(11).all() for i in estate_list: if i[0] == '整租': estate_list.pop(estate_list.index(i)) data = [{'name': i[0], 'amount': i[1]} for i in estate_list] return data
def analysis_cityedc_salary(): all_cityedu_salary = session.query( Job.city, Job.education, func.avg((Job.salary_upper + Job.salary_lower) / 2)).group_by( Job.city, Job.education).all() data = [{ 'city': i[0], 'edc': i[1], 'salary': float(i[2]) } for i in all_cityedu_salary] return data
def analysis_experience(): all_experience = session.query(Job.experience_lower, Job.experience_upper).all() experience_count = Counter(all_experience).most_common() data = [{ 'experience': str(i[0][0]) + '-' + str(i[0][1]) + '年', 'count': i[1] } for i in experience_count] for i in data: if i['experience'] == '0-0年': i['experience'] = '不限' return data
def analysis_country(): country_list = session.query(Movie.country).all() clean_country = [] for i in country_list: for country in i[0].split(): if '中国' in country: clean_country.append('中国大陆') else: clean_country.append(country) country_count = Counter(clean_country).most_common(10) data = [{'name': i[0], 'count': i[1]} for i in country_count] return data
def analysis_tags(): all_tags = session.query(Job.tags).all() tags_list = [] for tags in all_tags: for tag in tags: tags_list += tag.split() tag_count = Counter(tags_list).most_common(10) ''' tag_df = pd.DataFrame(tag_count).set_index(pd.DataFrame( tag_count)[0]).drop([0],axis=1) ''' data = [{'tag': i[0], 'count': i[1]} for i in tag_count] return data
def analysis_unit(): unit_list = session.query(Tenement.unit, func.count(Tenement.unit)).group_by(Tenement.unit).all() clean_unit = [] count = 0 for i in unit_list: if i[1] < 10: count += i[1] else: clean_unit.append(i) clean_unit.append(('其他', count)) total = sum([i[1] for i in clean_unit]) data = [{'item': i[0], 'count': i[1], 'percent': round(i[1] / total, 2)} for i in clean_unit] return data
def analysis_top10_city(): city_list = session.query(Job.city, func.count(Job.city)).group_by( Job.city).order_by(func.count(Job.city).desc()).all() data = [{'name': i[0], 'amount': i[1]} for i in city_list][:10] return data
def analysis_region(): region_list = session.query(Tenement.region, func.count(Tenement.region)).group_by( Tenement.region).order_by(func.count(Tenement.region).desc()).all() data = [{'name': i[0], 'amount': i[1]} for i in region_list] return data
def analysis_area(): area_list = session.query(Tenement.area, func.count(Tenement.area)).group_by(Tenement.area).all() data = [{'area': i[0], 'count': i[1]} for i in area_list] return data
def analysis_review_num(): review_num_list = session.query( Movie.name, Movie.review_num).order_by( Movie.review_num.desc()).limit(10).all() data = [{'name':i[0], 'num': i[1]} for i in review_num_list] return data