I have a question on using groupby() with the following shortened example of pandas dataframe. What I am trying to achieve here is to add-up the 'amount' value per 'date' for each 'store_id' while keeping the 'days_of_week' and 'holiday' column.
store_id date amount days_of_week holiday
0 0 2017-11-14 100 1 0
1 0 2017-11-14 -100 1 0
2 1 2017-11-14 122 1 0
3 1 2017-11-19 55 6 1
4 2 2017-11-19 11 6 1
5 2 2017-11-19 32 6 1
So the result should look like the following.
store_id date amount days_of_week holiday
0 0 2017-11-14 0 1 0
1 1 2017-11-14 122 1 0
2 1 2017-11-19 55 6 1
3 2 2017-11-19 43 6 1
I've tried dropping the 'day_of_week' and 'holiday' column, then using groupby() to get the sum per each date so far. But that is far from achieving my desired form of result.
train = train.drop(columns=['days_of_week', 'holiday'])
train.groupby(['store_id', 'date'])['amount'].sum()
Is there any other methods that I'm unaware of to easily get the second example form?