Python, pandas, cumulative sum in new column on matching groups
<p>If I have these columns in a dataframe:</p>

<pre><code>a     b  
1     5   
1     7
2     3
1,2   3
2     5
</code></pre>

<p>How do I create column <code>c</code> where column <code>b</code> is summed using groupings of column <code>a</code> (string), keeping the existing dataframe. Some rows can belong to more than one group.</p>

<pre><code>a     b     c
1     5     15
1     7     15
2     3     11
1,2   3     26
2     5     11
</code></pre>

<p>Is there an easy and efficient solution as the dataframe I have is very large.</p>

<p>You can first need split column <code>a</code> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.join.html" rel="nofollow"><code>join</code></a> it to original <code>DataFrame</code>:</p>

<pre><code>print (df.a.str.split(',', expand=True)
               .stack()
               .reset_index(level=1, drop=True)
               .rename('a'))
0    1
1    1
2    2
3    1
3    2
4    2
Name: a, dtype: object

df1 = df.drop('a', axis=1)
        .join(df.a.str.split(',', expand=True)
                      .stack()
                      .reset_index(level=1, drop=True)
                      .rename('a'))
print (df1)
   b  a
0  5  1
1  7  1
2  3  2
3  3  1
3  3  2
4  5  2
</code></pre>

<p>Then use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.transform.html" rel="nofollow"><code>transform</code></a> for <code>sum</code> without aggragation.</p>

<pre><code>df1['c'] = df1.groupby(['a'])['b'].transform(sum)
#cast for aggreagation join working with strings
df1['a'] = df1.a.astype(str)
print (df1)
   b  a   c
0  5  1  15
1  7  1  15
2  3  2  11
3  3  1  15
3  3  2  11
4  5  2  11
</code></pre>

<p>Last <code>groupby</code> by index and aggregate columns by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.agg.html" rel="nofollow"><code>agg</code></a>:</p>

<pre><code>print (df1.groupby(level=0)
          .agg({'a':','.join,'b':'first' ,'c':sum})
          [['a','b','c']] )

     a  b   c
0    1  5  15
1    1  7  15
2    2  3  11
3  1,2  3  26
4    2  5  11          
</code></pre>

python pandas dataframe group-by sum