pandas operations on part of multiindex dataframe

Question

Any help to solve the following problem would be appreciated.

I have the following df1:

                    2020-12-13
user_id currency              
4       1WO       1
        ADH       23
        ALX       5
12223   AMLT      7
        ANCT      9
        ARE       1

df2:

created_at    2020-12-13
currency                
1WO         1    
ALX         1    

I do the following code:

df1.loc[4] = df1.loc[4].sub(df2, axis=1, level='currency', fill_value=0)

output:

                    2020-12-13
user_id currency              
4       1WO       nan
        ADH       nan
        ALX       nan
12223   AMLT      7
        ANCT      9
        ARE       1

output desired

                    2020-12-13
user_id currency              
4       1WO       0
        ADH       23
        ALX       4
12223   AMLT      7
        ANCT      9
        ARE       1

Answer 1

You can use double [] for DataFrame with MultiIndex:

print (df1.loc[[4]].sub(df2, axis=1, level='currency', fill_value=0))
                  2020-12-13
user_id currency            
4       1WO              0.0
        ADH             23.0
        ALX              4.0


df1.loc[[4]] = df1.loc[[4]].sub(df2, axis=1, level='currency', fill_value=0)
print (df1)
                  2020-12-13
user_id currency            
4       1WO              0.0
        ADH             23.0
        ALX              4.0
12223   AMLT             7.0
        ANCT             9.0
        ARE              1.0

Answer 2

Use sub on the entire df1 instead of just a slice from the MultiIndex:

df1.sub(df2, level=1, fill_value=0)

                  2020-12-13
user_id currency            
4.0     1WO              0.0
        ADH             23.0
        ALX              4.0
12223.0 AMLT             7.0
        ANCT             9.0
        ARE              1.0

Note: I used @MaxU's great read_clipboard_mi() to import OP's MultiIndex data frames by copy/paste.