Pandas merge dataframes on date time index and one other column-CodePudding

How I can merge two data frames by index (date) and also by column (catcode_id)?

df1.head(5)
        catcode_id type    feccandid_amt   amount
date                
2007-12-31  A0000   24K     H6TX08100   1000
2007-12-31  A2000   24K     H8CA52052   500
2007-12-31  H3100   24K     S8AK00090   1000
1985-12-31  J7120   24E     H8OH18088   36
1997-12-31  z9600   24K     S6ND00058   2000
    
    
df2.head(5)
         catcode_id disposition   feccandid_disp  bills
date                
2007-12-31  A0000   support     S4HI00011               1
2007-12-31  A1000   oppose      S4IA00020', 'P20000741  1
2007-12-31  A1000   support     S8MT00010               1
2007-12-31  A1500   support     S6WI00061               2
2007-12-31  z9600   support     S4IA00020', 'P20000741  3

output

        catcode_id type    feccandid_amt   amount  disposition   feccandid_disp  bills
date                
2007-12-31  A0000   24K     H6TX08100   1000     support     S4HI00011               1

CodePudding user response：

You can reset the index before merge

out = (df1.reset_index().merge(df2.reset_index(), on=['catcode_id', 'date'])
       .set_index('date'))

print(out)

           catcode_id type feccandid_amt  amount disposition feccandid_disp  bills
date
2007-12-31      A0000  24K     H6TX08100    1000     support      S4HI00011      1