Pandas 合并数据集merge 和 join的使用

在数据处理中，经常需要把分散在不同表格里的信息整合到一起。Pandas 为此提供了非常高效的内存级连接（join）和合并（merge）操作——如果你接触过数据库，对这类概念应该不会陌生。

最核心的接口就是 pd.merge 函数。下面通过一系列实例来搞清楚它的具体用法。

先做常规导入，顺便把上一章那个用于并排显示多个对象的 display 函数也拉进来：

import pandas as pd
import numpy as np

class display(object):
    """Display HTML representation of multiple objects"""
    template = """

    
{0}
{1}
    
"""
    def __init__(self, *args):
        self.args = args
        
    def _repr_html_(self):
        return 'n'.join(self.template.format(a, eval(a)._repr_html_())
                         for a in self.args)
    
    def __repr__(self):
        return 'nn'.join(a + 'n' + repr(eval(a))
                         for a in self.args)

关系代数

pd.merge 背后遵循的是一套关系代数规则——这套形式化体系是绝大多数数据库操作的理论基石。关系代数的精妙之处在于它定义了一组基本操作，这些操作就像积木块一样，可以组合搭建出任意复杂的数据处理逻辑。

只要底层的实现是高效的，那么用它们组合出来的复合操作也就同样高效。Pandas 把其中几个核心构建块浓缩到了 pd.merge 函数以及 Series/DataFrame 的 join 方法里。用起来你会发现，不同来源的数据就这样被丝滑地串联起来了。

连接的类别

pd.merge 支持三种基本连接类型：一对一、多对一、多对多。三种类型实际上共用同一个接口，具体执行哪一种完全由输入的键列是否有重复来决定。先看简单例子，后面再展开细节。

一对一连接

最简单的场景：一对一连接，其实跟按列拼接（concat、append）有点像。举个具体例子，有两个 DataFrame，分别存了公司员工的基本信息和入职年份：

df1 = pd.DataFrame({'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'group': ['Accounting', 'Engineering',
                              'Engineering', 'HR']})
df2 = pd.DataFrame({'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],
                    'hire_date': [2004, 2008, 2012, 2014]})
display('df1', 'df2')

df1

	employee	group
0	Bob	Accounting
1	Jake	Engineering
2	Lisa	Engineering
3	Sue	HR

df2

	employee	hire_date
0	Lisa	2004
1	Bob	2008
2	Jake	2012
3	Sue	2014

直接调用 pd.merge(df1, df2)，系统会自动找到双方都有的 employee 列，以此作为连接键：

df3 = pd.merge(df1, df2)
df3

	employee	group	hire_date
0	Bob	Accounting	2008
1	Jake	Engineering	2012
2	Lisa	Engineering	2004
3	Sue	HR	2014

注意，每个人在两个表里的顺序并不一致，但 pd.merge 可以自动对齐。另外，合并后的结果通常会丢弃原始索引，除非你特意使用基于索引的合并（后面会聊 left_index 和 right_index）。

多对一连接

所谓多对一，就是说其中一个连接键存在重复值。结果 DataFrame 会如实保留那些重复。来看代码：

df4 = pd.DataFrame({'group': ['Accounting', 'Engineering', 'HR'],
                    'supervisor': ['Carly', 'Guido', 'Steve']})
display('df3', 'df4', 'pd.merge(df3, df4)')

df3

	employee	group	hire_date
0	Bob	Accounting	2008
1	Jake	Engineering	2012
2	Lisa	Engineering	2004
3	Sue	HR	2014

df4

	group	supervisor
0	Accounting	Carly
1	Engineering	Guido
2	HR	Steve

pd.merge(df3, df4)

	employee	group	hire_date	supervisor
0	Bob	Accounting	2008	Carly
1	Jake	Engineering	2012	Guido
2	Lisa	Engineering	2004	Guido
3	Sue	HR	2014	Steve

结果里多了一列 "supervisor"，其中的 Guido 因为对应两名员工而出现了两次，这就是多对一的典型行为。

多对多连接

如果左右两个表在连接键上都有重复，就会产生多对多合并。光说可能有点抽象，看个例子就明白了。假设有一个表记录每个小组所关联的必备技能：

df5 = pd.DataFrame({'group': ['Accounting', 'Accounting',
                              'Engineering', 'Engineering', 'HR', 'HR'],
                    'skills': ['math', 'spreadsheets', 'software', 'math',
                               'spreadsheets', 'organization']})
display('df1', 'df5', "pd.merge(df1, df5)")

df1

	employee	group
0	Bob	Accounting
1	Jake	Engineering
2	Lisa	Engineering
3	Sue	HR

df5

	group	skills
0	Accounting	math
1	Accounting	spreadsheets
2	Engineering	software
3	Engineering	math
4	HR	spreadsheets
5	HR	organization

pd.merge(df1, df5)

	employee	group	skills
0	Bob	Accounting	math
1	Bob	Accounting	spreadsheets
2	Jake	Engineering	software
3	Jake	Engineering	math
4	Lisa	Engineering	software
5	Lisa	Engineering	math
6	Sue	HR	spreadsheets
7	Sue	HR	organization

这三种连接类型跟 Pandas 的其他工具组合起来，能实现很多有趣的功能。不过现实中的数据很少像上面例子这么规整，所以接下来看看 pd.merge 提供的那些参数选项，它们正是用来应对各种复杂情况的。

指定合并键

之前 pd.merge 默认会去找两个 DataFrame 里同名的列作为连接键。但实际中列名往往不完全一样，这时就需要显式指定了。

on 关键字

最直接的方式就是用 on 参数指定要用哪一列（或列列表）做键：

display('df1', 'df2', "pd.merge(df1, df2, on='employee')")

df1

	employee	group
0	Bob	Accounting
1	Jake	Engineering
2	Lisa	Engineering
3	Sue	HR

df2

	employee	hire_date
0	Lisa	2004
1	Bob	2008
2	Jake	2012
3	Sue	2014

pd.merge(df1, df2, on='employee')

	employee	group	hire_date
0	Bob	Accounting	2008
1	Jake	Engineering	2012
2	Lisa	Engineering	2004
3	Sue	HR	2014

前提是左右两个表确实都有你指定的列。

left_on 和 right_on 关键字

有时候两边列的命名不一样，比如一个叫 "employee"，另一个叫 "name"。这时可以用 left_on 和 right_on 分别指定：

df3 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'salary': [70000, 80000, 120000, 90000]})
display('df1', 'df3', 'pd.merge(df1, df3, left_on="employee", right_on="name")')

df1

	employee	group
0	Bob	Accounting
1	Jake	Engineering
2	Lisa	Engineering
3	Sue	HR

df3

	name	salary
0	Bob	70000
1	Jake	80000
2	Lisa	120000
3	Sue	90000

pd.merge(df1, df3, left_on="employee", right_on="name")

	employee	group	name	salary
0	Bob	Accounting	Bob	70000
1	Jake	Engineering	Jake	80000
2	Lisa	Engineering	Lisa	120000
3	Sue	HR	Sue	90000

结果里多了一列 "name"，如果不需要，可以用 DataFrame.drop() 去掉：

pd.merge(df1, df3, left_on="employee", right_on="name").drop('name', axis=1)

	employee	group	salary
0	Bob	Accounting	70000
1	Jake	Engineering	80000
2	Lisa	Engineering	120000
3	Sue	HR	90000

left_index 和 right_index 关键字

有时候连接键是行索引而不是普通列，比如这样：

df1a = df1.set_index('employee')
df2a = df2.set_index('employee')
display('df1a', 'df2a')

df1a

	group
employee
Bob	Accounting
Jake	Engineering
Lisa	Engineering
Sue	HR

df2a

	hire_date
employee
Lisa	2004
Bob	2008
Jake	2012
Sue	2014

这时使用 left_index=True 和 right_index=True 就能让 merge 按索引对齐：

display('df1a', 'df2a',
        "pd.merge(df1a, df2a, left_index=True, right_index=True)")

df1a

	group
employee
Bob	Accounting
Jake	Engineering
Lisa	Engineering
Sue	HR

df2a

	hire_date
employee
Lisa	2004
Bob	2008
Jake	2012
Sue	2014

pd.merge(df1a, df2a, left_index=True, right_index=True)

	group	hire_date
employee
Bob	Accounting	2008
Jake	Engineering	2012
Lisa	Engineering	2004
Sue	HR	2014

为方便起见，Pandas 还提供了 DataFrame.join() 方法，直接默认按索引合并：

df1a.join(df2a)

	group	hire_date
employee
Bob	Accounting	2008
Jake	Engineering	2012
Lisa	Engineering	2004
Sue	HR	2014

也可以混合使用索引和列，比如左边用索引、右边用某列：

display('df1a', 'df3', "pd.merge(df1a, df3, left_index=True, right_on='name')")

df1a

	group
employee
Bob	Accounting
Jake	Engineering
Lisa	Engineering
Sue	HR

df3

	name	salary
0	Bob	70000
1	Jake	80000
2	Lisa	120000
3	Sue	90000

pd.merge(df1a, df3, left_index=True, right_on='name')

	group	name	salary
0	Accounting	Bob	70000
1	Engineering	Jake	80000
2	Engineering	Lisa	120000
3	HR	Sue	90000

这些选项同样支持多索引和多列组合，接口设计得非常直观。更多细节可以参考 Pandas 官方文档里“合并、连接与拼接”部分。

为连接指定集合运算方式

前面所有例子都默认了一个重要设定——当某个键只在一边出现时该怎么处理。看个具体例子：

df6 = pd.DataFrame({'name': ['Peter', 'Paul', 'Mary'],
                    'food': ['fish', 'beans', 'bread']},
                   columns=['name', 'food'])
df7 = pd.DataFrame({'name': ['Mary', 'Joseph'],
                    'drink': ['wine', 'beer']},
                   columns=['name', 'drink'])
display('df6', 'df7', 'pd.merge(df6, df7)')

df6

	name	food
0	Peter	fish
1	Paul	beans
2	Mary	bread

df7

	name	drink
0	Mary	wine
1	Joseph	beer

pd.merge(df6, df7)

	name	food	drink
0	Mary	bread	wine

这里两个表的 "name" 列只有 "Mary" 是共有的。默认情况下 merge 只保留两边都有的部分，这就是所谓的内连接（inner join）。可以通过 how 参数显式指定，默认值就是 inner：

pd.merge(df6, df7, how='inner')

	name	food	drink
0	Mary	bread	wine

how 的其他选项包括 'outer'、'left' 和 'right'。

外连接（outer join）会取两边所有键的并集，缺失的部分用 NA 填充：

display('df6', 'df7', "pd.merge(df6, df7, how='outer')")

df6

	name	food
0	Peter	fish
1	Paul	beans
2	Mary	bread

df7

	name	drink
0	Mary	wine
1	Joseph	beer

pd.merge(df6, df7, how='outer')

	name	food	drink
0	Joseph	NaN	beer
1	Mary	bread	wine
2	Paul	beans	NaN
3	Peter	fish	NaN

左连接（left join）和右连接（right join）则分别以左边或右边的键为基准：

display('df6', 'df7', "pd.merge(df6, df7, how='left')")

df6

	name	food
0	Peter	fish
1	Paul	beans
2	Mary	bread

df7

	name	drink
0	Mary	wine
1	Joseph	beer

pd.merge(df6, df7, how='left')

	name	food	drink
0	Peter	fish	NaN
1	Paul	beans	NaN
2	Mary	bread	wine

右连接同理。这些选项都能跟前面介绍的各种连接类型无缝配合。

列名重叠：suffixes 关键字

当两个 DataFrame 里有同名的列（且不是连接键）时，merge 会自动给它们加上 _x 和 _y 后缀以示区分。比如：

df8

	name	rank
0	Bob	1
1	Jake	2
2	Lisa	3
3	Sue	4

df9

	name	rank
0	Bob	3
1	Jake	1
2	Lisa	4
3	Sue	2

pd.merge(df8, df9, on="name")

	name	rank_x	rank_y
0	Bob	1	3
1	Jake	2	1
2	Lisa	3	4
3	Sue	4	2

如果默认后缀不好用，可以通过 suffixes 关键字自定义：

pd.merge(df8, df9, on="name", suffixes=["_L", "_R"])

	name	rank_L	rank_R
0	Bob	1	3
1	Jake	2	1
2	Lisa	3	4
3	Sue	4	2

这些后缀在所有连接方式中都适用，并且当有多对重叠列时也会依次处理。更深入的关系代数讨论可以在专门的文章中找到。

示例：美国各州数据

合并操作最常见的场景就是把来自不同源头的数据拼在一起。这里用美国各州的人口和面积数据来演示一次完整的实战。数据集来自 http://github.com/jakevdp/data-USstates。

# 下载数据的命令（已注释，实际使用时需要取消注释）
# repo = "https://raw.githubusercontent.com/jakevdp/data-USstates/master"
# !cd data && curl -O {repo}/state-population.csv
# !cd data && curl -O {repo}/state-areas.csv
# !cd data && curl -O {repo}/state-abbrevs.csv

先通过 read_csv 读进来看看样子：

pop = pd.read_csv('data/state-population.csv')
areas = pd.read_csv('data/state-areas.csv')
abbrevs = pd.read_csv('data/state-abbrevs.csv')

display('pop.head()', 'areas.head()', 'abbrevs.head()')

pop.head()

	state/region	ages	year	population
0	AL	under18	2012	1117489.0
1	AL	total	2012	4817528.0
2	AL	under18	2010	1130966.0
3	AL	total	2010	4785570.0
4	AL	under18	2011	1125763.0

areas.head()

	state	area (sq. mi)
0	Alabama	52423
1	Alaska	656425
2	Arizona	114006
3	Arkansas	53182
4	California	163707

abbrevs.head()

	state	abbreviation
0	Alabama	AL
1	Alaska	AK
2	Arizona	AZ
3	Arkansas	AR
4	California	CA

目标是计算 2010 年美国各州（含特区）的人口密度排名。数据都有了，但需要先做合并。

第一步，把人口表和州名缩写表连接起来，为人口数据补全完整的州名。因为要用缩写去匹配，所以采用多对一合并，加上 how='outer' 确保不遗漏任何记录：

merged = pd.merge(pop, abbrevs, how='outer',
                  left_on='state/region', right_on='abbreviation')
merged = merged.drop('abbreviation', axis=1) # 去掉重复信息
merged.head()

	state/region	ages	year	population	state
0	AK	total	1990	553290.0	Alaska
1	AK	under18	1990	177502.0	Alaska
2	AK	total	1992	588736.0	Alaska
3	AK	under18	1991	182180.0	Alaska
4	AK	under18	1992	184878.0	Alaska

检查是否有不匹配：

merged.isnull().any()

state/region    False
ages            False
year            False
population       True
state            True
dtype: bool

有些 population 为空，看看是哪些：

merged[merged['population'].isnull()].head()

	state/region	ages	year	population	state
1872	PR	under18	1990	NaN	NaN
1873	PR	total	1990	NaN	NaN
1874	PR	total	1991	NaN	NaN
1875	PR	under18	1991	NaN	NaN
1876	PR	total	1993	NaN	NaN

看来人口缺失的那部分都来自 2000 年之前的波多黎各，可能是原始数据本身就不全。

值得注意的是，有些 state 列也是空值，这说明缩写表里没有对应的条目。具体是哪些地区？

merged.loc[merged['state'].isnull(), 'state/region'].unique()

array(['PR', 'USA'], dtype=object)

问题很清楚了：人口数据里包含了波多黎各（PR）和美国整体（USA），但缩写表里没有这两项。我们手动补上：

merged.loc[merged['state/region'] == 'PR', 'state'] = 'Puerto Rico'
merged.loc[merged['state/region'] == 'USA', 'state'] = 'United States'
merged.isnull().any()

state/region    False
ages            False
year            False
population       True
state           False
dtype: bool

state 列已经没有空值了。接下来把面积数据也合并进来，用 state 列做键，采用左连接：

final = pd.merge(merged, areas, on='state', how='left')
final.head()

	state/region	ages	year	population	state	area (sq. mi)
0	AK	total	1990	553290.0	Alaska	656425.0
1	AK	under18	1990	177502.0	Alaska	656425.0
2	AK	total	1992	588736.0	Alaska	656425.0
3	AK	under18	1991	182180.0	Alaska	656425.0
4	AK	under18	1992	184878.0	Alaska	656425.0

再次检查是否有空值：

area 列果然有缺失。看看哪些地区被忽略了：

final['state'][final['area (sq. mi)'].isnull()].unique()

array(['United States'], dtype=object)

面积表里没有美国整体的数据。要算也可以自己加，不过这里我们直接删掉那些行——毕竟整个美国的人口密度不是当前讨论的重点：

final.dropna(inplace=True)
final.head()

	state/region	ages	year	population	state	area (sq. mi)
0	AK	total	1990	553290.0	Alaska	656425.0
1	AK	under18	1990	177502.0	Alaska	656425.0
2	AK	total	1992	588736.0	Alaska	656425.0
3	AK	under18	1991	182180.0	Alaska	656425.0
4	AK	under18	1992	184878.0	Alaska	656425.0

数据准备完毕。要回答最初的问题，需要提取出 2010 年且人口类型为 "total" 的那部分。用 query 方法一步到位：

data2010 = final.query("year == 2010 & ages == 'total'")
data2010.head()

	state/region	ages	year	population	state	area (sq. mi)
43	AK	total	2010	713868.0	Alaska	656425.0
51	AL	total	2010	4785570.0	Alabama	52423.0
141	AR	total	2010	2922280.0	Arkansas	53182.0
149	AZ	total	2010	6408790.0	Arizona	114006.0
197	CA	total	2010	37333601.0	California	163707.0

最后一步：计算人口密度，并按降序排列。把州设为索引，然后直接除：

data2010.set_index('state', inplace=True)
density = data2010['population'] / data2010['area (sq. mi)']

density.sort_values(ascending=False, inplace=True)
density.head()

state
District of Columbia    8898.897059
Puerto Rico             1058.665149
New Jersey              1009.253268
Rhode Island             681.339159
Connecticut              645.600649
dtype: float64

结果出来了。2010 年人口密度最高的地区是华盛顿特区（哥伦比亚特区），在各州中则是新泽西州最密集。

再看看排名靠后的：

density.tail()

state
South Dakota    10.583512
North Dakota     9.537565
Montana          6.736171
Wyoming          5.768079
Alaska           1.087509
dtype: float64

阿拉斯加毫无悬念垫底，平均每平方英里只有一个人。

这种把多个真实数据源合并起来回答具体问题的操作，在实际工作中非常