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

df2

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

df3 = pd.merge(df1, df2)
df3

注意，每个人在两个表里的顺序并不一致，但 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

df4

pd.merge(df3, df4)

结果里多了一列 "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

df5

pd.merge(df1, df5)

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

指定合并键

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

on 关键字

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

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

df1

df2

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

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

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

df3

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

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

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

left_index 和 right_index 关键字

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

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

df1a

df2a

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

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

df1a

df2a

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

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

df1a.join(df2a)

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

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

df1a

df3

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

这些选项同样支持多索引和多列组合，接口设计得非常直观。更多细节可以参考 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

df7

pd.merge(df6, df7)

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

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

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

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

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

df6

df7

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

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

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

df6

df7

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

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

列名重叠：suffixes 关键字

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

df8

df9

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

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

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

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

示例：美国各州数据

合并操作最常见的场景就是把来自不同源头的数据拼在一起。这里用美国各州的人口和面积数据来演示一次完整的实战。数据集来自 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()

areas.head()

abbrevs.head()

目标是计算 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()

检查是否有不匹配：

merged.isnull().any()

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

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

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

看来人口缺失的那部分都来自 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()

再次检查是否有空值：

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

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

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

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

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

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

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

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

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

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

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