DataFrame Groupby while maintaining original DataFrame
<p>I have a DataFrame that has  9 columns which are encoded values for Day of the week(1-7), Week of the Year(1-52), Month of the Year (1-12), Time bin (every 3 hours), Salary Day(0,1) and Holiday(0,1) and Amount(real number). The time is placed in a time bin e.g. 15:00 is placed in 6th time bin and 7:34 is placed in the 3rd time bin. </p>

<pre><code>Day Week    Month   Time    Salary  Holiday Amount
1   5       2      1        0       0        700.0
1   5       2      1        0       0        800.0
3   18      5      7        1       0        90.0
</code></pre>

<p>Basically, I need to group the data by the first 6 columns and create a new column which is the summed value of Amount. However, this amount is will be repeated since I need to maintain the original dataframe. So, it must only sum purchases that have the same Day, Week, Month, Time, Salary and Holiday i.e. sum in that group. </p>

<pre><code>Day Week    Month   Time    Salary  Holiday Amount  Sum
1   5       2      1        0       0        700.0   1500.0
1   5       2      1        0       0        800.0   1500.0
3   18      5      7        1       0        90.0    90.0
</code></pre>

<p>I have grouped the data frame by Day, Week, Month, Time, Salary, Holiday and summed Amount.</p>

<pre><code>temp= features.groupby(by=["Day", "Week", "Month", "Time", "Salary", "Holiday"])["Amount"].sum()
</code></pre>

<p>You can reset the index on temp and then do an outer merge with the original feature dataframe on all the columns you grouped by.</p>

<pre><code>result = features.merge(temp.reset_index(), on=["Day", "Week", "Month", "Time", "Salary", "Holiday"])
</code></pre>

python pandas dataframe group-by