CODEWITHSUNDEEP
excelpython-3.xpandasexcel-formulaexcel-2010
amiref
amiref

Reputation: 3431

Excel functions do not work properly after getting csv output from Pandas

Recently, I confronted a very strange thing in Microsoft Excel. I made a dataframe in Python 3.6 and filled it with some integer numbers, then I used "to_csv" function to get csv output. I opened the file with Microsoft Excel for doing basic statistical analysis and drawing some charts, however; Microsoft Excel doesn't recognize the numbers in the cells as number. For example, when I add two cell, the result will be zero, no matter what are the numbers. This is a screenshot from my my Excel environment: enter image description here

In the yellow cell (C101) I tried to get the sum of cells in column C, but as I explained the sum function (and all the other functions like Count or Max) doesn't work properly. I also, have to say, all the cells have "Number" data type. I'm quite confused, any suggestion would help.

Upvotes: 0

Views: 74

Answers (1)

EyJay
EyJay

Reputation: 180

I would have written the answer as a comment, but my reputation is too low.

By default, the decimal seperator is set to a dot ('.'). You have to switch it to a comma (',') like this:

df.to_csv(file, decimal=',')

EDIT:

I forgot that you also have to set the seperator, since its default value is comma:

df.to_csv(file, sep=';', decimal=',')

Upvotes: 3

Related Questions