7.10 组合数据集：合并和连接

原文：Combining Datasets: Merge and Join

译者：飞龙

协议：CC BY-NC-SA 4.0

本节是《Python 数据科学手册》（Python Data Science Handbook）的摘录。

Pandas 提供的一个基本特性，是内存中的高性能的连接和合并操作。如果你曾经使用过数据库，那么你应该熟悉这种类型的数据交互。它的主要接口是pd.merge函数，我们将看到几个在实践中如何工作的例子。

为方便起见，我们将从重新定义上一节的display()函数开始：

import pandas as pd
import numpy as np

class display(object):
    """Display HTML representation of multiple objects"""
    template = """<div style="float: left; padding: 10px;">
    <p style='font-family:"Courier New", Courier, monospace'>{0}</p>{1}
    </div>"""
    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 '\n\n'.join(a + '\n' + repr(eval(a))
                           for a in self.args)

关系代数

pd.merge()中实现的行为，是所谓的关系代数的一个子集，它是一组用于操纵关系数据的形式规则，并形成了大多数数据库中可用操作的概念基础。关系代数方法的优势在于它提出了几种原始操作，这些操作成为任何数据集上更复杂操作的积木。拥有在数据库或其他程序中高效实现的基本操作词典，可以执行各种相当复杂的复合操作。

Pandas 在pd.merge()函数和Series和Dataframe的相关join()方法中，实现了几个基本构建块。正如我们将看到的，这些可以让你有效地链接来自不同来源的数据。

连接的分类

pd.merge()函数实现了许多类型的连接：一对一，多对一和多对多连接。通过以相同方式调用pd.merge()接口，来访问所有三种类型的连接；执行的连接类型取决于输入数据的形式。这里我们将展示三种合并的简单示例，并在下面进一步讨论详细选项。

一对一连接

也许最简单的合并表达式是一对一连接，这在很多方面与“数据集的组合：连接和附加”中的按列连接非常相似。。作为一个具体的例子，考虑以下两个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：

要将这些信息组合成一个DataFrame，我们可以使用pd.merge()函数：

df3 = pd.merge(df1, df2)
df3

pd.merge()函数识别，每个DataFrame都有一个employee列，并使用该列作为键自动连接。合并的结果是一个新的DataFrame，它组合了两个输入的信息。

请注意，每列中的条目顺序不一定得到保留：在这种情况下，employee列的顺序在df1和df2之间有所不同。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)：

生成的DataFrame拥有带有supervisor信息的附加列，其中信息在输入所需的一个或多个位置重复。

多对多连接

多对多连接在概念上有点令人困惑，但仍然有很好的定义。如果左侧和右侧数组中的键列都包含重复项，则结果是多对多合并。

结合一个具体的例子可能是最清楚的。考虑以下内容，我们有一个DataFrame，展示了与特定分组相关的一项或多项技能。通过执行多对多连接，我们可以恢复与任何个人相关的技能：

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

df1：

df5：

pd.merge(df1, df5)：

这三种类型的连接可以与其他 Pandas 工具一起使用，以实现各种功能。但实际上，数据集很少像我们在这里使用的那样干净。在下一节中，我们将考虑pd.merge()提供的一些选项，使你能够调整连接操作的工作方式。

指定合并键

我们已经看到了pd.merge()的默认行为：它在两个输入之间查找一个或多个匹配的列名，并将其用作键。但是，通常列名称不能很好地匹配，而pd.merge()提供了各种处理它的选项。

on关键字

Most simply, you can explicitly specify the name of the key column using the on keyword, which takes a column name or a list of column names:

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

df1：

df2：

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

仅当左侧和右侧DataFrame都具有指定的列名时，此选项才有效。

left_on和right_on关键字

有时你可能希望合并具有不同列名的两个数据集；例如，我们可能有一个数据集，其中员工姓名被标记为name而不是employee。在这种情况下，我们可以使用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")：

结果有一个冗余列，如果需要我们可以删除 - 例如，通过使用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：

你可以通过在pd.merge()中指定left_index和/或right_index标志，来将索引用作合并的键：

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)：

为方便起见，DataFrame`实现join()``方法，该方法执行的合并默认为连接索引：

display('df1a', 'df2a', 'df1a.join(df2a)')

df1a：

df2a：

df1a.join(df2a)：

如果你想混合索引和列，你可以将left_index和right_on或left_on和right_index结合起来，来获得所需的行为：

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 文档的“合并，连接（Join）和连接（concat）”一节。

为连接指定集合运算

在前面的所有例子中，我们在执行连接时掩盖了一个重要的考虑因素：连接中使用的集合运算的类型。当一个值出现在一个键列而不出现在另一个键列中时，会出现此情况。 考虑这个例子：

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。默认情况下，结果包含两组输入的交集；这就是所谓的内连接。我们可以使用how关键字明确指定它，默认为"inner"：

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

how关键字的其他选项是'outer'，'left'和'right'。外连接返回输入列的并集上的连接，并使用 NA 填充所有缺少的值：

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

df6：

df7：

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

左连接和右连接分别返回左侧条目和右侧条目上的连接。例如：

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

df6：

df7：

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

输出行现在对应于左输入中的条目。how ='right'以类似的方式工作。所有这些选项都可以直接应用于任何前面的连接类型。

覆盖列名：suffixes关键字

最后，你最终可能会遇到两个输入DataFrame具有冲突列名的情况。考虑这个例子：

df8 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'rank': [1, 2, 3, 4]})
df9 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'rank': [3, 1, 4, 2]})
display('df8', 'df9', 'pd.merge(df8, df9, on="name")')

df8：

df9：

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

因为输出有两个冲突的列名，merge函数会自动附加后缀_x或_y来使输出列唯一。如果这些默认值不合适，可以使用suffixes关键字指定自定义后缀：

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

df8：

df9：

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

这些后缀适用于任何可能的连接模式，并且如果存在多个重叠列，则也有效。这些模式的更多信息，请参阅“聚合和分组”，其中我们深入研究了关系代数。这些主题的进一步讨论，请参阅[Pandas“合并，连接（Join）和连接（Concatenate）文档”。

示例：美国各州数据

在组合来自不同来源的数据时，合并和连接操作最常出现。在这里，我们将考虑美国各州及其人口数据的一些例子。数据文件可以在 http://github.com/jakevdp/data-USstates/ 找到：

# 下面是下载数据的 shell 命令
# !curl -O https://raw.githubusercontent.com/jakevdp/data-USstates/master/state-population.csv
# !curl -O https://raw.githubusercontent.com/jakevdp/data-USstates/master/state-areas.csv
# !curl -O https://raw.githubusercontent.com/jakevdp/data-USstates/master/state-abbrevs.csv

让我们看一下三个数据集，使用 Pandas 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 年人口密度对美国各州和地区进行排名。显然，我们在这里拥有用于找到这个结果的数据，但是我们必须结合数据集来找到结果。

我们将从多对一合并开始，它将向我们提供人口DataFrame中的完整的州名。我们想要根据pop的state/region列和abbrevs的abbreviation列进行合并。我们将使用how ='outer'来确保没有数据因标签不匹配而被丢弃。

merged = pd.merge(pop, abbrevs, how='outer',
                  left_on='state/region', right_on='abbreviation')
merged = merged.drop('abbreviation', 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条目也是控制，这意味着abbrevs键中没有相应的条目！让我们弄清楚哪些地区缺少这种匹配：

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

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

我们可以快速推断出这个问题：我们的人口数据包括波多黎各（PR）和整个美国（美国）的条目，而这些条目没有出现在州缩写的键中。我们可以通过填充适当的条目来快速解决这些问题。

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()

再次，让我们检查空值来查看是否存在任何不匹配：

final.isnull().any()

'''
state/region     False
ages             False
year             False
population        True
state            False
area (sq. mi)     True
dtype: bool
'''

area列中有空值; 我们可以看看这里忽略了哪些区域：

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

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

我们看到我们的areas DataFrame不包含整个美国的面积。我们可以插入适当的值（例如，使用所有州的面积总和），但在这种情况下，我们只会删除空值，因为整个美国的人口密度与我们当前的讨论无关：

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

现在我们拥有了所需的所有数据。 为了回答感兴趣的问题，让我们首先选择对应 2000 年的数据部分和总人口。我们将使用query()函数快速执行此操作（这需要安装numexpr包；参见“高性能 Pandas：eval()和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
'''

到目前为止，我们看到最不密集的州是阿拉斯加州，每平方英里平均略多于一个居民。

尝试使用真实数据源回答问题时，这种混乱的数据合并是一项常见任务。我希望这个例子让你了解，如何组合我们所涵盖的工具，来从你的数据中获得见解！