change sign of columns based on value of another column multi-level dataframe

user_18031628 0
Viewed 35

I am trying to change sign of columns(X,V) based on sign of C for multi-level dataframe with 0-62 columns. With condition do nothing if V is already -ve.

Dataframe sample

                0             1      
A         X  V  C       X  V  C      
0  foo  1.2  1 -2     1.3  2  4      
1  bar  4.1  4  6     1.2 -5 -1

What I am trying to achieve is change sign of C and D for all columns if E is -ve

                0             1      
A         X  V  C       X  V  C      
0  foo -1.2 -1 -2     1.3  2  4      
1  bar  4.1  4  6    -1.2 -5 -1

I know how to change sign for one column for simple df but can't do it for multi-level df with all columns.

df['V'] = df['V'].apply(lambda df['V']: if df['C'] >= 0 else df['C']*-1)

I have the code as below for V only but it gives the error,

df['V'] = (
    df.loc[:,(slice(None),'V')]
      .apply(lambda df.loc[:,(slice(None),'V')].iloc[:, 2:62]: 
          if df.loc[:,(slice(None),'C')].iloc[:, 2:62][out.loc[:,(slice(None),'C')] >= 0]  
            else df.loc[:,(slice(None),'V')].iloc[:, 2:62]*-1)
)

    .apply(lambda df.loc[:,(slice(None),'V')].iloc[:, 2:62]:
                     ^
SyntaxError: invalid syntax
pandas dataframe python
1 Answers

Let the input be:

df = pd.DataFrame({(0, 'X'): [-1.2, 4.1],
                   (0, 'V'): [-1, 4],
                   (0, 'C'): [-2, 6],
                   (1, 'X'): [1.3, -1.2],
                   (1, 'V'): [2, -5],
                   (1, 'C'): [4, -1]},
                  index=pd.MultiIndex.from_arrays([(0, 1), ('foo', 'bar')])
                  )


         0          1      
         X  V  C    X  V  C
0 foo -1.2 -1 -2  1.3  2  4
1 bar  4.1  4  6 -1.2 -5 -1

The general logic would be to determine the sign of column C per "group", then apply it to the absolute values of the original data.

The tricky thing is to work with the multiindex.

I see two options.

option 1: reindexing

import numpy as np

SIGN = np.sign(df.xs('C', axis=1, level=1, drop_level=False))
abs(df)*SIGN.reindex(columns=df.columns).bfill(axis=1)

output:

         0              1          
         X    V    C    X    V    C
0 foo -1.2 -1.0 -2.0  1.3  2.0  4.0
1 bar  4.1  4.0  6.0 -1.2 -5.0 -1.0

SIGN:

       0  1
       C  C
0 foo -1  1
1 bar  1 -1

option 2: stacking/unstacking

import numpy as np

(abs(df).stack(0)
 .multiply(np.sign(df.xs('C', axis=1, level=1)).stack(), axis=0)
 .unstack(-1).swaplevel(axis=1).sort_index(axis=1, level=0, sort_remaining=False)
)

output:

       0          1        
       C  V    X  C  V    X
0 foo -2 -1 -1.2  4  2  1.3
1 bar  6  4  4.1 -1 -5 -1.2
user_163434640
Related