CODEWITHSUNDEEP
pythonpandasstringregexp-replacecharacter-trimming
Pythn
Pythn

Reputation: 171

Remove trailing .0 from strings of entire DataFrame

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!

Upvotes: 2

Views: 8395

Answers (3)

Nathan RB
Nathan RB

Reputation: 1

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.

Upvotes: 0

wwnde
wwnde

Reputation: 26676

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

Upvotes: 0

Henry Ecker
Henry Ecker

Reputation: 35626

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.

Upvotes: 4

Related Questions