Pandas convert float to int if decimals are 0

Question

I have a pandas dataframe, in which some columns have numeric values while others don't, as shown below:

City          a     b       c
Detroit       129   0.54    2,118.00
East          188   0.79    4,624.4712
Houston       154   0.65    3,492.1422
Los Angeles   266   1.00    7,426.00
Miami         26    0.11    792.18
MidWest       56    0.24    772.7813

I want to round off these numeric values to 2 decimal places, for which I am using:

df = df.replace(np.nan, '', regex=True)

After which df becomes:

City          a       b       c
Detroit       129.0  0.54   2,118.0
East          188.0  0.79   4,624.47
Houston       154.0  0.65   3,492.14
Los Angeles   266.0  1.0    7,426.0
Miami         26.0   0.11   792.18
MidWest       56.0   0.24   772.78

It works mostly fine, but it also converts proper integers to decimals, i.e., values like 100 are rounded off to 100.0. I want the dataframe like this:

City          a       b         c
Detroit       129    0.54      2,118
East          188    0.79      4,624.47
Houston       154    0.65      3,492.14
Los Angeles   266    1         7,426
Miami         26     0.11      792.18
MidWest       56     0.24      772.28

I want to keep such values as proper integers itself, while rounding off others to 2 decimal places in all the numeric columns. How can I do that?

See this answer: https://stackoverflow.com/questions/27362234/mixed-types-of-elements-in-dataframes-column — Dan, Sep 30 '19 at 11:27
So original data are strings with `,` for thousands? Or original are floats? — jezrael, Sep 30 '19 at 11:42
@jezrael originally data was float but I added commas using df.update(df.select_dtypes(include=np.number).applymap('{:,}'.format)) — Ank, Sep 30 '19 at 11:43

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

Use g format:

General format. For a given precision p >= 1, this rounds the number to p significant digits and then formats the result in either fixed-point format or in scientific notation, depending on its magnitude.

The precise rules are as follows: suppose that the result formatted with presentation type 'e' and precision p-1 would have exponent exp. Then if -4 <= exp < p, the number is formatted with presentation type 'f' and precision p-1-exp. Otherwise, the number is formatted with presentation type 'e' and precision p-1. In both cases insignificant trailing zeros are removed from the significand, and the decimal point is also removed if there are no remaining digits following it, unless the '#' option is used.

Positive and negative infinity, positive and negative zero, and nans, are formatted as inf, -inf, 0, -0 and nan respectively, regardless of the precision.

A precision of 0 is treated as equivalent to a precision of 1. The default precision is 6.

df.update(df.select_dtypes(include=np.number).applymap('{:,g}'.format))
print (df)
          City    a     b         c
0      Detroit  129  0.54     2,118
1         East  188  0.79  4,624.47
2      Houston  154  0.65  3,492.14
3  Los Angeles  266     1     7,426
4        Miami   26  0.11    792.18
5      MidWest   56  0.24   772.781

It was giving the column type as float64 after updating all the columns's type coming as an object. I want to update those columns only having 0 after the decimal place as an integer. Could you help me out? I have been trying to do it as all day but couldn't find anything — Vikas Chauhan, May 19 '20 at 18:53

Pandas convert float to int if decimals are 0

1 Answers1

Linked

Related