Merging on multiple keys in Data Analysis Python - Time & Space Complexity

Time Complexity: Merging on multiple keys

O(n)

Understanding Time Complexity

When we merge two tables using more than one key, it takes some time to find matching rows.

We want to know how the time needed grows as the tables get bigger.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

import pandas as pd

merged_df = pd.merge(df1, df2, on=['key1', 'key2'])

This code merges two dataframes using two columns as keys to match rows.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Checking each row in one table against rows in the other based on both keys.
• How many times: For each row in the first table, it looks up matching rows in the second table.

How Execution Grows With Input

As the number of rows in the tables grows, the time to find matches grows roughly in proportion to the size of the tables.

Input Size (n)	Approx. Operations
10	About 10 lookups
100	About 100 lookups
1000	About 1000 lookups

Pattern observation: The time grows roughly in a straight line as the tables get bigger.

Final Time Complexity

Time Complexity: O(n)

This means the time to merge grows roughly in direct proportion to the number of rows in the tables.

Common Mistake

[X] Wrong: "Merging on multiple keys multiplies the time by the number of keys, so it's much slower than merging on one key."

[OK] Correct: The merge uses efficient lookups combining keys, so adding more keys does not multiply the time by the number of keys; it still scales mostly with the number of rows.

Interview Connect

Understanding how merging scales helps you explain data joining in real projects clearly and confidently.

Self-Check

"What if we merged on three keys instead of two? How would the time complexity change?"

Practice

1.

What does merging on multiple keys in pandas allow you to do?

Choose the best explanation.

easy

A. Join dataframes by adding new columns without matching rows.

B. Merge dataframes only by index without considering columns.

C. Sort dataframes based on multiple columns before merging.

D. Combine two dataframes using more than one column to match rows.

Solution

1. Step 1: Understand merging concept

   Merging combines rows from two dataframes based on matching column values.

2. Step 2: Identify multiple keys usage

   Using multiple keys means matching rows where all specified columns have the same values.

3. Final Answer:

   Combine two dataframes using more than one column to match rows. -> Option D

4. Quick Check:

   Merging on multiple keys = combine on several columns [OK]

Hint: Think of matching rows by multiple columns at once [OK]

Common Mistakes:

• Confusing merge with concatenation
• Using only one column when multiple are needed
• Merging without specifying keys
• Assuming merge sorts data automatically

2.

Which of the following is the correct syntax to merge two pandas DataFrames df1 and df2 on columns 'city' and 'year'?

import pandas as pd

# Assume df1 and df2 are defined
merged_df = 

easy

A. pd.merge(df1, df2, on=['city', 'year'])

B. pd.merge(df1, df2, keys=['city', 'year'])

C. df1.merge(df2, by=['city', 'year'])

D. df1.join(df2, on=['city', 'year'])

Solution

1. Step 1: Recall pandas merge syntax

   The correct parameter to specify columns for merging is on with a list of column names.

2. Step 2: Check each option

   pd.merge(df1, df2, on=['city', 'year']) uses on=['city', 'year'], which is correct. Others use wrong parameters like keys or by.

3. Final Answer:

   pd.merge(df1, df2, on=['city', 'year']) -> Option A

4. Quick Check:

   Use on for columns list in merge [OK]

Hint: Use 'on' parameter with list of columns for multiple keys [OK]

Common Mistakes:

• Using 'keys' instead of 'on'
• Using 'by' instead of 'on'
• Using join() instead of merge() for multiple keys
• Passing columns as a string instead of list

3.

Given the following dataframes:

import pandas as pd

df1 = pd.DataFrame({
    'city': ['NY', 'LA', 'NY', 'SF'],
    'year': [2020, 2020, 2021, 2020],
    'value1': [10, 20, 30, 40]
})

df2 = pd.DataFrame({
    'city': ['NY', 'LA', 'NY', 'SF'],
    'year': [2020, 2021, 2021, 2020],
    'value2': [100, 200, 300, 400]
})

merged = pd.merge(df1, df2, on=['city', 'year'], how='inner')
print(merged)

What will be the output?

medium

A. All rows from df1 with matching rows from df2 or NaN if no match

B. All rows from df2 with matching rows from df1 or NaN if no match

C. Rows where city and year match in both dfs: NY-2020, NY-2021, SF-2020

D. Concatenation of df1 and df2 ignoring keys

Solution

1. Step 1: Understand inner merge on multiple keys

   Inner merge returns rows where all keys match in both dataframes.

2. Step 2: Identify matching rows

   Matching pairs are: ('NY', 2020), ('NY', 2021), and ('SF', 2020). 'LA' with year 2020 or 2021 does not match in both.

3. Final Answer:

   Rows where city and year match in both dfs: NY-2020, NY-2021, SF-2020 -> Option C

4. Quick Check:

   Inner merge = only matching keys rows [OK]

Hint: Inner merge keeps only rows matching all keys [OK]

Common Mistakes:

• Confusing inner with outer merge
• Expecting all rows from one dataframe
• Ignoring that both keys must match
• Assuming concatenation instead of merge

4.

Consider this code snippet:

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [1, 2], 'B': [3, 5]})

merged = pd.merge(df1, df2, on=['A', 'B'])
print(merged)

What is the issue if the output is an empty dataframe?

medium

A. The values in column 'B' do not match exactly in both dataframes for all rows.

B. The merge syntax is incorrect; 'on' should be 'by'.

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

D. You cannot merge on multiple columns with the same names.

Solution

1. Step 1: Check merge keys values

   Rows merge only if all key columns match exactly. Here, df1 has B=4 in second row, df2 has B=5.

2. Step 2: Understand empty result cause

   Because second rows differ in 'B', only first rows match. First rows have A=1 and B=3 in both, so one row should merge. If output is empty, likely no exact matches.

3. Final Answer:

   The values in column 'B' do not match exactly in both dataframes for all rows. -> Option A

4. Quick Check:

   Exact key match needed for merge rows [OK]

Hint: Check all key columns have matching values for merge [OK]

Common Mistakes:

• Thinking merge needs same row count
• Using wrong parameter name 'by'
• Assuming merge works without exact key matches
• Believing multiple keys with same names cause error

5.

You have two dataframes:

df_sales = pd.DataFrame({
    'store': ['A', 'B', 'A', 'B'],
    'month': [1, 1, 2, 2],
    'sales': [100, 200, 150, 250]
})

df_targets = pd.DataFrame({
    'store': ['A', 'B'],
    'month': [1, 2],
    'target': [120, 230]
})

You want to merge these to compare sales with targets for matching store and month. Which merge type should you use to keep all sales data and add targets where available?

hard

A. Use how='outer' to keep all rows from both dataframes.

B. Use how='left' to keep all sales rows and add matching targets.

C. Use how='right' to keep all targets and add matching sales.

D. Use how='inner' to keep only rows with matching store and month in both.

Solution

1. Step 1: Identify main dataframe to keep

   You want to keep all sales data, so df_sales is the left/main dataframe.

2. Step 2: Choose merge type

   Left merge keeps all rows from left dataframe and adds matching rows from right. Missing matches get NaN.

3. Final Answer:

   Use how='left' to keep all sales rows and add matching targets. -> Option B

4. Quick Check:

   Left merge = keep left all, add right matches [OK]

Hint: Left merge keeps all left rows, adds matching right rows [OK]

Common Mistakes:

• Using inner merge and losing unmatched sales
• Using right merge and losing unmatched sales
• Using outer merge and getting extra unmatched rows
• Confusing which dataframe is left or right