Skip to content
Advertisement

How to convert each values in Data Frame to int and float in only one index row in Python Pandas?

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
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement