Joining Dataframes using Merge

We discussed concatenation in the last section and it how it glues dataframes together, but what if we needed to combine dataframes of different sizes where rows need to be matched up?

.merge() is a verb that give more precision and options when we join dataframes.

We compared .merge() in the last section to stitching fabric together, where we line up the patterns of each piece of cloth.

Unlike pd.concat(), .merge() can only combined dataframes horizontally but it can do so in many different ways.

Introducing the Data

candy = pd.read_csv('data/candybars.csv', nrows=5, usecols=['name', 'weight', 'chocolate', 'peanuts'])
candy.head()

	name	weight	chocolate	peanuts
0	Coffee Crisp	50	1	0
1	Butterfinger	184	1	1
2	Skor	39	1	0
3	Smarties	45	1	0
4	Twix	58	1	0

With .merge(), we need to identify a column in each dataframe that acts as a connection between them.

It’s a column where the values in Dataframe A are the same values to those in Dataframe B.

In the easiest situations both columns are named the same thing, but that doesn’t have to be the case.

Let’s use a subset of the candy bars dataset to explain this concept further.

candy has a column labeled name which has unique candy bar names.

candy

	name	weight	chocolate	peanuts
0	Coffee Crisp	50	1	0
1	Butterfinger	184	1	1
2	Skor	39	1	0
3	Smarties	45	1	0
4	Twix	58	1	0

Let’s load in the next dataset:

candy2m = pd.read_csv('data/candybars_merge.csv').loc[:4]
candy2m

	id_number	calories	fat	sugar	chocolate_bar
0	45623	798	30.0	72.0	Butterfinger
1	87685	250	12.0	25.0	Twix
2	34534	262	8.0	40.0	3 Musketeers
3	47886	239	12.0	22.0	KitKat
4	77785	275	13.0	32.0	Babe Ruth

This dataframe has new columns and rows not in the candy dataframe (3 Musketeers, Aero, and Kinder Bueno). We can see that a column named chocolate_bar is the same variable as the name column in the candy dataframe.

candy.merge(...)

candy.merge(candy2m, ...)

When we combine dataframes using .merge(), it’s quite different than pd.concat().

First, we decide which dataframe will be our left dataframe by implementing the merge verb on the selected dataframe. We are going to choose candy as our left dataframe.

Next, we specify the right dataframe as the first argument in .merge(). In our case, that’s candy2m.

The last step, which is the bulk of the work, is specifying the arguments.

We need to make sure we indicate which columns are the identifying key columns for each dataframe and what type of joining we want in our resulting dataframe.

Key Columns

.merge() needs arguments that identify a common key column. This is a column present in both dataframes which contain common values.

To choose our key columns in each dataframe, we use the following arguments:

• left_on - The left dataframe identifying key column label.
• right_on - The right dataframe identifying key column label.

.merge() needs arguments that identify a common key column. This is a column present in both dataframes which contain common values.

Key columns do not need to be named identically.

To choose our key columns in each dataframe, we use the following arguments:

• left_on - The left dataframe identifying key column label.
• right_on - The right dataframe identifying key column label.

For example:
Dataframe A can have a column labeled cereal, and Dataframe B could have a column labeled product_name that both share cereal names.

candy

	name	weight	chocolate	peanuts
0	Coffee Crisp	50	1	0
1	Butterfinger	184	1	1
2	Skor	39	1	0
3	Smarties	45	1	0
4	Twix	58	1	0

candy2m

	id_number	calories	fat	sugar	chocolate_bar
0	45623	798	30.0	72.0	Butterfinger
1	87685	250	12.0	25.0	Twix
2	34534	262	8.0	40.0	3 Musketeers
3	47886	239	12.0	22.0	KitKat
4	77785	275	13.0	32.0	Babe Ruth

Our candy dataframe which we said was our left dataframe, would use the argument left_on='name'.

our candy2m dataframe is our right dataframe, would use the argument right_on='chocolate_bar'.

candy.merge(candy2m, left_on='name', right_on='chocolate_bar')

	name	weight	chocolate	peanuts	...	calories	fat	sugar	chocolate_bar
0	Butterfinger	184	1	1	...	798	30.0	72.0	Butterfinger
1	Twix	58	1	0	...	250	12.0	25.0	Twix

2 rows × 9 columns

We combine that with the earlier code to get the following dataframe.

Great, we’ve combined the 2 dataframes horizontally. In the future, we may want to drop the columns named chocolate_bar or rename it before we merge.

If our identifying columns are named the same in both dataframes, .merge() only keeps one of them.

Ok, so what happened? We now only have 2 rows! We seemed to have lost all the rows that are not in both columns.

This is because .merge() uses a default joining method called inner join, which returns only the rows present in both dataframes. We can change that with the argument how.

how

candy.merge(candy2m, left_on='name', right_on='chocolate_bar', how='inner')

	name	weight	chocolate	peanuts	...	calories	fat	sugar	chocolate_bar
0	Butterfinger	184	1	1	...	798	30.0	72.0	Butterfinger
1	Twix	58	1	0	...	250	12.0	25.0	Twix

2 rows × 9 columns

The how argument specifies “how” our dataframes are joined.

We mentioned that the default argument value inner will only keep the rows with identifying column values that are present in both dataframes.

But there are 4 types of joins we could choose from for the how argument:

• inner
• outer
• left
• right

But there are 4 types of joins we could choose from for the how argument.

• outer

candy.merge(candy2m, left_on='name', right_on='chocolate_bar', how='outer')

	name	weight	chocolate	peanuts	...	calories	fat	sugar	chocolate_bar
0	NaN	NaN	NaN	NaN	...	262.0	8.0	40.0	3 Musketeers
1	NaN	NaN	NaN	NaN	...	275.0	13.0	32.0	Babe Ruth
2	Butterfinger	184.0	1.0	1.0	...	798.0	30.0	72.0	Butterfinger
...	...	...	...	...	...	...	...	...	...
5	Skor	39.0	1.0	0.0	...	NaN	NaN	NaN	NaN
6	Smarties	45.0	1.0	0.0	...	NaN	NaN	NaN	NaN
7	Twix	58.0	1.0	0.0	...	250.0	12.0	25.0	Twix

8 rows × 9 columns

• outer will return not only the rows with identifying column values that are present in both tables but all the rows in both tables. If there are any rows where any of the column values are missing, it will be filled with a NaN value.

Let’s colour coordinate this table and explore it on the next slide.

Here we see that Coffee Crisp and Butterfinger have complete rows.

Rows from the left dataframe that were not present in the right dataframe are Skor, Smarties, and Twix and therefore have NaN values for columns from the right table.

The opposite occurs for the values 3 Musketeers, Aero, and Kinder Bueno, which are present in the right dataframe and not the left one.

This results in NaN for values in the left dataframe columns.

• left

candy.merge(candy2m, left_on='name', right_on='chocolate_bar', how='left')

	name	weight	chocolate	peanuts	...	calories	fat	sugar	chocolate_bar
0	Coffee Crisp	50	1	0	...	NaN	NaN	NaN	NaN
1	Butterfinger	184	1	1	...	798.0	30.0	72.0	Butterfinger
2	Skor	39	1	0	...	NaN	NaN	NaN	NaN
3	Smarties	45	1	0	...	NaN	NaN	NaN	NaN
4	Twix	58	1	0	...	250.0	12.0	25.0	Twix

5 rows × 9 columns

The value left will only output the rows that are in the left dataframe, and if they are missing from the right dataframe, NaN values will occur. No candy bars that are only present in the right dataframe will be present.

Here we can see the values 3 Musketeers, Aero, and Kinder Bueno are not present in the resulting dataframe as they are only present in the right one.

• right

candy.merge(candy2m, left_on='name', right_on='chocolate_bar', how='right')

	name	weight	chocolate	peanuts	...	calories	fat	sugar	chocolate_bar
0	Butterfinger	184.0	1.0	1.0	...	798	30.0	72.0	Butterfinger
1	Twix	58.0	1.0	0.0	...	250	12.0	25.0	Twix
2	NaN	NaN	NaN	NaN	...	262	8.0	40.0	3 Musketeers
3	NaN	NaN	NaN	NaN	...	239	12.0	22.0	KitKat
4	NaN	NaN	NaN	NaN	...	275	13.0	32.0	Babe Ruth

5 rows × 9 columns

• right will only output the rows that are in the right dataframe, and if they are missing from the left dataframe, NaN values will occur. No candy bars that are only present in the left dataframe will be present.

We can see that the rows Skor, Smarties and Twix that were only present in the left dataframe, have been dropped.

One thing that all 4 joins have in common is they all will have the same columns labels that came from both dataframes.

indicator

candy.merge(candy2m, left_on='name', right_on='chocolate_bar', how='outer', indicator=True)

	name	weight	chocolate	peanuts	...	fat	sugar	chocolate_bar	_merge
0	NaN	NaN	NaN	NaN	...	8.0	40.0	3 Musketeers	right_only
1	NaN	NaN	NaN	NaN	...	13.0	32.0	Babe Ruth	right_only
2	Butterfinger	184.0	1.0	1.0	...	30.0	72.0	Butterfinger	both
...	...	...	...	...	...	...	...	...	...
5	Skor	39.0	1.0	0.0	...	NaN	NaN	NaN	left_only
6	Smarties	45.0	1.0	0.0	...	NaN	NaN	NaN	left_only
7	Twix	58.0	1.0	0.0	...	12.0	25.0	Twix	both

8 rows × 10 columns

indicator makes a new column name _merge and informs us from which dataframe the row originated from.

If we want to do an outer join and show all the possible rows from both dataframes, there is a useful argument called indicator.

Here we can see three possible values left_only, right_only, or both which informs us if the row came from the left dataframe, the right dataframe or if the row index label is shared between both dataframes.

Let’s apply what we learned!