# Tidy Data, Reshaping and Joins — R Programming

Source: https://www.geekswithgeeks.com/en/r-programming/t-tidyr

> Reshape data with tidyr and combine tables with dplyr joins.

## Getting data into the right shape

**Tidy data** has a simple structure: **each variable is a column, each observation is a row, and each value is a cell**. Data often arrives untidy, such as a spreadsheet with one column per month. **tidyr** reshapes it: **`pivot_longer()`** turns many columns into key-value rows (months become a `month` column and a `sales` column), and **`pivot_wider()`** does the reverse, often for presentation. **`separate_wider_delim()`** splits one column into several, `unite()` combines them, and `fill()`, `drop_na()` and `replace_na()` handle missing values. To combine tables, use dplyr **joins**: **`left_join()`** keeps all rows of the left table and adds matching columns from the right (the most common), `inner_join()` keeps only matches, `full_join()` keeps everything, `anti_join()` finds rows with **no** match (great for data-quality checks) and `semi_join()` filters to rows with a match. Specify keys explicitly with `by = join_by(customer_id)`, and watch for **duplicate keys**, which multiply rows; recent dplyr versions warn about unexpected many-to-many matches.

## Pivoting and joining

Wide monthly sales become tidy rows, then customer details are joined in.

```r
library(tidyr)
library(dplyr)

wide <- tibble::tibble(
  city = c("Pune", "Delhi"),
  Jan = c(10, 12), Feb = c(8, 15), Mar = c(9, 11)
)

long <- wide |>
  pivot_longer(cols = Jan:Mar, names_to = "month", values_to = "units")
# one row per city and month (columns city, month, units): six rows in total

long |> pivot_wider(names_from = city, values_from = units)   # back to a wide table

orders <- tibble::tibble(order_id = c("o1", "o2", "o3"), customer_id = c(1, 2, 9), total = c(1200, 300, 800))
customers <- tibble::tibble(customer_id = c(1, 2, 3), name = c("Asha", "Ravi", "Meera"))

orders |> left_join(customers, by = join_by(customer_id))      # o3 gets name = NA
orders |> anti_join(customers, by = join_by(customer_id))      # orders with unknown customers: o3
```

## Count rows before and after joins

If a join unexpectedly increases the number of rows, the key is duplicated in one table. Check with `count(table, key) |> filter(n > 1)` before joining.

**Quiz:** Which join returns rows from the left table that have no match in the right table?

- [x] anti_join
- [ ] left_join
- [ ] inner_join
- [ ] full_join

*Answer:* anti_join. anti_join is ideal for finding unmatched records during data checks.