2

Hi I would like to remove all ".0" at the end of a string for an entire DataFrame and I need it to be an exact match.

Let's make an example df:

a      b      c
20     39.0   17-50
34.0   .016.0   001-6784532

The desired output:

a      b      c
20     39     17-50
34     .016   001-6784532

I tried using replace but it didn't work for some reason (I read maybe because replace only replaces entire strings and not substrings?). Either way, if there is a way it can work I'm interested to hear about it because it would work for my dataframe but I feel it's less correct in case I'll have values like .016.0 beacause then it would also replace the first 2 characters.

Then I tried sub and rtrim with regex r'\.0$' but I didn't get this to work either. I'm not sure if it's because of the regex or because these methods don't work on an entire dataframe. Also using rtrim with .0 didn't work because it removes also zeros without a dot before and then 20 will become 2. When trying sub and rtrim with regex I got an error that dataframe doesn't have an attribute str, how is that possible?

Is there anyway to do this without looping over all columns?

Thank you!

Pythn
  • 171
  • 2
  • 10

3 Answers3

4

Let's try DataFrame.replace:

import pandas as pd

df = pd.DataFrame({
    'a': ['20', '34.0'],
    'b': ['39.0', '.016.0'],
    'c': ['17-50', '001-6784532']
})

df = df.replace(r'\.0$', '', regex=True)

print(df)

Optional DataFrame.astype if the columns are not already str:

df = df.astype(str).replace(r'\.0$', '', regex=True)

Before:

      a       b            c
0    20    39.0        17-50
1  34.0  .016.0  001-6784532

After:

    a     b            c
0  20    39        17-50
1  34  .016  001-6784532

rtrim/rstrip will not work here as they don't parse regex but rather take a list of characters to remove. For this reason, they will remove all 0 because 0 is in the "list" to remove.

Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
0

Conditionally replace; Use np.where().

df['b']=np.where(df['b'].str.contains('\.\d+\.'),df['b'].str.replace(r'\.\d+$','', regex=True), df['b'])



    a     b            c
0  20.0  39.0        17-50
1  34.0  .016  001-6784532

That is, where we have .digit(s)., replace .\digit(s) at the end

wwnde
  • 26,119
  • 6
  • 18
  • 32
  • Thank you for your response. I don't understand how this solves my problem. It still gives you .0 behind the rest of the dataframe and even in column b itself in the first row. And what do you mean with the last sentence? I see it's not being used in your answer. Thank you! – Pythn Jul 13 '21 at 17:01
0

For those who are trying to export the DataFrame to a CSV (or other types), you can use the parameter float_format from Pandas to eliminate all trailing zeros from the entire DataFrame.

df.to_csv(path_to_file.csv, float_format='%g')

'%g' and other formats explanation.

Nathan RB
  • 1
  • 2
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 30 '22 at 08:43