在pandas中，用groupby，total的百分比注释每一行？

Question

我有一个数据帧，看起来像这样：

Company       Speciality      Payment
AcmeCorp      Roofing         50.00
AcmeCorp      Grounding       50.00
LolCorp       Roofing         106.00
LolCorp       Grounding       94.00

我想添加一个百分比列，如下所示：

^{pr2}$

最好的办法是什么？在

我可以用这样的方法把事情搞得一团糟：

df_m = df.groupby('Company').sum()
final_df = pd.merge(df, df_m, on='Company', suffixes=('Raw', 'Total))
final_df['Percent of Total Payment] = final_df['Payment Raw'] / final_df['Payment_Total']

但我想知道有没有更有效的方法。在

Answer 1

使用groupby/transform生成与原始数据帧长度相同的列。这允许您避免调用pd.merge。在

import numpy as np
import pandas as pd

df = pd.DataFrame({'Company': ['AcmeCorp', 'AcmeCorp', 'LolCorp', 'LolCorp'],
 'Payment': [50.0, 50.0, 106, 94.00],
 'Speciality': ['Roofing', 'Grounding', 'Roofing', 'Grounding']})

total = df.groupby('Company')['Payment'].transform('sum')
df['percent'] = df['Payment']/total
print(df)

收益率

^{pr2}$

---

尽管

total = df.groupby('Company')['Payment'].transform('sum')
df['percent'] = df['Payment']/total

可以简化成一个直线

df['percent'] = df.groupby('Company')['Payment'].transform(lambda x: x/x.sum())

因为像.transform('sum')这样的内置操作比使用自定义函数（例如.transform(lambda x: x/x.sum())）的操作更快，所以两行版本更快（尤其是对于大型数据帧）

当然，两行版本也可以写成

df['percent'] = df['Payment'] / df.groupby('Company')['Payment'].transform('sum')

在速度没有损失的情况下，少了一个命名变量，但可能更难阅读。在

---

下面是一个100K行数据帧的基准测试：

In [53]: %timeit using_transform(df)
100 loops, best of 3: 8.5 ms per loop

In [54]: %timeit using_one_liner(df)
10 loops, best of 3: 20.2 ms per loop

In [55]: %timeit orig(df)
10 loops, best of 3: 30.2 ms per loop

---

这是用于执行基准测试的设置。在

import numpy as np
import pandas as pd

N = 10**5
df = pd.DataFrame({'Company': np.random.choice(list('ABCD'), size=N),
    'Payment': np.random.randint(10, size=N),
    'Speciality': np.random.choice(list('XYZ'), size=N)})

def using_transform(df):
    total = df.groupby('Company')['Payment'].transform('sum')
    df['percent'] = df['Payment']/total
    return df

def using_one_liner(df):
    df['percent'] = df.groupby('Company')['Payment'].transform(lambda x: x/x.sum())
    return df

def orig(df):
    df_m = df.groupby('Company').sum()
    final_df = pd.merge(df, df_m, left_on='Company', right_index=True, suffixes=('_Raw', '_Total'))
    final_df['Percent of Total Payment'] = final_df['Payment_Raw'] / final_df['Payment_Total']
    return final_df