I have Pandas Data Frame in Python like below:
JavaScript
x
6
1
IDX | ALL | COL1 | COL2
2
------------------------
3
ABC | 100 | 50 | 214
4
DEF | 250 | 32 | 89
5
GHI | 120 | 18 | 12
6
IDX is and index of this Data Frame. And I would like to add new row in this Data Frame which will calculate mathematic formula like:
JavaScript
1
2
1
(value from index DEF - value from index GHI) and result / by value from index DEF
2
So for example: (250 - 120) / 250 = 0.52
So as a result I need something like below:
JavaScript
1
7
1
IDX | ALL | COL1 | COL2
2
------------------------
3
ABC | 100 | 50 | 214
4
DEF | 250 | 32 | 89
5
GHI | 120 | 18 | 12
6
new1 | 0.52| 0.44 | 0.87
7
because:
JavaScript
1
4
1
(250 - 120) / 250 = 0.52
2
(32 - 18) / 32 = 0.44
3
(89 - 12) / 89 = 0.87
4
I used code like below:
JavaScript
1
2
1
df.loc['new1'] = df.lfoc['DEF'].sub(df.floc['GHI']).div(df.loc['DEF'])
2
Nevertheless, after using above code (which works) values in my DF changed from int to float and looks like below:
JavaScript
1
7
1
IDX | ALL | COL1 | COL2
2
------------------------
3
ABC | 100.00000 | 50.00000 | 214.00000
4
DEF | 250.00000 | 32.00000 | 89.00000
5
GHI | 120.00000 | 18.00000 | 12.00000
6
new1 | 0.52000 | 0.43750 | 0.86516
7
What can I do so as to acheive DF like below?????:
JavaScript
1
7
1
IDX | ALL | COL1 | COL2
2
------------------------
3
ABC | 100 | 50 | 214
4
DEF | 250 | 32 | 89
5
GHI | 120 | 18 | 12
6
new1 | 52.0| 43.7 | 86.5
7
Advertisement
Answer
There are same types of each column in pandas by default, so close is multiple and divide, but still there are floats columns:
JavaScript
1
9
1
df.loc['new1'] = df.loc['DEF'].sub(df.loc['GHI']).div(df.loc['DEF']).mul(100).round(1)
2
print (df)
3
ALL COL1 COL2
4
IDX
5
ABC 100.0 50.0 214.0
6
DEF 250.0 32.0 89.0
7
GHI 120.0 18.0 12.0
8
new1 52.0 43.8 86.5
9
Possible solution is transpose – original integers are not changed and new column is filled by floats:
JavaScript
1
8
1
df = df.T
2
df['new1'] = df['DEF'].sub(df['GHI']).div(df['DEF']).mul(100).round(1)
3
print (df)
4
IDX ABC DEF GHI new1
5
ALL 100 250 120 52.0
6
COL1 50 32 18 43.8
7
COL2 214 89 12 86.5
8
Another idea with formaters g
:
JavaScript
1
9
1
df = df.applymap('{:,g}'.format)
2
print (df)
3
ALL COL1 COL2
4
IDX
5
ABC 100 50 214
6
DEF 250 32 89
7
GHI 120 18 12
8
new1 52 43.8 86.5
9
Or converting to strings:
JavaScript
1
11
11
1
df.loc['new1'] = df.loc['DEF'].sub(df.loc['GHI']).div(df.loc['DEF']).mul(100).round(1).astype(str)
2
df = df.astype(str)
3
4
print (df)
5
ALL COL1 COL2
6
IDX
7
ABC 100 50 214
8
DEF 250 32 89
9
GHI 120 18 12
10
new1 52.0 43.8 86.5
11