Add new column based on sum of a column and grouped by 2 other columns in Pandas
<p>I have the dataframe:</p>

<pre><code>df = pd.DataFrame({'Continent':['North America','North America','North America','Europe','Europe','Europe','Europe'],
                'Country': ['US','Canada','Mexico','France','Germany','Spain','Italy'],
                'Status': ['Member','Non-Member','Non-Member','Member','Non-Member','Member','Non-Member'],
                'Units': [27,5,4,10,15,8,8]})

print df

       Continent  Country      Status  Units
0  North America       US      Member     27
1  North America   Canada  Non-Member      5
2  North America   Mexico  Non-Member      4
3         Europe   France      Member     10
4         Europe  Germany  Non-Member     15
5         Europe    Spain      Member      8
6         Europe    Italy  Non-Member      8
</code></pre>

<p>I need to add 2 columns which are summary statistics about the Continents. I need a column with the sum of Units for Member countries and Non Member countries.</p>

<p>so that the final output would look like:</p>

<pre><code>       Continent  Member Units  Non-Member Units  Country      Status  Units
0  North America            27                 9       US      Member     27
1  North America            27                 9   Canada  Non-Member      5
2  North America            27                 9   Mexico  Non-Member      4
3         Europe            18                23   France      Member     10
4         Europe            18                23  Germany  Non-Member     15
5         Europe            18                23    Spain      Member      8
6         Europe            18                23    Italy  Non-Member      8
</code></pre>

<p>It seems like I need to use groupby but I can't figure out how to take the groupby values and re-insert them into the dataframe as new columns. </p>

<pre><code>summary_stats = df.groupby(['Continent','Status'])['Units'].sum()
print summary_stats

Continent      Status    
Europe         Member        18
               Non-Member    23
North America  Member        27
               Non-Member     9
Name: Units, dtype: int64
</code></pre>

<p>I also tried not using groupby with these:</p>

<pre><code>df['Member Units'] = df['Units'][df['Status'] == 'Member'].sum()
df['Non-Member Units'] = df['Units'][df['Status'] == 'Non-Member'].sum()
</code></pre>

<p>but that doesn't differentiate by Continent so it just adds up all the Members and Non-Members</p>

<p>Any help is greatly appreicated!</p>