I need to assign the correct fiscal period to each transaction based on a separate table.

I have two datasets.

Dataset 1 has transaction information as below:

Dataset 2 is the table listing the fiscal periods for each start/end date.

Expected result:

Could someone please help me with how I could accomplish this using Python/Pandas? The only way I know how to handle something similar is by using the pd.merge function. However, since the "key" value is a range (start/end dates), I have no clue on how to go about it. Thank you!

CodePudding user response：

This does the job,

df1["Transaction Date"] = pd.to_datetime(df1["Transaction Date"])
df2 = df2.apply(pd.to_datetime)

transaction_date = df1.loc[1, "Transaction Date"]
fiscal_periods_indices = []

for transaction_date in df1["Transaction Date"]:
  fiscal_period = df2[(transaction_date >= df2["Start Date"]) & (transaction_date <= df2["End Date"])].index[0]
  fiscal_periods_indices.append(fiscal_period)

df1["Fiscal Period"] = df2.loc[fiscal_periods_indices, "Fiscal Period"].reset_index(drop = True)

Output -

CodePudding user response：

Since your intervals do not overlap, you can use pd.cut:

bins = pd.to_datetime(df2['Start Date'].to_list()   [df2['End Date'].iloc[-1]])

df1['Fiscal Period'] = pd.cut(pd.to_datetime(df1['Transaction Date']), bins=bins,
                              labels=df2['Fiscal Period'], include_lowest=True)

Output: