Locate the Upcoming Expiry date and Assign the Value based on it - Python Data frame

user_11239328 0
Viewed 123

There are two dataframes, need to extract the Nearest upcoming Expiry date from Dataframe2 based on Active date in Dataframe 1 to obtain the correct Value.

This is a sample. Original data contains thousands of rows

Dataframe 1

df_1 = pd.DataFrame({'Category': ['A','B'],
               'Active date': ['2021-06-20','2021-06-25']})

Dataframe 1

Dataframe 2

df_2 = pd.DataFrame({'Category': ['A','A','A','A','A','B','B','B'],
               'Expiry date': ['2021-05-22','2021-06-23','2021-06-24','2021-06-28','2021-07-26','2021-06-27','2021-06-28','2021-08-29'],
                'Value': [20,21,23,45,12,34,17,34]})

enter image description here

Final Output -

enter image description here

The code I was trying -

df = pd.merge(df_1, df_2, on='Category', how='inner')
#Removed all the dates which are less than Active date
df = df.loc[(df_1['Active Date'] <= df_2['Expiry Date'])]
jupyter-notebook pandas merge python
2 Answers

I believe this solution keeps a lot of your existing code and will accomplish what you are looking for.

df_1 = pd.DataFrame({'Category': ['A','B'],
               'Active date': ['2021-06-20','2021-06-25']})

df_2 = pd.DataFrame({'Category': ['A','A','A','A','A','B','B','B'],
               'Expiry date': ['2021-05-22','2021-06-23','2021-06-24','2021-06-28','2021-07-26','2021-06-27','2021-06-28','2021-08-29'],
                'Value': [20,21,23,45,12,34,17,34]})

df = pd.merge(df_1, df_2, on='Category', how='inner')

# Removed all the dates which are less than Active date
df = df.loc[(df['Active date'] <= df['Expiry date'])]
df = df.rename(columns={'Expiry date': 'Next Expiry Date'})
df = df.loc[df['Next Expiry Date'] == df.groupby('Category')['Next Expiry Date'].transform('min')]

Output:

  Category Active date Next Expiry Date  Value
1        A  2021-06-20       2021-06-23     21
5        B  2021-06-25       2021-06-27     34
user_150258090

You can use pandas merge_asof with direction set to forward. Note that for merge_asof, both data frames must be sorted :

 df_1 = df_1.transform(pd.to_datetime, errors='ignore')
 df_2 = df_2.astype({"Expiry date": np.datetime64})
 df_2 = df_2.sort_values('Expiry date')
 pd.merge_asof(df_1, 
               df_2, 
               left_on='Active date', 
               right_on='Expiry date', 
               direction='forward',
               by='Category')


        Category Active date Expiry date  Value
0        A       2021-06-20  2021-06-23     21
1        B       2021-06-25  2021-06-27     34
user_71757130
Related