Merging data frames in R Programming - Mini Project: Build & Apply

Merging data frames

📖 Scenario: You work in a small company that keeps customer and order information in separate tables. You want to combine these tables to see which customers made which orders.

🎯 Goal: Build a simple R program that merges two data frames: one with customer details and one with order details, using a common column.

📋 What You'll Learn

Create two data frames with exact given data

Create a variable for the common column name

Use the merge() function with the common column

Print the merged data frame

💡 Why This Matters

🌍 Real World

Combining customer and order data is common in business to analyze sales and customer behavior.

💼 Career

Data analysts and data scientists often merge data from different sources to prepare for analysis and reporting.

1

Create the initial data frames

Create a data frame called customers with columns customer_id and name containing these rows: (1, "Alice"), (2, "Bob"), (3, "Charlie"). Also create a data frame called orders with columns order_id, customer_id, and product containing these rows: (101, 1, "Book"), (102, 2, "Pen"), (103, 1, "Notebook").

R Programming

# Create the customers and orders data frames with the exact data
# Your code here

Hint

Use data.frame() with vectors for each column to create the data frames.

2

Set the common column name

Create a variable called common_col and set it to the string "customer_id" to use as the key for merging.

R Programming

customers <- data.frame(customer_id = c(1, 2, 3), name = c("Alice", "Bob", "Charlie"))
orders <- data.frame(order_id = c(101, 102, 103), customer_id = c(1, 2, 1), product = c("Book", "Pen", "Notebook"))
# Create the variable common_col with value "customer_id"
# Your code here

Hint

Just assign the string "customer_id" to the variable common_col.

3

Merge the data frames

Use the merge() function to merge customers and orders by the column stored in common_col. Save the result in a variable called merged_data.

R Programming

customers <- data.frame(customer_id = c(1, 2, 3), name = c("Alice", "Bob", "Charlie"))
orders <- data.frame(order_id = c(101, 102, 103), customer_id = c(1, 2, 1), product = c("Book", "Pen", "Notebook"))
common_col <- "customer_id"
# Merge customers and orders by common_col into merged_data
# Your code here

Hint

Use merge(customers, orders, by = common_col) to join the tables on the common column.

4

Print the merged data frame

Write a print() statement to display the merged_data data frame.

R Programming

customers <- data.frame(customer_id = c(1, 2, 3), name = c("Alice", "Bob", "Charlie"))
orders <- data.frame(order_id = c(101, 102, 103), customer_id = c(1, 2, 1), product = c("Book", "Pen", "Notebook"))
common_col <- "customer_id"
merged_data <- merge(customers, orders, by = common_col)
# Print the merged_data data frame
# Your code here

Hint

Use print(merged_data) to show the combined table.

Practice

1. What does the merge() function do in R when working with data frames?

easy

A. It combines two data frames by matching rows based on common columns.

B. It deletes rows from a data frame.

C. It sorts a data frame alphabetically.

D. It converts a data frame into a list.

Solution

1. Step 1: Understand the purpose of merge()

   The merge() function is designed to combine two data frames by matching rows based on one or more common columns.

2. Step 2: Eliminate incorrect options

   Options A, B, and D describe different operations unrelated to merging data frames.

3. Final Answer:

   It combines two data frames by matching rows based on common columns. -> Option A

4. Quick Check:

   merge() combines data frames by matching columns [OK]

Hint: Remember: merge() joins data frames on matching columns [OK]

Common Mistakes:

• Thinking merge() deletes rows
• Confusing merge() with sorting functions
• Assuming merge() converts data frames to lists

2. Which of the following is the correct syntax to merge two data frames df1 and df2 by a common column id in R?

easy

A. merge(df1, df2, on = "id")

B. merge(df1, df2, by = "id")

C. merge(df1, df2, key = "id")

D. merge(df1, df2, match = "id")

Solution

1. Step 1: Recall merge() argument names

   The correct argument to specify the column to merge on is by, not on, key, or match.

2. Step 2: Check syntax correctness

   merge(df1, df2, by = "id") uses by = "id", which is the correct syntax for merging by the column named "id".

3. Final Answer:

   merge(df1, df2, by = "id") -> Option B

4. Quick Check:

   Use by= to specify merge column [OK]

Hint: Use by= to specify columns in merge() [OK]

Common Mistakes:

• Using on= instead of by=
• Using key= or match= which are invalid
• Forgetting to quote the column name

3. Given the following R code:

df1 <- data.frame(id = c(1, 2, 3), val1 = c("A", "B", "C"))
df2 <- data.frame(id = c(2, 3, 4), val2 = c("X", "Y", "Z"))
result <- merge(df1, df2, by = "id", all = FALSE)
print(result)

What will be the output?

medium

A. id val1 val2
1 2 B X
2 3 C Y

B. id val1 val2
1 1 A <NA>
2 2 B X
3 3 C Y
4 4 <NA> Z

C. id val1 val2
1 1 A X
2 2 B Y
3 3 C Z

D. Error: all is not a valid argument

Solution

1. Step 1: Understand merge with all = FALSE

   Setting all = FALSE performs an inner join, keeping only rows with matching id in both data frames.

2. Step 2: Identify matching ids and resulting rows

   Matching ids are 2 and 3. So rows with id 1 and 4 are excluded. The merged data frame has rows for id 2 and 3 with corresponding values.

3. Final Answer:

   id val1 val2 1 2 B X 2 3 C Y -> Option A

4. Quick Check:

   Inner join keeps only matching ids [OK]

Hint: all=FALSE means keep only matching rows [OK]

Common Mistakes:

• Expecting all rows to appear (confusing inner and outer join)
• Misreading which ids match
• Thinking all=FALSE causes error

4. Consider this R code snippet:

df1 <- data.frame(id = c(1, 2), val1 = c("A", "B"))
df2 <- data.frame(id = c(2, 3), val2 = c("X", "Y"))
result <- merge(df1, df2, by = "id", all.x = TRUE, all.y = TRUE)
print(result)

What is the error in this code?

medium

A. Using both all.x and all.y together is invalid syntax.

B. The merge function requires a parameter named all, not all.x or all.y.

C. The data frames have different column names, causing a merge error.

D. There is no error; the code performs a full outer join correctly.

Solution

1. Step 1: Understand all.x and all.y parameters

   In R, all.x = TRUE keeps all rows from the first data frame, all.y = TRUE keeps all rows from the second. Using both performs a full outer join.

2. Step 2: Check for syntax or logical errors

   The syntax is correct and the data frames can be merged on id even if some ids don't match. No error occurs.

3. Final Answer:

   There is no error; the code performs a full outer join correctly. -> Option D

4. Quick Check:

   all.x and all.y together = full outer join [OK]

Hint: all.x=TRUE and all.y=TRUE means full outer join [OK]

Common Mistakes:

• Thinking all.x and all.y cannot be used together
• Confusing all with all.x or all.y
• Assuming different column names cause error

5. You have two data frames:

df1 <- data.frame(id = c(1, 2, 2, 3), val1 = c("A", "B", "C", "D"))
df2 <- data.frame(id = c(2, 2, 4), val2 = c("X", "Y", "Z"))

You want to merge them to keep all rows from df1 and add matching val2 values. Which code correctly merges and keeps all df1 rows, including duplicates, showing NA where no match exists?

hard

A. merge(df1, df2, by = "id", all = TRUE)

B. merge(df1, df2, by = "id", all.y = TRUE)

C. merge(df1, df2, by = "id", all.x = TRUE)

D. merge(df1, df2, by = "id")

Solution

1. Step 1: Understand all.x = TRUE behavior

   Setting all.x = TRUE keeps all rows from df1, including duplicates, and adds matching rows from df2. Non-matching rows get NA.

2. Step 2: Compare with other options

   merge(df1, df2, by = "id", all = TRUE) keeps all rows from both data frames (full outer join), which is not required. merge(df1, df2, by = "id", all.y = TRUE) keeps all rows from df2 only. merge(df1, df2, by = "id") keeps only matching rows (inner join).

3. Final Answer:

   merge(df1, df2, by = "id", all.x = TRUE) -> Option C

4. Quick Check:

   all.x=TRUE keeps all df1 rows with matches or NA [OK]

Hint: Use all.x=TRUE to keep all rows from first data frame [OK]

Common Mistakes:

• Using all=TRUE instead of all.x=TRUE
• Using all.y=TRUE which keeps second data frame rows
• Forgetting duplicates are kept automatically