Justin Kaplan

Assignment 4

Load the needed packages

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

library(readr)

Create the data-sets

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

Tasks

1. Inner Join (3 points) Perform an inner join between the customers and orders datasets. How many rows are in the result? Why are some customers or orders not included in the result? Display the result

inner_join(customers, orders)

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

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

1B. The inner join resulted in 4 rows. Some customers are not included because they didn’t make orders and some of the orders aren’t there because the customer id’s did not match with the left table.

2. Left Join (3 points) Perform a left join with customers as the left table and orders as the right table. How many rows are in the result? Explain why this number differs from the inner join result. Display the result

left_join(customers, orders)

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

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

2B. There are 6 rows in the results of the left join. This differs from the inner join because it includes the customers who have no orders whereas the inner join only includes customers with matching order data.

3. Right Join (3 points) Perform a right join with customers as the left table and orders as the right table. How many rows are in the result? Which customer_ids in the result have NULL for customer name and city? Explain why. Display the result

right_join(customers, orders)

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

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

3B.There are 6 rows in the right join results. The customer_ids 6 and 7 have NA written in the name and city column because there was no customer data to match their orders.

4. Full Join (3 points) Perform a full join between customers and orders. How many rows are in the result? Identify any rows where there’s information from only one table. Explain these results. Display the result

full_join(customers, orders)

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

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

4B. There are eight rows in the results of the full join. Rows 7 and 8 only have customer data and rows 5 and six only have product data.

5. Semi Join (3 points) Perform a semi join with customers as the left table and orders as the right table. How many rows are in the result? How does this result differ from the inner join result? Display the result

semi_join(customers, orders)

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

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

5B.There are 3 rows in the semi join. The results vary from the inner join because the inner join because the semi join shows rows from the customer data where there is also a match for order data.

6. Anti Join (3 points) Perform an anti join with customers as the left table and orders as the right table. Which customers are in the result? Explain what this result tells you about these customers. Display the result

anti_join(customers, orders)

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

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

6B. The only two customers in the results are David and Eve. This is because neither one of them had any data on the orders table. This data tells us which customers didn’t place any orders

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? Which join would you use to find only the customers who have placed orders? Why? Write the R code for both scenarios. Display the result

To create a join that showed all of the data I would use a full join. This would show all of the data including customers who both have and haven’t ordered, and orders that either did or didn’t have matches with customers. To create a join to only show customers that have placed orders we would use an inner join. This is because it will only return rows with full matches which will exclude customers with no orders.

Code:

full_join(customers, orders)

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

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

inner_join(customers, orders)

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

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

final <- full_join(customers, orders)

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

combined_df <- final %>%
  group_by(customer_id, city, name) %>%
  summarise(total_amount = sum(amount))

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

last <- combined_df %>%
  mutate(Amount_of_orders = frequency(name))
print(last)

## # A tibble: 7 × 5
## # Groups:   customer_id, city [7]
##   customer_id city        name    total_amount Amount_of_orders
##         <dbl> <chr>       <chr>          <dbl>            <dbl>
## 1           1 New York    Alice           1200                1
## 2           2 Los Angeles Bob             2300                1
## 3           3 Chicago     Charlie          300                1
## 4           4 Houston     David             NA                1
## 5           5 Phoenix     Eve               NA                1
## 6           6 <NA>        <NA>             600                1
## 7           7 <NA>        <NA>             150                1