CODEWITHSUNDEEP
pythonpandas
T. Hinde
T. Hinde

Reputation: 11

Counting number of events on each date in a dataframe in python

I'm attempting to count the number of events that occur on each date that occurs in the data frame. I have created a new dataframe with the dates occurring only once, how would i go about counting and totalling up the events that happen on each date.

I so far haven't been able to find a suitable method of doing so.

This is an example of the data I have:

date    event
01/01/10    1
01/01/10    1
01/01/10    2
02/01/10    1
04/01/10    3

I am hoping to get this results

date     event1 event2  event3
01/01/10    2     1      0
02/01/10    1     0      0
04/01/10    0     0      1

Any suggestions will be welcomed gratefully

Upvotes: 1

Views: 703

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

Use pd.crosstab:

pd.crosstab(df['date'], df['event'])

Output:

event      date  1  2  3
0      01/01/10  2  1  0
1      02/01/10  1  0  0
2      04/01/10  0  0  1

And, we can do some cleanup and renaming like this:

pd.crosstab(df['date'], df['event'])\
  .add_prefix('event')\
  .rename_axis(None, axis=1)\
  .reset_index()

Output:

       date  event1  event2  event3
0  01/01/10       2       1       0
1  02/01/10       1       0       0
2  04/01/10       0       0       1

Upvotes: 4

Related Questions