利用pandas 库进行数据清洗——实战练习

这周的计划是用之前看过的pandas模块对具体数据做数据清洗并做数据探索。以前习惯于用excel，不管是数据透视表还是power query，其实都比较便捷化/结构化，自然也就缺乏一定的灵活性（excel公式的灵活个人感觉在数据清洗上显得很捉襟见肘，没有可持续性，也容易出错）。

pandas模块是python中（主要）用于数据处理的第三方模块，本次就先完成清洗部分的任务，数据探索会放在下一篇文章中。

• 目标需求：
  获得国开部门所有在服学员考试通过科目的情况。

• 目标字段：
  学员ID 学员姓名 一级项目 二级项目 支付金额 花名 搭售情况 搭售金额 搭售班型 搭售家族 科目 分数 考期

• 数据底表准备：
  表1： 学员大表（3）——基准表，作为信息匹配的标准
  表2：产品搭售底表（1）——匹配表之一，需要学员对应搭售的信息
  表3：1810科目等级&通过率（1）——匹配表之二，需要学员通过科目的信息
  表4：考试通过科目—截止1217（1）——匹配表之三，需要学员1810考期之外的所有信息

• 工具：Jupyter
  涉及模块：pandas

• 思路：
  1、汇总学员大表，提取所需信息字段，得到表1；
  2、根据学员ID，将表2的搭售信息匹配到表1，获得学员搭售情况，得到汇总学员表（stu_ty）；
  3、观察表3、表4有效的共同字段，删除无关的字段，作为备用；
  4、提取处理后的表4中非1810考期的字段信息，并和表3 汇总，得到成绩表；
  5、以步骤2的汇总学员表为新的基准表，成绩表作为匹配表，将成绩表中学员通过科目和分数等信息匹配到汇总学员表。

• 具体步骤

数据清洗
导入所需要的库

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

读取数据

stu1=pd.DataFrame(pd.read_excel(r'D:/pylearn/OpenUniversity/学员大表.xls'))
stu2=pd.DataFrame(pd.read_excel(r'D:/pylearn/OpenUniversity/学员大表 (1).xls'))
stu3=pd.DataFrame(pd.read_excel(r'D:/pylearn/OpenUniversity/学员大表 (2).xls'))

思路 1

查看数据

stu2.info()

追加汇总三个表(因为三个表结构相同)

con_stu=pd.concat([stu1,stu2,stu3])
con_stu.info()

将汇总后的表格进行有效字段提取（.loc）

open_stu=con_stu.loc[:,["学员id","姓名","一级项目","二级项目","班型","支付金额","家族","花名"]]
open_stu.head()

致此，学员大表处理结束。现在开始处理搭售表。

思路 2

搭售表处理，首先提取有用字段；接着根据学员id进行信息匹配。

ty1=pd.DataFrame(pd.read_excel(r'D:/pylearn/OpenUniversity/国开搭售表.xlsx'))
ty1.head()

提取指定的五个字段，并将列名更改

ty2=ty1.loc[:,["学员ID","经营表(除泰罗专科).产品包名称","经营表(除泰罗专科).家族名称","经营表(除泰罗专科).流水"]]
ty3=ty2.rename(columns={"学员ID":"学员id","经营表(除泰罗专科).产品包名称":"搭售产品包名称","经营表(除泰罗专科).家族名称":"搭售家族名称","经营表(除泰罗专科).流水":"搭售流水"})
ty3.head()

将汇总好的学员大表和搭售表匹配：merge

stu_ty=pd.merge(open_stu,ty3,on="学员id")
stu_ty.head()

思路中的1, 2已经完成。现在处理表3、表4。注意：这两个表字段不一样，需要分别提取有效&相同的字段，再进行汇总和匹配。

思路3

还是导入数据

score1=pd.DataFrame(pd.read_excel(r'D:/pylearn/OpenUniversity/1810讲师科目等级&通过率.xlsx',"1810考期通过率情况"))
score2=pd.DataFrame(pd.read_excel(r'D:/pylearn/OpenUniversity/考试通过科目-截止1217.xlsx',"底表-1217通过科目全"))
score1.head()
score2.head()

分别查看一下列的内容
(注意，结尾木有()，我每次都输错。。)

score1.columns
score2.columns

Index(['学院', '家族', '小组', '学员ID', '子订单id', '科目名称', '省份', '考期', '学员ID&科目名称',
'分数', '是否通过'],
dtype='object')

Index(['科目', '分数', '考期', '用户Id', '姓名', '家族', '子订单ID', '准考证号'], dtype='object')

可以看到两个表中存在属性相同的列名不同。所以要改成相同。同时添加一列是否通过（这里的数据都是通过学员的数据。添加该列数据是为了接下来与表合并，用于统计汇总）

score3=score2.rename(columns={'用户Id':"学员ID"})
score3["是否通过"]="是"
score3.head()

提取有效字段

score4=score3.loc[:,["学员ID",'科目', '分数', '考期','是否通过']]

选择考期不是1810的数据/删除1810考期数据（1810的汇总数据是另一份，待合并）

score5=score4[score4['考期']!=201810]
score5.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 43830 entries, 0 to 74111
Data columns (total 5 columns):
学员ID 43578 non-null float64
科目 43830 non-null object
分数 34615 non-null float64
考期 43830 non-null int64
是否通过 43830 non-null object
dtypes: float64(2), int64(1), object(2)
memory usage: 2.0+ MB

可以看到学员id和分数都有空值。现在删除id为空的行。
不知为何，不能用dropna，只能用笨办法，找到空值填充12306，然后根据12306所在的索引位置删除行。真的很笨呐。。

score5['学员ID'].isnull().value_counts()
score5["学员ID"]=score5["学员ID"].fillna('12306')
score5[(score5.学员ID=="12306")].index.tolist()

score6=score5.drop([
73860,
 73861,
 73862,
....
 74109,
 74110,
 74111])
score6.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 43578 entries, 0 to 73859
Data columns (total 5 columns):
学员ID 43578 non-null object
科目 43578 non-null object
分数 34363 non-null float64
考期 43578 non-null int64
是否通过 43578 non-null object

现在已经没有空值了。
观察score6的字段提取score1中的字段，注意，列名不同的改为相同

score1=score1.loc[:,['学员ID','科目名称','分数','考期',"是否通过"]]
score1.head()

学员ID 科目名称 分数 考期 是否通过
0 1001361 法学概论 40 1810 否
1 1001361 管理心理学 54 1810 否
2 1001361 社会保障学 未参加 1810 未参加
3 1001361 社会研究方法 53 1810 否
4 1001723 马克思主义基本原理概论 未参加 1810 未参加

可以看到，分数列不全是数字，有中文字符。为后续计算需要，这里把‘未参加’替换为0.
注意：replace中参数inplace=true，否则不能实现本地替换

score8=score1.rename(columns={"科目名称":'科目'})
score8['分数'].replace("未参加",0,inplace=True)   #尝试多次失败，因为之前没有指定参数 inplace=True
score8['分数'].astype('int')

con_score=pd.concat([score8,score6])
con_score.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 144938 entries, 0 to 73859
Data columns (total 5 columns):
学员ID 144938 non-null object
科目 144938 non-null object
分数 135723 non-null float64
考期 144938 non-null int64
是否通过 144938 non-null object
dtypes: float64(1), int64(1), object(3)
memory usage: 6.6+ MB

现在已经把成绩表汇总整理好了。接下来进行最后的匹配。

思路5

score=con_score.rename(columns={'学员ID':'学员id'})
stu_ty.describe()  
score.describe()

分数 考期
count 135723.000000 144938.000000
mean 28.185672 61780.039458
std 31.463908 91489.245227
min 0.000000 1810.000000
25% 0.000000 1810.000000
50% 0.000000 1810.000000
75% 61.000000 201801.000000
max 97.000000 201804.000000

可以看到，这里的id并不是int，所以待会没办法匹配。因此首先需要对id进行格式转换。

score['学员id']=score['学员id'].astype('int',inplace=True) 

之前没有写=前面的赋值，所以改了类型也一直不成功
现在可以进行匹配了：merge

con_table=pd.merge(stu_ty,score,on="学员id")

con_table.head()

data clean .png

致此，表格清洗部分已经完成。