Using numpy and pandas how to calculate percentage and use criteria and give negative sign
I have data like this:
A B
25 50
25 25
50 25
75 100
80 100
100 80
I want to calculate the percentage, for column B, how much% is greater than A. So,% = (BA) * 100,
But, when B is less than A, I want to display a non-negative sign (e.g. -50%). Because I only want B, how many%> A.
If B is less than A, then it will show 0 or - negative sign percentages.
For example: A = 50, B = 25, then (B / A) * 100 = -50% or 0%
Any idea?
+3
user7366076
source
to share
2 answers
You can use Series.mask
either numpy.where
:
df['C'] = (df['B'] / df['A']) * 100
df['D'] = df['C'].mask((df['B'] < df['A']), df['C'] * -1)
print (df)
A B C D
0 25 50 200.000000 200.000000
1 25 25 100.000000 100.000000
2 50 25 50.000000 -50.000000
3 75 100 133.333333 133.333333
4 80 100 125.000000 125.000000
5 100 80 80.000000 -80.000000
df['C'] = df['B'].div(df['A']).mul(100)
df['D'] = df['C'].mask((df['B'] < df['A']), df['C'].mul(-1))
print (df)
A B C D
0 25 50 200.000000 200.000000
1 25 25 100.000000 100.000000
2 50 25 50.000000 -50.000000
3 75 100 133.333333 133.333333
4 80 100 125.000000 125.000000
5 100 80 80.000000 -80.000000
df['D'] = np.where((df['B'] < df['A']), df['C'] * -1, df['C'])
print (df)
A B C D
0 25 50 200.000000 200.000000
1 25 25 100.000000 100.000000
2 50 25 50.000000 -50.000000
3 75 100 133.333333 133.333333
4 80 100 125.000000 125.000000
5 100 80 80.000000 -80.000000
+3
source to share
Using eval
with some logic
df.eval('C = ((B >= A) * 2 - 1) * B / A * 100', inplace=False)
A B C
0 25 50 200.000000
1 25 25 100.000000
2 50 25 -50.000000
3 75 100 133.333333
4 80 100 125.000000
5 100 80 -80.000000
Without eval
df.assign(C=((df.B >= df.A) * 2 - 1) * df.B / df.A * 100)
A B C
0 25 50 200.000000
1 25 25 100.000000
2 50 25 -50.000000
3 75 100 133.333333
4 80 100 125.000000
5 100 80 -80.000000
FROM numpy
B = df.B.values
A = df.A.values
df.assign(C=((B >= A) * 2 - 1) * B / A * 100)
A B C
0 25 50 200.000000
1 25 25 100.000000
2 50 25 -50.000000
3 75 100 133.333333
4 80 100 125.000000
5 100 80 -80.000000
+1
source to share