Python

import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt
import pymysql

con = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/my')
data = pd.read_sql_table("radar_lines_copy", con)
data

print('===***原数据条数为***===')
print(data.shape[0])

for i in data['accommodation_num']:
    try:
        i = float(i)
    except:
        data = data[data['accommodation_num'] != i]
print('===***异常处理后据条数为***===')
print(data.shape[0])

data["lease_rate"] = data["lease_rate"].astype(float)
data['accommodation_num'] = data['accommodation_num'].astype(float)

data = data.drop_duplicates()
print('===***删除重复值后据条数为***===')
print(data.shape[0])

data.dropna(subset=['province'], how='all', axis=0, inplace=True)
print('===***删除省份为空的数据后据条数为***===')
print(data.shape[0])

city_isnull = data.loc[data['lease_rate'].isnull()]
province_lst = []
for t in city_isnull['province']:
    province_lst.append(t)
province_lst = pd.unique(province_lst)
print(province_lst)

lst = []
for i in data.groupby(data['province']):
    lst.append(list(i)[1])

menan_lst = 0.00
b = []
for i in province_lst:
    for j in lst:
        if i == j['province'].values[0]:
            menan_lst = j['lease_rate'].sample(50).mean()
            data.loc[(data['province'] == i) & (data['lease_rate'].isnull())] = menan_lst

province = []

c = []
f = []
for j in lst:
    province.append(j['province'].values[0])
    mean_rate = data.loc[data['province'] == j['province'].values[0]]['lease_rate'].mean()
    sum = data.loc[data['province'] == j['province'].values[0]]['accommodation_num'].sum()
    c.append(mean_rate)
    f.append(sum)
    print(sum)

dict = dict(zip(province, c))
e = sorted(dict.items(), key=lambda x: -x[1])[:10]
print('===***有效数据条数为***===')
print(data.shape[0])

x = []
y = []
for i, j in e:
    x.append(i)
    y.append(j)
for (j, p), i in zip(e, f):
    p = "%.2f" % p
    print("省份：{}=住宿场所数为{}个=出租率为：{}".format(j, i, p))

plt.rcParams['font.sans-serif'] = ['FangSong']
plt.rcParams['axes.unicode_minus'] = False
plt.figure(figsize=(4, 3))

plt.xlabel('省份')
plt.ylabel('出租率')
plt.bar(x, y)
plt.show()