数据整理

本页目录

数据整理是在文件这个层面上对导入的数据进行处理，包括行、列数据的添加、移动和删除等，以及数据排序、筛选、合并、拼接等操作。[大谦Excel，dqexcel点com]

使用对象模型进行数据整理

使用Excel VBA和Python xlwings，可以将数据导入到Excel工作表中并使用工作表对象和单元格对象提供的方法进行行、列的复制、移动、插入和删除等，可以对数据进行排序、过滤等操作，实现数据整理。这部分内容请参见第4章和第13章的介绍。可以用数据图形、图表和透视表等处理数据，请参见第5-19章的介绍。

使用Excel函数进行数据整理

使用Excel, Excel VBA和Python xlwings，可以调用Excel函数处理数据。这部分内容请参见第16章的介绍。

使用Power Query和pandas进行数据整理

Power Query和pandas都是为处理大型数据设计的，它们都不能操作Excel对象模型，因为数据量很大时在展示在Excel工作表里面是没什么意义的，管中窥豹而已。而且在工作表中加载卸载数据都需要时间，会影响处理数据的速度。

Power Query和pandas都提供了很多进行行操作、列操作、数据合并、拆分、排序、过滤等操作的工具或函数，使用它们，可以轻松实现数据的整理。9.1.2小节介绍了一个用pandas的contact函数进行数据拼接的例子，下面再介绍一个数据筛选的例子。

进行数据处理时，有时只需要原始数据中的一部分数据。用pandas包的read_excel方法时，使用usecols, skiprows, nrows, skip_footer, sheet_name等参数可以有选择地导入部分数据。对于导入后的数据，可以使用布尔索引进行筛选。

下面的Python脚本文件用pandas打开当前路径下的Excel文件“各科室人员.xlsx”。使用pandas包的read_excel方法导入该文件中指定列的数据。脚本文件的存放路径为Samples\ch21\Python\各科室人员.py。

code.python 
import pandas as pd
import os
root = os.getcwd()
df=pd.read_excel(io=root+r'\各科室人员.xlsx',\
           usecols=['编号','性别','年龄','科室','工资'],\
           engine='openpyxl')
print(df)

运行脚本，在Python Shell窗口输出选定的数据。

code.python 
>>> = RESTART: …\基础篇\Samples\ch21\Python\各科室人员.py
      编号 性别  年龄   科室    工资
0  10001  女  45  科室2  4300
1  10002  女  42  科室1  3800
2  10003  男  29  科室1  3600
3  10004  女  40  科室1  4400
4  10005  男  55  科室2  4500
5  10006  男  35  科室3  4100
6  10007  男  23  科室2  3500
7  10008  男  36  科室1  3700
8  10009  男  50  科室1  4800

选择女性工作人员的数据。在脚本中添加下面的语句行：

code.python 
df2=df[df['性别']=='女']
print(df2)

运行脚本，在Python Shell窗口输出选定的数据。

code.python 
>>> = RESTART: …\基础篇\Samples\ch21\Python\各科室人员.py
      编号 性别  年龄   科室    工资
0  10001  女  45  科室2  4300
1  10002  女  42  科室1  3800
3  10004  女  40  科室1  4400

选择工资大于4000并且年龄小于等于40岁的工作人员的数据。在脚本中添加下面的语句行：

code.python 
df3=df[(df['工资']>4000) & (df['年龄']<=40)]
print(df3)

运行脚本，在Python Shell窗口输出选定的数据。

code.python 
>>> = RESTART: …\基础篇\Samples\ch21\Python\各科室人员.py
      编号 性别  年龄   科室    工资
3  10004  女  40  科室1  4400
5  10006  男  35  科室3  4100

也可以使用DataFrame对象的where方法筛选数据，该方法也是基于布尔索引来实现。下面筛选年龄大于等于35岁的工作人员的数据。在脚本中添加下面的语句行：

code.python 
df4=df.where(df['年龄']>=35)
print(df4)

运行脚本，在Python Shell窗口输出选定的数据。

code.python 
>>> = RESTART: …\基础篇\Samples\ch21\Python\各科室人员.py
        编号   性别    年龄   科室      工资
0  10001.0    女  45.0  科室2  4300.0
1  10002.0    女  42.0  科室1  3800.0
2      NaN  NaN   NaN  NaN     NaN
3  10004.0    女  40.0  科室1  4400.0
4  10005.0    男  55.0  科室2  4500.0
5  10006.0    男  35.0  科室3  4100.0
6      NaN  NaN   NaN  NaN     NaN
7  10008.0    男  36.0  科室1  3700.0
8  10009.0    男  50.0  科室1  4800.0

可见，默认时，where方法将不匹配的数据用NaN代替，即置空。可以用other参数指定一个替换值。

使用SQL整理数据

Python中可以使用pandasql包处理DataFrame数据。安装pandasql，首先在Windows菜单中的“附件”子菜单中单击“命令提示符”选项，打开DOS命令窗口，在提示符后面输入：

pip install –U pandasql

回车即可进行安装。

pandasql包中使用的主要函数是sqldf，它有2个参数。第1个参数是进行查询的SQL语句，第2个参数指定环境变量，可以是locals()或globals()。

为了便于使用，常常用lambda定义一个匿名函数，这样使用时只需要指定SQL语句即可，即：

code.python 
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())
q=…  #从表df中进行查询的SQL语句
df2=pysqldf(q)

df2是执行查询后得到的表数据。

下面的Python脚本文件用pandas打开当前路径下的Excel文件“各科室人员.xlsx”。使用pandas包的read_excel方法导入该文件中的数据，然后用SQL查询提取出男性的全部资料。脚本文件的存放路径为Samples\ch21\Python\sql.py。

code.python 
import pandas as pd  #导入pandas
from pandasql import sqldf  #导入pandasql
import os  #导入os
root = os.getcwd()  #获取当前路径
#从指定文件中读取数据
df=pd.read_excel(io=root+r'\各科室人员.xlsx',engine='openpyxl')
pysqldf=lambda q:sqldf(q,globals())  #定义匿名函数
q="SELECT * FROM df WHERE 性别=&#x27;男'"  #SQL查询语句
df2=pysqldf(q)  #调用匿名函数，参数为查询语句，返回查询结果
print(df2)  #输出查询结果

运行脚本，在Python Shell窗口输出查询结果。[大谦Excel，dqexcel点com]

code.python 
>>> = RESTART: …\基础篇\Samples\ch21\Python\sql.py
      编号 性别  年龄  学历   科室 职务等级    工资
0  10003  男  29  博士  科室1  正处级  3600
1  10005  男  55  本科  科室2  副局级  4500
2  10006  男  35  硕士  科室3  正处级  4100
3  10007  男  23  本科  科室2   科员  3500
4  10008  男  36  大专  科室1   科员  3700
5  10009  男  50  硕士  科室1  正局级  4800