CODEWITHSUNDEEP
pythonpandasdatetimegroup-by
spcol
spcol

Reputation: 433

Count how many values occur in a month Pandas

I have a dataframe as follows;

            Country
From Date

02/04/2020  Canada
04/02/2020  Ireland
10/03/2020  France
11/03/2020  Italy
15/03/2020  Hungary
.
.
.
10/10/2020 Canada

And I simply want to do a groupby() or something similar which will tell me how many times a country occurs per month

eg.

       Canada  Ireland France . . . 
2010 1  3      4       1
     2  4      3       2
.
.
.
    10  4      4      4

Is there a simple way to do this?

Any help much appreciated!

Upvotes: 1

Views: 1497

Answers (2)

adhg
adhg

Reputation: 10853

A different angle to solve your question would be to use groupBy, count_values and unstack.

It goes like this:

I assume your "from date" is type date (datetime64[ns]) if not:

df['From Date']=pd.to_datetime(df['From Date'], format= '%d/%m/%Y')

  1. convert the date to string with Year + Month:

    df['From Date'] = df['From Date'].dt.strftime('%Y-%m')

  2. group by From Date and count the values:
    df.groupby(['From Date'])['Country'].value_counts().unstack().fillna(0).astype(int).reindex()

desired result (from the snapshot in your question):

 Country    Canada  France  Hungary Ireland Italy    
 From Date                  
2020-02       0       0       0      1      0
2020-03       0       1       1      0      1
2020-04       1       0       0      0      0

note the unstack that places the countries on the horizontal, astype(int) to avoid instances such as 1.0 and fillna(0) just in case any country has nothing - show zero.

Upvotes: 1

BENY
BENY

Reputation: 323226

Check with crosstab

# df.index=pd.to_datetime(df.index, format= '%d/%m/%Y')
pd.crosstab(df.index.strftime('%Y-%m'), df['Country'])

Upvotes: 0

Related Questions