Python 熊猫左外连接多列上的多个数据框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21786490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pandas left outer join multiple dataframes on multiple columns
提问by user3311225
I am new to using DataFrame and I would like to know how to perform a SQL equivalent of left outer join on multiple columns on a series of tables
我是使用 DataFrame 的新手，我想知道如何在一系列表的多个列上执行等效于左外连接的 SQL
Example:
例子：
df1:
Year Week Colour Val1
2014 A Red 50
2014 B Red 60
2014 B Black 70
2014 C Red 10
2014 D Green 20
df2:
Year Week Colour Val2
2014 A Black 30
2014 B Black 100
2014 C Green 50
2014 C Red 20
2014 D Red 40
df3:
Year Week Colour Val3
2013 B Red 60
2013 C Black 80
2013 B Black 10
2013 D Green 20
2013 D Red 50
Essentially I want to do something like this SQL code (Notice that df3 is not joined on Year):
基本上我想做这样的 SQL 代码（注意 df3 没有加入 Year）：
SELECT df1.*, df2.Val2, df3.Val3
FROM df1
LEFT OUTER JOIN df2
ON df1.Year = df2.Year
AND df1.Week = df2.Week
AND df1.Colour = df2.Colour
LEFT OUTER JOIN df3
ON df1.Week = df3.Week
AND df1.Colour = df3.Colour
The result should look like:
结果应如下所示：
Year Week Colour Val1 Val2 Val3
2014 A Red 50 Null Null
2014 B Red 60 Null 60
2014 B Black 70 100 Null
2014 C Red 10 20 Null
2014 D Green 20 Null Null
I have tried using merge and join but can't figure out how to do it on multiple tables and when there are multiple joints involved. Could someone help me on this please?
我曾尝试使用合并和连接，但无法弄清楚如何在多个表上以及何时涉及多个关节。有人可以帮我吗？
Thanks
谢谢
采纳答案by TomAugspurger
Merge them in two steps, df1and df2first, and then the result of that to df3.
合并他们在两个步骤，df1和df2第一，然后那到结果df3。
In [33]: s1 = pd.merge(df1, df2, how='left', on=['Year', 'Week', 'Colour'])
I dropped year from df3 since you don't need it for the last join.
我从 df3 中删除了一年，因为您在最后一次加入时不需要它。
In [39]: df = pd.merge(s1, df3[['Week', 'Colour', 'Val3']],
how='left', on=['Week', 'Colour'])
In [40]: df
Out[40]:
Year Week Colour Val1 Val2 Val3
0 2014 A Red 50 NaN NaN
1 2014 B Red 60 NaN 60
2 2014 B Black 70 100 10
3 2014 C Red 10 20 NaN
4 2014 D Green 20 NaN 20
[5 rows x 6 columns]
回答by FatihAkici
One can also do this with a compact version of @TomAugspurger's answer, like so:
也可以使用@TomAugspurger 答案的紧凑版本来做到这一点，如下所示：
df = df1.merge(df2, how='left', on=['Year', 'Week', 'Colour']).merge(df3[['Week', 'Colour', 'Val3']], how='left', on=['Week', 'Colour'])
