Merging data

Merging sounds a bit like concatenating our dataset; however, it is quite different. To merge datasets, we need to have a common field in both datasets on which we can perform a merge.

If you are familiar with the SQL or join commands, then you are probably familiar with merging data. Usually, data from relational databases will require merging operations. Relational databases typically contain tabular data and account for a significant proportion of data found in many organizations. Some key concepts to note when doing merge operations include the following:

• Join key column: This refers to the common column within both datasets in which there are matching values. This is typically used to join the datasets. The columns do not need to have the same name; they only need to have matching values within the two datasets.
• Type of join: There are different types of join operations that can be performed on datasets:
  • Left join: We retain all the rows in the left dataframe. Values in the right dataframe that do not match the values in the left dataframe are added as empty/Not a Number (NaN) values in the result. The matching is done based on the matching/join key column.
  • Right join: We retain all the rows in the right dataframe. Values in the left dataframe that do not match the values in the right dataframe are added as empty/NaN values in the result. The matching is done based on the matching/join key column.
  • Inner join: We retain only the common values in both the left and right dataframes in the result – that is, we do not return empty/NaN values.
  • Outer join/full outer join: We retain all the rows for the left and right dataframes. If the values do not match, NaN is added to the result.

Figure 2.1 – Venn diagrams illustrating different types of joins

In pandas, the merge method helps us to merge dataframes.

Getting ready

We will continue working with the Marketing Campaign data from Kaggle. We will work with two samples of that dataset.

Place the marketing_campaign_merge1.csv and marketing_campaign_merge2.csv files in the data subfolder created in the first recipe. Alternatively, you can retrieve all the files from the GitHub repository.

How to do it…

We will merge datasets using the pandas library:

1. Import the pandas library:

   import pandas as pd

2. Load the .csv files into a dataframe using read_csv:

   marketing_sample1 = pd.read_csv("data/marketing_campaign_merge1.csv")

   marketing_sample2 = pd.read_csv("data/marketing_campaign_merge2.csv")

3. Take a look at the two datasets. Check the first few rows through the head method. Also, check the number of columns and rows:

   marketing_sample1.head()

         ID  Year_Birth  Education

   0    5524  1957     Graduation

   1    2174  1954     Graduation

   2    4141  1965     Graduation

   3    6182  1984     Graduation

   4    5324  1981      PhD

       ID    Marital_Status    Income

   0    5524    Single    58138.0

   1    2174    Single    46344.0

   2    4141    Together    71613.0

   3    6182    Together    26646.0

   4    5324    Married    58293.0

   marketing_sample1.shape

   (2240, 3)

   marketing_sample2.shape

   (2240, 3)

4. Merge the datasets. Use the merge method from the pandas library to merge the datasets:

   merged_data = pd.merge(marketing_sample1,marketing_sample2,on = "ID")

5. Inspect the shape of the result and the first few rows:

   merged_data.head()

       ID    Year_Birth    Education    Marital_Status    Income

   0    5524    1957    Graduation    Single    58138.0

   1    2174    1954    Graduation    Single    46344.0

   2    4141    1965    Graduation    Together    71613.0

   3    6182    1984    Graduation    Together    26646.0

   4    5324    1981    PhD    Married    58293.0

   merged_data.shape

   (2240, 5)

Great! We have merged our dataset.

How it works...

We import the pandas library and refer to it as pd in step 1. In step 2, we use read_csv to load the two .csv files to be merged into pandas dataframes. We call the dataframes marketing_sample1 and marketing_sample2 respectively. In step 3, we inspect the dataset using head() to see the first five rows in the dataset. We inspect the number of rows and columns using shape, which returns a tuple that displays the number of rows and columns respectively.

In step 4, we apply the merge method to merge the two datasets. We provide four arguments for the merge method. The first two arguments are the dataframes we want to merge, the third specifies the key or common column upon which a merge can be achieved. The merge method also has a how parameter. This parameter specifies the type of join to be used. The default parameter of this argument is an inner join.

There’s more...

Sometimes, the common field in two datasets may have a different name. The merge method allows us to address this through two arguments, left_on and right_on. left_on specifies the key on the left dataframe, while right_on is the same thing on the right dataframe.

See also

You can check out this useful resource by Real Python on merging data in pandas: https://realpython.com/pandas-merge-join-and-concat/.