如何选取block分组首行定义试验条件并重组组内后续行数据

Python Pandas 版本 #

import pandas as pd

# 原始数据读入示例，你可以替换成自己的读入代码
df = pd.DataFrame({
    "trialcode": ["AM2A3", "Empathy", "ToM", "Transfer", "AM2D1", "Empathy", "ToM", "Transfer", "AM4M1", "Empathy", "ToM", "Transfer"],
    "response": [0, 1, 4, 2.5, 0, 4, 2, 6, 0, 6, 6, 10],
    "block": [1,1,1,1,2,2,2,2,3,3,3,3]
})

# 按block分组标记行号，0号是条件行，其余是因变量行
df["row_idx"] = df.groupby("block").cumcount()

# 提取每个block对应的条件
cond_df = df.query("row_idx == 0")[["block", "trialcode"]].rename(columns={"trialcode": "condition"})

# 因变量行转宽表
dv_df = df.query("row_idx > 0").pivot(index="block", columns="trialcode", values="response").reset_index()

# 合并表并调整列顺序
result = pd.merge(dv_df, cond_df, on="block")[["Empathy", "ToM", "Transfer", "condition", "block"]]

R Tidyverse 版本 #

library(tidyverse)

# 原始数据读入示例，可替换为自己的读入代码
df <- tibble(
  trialcode = c("AM2A3", "Empathy", "ToM", "Transfer", "AM2D1", "Empathy", "ToM", "Transfer", "AM4M1", "Empathy", "ToM", "Transfer"),
  response = c(0, 1, 4, 2.5, 0, 4, 2, 6, 0, 6, 6, 10),
  block = c(1,1,1,1,2,2,2,2,3,3,3,3)
)

result <- df %>%
  group_by(block) %>%
  mutate(
    condition = first(trialcode),
    row_idx = row_number()
  ) %>%
  filter(row_idx > 1) %>%
  select(-row_idx) %>%
  pivot_wider(names_from = trialcode, values_from = response) %>%
  ungroup() %>%
  select(Empathy, ToM, Transfer, condition, block)

两种方法输出的结果和你要求的格式完全一致。

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