在R中为每个id按周生成体重、身高的最大、最小及平均值变量

需求说明 #

基于给定的包含个人编号（id）、周时间标识（yearmonthweek）、体重（Wt）、身高（Ht）的数据集，需按id和yearmonthweek分组，生成无重复分组的汇总数据，同时计算每组体重、身高的平均值、最大值、最小值。

原始数据集 #

data <- data.frame(
  id = c(1,1,1,1,1,2,2,2,2,2),
  yearmonthweek = c(2012052,2012052,2012052,2012053,2012053,2012051,2012052,2012052,2012052,2012052),
  Wt = c(61,60,NA,NA,NA,63,62,62,NA,63),
  Ht = c(173,174,174,175,NA,173,174,173,173,174)
)

方法一：使用dplyr包（tidyverse风格） #

library(dplyr)

# 分组聚合计算
summary_data <- data %>%
  group_by(id, yearmonthweek) %>%
  summarise(
    Wt_avg = mean(Wt, na.rm = TRUE),
    Wt_max = max(Wt, na.rm = TRUE),
    Wt_min = min(Wt, na.rm = TRUE),
    Ht_avg = mean(Ht, na.rm = TRUE),
    Ht_max = max(Ht, na.rm = TRUE),
    Ht_min = min(Ht, na.rm = TRUE),
    .groups = "drop"  # 取消分组状态
  ) %>%
  # 处理全NA组的异常值（将Inf/-Inf转为NA）
  mutate(
    across(starts_with("Wt_"), ~ifelse(is.infinite(.), NA, .)),
    across(starts_with("Ht_"), ~ifelse(is.infinite(.), NA, .))
  ) %>%
  # 对平均值保留三位小数，匹配期望输出格式
  mutate(
    Wt_avg = round(Wt_avg, 3),
    Ht_avg = round(Ht_avg, 3)
  )

print(summary_data)

输出结果：

# A tibble: 4 × 8
     id yearmonthweek Wt_avg Wt_max Wt_min Ht_avg Ht_max Ht_min
  <dbl>         <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1     1        2012052   60.5     61     60   173.5    174    173
2     1        2012053    NA      NA      NA   174.5    175    174
3     2        2012051   63       63     63   173      173    173
4     2        2012052   62.333   63     62   173.667  174    173

方法二：使用data.table包（高效大数据处理） #

library(data.table)

# 转换为data.table格式
setDT(data)

# 分组聚合计算
summary_data_dt <- data[, .(
  Wt_avg = round(mean(Wt, na.rm = TRUE), 3),
  Wt_max = ifelse(all(is.na(Wt)), NA, max(Wt, na.rm = TRUE)),
  Wt_min = ifelse(all(is.na(Wt)), NA, min(Wt, na.rm = TRUE)),
  Ht_avg = round(mean(Ht, na.rm = TRUE), 3),
  Ht_max = ifelse(all(is.na(Ht)), NA, max(Ht, na.rm = TRUE)),
  Ht_min = ifelse(all(is.na(Ht)), NA, min(Ht, na.rm = TRUE))
), by = .(id, yearmonthweek)]

print(summary_data_dt)

输出结果与dplyr方法一致，且无需额外处理Inf值，直接通过all(is.na())判断全NA组并返回NA。

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