Pandas groupby sum
<p>I have a dataframe as follows:</p>

<pre><code>ref, type, amount
001, foo, 10
001, foo, 5
001, bar, 50
001, bar, 5
001, test, 100
001, test, 90
002, foo, 20
002, foo, 35
002, bar, 75
002, bar, 80
002, test, 150
002, test, 110
</code></pre>

<p>This is what I'm trying to get:</p>

<pre><code>ref, type, amount, foo, bar, test
001, foo, 10, 15, 55, 190
001, foo, 5, 15, 55, 190
001, bar, 50, 15, 55, 190
001, bar, 5, 15, 55, 190
001, test, 100, 15, 55, 190
001, test, 90, 15, 55, 190
002, foo, 20, 55, 155, 260
002, foo, 35, 55, 155, 260
002, bar, 75, 55, 155, 260
002, bar, 80, 55, 155, 260
002, test, 150, 55, 155, 260
002, test, 110, 55, 155, 260
</code></pre>

<p>So I have this:</p>

<pre><code>df.groupby('ref')['amount'].transform(sum)
</code></pre>

<p>But how can I filter it such that the above only applies to rows where <code>type=foo</code> or <code>bar</code> or <code>test</code>?</p>

<p>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.unstack.html" rel="nofollow"><code>unstack</code></a> and then <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.merge.html" rel="nofollow"><code>merge</code></a> to original <code>DataFrame</code>:</p>

<pre><code>df1 = df.groupby(['ref','type'])['amount'].sum().unstack().reset_index()
print (df1)
type  ref  bar  foo  test
0     001   55   15   190
1     002  155   55   260

df = pd.merge(df, df1, on='ref')
print (df)
    ref  type  amount  sums  bar  foo  test
0   001   foo      10    15   55   15   190
1   001   foo       5    15   55   15   190
2   001   bar      50    55   55   15   190
3   001   bar       5    55   55   15   190
4   001  test     100   190   55   15   190
5   001  test      90   190   55   15   190
6   002   foo      20    55  155   55   260
7   002   foo      35    55  155   55   260
8   002   bar      75   155  155   55   260
9   002   bar      80   155  155   55   260
10  002  test     150   260  155   55   260
11  002  test     110   260  155   55   260
</code></pre>

<p><strong>Timings</strong>:</p>

<pre><code>In [506]: %timeit (pd.merge(df, df.groupby(['ref','type'])['amount'].sum().unstack().reset_index(), on='ref'))
100 loops, best of 3: 3.4 ms per loop

In [507]: %timeit (pd.merge(df, pd.pivot_table(df, values='amount', index=['ref'], columns=['type'], aggfunc=np.sum), left_on='ref', right_index=True))
100 loops, best of 3: 4.99 ms per loop
</code></pre>

python pandas merge group-by sum