Week 9: Merging data sets

Week 9: Merging data sets#

The pd.merge() method allows us to combine two dataframes based on the values in a given column.

For example, we are going to use the class survey data in ggr274_survey.csv and some made up data in fake_pastimes.csv.

There are a few things to note about the data in fake_pastimes.csv:

  • The id column is named differently (student_id vs. id)

  • There are some ids in ggr274_survey.csv (1, 2, 3) that are not in fake_pastimes.csv

  • There are some ids in fake_pastimes.csv (63, 64, 65, 66, 67)that are not in ggr274_survey.csv

The arguments to .merge() depend on how you want to merge the dataframes. Since there are two dataframes we identify them as the left and right dataframes.

The left_on=column_left and right_on=column_right arguments give the column name in each dataframe that we will use to merge. If the two columns have the same name, then we can use the argument on=column_name instead.

Let’s see a simple example, and then we can go into more details:

import pandas as pd

main_survey = pd.read_csv("ggr274_survey.csv")
pastimes = pd.read_csv("fake_pastimes.csv")

print(f"Number of rows in the main survey is {main_survey.shape[0]}")
print(f"Number of rows in the pasttimes is {pastimes.shape[0]}")

merged_df= pd.merge(left=main_survey, right=pastimes,
         left_on="student_id",
         right_on="id"
         )
print(f"The number of rows in the merged datafram is {merged_df.shape[0]}")
merged_df.head()

Handling data that appears in only one dataframe#

Without any special arguments, merge will perform an inner join. This means that ids must appear in both dataframes to be included in the merged dataframe.

inner_merge = pd.merge(left=main_survey, right=pastimes,
         left_on="student_id",
         right_on="id",
         how="inner"
         )
print(f"The number of rows in the merged datafram is {inner_merge.shape[0]}")
inner_merge.head()

There are a total of four possible ways to merge this data. The table below shows when we keep rows that have a value in only one of the left and right dataframes when merging.

Join type

Keep Left

Keep Right

Inner

No

No

Outer

Yes

Yes

Left

Yes

No

Right

No

Yes

Let’s see what this looks like.

# The number of rows after the merge should be union of the rows in both dataframes. 

outer_merge = pd.merge(left=main_survey, right=pastimes,
         left_on="student_id",
         right_on="id",
         how="outer"
         )
print(f"The number of rows in the outer merged dataframe is {outer_merge.shape[0]}")

# The number of rows after the merge should be the same as the left dataframe
left_merge = pd.merge(left=main_survey, right=pastimes,
         left_on="student_id",
         right_on="id",
         how="left"
         )
print(f"The number of rows in the left merged dataframe is {left_merge.shape[0]}")

# The number of rows after the merge should be the same as the right dataframe
right_merge = pd.merge(left=main_survey, right=pastimes,
         left_on="student_id",
         right_on="id",
         how="right"
         )
print(f"The number of rows in the right merged dataframe is {right_merge.shape[0]}")

Once we have a merged dataframe, we can go ahead and do all the analysis that we want to do.

Contents