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

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?

nrow(q1)

## [1] 4

Why are some customers or orders not included in the result?
Inner join returns all rows from both tables where there is a match. the customer data only has 3 customers in the order table where one customer has two 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

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)

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

How many rows are in the result?

nrow(q2)

## [1] 6

Explain why this number differs from the inner join result.
The numbers vary between the results because the left join retains all customers regardless of their order status, while the inner join only includes those with orders.
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

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

## Joining with `by = join_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 right join retains all orders, including those made by customers not listed in the customers dataset, resulting in NA values for any customer-related fields.
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

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

q4 <- full_join(customers, orders)

## Joining with `by = join_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 with only customer information (IDs 4 and 5) indicate customers who have not placed any orders, while the rows with only order information (IDs 6 and 7) correspond to orders made by customers not listed in the customers dataset
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

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)

## Joining with `by = join_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 gives a distinct list of customers who ordered, while the inner join includes all orders and can produce duplicate customer entries.
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

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)

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

Which customers are in the result?
david and eve
Explain what this result tells you about these customers
The result indicates that these customers, David and Eve, have not placed any orders.
Display the result

head(q6)

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

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 a left join asit includes all customers from the customers dataset, along with any matching orders. This will allow you to see all customers, even those who have not placed any orders, filling in NA for order details where applicable.
Which join would you use to find only the customers who have placed orders? Why?
An inner join returns only the customers who have matching entries in the orders dataset. This means you’ll only get customers who have placed at least one order.
Write the R code for both scenarios.

customers_with_orders <- inner_join(customers, orders)

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

Display the result

head(customers_with_orders)

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

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. library(dplyr)

Perform a left join to include all customers and their orders

summary_data <- customers %>%
  left_join(orders, by = "customer_id") %>%
  group_by(customer_id, name, city) %>%
  summarize(
    total_orders = n(), # Count of orders (0 if no orders)
    total_amount_spent = sum(amount, na.rm = TRUE) # Total amount spent
  ) %>%
  ungroup()

## `summarise()` has grouped output by 'customer_id', 'name'. You can override
## using the `.groups` argument.

print(summary_data)

## # A tibble: 5 × 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                1                  0
## 5           5 Eve     Phoenix                1                  0

Assignment 4

Kaley Morgenstern

2024-09-27