Count the number of occurrences for each month

Question

I have a Pandas Dataframe and wish to count and display occurrences of events per month as following. How do I do it?

I have a Pandas Dataframe as follows:

Name    Month

Jack    February
Jill    December
Bill    September
Jack    June
Jill    August
Bill    July
.
.
.

I wish to have a count of names born in each month and then display as follows:

Name    January   February   March  ...  December

Jack      0          6        10    ...     3
Jill      2          8         4    ...     2
Bill      4          5         9    ...     0
.
.
.

How do I do it?

Read the help pages for [`pivot_table`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.pivot_table.html). — Brendan, Jul 05 '19 at 13:29
Question #9 on the dup target should answer your question https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe — piRSquared, Jul 05 '19 at 13:37

score 0 · Answer 1 · answered Jul 05 '19 at 13:30

Available data

   Name      Month
0  Jack   February
1  Jill   December
2  Bill  September
3  Jack       June
4  Jill     August
5  Bill       July
6  Mark       July
7  Mark       July

using pd.get_dummies

pd.get_dummies(df.set_index('Name')['Month']).reset_index().groupby('Name').sum()

Output

      August  December  February  July  June  September
Name                                                   
Bill       0         0         0     1     0          1
Jack       0         0         1     0     1          0
Jill       1         1         0     0     0          0
Mark       0         0         0     2     0          0

Thanks a lot guys. Both of the above approaches help :) – Ganesh Shahane Jul 05 '19 at 13:49 — Ganesh Shahane, Jul 05 '19 at 13:49

score 0 · Answer 2 · answered Jul 05 '19 at 13:38

You could use pandas.crosstab here:

months = ['January', 'February', 'March', 'April', 'May', 'June',
          'July', 'August', 'September', 'October', 'November', 'December']

pd.crosstab(df.Name, df.Month).reindex(months, axis=1, fill_value=0)

[out]

Month  January  February  March  April  May  June  July  August  September  \
Name                                                                         
Bill         0         0      0      0    0     0     1       0          1   
Jack         0         1      0      0    0     1     0       0          0   
Jill         0         0      0      0    0     0     0       1          0   

Month  October  November  December  
Name                                
Bill         0         0         0  
Jack         0         0         0  
Jill         0         0         1

Count the number of occurrences for each month

2 Answers2