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

Question

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

jezrael · Accepted Answer · 2021-09-14 10:18:01Z

1

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

edited Sep 14, 2021 at 10:18

answered Sep 14, 2021 at 9:59

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

dingaro Over a year ago

unfortunately, code from second cell in your answer does not work, are you take into considerration that IDX is index of pivot table ?

jezrael Over a year ago

@Matop - mixxing integers and floats in one column is problem, if need numeric columns. pandas always convert to flaots :(

dingaro Over a year ago

so maybe we can change all values to str and then drop from str everything after dot ? what do you think ? could you try to do that ?

dingaro Over a year ago

convert to string and then drop all values after dot in each index without index "new1" I think it could work

dingaro Over a year ago

Great, thank you! solution with str works

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related