close

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

Hello Guys, How are you all? Hope You all Are Fine. Today We Are Going To learn about How to group and count rows by month and year using Pandas in Python. So Here I am Explain to you all the possible Methods here.

Without wasting your time, Let’s start This Article.

Table of Contents

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

  1. How to group and count rows by month and year using Pandas?

    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:

  2. group and count rows by month and year using Pandas

    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:

Method 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

Method 2

You can also use the “monthly” period with to_period with the dt accessor:

In [11]: df = pd.DataFrame({'birthdate': pd.date_range(start='20-12-2015', end='3-1-2016')})

In [12]: df['birthdate'].groupby(df.birthdate.dt.to_period("M")).agg('count')
Out[12]:
birthdate
2015-12    12
2016-01    31
2016-02    29
2016-03     1
Freq: M, Name: birthdate, dtype: int64

It’s worth noting if the datetime is the index (rather than a column) you can use resample:

df.resample("M").count()

Summery

It’s all About this issue. Hope all Methods helped you a lot. Comment below Your thoughts and your queries. Also, Comment below which Method worked for you? Thank You.

Also, Read