数据可视化练习(3)

任务三：柱状图呈现城市出租率

出租率是反映住宿场所经营状况的一项重要指标，它是已出租的客房数与住宿场所可以提供租用的房间总数的百分比。住宿场所出租率的情况可以在一定程度上反应出该住宿场所的整体运营的情况，为了更好的分析指定住宿场所的入住情况，请根据相关表中数据完成出租率分析，通过指定图例进行呈现。

详细描述：

请以数据库中相关表格为数据源，分析并统计各省住宿场所出租率，使用柱状图表达。

具体任务要求：

1）        提取表格相关字段，在控制台按照“各省住宿场所出租率”降序排列，打印输出各省名称及包含的住宿场所数量以及出租率；

打印语句格式如下：

==1.***省=住宿场所数为***个=出租率为：***===

==2.***省=住宿场所数为***个=出租率为：***===

……

2）        使用Flask框架，结合Echarts绘制柱状图。柱状图主标题为“各省住宿场所出租率”（字体要求：红色、加粗、斜体），副标题为出租率前十的省份；纵坐标为出租率，横坐标为省份名称（按照出租率降序排列）；将可视化结果截图并保存。

(1)ralateLease.py

import datetime
import pandas as pd
import numpy as np
from sqlalchemy import create_engine

mysql = create_engine('mysql+mysqlconnector://root:123456@127.0.0.1:3306/hotel')
sql = "select Province,order_num,midnight_num,real_num,realmidnight_num from platform;"
df_leaseRate = pd.read_sql(sql, mysql)

print(df_leaseRate)

df_leaseRate['order_num'] = df_leaseRate['order_num'].astype('float')
df_leaseRate['midnight_num'] = df_leaseRate['midnight_num'].astype('float')
df_leaseRate['real_num'] = df_leaseRate['real_num'].astype('float')
df_leaseRate['realmidnight_num'] = df_leaseRate['realmidnight_num'].astype('float')

df_leaseRate = df_leaseRate.groupby(df_leaseRate['Province'])

province_lst = []
accommodation_lst = []
leaseRate_lst = []
for i in df_leaseRate:
    # print(i[1])
    province_lst.append(i[0])
    accommodation_lst.append(i[1].sum()['real_num'] + i[1].sum()['realmidnight_num'])
    leaseRate_lst.append((i[1].sum()['real_num'] + i[1].sum()['realmidnight_num']) /
                         (i[1].sum()['order_num'] + i[1].sum()['midnight_num']))
    print("==" + i[0] + "省(市/自治区)=住宿场所数为" + str(i[1].sum()['real_num'] + i[1].sum()['realmidnight_num']) +
          "个=出租率为：" + str((i[1].sum()['real_num'] + i[1].sum()['realmidnight_num']) /
                          (i[1].sum()['order_num'] + i[1].sum()['midnight_num'])) + "===")
# print(province_lst)
# print(leaseRate_lst)

df_output = pd.DataFrame({'Province': province_lst, 'accommodation_lst': accommodation_lst, 'leaseRate': leaseRate_lst})
df_output = df_output.sort_values('leaseRate', ascending=False).reset_index(drop=True)
# print(df_output)

Now_time = datetime.datetime.now();
filepath = "csv//" + str(Now_time.date()) + "_" + str(Now_time.timestamp()) + "df_leaseRate.csv"
df_output.to_csv(filepath, index=False, header=['province', 'accommodation_lst', 'leaseRate'])