How to do quarter in python dataframe?

user_15506607 0
Viewed 363

I've a dataframe df1:

     Date     Number
0 Jan-2017       20
1 Sep-2021       30
2 Jul-2021       40
3 Mar-2017       50
4 Apr-2020       60
5 Jun-2020       70
6 Feb-2017       80

'Date' column is in datatype of 'object'

I need to group the 'Date' in quarter like

1st quater -->(Jan,Feb,Mar)
2nd quater -->(Apr,May,Jun)
3rd quater -->(Jul,Aug,Sep)
4th quater -->(Oct,Nov,Dec)

Sum the 'Number' column

Expected Output like this:

     Date     Number
0 Q1-2017       150
1 Q3-2021        70
2 Q2-2020       130

Thank You For Your Time :)

pandas dataframe python
3 Answers

You can also, try this:

df.groupby(pd.to_datetime(df['Date'], format='%b-%Y').dt.to_period('Q'))['Number'].sum()

Output:

Date
2017Q1    150
2020Q2    130
2021Q3     70
Freq: Q-DEC, Name: Number, dtype: int64
user_63615310

The groupby can be done using the datetime attributes (make sure your Date column is datetime, i.e. df['Date'] = pd.to_datetime(df['Date'])):

>>> df.groupby([df['Date'].dt.quarter, df['Date'].dt.year]).sum()
           Number
Date Date        
2017 1        150
2020 2        130
2021 3         70

Just a matter of renaming the index then. You could achieve the format you specified by:

df = df.groupby([df['Date'].dt.quarter, df['Date'].dt.year]).sum()
df.index = df.index.set_names(['Quarter', 'Year'])
q = df.index.get_level_values('Quarter').astype(str)
y = df.index.get_level_values('Year').astype(str)
df.index = 'Q'+ q + '-' + y

Giving:

         Number
Q1-2017     150
Q2-2020     130
Q3-2021      70
user_133869790

Use .resample() + .sum() + .query():

df_out = (df.assign(Date=pd.to_datetime(df['Date'], format='%b-%Y'))
            .resample('Q', on='Date', kind='period')
            .sum()
            .query('Number != 0')
         ).reset_index()

Result:

print(df_out)

     Date  Number
0  2017Q1     150
1  2020Q2     130
2  2021Q3      70
user_150706970
Related