Add new column with specific increasing of a quarter using python

user_5942100 0
Viewed 70

I have a dataframe, df, that has a quarters column where I would like to add an additional increased quarters column adjacent to it (increased by 2)

Data

id  date    
a   Q1 2022 
a   Q1 2022 
a   Q1 2022 
a   Q1 2022 
b   Q1 2022 
b   Q1 2022

Desired

id  date      new 
a   Q1 2022   Q3 2022
a   Q1 2022   Q3 2022
a   Q1 2022   Q3 2022
a   Q1 2022   Q3 2022
b   Q1 2022   Q3 2022
b   Q1 2022   Q3 2022

Doing

df['new'] = df.index + 2

However this is not adding 2 consistently to the entire column I am still troubleshooting, any suggestion is appreciated

pandas numpy python
1 Answers

Reformat the strings in date in such a way that the resulting date format is YearQuarter so that it can be parsed into PeriodIndex, now add 2 to this index and strftime to convert back to orignal format

s = df['date'].str.replace(r'(\S+) (\S+)', r'\2\1')
df['new'] = (pd.PeriodIndex(s, freq='Q') + 2).strftime('Q%q %Y')

  id     date      new
0  a  Q1 2022  Q3 2022
1  a  Q1 2022  Q3 2022
2  a  Q1 2022  Q3 2022
3  a  Q1 2022  Q3 2022
4  b  Q1 2022  Q3 2022
5  b  Q1 2022  Q3 2022
user_128331660
Related