Pandas Data data frame filtering after using pivot function

user_15411249 0
Viewed 70

Can someone please help me with my current error or suggest another way of doing this ? Thanks a lot in advance

I have bellow data frame given bellow :-

GroupName  Id     Type1     Type2      Type 3
ABC         1       100       0            0
            2         0      -100          0 
            3         0       100         100
DEF         4       100       0            0 
            5        0        100          0

I want to filter the data frame such that it returns me all the rows and the GroupName where one or more of the columns (Type1 /Type2/Type3) has +ve and a -ve value .

OUTPUT in the above df it will return be only

GroupName  Id     Type1     Type2      Type 3
ABC         1       100       0            0
            2         0      **-100**          0 
            3         0       100         **100**

I tried

df[df.gt(0).any(axis=1) & df.lt(0).any(axis=1)]

getting error

    if level_codes[key] == -1:
IndexError: index 0 is out of bounds for axis 0 with size 0

I created this df by using a pivot table

df= old_df.pivot_table(index=["GroupName","Id"], columns="Type", values="Value",
                                              aggfunc=np.sum)
pandas python
1 Answers

IIUC:

import numpy as np
import pandas as pd

def posandneg(d):
    return np.in1d([1, -1], np.sign(d)).all()

df.groupby(level=0).filter(posandneg)

             Type1 Type2 Type 3
GroupName Id                   
ABC       1    100     0      0
          2      0  -100      0
          3      0   100    100
​
user_23366540
Related