如何按用户分组将大型DataFrame拆分为小型DataFrame列表？

原始DataFrame #

df <- data.frame( Time = c('2023-07-26 14:30:01', '2023-07-26 15:00:00','2023-07-26 15:30:00', '2023-07-26 16:00:00','2023-07-26 16:30:00'),
                
                 `[user1] zuzycie` = rnorm(5, 1, 1),
                 `[user1] temp` = rnorm(5, 10, 1),
                 `[user1] level` = rnorm(5, 20, 10),
                 
                 `[user2] zuzycie` = rnorm(5, 1, 1),
                 `[user2] temp` = rnorm(5, 10, 1),
                 `[user2] level` = rnorm(5, 20, 10),
                 
                 `[user3] zuzycie` = rnorm(5, 1, 1),
                 `[user3] temp` = rnorm(5, 10, 1),
                 `[user3] level` = rnorm(5, 20, 10)
                 )

需求说明 #

需要将上述DataFrame拆分为一个DataFrame列表，每个子DataFrame包含Time列和对应单个用户的所有指标列，预期输出示例如下：

> df_list [[1]]
                 Time [user1] zuzycie [user1] temp [user1] level 
1 2023-07-26 14:30:01        1.5365839      9.836822       24.35510
2 2023-07-26 15:00:00        2.6389661      8.206188       30.14941
3 2023-07-26 15:30:00        0.1443759     11.506217       10.23074
4 2023-07-26 16:00:00        1.3582380     11.510744       24.57023
5 2023-07-26 16:30:00        0.5152619     10.362905        9.92493


> df_list [[2]]
                 Time [user2] zuzycie [user2] temp [user2] level 
1 2023-07-26 14:30:01        0.9914846      9.836822       24.35510
2 2023-07-26 15:00:00        0.8715091      8.206188       30.14941
3 2023-07-26 15:30:00        0.3119822     11.506217       10.23074
4 2023-07-26 16:00:00        2.4220240     11.510744       24.57023
5 2023-07-26 16:30:00        1.5912865     10.362905        9.92493

现有问题 #

当前使用的代码是按指标而非用户拆分，无法满足需求：

df_lst <- split.default(df, sub('^(X.user)([1-3])(..)*', '', names(df)))

解决方案 #

方法一：基础R实现

通过正则提取用户标识，遍历每个用户筛选对应列：

# 提取列名中的用户标识，Time列单独标记
groups <- sub("^(\\[user\\d+\\]).*", "\\1", names(df))
groups[names(df) == "Time"] <- "Time"

# 生成按用户分组的DataFrame列表
df_list <- lapply(unique(groups[groups != "Time"]), function(user) {
  df[, c("Time", names(df)[groups == user])]
})

# 可选：给列表命名，方便索引
names(df_list) <- unique(groups[groups != "Time"])

方法二：split.default简化版

先拆分非Time列，再合并Time列到每个子DataFrame：

# 提取非Time列的用户分组
user_groups <- sub("^(\\[user\\d+\\]).*", "\\1", names(df)[-1])

# 拆分后合并Time列
df_list <- lapply(split.default(df[-1], user_groups), function(x) {
  cbind(df["Time"], x)
})

方法三：tidyverse实现

通过宽长表转换实现分组拆分：

library(tidyverse)

df_list <- df %>%
  # 宽表转长表，提取用户和指标
  pivot_longer(-Time, names_to = c("user", "metric"), names_pattern = "\\[(.*)\\] (.*)") %>%
  # 按用户分组拆分
  group_split(user, .keep = TRUE) %>%
  # 每个分组转回宽表
  map(~ pivot_wider(.x, names_from = c(user, metric), values_from = value, names_glue = "[{user}] {metric}"))

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