How to merge dataframes on the next highest key value-CodePudding

I have df1:

and df2

My goal would be that the key a from df1 merges onto the next highest key in df2 e.g. 2013->2014, 2014->2017 and 2015->2017. So even if both keys are equal I'd like to merge on the next highest, would that be possible?

CodePudding user response：

Use merge_asof with direction='forward' and parameter allow_exact_matches=False:

df = pd.merge_asof(df1, df2.rename(columns={'a':'a1'}),
                   left_on='a', 
                   right_on='a1',
                   direction='forward',
                   allow_exact_matches=False)
print (df)
      a    a1
0  2013  2014
1  2014  2017
2  2015  2017