CODEWITHSUNDEEP
pandas-groupby
Bode
Bode

Reputation: 529

grouping by weekly days pandas

I have a dataframe,df containing

 Index         Date & Time        eventName       eventCount
 0            2017-08-09              ABC            24
 1            2017-08-09              CDE           140
 2            2017-08-10              CDE           150
 3            2017-08-11              DEF           200
 4            2017-08-11              ABC            20
 5            2017-08-16              CDE            10
 6            2017-08-16              ABC            15
 7            2017-08-17              CDE            10
 8            2017-08-17              DEF            50
 9            2017-08-18              DEF            80
     ...

I want to sum the eventCount for each weekly day occurrences and plot for the total events for each weekly day(from MON to SUN) i.e. for example: Summation of the eventCount values of: 

2017-08-09 and 2017-08-16(Mondays)=189 
2017-08-10 and 2017-08-17(Tuesdays)=210
2017-08-16 and 2017-08-23(Wednesdays)=300

I have tried

dailyOccurenceSum=df['eventCount'].groupby(lambda x: x.weekday).sum()

and I get this error:AttributeError: 'int' object has no attribute 'weekday'

Upvotes: 1

Views: 336

Answers (1)

cs95
cs95

Reputation: 402333

Starting with df - 

df

   Index Date & Time eventName  eventCount
0      0  2017-08-09       ABC          24
1      1  2017-08-09       CDE         140
2      2  2017-08-10       CDE         150
3      3  2017-08-11       DEF         200
4      4  2017-08-11       ABC          20
5      5  2017-08-16       CDE          10
6      6  2017-08-16       ABC          15
7      7  2017-08-17       CDE          10
8      8  2017-08-17       DEF          50
9      9  2017-08-18       DEF          80

First, convert Date & Time to a datetime column - 

df['Date & Time'] = pd.to_datetime(df['Date & Time'])

Next, call groupby + sum on the weekday name.

df = df.groupby(df['Date & Time'].dt.weekday_name)['eventCount'].sum()
df

Date & Time
Friday       300
Thursday     210
Wednesday    189
Name: eventCount, dtype: int64

If you want to sort by weekday, convert the index to categorical and call sort_index - 

cat = ['Monday','Tuesday','Wednesday','Thursday','Friday','Saturday', 'Sunday']

df.index = pd.Categorical(df.index, categories=cat, ordered=True)
df = df.sort_index()
df

Wednesday    189
Thursday     210
Friday       300
Name: eventCount, dtype: int64

Upvotes: 1

Related Questions