merge() for SQL-style joins in Data Analysis Python

Introduction

We use merge() to combine two tables based on common columns, just like joining tables in a database. This helps us see related information together.

You have two lists of customer data and want to see all details in one place.

You want to combine sales data with product details using product IDs.

You need to find matching records between two datasets, like students and their grades.

You want to keep all records from one table and add matching info from another.

You want to find records that appear in both tables or only in one.

Syntax

Data Analysis Python

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None)

left and right are the two tables (DataFrames) to join.

how decides the type of join: 'inner', 'left', 'right', or 'outer'.

Examples

Join two tables on the column 'id' keeping only matching rows (inner join).

Data Analysis Python

pd.merge(df1, df2, on='id')

Keep all rows from df1 and add matching rows from df2.

Data Analysis Python

pd.merge(df1, df2, how='left', on='id')

Keep all rows from both tables, filling missing matches with NaN.

Data Analysis Python

pd.merge(df1, df2, how='outer', on='id')

Join on different column names from each table.

Data Analysis Python

pd.merge(df1, df2, left_on='key1', right_on='key2')

Sample Program

This code shows three types of joins between customers and their orders using merge(). It helps see which customers have orders and which don't.

Data Analysis Python

import pandas as pd

# Create first table with customer info
df_customers = pd.DataFrame({
    'customer_id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Charlie', 'David']
})

# Create second table with orders info
df_orders = pd.DataFrame({
    'order_id': [101, 102, 103, 104],
    'customer_id': [2, 2, 3, 5],
    'product': ['Book', 'Pen', 'Notebook', 'Pencil']
})

# Inner join: only customers with orders
inner_join = pd.merge(df_customers, df_orders, on='customer_id', how='inner')
print('Inner Join Result:')
print(inner_join)

# Left join: all customers, with orders if any
left_join = pd.merge(df_customers, df_orders, on='customer_id', how='left')
print('\nLeft Join Result:')
print(left_join)

# Outer join: all customers and all orders
outer_join = pd.merge(df_customers, df_orders, on='customer_id', how='outer')
print('\nOuter Join Result:')
print(outer_join)

Important Notes

If columns have different names, use left_on and right_on to specify them.

Missing matches appear as NaN in the result.

Inner join keeps only rows with matching keys in both tables.

Summary

merge() combines tables like database joins.

Use how to choose join type: inner, left, right, or outer.

Specify columns to join on with on, or left_on and right_on.

Practice

1. What does the how parameter in merge() control when joining two DataFrames?

easy

A. The data types of the columns

B. The columns to join on

C. The order of rows in the result

D. The type of join: inner, left, right, or outer

Solution

1. Step 1: Understand the role of how in merge()

   The how parameter specifies the type of join to perform, such as inner, left, right, or outer join.

2. Step 2: Differentiate from other parameters

   Other parameters like on specify columns to join on, but how controls how rows are matched and combined.

3. Final Answer:

   The type of join: inner, left, right, or outer -> Option D

4. Quick Check:

   how = join type [OK]

Hint: Remember: 'how' means join type like SQL joins [OK]

Common Mistakes:

• Confusing 'how' with 'on' parameter
• Thinking 'how' controls column selection
• Assuming 'how' changes data types

2. Which of the following is the correct syntax to merge two DataFrames df1 and df2 on the column 'id' using an inner join?

easy

A. pd.merge(df1, df2, join='id', how='inner')

B. pd.merge(df1, df2, on='id', how='inner')

C. df1.merge(df2, on='id', type='inner')

D. pd.merge(df1, df2, on='id', join='inner')

Solution

1. Step 1: Identify correct parameter names

   The correct parameters for merging are on for the column and how for join type.

2. Step 2: Check syntax correctness

   pd.merge(df1, df2, on='id', how='inner') uses pd.merge(df1, df2, on='id', how='inner'), which is the correct syntax. Other options use incorrect parameter names like join or type.

3. Final Answer:

   pd.merge(df1, df2, on='id', how='inner') -> Option B

4. Quick Check:

   Correct parameters: on, how [OK]

Hint: Use 'on' for columns and 'how' for join type [OK]

Common Mistakes:

• Using 'join' instead of 'how'
• Using 'type' instead of 'how'
• Mixing parameter names

3. Given the following code, what is the output of result?

import pandas as pd

df1 = pd.DataFrame({'key': ['A', 'B', 'C'], 'val1': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['B', 'C', 'D'], 'val2': [4, 5, 6]})

result = pd.merge(df1, df2, on='key', how='inner')
print(result)

medium

A. key val1 val2
0 B 2 4
1 C 3 5

B. key val1 val2
0 A 1 4
1 B 2 5
2 C 3 6

C. key val1 val2
0 A 1 NaN
1 B 2 4
2 C 3 5

D. key val1 val2
0 B 2 NaN
1 C 3 NaN
2 D NaN 6

Solution

1. Step 1: Understand inner join on 'key'

   Inner join keeps only rows with keys present in both DataFrames: 'B' and 'C'.

2. Step 2: Match rows and combine columns

   For 'B': val1=2, val2=4; for 'C': val1=3, val2=5. Rows with 'A' and 'D' are excluded.

3. Final Answer:

   key val1 val2 0 B 2 4 1 C 3 5 -> Option A

4. Quick Check:

   Inner join = intersection keys [OK]

Hint: Inner join keeps only common keys [OK]

Common Mistakes:

• Including keys not in both DataFrames
• Confusing inner with outer join
• Misaligning values for keys

4. What is wrong with this code snippet?

import pandas as pd

df1 = pd.DataFrame({'id': [1, 2], 'val': ['A', 'B']})
df2 = pd.DataFrame({'key': [1, 3], 'val2': ['X', 'Y']})

result = pd.merge(df1, df2, on='id', how='left')
print(result)

medium

A. DataFrames must have the same number of rows to merge

B. The 'how' parameter cannot be 'left'

C. The merge function is missing the 'left_on' and 'right_on' parameters

D. Column 'id' does not exist in both DataFrames

Solution

1. Step 1: Check columns used for merging

   The code tries to merge on column 'id', but df2 has 'key' column instead of 'id'.

2. Step 2: Understand merge requirements

   For merging on different column names, left_on and right_on must be used, or columns must have the same name.

3. Final Answer:

   The merge function is missing the 'left_on' and 'right_on' parameters -> Option C

4. Quick Check:

   Merge columns must exist in both DataFrames or be specified [OK]

Hint: Check column names exist in both DataFrames or specify left_on/right_on [OK]

Common Mistakes:

• Assuming merge works with different column names without specifying
• Thinking 'how' parameter is invalid
• Believing row counts must match

5. You have two DataFrames: orders with columns order_id, customer_id, and amount, and customers with columns cust_id and name. How do you merge them to get all orders with customer names, even if some customers are missing in customers DataFrame?

hard

A. pd.merge(orders, customers, left_on='customer_id', right_on='cust_id', how='left')

B. pd.merge(orders, customers, on='customer_id', how='inner')

C. pd.merge(orders, customers, left_on='cust_id', right_on='customer_id', how='right')

D. pd.merge(orders, customers, on='cust_id', how='outer')

Solution

1. Step 1: Identify join keys with different column names

   Orders use 'customer_id', customers use 'cust_id', so use left_on and right_on to specify.

2. Step 2: Choose join type to keep all orders

   Use how='left' to keep all rows from orders, even if no matching customer exists.

3. Final Answer:

   pd.merge(orders, customers, left_on='customer_id', right_on='cust_id', how='left') -> Option A

4. Quick Check:

   Left join keeps all left rows [OK]

Hint: Use left_on/right_on for different keys and 'left' to keep all left rows [OK]

Common Mistakes:

• Using 'on' with different column names
• Using inner join and losing unmatched orders
• Swapping left_on and right_on