假设我有两个这样的数据框架:
left = pd.DataFrame({'key1': ['foo', 'bar'], 'lval': [1, 2]})
right = pd.DataFrame({'key2': ['foo', 'bar'], 'rval': [4, 5]})
我想合并它们，所以我尝试这样做:
pd.merge(left, right, left_on='key1', right_on='key2')
我很开心
key1 lval key2 rval
0 foo 1 foo 4
1 bar 2 bar 5
但我尝试使用join方法，我一直认为它非常相似。
left.join(right, on=['key1', 'key2'])
我得到了这个:
//anaconda/lib/python2.7/site-packages/pandas/tools/merge.pyc in _validate_specification(self)
406 if self.right_index:
407 if not ((len(self.left_on) == self.right.index.nlevels)):
--> 408 raise AssertionError()
409 self.right_on = [None] * n
410 elif self.right_on is not None:
AssertionError:
我错过了什么?
我总是在索引上使用join:
import pandas as pd
left = pd.DataFrame({'key': ['foo', 'bar'], 'val': [1, 2]}).set_index('key')
right = pd.DataFrame({'key': ['foo', 'bar'], 'val': [4, 5]}).set_index('key')
left.join(right, lsuffix='_l', rsuffix='_r')
val_l val_r
key
foo 1 4
bar 2 5
同样的功能可以通过对列使用merge:
left = pd.DataFrame({'key': ['foo', 'bar'], 'val': [1, 2]})
right = pd.DataFrame({'key': ['foo', 'bar'], 'val': [4, 5]})
left.merge(right, on=('key'), suffixes=('_l', '_r'))
key val_l val_r
0 foo 1 4
1 bar 2 5
从本文档中
Pandas提供了一个单一的功能，合并，作为所有的入口点
DataFrame对象之间的标准数据库连接操作:
merge(left, right, how='inner'， on=None, left_on=None, right_on=None，
left_index=False, right_index=False, sort=True
后缀=(“值”、“_y吗”),复制= True,指标= False)
和:
DataFrame。Join是一种方便的方法，用于组合两个列
潜在的不同索引的数据帧到一个单一的结果
DataFrame。这里有一个非常基本的例子:这里的数据对齐是打开的
索引(行标签)。同样的行为可以使用
Merge加上指示它使用索引的附加参数:
Result = pd。merge(left, right, left_index=True, right_index=True，
=“外”)
Pandas.merge()是用于所有合并/连接行为的底层函数。
DataFrames提供了pandas.DataFrame.merge()和pandas.DataFrame.join()方法，作为访问pandas.merge()功能的方便方式。例如，df1。Merge (right=df2，…)相当于pandas。合并(左=df1，右=df2，…)
以下是df.join()和df.merge()之间的主要区别:
lookup on right table: df1.join(df2) always joins via the index of df2, but df1.merge(df2) can join to one or more columns of df2 (default) or to the index of df2 (with right_index=True).
lookup on left table: by default, df1.join(df2) uses the index of df1 and df1.merge(df2) uses column(s) of df1. That can be overridden by specifying df1.join(df2, on=key_or_keys) or df1.merge(df2, left_index=True).
left vs inner join: df1.join(df2) does a left join by default (keeps all rows of df1), but df.merge does an inner join by default (returns only matching rows of df1 and df2).
所以，一般的方法是使用熊猫。合并(df1, df2)或df1. Merge (df2)。但是对于许多常见的情况(保留df1的所有行并连接到df2中的索引)，可以通过使用df1.join(df2)来节省一些输入。
关于这些问题的一些说明，请参阅http://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging:的文档
merge is a function in the pandas namespace, and it is also
available as a DataFrame instance method, with the calling DataFrame
being implicitly considered the left object in the join.
The related DataFrame.join method, uses merge internally for the
index-on-index and index-on-column(s) joins, but joins on indexes by
default rather than trying to join on common columns (the default
behavior for merge). If you are joining on index, you may wish to
use DataFrame.join to save yourself some typing.
...
这两个函数调用是完全等价的:
离开了。加入(右,= key_or_keys)
pd。merge(left, right, left_on=key_or_keys, right_index=True, how='left'， sort=False)
其中一个区别是merge创建了一个新索引，而join则保留了左边的索引。如果您错误地假设索引不会因合并而改变，则可能会对以后的转换产生很大的影响。
例如:
import pandas as pd
df1 = pd.DataFrame({'org_index': [101, 102, 103, 104],
'date': [201801, 201801, 201802, 201802],
'val': [1, 2, 3, 4]}, index=[101, 102, 103, 104])
df1
date org_index val
101 201801 101 1
102 201801 102 2
103 201802 103 3
104 201802 104 4
-
df2 = pd.DataFrame({'date': [201801, 201802], 'dateval': ['A', 'B']}).set_index('date')
df2
dateval
date
201801 A
201802 B
-
df1.merge(df2, on='date')
date org_index val dateval
0 201801 101 1 A
1 201801 102 2 A
2 201802 103 3 B
3 201802 104 4 B
-
df1.join(df2, on='date')
date org_index val dateval
101 201801 101 1 A
102 201801 102 2 A
103 201802 103 3 B
104 201802 104 4 B
