Dataframe-Compare list of columns' values against one column's value with multiple criteria - vectorized

user_12137722 0
Viewed 39

I have the below dataframe:

Com1       a       b       c        d       e      f       g      
0.83389 0.83585 0.8351  0.83493 0.83683 0.83697 0.83622 0.83573    
0.83375 0.83585 0.8351  0.83493 0.83683 0.83487 0.83357 0.83147    
0.83366 0.83585 0.8351  0.83493 0.83683 0.83567 0.83487 0.83357    
0.834   0.83585 0.8351  0.83493 0.83567 0.83147 0.82807 0.82257

What I need is to compare the letter columns to the Com1 column. In plain language, the formula is:

ColList = [a,b,c,d,e,f,g]
if any number in ColList is > Com1 and < (Com1 +0.001):
    Result = 0
else:
    Result = 1

The desired result is:

Com1       a       b       c        d       e      f       g         Result
0.83389 0.83585 0.8351  [0.83473]   0.83683 0.83697 0.83622 0.83573    0
0.83375 0.83585 0.8351  0.83493 0.83683 0.83487 0.83357 0.83147        1
0.83366 0.83585 0.8351  0.83493 0.83683 0.83567 0.83487 0.83357        1
0.834   0.83585 0.8351  [0.83493]   0.83567 0.83147 0.82807 0.82257    0

(I bracketed the numbers that are causing the "0" result)

I tried to use np.where, but it did not yield the correct results when I manually compared in excel. The formula I tried was:

ColList = [a,b,c,d,e,f,g]
df['Result'] = np.where( (df[ColList ].gt(df['Com1'],axis=0).any(axis=1)) & (df[ColList ].lt(df['Com1'] + 0.001,axis=0).any(axis=1)),0,1)

I also considered using the between() function, but from my research, since its a list of columns, I am not having a series and it cannot be used.

I appreciate the assistance.

pandas numpy python
2 Answers

You can use apply with axis=1 to work on the rows with the help of a function:

def between(s: pd.Series):
    ColList = ['a', 'b', 'c', 'd', 'e', 'f', 'g']
    return int(not any(s[ColList].between(s['Com1'], s['Com1']+0.001)))


df['result'] = df.apply(between, axis=1)

If you want you could also have the column list and column to compare to as argument and use partial before applying it.

user_148262510

For vectorized operations, you could use gt and lt methods to get boolean frames and combine them using & operator and combine across rows using any:

cols = df.drop(columns='Com1')
out = (1-(cols.gt(df['Com1'], axis=0) & 
       cols.lt(df['Com1']+0.001, axis=0))
       .any(axis=1).astype(int))

Output:

0    1
1    1
2    1
3    0
dtype: int64

Note that the output I get here is different from the one you get because the input given is different from the input used to derive the desired output.

Admin1
Related