数据科学 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()函数和SeriesDataframe的相关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列的顺序在df1df2之间有所不同。pd.merge()函数正确地解释了这一点。另外,请记住,合并一般会丢弃索引,除了在索引合并的特殊情况下(参见left_indexright_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_onright_on关键字

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

结果有一个冗余列,如果需要我们可以删除 - 例如,通过使用DataFramedrop()方法:

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_indexright_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_indexright_onleft_onright_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中的完整的州名。我们想要根据popstate/region列和abbrevsabbreviation列进行合并。我们将使用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
'''

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

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

展开阅读全文
©️2020 CSDN 皮肤主题: 黑客帝国 设计师: 上身试试 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值