Change Quarters as obtained in time series to financial quarters

user_13186690 0
Viewed 25

So I have a table as shown. It has a date quarter of the year and a SoldItems column. I would like to change it to words and also have it in Fiscal year quarters where Jan-Mar =Qtr4, Apr-Jun = Qtr1, Jul-Sep =Qtr2, Oct-Dec =Qtr3.

0   2019-01-01   1  23
1   2019-01-02   1  87
2   2019-01-03   1  54
3   2019-01-04   1  63
4   2019-01-05   1  14

How do I do that?

pandas time-series
2 Answers

You can use PeriodIndex where by using the freq parameter, you can set when to start the year. Note that I added a few more rows to illustrate that it works in other months.

df['Qtr'] = pd.PeriodIndex(df['Date'], freq='Q-MAR').strftime('Qtr%q')

Output:

         Date   Qtr  SoldItems
0  2019-01-01  Qtr4         23
1  2019-01-02  Qtr4         87
2  2019-01-03  Qtr4         54
3  2019-01-04  Qtr4         63
4  2019-01-05  Qtr4         14
5  2019-04-03  Qtr1         54
6  2019-09-04  Qtr2         63
7  2019-11-05  Qtr3         14
Admin1

First method using np.where:

d = '''0   2019-01-01   1  23
1   2019-01-02   1  87
2   2019-01-03   1  54
3   2019-01-04   1  63
4   2019-01-05   1  14'''
data=[x.split() for x in d.split('\n')]
df = pd.DataFrame(data)
df[2]=df[2].astype(int)


#solution
df['Q'] = np.where(df[2]<4, 'Qtr4',
         np.where(df[2]<7,'Qtr1',
         np.where(df[2]<10, 'Jul-Sep','Oct-Dec')))

Output:

enter image description here

Another method using pd.cut:

bins = [0,3,6,9,12]
pd.cut(df[2], bins = bins, labels = [f'Qtr{i}' for i in range(1,len(bins))])
user_72816750
Related