pandas 数据框groupby并加入 [英] pandas dataframe groupby and join

问题描述

让我们假设有这个:

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列的总和，最后将每个分组结果与原始df合并. 在Python中:

I want to group the df by B, calculate the sum of C column multiplied by the sum of D column for each group and finally joining this grouped-by result with the original df. In 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 方法来解决此类问题吗?

There exists a more pythonic and efficient way to solve this kind of problem?

推荐答案

解决方案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

这篇关于 pandas 数据框groupby并加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！