knitr::opts_chunk$set(echo = TRUE)#How many rows are in the result? 4 rows
#Why are some customers or orders not included in the result? Because they do not have an order that matches their customer_id
#Display the result
inner_join(customers, orders, 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####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? 6 rows ####Explain why this number differs from the inner join result. This is different than the inner join, because it includes all of the Customers from the customers table, even if they do not have any orders. ####Display the result
left_join(customers,orders, 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#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? 6 rows #Which customer_ids in the result have NULL for customer name and city? Explain why. Customer 6 and 7, this is because there is no customer name for those cuustmer_id in the customers table. #Display the result
right_join(customers, orders, 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#Full Join (3 points) Perform a full join between customers and orders. #How many rows are in the result? 8 rows #Identify any rows where there’s information from only one table. Explain these results. Row 5 and 6 are only from the customers table, while Rows 7 and 8 are only in the orders table. The reason for this is because there is no match with the customer_id and the orders #Display the result
full_join(customers, orders, 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#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? 3 rows #How does this result differ from the inner join result? It has one less row, as it does not repeat Bob twice. It also has fewer columns #Display the result```
semi_join(customers, orders, 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#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? David Hudson and Eve Phoenix #Explain what this result tells you about these customers. That they did not place any orders #Display the result
anti_join(customers,orders, by = "customer_id")## # 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? You would use the left join, as it brings all the customers on the table, even the ones that did not place an order. It happens becasue the customers table is the left table. #Which join would you use to find only the customers who have placed orders? Why? You would use an inner join, because it returns only the rows where is a matching customer_id in both tables. #Write the R code for both scenarios. #Display the result
#Scenario 1
left_join(customers,orders, 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#Scenario 2
inner_join(customers, orders, 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.
customer_summary <- customers %>%
left_join(orders, by = "customer_id") %>%
group_by(customer_id,name, city) %>%
summarize(total_orders = n(), total_amount = sum(amount, na.rm = TRUE)) %>%
ungroup()## `summarise()` has grouped output by 'customer_id', 'name'. You can override
## using the `.groups` argument.head(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 1 0
## 5 5 Eve Phoenix 1 0