数据科学 IPython 笔记本 7.10 组合数据集：合并和连接

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：

	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

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

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()函数识别，每个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：

	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

生成的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：

	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	coding
3	Engineering	linux
4	HR	spreadsheets
5	HR	organization

pd.merge(df1, df5)：

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

这三种类型的连接可以与其他 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：

	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

仅当左侧和右侧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：

	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

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

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

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
Lisa	Engineering	2004
Bob	Accounting	2008
Jake	Engineering	2012
Sue	HR	2014

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

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

df1a：

	group
employee
Bob	Accounting
Jake	Engineering
Lisa	Engineering
Sue	HR

df2a：

	hire_date
employee
Lisa	2004
Bob	2008
Jake	2012
Sue	2014

df1a.join(df2a)：

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

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

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 文档的“合并，连接（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：

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

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

	name	food	drink
0	Mary	bread	wine

how关键字的其他选项是'outer'，'left'和'right'。外连接返回输入列的并集上的连接，并使用 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	Peter	fish	NaN
1	Paul	beans	NaN
2	Mary	bread	wine
3	Joseph	NaN	beer

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

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 |

输出行现在对应于左输入中的条目。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：

	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

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

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

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", suffixes=["_L", "_R"])：

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

这些后缀适用于任何可能的连接模式，并且如果存在多个重叠列，则也有效。这些模式的更多信息，请参阅“聚合和分组”，其中我们深入研究了关系代数。这些主题的进一步讨论，请参阅[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()：

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

我们将从多对一合并开始，它将向我们提供人口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()

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

让我们仔细检查这里是否存在任何不匹配，我们可以通过查找带有空值的行来实现：

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
2448	PR	under18	1990	NaN	NaN
2449	PR	total	1990	NaN	NaN
2450	PR	total	1991	NaN	NaN
2451	PR	under18	1991	NaN	NaN
2452	PR	total	1993	NaN	NaN

似乎所有空的人口值都来自 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()

	state/region	ages	year	population	state	area (sq. mi)
0	AL	under18	2012	1117489.0	Alabama	52423.0
1	AL	total	2012	4817528.0	Alabama	52423.0
2	AL	under18	2010	1130966.0	Alabama	52423.0
3	AL	total	2010	4785570.0	Alabama	52423.0
4	AL	under18	2011	1125763.0	Alabama	52423.0

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

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

	state/region	ages	year	population	state	area (sq. mi)
0	AL	under18	2012	1117489.0	Alabama	52423.0
1	AL	total	2012	4817528.0	Alabama	52423.0
2	AL	under18	2010	1130966.0	Alabama	52423.0
3	AL	total	2010	4785570.0	Alabama	52423.0
4	AL	under18	2011	1125763.0	Alabama	52423.0

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

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

	state/region	ages	year	population	state	area (sq. mi)
3	AL	total	2010	4785570.0	Alabama	52423.0
91	AK	total	2010	713868.0	Alaska	656425.0
101	AZ	total	2010	6408790.0	Arizona	114006.0
189	AR	total	2010	2922280.0	Arkansas	53182.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
'''

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

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

posted @ 2022-10-20 13:42 绝不原创的飞龙阅读(82) 评论(0) 收藏举报来源

刷新页面返回顶部

龙哥盟

掠夺·扩张·投机·博弈

数据科学 IPython 笔记本 7.10 组合数据集：合并和连接

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

关系代数

连接的分类

一对一连接

多对一连接

多对多连接

指定合并键

`on`关键字

`left_on`和`right_on`关键字

`left_index`和`right_index`关键字

为连接指定集合运算

覆盖列名：`suffixes`关键字

示例：美国各州数据

公告

龙哥盟

掠夺·扩张·投机·博弈

数据科学 IPython 笔记本 7.10 组合数据集：合并和连接

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

关系代数

连接的分类

一对一连接

多对一连接

多对多连接

指定合并键

on关键字

left_on和right_on关键字

left_index和right_index关键字

为连接指定集合运算

覆盖列名：suffixes关键字

示例：美国各州数据

公告

`on`关键字

`left_on`和`right_on`关键字

`left_index`和`right_index`关键字

覆盖列名：`suffixes`关键字