df = pd.read_excel('data.xlsx')
n, m = df.shape
type(df.iloc[1])

df.dtypes

print('没有房龄={}, 没有面积={}'.format(df['year'].isna().sum(), df['size'].isna().sum()))
print('既没有房龄也没有面积={}'.format((df['year'].isna() & df['size'].isna()).sum()))
print('既没有房龄或者没有面积={}'.format((df['year'].isna() | df['size'].isna()).sum()))

city_name = ['未央区', '雁塔区', '莲湖区', '碑林区', '新城区', '灞桥区', '长安区', '高新区', '经开区',
             '浐灞生态区', '国际港务区', '西咸新区']
evl = df[-df['5_price'].isna()].reset_index()
n, m = evl.shape
cnt = [0] * 12
for i in range(n):
    cnt[evl.loc[i, 'city']] += 1
print('地区: 用于检验的个数')
for i in range(12):
    print('{}: {}'.format(city_name[i], cnt[i]))
print('总计: {}'.format(sum(cnt)))

print('训练数据个数={}, 检验数据个数={}, 总计={}'.format(train.shape[0], evl.shape[0], df.shape[0]))
df.describe()

# k-s测试
def ks_test(name):
    return kstest(train[name].to_numpy(), 'norm', (train[name].mean(), train[name].std()))
print(ks_test('metro'))
print(ks_test('bus'))
print(ks_test('school'))
print(ks_test('hospital'))
print(ks_test('shop'))

plt.rcParams['font.size'] = 40 #固定字体大小
plt.rcParams['figure.figsize'] = (14, 10) #固定图像大小
ax1 = train['metro'].plot(kind = 'hist', bins=50, grid=True)
plt.axis([0, 6000, 0, 30])
plt.xticks([x for x in range(1, 6000) if x % 1000 == 0])
ax1.set_xlabel('地铁距离')
ax1.set_ylabel('个数')

ax2 = ax1.twinx()
train['metro'].plot(kind = 'kde', color='orange', label='概率密度分布', ax=ax2)
ax2.set_yticks([])
ax2.set_ylabel('')
plt.legend()

plt.savefig('metro.pdf')
plt.show()

ax1 = train['bus'].plot(kind = 'hist', bins=50, grid=True)
plt.axis([0, 2400, 0, 23])
plt.xticks([x for x in range(1, 2401) if x % 500 == 0])
ax1.set_xlabel('公交站距离')
ax1.set_ylabel('个数')

ax2 = ax1.twinx()
train['bus'].plot(kind = 'kde', color='orange', label='概率密度分布', ax=ax2)
ax2.set_yticks([])
ax2.set_ylabel('')
plt.legend()

plt.savefig('bus.pdf')
plt.show()

ax1 = train['school'].plot(kind = 'hist', bins=50, grid=True)
plt.axis([0, 2100, 0, 17])
plt.xticks([x for x in range(1, 2001) if x % 500 == 0])
ax1.set_xlabel('学校距离')
ax1.set_ylabel('个数')

ax2 = ax1.twinx()
ax2 = train['school'].plot(kind = 'kde', color='orange', label='概率密度分布')
ax2.set_yticks([])
ax2.set_ylabel('')
plt.legend()

plt.savefig('school.pdf')
plt.show()

ax1 = train['hospital'].plot(kind = 'hist', bins=50, grid=True)
plt.axis([0, 5000, 0, 25])
plt.xticks([x for x in range(1, 5001) if x % 1000 == 0])
ax1.set_xlabel('医院距离')
ax1.set_ylabel('个数')

ax2 = ax1.twinx()
ax2 = train['hospital'].plot(kind = 'kde', color='orange', label='概率密度分布')
ax2.set_yticks([])
ax2.set_ylabel('')
plt.legend()

plt.savefig('hospital.pdf')
plt.show()

ax1 = train['shop'].plot(kind = 'hist', bins=50, grid=True)
plt.axis([0, 2000, 0, 20])
plt.xticks([x for x in range(1, 2001) if x % 300 == 0])
ax1.set_xlabel('超市距离')
ax1.set_ylabel('个数')

ax2 = ax1.twinx()
ax2 = train['shop'].plot(kind = 'kde', color='orange', label='概率密度分布')
ax2.set_yticks([])
ax2.set_ylabel('')
plt.legend()

plt.savefig('shop.pdf')
plt.show()

ax1 = train['year'].plot(kind = 'hist', bins=50, grid=True)
plt.axis([0, 26, 0, 30])
plt.xticks([x for x in range(1, 30) if x % 5 == 0])
ax1.set_xlabel('房龄')
ax1.set_ylabel('个数')

ax2 = ax1.twinx()
ax2 = train['year'].plot(kind = 'kde', color='orange', label='概率密度分布')
ax2.set_yticks([])
ax2.set_ylabel('')
plt.legend()

plt.savefig('year.pdf')
plt.show()

ax1 = train['size'].plot(kind = 'hist', bins=50, grid=True)
plt.axis([0, 350, 0, 28])
plt.xticks([x for x in range(1, 400) if x % 50 == 0])
ax1.set_xlabel('建筑面积')
ax1.set_ylabel('个数')

ax2 = ax1.twinx()
ax2 = train['size'].plot(kind = 'kde', color='orange', label='概率密度分布')
ax2.set_yticks([])
ax2.set_ylabel('')
plt.legend()

plt.savefig('size.pdf')
plt.show()

def grade(mean, std):  # mean:平均值，std:标准差，用于计算等级分划
    ret = []
    ret.append([0, max(0, mean - std)]) # 5级
    ret.append([max(0, mean - std), mean]) # 4级
    ret.append([mean, mean + std]) # 3级
    ret.append([mean + std, mean + std * 2]) # 2级
    ret.append([mean + std * 2, 1e5]) # 1级
    return ret
def print_grade(name, div):
    print('{}等级划分为: '.format(name), end='')
    for i in range(4):
        print('{:.2f}~{:.2f}, '.format(div[i][0], div[i][1]), end='')
    print('{:.2f}~infty'.format(div[4][0]))
show = lambda name: print_grade(name, grade(df[name].mean(), df[name].std()))

cols = ['size', 'metro', 'bus', 'school', 'hospital', 'shop', 'year']
for col in cols:
    show(col)

df.describe()

# 全部的数据都转化为等级
df = df.reset_index()
tmp = df.pop('index')
df_grade = convert(df.copy())
df_grade