R: إعادة هيكلة البيانات في R: دليل شامل لـ tidyr

Region    Q1    Q2    Q3    Q4
Beijing  1000  1200  1100  1500
Shanghai  1500  1800  1700  2000
Guangzhou   800   900  1000  1200

Region   Quarter  Sales
Beijing   Q1    1000
Beijing   Q2    1200
...

library(tidyr)

# Wide Table → Long Table(One line)
sales_long <- sales_wide |>
  pivot_longer(
    cols = c(Q1, Q2, Q3, Q4),
    names_to = "Quarter",
    values_to = "Sales"
  )

pivot_longer(
  data,
  cols,                 # Columns to convert
  names_to = "name",    # Newly Listed(Originally listed)
  values_to = "value",  # Newly Listed(Original value)
  names_prefix = "",    # Listed Public Prefixes
  names_sep = NULL,     # Delimiter(Split Column Names)
  names_pattern = NULL, # Regular(Extract Column Names)
  values_drop_na = FALSE
)

library(tidyr)

# Wide Table
sales_wide <- tibble(
  region = c("Beijing", "Shanghai", "Guangzhou"),
  Q1 = c(1000, 1500, 800),
  Q2 = c(1200, 1800, 900),
  Q3 = c(1100, 1700, 1000),
  Q4 = c(1500, 2000, 1200)
)
print(sales_wide)
# # A tibble: 3 × 5
#   region    Q1    Q2    Q3    Q4
#   <chr>  <dbl> <dbl> <dbl> <dbl>
# 1 Beijing    1000  1200  1100  1500
# 2 Shanghai    1500  1800  1700  2000
# 3 Guangzhou     800   900  1000  1200

# Rotate the Long Table
sales_long <- sales_wide |>
  pivot_longer(
    cols = c(Q1, Q2, Q3, Q4),
    names_to = "quarter",
    values_to = "sales"
  )
print(sales_long)
# # A tibble: 12 × 3
#    region quarter sales
#    <chr>  <chr>   <dbl>
#  1 Beijing   Q1       1000
#  2 Beijing   Q2       1200
#  3 Beijing   Q3       1100
#  4 Beijing   Q4       1500
#  5 Shanghai   Q1       1500
#  6 Shanghai   Q2       1800
#  7 Shanghai   Q3       1700
#  8 Shanghai   Q4       2000
# ...

# 1. Explicitly listed vectors
cols = c(Q1, Q2, Q3, Q4)

# 2. starts_with()(Most Commonly Used)
cols = starts_with("Q")

# 3. Number Range
cols = 2:5  # Columns 2-5

# 4. Listed pattern(Regular)
cols = matches("^Q\\d+$")

# 5. Exclude a specific column
cols = -region

# Listing includes prefixes
df <- tibble(
  id = 1:3,
  sales_2022 = c(100, 200, 300),
  sales_2023 = c(150, 250, 350),
  profit_2022 = c(10, 20, 30),
  profit_2023 = c(15, 25, 35)
)

# Split sales_2022 → Type=Sales, Year=2022
df_long <- df |>
  pivot_longer(
    cols = -id,
    names_to = c("type", "year"),
    names_sep = "_",
    values_to = "value"
  )
print(df_long)
# # A tibble: 12 × 4
#       id type    year  value
#    <int> <chr>   <chr> <dbl>
#  1     1 sales   2022    100
#  2     1 sales   2023    150
#  3     1 profit  2022     10
#  4     1 profit  2023     15
#  ...

# Listing Format:Variable_Year_Quarter
df <- tibble(
  id = 1:2,
  sales_2024_Q1 = c(100, 200),
  sales_2024_Q2 = c(150, 250),
  cost_2024_Q1 = c(50, 80),
  cost_2024_Q2 = c(70, 100)
)

# Extract using regular expressions
df_long <- df |>
  pivot_longer(
    cols = -id,
    names_to = c("type", "year", "quarter"),
    names_pattern = "(\\w+)_(\\d+)_(\\w+)",
    values_to = "value"
  )
print(df_long)

pivot_wider(
  data,
  id_cols = NULL,        # Identifier Column(Keep as a line)
  names_from = name,     # Sources of New Listings
  values_from = value,   # Source of Newly Listed Values
  values_fill = NULL,    # Missing Value Imputation
  names_prefix = ""      # New Listing Prefix
)

# Converting a Long Table to a Wide Table
sales_wide_again <- sales_long |>
  pivot_wider(
    id_cols = region,
    names_from = quarter,
    values_from = sales
  )
print(sales_wide_again)
# # A tibble: 3 × 5
#   region    Q1    Q2    Q3    Q4
#   <chr>  <dbl> <dbl> <dbl> <dbl>
# 1 Beijing    1000  1200  1100  1500
# ...

# There are missing data points(Not all region All of them have everything quarter)
df <- tibble(
  region = c("A", "A", "B", "C", "C"),
  quarter = c("Q1", "Q2", "Q1", "Q2", "Q3"),
  sales = c(100, 200, 150, 250, 300)
)

# Width Conversion Table(Fill in the blank 0)
df_wide <- df |>
  pivot_wider(
    id_cols = region,
    names_from = quarter,
    values_from = sales,
    values_fill = 0
  )
print(df_wide)
# # A tibble: 3 × 4
#   region    Q1    Q2    Q3
#   <chr>  <dbl> <dbl> <dbl>
# 1 A        100   200     0
# 2 B        150     0     0
# 3 C          0   250   300

# Split the Date Column
df <- tibble(date = c("2024-01-15", "2024-02-20"))

# Split by delimiter
df |> separate(date, into = c("year", "month", "day"), sep = "-")
# # A tibble: 2 × 3
#   year  month day
#   <chr> <chr> <chr>
# 1 2024  01    15
# 2 2024  02    20

# Breakdown by Location
df |> separate(date, into = c("year", "rest"), sep = 4)
# year  rest
# "2024" "-01-15"

# Split using regular expressions
df |> separate(date, into = c("year", "month", "day"),
               sep = "(-)")

# Merge Year/Month/Day
df_split <- tibble(
  year = c("2024", "2024"),
  month = c("01", "02"),
  day = c("15", "20")
)

df_split |> unite("date", year, month, day, sep = "-")
# # A tibble: 2 × 1
#   date
#   <chr>
# 1 2024-01-15
# 2 2024-02-20

# Customer Table
customers <- tibble(
  id = 1:5,
  name = c("Alice", "Bob", "Charlie", "Diana", "Eve")
)

# Orders Table
orders <- tibble(
  customer_id = c(1, 1, 2, 4, 4, 6),
  amount = c(100, 200, 150, 300, 250, 500)
)

# 1. left_join:Retain all customers(Those with no orders are NA)
customers |> left_join(orders, by = c("id" = "customer_id"))
# # A tibble: 7 × 3  ← 5 Customer + 6 Order = 7 row(Charlie/Eve No orders)
#    id name    amount
# 1  1 Alice     100
# 2  1 Alice     200
# 3  2 Bob       150
# 4  3 Charlie    NA  ← No orders
# 5  4 Diana     300
# 6  4 Diana     250
# 7  5 Eve        NA  ← No orders

# 2. inner_join:Find the Intersection
customers |> inner_join(orders, by = c("id" = "customer_id"))
# # A tibble: 6 × 3  ← 4 A customer with an order

# 3. full_join:Union-Disjoint Set
customers |> full_join(orders, by = c("id" = "customer_id"))
# # A tibble: 7 × 3  ← Includes id=6 "No customer" Order

# 4. right_join:Keep all orders
customers |> right_join(orders, by = c("id" = "customer_id"))
# # A tibble: 6 × 3  ← 6 Orders

# Sort by multiple columns join
df1 |> left_join(df2, by = c("year", "month", "id"))

# ============================================
# 4 In-Depth Analysis of Quarterly Sales Data
# Features:Wide↔Long Conversion + Multiple Tables join + Reports
# ============================================

library(tidyr)
library(dplyr)

# 1. Prepare the raw data(Wide Table)
sales_wide <- tibble(
  region = c("Beijing", "Shanghai", "Guangzhou", "Shenzhen"),
  Q1 = c(1000, 1500, 800, 1200),
  Q2 = c(1200, 1800, 900, 1400),
  Q3 = c(1100, 1700, 1000, 1300),
  Q4 = c(1500, 2000, 1200, 1600)
)

# 2. Wide Table → Long Table
sales_long <- sales_wide |>
  pivot_longer(
    cols = c(Q1, Q2, Q3, Q4),
    names_to = "quarter",
    values_to = "sales"
  )

cat("=== Long Table(First 8 rows)===\n")
print(head(sales_long, 8))

# 3. Calculate the year-over-year growth
sales_growth <- sales_long |>
  group_by(region) |>
  arrange(quarter) |>
  mutate(
    prev_sales = lag(sales),
    growth = round((sales - prev_sales) / prev_sales * 100, 2)
  )

cat("\n=== Year-over-year increase ===\n")
print(sales_growth)

# 4. Long Table → Wide Table(Generate a Comparison Report)
report_wide <- sales_growth |>
  select(region, quarter, sales, growth) |>
  pivot_wider(
    id_cols = region,
    names_from = quarter,
    values_from = c(sales, growth),
    names_glue = "{quarter}_{.value}"
  )

cat("\n=== Wide-Format Reports ===\n")
print(report_wide)

# 5. Breakdown by Quarter → Quarter+Numbers
sales_long2 <- sales_long |>
  separate(quarter, into = c("q_prefix", "q_num"), sep = 1, remove = FALSE)

cat("\n=== Breakdown by Quarter ===\n")
print(sales_long2)

# 6. Multiple Tables join:Merge Customer Information
customers <- tibble(
  region = c("Beijing", "Shanghai", "Guangzhou", "Shenzhen", "Hangzhou"),
  manager = c("Alice", "Bob", "Charlie", "Diana", "Eve"),
  tier = c("A", "A", "B", "A", "C")
)

# left_join:Retain all sales regions
sales_with_manager <- sales_long |>
  left_join(customers, by = "region")

cat("\n=== Sales + Account Manager ===\n")
print(head(sales_with_manager, 8))

# 7. Grouped by Manager
manager_summary <- sales_with_manager |>
  group_by(manager, tier) |>
  summarise(
    Total Sales = sum(sales),
    Average Quarter = round(mean(sales), 2),
    Highest Quarterly = max(sales)
  ) |>
  arrange(desc(Total Sales))

cat("\n=== Manager Performance ===\n")
print(manager_summary)

# 8. Quarter-by-Quarter Comparison Report
quarter_comparison <- sales_long |>
  group_by(quarter) |>
  summarise(
    Total Sales = sum(sales),
    Average = round(mean(sales), 2),
    Maximum = max(sales),
    Minimum = min(sales)
  )

cat("\n=== Quarter-over-Quarter Comparison ===\n")
print(quarter_comparison)

# 9. Complex Wide Table(Multi-indicator)
complex_report <- sales_with_manager |>
  group_by(region) |>
  summarise(
    Total Sales = sum(sales),
    Average Quarter = round(mean(sales), 2)
  ) |>
  pivot_wider(
    names_from = region,
    values_from = c(Total Sales, Average Quarter)
  )

cat("\n=== Complex Wide Table ===\n")
print(complex_report)

# 10. Reverse Operation:Convert the width table above back to the length table(For drawing)
viz_data <- complex_report |>
  pivot_longer(
    cols = everything(),
    names_to = c("metric", "region"),
    names_sep = "_",
    values_to = "value"
  )

cat("\n=== Data Visualization ===\n")
print(viz_data)

=== Manager Performance ===
# A tibble: 4 × 5
  manager tier  Total Sales Average Quarter Highest Quarterly
  <chr>   <chr>  <int>    <dbl>    <int>
1 Bob    A       7000    1750       2000
2 Alice    A       4800    1200       1500
3 Diana    A       5500    1375       1600
4 Charlie    B       3900     975       1200

=== Quarter-over-Quarter Comparison ===
# A tibble: 4 × 5
  quarter Total Sales   Average  Maximum  Minimum
  <chr>    <int>  <dbl> <int> <int>
1 Q1        4500  1125    1500   800
2 Q2        5300  1325    1800   900
3 Q3        5100  1275    1700  1000
4 Q4        6300  1575    2000  1200