CODEWITHSUNDEEP
pythonpandas
Rabin Hansda
Rabin Hansda

Reputation: 13

How to group date and with other column in pandas

How to use group by to date and category with date by year and category which has 3 and who's sales amount should be sum for each year.

I have tried using groupby and its not worked out

This is the input:

      date         category   sales
------------------------------------
0     20/2/2014    weekdays  120.96
1     05/03/2015   weekend   120.96
2     19/05/2014   weekdays   75.99
3     09/02/2014   weekend    60.76
4     15/03/2015   weekdays   49.01
5     03/03/2014   weekend    50.3
6     09/05/2014   weekend    203.2

df2 = df.groupby(['date','category'], as_index=False)['sales'].sum()
print (df2)

After using this, the code throws an error.

The output should be like this:

  date   category    sales
  -------------------------
  2014   weekdays    196.95
  2014   weekend     314.26
  2015   weekdays     49.01
  2015   weekend     120.96

Upvotes: 1

Views: 57

Answers (1)

anky
anky

Reputation: 75080

Use series.dt.year under df.groupby():

#df['date'] = pd.to_datetime(df['date']) : if date is object dtype
df.groupby([df.date.dt.year,'category'])['sales'].sum().reset_index()

   date  category   sales
0  2014  weekdays  196.95
1  2014   weekend  314.26
2  2015  weekdays   49.01
3  2015   weekend  120.96

Upvotes: 2

Related Questions