MergeError: right can only have one index-CodePudding

I have a large dataframe df (applicationstartdate as a index) and a series app_3M, I want to merge together.

The series looks like:

New_ID  applicationstartdate
1001    2021-02-28              0.0
1003    2021-01-31              0.0
        2021-02-28              2.0
        2021-03-31              2.0
        2021-04-30              1.0
Name: N_App_3M, dtype: float64

I want to merge the last (numerical) column to df on New_ID and applicationstartdate.

I tried:

df = pd.merge_asof(df, app_3M, direction="nearest", left_index=True, right_index=True)

which resulted in:

MergeError: right can only have one index

Help would be appreciated.

CodePudding user response：

Convert first level to column and if need also merge by New_ID add parameter by='New_ID' to merge_asof, for sorting add Series.sort_index:

df = pd.merge_asof(df, 
                   app_3M.reset_index(level=0).sort_index(), 
                   direction="nearest", 
                   left_index=True, 
                   right_index=True, 
                   by='New_ID')