CODEWITHSUNDEEP
pythonpandasdataframe
ds882
ds882

Reputation: 105

Compare one column against two other columns and assign the result back to the DataFrame

Print below code

import pandas as pd

df = pd.DataFrame()

df['A'] = (10,20,34,13,45,2,34,1,18,19,23,9,40,33,17,6,15)

df['B'] = (14,26,23,41,12,24,31,1,9,53,4,22,16,19,16,28,13)

print(df)

  1. I would like to add a column that returns 'TRUE' or 'FALSE' if each number in column A is greater than the number that is 5 rows below. Obviously the last 4 numbers can't be compared and as such these can be regarded as 'IGNORE'

  2. I would like to add a second column that returns 'TRUE' or 'FALSE' if each in column A number is greater than the number 5 rows below in column B. Obviously the last 4 numbers can't be compared and as such these can be regarded as 'IGNORE' as well

Upvotes: 1

Views: 65

Answers (2)

BENY
BENY

Reputation: 323226

You can check with np.where 

s=np.where(df.A.shift(-5).isna(),'ignore',df.A>df.A.shift(-5))
s
Out[90]: 
array(['True', 'False', 'True', 'False', 'True', 'False', 'True', 'False',
       'False', 'True', 'True', 'False', 'ignore', 'ignore', 'ignore',
       'ignore', 'ignore'], dtype='<U6'

t=np.where(df.B.shift(-5).isna(),'ignore',df.A>df.B.shift(-5))

df['col1'],df['col2']=s,t

Or as cs95 mentioned we can do it by using mask and only apply the condition once . 

s=df.shift(-5).ge(df.A,0).mask(df.A.shift(-5).isna(),'ignore')
s.columns=['col1','col2']
df=pd.concat([df,s],axis=1)

Upvotes: 3

cs95
cs95

Reputation: 402353

You can shift "A" and "B" up by 5 rows using shift, then compare each of the shifted values against "A" together.

# shift up rows
s = df[['A', 'B']].shift(-5)

# compare against "A" and mask NaNs 
m = s.lt(df['A'], axis=0).mask(s.isna())  

# create and concatenate the result
df2 = pd.DataFrame(
        np.select([m == 1, m == 0], ['TRUE', 'FALSE'], default='IGNORE'),
        columns=['C', 'D'],   
        index=df.index)    
pd.concat([df, df2], axis=1)

     A   B       C       D
0   10  14    TRUE   FALSE
1   20  26   FALSE   FALSE
2   34  23    TRUE    TRUE
3   13  41   FALSE    TRUE
4   45  12    TRUE   FALSE
5    2  24   FALSE   FALSE
6   34  31    TRUE    TRUE
7    1   1   FALSE   FALSE
8   18   9   FALSE   FALSE
9   19  53    TRUE    TRUE
10  23   4    TRUE   FALSE
11   9  22   FALSE   FALSE
12  40  16  IGNORE  IGNORE
13  33  19  IGNORE  IGNORE
14  17  16  IGNORE  IGNORE
15   6  28  IGNORE  IGNORE
16  15  13  IGNORE  IGNORE

Upvotes: 2

Related Questions