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)
Advertisement
Answer
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