I have Pandas Data Frame in Python like below:
IDX | ALL | COL1 | COL2 ------------------------ ABC | 100 | 50 | 214 DEF | 250 | 32 | 89 GHI | 120 | 18 | 12
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:
(value from index DEF - value from index GHI) and result / by value from index DEF
So for example: (250 - 120) / 250 = 0.52
So as a result I need something like below:
IDX | ALL | COL1 | COL2 ------------------------ ABC | 100 | 50 | 214 DEF | 250 | 32 | 89 GHI | 120 | 18 | 12 new1 | 0.52| 0.44 | 0.87
because:
(250 - 120) / 250 = 0.52 (32 - 18) / 32 = 0.44 (89 - 12) / 89 = 0.87
I used code like below:
df.loc['new1'] = df.lfoc['DEF'].sub(df.floc['GHI']).div(df.loc['DEF'])
Nevertheless, after using above code (which works) values in my DF changed from int to float and looks like below:
IDX | ALL | COL1 | COL2 ------------------------ ABC | 100.00000 | 50.00000 | 214.00000 DEF | 250.00000 | 32.00000 | 89.00000 GHI | 120.00000 | 18.00000 | 12.00000 new1 | 0.52000 | 0.43750 | 0.86516
What can I do so as to acheive DF like below?????:
IDX | ALL | COL1 | COL2 ------------------------ ABC | 100 | 50 | 214 DEF | 250 | 32 | 89 GHI | 120 | 18 | 12 new1 | 52.0| 43.7 | 86.5
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:
df.loc['new1'] = df.loc['DEF'].sub(df.loc['GHI']).div(df.loc['DEF']).mul(100).round(1) print (df) ALL COL1 COL2 IDX ABC 100.0 50.0 214.0 DEF 250.0 32.0 89.0 GHI 120.0 18.0 12.0 new1 52.0 43.8 86.5
Possible solution is transpose – original integers are not changed and new column is filled by floats:
df = df.T df['new1'] = df['DEF'].sub(df['GHI']).div(df['DEF']).mul(100).round(1) print (df) IDX ABC DEF GHI new1 ALL 100 250 120 52.0 COL1 50 32 18 43.8 COL2 214 89 12 86.5
Another idea with formaters g
:
df = df.applymap('{:,g}'.format) print (df) ALL COL1 COL2 IDX ABC 100 50 214 DEF 250 32 89 GHI 120 18 12 new1 52 43.8 86.5
Or converting to strings:
df.loc['new1'] = df.loc['DEF'].sub(df.loc['GHI']).div(df.loc['DEF']).mul(100).round(1).astype(str) df = df.astype(str) print (df) ALL COL1 COL2 IDX ABC 100 50 214 DEF 250 32 89 GHI 120 18 12 new1 52.0 43.8 86.5