CODEWITHSUNDEEP
pythonpandas
xunitc
xunitc

Reputation: 217

In python with pandas, how to add a column with other columns compare

now I have a dataframe like:

A,B
1,2
4,3
5,5

and I want add a column C, if A>B then C=1, if A < B then C=-1, if A=B then C=0

Now I use for loop to do that, but I do not think that is a right way. What is the right way please?

Upvotes: 0

Views: 70

Answers (4)

cs95
cs95

Reputation: 402553

I'd recommend sub + clip:

df['C'] = (df.A - df.B).clip(-1, 1)
df

   A  B  C
0  1  2 -1
1  4  3  1
2  5  5  0

If your columns are floats, add an astype call before clipping.

df['C'] = (df.A - df.B).astype(int).clip(-1, 1)

Upvotes: 0

Alexander
Alexander

Reputation: 109546

You can use np.where to assign a new column C equal to a value of 1 if A is greater than B otherwise a a value of zero. Then use .loc to check where A and B are equal, where you then assign column C with a value of zero.

df = df.assign(C=np.where(df['A'].gt(df['B']), 1, -1))
df.loc[df['A'] == df['B'], 'C'] = 0

>>> df
   A  B  C
0  1  2 -1
1  4  3  1
2  5  5  0

Upvotes: 0

Mohamed Ali JAMAOUI
Mohamed Ali JAMAOUI

Reputation: 14689

Alongside the other nice answers, a simple step by step translation in pandas would look like this: 

In [77]: df
Out[77]: 
   A  B
0  1  2
1  4  3
2  5  5

In [78]: df['C'] = 0

In [79]: m1 = df.A < df.B

In [80]: m2 = df.A > df.B

In [81]: df.loc[m1, 'C'] = 1

In [82]: df.loc[m2, 'C'] = -1

In [83]: df
Out[83]: 
   A  B  C
0  1  2  1
1  4  3 -1
2  5  5  0

Upvotes: 1

jezrael
jezrael

Reputation: 862731

m1 = df.A > df.B
m2 = df.A < df.B

df['C'] = np.select([m1,m2], [1,-1], default=0)

But nicer is ayhan solution from comment:

df['C'] = np.sign(df['A'] - df['B'])

print (df)
   A  B  C
0  1  2 -1
1  4  3  1
2  5  5  0

Upvotes: 0

Related Questions