Python Pandas DataFrame join方法用法及代码示例

例子

基本用法

考虑以下关于商店的一些products的DataFrame：

df_products = pd.DataFrame({"product": ["computer", "smartphone", "headphones"],
 "bought_by": ["bob", "alex", "bob"]},
 index=["A","B","C"])
df_products



   product      bought_by
A  computer     bob
B  smartphone   alex
C  headphones   bob

这是关于商店的一些customers的DataFrame：

df_customers = pd.DataFrame({"age": [10, 20, 30]},
 index=["alex","bob","cathy"])
df_customers



       age
alex   10
bob    20
cathy  30

要对 df_products 的 bought_by 列执行 left-join：

df_products.join(df_customers, on="bought_by")   # how="left"



   product     bought_by   age
A  computer      bob       20
B  smartphone    alex      10
C  headphones    bob       20

默认情况下，other 的索引将用作连接键。如果您希望更灵活地选择哪些列用于连接，请改用 merge(~) 方法。

不同连接的比较

考虑以下有关产品和客户的DataFrames：

df_products = pd.DataFrame({"product": ["computer", "smartphone", "headphones"],
 "bought_by": ["bob", "alex", "david"]},
 index=["A","B","C"])
df_customers = pd.DataFrame({"age": [10, 20, 30]}, index=["alex","bob","cathy"])



        [df_products]         |   [df_customers]
   product      bought_by     |            age
A  computer     bob           |     alex   10
B  smartphone   alex          |     bob    20
C  headphones   david         |     cathy  30

左连接

df_products.join(df_customers, on="bought_by", how="left")



   product     bought_by  age
A  computer    bob        20.0
B  smartphone  alex       10.0
C  headphones  david      NaN

右连接

df_products.join(df_customers, on="bought_by", how="right")



     product     bought_by  age
B    smartphone  alex       10
A    computer    bob        20
NaN  NaN         cathy      30

内部联接

df_products.join(df_customers, on="bought_by", how="inner")



   product     bought_by  age
A  computer    bob        20
C  headphones  bob        20
B  smartphone  alex       10

外连接

df_products.join(df_customers, on="bought_by", how="outer")



     product     bought_by  age
A    computer    bob        20
C    headphones  bob        20
B    smartphone  alex       10
NaN  NaN         cathy      30

指定 lsuffix 和 rsuffix

假设我们想要加入以下 DataFrame：

df_products = pd.DataFrame({"product": ["computer", "smartphone", "headphones"],
 "age": [5,6,7],
 "bought_by": ["bob", "alex", "bob"]},
 index=["A","B","C"])
df_customers = pd.DataFrame({"age": [10, 20, 30]},
 index=["alex","bob","cathy"])



   product     age  bought_by   |         age
A  computer     5   bob         |  alex   10
B  smartphone   6   alex        |  bob    20
C  headphones   7   bob         |  cathy  30

请注意DataFrames 都有age 列。

由于命名重叠，执行连接会产生 ValueError ：

df_products.join(df_customers, on="bought_by")



ValueError: columns overlap but no suffix specified: Index(['age'], dtype='object')

可以通过指定 lsuffix 或 rsuffix 来解决此错误：

df_products.join(df_customers, on="bought_by", lsuffix="_product")



   product     age_product  bought_by   age
A  computer        5         bob        10
B  smartphone      6         alex       10
C  headphones      7         bob        20

请注意，lsuffix 和 rsuffix 仅在存在重复列标签时才生效。

指定排序

考虑与之前相同的示例：

[df_products]         |   [df_customers]
   product      bought_by     |            age
A  computer     bob           |     alex   10
B  smartphone   alex          |     bob    20
C  headphones   bob           |     cathy  30

默认情况下， sort=False ，这意味着生成的 DataFrame 的行不按连接键 ( bought_by ) 排序：

df_products.join(df_customers, on="bought_by")   # sort=False



   product     bought_by  age
A  computer    bob        20
B  smartphone  alex       10
C  headphones  bob        20

设置 sort=True 会产生：

df_products.join(df_customers, on="bought_by", sort=True)



   product     bought_by  age
B  smartphone  alex       10
A  computer    bob        20
C  headphones  bob        20