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, union

# Dataset 1: Customers
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")
)

# Dataset 2: Orders
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)
)

Q1: 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?

nrow(q1)

## [1] 4

Why are some customers or orders not included in the result? Some customers and orders are not included because an inner join only keeps rows where there is a match in both tables. If a customer_id exists in customers but not in orders, or vice versa, that row is excluded from the result.

Display the result

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     300

Q2: 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?

  nrow(q2)

## [1] 6

Explain why this number differs from the inner join result.
Some customers and orders are not included because an inner join only keeps rows where there is a match in both tables. If a customer_id exists in customers but not in orders, or vice versa, that row is excluded from the result.

Display the result

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>        NA

Q3: 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?

nrow(q3)

## [1] 6

Which customer_ids in the result have NULL for customer name and city? Explain why. The customer ids that have NULL for customer name and city are 6 and 7. That is because they exist in the orders table but not in the customers table. Since a right join keeps all rows from orders, missing matches from customers result in NULL values.

Display the result

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    150

Q4: #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?

  nrow(q4)

## [1] 8

Identify any rows where there’s information from only one table. Explain these results.

The rows that only have information from one table are 5-8. If a row has data only from customers, it means the customer_id exists in customers but not in orders. If a row has data only from orders, it means the customer_id exists in orders but not in customers. Since a full join keeps all records from both tables, unmatched values appear as NULL in columns from the missing table.

Display the result

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    150

Q5: 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?

nrow(q5)

## [1] 3

How does this result differ from the inner join result?

The semi join result differs from the inner join result as it only keeps matching rows from customers but doesn’t add columns from orders. An inner join includes matching rows from both tables and combines their columns.

Display the result

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 Chicago

Q6: 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?

The customers that are in the result are David and Eve.

Explain what this result tells you about these customers.

David and Eve are in the result because an anti join returns only the rows from customers that have no match in orders. This means David and Eve have no recorded orders in the orders table.

Display the result

q6

## # A tibble: 2 × 3
##   customer_id name  city   
##         <dbl> <chr> <chr>  
## 1           4 David Houston
## 2           5 Eve   Phoenix

Q7: Practical Application (4 points) Imagine you’re analyzing customer behavior.

a. Which join would you use to find all customers, including those who haven’t placed any orders? Why?

b. Which join would you use to find only the customers who have placed orders? Why?

Display the result

Write the R code for both scenarios.

We would use left join as it includes all customers, even those without orders.

left_join <- left_join(customers, orders, by = 'customer_id')
print(left_join)

## # 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>        NA

We would use a semi join as it ensures only customers who have placed orders are included.

semi_join <- semi_join(customers, orders, by = 'customer_id')
print(semi_join)

## # A tibble: 3 × 3
##   customer_id name    city       
##         <dbl> <chr>   <chr>      
## 1           1 Alice   New York   
## 2           2 Bob     Los Angeles
## 3           3 Charlie Chicago

Q8: 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.

Joining the datasets to get customer info with their readers

summary_data <- customers %>%
  left_join(orders, by = 'customer_id') %>%
  group_by(customer_id, name, city) %>%                                         
  summarize(
    total_orders = sum(!is.na(order_id)),                                      
    total_spent = sum(amount, na.rm = TRUE)
  ) %>%
    ungroup()

Viewing the result

print(summary_data)

## # A tibble: 5 × 5
##   customer_id name    city        total_orders total_spent
##         <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

Assignment 4

Raquel DeLeo

2025-02-18

Q1: Inner Join (3 points) Perform an inner join between the customers and orders datasets.

Q2: Left Join (3 points) Perform a left join with customers as the left table and orders as the right table.

Q3: Right Join (3 points) Perform a right join with customers as the left table and orders as the right table.

Q4: #Full Join (3 points) Perform a full join between customers and orders.

Q5: Semi Join (3 points) Perform a semi join with customers as the left table and orders as the right table.

Q6: Anti Join (3 points) Perform an anti join with customers as the left table and orders as the right table.

Q7: Practical Application (4 points) Imagine you’re analyzing customer behavior.

Q8: 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.