如何按ID分组计算CD4值的所有两两组合差值并生成目标数据集？

原始数据集 #

id   time       cd4  sequence
1   -0.741958   548  1 
1   -0.246407   893  2
1    0.243669   657  3
        
2   -2.7296369  464  1
2   -2.2505131  845  2
2   -0.221766   752  3
2    0.221766   459  4
2    0.77481198 181  5
2    1.256673   434  6

需求说明 #

按ID分组，计算每个ID内所有CD4值的两两组合差值（包含自身与自身的组合，差值为0）：

• 差值计算逻辑：后序sequence的CD4值减去前序sequence的CD4值（如ID1中sequence1 vs sequence2：893 - 548）
• 每个ID的组合数为n*(n+1)/2（n为该ID的观测条数），比如ID2有6条数据，对应15种组合

期望输出 #

id  time        cd4 seq comb    cd4_lag
1   -0.74195    548 1   1,1     0
1   -0.24640    893 2   1,2     345
1   0.243669    657 3   1,3     109
1                       2,3    -236
2   -2.72963    464 1   1,1 
2   -2.25051    845 2   1,2     381
2   -0.22176    752 3   1,3     288
2   0.221766    459 4   1,4     -5
2   0.774811    181 5   1,5     -283
2   1.256673    434 6   1,6     -30
                        2,3     -93
                        2,4     -386
                        2,5     -664
                        2,6     -411
                        3,4     -293
                        3,5     -571
                        3,6     -318
                        4,5     -278
                        4,6     -25
                        5,6      253

实现方案 #

方案1：Python（Pandas） #

核心思路是对每个ID生成所有sequence的笛卡尔积（包含自身），筛选出seq2 >= seq1的组合，再计算差值：

import pandas as pd

# 读取原始数据（如果是本地文件可替换为pd.read_csv，分隔符用\s+适配空格分隔）
df = pd.read_table("your_data.txt", sep="\s+")

# 定义分组处理函数
def process_group(group):
    # 生成sequence与cd4的笛卡尔积
    seq_pairs = pd.merge(group[["sequence", "cd4"]], group[["sequence", "cd4"]], 
                         how="cross", suffixes=("_1", "_2"))
    # 筛选非重复组合（仅保留seq2 >= seq1）
    seq_pairs = seq_pairs[seq_pairs["sequence_2"] >= seq_pairs["sequence_1"]]
    # 生成组合标识列
    seq_pairs["comb"] = seq_pairs.apply(lambda x: f"{x['sequence_1']},{x['sequence_2']}", axis=1)
    # 计算CD4差值
    seq_pairs["cd4_lag"] = seq_pairs["cd4_2"] - seq_pairs["cd4_1"]
    # 关联原始行的time、cd4等信息
    result = pd.merge(seq_pairs, group, left_on="sequence_1", right_on="sequence", how="left")
    # 整理输出列顺序
    return result[["id", "time", "cd4", "sequence", "comb", "cd4_lag"]]

# 分组处理并合并结果
final_df = df.groupby("id").apply(process_group).reset_index(drop=True)
# 按ID和组合排序
final_df = final_df.sort_values(by=["id", "comb"])

print(final_df)

方案2：R（dplyr + tidyr） #

利用crossing生成笛卡尔积，分组计算差值：

library(dplyr)
library(tidyr)

# 读取原始数据
df <- read.table(text = "id   time       cd4  sequence
1   -0.741958   548  1 
1   -0.246407   893  2
1    0.243669   657  3
        
2   -2.7296369  464  1
2   -2.2505131  845  2
2   -0.221766   752  3
2    0.221766   459  4
2    0.77481198 181  5
2    1.256673   434  6", header = TRUE)

# 分组计算并整理结果
final_df <- df %>%
  group_by(id) %>%
  # 存储当前组的sequence和cd4列表
  mutate(seq_list = list(sequence), cd4_list = list(cd4)) %>%
  # 生成所有sequence组合
  unnest(crossing(seq1 = seq_list, seq2 = seq_list)) %>%
  # 筛选非重复组合
  filter(seq2 >= seq1) %>%
  # 生成组合标识和差值
  mutate(comb = paste(seq1, seq2, sep = ","),
         cd4_lag = cd4_list[[1]][match(seq2, sequence)] - cd4_list[[1]][match(seq1, sequence)]) %>%
  # 关联原始行信息
  left_join(df, by = c("id", "sequence" = "seq1")) %>%
  # 调整列顺序并排序
  select(id, time, cd4, sequence, comb, cd4_lag) %>%
  arrange(id, comb) %>%
  ungroup()

print(final_df)

内容的提问来源于stack exchange，提问作者Ahir Bhairav Orai