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
