CODEWITHSUNDEEP
pythonpandas
math_guy_shy
math_guy_shy

Reputation: 181

How to filter id and name of who does not have a specific value in a pandas column

I have this dataframe:

id      name       update
1      andre       torch2
2      paulo       stock_opt
3      Manuela     length
1      andre       stock
3      Manuela     torch2

how can I query the id and name of who does not have torch2 on update column?

the output should be:

id      name       update  
2      paulo       stock_opt

How can I do this?

Upvotes: 0

Views: 33

Answers (1)

jezrael
jezrael

Reputation: 863166

Get all groups by id and name if torch2 not exist by compare with Series.ne and test by GroupBy.transform with GroupBy.all and filter by boolean indexing:

df1 = df[df['update'].ne('torch2').groupby([df['id'],df['name']]).transform('all')]
print (df1)
   id   name     update
1   2  paulo  stock_opt

Or create MultiIndex by DataFrame.set_index, test if equal torch2 and filter out indices in Index.isin:

s = df.set_index(['id','name'])['update']
df1 = df[~s.index.isin(s.index[s.eq('torch2')])]
print (df1)
   id   name     update
1   2  paulo  stock_opt

For opposite compare by equal and test if at least one match by GroupBy.any:

df2 = df[df['update'].eq('torch2').groupby([df['id'],df['name']]).transform('any')]
print (df2)
   id     name  update
0   1    andre  torch2
2   3  Manuela  length
3   1    andre   stock
4   3  Manuela  torch2

Or remove ~ for invert mask:

s = df.set_index(['id','name'])['update']
df2 = df[s.index.isin(s.index[s.eq('torch2')])]
print (df2)
   id     name  update
0   1    andre  torch2
2   3  Manuela  length
3   1    andre   stock
4   3  Manuela  torch2

Upvotes: 1

Related Questions