library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, unionLoad the Customers and Orders datasets
customers <- tibble(
customer_id = c(1, 2, 3, 4, 5),
name = c("Alice", "Bob", "Charlie", "David", "Eve"),
city = c("New York", "Los Angeles", "Chicago", "Houston", "Phoenix")
)
orders <- tibble(
order_id = c(101, 102, 103, 104, 105, 106),
customer_id = c(1, 2, 3, 2, 6, 7),
product = c("Laptop", "Phone", "Tablet", "Desktop", "Camera", "Printer"),
amount = c(1200, 800, 300, 1500, 600, 150)
)1. Inner Join (3 points) Perform an inner join between the customers and orders datasets.
q1 <- inner_join(customers , orders, by = "customer_id")How many rows are in the result?
There are 4 rows in the result.
Why are some customers or orders not included in the
result?
The customer data only has three matches to the order data
Display the result
print(q1)## # A tibble: 4 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 3002. Left Join (3 points) Perform a left join with customers as the left table and orders as the right table.
q2 <- left_join(customers, orders, by = "customer_id")How many rows are in the result?
There are 6 rows in the result.
Explain why this number differs from the inner join result.
This number differs from the inner join results because inner join only
matches data sets that match.
Display the result
print(q2)## # A tibble: 6 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 300
## 5 4 David Houston NA <NA> NA
## 6 5 Eve Phoenix NA <NA> NA3. Right Join (3 points) Perform a right join with customers as the left table and orders as the right table.
q3 <- right_join(customers, orders, by = "customer_id")How many rows are in the result?
There are 6 rows in the result.
Which customer_ids in the result have NULL for customer name and
city? Explain why.
customer_ids 6 and 7 because there is no data about them in
customers
Display the result
print(q3)## # A tibble: 6 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 300
## 5 6 <NA> <NA> 105 Camera 600
## 6 7 <NA> <NA> 106 Printer 1504. Full Join (3 points) Perform a full join between customers and orders.
q4 <- full_join(customers, orders, by = "customer_id")How many rows are in the result?
There are 8 rows in the result.
Identify any rows where there’s information from only one table.
Explain these results.
Rows 5, 6, 7, and 8. This is because there is no match to the data from
both orders and customers.
Display the result
print(q4)## # A tibble: 8 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 300
## 5 4 David Houston NA <NA> NA
## 6 5 Eve Phoenix NA <NA> NA
## 7 6 <NA> <NA> 105 Camera 600
## 8 7 <NA> <NA> 106 Printer 1505. Semi Join (3 points) Perform a semi join with customers as the left table and orders as the right table.
q5 <- semi_join(customers, orders, by = "customer_id")How many rows are in the result?
There are 3 rows in the result.
How does this result differ from the inner join result?
Semi join shows the matches from customers, but not orders.
Display the result
print(q5)## # A tibble: 3 × 3
## customer_id name city
## <dbl> <chr> <chr>
## 1 1 Alice New York
## 2 2 Bob Los Angeles
## 3 3 Charlie Chicago6. Anti Join (3 points) Perform an anti join with customers as the left table and orders as the right table.
q6 <- anti_join(customers, orders, by = "customer_id")Which customers are in the result?
There are 2 rows in the result.
Explain what this result tells you about these customers.
The results tell me that customers 4 and 5 have not placed any
orders.
Display the result
print(q6)## # A tibble: 2 × 3
## customer_id name city
## <dbl> <chr> <chr>
## 1 4 David Houston
## 2 5 Eve Phoenix7. Practical Application (4 points) Imagine you’re analyzing customer behavior.
Which join would you use to find all customers, including those
who haven’t placed any orders? Why?
I would use left or right join because left and right join returns all
rows from the left/right table and matching rows from the right/left
table.
Which join would you use to find only the customers who have
placed orders? Why?
I would use inner join because inner join returns only matches
Write the R code for both scenarios.
q7a <- left_join(customers, orders, by = "customer_id")q7b <- inner_join(customers , orders, by = "customer_id")print(q7a)## # A tibble: 6 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 300
## 5 4 David Houston NA <NA> NA
## 6 5 Eve Phoenix NA <NA> NAprint(q7b)## # A tibble: 4 × 6
## customer_id name city order_id product amount
## <dbl> <chr> <chr> <dbl> <chr> <dbl>
## 1 1 Alice New York 101 Laptop 1200
## 2 2 Bob Los Angeles 102 Phone 800
## 3 2 Bob Los Angeles 104 Desktop 1500
## 4 3 Charlie Chicago 103 Tablet 3008 Challenge Question (3 points) Create a summary that shows each customer’s name, city, total number of orders, and total amount spent. Include all customers, even those without orders. Hint: You’ll need to use a combination of joins and group_by/summarize operations.
Merge customers with orders, ensuring all customers are included
customer_summary <- customers %>%
left_join(orders, by = "customer_id") %>%
group_by(customer_id, name, city) %>%
summarise(
total_orders = sum(!is.na(order_id)), # Count of orders, which will be 0 for those without orders
total_amount = sum(amount, na.rm = TRUE) # Total amount spent, will be 0 if no orders
) %>%
ungroup()## `summarise()` has grouped output by 'customer_id', 'name'. You can override
## using the `.groups` argument.print(customer_summary)## # A tibble: 5 × 5
## customer_id name city total_orders total_amount
## <dbl> <chr> <chr> <int> <dbl>
## 1 1 Alice New York 1 1200
## 2 2 Bob Los Angeles 2 2300
## 3 3 Charlie Chicago 1 300
## 4 4 David Houston 0 0
## 5 5 Eve Phoenix 0 0