借鉴Hadley方法用tidyr结合正则多列聚合遇阻求助

Hey there! Let's work through this regex + tidyr issue together—sounds like you're building a smart workflow inspired by Hadley's approach, so let's get that pattern matching sorted out.

First, let's break down the common pitfalls that might be tripping you up, then walk through concrete solutions using gather()/spread() (and the modern pivot_longer()/pivot_wider() since they're more flexible for regex work).

Common Regex Missteps with Tidyr #

Chances are your regex issues stem from one of these:

• Missing anchor points: Forgetting ^ (start of string) or $ (end of string) can lead to matching unintended parts of column names.
• Incorrect capture groups: When splitting column names into multiple variables, your regex needs to explicitly define which parts map to which new columns.
• Greedy matching: Using .* without bounds can gobble up more characters than you intend, breaking your splits.
• Overcomplicating with matches(): Sometimes combining starts_with()/ends_with() with boolean logic works better than a single regex, but you need to structure it correctly in dplyr/tidyr.

Solution 1: gather() + separate() with Regex #

Let's use a realistic example to demonstrate. Suppose your wide data looks like this:

library(tidyr)
library(dplyr)

# Sample wide data
df_wide <- tibble(
  customer_id = 1:3,
  sales_2023_q1 = c(1200, 950, 1500),
  sales_2023_q2 = c(1300, 1000, 1600),
  expenses_2023_q1 = c(400, 300, 500),
  expenses_2023_q2 = c(450, 320, 550)
)

You want to aggregate by metric (sales/expenses) and quarter. Here's how to do it:

1. Gather the wide columns into a long format:

   df_long <- df_wide %>%
     gather(key = "metric_quarter", value = "amount", -customer_id)
   

2. Split the metric_quarter column using regex capture groups:
   The column names follow [metric]_[year]_[quarter], so we can split on underscores, or use a regex to explicitly capture each part:

   df_split <- df_long %>%
     separate(metric_quarter, into = c("metric", "year", "quarter"), sep = "_")
   

3. Aggregate as needed:

   df_aggregated <- df_split %>%
     group_by(metric, year, quarter) %>%
     summarise(total_amount = sum(amount), .groups = "drop")
   

Solution 2: Modern Approach with pivot_longer() (Easier Regex Handling) #

Tidyr's pivot_longer() lets you use regex directly in the names_pattern argument to split column names during the reshape—no need for a separate separate() step:

df_long <- df_wide %>%
  pivot_longer(
    cols = -customer_id,
    names_to = c("metric", "year", "quarter"),
    names_pattern = "(.*)_(\\d{4})_(q\\d)"  # Capture groups match each part of the column name
  )

This regex breaks down to:

• (.*): Capture the first part (metric name, e.g., sales)
• _(\\d{4})_: Capture the 4-digit year, surrounded by underscores
• (q\\d): Capture the quarter (e.g., q1)

Fixing matches()/starts_with() Issues #

If you were trying to select specific columns with these helpers and it wasn't working, double-check your syntax. For example, to select all columns starting with sales or expenses:

# Correct boolean syntax for column selection
df_wide %>%
  gather(key = "metric_quarter", value = "amount", starts_with("sales") | starts_with("expenses"))

Or with matches():

df_wide %>%
  gather(key = "metric_quarter", value = "amount", matches("^(sales|expenses)_"))

The ^ ensures we only match columns that start with sales_ or expenses_, avoiding accidental matches.

Final Tips #

If your column names have a more complex pattern (no underscores, mixed letters/numbers), use lookaround assertions to split without consuming characters. For example, if columns are sales2023q1 instead of sales_2023_q1:

df_split <- df_long %>%
  separate(metric_quarter, into = c("metric", "year", "quarter"), 
           sep = "(?<=sales|expenses)(?=\\d)|(?<=\\d)(?=q)")

This uses positive lookarounds to split between the metric name and year, and between year and quarter.

内容的提问来源于stack exchange，提问作者Eric Fail