Replace Comma with Dot Pandas

Replace comma with dot Pandas

You need to assign the result of your operate back as the operation isn't inplace, besides you can use apply or stack and unstack with vectorised str.replace to do this quicker:

In [5]:
df.apply(lambda x: x.str.replace(',','.'))

Out[5]:
          1-8        1-7
H0   0.140711   0.140711
H1     0.0999     0.0999
H2      0.001      0.001
H3   0.140711   0.140711
H4   0.140711   0.140711
H5   0.140711   0.140711
H6          0          0
H7          0          0
H8   0.140711   0.140711
H9   0.140711   0.140711
H10  0.140711  0.1125688
H11  0.140711  0.1125688
H12  0.140711  0.1125688
H13  0.140711  0.1125688
H14  0.140711   0.140711
H15  0.140711   0.140711
H16  0.140711   0.140711
H17  0.140711   0.140711
H18  0.140711   0.140711
H19  0.140711   0.140711
H20  0.140711   0.140711
H21  0.140711   0.140711
H22  0.140711   0.140711
H23  0.140711   0.140711

In [4]:    
df.stack().str.replace(',','.').unstack()

Out[4]:
          1-8        1-7
H0   0.140711   0.140711
H1     0.0999     0.0999
H2      0.001      0.001
H3   0.140711   0.140711
H4   0.140711   0.140711
H5   0.140711   0.140711
H6          0          0
H7          0          0
H8   0.140711   0.140711
H9   0.140711   0.140711
H10  0.140711  0.1125688
H11  0.140711  0.1125688
H12  0.140711  0.1125688
H13  0.140711  0.1125688
H14  0.140711   0.140711
H15  0.140711   0.140711
H16  0.140711   0.140711
H17  0.140711   0.140711
H18  0.140711   0.140711
H19  0.140711   0.140711
H20  0.140711   0.140711
H21  0.140711   0.140711
H22  0.140711   0.140711
H23  0.140711   0.140711

the key thing here is to assign back the result:

df = df.stack().str.replace(',','.').unstack()

How to replace only certain commas with dots in Pandas?

IIUC, you need to replace only the last comma, so you can do:

s = pd.Series(['40,910,27', '3,479.29', '34,561.09', '132,634,98'], dtype='string')
res = s.str.replace(',(\d+)$', r'.\1', regex=True)
print(res)

Output

0     40,910.27
1      3,479.29
2     34,561.09
3    132,634.98
dtype: string

The regex:

• ',(\d+)$' means match a comma that is followed by a group of digits till the end of the string.

The replacement:

• '.\1' a point and the first capture group (that is the groups digits after the last comma)

Search and replace dots and commas in pandas dataframe

The best is use if possible parameters in read_csv:

df = pd.read_csv(file, thousands='.', decimal=',')

If not possible, then replace should help:

df['col2'] = (df['col2'].replace('\.','', regex=True)
                        .replace(',','.', regex=True)
                        .astype(float))

Replacing dot with comma from a dataframe using Python

Where does the dataframe come from - how was it generated? Was it imported from a CSV file?

Your code works if you apply it to columns which are strings, as long as you remember to do
df = df.apply() and not just df.apply() , e.g.:

import pandas as pd
df = pd.DataFrame()
df['a'] =['some . text', 'some . other . text']
df = df.apply(lambda x: x.str.replace('.', ','))
print(df)

However, you are trying to do this with numbers, not strings.
To be precise, the other question is: what are the dtypes of your dataframe?
If you type

df.dtypes

what's the output?

I presume your columns are numeric and not strings, right? After all, if they are numbers they should be stored as such in your dataframe.

The next question: how are you exporting this table to Excel?

If you are saving a csv file, pandas' to_csv() method has a decimal argument which lets you specify what should be the separator for the decimals (tyipically, dot in the English-speaking world and comma in many countries in continental Europe). Look up the syntax.

If you are using the to_excel() method, it shouldn't matter because Excel should treat it internally as a number, and how it displays it (whether with a dot or comma for decimal separator) will typically depend on the options set in your computer.

Please clarify how you are exporting the data and what happens when you open it in Excel: does Excel treat it as a string? Or as a number, but you would like to see a different separator for the decimals?

Also look here for how to change decimal separators in Excel: https://www.officetooltips.com/excel_2016/tips/change_the_decimal_point_to_a_comma_or_vice_versa.html

UPDATE

OP, you have still not explained where the dataframe comes from. Do you import it from an external source? Do you create it/ calculate it yourself?
The fact that the columns are objects makes me think they are either stored as strings, or maybe some rows are numeric and some are not.

What happens if you try to convert a column to float?

df['Open'] = df['Open'].astype('float64')

If the entire column should be numeric but it's not, then start by cleansing your data.

Second question: what happens when you use Excel to open the file you have just created? Excel displays a comma, but what character Excel sues to separate decimals depends on the Windows/Mac/Excel settings, not on how pandas created the file. Have you tried the link I gave above, can you change how Excel displays decimals? Also, does Excel treat those numbers as numbers or as strings?

Pandas replacing decimal seperator in string columns

You have to perform str.replace on a pd.Series object, i.e. a single column. You can first select the columns that are not numeric and then use apply on this sub-frame to replace the comma in each column:

string_columns = df.select_dtypes(include='object').columns
df[string_columns].apply(lambda c: c.str.replace(',', '.').astype(float), axis=1)

How to remove commas from ALL the column in pandas at once

Numeric columns have no ,, so converting to strings is not necessary, only use DataFrame.replace with regex=True for substrings replacement:

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

Or:

df.replace(',','', regex=True, inplace=True)

And last convert strings columns to numeric, thank you @anki_91:

c = df.select_dtypes(object).columns
df[c] = df[c].apply(pd.to_numeric,errors='coerce')

replace commas to decimal points in DataFrame columns to make them numeric

import re    

for col in ['b', 'c', 'd']:
    df[col] = pd.to_numeric(df[col].apply(lambda x: re.sub(',', '.', str(x))))

Change price format from dot (.) to Comma (,) in python

Try this, You almost got it. Just had to convert the float to str and then apply str.replace.

df['price'] = df['price'].astype(str).str.replace('.',',')

Input:

   price
0  20.12
1  10.12
2  34.12
3  35.43

Output (After running the code)

   price
0  20,12
1  10,12
2  34,12
3  35,43

---

Related Topics