python merge on多个条件_Python数据核对系列之2—power query VS pandas

提示：本文略长，干货满满上一篇文章我们整体梳理了我核对两个表数据，从excel query模式一版版过渡到python多线程多进程读取数据库数据后完成核对并输出结果到数据库表中的整个版本迭代过程。从本篇开始，我们挨个梳理讲解下，在整个过程中我的实际操作，遇到的各种坑，以及找到的解决方案。

Excel power query两表核对

利用query进行核对使用的是query里面的合并查询，其实也就是sql当中的左连接、右连接、全外连接。可以通过设定的连接字段来完成两表的合并。我们从数据-新建查询-从文件里选择从工作簿或者从csv。然后加载我们的源文件。

在选择csv文件加载的时候，有时候会因为原始数据的某个字段里包含换行符而导致加载完的数据会出现错行问题，导致你的数据格式设置会报错。这个问题的解决方案我们之前已经发过了，大家再遇到的话可以再查看一下。power query加载csv错行？

原始数据加载完后，我们选择query的主页里的组合中的合并查询

合并查询里有个合并查询、将查询合并为新查询

点击合并查询后，弹出合并设置窗口，选择两个原始表的查询，并点选连接字段。

如果是多个连接字段的话，需要按住ctrl点击字段，效果会如下图所示

选择完连接的字段之后，我们需要设置他们的连接种类。共有6种连接设置。上面的解释也很清楚明白。

选择连接方式之后，它会显示连接的匹配行数。

两表连接完成之后，关联的数据会存储在一新的列里

点击这个table，它会显示跟当前数据关联的数据明细

如果关联的数据有多条如下图所示

单条数据如下图所示

点击这个合并列的右上角的符号，可以对数据进行展开。

展开时可以选择展开(明细数据)，聚合(汇总数据)，还可以选择原始列名作为展开后的列名的前缀。

展开之后我们就可以针对要核对的列进行值比对了。

在这里我们引申一下，简单介绍下query函数

点击主页菜单里高级编辑器，或者点击右侧应用步骤里的源，我们可以看到在公式栏里的query函数。

在这里它使用的是Table.NestedJoin函数

https://docs.microsoft.com/zh-cn/powerquery-m/table-nestedjoin

在官方文档里的介绍如下

Table.NestedJoin(table1 as table,key1 as any,table2 as any,key2 as any,newColumnName as text, optionaljoinKind as nullable number, optionalkeyEqualityComparers as nullable list) as table

= Table.NestedJoin(BI, {"GID"}, ERP, {"GID"}, "ERP", JoinKind.RightOuter)

我们对比官方介绍和我们通过视窗操作自动生成的函数来了解这个函数。

table1 as table：选择表格1(也就是查询生成的结果表)，我们选择的是BI

key1 as any : 选择任意格式的关键字段。我们选择的是BI表里的GID字段。它需要用列表形式展示。{"GID"}，大括号就是query的列表

table2，key2同上

newColumnName as Text ：设置文本格式的新列名。它会把关联生成的数据存储在这个列里。我们的例子中就是ERP列。

optional joinKind as nullable number ：可选择的连接类型。连接的在上面的截图里我们介绍过了。这里我们在介绍下它的参数名称。在query函数列表里有下面这几个

• JoinKind.FullOuter ：全外连接
• JoinKind.Inner ： 内连接
• JoinKind.LeftAnti ： 左反
• JoinKind.LeftOuter ：左外连接
• JoinKind.RightAnti ：右反
• JoinKind.RightOuter ：右外连接

在上面的案例中，我们选择的是RightOuter。以后我们仔细介绍query的M函数，之前的多篇文章我们也有所涉及。在数据量相对不大的时候(大概300万内吧看电脑性能)，M函数真的很好用。比如上的Table函数，还有List函数、Text函数等，数据清洗利器！上周给一研究院的老同学用query计算了三组实验数据。每组50组个数，要先计算3组数据的笛卡尔积，然后再分别根据公式计算值。50*50*50笛卡尔积后的数据是12万行。 query 分分钟就完成了这组计算。而且后期数据如果发生变化，只需要修改源数据，然后点击刷新就立马生成最新结果。

pandas两表核对

上面说完了query的模式，下面我们聊聊python的数据分析库pandas。query好用是好用，但遇到千万级别的数据量就会慢到怀疑人生，它会多次加载，计算性能也会很慢。这时就得用python这种编程数据处理工具了。 Python、pandas安装关于python的安装、环境设置，大家可以百度即可。python免费，可以官网直接下载就行。现在流行使用的python版本是3.7.https://www.python.org/downloads/ 这是下载网址https://www.python.org/ftp/python/3.7.8/python-3.7.8-amd64.exe我使用的是3.7.8版本

安装完成之后在cmd命令窗口里输入python --version查看python版本

或者直接输入python，启用python

有兴趣的朋友也可以自行百度安装下Ipython、Jupyter NoteBook。尤其是Jupyter Notebook是非常好用的python工具。Ipython和Jupyter Notebook都可以带有代码自动补充功能。但原生python并没有。启用Ipython

启用Jupyter Notebook

Jupyter Notebook会在浏览器上打开，并在浏览器上使用。如下图我们生成2行5列的符合正态分布(均值为0，方差为1)的随机数。

pandas、numpy等库安装的话，可以从cmd窗口输入pip install pandas( 我的已经安装完毕 )numpy的话就是pip install numpy其他库的话就是pip install 库名称。其他关于pip的安装、更新操作大家可自行百度。

pandas文件读取https://pandas.pydata.org/pandas-docs/stable/user_guide/index.html这是pandas的官方文档。因为在python版1.0里使用的是从sql导出的csv或者Excel格式文件加载到pandas里，然后进行的数据核对，所以我们首先来说下如何读取csv、Excel格式文件。pandas文档左侧是目录，第一个主题便是IO工具，即输入输出工具。

比较主流文件类型基本都已经涵盖了。csv、json、html、excel、HDF5、stata、sas、spass、sql等等。读取文件的时候需要注意文件路径问题，路径中有中文字符的容易报错。我们先设置路径变量path path = r'C:\Users\Administrator\Desktop\test\BI.csv'用r的原因是路径中存在反斜杠\然后我们就可以调用pandas的read_csv来读取csv文件了。df = pd.read_csv(path)

当然如果文件格式是excel的话path_excel = r'C:\Users\Administrator\Desktop\test\BI.xlsx'df_excel = pd.read_excel(path_excel)

%time是Jupyter Notebook里的魔法函数，可以显示代码执行时间。如上图所示，excel的读取时间花了8.73s我们多执行几次看看平均时间。7次执行的平均时间是7.21s。

我们再看看读取csv格式文件的速度，7次读取的平均时间是174ms

虽然csv文件在 同样数据情况下，csv文件大小为13.6MB，而excel为8MB。但是csv的读取时间却是excel的1/50左右。 pandas的Merge方法

读取完数据之后我们如何去进行比较呢？

我们先来看看读取完的数据是什么类型的.

通过.info()方法可以看到数据显示为DataFrame格式。其实这正是我们用pandas的read方法读取数据的原因，它读取完就是DataFrame格式，而无需另行转换。

DataFrame可以简单理解为一个二维表，有行有列。

然后我们看看pandas文档对于merge方法的介绍，在第4章节里介绍的就是merge，join和concatenate。

concatenate可横向连接，也可纵向连接。功能强大，使用百变

merge是数据库风格的连接，也就是我们在query中介绍的合并查询的那几种。被介绍为类似数据库sql连接类型的功能强大、高性能的内存连接操作。

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None,left_index=False, right_index=False, sort=True,suffixes=('_x', '_y'), copy=True, indicator=False,validate=None)

上面这是merge方法的参数

left为左侧表，right为右侧表(其实也可以是series，DataFrame类型的单维表，可以简单理解为一行或者一列)

how：是两边的连接方式。 'left', 'right', 'outer', 'inner'。它这里有四种选项。

on：连接的列或者索引级别名称，是必须同时存在于两个表里的。如果没有找到连接的名称，那么左右两表的交集将被当做连接字段。

(DataFrame结构有索引和列的概念，索引可以为多层，可以简单理解为excel透视表的行分组。)

left_on和right_on：如果on参数不设置或者没有同名的存在。那么可以分别在这俩参数里指定左右两表的连接字段名称。

left_index和right_index：如果为TRUE，那么基于两表的索引建立连接

suffixes：用于设置两表重叠字段的名称的。默认是('_x','_y')，分别给左表重叠字段名称添加后缀_x，给右表添加_y

indicator：如果为True，则产生一列名为"_merge"的列，它的值为left_only(左表独有)，right_only(右表独有)，both(两表都有)。这3个值的出现是根据how的值来定的。

了解了merge的用法，我们就可以用它来查找左表、右表独有的内容了。

merge_df = pd.merge(left=left_df,right=right_df,how = 'outer',on = ['GID'],left_index=False,

                    right_index=False,sort=False,indicator=True)

这里我们只需要一行代码就完成了两表的比对。下图可见我们比对后生成的数据。PID这个重复的列名各自加了后缀。其他列名因为大小写不同而不认为相同。在最后有一个_merge列

_merge列的值只有3个。(unique()方法可查看列的非重复值，merge_df['_merge'].unique()，这样写也可以)

可通过merge_df[merge_df['_merge'] == 'right_only']来筛选right_only的列

merge_df[merge_df['_merge'] == 'right_only'].to_excel('right_only.xlsx')

这行代码可以将过滤结果输出为excel文件。

虽然啰啰嗦嗦3千多字，但实际操作却只是几行代码的事。这样我们就可以通过几行代码完成整个表数据的核对。我的数据样本是10万行。即使是几百万、几千万行(只要内存够)，Python pandas都可以迅速完成你的命令！