sklearn代码18 python自动化处理数据

import numpy as np

import pandas as pd

from pandas import Series,Dataframe

import matplotlib.pyplot as plt
%matplotlib inline

data = pd.read_excel('./18级高一体测成绩汇总.xls')
data

493 rows × 11 columns

data[:45]

cond = data['班级']!='班级'  # 删除多余的班级信息
data = data[cond]
data[:45]

data.fillna(0,inplace=True)  #没参加处理为0

C:UsersLXQAnaconda3libsite-packagespandascoreframe.py:2754: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a Dataframe

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  downcast=downcast, **kwargs)

# 没有空数据了
data.isnull().any()

班级       False
性别       False
姓名       False
1000米    False
50米      False
跳远       False
体前屈      False
引体       False
肺活量      False
身高       False
体重       False
dtype: bool

data.head()

def convert(x):    #将数据转换为成绩
    if isinstance(x,str):
        minute,second = x.split("'")
        minute = int(minute)
        second = int(second)
        return minute + second/100.0
    else:
        return x
    
data['1000米'] = data['1000米'].map(convert)

data.head()

C:UsersLXQAnaconda3libsite-packagesipykernel_launcher.py:10: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a Dataframe.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  # Remove the CWD from sys.path while we load stuff.

score = pd.read_excel('体侧成绩评分表.xls',header = [0,1])
score

20 rows × 23 columns

# 男生的成绩进行了转化
def convert(item):
    m,s = item.strip('"').split("'")
    m,s = int(m),int(s)
    return m + s/100.0
score.iloc[:,-4] = score.iloc[:,-4].map(convert)

# 女生成绩，进行转化
def convert(item):
    m,s = item.strip('"').split("'")
    m,s = int(m),int(s)
    return m + s/100.0
score.iloc[:,-2] = score.iloc[:,-2].map(convert)

score

20 rows × 23 columns

data.columns

Index(['班级', '性别', '姓名', '1000米', '50米', '跳远', '体前屈', '引体', '肺活量', '身高', '体重'], dtype='object')

data.columns = ['班级', '性别', '姓名', '男1000', '男50米跑', '跳远', '体前屈', '引体', '肺活量', '身高', '体重']

data['男50米跑'] = data['男50米跑'].astype(np.float)

C:UsersLXQAnaconda3libsite-packagesipykernel_launcher.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a Dataframe.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  """Entry point for launching an IPython kernel.

score['男1000']

for col in [ '男1000', '男50米跑']:
    #     获取成绩的标准
    s = score[col]
    
    def convert(x):
        for i in range(len(s)):
            if x <= s['成绩'].iloc[0]:
                if x == 0:
                    return 0   #没有参加这个项目
                return 100
            elif x > s['成绩'].iloc[-1]:
                return 0 #跑的太慢
            elif (x > s['成绩'].iloc[i - 1]) and (x <= s['成绩'].iloc[i]):
                return s['分数'].iloc[i]
    
    data[col + '成绩'] = data[col].map(convert)

C:UsersLXQAnaconda3libsite-packagesipykernel_launcher.py:16: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a Dataframe.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  app.launch_new_instance()

data.head()

for col in ['跳远', '体前屈', '引体']:
    s = score['男'+col]
    
    def convert(x):
        for i in range(len(s)):
            if x >= s['成绩'].iloc[i]:
                return s['分数'].iloc[i]
        return 0
    
    data[col + '成绩'] = data[col].map(convert)

C:UsersLXQAnaconda3libsite-packagesipykernel_launcher.py:10: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a Dataframe.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  # Remove the CWD from sys.path while we load stuff.

data.head()

cols = ['班级', '性别', '姓名', '男1000','男1000成绩', '男50米跑', '男50米跑成绩',
 '跳远', '跳远成绩', '体前屈', '体前屈成绩',  '引体', '引体成绩','肺活量','身高','体重']

# 根据索引的顺序去Dataframe中取值
data = data[cols]
data.head()

data

477 rows × 16 columns

def convert(x):
    if x > 100:
        return x/100
    else:
        return x
data['身高'] = data['身高'].map(convert)

data['BMI'] = (data['体重']/(data['身高'])**2).round(1)

'''≤16.4
23.3~26.3'''
def convert_bmi(x):   #在取名时可以更加准确比如取为convert_bmi
    if x >= 26.4:
        return 60
    elif (x <= 16.4) or (x >=23.3 and x <= 26.3):
        return 80
    elif x >=16.5 and x <=23.2:
        return 100
    else:
        return 0

data['BMI_score'] = data['BMI'].map(convert_bmi)

data.head(50)

# 统计分析
# 定义需求，画图，对比分析
(data['BMI_score'].value_counts()).plot(kind = 'pie',autopct = '%0.2f%%')

(data['BMI_score'].value_counts()).plot(kind = 'bar')

data.groupby(['男1000成绩'])['BMI_score'].count().plot(kind = 'bar')