check for value in list of Pandas data frame columns

Question

I have a pandas dataframe data that looks like this

    MED1    MED2    MED3    MED4    MED5
0   60735   24355   33843   16475   9995
1   10126   5789    17165   90000   90000
2   5789    19675   30553   90000   90000
3   60735   17865   34495   90000   90000
4   19675   5810    90000   90000   90000

I want to create a new bool column "med" that has True/False based on 60735 in the columns MED1...MED5 I am trying this and am not sure how to make it work.

DF['med'] = (60735 in [DF['MED1'], DF['MED2']])

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()

MED1..MED5 represent drugs being taken by a patient at a hospital visit. I have a list of about 20 drugs for which I need to know if the patien was taking them. Each drug is coded with a number but has a name. A nice solution would look something like (below) but how do I do this with pandas.

drugs = {'drug1':60735, 'drug2':5789}  
for n in drugs.keys():
    DF[n] = drugs[n] in DF[['MED1', 'MED2', 'MED3', 'MED4', 'MED5']]

Sorry, I am confused. You want to have True when MED1 = 60735 and False otherwise? — Patrick the Cat
– Patrick the Cat, Commented Jul 29, 2014 at 22:02

chrisb · Accepted Answer · 2014-07-30 10:33:32Z

4

@Mai's answer will of course work - it may be a bit more standard to write it like this, with the | operator.

df['med'] = (df['MED1'] == 60735) | (df['MED1'] == 60735)

If you want to check for a value in all (or many) columns, you could also use isin as below. The isin checks whether the value in the list is in each cell, and the any(1) returns True if any element in each row is True.

df['med'] = df.isin([60735]).any(1)

Edit: Based on your edited question, would this work?

for n in drugs:
    df[n] = df[['MED1','MED2','MED3','MED4','MED5']].isin([drugs[n]]).any(1)

edited Jul 30, 2014 at 10:33

answered Jul 29, 2014 at 23:36

chrisb

52.7k8 gold badges73 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vincent Over a year ago

Is there no way to slice the row based on column names and get the values? I have added to the question to better explain what I am trying to do.

Patrick the Cat · Accepted Answer · 2014-07-29 22:19:36Z

0

I am still confused. But part of what you want may be this:

import numpy as np
DF['med'] = np.logical_or(DF['MED1'] == 60735, DF['MED2'] == 60735)

answered Jul 29, 2014 at 22:19

Patrick the Cat

2,1781 gold badge17 silver badges35 bronze badges

Comments

cheekybastard · Accepted Answer · 2014-07-30 02:42:37Z

0

Here are a few %timeit comparisons of some methods to return bools from a dataframe column.

In [2]: %timeit df['med'] = [bool(x) if int(60735) in x else False for x in enumerate(df['MED1'])]
1000 loops, best of 3: 379 µs per loop

In [3]: %timeit df['med'] = (df['MED1'] == 60735)
1000 loops, best of 3: 649 µs per loop

In [4]: %timeit df['med'] = df['MED1'].isin([60735])
1000 loops, best of 3: 404 µs per loop

answered Jul 30, 2014 at 2:42

cheekybastard

5,7653 gold badges25 silver badges26 bronze badges

Collectives™ on Stack Overflow

check for value in list of Pandas data frame columns

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related