pandas converting floats to strings without decimals

Question

I have a dataframe

df = pd.DataFrame([
        ['2', '3', 'nan'],
        ['0', '1', '4'],
        ['5', 'nan', '7']
    ])

print df

   0    1    2
0  2    3  nan
1  0    1    4
2  5  nan    7

I want to convert these strings to numbers and sum the columns and convert back to strings.

Using astype(float) seems to get me to the number part. Then summing is easy with sum(). Then back to strings should be easy too with astype(str)

df.astype(float).sum().astype(str)

0     7.0
1     4.0
2    11.0
dtype: object

That's almost what I wanted. I wanted the string version of integers. But floats have decimals. How do I get rid of them?

I want this

0     7
1     4
2    11
dtype: object

Asclepius · Accepted Answer · 2020-06-08 16:18:31Z

44

For pandas >= 1.0:

<NA> type was introduced for 'Int64'. You can now do this:

df['your_column'].astype('Int64').astype('str')

And it will properly convert 1.0 to 1.

Alternative:

If you do not want to change the display options of all pandas, @maxymoo solution does, you can use apply:

df['your_column'].apply(lambda x: f'{x:.0f}')

edited Jun 8, 2020 at 16:18

Asclepius

64.7k20 gold badges188 silver badges165 bronze badges

answered May 26, 2020 at 9:14

toto_tico

19.2k10 gold badges103 silver badges121 bronze badges

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

1 Comment

Culdesac Over a year ago

Great! astype('Int64').astype('str') worked for converting individual columns. Since it's true reformatting of data type, I'd prefer this over changing display option.

miriamsimone · Accepted Answer · 2018-05-24 04:36:22Z

32

Converting to int (i.e. with .astype(int).astype(str)) won't work if your column contains nulls; it's often a better idea to use string formatting to explicitly specify the format of your string column; (you can set this in pd.options):

>>> pd.options.display.float_format = '{:,.0f}'.format
>>> df.astype(float).sum()
0     7
1     4
2    11
dtype: float64

edited May 24, 2018 at 4:36

answered Sep 14, 2016 at 4:28

miriamsimone

36.7k12 gold badges97 silver badges121 bronze badges

4 Comments

IanS Over a year ago

I believe the correct method for a dataframe is applymap, not map.

miriamsimone Over a year ago

@IanS yes you're right, I used map because because I summed the columns before doing the formatting

ℕʘʘḆḽḘ Over a year ago

why does .format() convert to object here? It is implicitly converting from float to string?

Felix Over a year ago

@ℕʘʘḆḽḘ I guess the format is used in the conversion. Maybe in the series formatter as mentioned in the documentation.

piRSquared · Accepted Answer · 2017-07-27 16:14:42Z

25

Add a astype(int) in the mix:

df.astype(float).sum().astype(int).astype(str)

0     7
1     4
2    11
dtype: object

Demonstration of example with empty cells. This was not a requirement from the OP but to satisfy the detractors

df = pd.DataFrame([
        ['2', '3', 'nan', None],
        [None, None, None, None],
        ['0', '1', '4', None],
        ['5', 'nan', '7', None]
    ])

df

      0     1     2     3
0     2     3   nan  None
1  None  None  None  None
2     0     1     4  None
3     5   nan     7  None

Then

df.astype(float).sum().astype(int).astype(str)

0     7
1     4
2    11
3     0
dtype: object

Because the OP didn't specify what they'd like to happen when a column was all missing, presenting zero is a reasonable option.

However, we could also drop those columns

df.dropna(1, 'all').astype(float).sum().astype(int).astype(str)

0     7
1     4
2    11
dtype: object

edited Jul 27, 2017 at 16:14

answered Jul 22, 2016 at 0:19

piRSquared

296k68 gold badges509 silver badges654 bronze badges

4 Comments

mechanical_meat Over a year ago

I believe you were just now the victim of some strategic downvoting. +1 to counter that and because your answer came way before the other.

dlm Over a year ago

This won't handle a row with all missing values.

piRSquared Over a year ago

@dlm sure it does.. I just ran example. If you have a specific case, why don't you ask a question to clear it up. This answer satisfied the requirements of the OP. If you didn't find it useful, you don't have to up-vote. But a down-vote is a declaration that the answer is not useful when it clearly was, as it solved the problem presented.

Rocketq Over a year ago

Wrong solution, using sum changes the final result

Jossie Calderon · Accepted Answer · 2016-07-22 00:21:08Z

3

Add astype(int) right before conversion to a string:

print (df.astype(float).sum().astype(int).astype(str))

Generates the desired result.

answered Jul 22, 2016 at 0:21

Jossie Calderon

1,42513 silver badges23 bronze badges

Comments

Mick · Accepted Answer · 2023-02-08 22:12:40Z

3

The above didnt work for me so im going to add my solution

Convert to a string and strip away the .0:

db['a] = db['a'].astype(str).str.rstrip('.0')

answered Feb 8, 2023 at 22:12

Mick

817 bronze badges

1 Comment

Filipe Gomes Over a year ago

if it ends with 0, the 0 is also removed. Example: input: 1230.0 expected output: 1230 real output: 123

Donald Tse · Accepted Answer · 2022-04-08 10:15:57Z

1

based on toto_tico's solution - alternative , minor changes to avoid null case become nan

df['your_column'].apply(lambda x: f'{x:.0f}' if not pd.isnull(x) else '')

answered Apr 8, 2022 at 10:15

Donald Tse

312 bronze badges

Comments

ifly6 · Accepted Answer · 2022-11-18 21:35:11Z

0

The above solutions, when converting to string, will turn NaN into a string as well. To get around that and retain NaN, use:

c = ...  # your column
np.where(
    df[c].isnull(), np.nan,
    df[c].apply('{:.0f}'.format)
)

Retaining NaN allows you to do stuff like convert a nullable column of integers like 19991231, 20000101, np.nan, 20000102 into date time without triggering date parsing errors.

answered Nov 18, 2022 at 21:35

ifly6

5,3993 gold badges28 silver badges52 bronze badges

Collectives™ on Stack Overflow

pandas converting floats to strings without decimals

7 Answers 7

For pandas >= 1.0:

Alternative:

1 Comment

4 Comments

4 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

For pandas >= 1.0:

Alternative:

1 Comment

4 Comments

4 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related