compare multiple columns of pandas dataframe with one column

Question

I have a dataframe: df-

    A   B   C   D   E
0   V   10  5   18  20
1   W   9   18  11  13
2   X   8   7   12  5
3   Y   7   9   7   8
4   Z   6   5   3   90

I want to add a column 'Result' which should return 1 if the value in column 'E' is greater than the values in B, C & D columns else return 0.

Output should be:

    A   B   C   D   E   Result
0   V   10  5   18  20  1
1   W   9   18  11  13  0
2   X   8   7   12  5   0
3   Y   7   9   7   8   0
4   Z   6   5   3   90  1

For few columns, i would use logic like : if(and(E>B,E>C,E>D),1,0), But I have to compare around 20 columns (from B to U) with column name 'V'. Additionally, the dataframe has around 100 thousand rows.

I am using

df['Result']=np.where((df.ix[:,1:20])<df['V']).all(1),1,0)

And it gives a Memory error.

I am really curious if my solution is working, can you test it? — jezrael
– jezrael, Commented May 23, 2018 at 10:37

jezrael · Accepted Answer · 2018-05-23 10:23:00Z

11

One possible solution is compare in numpy and last convert boolean mask to ints:

df['Result'] = (df.iloc[:, 1:4].values < df[['E']].values).all(axis=1).astype(int)
print (df)
   A   B   C   D   E  Result
0  V  10   5  18  20       1
1  W   9  18  11  13       0
2  X   8   7  12   5       0
3  Y   7   9   7   8       0
4  Z   6   5   3  90       1

answered May 23, 2018 at 10:23

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

8 Comments

piyush bansal Over a year ago

Thank you so much Jezrael. It worked. I am just wondering why is my code is resulting in memory issue.

jezrael Over a year ago

@piyushbansal - Hard question, mainly because big data :)

jezrael Over a year ago

@panda - use df['Result'] = df[['B','C','D','E']].gt(0).sum(axis=1).astype(int), gt is >, gt

jezrael Over a year ago

@panda - use ge (greater or equal) like df['Result'] = df[['B','C','D','E']].ge(0).sum(axis=1).astype(int)

jezrael Over a year ago

@VictorHugoCalegarideToledo - It means df['E'] has strings, check this for convert column to numeric.

|

Collectives™ on Stack Overflow

compare multiple columns of pandas dataframe with one column

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related