Pandas Merging 101
Solution 1
This post aims to give readers a primer on SQL-flavored merging with Pandas, how to use it, and when not to use it.
In particular, here's what this post will go through:
-
The basics - types of joins (LEFT, RIGHT, OUTER, INNER)
- merging with different column names
- merging with multiple columns
- avoiding duplicate merge key column in output
What this post (and other posts by me on this thread) will not go through:
- Performance-related discussions and timings (for now). Mostly notable mentions of better alternatives, wherever appropriate.
- Handling suffixes, removing extra columns, renaming outputs, and other specific use cases. There are other (read: better) posts that deal with that, so figure it out!
Note Most examples default to INNER JOIN operations while demonstrating various features, unless otherwise specified.
Furthermore, all the DataFrames here can be copied and replicated so you can play with them. Also, see this post on how to read DataFrames from your clipboard.
Lastly, all visual representation of JOIN operations have been hand-drawn using Google Drawings. Inspiration from here.
Enough talk - just show me how to use merge
!
Setup & Basics
np.random.seed(0)
left = pd.DataFrame({'key': ['A', 'B', 'C', 'D'], 'value': np.random.randn(4)})
right = pd.DataFrame({'key': ['B', 'D', 'E', 'F'], 'value': np.random.randn(4)})
left
key value
0 A 1.764052
1 B 0.400157
2 C 0.978738
3 D 2.240893
right
key value
0 B 1.867558
1 D -0.977278
2 E 0.950088
3 F -0.151357
For the sake of simplicity, the key column has the same name (for now).
An INNER JOIN is represented by
Note This, along with the forthcoming figures all follow this convention:
- blue indicates rows that are present in the merge result
- red indicates rows that are excluded from the result (i.e., removed)
- green indicates missing values that are replaced with
NaN
s in the result
To perform an INNER JOIN, call merge
on the left DataFrame, specifying the right DataFrame and the join key (at the very least) as arguments.
left.merge(right, on='key')
# Or, if you want to be explicit
# left.merge(right, on='key', how='inner')
key value_x value_y
0 B 0.400157 1.867558
1 D 2.240893 -0.977278
This returns only rows from left
and right
which share a common key (in this example, "B" and "D).
A LEFT OUTER JOIN, or LEFT JOIN is represented by
This can be performed by specifying how='left'
.
left.merge(right, on='key', how='left')
key value_x value_y
0 A 1.764052 NaN
1 B 0.400157 1.867558
2 C 0.978738 NaN
3 D 2.240893 -0.977278
Carefully note the placement of NaNs here. If you specify how='left'
, then only keys from left
are used, and missing data from right
is replaced by NaN.
And similarly, for a RIGHT OUTER JOIN, or RIGHT JOIN which is...
...specify how='right'
:
left.merge(right, on='key', how='right')
key value_x value_y
0 B 0.400157 1.867558
1 D 2.240893 -0.977278
2 E NaN 0.950088
3 F NaN -0.151357
Here, keys from right
are used, and missing data from left
is replaced by NaN.
Finally, for the FULL OUTER JOIN, given by
specify how='outer'
.
left.merge(right, on='key', how='outer')
key value_x value_y
0 A 1.764052 NaN
1 B 0.400157 1.867558
2 C 0.978738 NaN
3 D 2.240893 -0.977278
4 E NaN 0.950088
5 F NaN -0.151357
This uses the keys from both frames, and NaNs are inserted for missing rows in both.
The documentation summarizes these various merges nicely:
Other JOINs - LEFT-Excluding, RIGHT-Excluding, and FULL-Excluding/ANTI JOINs
If you need LEFT-Excluding JOINs and RIGHT-Excluding JOINs in two steps.
For LEFT-Excluding JOIN, represented as
Start by performing a LEFT OUTER JOIN and then filtering to rows coming from left
only (excluding everything from the right),
(left.merge(right, on='key', how='left', indicator=True)
.query('_merge == "left_only"')
.drop('_merge', 1))
key value_x value_y
0 A 1.764052 NaN
2 C 0.978738 NaN
Where,
left.merge(right, on='key', how='left', indicator=True)
key value_x value_y _merge
0 A 1.764052 NaN left_only
1 B 0.400157 1.867558 both
2 C 0.978738 NaN left_only
3 D 2.240893 -0.977278 both
And similarly, for a RIGHT-Excluding JOIN,
(left.merge(right, on='key', how='right', indicator=True)
.query('_merge == "right_only"')
.drop('_merge', 1))
key value_x value_y
2 E NaN 0.950088
3 F NaN -0.151357
Lastly, if you are required to do a merge that only retains keys from the left or right, but not both (IOW, performing an ANTI-JOIN),
You can do this in similar fashion—
(left.merge(right, on='key', how='outer', indicator=True)
.query('_merge != "both"')
.drop('_merge', 1))
key value_x value_y
0 A 1.764052 NaN
2 C 0.978738 NaN
4 E NaN 0.950088
5 F NaN -0.151357
Different names for key columns
If the key columns are named differently—for example, left
has keyLeft
, and right
has keyRight
instead of key
—then you will have to specify left_on
and right_on
as arguments instead of on
:
left2 = left.rename({'key':'keyLeft'}, axis=1)
right2 = right.rename({'key':'keyRight'}, axis=1)
left2
keyLeft value
0 A 1.764052
1 B 0.400157
2 C 0.978738
3 D 2.240893
right2
keyRight value
0 B 1.867558
1 D -0.977278
2 E 0.950088
3 F -0.151357
left2.merge(right2, left_on='keyLeft', right_on='keyRight', how='inner')
keyLeft value_x keyRight value_y
0 B 0.400157 B 1.867558
1 D 2.240893 D -0.977278
Avoiding duplicate key column in output
When merging on keyLeft
from left
and keyRight
from right
, if you only want either of the keyLeft
or keyRight
(but not both) in the output, you can start by setting the index as a preliminary step.
left3 = left2.set_index('keyLeft')
left3.merge(right2, left_index=True, right_on='keyRight')
value_x keyRight value_y
0 0.400157 B 1.867558
1 2.240893 D -0.977278
Contrast this with the output of the command just before (that is, the output of left2.merge(right2, left_on='keyLeft', right_on='keyRight', how='inner')
), you'll notice keyLeft
is missing. You can figure out what column to keep based on which frame's index is set as the key. This may matter when, say, performing some OUTER JOIN operation.
Merging only a single column from one of the DataFrames
For example, consider
right3 = right.assign(newcol=np.arange(len(right)))
right3
key value newcol
0 B 1.867558 0
1 D -0.977278 1
2 E 0.950088 2
3 F -0.151357 3
If you are required to merge only "newcol" (without any of the other columns), you can usually just subset columns before merging:
left.merge(right3[['key', 'newcol']], on='key')
key value newcol
0 B 0.400157 0
1 D 2.240893 1
If you're doing a LEFT OUTER JOIN, a more performant solution would involve map
:
# left['newcol'] = left['key'].map(right3.set_index('key')['newcol']))
left.assign(newcol=left['key'].map(right3.set_index('key')['newcol']))
key value newcol
0 A 1.764052 NaN
1 B 0.400157 0.0
2 C 0.978738 NaN
3 D 2.240893 1.0
As mentioned, this is similar to, but faster than
left.merge(right3[['key', 'newcol']], on='key', how='left')
key value newcol
0 A 1.764052 NaN
1 B 0.400157 0.0
2 C 0.978738 NaN
3 D 2.240893 1.0
Merging on multiple columns
To join on more than one column, specify a list for on
(or left_on
and right_on
, as appropriate).
left.merge(right, on=['key1', 'key2'] ...)
Or, in the event the names are different,
left.merge(right, left_on=['lkey1', 'lkey2'], right_on=['rkey1', 'rkey2'])
Other useful merge*
operations and functions
-
Merging a DataFrame with Series on index: See this answer.
-
Besides
merge
,DataFrame.update
andDataFrame.combine_first
are also used in certain cases to update one DataFrame with another. -
pd.merge_ordered
is a useful function for ordered JOINs. -
pd.merge_asof
(read: merge_asOf) is useful for approximate joins.
This section only covers the very basics, and is designed to only whet your appetite. For more examples and cases, see the documentation on merge
, join
, and concat
as well as the links to the function specifications.
Continue Reading
Jump to other topics in Pandas Merging 101 to continue learning:
*You are here.
Solution 2
A supplemental visual view of pd.concat([df0, df1], kwargs)
.
Notice that, kwarg axis=0
or axis=1
's meaning is not as intuitive as df.mean()
or df.apply(func)
Solution 3
Joins 101
These animations might be better to explain you visually. Credits: Garrick Aden-Buie tidyexplain repo
Inner Join
Outer Join or Full Join
Right Join
Left Join
Solution 4
In this answer, I will consider practical examples.
The first one, is of pandas.concat
.
The second one, of merging dataframes from the index of one and the column of another one.
Considering the following DataFrames
with the same column names:
Preco2018 with size (8784, 5)
Preco 2019 with size (8760, 5)
That have the same column names.
You can combine them using pandas.concat
, by simply
import pandas as pd
frames = [Preco2018, Preco2019]
df_merged = pd.concat(frames)
Which results in a DataFrame with the following size (17544, 5)
If you want to visualize, it ends up working like this
(Source)
2. Merge by Column and Index
In this part, I will consider a specific case: If one wants to merge the index of one dataframe and the column of another dataframe.
Let's say one has the dataframe Geo
with 54 columns, being one of the columns the Date Data
, which is of type datetime64[ns]
.
And the dataframe Price
that has one column with the price and the index corresponds to the dates
In this specific case, to merge them, one uses pd.merge
merged = pd.merge(Price, Geo, left_index=True, right_on='Data')
Which results in the following dataframe
Solution 5
This post will go through the following topics:
- Merging with index under different conditions
- options for index-based joins:
merge
,join
,concat
- merging on indexes
- merging on index of one, column of other
- options for index-based joins:
- effectively using named indexes to simplify merging syntax
Index-based joins
TL;DR
There are a few options, some simpler than others depending on the use case.
DataFrame.merge
withleft_index
andright_index
(orleft_on
andright_on
using named indexes)
- supports inner/left/right/full
- can only join two at a time
- supports column-column, index-column, index-index joins
DataFrame.join
(join on index)
- supports inner/left (default)/right/full
- can join multiple DataFrames at a time
- supports index-index joins
pd.concat
(joins on index)
- supports inner/full (default)
- can join multiple DataFrames at a time
- supports index-index joins
Index to index joins
Setup & Basics
import pandas as pd
import numpy as np
np.random.seed([3, 14])
left = pd.DataFrame(data={'value': np.random.randn(4)},
index=['A', 'B', 'C', 'D'])
right = pd.DataFrame(data={'value': np.random.randn(4)},
index=['B', 'D', 'E', 'F'])
left.index.name = right.index.name = 'idxkey'
left
value
idxkey
A -0.602923
B -0.402655
C 0.302329
D -0.524349
right
value
idxkey
B 0.543843
D 0.013135
E -0.326498
F 1.385076
Typically, an inner join on index would look like this:
left.merge(right, left_index=True, right_index=True)
value_x value_y
idxkey
B -0.402655 0.543843
D -0.524349 0.013135
Other joins follow similar syntax.
Notable Alternatives
-
DataFrame.join
defaults to joins on the index.DataFrame.join
does a LEFT OUTER JOIN by default, sohow='inner'
is necessary here.left.join(right, how='inner', lsuffix='_x', rsuffix='_y') value_x value_y idxkey B -0.402655 0.543843 D -0.524349 0.013135
Note that I needed to specify the
lsuffix
andrsuffix
arguments sincejoin
would otherwise error out:left.join(right) ValueError: columns overlap but no suffix specified: Index(['value'], dtype='object')
Since the column names are the same. This would not be a problem if they were differently named.
left.rename(columns={'value':'leftvalue'}).join(right, how='inner') leftvalue value idxkey B -0.402655 0.543843 D -0.524349 0.013135
-
pd.concat
joins on the index and can join two or more DataFrames at once. It does a full outer join by default, sohow='inner'
is required here..pd.concat([left, right], axis=1, sort=False, join='inner') value value idxkey B -0.402655 0.543843 D -0.524349 0.013135
For more information on
concat
, see this post.
Index to Column joins
To perform an inner join using index of left, column of right, you will use DataFrame.merge
a combination of left_index=True
and right_on=...
.
right2 = right.reset_index().rename({'idxkey' : 'colkey'}, axis=1)
right2
colkey value
0 B 0.543843
1 D 0.013135
2 E -0.326498
3 F 1.385076
left.merge(right2, left_index=True, right_on='colkey')
value_x colkey value_y
0 -0.402655 B 0.543843
1 -0.524349 D 0.013135
Other joins follow a similar structure. Note that only merge
can perform index to column joins. You can join on multiple columns, provided the number of index levels on the left equals the number of columns on the right.
join
and concat
are not capable of mixed merges. You will need to set the index as a pre-step using DataFrame.set_index
.
Effectively using Named Index [pandas >= 0.23]
If your index is named, then from pandas >= 0.23, DataFrame.merge
allows you to specify the index name to on
(or left_on
and right_on
as necessary).
left.merge(right, on='idxkey')
value_x value_y
idxkey
B -0.402655 0.543843
D -0.524349 0.013135
For the previous example of merging with the index of left, column of right, you can use left_on
with the index name of left:
left.merge(right2, left_on='idxkey', right_on='colkey')
value_x colkey value_y
0 -0.402655 B 0.543843
1 -0.524349 D 0.013135
Continue Reading
Jump to other topics in Pandas Merging 101 to continue learning:
* you are here
cs95
Hi Magi the pirate My pandas canonicals ⬅ please critique! and/or buy me a drink ;-) When asking a question here, please describe the problem you are trying to solve, rather than asking about the method you think is the solution to that problem. This is known as the XY problem and can easily be averted if you ask the right questions. This will make it easy for us to understand what you're trying to do, and help you arrive at the best solution for your problem even sooner. Rep = participation Badges = quality 1 like = 1 prayer 1 upvote = 1 thanks Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems. (Actively Maintained) Pandas Canonicals The full list is on GitHub. Pandas Merging 101: Everything you ever wanted to know about merging and more How to iterate over a DataFrame. Spoler alert: don't! How to convert a DataFrame to NumPy array: from 0.24, use df.to_numpy() The Right Way to create an empty DataFrame and fill it: Use a list to grow your data, not a DataFrame Don't use inplace=True! Other Posts I'm Proud Of Pandas Merging 101 (see above) Best way to interleave two lists How do "and" and "or" act with non-boolean values? So you think you know recursion? In addition to this, some of my answers that use perfplot are worth a read. 236th awardee of the Legendary badge. Thanks, Stack Overflow! I love the shirt :-) & [SPACE RESERVED FOR 250k SWAG]
Updated on September 18, 2021Comments
-
cs95 over 2 years
- How can I perform a (
INNER
| (LEFT
|RIGHT
|FULL
)OUTER
)JOIN
with pandas? - How do I add NaNs for missing rows after a merge?
- How do I get rid of NaNs after merging?
- Can I merge on the index?
- How do I merge multiple DataFrames?
- Cross join with pandas
-
merge
?join
?concat
?update
? Who? What? Why?!
... and more. I've seen these recurring questions asking about various facets of the pandas merge functionality. Most of the information regarding merge and its various use cases today is fragmented across dozens of badly worded, unsearchable posts. The aim here is to collate some of the more important points for posterity.
This Q&A is meant to be the next installment in a series of helpful user guides on common pandas idioms (see this post on pivoting, and this post on concatenation, which I will be touching on, later).
Please note that this post is not meant to be a replacement for the documentation, so please read that as well! Some of the examples are taken from there.
Table of Contents
For ease of access.
- How can I perform a (
-
cs95 almost 5 yearsThis is a nice diagram. May I ask how you produced it?
-
eliu almost 5 yearsgoogle doc's built-in "insert ==> drawing... ==> new" (as of 2019-May). But, to be clear: the only reason I used google doc for this picture is because my notes is stored in google doc, and I would like a picture that can be modified quickly within google doc itself. Actually now you mentioned it, the google doc's drawing tool is pretty neat.
-
Ufos almost 5 yearsWow, this is great. Coming from the SQL world, "vertical" join is not a join in my head, as the table's structure is always fixed. Now even think pandas should consolidate
concat
andmerge
with a direction parameter beinghorizontal
orvertical
. -
cs95 over 4 years@Ufos Isn't that exactly what
axis=1
andaxis=0
is? -
Ufos over 4 yearsyes, there're now
merge
andconcat
and axis and whatever. However, as @eliu shows, it's all just the same concept of merge with "left" and "right" and "horizontal" or "vertical". I, personally, have to look into the documentation every time I have to remember which "axis" is0
and which is1
. -
eliu over 4 yearsif possible someone should address the
axis=0
andaxis=1
in.mean() .apply() .dropna() .concat()
. I have to think a lot to decide for each case. -
cs95 over 3 yearsIf anyone is confused by the table of contents at the end of each post, I split up this massive answer into 4 separate ones, 3 on this question and 1 on another. The way it was setup previously made it harder to reference folks to specific topics. This allows you to bookmark separate topics easily now!
-
ThatNewGuy about 3 yearsThis is an awesome resource! The only question I still have is why call it merge instead of join, and join instead of merge?
-
Talha Tayyab over 2 yearsThese are awesome!
-
eliu over 2 yearssummary of the "axis" arguments use cases: stackoverflow.com/a/69679300/6226980
-
Manjunath K Mayya about 2 yearsI appreciate the effort put in to achieve this. Beautifully done.