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 <- customers %>%
inner_join(orders, by = "customer_id")
#How many rows are in the result?
Q1_rows <- nrow(Q1)
print(paste("Number of rows in the result:", Q1_rows))## [1] "Number of rows in the result: 4"#Why are some customers or orders not included in the result?
#In an inner join, both datasets must have matching values in the key column for rows to appear in the final result. If there's no match in either dataset, that row is omitted.
#Display the result
print(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 300Q2 <- customers %>%
left_join(orders, by = "customer_id")
#How many rows are in the result?
Q2_rows <- nrow(Q2)
print(paste("Number of rows in the result:", Q2_rows))## [1] "Number of rows in the result: 6"#Explain why this number differs from the inner join result.
#Left join preserves all rows from the left table, which is why it has more rows than the inner join, where only rows with matching customer ids are retained.
#Display the result
print(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> NAQ3 <- customers %>%
right_join(orders, by = "customer_id")
#How many rows are in the result?
Q3_rows <- nrow(Q3)
print(paste("Number of rows in the result:", Q3_rows))## [1] "Number of rows in the result: 6"#Which customer_ids in the result have NULL for customer name and city? Explain why.
#Customer_id 6 and 7 are not found in the customers dataset, so the name and city columns for these customer_ids will be NULL.
#Display the result
print(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 150Q4 <- customers %>%
full_join(orders, by = "customer_id")
#How many rows are in the result?
Q4_rows <- nrow(Q4)
print(paste("Number of rows in the result:", Q4_rows))## [1] "Number of rows in the result: 8"#Identify any rows where there’s information from only one table. Explain these results.
#Rows where customer_id is present in customers but not in orders will have NA for order information.
#Rows where customer_id is present in orders but not in customers will have NA for name and city.
#Display the result
print(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 150Q5 <- customers %>%
semi_join(orders, by = "customer_id")
#How many rows are in the result?
Q5_rows <- nrow(Q5)
print(paste("Number of rows in the result:", Q5_rows))## [1] "Number of rows in the result: 3"#How does this result differ from the inner join result?
# In a semi join, only the rows from the left table (customers) are returned, and no columns from the right table (orders) are included. It shows the customers who have orders, but does not include order details.
#Display the result
print(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 ChicagoQ6 <- customers %>%
anti_join(orders, by = "customer_id")
#Which customers are in the result?
# David and Eve.
#Explain what this result tells you about these customers.
# Customers in the result have not placed any orders.
#Display the result
print(Q6)## # A tibble: 2 × 3
## customer_id name city
## <dbl> <chr> <chr>
## 1 4 David Houston
## 2 5 Eve Phoenix#Left join because it keeps all rows from the customers table, regardless of status on the orders table.
all_customers <- customers %>%
left_join(orders, by = "customer_id")
print(all_customers)## # 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#Which join would you use to find only the customers who have placed orders? Why?
#Inner join because it only displays rows that have a match in both tables, meaning it will only include customers who were also included in the orders table.
customers_with_orders <- customers %>%
inner_join(orders, by = "customer_id")
print(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 300customer_orders <- customers %>%
left_join(orders, by = "customer_id")
summary_result <- customer_orders %>%
group_by(name, city) %>%
summarize(
total_orders = n_distinct(order_id),
total_amount_spent = sum(amount, na.rm = TRUE)
)## `summarise()` has grouped output by 'name'. You can override using the
## `.groups` argument.print(summary_result)## # A tibble: 5 × 4
## # Groups: name [5]
## name city total_orders total_amount_spent
## <chr> <chr> <int> <dbl>
## 1 Alice New York 1 1200
## 2 Bob Los Angeles 2 2300
## 3 Charlie Chicago 1 300
## 4 David Houston 1 0
## 5 Eve Phoenix 1 0