python - How to group and count rows by month and year using Pandas?

Question

I have a dataset with personal data such as name, height, weight and date of birth. I would build a graph with the number of people born in a particular month and year. I'm using python pandas to accomplish this and my strategy was to try to group by year and month and add using count. But the closest I got is to get the count of people by year or by month but not by both.

df['birthdate'].groupby(df.birthdate.dt.year).agg('count')

Other questions in stackoverflow point to a Grouper called TimeGrouper but searching in pandas documentation found nothing. Any idea?

Answer 1

To group on multiple criteria, pass a list of the columns or criteria:

df['birthdate'].groupby([df.birthdate.dt.year, df.birthdate.dt.month]).agg('count')

Example:

In [165]:
df = pd.DataFrame({'birthdate':pd.date_range(start=dt.datetime(2015,12,20),end=dt.datetime(2016,3,1))})
df.groupby([df['birthdate'].dt.year, df['birthdate'].dt.month]).agg({'count'})

Out[165]:
                    birthdate
                        count
birthdate birthdate          
2015      12               12
2016      1                31
          2                29
          3                 1

UPDATE

As of version 0.23.0 the above code no longer works due to the restriction that multi-index level names must be unique, you now need to rename the levels in order for this to work:

In[107]:
df.groupby([df['birthdate'].dt.year.rename('year'), df['birthdate'].dt.month.rename('month')]).agg({'count'})

Out[107]: 
           birthdate
               count
year month          
2015 12           12
2016 1            31
     2            29
     3             1