select element in a column A while column B does not have a value

user_1382763 0
Viewed 76

I want to select products as long as it does not contain 0 in x. Input:

test = pd.DataFrame(
    [
        ['a', 0],
        ['a', 3],
        ['a', 4],
        ['b', 3],
        ['b', 2],
        ['c', 1],
        ['d', 0]
    ]
)

test.columns = ['product', 'x']

test.query("select distinct (product) where x not in (0) ")

expected out come: b,c

How to do this in both pandas and SQL?

pandas sql python
3 Answers

In SQL, you would use:

select product
from t
group by product
having min(x) > 0;

This works assuming x is never negative. A more general formulation is:

having sum(case when x = 0 then 1 else 0 end) = 0
user_11440350

In your case pandas can do with isin

test.loc[~test['product'].isin(test.loc[test.x.eq(0),'product']),'product'].unique()
Out[41]: array(['b', 'c'], dtype=object)

Or do with set

set(test['product'].tolist())-set(test.loc[test.x.eq(0),'product'].tolist())
Out[47]: {'b', 'c'}
user_79645270

If you want to filter your dataframe, you can use groupby with .any():

test[~test.groupby('product')['x'].transform(lambda x: x.eq(0).any())]

Output:

product   x
      b   3
      b   2
      c   1

If you only want to see unique values you can add ['product'].unique().tolist() at the end of the code which I pasted above.

Then we have the output:

['b', 'c']
user_133401430
Related