如何在DataFrames.jl、R、Python中实现分组多列转置？

背景：InmemoryDatasets 中的实现示例 #

在InmemoryDatasets中，transpose函数可通过传递列选择元组实现分组转置，示例代码及结果如下：

原始数据构造 #

ds = Dataset([[1, 1, 1, 2, 2, 2],
              ["foo", "bar", "monty", "foo", "bar", "monty"],
              ["a", "b", "c", "d", "e", "f"],
              [1, 2, 3, 4, 5, 6]], [:g, :key, :foo, :bar])

分组转置操作 #

transpose(groupby(ds, :g), (:foo, :bar), id = :key)

输出结果 #

g   foo bar monty   foo_1   bar_1   monty_1
identity    identity    identity    identity    identity    identity    identity
Int64?  String? String? String? Int64?  Int64?  Int64?
1   1   a   b   c   1   2   3
2   2   d   e   f   4   5   6

下面分别给出DataFrames.jl、R、Python中实现相同功能的代码：

1. DataFrames.jl 实现 #

结合groupby、combine和unstack函数，分步骤完成分组、宽表转换与结果合并：

using DataFrames

# 构造原始数据
df = DataFrame(
    g = [1,1,1,2,2,2],
    key = ["foo", "bar", "monty", "foo", "bar", "monty"],
    foo = ["a", "b", "c", "d", "e", "f"],
    bar = [1,2,3,4,5,6]
)

# 分组转置逻辑
result = combine(groupby(df, :g)) do g_df
    # 分别对foo、bar列按key展开为宽表
    foo_wide = unstack(g_df, :key, :foo)
    bar_wide = unstack(g_df, :key, :bar)
    # 给bar对应的列名添加_1后缀
    rename!(bar_wide, names(bar_wide)[2:end] .=> string.(names(bar_wide)[2:end], "_1"))
    # 合并两个宽表（保留g列一次）
    hcat(foo_wide, bar_wide[:, Not(:g)])
end

# 调整列顺序与示例结果对齐
select!(result, :g, :foo, :bar, :monty, :foo_1, :bar_1, :monty_1)

2. R 实现 #

使用dplyr分组，搭配tidyr::pivot_wider一次性完成多列的宽表转换，通过names_glue控制列名格式：

library(dplyr)
library(tidyr)

# 构造原始数据
df <- data.frame(
    g = c(1,1,1,2,2,2),
    key = c("foo", "bar", "monty", "foo", "bar", "monty"),
    foo = c("a", "b", "c", "d", "e", "f"),
    bar = c(1,2,3,4,5,6)
)

# 分组转置逻辑
result <- df %>%
    group_by(g) %>%
    pivot_wider(
        names_from = key,
        values_from = c(foo, bar),
        names_glue = "{ifelse(.value == 'bar', paste0(key, '_1'), key)}"
    ) %>%
    ungroup() %>%
    # 调整列顺序与示例对齐
    select(g, foo, bar, monty, foo_1, bar_1, monty_1)

3. Python 实现 #

利用pandas的groupby.apply自定义分组处理逻辑，分别对foo、bar列做透视后合并结果：

import pandas as pd

# 构造原始数据
df = pd.DataFrame({
    "g": [1,1,1,2,2,2],
    "key": ["foo", "bar", "monty", "foo", "bar", "monty"],
    "foo": ["a", "b", "c", "d", "e", "f"],
    "bar": [1,2,3,4,5,6]
})

# 自定义分组转置函数
def process_group(group):
    # 对foo、bar分别按key透视
    foo_pivot = group.pivot(index="g", columns="key", values="foo").reset_index()
    bar_pivot = group.pivot(index="g", columns="key", values="bar").reset_index()
    # 给bar的列名添加_1后缀
    bar_pivot.columns = [f"{col}_1" if col != "g" else col for col in bar_pivot.columns]
    # 合并两个透视表
    return pd.merge(foo_pivot, bar_pivot, on="g")

# 应用分组逻辑并整理结果
result = df.groupby("g").apply(process_group).reset_index(drop=True)
# 调整列顺序与示例对齐
result = result[["g", "foo", "bar", "monty", "foo_1", "bar_1", "monty_1"]]

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