Python Dataframe: To get a column value from 2nd dataframe based on a column in the 1st dataframe is in between two columns in the 2nd dataframe

userbH8m1 0
Viewed 14

There are two dataframes. df1 with 2 columns code and date

enter image description here

df2 with 4 columns code, start date, end date and year

enter image description here

How to add the year from df2 based on the date in the df1 is in between start and end date in the df2.

The required output is as

enter image description here

merge dataframe pandas python
1 Answers

You will want to modify df2 so that it has a new row for every day between the start and end ranges of every code/year.

You could do this by creating a daily date range list for every row, and exploding that into many rows. Then you can simply merge the two together and get the results you want.

Here is an small example:

import pandas as pd
df = pd.DataFrame({'Code':[101],'weekEndingDate':['2022-07-01']})
df2 = pd.DataFrame({'Code':[101, 101], 'start':['2022-06-01', '2021-06-01'],'end':['2023-05-01', '2022-05-31'],'year':[2023, 2022]})


df['weekEndingDate'] = pd.to_datetime(df['weekEndingDate'])
df2['range'] = df2.apply(lambda x: pd.date_range(x.start,x.end, freq='D'), axis=1)
df2 = df2.explode('range')

df = df.merge(df2[['range','year']], left_on='weekEndingDate', right_on='range').drop(columns='range')

print(df)

Output

   Code weekEndingDate  year
0   101     2022-07-01  2023
user2uNj0
Related