q1<- inner_join(customers,orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
There are 4 rows
b. Why are some customers or orders not included in the
result?
The customer date has only 3 customers in the order table where one
customer has 2 orders
c. 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 300 q2<-left_join(customers,orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
There are 6 rows
b. 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 even even those without information for the right
table
c. 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> NA q3<-right_join(customers,orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
There are 6rows
b. Which customer_ids in the result have NULL for customer name
and city? Explain why.
Customer_ids 6 and 7 have no results for customer name and city because
they have no data in customers table
c. 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<-full_join(customers,orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
There are 8 rows
b. Identify any rows where there’s information from only one
table. Explain these results.
Rows 5, 6, 7, and 8 all have data from one table.Full_Join includes all
rows from both tables regardless of missing data
c. 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<-semi_join(customers,orders)## Joining with `by = join_by(customer_id)`a. How many rows are in the result?
There are 3 rows
b. How does this result differ from the inner join
result?
It differs from the Inner_Join result as there are no repeats in orders
as orders are not included in the table but are used to match with the
customer table
c. 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<-anti_join(customers,orders)## Joining with `by = join_by(customer_id)`a. Which customers are in the result?
David and Eve
b. Explain what this result tells you about these
customers.
Both David and Eve have customer IDs but no Orders to match
c. Display the result
print(q6)## # A tibble: 2 × 3
## customer_id name city
## <dbl> <chr> <chr>
## 1 4 David Houston
## 2 5 Eve Phoenixa. Which join would you use to find all customers, including
those who haven’t placed any orders? Why?
I would use Left_join returns all records from the left table, and the
matching records from the right table. Even if there is no match, it
will still include the customer, but with the value “NA”
b. Which join would you use to find only the customers who have
placed orders? Why?
I would use Inner_Join as it returns only the records where there is a
match between the two tables
c. Write the R code for both scenarios.
q7.1 <- left_join(customers, orders)## Joining with `by = join_by(customer_id)` q7.2 <- inner_join(customers, orders)## Joining with `by = join_by(customer_id)`d. Display the result
print(q7.1)## # 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 print(q7.2)## # 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 300Create 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.
Challenge_Question <- customers %>%
full_join(orders, by = "customer_id") %>%
group_by(customer_id, name, city) %>%
summarize(
total_orders = sum(!is.na(order_id)),
total_amount_spent = sum(amount, na.rm = TRUE)) %>%
ungroup() ## `summarise()` has grouped output by 'customer_id', 'name'. You can override
## using the `.groups` argument.print(Challenge_Question)## # A tibble: 7 × 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
## 7 7 <NA> <NA> 1 150