How to find Quarter Aging in Python pandas dataframe-CodePudding

Using date, I have already created Quarter column in pandas dataframe

df['Quarter'] = pd.DatetimeIndex(df['date']).quarter

Now, i am looking for Quarter aging as a calculated column like below

df['Quarter Aging'] = ????

so that I can compare current quarter and Last quarter sales differences dynamically when years are passing in the sales reporting. Expected output is Click here

For example, If my Current Quarter is 2022-Q4, then my Quarter aging is 0 Likewise,

2022-Q4 = 0

2022-Q3 = -1

2022-Q2 = -2

2022-Q1 = -3

2021-Q4 = -4

2021-Q3 = -5

2021-Q2 = -6

2021-Q1 = -7

. . . . . . . . . .

2018-Q1 = -19

Click here to see expected column

CodePudding user response：

You should consider to separate year and the quarter into different column. For example 2022Q4 became year = 2022 and quarter = 4. That way you could simply get quarter aging by using the following function:

def compute_quarter_aging(year, quarter, current_year, current_quarter):
    return 4 * (year - current_year) - (current_quarter - quarter)

In your case current_year is 2022 and current_quarter is 4.

In case your data has already been that way you could use python slice to get quarter from string year and quarter.

CodePudding user response：

You can use this function with df.apply method like this.

df["quarter_aging"] = df["quarter"].apply(q_age)


def q_age(quarter):
    q = quarter.split("-")
    year = int(q[0])
    quarter = int(q[1][1])
    quarter_aging = ((year - 2022) * 4 )   (quarter - 4)
    return(quarter_aging)