数据预处理

本页目录

数据预处理是对数据中的特殊数据进行处理，包括重复数据、缺失值和异常值的处理。进行统计分析时，常要求数据满足一定的要求，不满足时对数据进行转换处理，这也是数据预处理的内容。[大谦Excel，dqexcel点com]

数据去重

由于各种原因，数据中可能会出现重复数据。可以使用Excel函数、字典、Power Query和Python等多种方法删除重复数据。

一、使用Excel函数去重

1.4.8小节介绍了使用COUNTIF函数找到数据中的重复行，然后用Range对象的Delete方法删除重复行。请参阅。

二、使用字典去重

字典中的键在整个字典中必须是唯一的。利用字典的这个性质，可以对数据去重。图9-1中为各部门人员的身份证号信息。观察发现，工作表中工号1002和1008的人员信息有重复，现用Excel VBA和Python xlwings使用字典进行去重处理。

Document Image

图9-1 根据工号对数据去重

【Excel VBA】

Excel VBA中使用字典，需要首先引用相关的库，请参见第8章的介绍。创建字典时，字典中键值对的键由第1列的工号组成，值由它对应的其他各列的数据组成，这样构造4个字典。因为字典中的键是唯一的，所以字典构造完成以后，字典中的键和键对应的这组数据是唯一的，达到了去重的目的。示例文件的存放路径为Samples\ch21\Excel VBA\身份证号-去重.xlsm。

code.vba 
Sub 去重()
  Dim intI As Integer
  Dim arr
  Dim dicT1 As New Dictionary
  Dim dicT2 As New Dictionary
  Dim dicT3 As New Dictionary
  Dim dicT4 As New Dictionary

  '获取数据
  arr = Range("A1", Cells(Rows.Count, "E").End(xlUp))
  For intI = 1 To UBound(arr)    '构造字典，去重
    dicT1(arr(intI, 1)) = arr(intI, 2)
    dicT2(arr(intI, 1)) = arr(intI, 3)
    dicT3(arr(intI, 1)) = arr(intI, 4)
    dicT4(arr(intI, 1)) = arr(intI, 5)
  Next
  '输出去重后的数据
  [G1].Resize(dicT1.Count) = Application.Transpose(dicT1.Keys)  '工号
  [H1].Resize(dicT1.Count) = Application.Transpose(dicT1.Items)  '部门
  [I1].Resize(dicT1.Count) = Application.Transpose(dicT2.Items)  '姓名
  [J1].Resize(dicT1.Count) = Application.Transpose(dicT3.Items)  '身份证号
  [K1].Resize(dicT1.Count) = Application.Transpose(dicT4.Items)  '性别
End Sub

运行过程，在工作表的G-K列输出去重后的数据。

Document Image

图9-2 去重后的结果

【Python xlwings】

对图9-1中工作表的数据进行去重处理。创建字典，字典中的键值对，键由第1列的工号组成，值由它对应的行数据组成。使用字典对象的keys方法可以获取当前所有的键。添加键值对时如果键已经存在，则不添加，否则添加。这样，最后得到的所有键值对的值就是去重后的数据。脚本文件的存放路径为Samples\ch21\Python\身份证号-去重.py。

code.python 
import xlwings as xw
import os
root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/身份证号-去重.xlsx',read_only=False)
sht=wb.sheets(1)
rng=sht.range('A1', sht.cells(sht.cells(1,'B').end('down').row, 'E'))
dd={}  #创建字典dd
for i in range(rng.rows.count):  #遍历行数据
    if sht[i,0].value not in dd.keys():  #如果dd的键中不包括该行工号
        dd[sht[i,0].value]=rng.rows(i+1).value  #添加行数据到字典的值
lst=list(dd.values())  #字典的值转成列表
sht.range('G1').options(expand='table').value=lst  #列表数据写入工作表

运行脚本，去重后的数据如图9-2所示。

三、使用Power Query和pandas去重

数据量比较大的时候用Power Query或pandas进行处理(处理中小数据也可以)。此处介绍使用pandas DataFrame对象的drop_duplicates方法给数据去重。

下面的Python脚本文件用pandas打开当前路径下的Excel文件“身份证号-去重.xlsx”。使用pandas包的read_excel方法导入该文件中的数据，然后用DataFrame对象的drop_duplicates方法删除重复数据，用keep参数指定保留重复数据中的第1条数据，设置ignore_index参数的值为True，重排行索引编号。脚本文件的存放路径为Samples\ch21\Python\身份证号-去重2.py。

code.python 
import pandas as pd
import os
root = os.getcwd()
df=pd.read_excel(io=root+r'\身份证号-去重.xlsx',engine='openpyxl')
df2=df.drop_duplicates(subset=['工号'], keep='first', ignore_index=True)
print(df2)

运行脚本，在Python Shell窗口输出查询结果。

code.python 
>>> = RESTART: …\基础篇\Samples\ch21\Python\身份证号-去重2.py
     工号   部门  姓名                身份证号 性别
0  1001  财务部  陈东  5103211978100300**  男
1  1002  财务部  田菊  4128231980052512**  女
2  1008  财务部  夏东  1328011947050583**  男
3  1003  生产部  王伟  4302251980031135**  男
4  1004  生产部  韦龙  4302251985111635**  男
5  1005  销售部  刘洋  4302251980081235**  男
6  1006  生产部  吕川  3203251970010171**  男
7  1007  销售部  杨莉  4201171973021753**  女

得到去重后的结果。默认时生成新的DataFrame对象，设置inplace参数的值为True，不生成新对象，直接修改原数据df。

缺失值处理

数据采集过程中，由于条件受限无法采集到数据，或者采集到的数据遗失了，出现了数据缺失的情况，这就是缺失值。缺失值不是0，而是这个位置没有数据，是空的。数据中存在缺失值，会导致数据处理方法无法进行，所以必须先对缺失值进行处理，要么删除，要么用指定的值进行填充。

【Excel VBA】

1.5.7小节讲到，使用单元格区域对象的SpecialCells方法可以引用区域中的特殊单元格，这其中就包括为空的单元格。空白单元格的引用效果如图1-17所示。这是发现数据中缺失值的一种方式。

使用该方法，还可以将空白单元格用指定的值进行填充，比如指定为数据的均值或中值等。比如下面单元格区域对象的SpecialCells方法找到工作表已用区域中的空单元格，将它们的值指定为10。示例文件的存放路径为Samples\ch21\Excel VBA\缺失值.xlsm。

code.vba 
Sub MissingValues()
  Dim sht As Worksheet, rngN As Range

  Set sht = ActiveSheet
  '找到空单元格
  Set rngN = sht.UsedRange.SpecialCells(xlCellTypeBlanks)
  If Not rngN Is Nothing Then
    rngN.value = 10    '指定空单元格的值为10
  End If
End Sub

运行过程，生成图9-3。对比图1-17可以发现，原来为空的单元格现在都填充了数据10。

Document Image

图9-3 用固定值填充空单元格

如果想将空单元格的值指定为它周围某个单元格的值，需要通过循环结构来实现。判断单元格的值是否等于""可以判断该单元格是否为空。

如果希望删除有区域中有空单元格的行或列，使用下面的代码。示例文件的存放路径为Samples\ch21\Excel VBA\缺失值.xlsm。

code.vba 
Sub MissingValues2()
  '删除有空单元格的行
  Dim sht As Worksheet, rngN As Range

  Set sht = ActiveSheet
  '找到空单元格
  Set rngN = sht.UsedRange.SpecialCells(xlCellTypeBlanks)
  If Not rngN Is Nothing Then  '如果是空单元格
    rngN.EntireRow.Delete  '删除该行
    'rngN.EntireColumn.Delete  '删除该列
  End If
End Sub

【Python xlwings】

编写脚本，用Python xlwings将指定区域内的空单元格用数据10进行填充。脚本文件的存放路径为Samples\ch21\Python\缺失值.py。

code.python 
import xlwings as xw
import os
root = os.getcwd()
app = xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/缺失值.xlsx',read_only=False)
sht=wb.sheets(1)  #获取工作表
#获取空单元格
rng=sht.api.Range('A1').CurrentRegion.\
          SpecialCells(xw.constants.CellType.xlCellTypeBlanks)
if not rng is None:
    rng.Value=10  #用10填充

运行脚本，生成图9-3。

如果删除含空单元格的行，使用下面的语句行：

code.python 
#获取空单元格
rng=sht.api.Range('A1').CurrentRegion.\
          SpecialCells(xw.constants.CellType.xlCellTypeBlanks)
if not rng is None:
    rng.EntireRow.Delete()  #删除包含空单元格的行
    #rng.EntireColumn.Delete()  #删除包含空单元格的列

【Python pandas】

pandas的DataFrame对象提供了一些方法查找和处理数据中的缺失值。用isnull方法查看是否有缺失值，用dropna方法删除缺失值所在的行或列，用fillna方法对缺失值进行填充。脚本文件的存放路径为Samples\ch21\Python\缺失值2.py。含有缺失值的数据如图9-4所示。

Document Image

图9-4 有缺失值的数据

用DataFrame对象的isnull方法查找缺失值。

code.python 
import pandas as pd
import os
root = os.getcwd()
df=pd.read_excel(io=root+r'\缺失值2.xlsx',engine='openpyxl')
df2=df.isnull()
print(df2)

运行脚本，在Python Shell窗口输出结果：

code.python 
>>> = RESTART: …\基础篇\Samples\ch21\Python\缺失值2.py
        A      B      C      D
0   False  False  False  False
1   False  False   True  False
2   False   True  False  False
3   False  False  False  False
4   False  False  False   True
5   False  False  False  False
6    True  False  False  False
7   False  False   True  False
8   False  False  False   True
9   False   True  False  False
10  False  False  False  False

上面结果中，缺失值对应的值是True，非缺失值对应的是False。

用DataFrame对象的dropna方法删除含有缺失值的行。

code.python 
df3=df.dropna(how='any')

how参数的值为'any'，表示只要行中有一个缺失值，就删除整行。

用DataFrame对象的fillna方法填充缺失值。下面的语句用10填充所有缺失值。

code.python 
df4=df.fillna(10)

下面的语句用每一列的均值填充该列的缺失值。

code.python 
df5=df.fillna({'A':df['A'].mean(),'B':df['B'].mean(),\
            'C':df['C'].mean(),'D':df['D'].mean()})

下面的语句用缺失值下方的值填充缺失值。

code.python 
df6=df.fillna(method='backfill')

异常值处理

异常值是由于某种原因造成的数据中出现的统计上过大或过小的值，将它们纳入数据分析，会影响分析结果。判断一个值是否异常值，有各种不同的算法。这里介绍比较常用的两种。

第1种方法是使用数据的均值和标准差进行判断，如果数据落在[均值-3*标准差, 均值+3*标准差]范围外，认为数据是异常值，否则不是。第2种方法使用分位数进行判断。0.75分位数减去0.25分位数得到数据的内四分极值，如果数据落在[0.25分位数-1.5*内四分极值, 0.75分位数+1.5*内四分极值]范围外，认为数据是异常值，否则不是。后一种方法也是用箱形图判断异常值的方法。

对于判断为异常值的数据，常常将它作为缺失值进行处理，删除或指定为特殊的值。

下面介绍用Excel函数、Excel VBA, Python xlwings和Python pandas进行异常值查找和处理的方法。对于图9-5工作表中第1列的数据，查找异常值并进行处理。

【Excel】

示例文件的存放路径为Samples\ch21\Excel函数\异常值.xlsx。

Document Image

图9-5 用Excel函数和箱形图查找异常值

使用第1种方法，即用数据的均值和标准差进行查找，在B1单元格输入下面的公式：

=OR($A1<AVERAGE($A$1:$A$14)-3*STDEV($A$1:$A$14),$A1>AVERAGE($A$1:$A$14)+3*STDEV($A$1:$A$14))

其中，AVERAGE函数计算数据的均值，STDEV函数计算数据的标准差。回车，单元格中显示结果为FALSE，说明A1单元格中的数据不是异常值。双击单元格右下角的圆点，向下复制和填充公式，得到其他数据的判断结果，发现数据326被判断为异常值。

使用第2种方法，即用数据的分位数进行查找，在C1单元格输入下面的公式：

=OR($A1<PERCENTILE.EXC($A$1:$A$14,0.25)-1.5*(PERCENTILE.EXC($A$1:$A$14,0.75)-PERCENTILE.EXC($A$1:$A$14,0.25)),$A1>PERCENTILE.EXC($A$1:$A$14,0.75)+1.5*(PERCENTILE.EXC($A$1:$A$14,0.75)-PERCENTILE.EXC($A$1:$A$14,0.25)))

其中，PERCENTILE.EXC函数计算数据的分位数，参数指定数据范围和分位数的位置。0.75分位数减去0.25分位数得到数据的内四分极值。回车，单元格中显示结果为FALSE，说明A1单元格中的数据不是异常值。双击单元格右下角的圆点，向下复制和填充公式，得到其他数据的判断结果，发现数据3, 104和326被判断为异常值。算法不同，计算结果有差异。

选定A列数据后，在工作表中插入箱形图，如图9-5中所示。箱形图中间箱体的上下界表示数据的0.75分位数和0.25分位数，向外扩展1.5*内四分极值的距离得到上下两个触须。触须之外的点即为异常值点。

【Excel VBA】

在Excel VBA中可以调用Excel函数处理异常值。示例文件的存放路径为Samples\ch21\Excel VBA\异常值.xlsm。

过程Test用均值和标准差查找异常值。

code.vba 
Sub Test()
  '用均值和标准差查找异常值
  Dim intI As Integer
  Dim sngMean As Single
  Dim sngSTDEV As Single
  '均值
  sngMean = Application.WorksheetFunction.Average(Range("A1:A14"))
  '标准差
  sngSTDEV = Application.WorksheetFunction.StDev(Range("A1:A14"))
  '遍历每个数据，如果<(均值-3*标准差)或>(均值+3*标准差)，异常
  For intI = 1 To 14
    If Cells(intI, 1) < sngMean - 3 * sngSTDEV Or Cells(intI, 1) > _
            sngMean + 3 * sngSTDEV Then
      Cells(intI, 2).Value = True
    Else
      Cells(intI, 2).Value = False
    End If
  Next
End Sub

过程Test2用分位数查找异常值。

code.vba 
Sub Test2()
  '用分位数查找异常值
  Dim intI As Integer
  Dim sngP25 As Single
  Dim sngP75 As Single
  Dim sngIQR As Single
  '0.75分位数
  sngP75 = Application.WorksheetFunction.Percentile(Range("A1:A14"), 0.75)
  '0.25分位数
  sngP25 = Application.WorksheetFunction.Percentile(Range("A1:A14"), 0.25)
  '内四分极值
  sngIQR = sngP75 - sngP25
  '遍历每个数据，如果<(0.25分位数-1.5*内四分极值)或
  '>(0.75分位数+1.5*内四分极值)，异常
  For intI = 1 To 14
    If Cells(intI, 1) < sngP25 - 1.5 * sngIQR Or Cells(intI, 1) > _
            sngP75 + 1.5 * sngIQR Then
      Cells(intI, 3).Value = True
    Else
      Cells(intI, 3).Value = False
    End If
  Next
End Sub

运行两个过程，分别在工作表中B列和C列输出判断结果。

【Python xlwings】

下面在Python中结合xlwings包调用Excel函数查找数据的异常值。

使用均值和标准差进行判断。脚本文件的存放路径为Samples\ch21\Python\异常值-xlwings-1.py。

code.python 
import xlwings as xw
import os
root=os.getcwd()
app=xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/异常值.xlsx',read_only=False)
sht=wb.sheets(1)
#计算均值
mean_v=app.api.WorksheetFunction.Average(sht.api.Range('A1:A14'))
#计算标准差
stdev_v=app.api.WorksheetFunction.StDev(sht.api.Range('A1:A14'))
#遍历每个数据，如果<(均值-3*标准差)或>(均值+3*标准差)，异常
for i in range(1,15):
    if sht.api.Cells(i,1).Value<mean_v-3*stdev_v or\
             sht.api.Cells(i,1).Value>mean_v+3*stdev_v:
        sht.api.Cells(i,2).Value=True
    else:
        sht.api.Cells(i,2).Value=False

使用分位数进行判断。脚本文件的存放路径为Samples\ch21\Python\异常值-xlwings-2.py。

code.python 
import xlwings as xw
import os
root=os.getcwd()
app=xw.App(visible=True, add_book=False)
wb=app.books.open(root+r'/异常值.xlsx',read_only=False)
sht=wb.sheets(1)
#计算0.75分位数
stp75=app.api.WorksheetFunction.Percentile(sht.api.Range('A1:A14'),0.75)
#计算0.25分位数
stp25=app.api.WorksheetFunction.Percentile(sht.api.Range('A1:A14'),0.25)
#计算内四分极值
iqr=stp75-stp25
#遍历每个数据，如果<(0.25分位数-1.5*内四分极值)或
#>(0.75分位数+1.5*内四分极值)，异常
for i in range(1,15):
    if sht.api.Cells(i,1).Value<stp25-1.5*iqr or\
             sht.api.Cells(i,1).Value>stp75+1.5*iqr:
        sht.api.Cells(i,3).Value=True
    else:
        sht.api.Cells(i,3).Value=False

运行两个脚本，两种方法的判断结果输出到工作表的第2列和第3列。

【Python pandas】

Python的pandas包提供了计算均值、标准差和分位数的函数，下面使用pandas包在数据中查找异常值。

使用均值和标准差进行判断，pandas中用序列对象的mean函数和std函数计算均值和标准差。脚本文件的存放路径为Samples\ch21\Python\异常值-pandas-1.py。

code.python 
import pandas as pd
import numpy as np
import os
root = os.getcwd()
df=pd.read_excel(io=root+r'\异常值2.xlsx',engine="openpyxl")
#计算均值
mean_v=df['A'].mean()
#计算标准差
stdev_v=df['A'].std()
#数据
data=df['A']
#输出异常值
print(data[(data>mean_v+3*stdev_v) | (data<mean_v-3*stdev_v)])
#处理异常值，换成缺失值
data[(data>mean_v+3*stdev_v) | (data<mean_v-3*stdev_v)]=np.nan
print(data)

使用分位数进行判断，pandas中用序列对象的quantile函数计算分位数。脚本文件的存放路径为Samples\ch21\Python\异常值-pandas-2.py。

code.python 
import pandas as pd
import numpy as np
import os
root = os.getcwd()
df=pd.read_excel(io=root+r'\异常值2.xlsx',engine="openpyxl")
#计算0.75分位数
stp75=df['A'].quantile(0.75)
#计算0.25分位数
stp25=df['A'].quantile(0.25)
#计算内四分极值
iqr=stp75-stp25
#数据
data=df['A']
#输出异常值
print(data[(data>stp75+1.5*iqr) | (data<stp25-1.5*iqr)])
#处理异常值，换成缺失值
data[(data>stp75+1.5*iqr) | (data<stp25-1.5*iqr)]=np.nan
print(data)

分别运行两个脚本，在Python Shell窗口输出异常值和将异常值处理为缺失值后的结果。

Python中使用Matplotlib包绘制箱形图。编写脚本，脚本文件的存放路径为Samples\ch21\Python\箱形图.py。

code.python 
import pandas as pd
import matplotlib.pyplot as plt
import os
root = os.getcwd()
df=pd.read_excel(io=root+r'\异常值2.xlsx',engine="openpyxl")
plt.boxplot(df['A'])
plt.show()

运行脚本，生成箱形图如图9-6所示。

Document Image

图9-6 用Matplotlib生成箱形图

数据转换

对数据进行统计分析时，为了消除量纲和量级的影响，或者为了满足统计方法对数据要求，经常需要在统计分析之前度数据进行转换。常见的数据转换方法有对数转换、平方根转换、反正弦转换、中心化、标准化和归一化等。这里主要介绍后面3种。

中心化是将数据点向中心点平移，算法比较简单，将每个数据减去它们的均值即可。

标准化则使数据变换后服从标准正态分布，其作用是消除量纲和量级的影响，使得表示样本的多个指标具有相同的尺度。标准化的算法是将每个数据减去它们的均值后除以标准差。

归一化是将所有数据转换到0和1之间。算法是将每个数减去数据最小值得到的差除以数据的极差。极差用数据的最大值减去最小值得到。

对于图9-7所示工作表第1列的数据，分别用中心化、归一化和标准化进行转换。

Document Image

图9-7 数据转换

【Excel】

示例文件的存放路径为Samples\ch21\Excel函数\数据转换.xlsx。

在工作表中，在B2单元格中输入公式：

=$A2-AVERAGE($A$2:$A$85)

回车，双击B2单元格右下角圆点，得到中心化数据的结果如图9-7工作表中第2列所示。

在工作表中C2单元格中输入公式：

=($A2-MIN($A$2:$A$85))/(MAX($A$2:$A$85)-MIN($A$2:$A$85))

回车，双击C2单元格右下角圆点，得到归一化数据的结果如图9-7工作表中第3列所示。

在工作表中D2单元格中输入公式：

=STANDARDIZE($A2,AVERAGE($A$1:$A$85),STDEV($A$1:$A$85))

回车，双击D2单元格右下角圆点，得到标准化化数据的结果如图9-7工作表中第4列所示。

【Excel VBA, Python】

仿照9.3.3小节，可在Excel VBA, Python xlwings和Python pandas环境下实现对应的数据转换方法，不再赘述。[大谦Excel，dqexcel点com]