How to display the quarter for a given date using pandas

Question

I am trying to generate a 4-4-5 calendar. I have a dataframe of dates from 29-03-2020 to 2022-04-09 consisting of their equivalent week number. 29-03-2020 is the starting date of the fiscal year. I am trying to generate a column with their respective quarters it would belong to.

Here is the final df I am looking for,

    a      |  count  | quarter
2020-04-04 |    1    | Q1 2021
2020-04-11 |    2    | Q1 2021
.
.
.
2021-03-27 |    52   | Q4 2021
2021-04-03 |    53   | Q4 2021           #since 2020 is a leap year there are 53 weeks otherwise it will be 52 weeks
2021-04-10 |    1    | Q1 2022
2021-04-17 |    2    | Q1 2022
.
2022-03-02 |    52   | Q4 2022
2022-04-09 |    1    | Q1 2023

I made the following attempt,

import pandas as pd
import numpy as np
year_start = '2020-03-29'
year_end = '2022-04-09'
week_end_sat = pd.DataFrame(pd.date_range(year_start, year_end, freq=f'W-SAT'), columns=['a'])
first_day_of_year = week_end_sat.iloc[0, 0].replace(day=1, month=1)
baseline = pd.DataFrame(pd.date_range(first_day_of_year, periods=len(week_end_sat), freq=f'W-SAT'), columns=['a'])
week_end_sat['count'] = baseline['a'].dt.isocalendar().week
week_end_sat['quarter'] = 'Q' + baseline['a'].dt.quarter.astype(str) + ' ' + (baseline['a'].dt.year+1).astype(str)
week_end_sat['b'] = baseline['a']
all_days_df = pd.DataFrame(pd.date_range(year_start, year_end), columns=['a'])
merge_df = pd.merge(all_days_df,week_end_sat, on='a', how='left')
merge_df['count'] = merge_df['count'].bfill()
merge_df['quarter'] = merge_df['quarter'].bfill()
merge_df['week_day'] = merge_df['a'].dt.day_name()

I get the quarters correct till week number 52, that is, Q4 2021 but after that it messes up. For 53rd week I get Q1 2022 and so on. Shouldn't it be Q4 2021 for 53rd week as well since it is a leap year? Could anyone guide me as to I may correct this?

EDIT

What if I also want to display the Month it belongs to for each date?

The final df should look like this,

   a       |  count  | quarter | month
2020-04-04 |    1    | Q1 2021 | 2020-04
2020-04-11 |    2    | Q1 2021 | 2020-04
.
.
.
2021-03-27 |    52   | Q4 2021 | 2021-03
2021-04-03 |    53   | Q4 2021 | 2021-03        
2021-04-10 |    1    | Q1 2022 | 2021-04
2021-04-17 |    2    | Q1 2022 | 2021-04
.
2022-03-02 |    52   | Q4 2022 | 2022-03
2022-04-09 |    1    | Q1 2023 | 2022-04

user_2538939 · Answer

Assuming year_start is always the start of a fiscal year, you can do this with a for loop:

# Generate a week-to-accounting-month mapping
m = np.roll(np.arange(1, 13, dtype='int'), -3)
w = np.tile([4,4,5], 4)

acct_month = {
    index + 1: month
        for index, month in enumerate(np.repeat(m, w))
}
acct_month[53] = 3 # week 53, if exists, always belong to month 3

#
data = []
y, week = year_start.year, 1
month = year_start.replace(month=4, day=1)

# A fiscal year has 53 weeks if it starts on a leap year. 52 weeks otherwise
num_week = lambda: 53 if (y % 4 == 0 and y % 100 != 0) or (y % 400 == 0) else 52

for date in pd.date_range(year_start, year_end, freq='7D'):
    data.append((date + pd.Timedelta(days=6), y+1, week, month))
    
    week += 1
    if week > num_week():
        y += 1
        week = 1
        
    if acct_month[week] != month.month:
        month += pd.offsets.MonthBegin(1)
        
df = pd.DataFrame(data, columns=['week_end', 'fy', 'week_no', 'acct_month'])

# Q1, Q2 and Q3 always have 13 weeks
# Q4 may have 13 or 14 weeks
q = np.ceil(df['week_no'].div(13).clip(upper=4)).astype('int')
df['quarter'] = 'Q' + q.astype(str) + ' ' + df['fy'].astype(str)

Result:

      week_end    fy  week_no acct_month  quarter
0   2020-04-04  2021        1 2020-04-01  Q1 2021
1   2020-04-11  2021        2 2020-04-01  Q1 2021
2   2020-04-18  2021        3 2020-04-01  Q1 2021
3   2020-04-25  2021        4 2020-04-01  Q1 2021
...
48  2021-03-06  2021       49 2021-03-01  Q4 2021
49  2021-03-13  2021       50 2021-03-01  Q4 2021
50  2021-03-20  2021       51 2021-03-01  Q4 2021
51  2021-03-27  2021       52 2021-03-01  Q4 2021
52  2021-04-03  2021       53 2021-03-01  Q4 2021
53  2021-04-10  2022        1 2021-04-01  Q1 2022
54  2021-04-17  2022        2 2021-04-01  Q1 2022
55  2021-04-24  2022        3 2021-04-01  Q1 2022
...
100 2022-03-05  2022       48 2022-03-01  Q4 2022
101 2022-03-12  2022       49 2022-03-01  Q4 2022
102 2022-03-19  2022       50 2022-03-01  Q4 2022
103 2022-03-26  2022       51 2022-03-01  Q4 2022
104 2022-04-02  2022       52 2022-03-01  Q4 2022
105 2022-04-09  2023        1 2022-04-01  Q1 2023

How to display the quarter for a given date using pandas

1 Answers