python - Pandas 数据框分组并加入

Question

让我们假设有这个:

np.random.seed(123)
df = pd.DataFrame({'A' : ['foo', 'bar', 'foo', 'bar',
                          'foo', 'bar', 'foo', 'foo'],
                   'B' : ['one', 'one', 'two', 'three',
                           'two', 'two', 'one', 'three'],
                   'C' : np.random.randn(8),
                   'D' : np.random.randn(8)})

因此数据框如下所示:

     A      B         C         D
0  foo    one -1.085631  1.265936
1  bar    one  0.997345 -0.866740
2  foo    two  0.282978 -0.678886
3  bar  three -1.506295 -0.094709
4  foo    two -0.578600  1.491390
5  bar    two  1.651437 -0.638902
6  foo    one -2.426679 -0.443982
7  foo  three -0.428913 -0.434351

我想将 df 按 B 分组，计算 C 列的总和乘以 D 的总和每个组的 code> 列，最后将这个分组结果与原始 df 连接起来。在 Python 中:

grouped = df.groupby('B').apply(lambda group: sum(group['C'])*sum(group['D'])).reset_index()
grouped.columns = ['B', 'new_value']
df.join(grouped.set_index('B'), on='B')

是否存在一种更 pythonic 和更有效的方法来解决此类问题？

Answer 1

解决方案一:

In [25]: df.groupby('B')['C','D'].transform('sum').prod(1)
Out[25]:
0    0.112635
1    0.112635
2    0.235371
3    1.023841
4    0.235371
5    0.235371
6    0.112635
7    1.023841
dtype: float64

解决方案 2:

In [18]: grp = df.groupby('B')

In [19]: grp['C'].transform('sum') * grp['D'].transform('sum')
Out[19]:
0    0.112635
1    0.112635
2    0.235371
3    1.023841
4    0.235371
5    0.235371
6    0.112635
7    1.023841
dtype: float64

演示:

In [20]: df
Out[20]:
     A      B         C         D
0  foo    one -1.085631  1.265936
1  bar    one  0.997345 -0.866740
2  foo    two  0.282978 -0.678886
3  bar  three -1.506295 -0.094709
4  foo    two -0.578600  1.491390
5  bar    two  1.651437 -0.638902
6  foo    one -2.426679 -0.443982
7  foo  three -0.428913 -0.434351

In [21]: grp = df.groupby('B')

In [22]: df['new'] = grp['C'].transform('sum') * grp['D'].transform('sum')

In [23]: df
Out[23]:
     A      B         C         D       new
0  foo    one -1.085631  1.265936  0.112635
1  bar    one  0.997345 -0.866740  0.112635
2  foo    two  0.282978 -0.678886  0.235371
3  bar  three -1.506295 -0.094709  1.023841
4  foo    two -0.578600  1.491390  0.235371
5  bar    two  1.651437 -0.638902  0.235371
6  foo    one -2.426679 -0.443982  0.112635
7  foo  three -0.428913 -0.434351  1.023841


In [26]: df['new2'] = df.groupby('B')['C','D'].transform('sum').prod(1)

In [27]: df
Out[27]:
     A      B         C         D       new      new2
0  foo    one -1.085631  1.265936  0.112635  0.112635
1  bar    one  0.997345 -0.866740  0.112635  0.112635
2  foo    two  0.282978 -0.678886  0.235371  0.235371
3  bar  three -1.506295 -0.094709  1.023841  1.023841
4  foo    two -0.578600  1.491390  0.235371  0.235371
5  bar    two  1.651437 -0.638902  0.235371  0.235371
6  foo    one -2.426679 -0.443982  0.112635  0.112635
7  foo  three -0.428913 -0.434351  1.023841  1.023841

检查:

In [28]: df.new.eq(df.new2).all()
Out[28]: True