Why combining DataFrames matters in Pandas - Challenge Your Understanding

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this DataFrame merge?

Consider two DataFrames merged on a common column. What is the resulting DataFrame?

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  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   B     2     5
1   C     3     6
2   D   NaN     6

D

  key  val1  val2
0   A     1   NaN
1   B     2   NaN
2   C     3   NaN

💡 Hint

Inner merge keeps only keys present in both DataFrames.

The inner merge keeps only rows with keys 'B' and 'C' present in both DataFrames. Values from both are combined accordingly.

❓ data_output

intermediate

How many rows after concatenation?

Two DataFrames are concatenated vertically. How many rows does the result have?

Pandas

import pandas as pd

df1 = pd.DataFrame({'A': [1, 2], 'B': [3, 4]})
df2 = pd.DataFrame({'A': [5, 6, 7], 'B': [8, 9, 10]})
result = pd.concat([df1, df2], ignore_index=True)
print(len(result))

A 5

B 6

C 4

D 3

💡 Hint

Count rows in both DataFrames and add them.

df1 has 2 rows, df2 has 3 rows, so concatenation results in 5 rows total.

🔧 Debug

advanced

Why does this merge produce NaN values?

Examine the code and identify why the merged DataFrame has NaN values in some columns.

Pandas

import pandas as pd

df1 = pd.DataFrame({'id': [1, 2, 3], 'score': [90, 80, 70]})
df2 = pd.DataFrame({'id': [2, 3, 4], 'grade': ['B', 'C', 'D']})
result = pd.merge(df1, df2, on='id', how='left')
print(result)

A Because the 'id' columns have different data types causing merge failure.

B Because 'how="left"' keeps all rows from df2, missing matches in df1 cause NaN.

C Because 'how="left"' keeps all rows from df1, missing matches in df2 cause NaN.

D Because the merge key 'id' is missing in one DataFrame.

💡 Hint

Left merge keeps all rows from the left DataFrame.

Left merge keeps all rows from df1. Rows with id=1 have no match in df2, so 'grade' is NaN for that row.

❓ visualization

advanced

Which plot shows the combined data correctly?

Given two DataFrames combined by concatenation, which plot correctly shows the combined data distribution?

Pandas

import pandas as pd
import matplotlib.pyplot as plt

df1 = pd.DataFrame({'value': [1, 2, 3]})
df2 = pd.DataFrame({'value': [4, 5, 6]})
combined = pd.concat([df1, df2], ignore_index=True)
plt.hist(combined['value'])
plt.show()

A A scatter plot with points only from df1

B A histogram with bars at 1, 2, 3, 4, 5, 6 each with height 1

C A bar chart showing counts of df2 values only

D A line plot connecting points 1 to 6

💡 Hint

Histogram shows frequency of all combined values.

The histogram shows all values from both DataFrames combined, each value appears once, so bars have height 1.

🚀 Application

expert

How to combine DataFrames to keep all unique keys?

You have two DataFrames with overlapping and unique keys. You want to combine them so that all keys appear, with matching data where possible. Which merge option achieves this?

Pandas

import pandas as pd

df1 = pd.DataFrame({'key': ['X', 'Y', 'Z'], 'val1': [10, 20, 30]})
df2 = pd.DataFrame({'key': ['Y', 'Z', 'W'], 'val2': [40, 50, 60]})
result = pd.merge(df1, df2, on='key', how=?)
print(result)

A "inner"

B "right"

C "left"

D "outer"

💡 Hint

Think about keeping all keys from both DataFrames.

Outer merge keeps all keys from both DataFrames, filling missing values with NaN where no match exists.

Practice

1. Why do we combine DataFrames in pandas?
pd.concat and pd.merge are common methods. What is the main reason to combine DataFrames?

easy

A. To get a complete view by joining or stacking data

B. To delete duplicate rows automatically

C. To convert DataFrames into lists

D. To change the data types of columns

Solution

1. Step 1: Understand the purpose of combining DataFrames

   Combining DataFrames helps to bring together data from different sources or parts to analyze them as one.

2. Step 2: Identify the main reason

   Using pd.concat stacks data vertically or horizontally, and pd.merge joins data based on matching columns, both to get a fuller dataset.

3. Final Answer:

   To get a complete view by joining or stacking data -> Option A

4. Quick Check:

   Combining DataFrames = Complete view [OK]

Hint: Combining means joining or stacking data for full info [OK]

Common Mistakes:

• Thinking combining deletes duplicates automatically
• Confusing combining with data type changes
• Believing combining converts DataFrames to lists

2. Which of the following is the correct syntax to stack two DataFrames df1 and df2 vertically using pandas?

easy

A. pd.merge(df1, df2, axis=0)

B. pd.concat([df1, df2], axis=0)

C. df1.append(df2, axis=1)

D. pd.concat(df1, df2, axis=1)

Solution

1. Step 1: Recall the function to stack DataFrames vertically

   pd.concat stacks DataFrames; axis=0 means stacking rows vertically.

2. Step 2: Check the syntax

   The correct syntax is pd.concat([df1, df2], axis=0). Other options misuse pd.merge or wrong axis or argument types.

3. Final Answer:

   pd.concat([df1, df2], axis=0) -> Option B

4. Quick Check:

   Stack vertically = concat with axis=0 [OK]

Hint: Use pd.concat with a list and axis=0 to stack rows [OK]

Common Mistakes:

• Using pd.merge instead of pd.concat for stacking
• Passing DataFrames without a list to pd.concat
• Using wrong axis value for vertical stacking

3. What will be the output of this code?

import pandas as pd

df1 = pd.DataFrame({'key': ['A', 'B'], 'val1': [1, 2]})
df2 = pd.DataFrame({'key': ['B', 'C'], 'val2': [3, 4]})
result = pd.merge(df1, df2, on='key', how='inner')
print(result)

medium

A. key val1
0 A 1
1 B 2

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

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

D. key val1 val2
0 B 2 3

Solution

1. Step 1: Understand pd.merge with how='inner'

   Inner join keeps only rows with keys present in both DataFrames. Here keys are 'A', 'B' in df1 and 'B', 'C' in df2.

2. Step 2: Find common keys and merge rows

   Only 'B' is common. So result has one row with key 'B', val1=2 from df1, val2=3 from df2.

3. Final Answer:

   key val1 val2 0 B 2 3 -> Option D

4. Quick Check:

   Inner join = common keys only [OK]

Hint: Inner merge keeps only matching keys in both DataFrames [OK]

Common Mistakes:

• Expecting all keys from both DataFrames (confusing with outer join)
• Mixing up val2 values for keys
• Ignoring how='inner' effect

4. The following code throws an error. What is the main issue?

import pandas as pd

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

medium

A. Column 'A' does not exist in both DataFrames

B. pd.merge cannot merge DataFrames with different columns

C. pd.merge requires axis parameter

D. DataFrames must have same number of rows to merge

Solution

1. Step 1: Check columns in both DataFrames

   df1 has column 'A', but df2 has only column 'B'. So 'A' is missing in df2.

2. Step 2: Understand pd.merge on parameter

   pd.merge requires the 'on' column to be present in both DataFrames to join on it. Missing column causes error.

3. Final Answer:

   Column 'A' does not exist in both DataFrames -> Option A

4. Quick Check:

   Merge 'on' column must be in both DataFrames [OK]

Hint: Merge 'on' column must exist in both DataFrames [OK]

Common Mistakes:

• Thinking axis parameter is needed for merge
• Believing DataFrames must have same row count
• Assuming merge works with different columns without 'on'

5. You have two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 3], 'score': [90, 80, 70]})
df2 = pd.DataFrame({'id': [2, 3, 4], 'grade': ['B', 'C', 'A']})

You want a DataFrame that includes all ids from both, with scores and grades matched where possible, and NaN where missing. Which code achieves this?

hard

A. pd.concat([df1, df2], axis=0)

B. pd.concat([df1, df2], axis=1)

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

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

Solution

1. Step 1: Understand the goal

   We want all ids from both DataFrames, matching scores and grades where ids overlap, and NaN where data is missing.

2. Step 2: Choose the correct merge type

   Using how='outer' in pd.merge keeps all keys from both DataFrames, filling missing values with NaN.

3. Step 3: Check other options

   pd.concat stacks data but does not join on keys; how='inner' drops ids not in both.

4. Final Answer:

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

5. Quick Check:

   Outer merge = all keys, fill missing with NaN [OK]

Hint: Use outer merge to keep all keys and fill missing data [OK]

Common Mistakes:

• Using inner merge and losing unmatched ids
• Using concat which stacks instead of joining
• Confusing axis parameter with merge how