Python for Data Analysis Part 18: Merging Data

* Edit Jan 2021: I recently completed a YouTube video covering topics in this post:

Data you use for your projects won't always be confined to a single table in a CSV or excel file. Data is often split across several tables that you need to combine in some way. Data frames can be joined together if they have columns in common. Joining tables in various ways is a common operation when working with databases but you can also join data frames in Python using functions included with pandas.

First, let's import some libraries and load two tables of related data. You can load the data into your own environment by copying each table below and then using pd.read_clipboard(sep="\t")

In [1]:

import numpy as np
import pandas as pd
import os

In [2]:

table1 = pd.read_clipboard(sep="\t")  

table1

Out[2]:

	Unnamed: 0	P_ID	gender	height	weight
0	1	1	male	71	175
1	2	2	male	74	225
2	3	3	female	64	130
3	4	4	female	64	125
4	5	5	female	66	165
5	6	6	male	69	160
6	7	7	female	62	115
7	8	8	male	72	250

In [3]:

table2 = pd.read_clipboard(sep="\t")  

table2

Out[3]:

	Unnamed: 0	P_ID	sex	visits	checkup	follow_up	illness	surgery	ER
0	1	1	male	1	1	0	0	0	0
1	2	2	male	2	1	0	0	0	1
2	3	4	female	4	1	1	2	0	0
3	4	5	female	12	1	2	7	2	0
4	5	7	female	2	1	0	1	0	0
5	6	8	male	2	1	0	1	0	0
6	7	9	male	1	0	0	0	0	1
7	8	10	female	1	0	0	0	0	1

Let's delete the unnamed column:

In [4]:

del table1["Unnamed: 0"]
del table2["Unnamed: 0"]

Both data frames contain the column "P_ID" but the other columns are different. A unique identifier like an ID is usually a good key for joining two data frames together. You can combine two data frames by a common column with merge():

In [5]:

combined1 = pd.merge(table1,       # First table
                    table2,        # Second table
                    how="inner",   # Merge method
                    on="P_ID")     # Column(s) to join on

combined1

Out[5]:

	P_ID	gender	height	weight	sex	visits	checkup	follow_up	illness	surgery	ER
0	1	male	71	175	male	1	1	0	0	0	0
1	2	male	74	225	male	2	1	0	0	0	1
2	4	female	64	125	female	4	1	1	2	0	0
3	5	female	66	165	female	12	1	2	7	2	0
4	7	female	62	115	female	2	1	0	1	0	0
5	8	male	72	250	male	2	1	0	1	0	0

Inspecting the new combined data frame, we can see that the number of records dropped from 8 in the original tables to 6 in the combined table. If we inspect the P_ID column closely, we see that the original data tables contain some different values for P_ID. Note that inside the merge function we set the argument "how" to "inner". An inner join only merges records that appear in both columns used for the join. Since patients 3 and 6 only appear in table1 and patients 9 and 10 only appear in table2, those four patients were dropped when we merged the tables together.

Inner joins ensure that we don't end up introducing missing values in our data. For instance, if we kept patients 3 and 6 in the combined data frame, those patients would end up with a lot of missing values because they aren't present in the table2. If you want to keep more of your data and don't mind introducing some missing values, you can use merge to perform other types of joins, such as left joins, right joins and outer joins:

In [6]:

# A left join keeps all key values in the first(left) data frame

left_join = pd.merge(table1,       # First table
                    table2,        # Second table
                    how="left",   # Merge method
                    on="P_ID")     # Column(s) to join on

left_join

Out[6]:

	P_ID	gender	height	weight	sex	visits	checkup	follow_up	illness	surgery	ER
0	1	male	71	175	male	1	1	0	0	0	0
1	2	male	74	225	male	2	1	0	0	0	1
2	3	female	64	130	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	4	female	64	125	female	4	1	1	2	0	0
4	5	female	66	165	female	12	1	2	7	2	0
5	6	male	69	160	NaN	NaN	NaN	NaN	NaN	NaN	NaN
6	7	female	62	115	female	2	1	0	1	0	0
7	8	male	72	250	male	2	1	0	1	0	0

In [7]:

# A right join keeps all key values in the second(right) data frame

right_join = pd.merge(table1,       # First table
                    table2,        # Second table
                    how="right",   # Merge method
                    on="P_ID")     # Column(s) to join on

right_join

Out[7]:

	P_ID	gender	height	weight	sex	visits	checkup	follow_up	illness	surgery	ER
0	1	male	71	175	male	1	1	0	0	0	0
1	2	male	74	225	male	2	1	0	0	0	1
2	4	female	64	125	female	4	1	1	2	0	0
3	5	female	66	165	female	12	1	2	7	2	0
4	7	female	62	115	female	2	1	0	1	0	0
5	8	male	72	250	male	2	1	0	1	0	0
6	9	NaN	NaN	NaN	male	1	0	0	0	0	1
7	10	NaN	NaN	NaN	female	1	0	0	0	0	1

In [8]:

# An outer join keeps all key values in both data frames

outer_join = pd.merge(table1,      # First table
                    table2,        # Second table
                    how="outer",   # Merge method
                    on="P_ID")     # Column(s) to join on

outer_join

Out[8]:

	P_ID	gender	height	weight	sex	visits	checkup	follow_up	illness	surgery	ER
0	1	male	71	175	male	1	1	0	0	0	0
1	2	male	74	225	male	2	1	0	0	0	1
2	3	female	64	130	NaN	NaN	NaN	NaN	NaN	NaN	NaN
3	4	female	64	125	female	4	1	1	2	0	0
4	5	female	66	165	female	12	1	2	7	2	0
5	6	male	69	160	NaN	NaN	NaN	NaN	NaN	NaN	NaN
6	7	female	62	115	female	2	1	0	1	0	0
7	8	male	72	250	male	2	1	0	1	0	0
8	9	NaN	NaN	NaN	male	1	0	0	0	0	1
9	10	NaN	NaN	NaN	female	1	0	0	0	0	1

By this point, you may have noticed that the two data frames contain a second column in common. The first table contains the column "gender" while the second contains the column "sex", both of which record the same information. We can solve this issue by first renaming one of the two columns so that their names are the same and then supplying that column's name as a second column to merge upon:

In [9]:

table2.rename(columns={"sex":"gender"}, inplace=True) # Rename "sex" column

combined2 = pd.merge(table1,               # First data frame
                  table2,                  # Second data frame
                  how="outer",             # Merge method
                  on=["P_ID","gender"])    # Column(s) to join on

combined2

Out[9]:

	P_ID	gender	height	weight	visits	checkup	follow_up	illness	surgery	ER
0	1	male	71	175	1	1	0	0	0	0
1	2	male	74	225	2	1	0	0	0	1
2	3	female	64	130	NaN	NaN	NaN	NaN	NaN	NaN
3	4	female	64	125	4	1	1	2	0	0
4	5	female	66	165	12	1	2	7	2	0
5	6	male	69	160	NaN	NaN	NaN	NaN	NaN	NaN
6	7	female	62	115	2	1	0	1	0	0
7	8	male	72	250	2	1	0	1	0	0
8	9	male	NaN	NaN	1	0	0	0	0	1
9	10	female	NaN	NaN	1	0	0	0	0	1

By renaming and merging on the gender column, we've managed to eliminate some NA values in the outer join. Although an outer joins can introduce NA values, they can also be helpful for discovering patterns in the data. For example, in our combined data, notice that the two patients who did not have values listed for height and weight only made visits to the ER. It could be that the hospital did not have patients 9 and 10 on record previously and that it does not take height and weight measurements for ER visits. Using the same type of intuition, it could be that patients 3 and 6 have height and weight measurements on file from visits in the past, but perhaps they did not visit the hospital during the time period for which the visit data was collected.

Wrap Up

The pandas function merge() can perform common joins to combine data frames with matching columns. For some projects, you may have to merge several tables into one to get the most out of your data.

Now that we know how to prepare and merge data, we're ready to learn more about two of the most common tools for exploring data sets: frequency tables and plots.

Next Time: Python for Data Analysis Part 19: Frequency Tables