python预处理练习数据

import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
# 读取数据
excel1= pd.DataFrame(pd.read_excel('第5章 练习数据.xls',sheet_name='Sheet1'))

#一，班级中以学号的班级填充
excel1_number=excel1["学号"]
for int1 in excel1_number:
    int2=int1//100%10;
    if(int2==1):{
        excel1.fillna({"班级":"大数据1班"},inplace=True)
    }
    elif(int2==2):{
        excel1.fillna({"班级":"大数据2班"},inplace=True)
    }
    elif(int2==3):{
        excel1.fillna({"班级":"大数据3班"},inplace=True)
    }
    else:
        {
        excel1.fillna({"班级":"大数据4班"},inplace=True)
        }
print("*******************************************以上一，班级中以学号的班级填充")
#二，性别操作
excel0_sex=excel1["性别"]
excel1["性别"].fillna(method="bfill",inplace=True)
#总数
count0=excel0_sex.count()
#性别异常
excel0_sex_set=set()
for i in excel0_sex:
    if(i!='男'or i!='女'):
        {
            excel0_sex_set.add(i)
        }
print(excel0_sex_set)
# 男女异常替换
for i in range(count0):
    if(excel0_sex.iloc[i]=='M' or excel0_sex.iloc[i]=='male'):
        {
          excel1["性别"].replace({excel1["性别"].iloc[i]:'男'},inplace=True)

        }
    elif(excel0_sex.iloc[i]=='F' or excel0_sex.iloc[i]=='female'):{
        excel1["性别"].replace({excel1["性别"].iloc[i]: '女'}, inplace=True)
    }

#异常检测前准备
def box_outliers(ser):
# 对需要检测的数据集进行排序
    new_ser = ser.sort_values()
# 判断数据的总数量是奇数还是偶数
    if new_ser.count() % 2 == 0:
# 分别计算Q3、Q1、IQR
        Q3 = new_ser[int(len(new_ser) / 2):].median()
        Q1 = new_ser[:int(len(new_ser) / 2)].median()
    elif new_ser.count() % 2 != 0:
        Q3 = new_ser[int((len(new_ser)-1) / 2+1):].median()
        Q1 = new_ser[:int((len(new_ser)-1) / 2)].median()
    IQR = round(Q3 - Q1, 1)
    rule = (round(Q3+1.5 * IQR, 1)<ser) | (round(Q1-1.5 * IQR, 1) > ser)
    index = np.arange(ser.shape[0])[rule]
# 获取包含异常值的数据
    outliers = ser.iloc[index]
    return outliers
print("*******************************************************************以上二，性别操作")
#三，身高男 ffill 女 ffill
excel1_hight=excel1["身高(cm)"]
excel1_hight.fillna(method="ffill",inplace=True)
#异常处理
# plt.rcParams['font.sans-serif'] = ['SimHei']
# estate = excel1
# box = estate.boxplot(column="身高(cm)")
# plt.show()
#男 0-151
#excel1_hight.iloc[:152]
# 保存异常值索引
set1=set(excel1["性别"])
print(set1)
outliers_index_list = []
for i in set(set1):
    estate = excel1[excel1["性别"].values == i]
    outliers_index = box_outliers(estate["身高(cm)"])
    if len(outliers_index) != 0:
# 将异常值的索引添加到定义的列表中
        outliers_index_list.append(outliers_index.index.tolist())
# 此时的outliers_index_list为嵌套列表，将其转换为单层列表
print(outliers_index_list)

#替换身高异常
replace_data=excel1["身高(cm)"].replace({1.72:172,1.85:185,1.67:167,1.60:160,1.68:168},inplace=True)
print("*****************************************************************以上三，身高男 ffill 女 ffill")


#四，体重 男 ffill 女 ffill
excel1_weight=excel1["体重(kg)"]
excel1_weight.fillna(method="ffill",inplace=True)

set1=('男','女')
outliers_index_list = []
for i in set(set1):
    estate = excel1[excel1["性别"].values == i]
    outliers_index = box_outliers(estate["体重(kg)"])
    if len(outliers_index) != 0:
# 将异常值的索引添加到定义的列表中
        outliers_index_list.append(outliers_index.index.tolist())
# 此时的outliers_index_list为嵌套列表，将其转换为单层列表
print(outliers_index_list)
#替换体重异常
#替换身高异常
replace_data=excel1["体重(kg)"].replace({7.5:75,730.0:73,4.5:45,6.0:60,450.0:45},inplace=True)
print("*************************************************************************以上四，体重 男 ffill 女 ffill")


#五，BMI填充
#1.空值检测
excel1_bmi=excel1[excel1["BMI（体重(kg)/身高(m)²）"].isna()==True]
print(excel1_bmi)
#空值数量
count1=excel1_bmi["学号"].count()
print(count1)
#填充
# 体质指数(BMI)=体重（千克）/身高（米）²
for i in range(count1):
    excel1_bmi_hight=(excel1_bmi["身高(cm)"].iloc[i]*0.01)**2
    excel1_bmi_weight=excel1_bmi["体重(kg)"].iloc[i]
    bmi=np.round(excel1_bmi_weight/excel1_bmi_hight,1)
    number=excel1_bmi["学号"].iloc[i]
    for a in excel1['学号']:
        if(a==number):{
            excel1.fillna({"BMI（体重(kg)/身高(m)²）":bmi},inplace=True)
        }
#空值再次检测
excel2_bmi=excel1[excel1["BMI（体重(kg)/身高(m)²）"].isna()==True]
print(excel2_bmi)


# 2.异常值检测
print("----------------异常值索引")
print(box_outliers(excel1["BMI（体重(kg)/身高(m)²）"]))

outliers_index = box_outliers(excel1["BMI（体重(kg)/身高(m)²）"])
outliers_index_list=outliers_index.index.tolist()
print(outliers_index_list)
for i in outliers_index_list:
    excel1_hight=(excel1["身高(cm)"].loc[i]*0.01)**2
    excel1_weight=excel1["体重(kg)"].loc[i]
    bmi=np.round(excel1_weight/excel1_hight,1)
    for a in excel1.index:
        if(a==i):{
            excel1.replace({excel1["BMI（体重(kg)/身高(m)²）"].loc[i]:bmi},inplace=True)
        }
#异常值再次检测，可得伪异常
print(box_outliers(excel1["BMI（体重(kg)/身高(m)²）"]))
print("***********************************************************************以上五，BMI填充")
#体测成绩
#异常检测
print(box_outliers(excel1["体测成绩"]))
replace_data=excel1["体测成绩"].replace({-71:71,-89:89,10:100},inplace=True)
#异常值再次检测
print(box_outliers(excel1["体测成绩"]))
print(excel1.info())
excel1.to_csv('final.csv', encoding = 'utf_8_sig')
pd.read_csv('final.csv')

当我拾起python,啊！，忘记，忘记