Python/Pandas: how to generate date ranges from month, quarter, year etc relative to a given timestamp?

Question

I have this dataframe

         2022-03-18
AAAMO00      33.001
AAAMO01      31.900
AAAMO02      30.800
AAAMO03      30.850
AAAQR01      30.850
AAAQR02      29.933
AAASN01      28.592
AAASN02      18.575
AAAYR01      20.700

'2022-03-18' is the "given timestamp", and 'AAA' is the product name, then when we have "MO" means month, and MO00 would be the next month compared to 2022-03-18, so April. MO01 = May etc

"QR" means "quarter". "QR01" would mean 2022-Q3 because the ref date 2022-03-18 is in 2022-Q1, hence QR00 is 2022-Q2, QR01 is 2022-Q3, the start and end date are just the start and end date of 2022Q3: 2022-07-01 and 2022-09-30 (QR00 is the NEXT quarter compared to the ref date, the same logic and naming convention as months)

From the dataframe I wish to get a new dataframe like this

How can I achieve this with Pandas? Thanks a lot.

(PS YR means year and SN means season, let's ignore the Season data for the moment :) )

Answer 1

Maybe something like the one below will work for you. Obviously, you will have to decide if the "YR" are relative to the current point in time or absolute

import pandas as pd
from pandas.tseries.offsets import DateOffset

# Initial dataframe
contract = ['dJKLMO00', 'JKLMO01', 'JKLMO02', 'JKLMO03', 'JKLQR01', 'JKLQR02', 'JKLSN01',
            'JKLSN02', 'JKLYR01']
value = [33.001, 31.900, 30.800, 30.850, 30.850, 29.993, 28.592, 18.575, 20.700]
data = {'index': contract, '2022-03-21': value}
df = pd.DataFrame(data)
print(df)

# New dataframe
data_new = {'date': pd.to_datetime(df.columns[1]), 'values': df.iloc[0].iat[1]}
df_new = pd.DataFrame(data_new, index=[0])
print(df_new)
map_quarters = {"01": 4,
                "02": 7,
                "03": 10,
                }


for index, row in df.iterrows():
    if index == 0:
        print("Do nothing")
    else:
        if row['index'][-4:-2] == "MO":
            print(row)
            df_new = df_new.append(
                {'date': pd.to_datetime(df_new['date'][0] + DateOffset(months=int(row['index'][-2:]))),
                 "values": row.values[1]},
                ignore_index=True)
        if row['index'][-4:-2] == "QR":
            df_new = df_new.append(
                {'date': pd.to_datetime(df_new['date'][0] + DateOffset(months=int(map_quarters[row['index'][-2:]]))),
                 "values": row.values[1]},
                ignore_index=True)
        if row['index'][-4:-2] == "YR":
            df_new = df_new.append(
                {'date': pd.to_datetime(df_new['date'][0] + DateOffset(years=int(row['index'][-2:]))),
                 "values": row.values[1]},
                ignore_index=True)

# Resample and fill values in between
df_new = df_new.set_index('date').resample('D').ffill()

print(df_new)