-1

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

oguz ismail
  • 1
  • 16
  • 47
  • 69
Yash
  • 5
  • 4
  • Welcome to StackOverflow! Essentially you are asking how to get time difference in quarters. Perhaps answers to this question could help https://stackoverflow.com/q/1406131/1328439. Can you use Pandas? – Dima Chubarov Dec 18 '22 at 13:17
  • Yes, i am using pandas. And the link you shared is not helping me to calculate quarter aging, I already calculated Quarter by using date column, Now I am finding out a way to calculate Quarter aging or Quarter relative number – Yash Dec 18 '22 at 13:31
  • So that I can always use Quarter aging = 0 & -1 for my Current quarter and previous quarter calculations dynamically irrespective of Years passing in the future – Yash Dec 18 '22 at 13:35

2 Answers2

1

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. But from your question, I see that you clearly get year and quarter from date. You should just directly separate them to different column instead of placing them on the same column.

tangorboyz
  • 39
  • 8
  • This works and but it's not dynamic. It should run when the year gets changed in the future. Thanks, – Yash Dec 18 '22 at 19:51
  • Now the year gets changed, Can you please assist here to make it dynamic – Yash Jan 03 '23 at 13:31
  • Now, the expected output should be like this Quarter aging for 2023-Q1 ----> 0 ,,,,, Quarter aging for 2022-Q4 ----> -1 ,,,,,, Quarter aging for 2022-Q3----> -2,,,,,,, Quarter aging for 2022-Q2----> -3,,,,,,, Quarter aging for 2022-Q1----> -4 – Yash Jan 03 '23 at 13:31
1

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)
Rashmi Shehana
  • 137
  • 7
  • Yes, Its working and giving me the expected column values. Thanks a lot. – Yash Dec 18 '22 at 20:00
  • Hi Rashmi, How can we make that number "4" dynamic, I am asking this because now we jumped into 2023 and this code is not working. now If my Current Quarter is 2023-Q1, then my Quarter aging is 0 Likewise, 2023-Q1 = 0, 2022-Q4 = -1, 2022-Q3 = -2, 2022-Q2 = -3, 2022-Q1 = -4, – Yash Jan 03 '23 at 08:37
  • Hi Yash, Do you want to make the quarter dynamic or the 2022 year dynamic here? I have added 4 here because there are 4 quarters in any year. – Rashmi Shehana Jan 03 '23 at 11:04
  • If you want to get the current year here, first import date module like this on the top. --> from datetime import date and instead of 2022 add this --> int(date.today().year) – Rashmi Shehana Jan 03 '23 at 11:09
  • As per your formula, I am getting Quarter aging = "-3" for the 2023-Q1. But it supposes to be quarter aging = "0" – Yash Jan 03 '23 at 13:23
  • Now, the expected output should be like this Quarter aging for 2023-Q1 ----> 0 ,,,,, Quarter aging for 2022-Q4 ----> -1 ,,,,,, Quarter aging for 2022-Q3----> -2,,,,,,, Quarter aging for 2022-Q2----> -3,,,,,,, Quarter aging for 2022-Q1----> -4 ,,,,,,, – Yash Jan 03 '23 at 13:27
  • To get the output you have posted, you can use this.--> quarter_aging = ((year - 2023) * 4 ) + (quarter - 1) – Rashmi Shehana Jan 03 '23 at 16:47
  • 1
    Hi, This works. Thank you Rashmi. And i have made it dynamic now. ----------------------- my_date = dt.date.today() year, week_num, day_of_week = my_date.isocalendar() CY = year and CQ = df['Quarter'].max() Then, quarter_aging = ((year - CY) * 4 ) + (quarter - CQ) – Yash Jan 03 '23 at 19:50