R: R Data Reshaping

Q: How do you write pivot_longer for cols ?

There are 4 ways: c(Q1, Q2, Q3, Q4) explicit list / starts_with("Q") prefix match / 2:5 numeric range / matches("^Q\\d+$") regular expression.

Q: What is the difference between pivot_longer and gather ?

gather is the old tidyr function (deprecated), while pivot_longer is the new version. It is more powerful and consistent; the new code uses pivot_longer .

Q: Which of the 4 join types should I use?

The most commonly used is left_join (retains all data from the primary table). inner_join performs an exact match, full_join retains all data, and right_join should be used sparingly (simply swap the left and right tables).

Q: How do I split column names after converting a wide table to a long table?

Use names_sep = "_" to split (fixed delimiter) or names_pattern = "(\\w+)_(\\d+)" to split (regular expression). Declare the resulting columns using names_to = c("a", "b") .

Last updated: 2026-08-26

In real-world projects, 80% of the data consists of "wide tables" (one entity per row, with fields representing attributes), but analysis often requires "long tables" (one observation per row). In this lesson, we’ll learn how to "transform" R data—using tidyr reshaping + dplyr joining—to convert data from any format into the format needed for analysis.

After completing this lesson, you will be able to: convert between wide and long tables, split and merge columns, merge multiple tables using joins, and create any type of report.

1. What You'll Learn

The Difference Between Wide Tables and Long Tables
pivot_longer width → length
pivot_wider Length → Width
separate / unite: Column splitting and merging
dplyr 4 types of join (left/right/inner/full)
Hands-On: Converting Sales Data from Length to Width
Report Generation

2. A Pain Point in Data Transformation

(1) Pain Point: Wide Tables Are Difficult to Analyze

Xiao Zhao received a quarterly sales report (wide table):

TEXT 📖 Display only

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

The manager asked for a "Quarterly Sales Trend Chart"—ggplot2 requires a long table:

TEXT 📖 Display only

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

If it takes 5 minutes to copy and paste in Excel; Python pd.melt—one line; R tidyr—

(2) Solution using R

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"
  )

Convert width to height with just 1 line of code.

3. Wide Tables vs. Long Tables

(1) Comparison of the Two Forms

graph LR
    A[Wide Table Wide] -->|pivot_longer| B[Long Table Long]
    B -->|pivot_wider| A
    
    subgraph Width Examples
        C[Region Q1 Q2 Q3 Q4]
    end
    subgraph Long Example
        D[Region Quarter Sales]
    end

(2) When to use which one?

Scenario	Recommendation
Storing Data	Wide Table (Saves on the Number of Rows)
View Reports	Wide Table (Excel-friendly)
ggplot2 Plotting	Long Table (Required)
dplyr Analysis	Long Table (group_by-friendly)
Machine Learning	Wide table (1 sample per row)

4. pivot_longer(): Wide → Long

(1) Basic Syntax

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
)

(2) Basic Conversions

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
# ...

(3) 4 Ways to Select Columns

# 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

(4) Hands-On: Splitting Complex Column Names

# 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
#  ...

(5) Hands-On: Extracting Data with Regular Expressions

# 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)

5. pivot_wider(): Length → Width

(1) Basic Syntax

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
)

(2) Basic Conversions

# 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
# ...

(3) Practical Application: Handling Missing Values

# 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

6. separate() / unite(): Split and Merge Columns

(1) separate() Split

# 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 = "(-)")

(2) unite() Merge

# 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

7. dplyr: 4 Types of Joins

(1) Quick Reference Table for Join Types

graph TB
    A[dplyr 4 types of join] --> B[left_join<br/>Keep the entire table]
    A --> C[right_join<br/>Keep the entire right-hand table]
    A --> D[inner_join<br/>Find the Intersection]
    A --> E[full_join<br/>Union-Disjoint Set]
    
    style A fill:#fff3cd
    style B fill:#cce5ff
    style C fill:#d4edda
    style D fill:#f8d7da
    style E fill:#e1d4ff

(2) SQL Analogy

dplyr	SQL	Meaning
`left_join(a, b, by)`	`LEFT JOIN`	Keep all a
`right_join(a, b, by)`	`RIGHT JOIN`	Keep all b
`inner_join(a, b, by)`	`INNER JOIN`	Reserve a∩b
`full_join(a, b, by)`	`FULL OUTER JOIN`	Reserve a∪b

(3) Hands-On: Joining Two Tables

# 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

(4) Multi-key join

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

8. Hands-On: Analyzing Sales Data Across Multiple Tables

Below is an example of a complete workflow that ties together all the refactoring concepts covered in this lesson.

▶ Example: In-Depth Analysis of 4th-Quarter Sales Data

R 📖 Display only

# ============================================
# 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)

90 logic lines (exceeds 40-line limit, display only)

Expected Output (Excerpt):

TEXT 📖 Display only

=== 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

❓ FAQ

Q When should I use wide tables vs. long tables?

A Use wide tables for storage and display (more user-friendly), and long tables for analysis and plotting (required by ggplot2). All tidyverse functions assume long tables.

Q How do you write pivot_longer for cols?

A There are 4 ways: c(Q1, Q2, Q3, Q4) explicit list / starts_with("Q") prefix match / 2:5 numeric range / matches("^Q\\d+$") regular expression.

Q What is the difference between pivot_longer and gather?

A gather is the old tidyr function (deprecated), while pivot_longer is the new version. It is more powerful and consistent; the new code uses pivot_longer.

Q Which of the 4 join types should I use?

A The most commonly used is left_join (retains all data from the primary table). inner_join performs an exact match, full_join retains all data, and right_join should be used sparingly (simply swap the left and right tables).

Q What should I do if the number of rows increases after a join?

A Check whether the join key is unique. A one-to-many join is normal, but a many-to-many join will result in a Cartesian product. Use relationship = "many-to-many" to explicitly specify it.

Q How do I split column names after converting a wide table to a long table?

A Use names_sep = "_" to split (fixed delimiter) or names_pattern = "(\\w+)_(\\d+)" to split (regular expression). Declare the resulting columns using names_to = c("a", "b").

📖 Summary

Wide table: One entity per row, with fields representing attributes (user-friendly)
Long table: One observation per row (analysis-friendly)
pivot_longer() Width → Length, pivot_wider() Length → Width
pivot_longer Key parameters: cols / names_to / values_to / names_sep / names_pattern
pivot_wider Key parameters: id_cols / names_from / values_from / values_fill
separate() Split columns (by delimiter or position), unite() Merge columns
dplyr 4 types of joins: left_join (most common) / right_join / inner_join / full_join
tidyverse data flow: Read wide table → pivot_longer → dplyr analysis → ggplot2 visualization → pivot_wider report

📝 Exercises

Basic Problem: Construct a wide table (3 rows × 4 columns: Region/Q1/Q2/Q3/Q4), convert it to a long table using pivot_longer, then convert it back to a wide table using pivot_wider, and verify that the data remains complete and intact.
Basic Problem: Create a tibble containing a column named year_month ("2024-01"), use separate() to split it into two columns, year and month, and then use unite() to merge them back together.
Basic Exercise: Simulate two tables (Customer Table + Order Table) and combine them using four different types of joins. Record the difference in the number of rows returned for each type of join.
Advanced Exercise: Create a complex wide table (column name format: variable_year_quarter), use pivot_longer + names_sep to convert it to a long table in one step, and then use pivot_wider + names_glue to convert it back to a wide table with a prefix added.
Challenge: Simulate a complete scenario: ① Import a wide table containing sales data for 4 branch offices across 4 quarters ② Import a table containing basic branch office information (region/manager/department) ③ Merge using a LEFT JOIN ④ Convert to a long table using pivot_longer ⑤ Group and summarize by manager ⑥ Generate a summary report (wide table) ⑦ Use ggplot2 to plot a "Quarterly Sales Trend Chart" (long table). Save the code for the entire process and screenshots of the results.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

R: R Data Reshaping

1. What You'll Learn

2. A Pain Point in Data Transformation

(1) Pain Point: Wide Tables Are Difficult to Analyze

(2) Solution using R

3. Wide Tables vs. Long Tables

(1) Comparison of the Two Forms

(2) When to use which one?

4. pivot_longer(): Wide → Long

(1) Basic Syntax

(2) Basic Conversions

(3) 4 Ways to Select Columns

(4) Hands-On: Splitting Complex Column Names

(5) Hands-On: Extracting Data with Regular Expressions

5. pivot_wider(): Length → Width

(1) Basic Syntax

(2) Basic Conversions

(3) Practical Application: Handling Missing Values

6. separate() / unite(): Split and Merge Columns

(1) separate() Split

(2) unite() Merge

7. dplyr: 4 Types of Joins

(1) Quick Reference Table for Join Types

(2) SQL Analogy

(3) Hands-On: Joining Two Tables

(4) Multi-key join

8. Hands-On: Analyzing Sales Data Across Multiple Tables

▶ Example: In-Depth Analysis of 4th-Quarter Sales Data

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好