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

score 1 · Accepted Answer · answered Dec 14 '20 at 07:02

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

andrew_reece · Answer 2 · 2020-12-14T06:11:34.577

0

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.

edited Dec 14 '20 at 06:11

answered Dec 14 '20 at 06:02

andrew_reece

20,390
3
33
58

thanks for the answer but i forgot to mention that there could be the same currency in different user_id. So I need to specifiy the user_id. – delalma Dec 14 '20 at 06:14

pandas operations on part of multiindex dataframe

2 Answers2