Assignment 4

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

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

q1 <- inner_join(customers , orders)

## Joining with `by = join_by(customer_id)`

How many rows are in the result?
There are 4 rows.
Why are some customers or orders not included in the result?
Inner join returns where this is a match on both tables. The customer data only has 3 customers in the order table where one customer has 2 orders.
Display the result

head(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

2. 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.
Explain why this number differs from the inner join result.
Left join returns all rows from the left table and matching rows from the right table whereas inner join returns all rows where there is a match on both tables.
Display the result

head(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

3. 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.
Which customer_ids in the result have NULL for customer name and city? Explain why.
customer_ids 6 and 7 have NULL for customer name and city. The NA values indicate that there was no corresponding entry in the left table for that particular entry in the right table.
Display the result

head(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

4. 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.
Identify any rows where there’s information from only one table. Explain these results.
Rows 5 and 6 have data only from the customers table and rows 7 and 8 have data only from the orders table. I found the rows with data from only one table by looking at which rows had NA for certain columns. For example, row 5 had data in name and city, both columns in the customers table, and NA for the other columns which are only in the orders table.
Display the result

head(q4)

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

5. 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.
How does this result differ from the inner join result?
An inner join returns all rows from both tables where there is a match while a semi join returns all rows from the left table where there is a match in the right table.
Display the result

head(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

6. 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?
Customers 4 and 5, David and Eve respectively, are in the result.
Explain what this result tells you about these customers.
An anti join in R shows you which rows in one data frame don’t have matches in another data frame. Therefore in this case, the result is telling us that customers 4 and 5 are in the customers data but not the orders data.
Display the result

head(q6)

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

7. 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?
You would use a full join because a full join returns all records from both tables, including those that don’t have matches.
Which join would you use to find only the customers who have placed orders? Why?
You would use an right join because A right join returns all records from the right table (orders) and the matched records from the left table (customers). If your primary interest is in the orders, a right join ensures you get every order, even if some of those orders are linked to customers not present in your customers table.
Write the R code for both scenarios.

q7a <- full_join(customers, orders, by = "customer_id")

q7b <- right_join(customers, orders, by = "customer_id")

Display the result

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

head(q7b)

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

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.

joined_data_for_challenge <- full_join(customers, orders, by = "customer_id") 

head(joined_data_for_challenge)

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

challenge <- joined_data_for_challenge %>%
  group_by(customer_id) %>%  
  summarize(
    name = first(name),  
    city = first(city), 
    total_orders = sum(!is.na(order_id), na.rm = TRUE),  
    total_amount_spent = sum(amount, na.rm = TRUE),  
    .groups = 'drop'  
  )

head(challenge)

## # A tibble: 6 × 5
##   customer_id name    city        total_orders total_amount_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
## 6           6 <NA>    <NA>                   1                600

Assignment 4

Liz Miller

2024-09-27

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

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

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

4. Full Join (3 points) Perform a full join between customers and orders.

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

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

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

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.