CODEWITHSUNDEEP
pythonexcelpandasdataframereplace
user14148145
user14148145

Reputation:

Replacing dot with comma from a dataframe using Python

I have a dataframe for example df :

I'm trying to replace the dot with a comma to be able to do calculations in excel.

I used :

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

or

df = df.apply(lambda x: x.str.replace('.', ','))

Results :

Nothing changes but I receive his warning at the end of an execution without errors :

FutureWarning: The default value of regex will change from True to False in a future version. In addition, single character regular expressions willnot be treated as literal strings when regex=True.

View of what I have :

dataframetoexcel

Expected Results :

enter image description here

Updated Question for more information thanks to @Pythonista anonymous:

print(df.dtypes)

returns :

Date         object
Open         object
High         object
Low          object
Close        object
Adj Close    object
Volume       object
dtype: object

I'm extracting data with the to_excel method:

df.to_excel()

I'm not exporting the dataframe in a .csv file but an .xlsx file

Upvotes: 1

Views: 4540

Answers (1)

Pythonista anonymous
Pythonista anonymous

Reputation: 8950

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?

Upvotes: 0

Related Questions