如何按ks_id合并DataFrame多行，拆分direction生成上下行时间列

这个需求属于长表转宽表的典型场景，分别提供Python和R生态下的常用实现：

Python（pandas）方案 #

import pandas as pd

# 示例数据构造，可替换为你的真实数据读取逻辑
df = pd.DataFrame({
    "ks_id": ["1a", "1a", "1b", "1b"],
    "direction": ["down", "up", "down", "up"],
    "time": [1, 3, 4, 7],
    "text": ["", "h", "", "e"]
})

# 转换逻辑
# 1. 透视time列得到对应方向的时间列
result = df.pivot(index="ks_id", columns="direction", values="time").reset_index()
result.columns = ["ks_id", "down_time", "up_time"]
# 2. 关联每个ks_id对应的非空text值
result["text"] = df.groupby("ks_id")["text"].agg(lambda x: x[x != ""].iloc[0]).values

运行后得到的result就是你需要的目标格式。

R（tidyverse）方案 #

library(tidyverse)

# 示例数据构造
df <- tibble(
  ks_id = c("1a", "1a", "1b", "1b"),
  direction = c("down", "up", "down", "up"),
  time = c(1,3,4,7),
  text = c("", "h", "", "e")
)

# 转换逻辑
result <- df %>%
  # 长转宽生成时间列
  pivot_wider(
    id_cols = ks_id,
    names_from = direction,
    values_from = time,
    names_glue = "{direction}_time"
  ) %>%
  # 关联非空的text字段
  left_join(df %>% filter(text != "") %>% select(ks_id, text), by = "ks_id")

之前用分组+mutate没有得到预期结果，是因为mutate仅会在分组内给每一行新增字段，不会把多行聚合为单行，需要用专门的宽表转换函数完成操作。

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