How to remove extra decimals from the column values if occurs in python

user_11050535 0
Viewed 71

I am looking to remove any 2nd decimal from the last of an row if it exists in a column.

Input Data:

column1

27.356.10
25689
2345.00
NaN
123.25.70
12.345.75.00

Expected Output:

column1

27356.10
25689
2345.00
NaN
12325.70
1234575.00

Code Used:

df['column1'] = df['column1'].replace('[^\d.]', '', regex=True).astype(float)

Above script removes special character but how 2nd or 3rd decimal from the last can be Removed if exists

pandas python
3 Answers

Regex101:

df["column1"] = df["column1"].str.replace(r"(\.)(?=.*\.)", "", regex=True)
print(df)

Prints:

      column1
0    27356.10
1       25689
2     2345.00
3         NaN
4    12325.70
5  1234575.00
user_100359850

Alternatively, you can choose not to use Regex:

df["column1"] = df["column1"].str.replace('.', '', df["column1"].count('.')-1)

I think this reads more intuitively -- replace . with {empty} {count-of-periods-used}-1 times

inputs = """27.356.10
25689
2345.00
NaN
123.25.70
12.345.75.00""".split('\n')

print(inputs)

for input in inputs:
    print(input.replace('.', '', input.count('.')-1))

['27.356.10', '25689', '2345.00', 'NaN', '123.25.70', '12.345.75.00']
27356.10
25689
2345.00
NaN
12325.70
1234575.00
user_65065390

Here is another way:

l = df['column1'].str.rsplit('.',n=1,expand=True).fillna(0)
(l[0].str.replace('\.','') + '.' + l[1].astype(str)).astype(float)
user_138021150
Related