CODEWITHSUNDEEP
pythonpandas
Singapore 123
Singapore 123

Reputation: 95

Creating new column in Pandas with a condition based on existing row values and returning another row's values

would like some help with the following problem. I currently have a panda dataframe with 3 columns - test1, test2, test3

What I hope to achieve is result in the result_column, where the logic will be:

1) If value in test1 AND test2 > 0, then return value of test3

2) Else If value test1 AND test2 < 0, then return NEGATIVE value of test3

3) Otherwise return 0

  test1  test2  test3  result_column
0    0.5    0.1   1.25    1.25
1    0.2   -0.2   0.22       0
2   -0.3   -0.2   1.12   -1.12
3    0.4   -0.3   0.34       0
4    0.5      0   0.45       0

This is my first time posting a question on python and pandas. Apologies in advance if the formatting here is not optimum. Appreciate any help I can get!

Upvotes: 4

Views: 124

Answers (2)

piRSquared
piRSquared

Reputation: 294348

Clever use of np.sign and logic
If both > 0 or both < 0 then product is 1, otherwise product is -1 or 0. If both > 0 then sign of sum is 1. If both < 0 then sign of sum is -1, else 0. The product of these things is exactly what we want.

v = np.sign(df[['test1', 'test2']].values)
df.assign(result_column=v.prod(1) * np.sign(v.sum(1)) * df.test3 + 0)

   test1  test2  test3  result_column
0    0.5    0.1   1.25           1.25
1    0.2   -0.2   0.22           0.00
2   -0.3   -0.2   1.12          -1.12
3    0.4   -0.3   0.34           0.00
4    0.5    0.0   0.45           0.00

Upvotes: 3

jezrael
jezrael

Reputation: 862851

I think need numpy.select with conditions chained by & (AND) or select all tested columns by subset [[]], compare ant test by DataFrame.all:

m1 = (df.test1 > 0) & (df.test2 > 0)
#alternative
#m1 = (df[['test1', 'test2']] > 0).all(axis=1)

m2 = (df.test1 < 0) & (df.test2 < 0)
#alternative
#m2 = (df[['test1', 'test2']] < 0).all(axis=1)

df['result_column'] = np.select([m1,m2], [df.test3, -df.test3], default=0)
print (df)
   test1  test2  test3  result_column
0    0.5    0.1   1.25           1.25
1    0.2   -0.2   0.22           0.00
2   -0.3   -0.2   1.12          -1.12
3    0.4   -0.3   0.34           0.00
4    0.5    0.0   0.45           0.00

Upvotes: 4

Related Questions