Inner join behavior in Pandas - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this inner join?

Given two dataframes df1 and df2, what is the result of the inner join on column key?

Pandas

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)

A [{'key': 'A', 'val1': 1, 'val2': 4}, {'key': 'B', 'val1': 2, 'val2': 5}]

B [{'key': 'B', 'val1': 2, 'val2': 4}, {'key': 'C', 'val1': 3, 'val2': 5}]

C [{'key': 'A', 'val1': 1, 'val2': 6}, {'key': 'D', 'val1': 3, 'val2': 6}]

D [{'key': 'A', 'val1': 1, 'val2': null}, {'key': 'B', 'val1': 2, 'val2': 4}, {'key': 'C', 'val1': 3, 'val2': 5}, {'key': 'D', 'val1': null, 'val2': 6}]

💡 Hint

Inner join returns only rows with matching keys in both dataframes.

The inner join keeps only keys present in both df1 and df2. Here, keys 'B' and 'C' are common.

🧠 Conceptual

intermediate

Which statement best describes an inner join?

Choose the correct description of an inner join operation between two tables or dataframes.

A Returns only rows where keys exist in both tables, excluding all others.

B Returns all rows from the left table and matching rows from the right table, filling with nulls if no match.

C Returns all rows from the right table and matching rows from the left table, filling with nulls if no match.

D Returns all rows from both tables, combining matching rows and filling nulls where no match exists.

💡 Hint

Think about which rows appear in the result when keys do not match.

An inner join returns only rows with keys present in both tables, excluding rows without matches.

📝 Syntax

advanced

Identify the correct syntax for an inner join in pandas

Which of the following code snippets correctly performs an inner join on column id between df_left and df_right?

A df_left.merge(df_right, on='id', join='inner')

B df_left.join(df_right, on='id', how='inner')

C pd.join(df_left, df_right, on='id', how='inner')

D pd.merge(df_left, df_right, on='id', how='inner')

💡 Hint

Recall the correct function and parameter names for pandas merge.

The pd.merge() function with how='inner' and on='id' is the correct syntax.

❓ optimization

advanced

Optimizing inner join performance on large dataframes

You have two large dataframes with millions of rows each. Which approach can improve the performance of an inner join on column user_id?

A Ensure both dataframes have <code>user_id</code> as an index before merging.

B Convert <code>user_id</code> columns to string type before merging.

C Drop duplicates from both dataframes before merging regardless of keys.

D Use <code>how='outer'</code> instead of <code>how='inner'</code> to reduce computation.

💡 Hint

Indexes can speed up join operations.

Setting the join key as index allows pandas to use faster join algorithms internally.

🔧 Debug

expert

Why does this inner join produce an empty dataframe?

Given the code below, why is the result of the inner join empty?

Pandas

import pandas as pd

df1 = pd.DataFrame({'key': ['a', 'b', 'c'], 'val': [1, 2, 3]})
df2 = pd.DataFrame({'key': ['A', 'B', 'C'], 'val': [4, 5, 6]})

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

A The merge function requires specifying suffixes for overlapping columns.

B The 'val' columns have different names causing the join to fail.

C The keys differ in case sensitivity; 'a' != 'A', so no matches occur.

D The 'how' parameter should be 'left' instead of 'inner' to get results.

💡 Hint

Check if the keys match exactly including letter case.

Inner join matches keys exactly. Here, lowercase and uppercase letters differ, so no rows match.