Python3技能大赛 （1、柱状图呈现各省出租率）

#pip install mysql-connector-python
# %load bisai.py
import pandas as pd
from sqlalchemy import create_engine
import matplotlib.pyplot as plt

engine = create_engine('mysql+mysqlconnector://root:123456@127.0.0.1:3306/python')
sql = 'select * from  radar_lines_copy'
datas = pd.read_sql(sql, engine)
print('===***原数据条数为***===')
print(datas.shape[0])

===***原数据条数为***===
9966

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

===***异常处理后据条数为***===
9963

datas["lease_rate"] = datas["lease_rate"].astype(float)
datas['accommodation_num'] = datas['accommodation_num'].astype(float)
datas = datas.drop_duplicates()
print('===***删除重复值后据条数为***===')
print(datas.shape[0])

===***删除重复值后据条数为***===
9960

city_isnull = datas.loc[datas['lease_rate'].isnull()]
city_isnull

province	accommodation_num	avg_score	comment_num	lease_rate	direno_rate	id
9961	上海	156.0	3.8	1009.0	NaN	0.17	259
9962	上海	117.0	3.7	613.0	NaN	0.37	298
9963	云南	168.0	3.2	602.0	NaN	0.43	2298
9964	北京	93.0	4.0	206.0	NaN	0.85	3295
9965	天津	117.0	3.8	632.0	NaN	0.91	6295

In [17]:

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 datas.groupby(datas['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()
            datas.loc[(datas['province'] == i) & (datas['lease_rate'].isnull())] = menan_lst

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

​

221780.0
59757.0
19160.0
202046.0
23690.0
147480.0
53951.0
14890.0
53956.0
50275.0
27465.0
224539.0

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

===***有效数据条数为***===
9956

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))

省份：吉林=住宿场所数为221780.0个=出租率为：0.58
省份：内蒙古=住宿场所数为59757.0个=出租率为：0.57
省份：宁夏=住宿场所数为19160.0个=出租率为：0.55
省份：安徽=住宿场所数为202046.0个=出租率为：0.54
省份：天津=住宿场所数为23690.0个=出租率为：0.53
省份：北京=住宿场所数为147480.0个=出租率为：0.53
省份：山西=住宿场所数为53951.0个=出租率为：0.53
省份：广东=住宿场所数为14890.0个=出租率为：0.52
省份：四川=住宿场所数为53956.0个=出租率为：0.52
省份：上海=住宿场所数为50275.0个=出租率为：0.52

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()