CODEWITHSUNDEEP
pythonpandasdataframefilter
pythonRcpp
pythonRcpp

Reputation: 2146

group and filter pandas dataframe

OID,TYPE,ResponseType
100,mod,ok
100,mod,ok
101,mod,ok
101,mod,ok
101,mod,ok
101,mod,ok
101,mod,no
102,mod,ok
102,mod,ok2
103,mod,ok
103,mod,no2

I want to remove all OIDs that ever had no or no2 as their response.

I tried:

dfnew = df.groupby('OID').filter(lambda x: ((x['ResponseType']=='no') | x['ResponseType']=='no2')).any() )

But I get SyntaxError: invalid syntax

Another apporach could be to make a set of all OIDs that are to be filtered and then use those to filter the df.The df has 5000000 rows !

Expected OP

OID,TYPE,ResponseType
100,mod,ok
100,mod,ok

102,mod,ok
102,mod,ok2

Upvotes: 3

Views: 1142

Answers (2)

jezrael
jezrael

Reputation: 862831

You need add one ( and ~ for invert booelan mask - but it is really slow:

dfnew = df.groupby('OID').filter(lambda x: ~((x['ResponseType']=='no') | 
                                             (x['ResponseType']=='no2')).any() )
                                          #here

print (dfnew)
   OID TYPE ResponseType
0  100  mod           ok
1  100  mod           ok
7  102  mod           ok
8  102  mod          ok2

Another solution, faster with boolean indexing and double isin:

oids = df.loc[df['ResponseType'].isin(['no','no2']), 'OID']
print (oids)
6     101
10    103
Name: OID, dtype: int64

dfnew = df[~df['OID'].isin(oids)]
print (dfnew)
   OID TYPE ResponseType
0  100  mod           ok
1  100  mod           ok
7  102  mod           ok
8  102  mod          ok2

A bit slowier solution with unique:

oids = df.loc[df['ResponseType'].isin(['no','no2']), 'OID'].unique()
print (oids)
[101 103]

Timings:

np.random.seed(123)
N = 1000000
df = pd.DataFrame({'ResponseType': np.random.choice(['ok','ok2','no2', 'no'], N),
                   'TYPE':['mod'] * N,
                   'OID':np.random.randint(100000, size=N)})
print (df)

In [285]: %timeit (df[~df['OID'].isin(df.loc[df['ResponseType'].isin(['no','no2']), 'OID'])])
10 loops, best of 3: 67.2 ms per loop

In [286]: %timeit (df[~df['OID'].isin(df.loc[df['ResponseType'].isin(['no','no2']), 'OID'].unique())])
10 loops, best of 3: 69.5 ms per loop

#zipa solution
In [287]: %timeit (df[~df['OID'].isin(df[df['ResponseType'].isin(['no', 'no2'])]['OID'])])
10 loops, best of 3: 91.5 ms per loop

#groupby solution :(
In [288]: %timeit (df.groupby('OID').filter(lambda x: ~((x['ResponseType']=='no') |  (x['ResponseType']=='no2')).any() ))
1 loop, best of 3: 1min 54s per loop

Upvotes: 2

zipa
zipa

Reputation: 27879

You can do it like this:

df[~df['OID'].isin(df[df['ResponseType'].isin(['no', 'no2'])]['OID'])]

Upvotes: 0

Related Questions