使用Pandas按指定列计算跨行X/Y比值的高效实现问询

我确信Pandas里有解决这个问题的简便方法，但折腾了好几个小时试df.pivot()和df.groupby()都没搞定。

我正在处理的DataFrame结构如下：

uid               col1        col2      col3   aggregate this value
100002891-100003074 A      1         X         1
100002891-100003074 A      3         Y         11
100002891-100003074 B      1         X         7
100002891-100003074 B      1         Y         5
100002891-100003074 B      2         X         2
100002891-100003074 B      2         Y         16
100002891-100003074 B      3         X         10
100002891-100003074 B      3         Y         7
100006186-100006393 A      1         X         1
100006186-100006393 A      1         Y         1
100006186-100006393 A      3         X         2
100006186-100006393 B      2         X         1
100006186-100006393 B      2         Y         2
100042231-100042624 B      1         X         2
100042231-100042624 B      1         Y         1

需求是：按uid、col1、col2分组，计算col3中X对应的值除以Y对应的值的比值，保留分类列。结果行数大概是输入的一半（部分分组只有X或Y，所以实际会多一些）。

我试过用df.pivot()生成带X、Y列的DataFrame，但结果里有很多交替的NaN；也试过df.groupby().transform(lambda x)，但写不出合适的lambda表达式。

预期输出如下：

uid               col1        col2         X/Y Ratio
100002891-100003074 A      1                   1
100002891-100003074 A      3                   11
100002891-100003074 B      1                   1.4
100002891-100003074 B      2                   0.125
100002891-100003074 B      3                   1.42
100006186-100006393 A      1                   1
100006186-100006393 A      3                   2
100006186-100006393 B      2                   1.5
100042231-100042624 B      1                   2

另外需要处理超过1亿行的数据，方法得有性能保障。

针对大数据量场景，推荐使用**pivot_table或者groupby+unstack**的方案，这两个方法都是Pandas底层优化过的，性能优于自定义lambda逻辑。

方法1：使用pivot_table #

import pandas as pd

# 假设原DataFrame名为df
result = df.pivot_table(
    index=['uid', 'col1', 'col2'],
    columns='col3',
    values='aggregate this value',
    aggfunc='first'  # 每个分组下X/Y对应唯一值，用first直接取值即可
).reset_index()

# 计算X/Y比值，用fill_value处理仅存在X或Y的情况
result['X/Y Ratio'] = result['X'].div(result['Y'], fill_value=1)

# 筛选目标列并调整小数位数
result = result[['uid', 'col1', 'col2', 'X/Y Ratio']]
result['X/Y Ratio'] = result['X/Y Ratio'].round(2)

方法2：使用groupby+unstack #

import pandas as pd

# 按分组键聚合，将col3转为列
grouped = df.groupby(['uid', 'col1', 'col2', 'col3'])['aggregate this value'].first().unstack('col3')

# 计算比值并整理结果格式
result = grouped.assign(
    **{'X/Y Ratio': grouped['X'].div(grouped['Y'], fill_value=1)}
).reset_index()[['uid', 'col1', 'col2', 'X/Y Ratio']].round(2)

性能优化提示 #

• 两个方法均采用Pandas向量化操作，避免逐行循环，适合1亿级数据处理。
• 若内存不足，可通过pd.read_csv(chunksize=...)分块处理，或使用Dask等分布式框架扩展能力。
• 将uid、col1、col2、col3转为category类型，可大幅降低内存占用，提升运算速度。

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