CODEWITHSUNDEEP
pythonpandasdataframe
user248237
user248237

Reputation:

Making new column in pandas DataFrame based on filter

Given this DataFrame:

df = pandas.DataFrame({"a": [1,10,20,3,10], "b": [50,60,55,0,0], "c": [1,30,1,0,0]})

What is the best way to make a new column, "filter" that has value "pass" if the values at columns a and b are both greater than x and value "fail" otherwise?

It can be done by iterating through rows but it's inefficient and inelegant:

c = []

for x, v in df.iterrows():
     if v["a"] >= 20 and v["b"] >= 20:
         c.append("pass")
     else:
         c.append("fail")

df["filter"] = c

Upvotes: 2

Views: 12477

Answers (1)

Alex Riley
Alex Riley

Reputation: 176730

One way would be to create a column of boolean values like this:

>>> df['filter'] = (df['a'] >= 20) & (df['b'] >= 20)
    a   b   c filter
0   1  50   1  False
1  10  60  30  False
2  20  55   1   True
3   3   0   0  False
4  10   0   0  False

You can then change the boolean values to 'pass' or 'fail' using replace:

>>> df['filter'].astype(object).replace({False: 'fail', True: 'pass'})
0    fail
1    fail
2    pass
3    fail
4    fail

You can extend this to more columns using all. For example, to find rows across the columns with entries greater than 0:

>>> cols = ['a', 'b', 'c'] # a list of columns to test
>>> df[cols] > 0 
      a      b      c
0  True   True   True
1  True   True   True
2  True   True   True
3  True  False  False
4  True  False  False

Using all across axis 1 of this DataFrame creates the new column:

>>> (df[cols] > 0).all(axis=1)
0     True
1     True
2     True
3    False
4    False
dtype: bool

Upvotes: 12

Related Questions