Counting number of entries per month pandas

user_12888591 0
Viewed 177

I have a df in format:

   start        end         
0  2020-01-01   2020-01-01
1  2020-01-01   2020-01-01
2  2020-01-02   2020-01-02
 ...
57 2020-04-01   2020-04-01
58 2020-04-02   2020-04-02

And I want to count the number of entries in each month and place it in a new df i.e. the number of 'start' entries for Jan, Feb, etc, to give me:

Month    Entries
2020-01   3
...
2020-04   2

I am currently trying something like this, but its not what I'm needing:

df.index = pd.to_datetime(df['start'],format='%Y-%m-%d')
df.groupby(pd.Grouper(freq='M'))
df['start'].value_counts()
pandas dataframe python
1 Answers

Use Groupby.count with Series.dt:

In [1282]: df
Out[1282]: 
         start         end
0   2020-01-01  2020-01-01
1   2020-01-01  2020-01-01
2   2020-01-02  2020-01-02
57  2020-04-01  2020-04-01
58  2020-04-02  2020-04-02

# Do this only when your `start` and `end` columns are object. If already datetime, you can ignore below 2 statements
In [1284]: df.start = pd.to_datetime(df.start)   
In [1285]: df.end = pd.to_datetime(df.end)

In [1296]: df1 = df.groupby([df.start.dt.year, df.start.dt.month]).count().rename_axis(['year', 'month'])['start'].reset_index(name='Entries')

In [1297]: df1
Out[1297]: 
   year  month  Entries
0  2020      1        3
1  2020      4        2
user_58208140
Related