按列字符串前缀分组实现Pivot Longer的通用tidyverse方案

Hey, here's a clean, flexible way to handle this that works for any number of prefix groups (not just A and B) using tidyr::pivot_longer's built-in parameters—no need to manually bind rows for each group!

Step-by-Step Solution #

The key is using names_sep to split your column names at the dot, and names_to to map the split parts to a group column (k) while keeping the suffix as value columns:

library(tidyverse)

# Your original data setup (included for context)
set.seed(1)
df <- data.table(
  date = rep(seq(as.Date("2020-01-01"),as.Date("2020-01-05"),by="day"),each=6),
  k = rep(c("A.mean","A.median","A.min","B.mean","B.median","B.min"),5),
  v = runif(30,0,50)
) %>% pivot_wider(names_from = k, values_from = v)

# Universal pivot longer code
df_long <- df %>%
  pivot_longer(
    cols = -date,  # Keep date as a fixed column, pivot all others
    names_sep = "\\.",  # Split column names at the dot character
    names_to = c("k", ".value")  # First split part = group column "k"; second = value column names
  )

# View the full result
df_long

Expected Output #

This produces exactly the structure you want, and it scales automatically if you add more prefix groups (like C.mean, C.median, etc.):

date       k     mean median   min
<date>     <chr> <dbl>  <dbl> <dbl>
1 2020-01-01 A      13.3   18.6  28.6
2 2020-01-01 B      45.4   10.1  44.9
3 2020-01-02 A      47.2   33.0  31.5
4 2020-01-02 B       3.09  10.3   8.83
5 2020-01-03 A      34.4   19.2  38.5
6 2020-01-03 B      24.9   35.9  49.6
7 2020-01-04 A      19.0   38.9  46.7
8 2020-01-04 B      10.6   32.6   6.28
9 2020-01-05 A      13.4   19.3   0.670
10 2020-01-05 B      19.1   43.5  17.0

Why This Works #

• names_sep = "\\.": Tells pivot_longer to split each column name into two segments at the dot (e.g., A.mean becomes c("A", "mean")).
• names_to = c("k", ".value"): The .value keyword is a special tidyr syntax—it uses the second split segment as the column name for the numeric values, while the first segment populates the k group column.
• Full scalability: Add as many prefix groups as you need (C, D, etc.) and the code will handle them without any modifications.

Advantage Over Your Non-Universal Code #

This replaces the repetitive select/rename_with/bind_rows workflow with a single, concise pivot_longer call—way easier to maintain and adapt to new groups!

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