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:
最近,我在Microsoft Excel中遇到了一个非常奇怪的事情。我在Python 3.6中创建了一个数据帧并用一些整数填充它,然后我使用“to_csv”函数来获取csv输出。我用Microsoft Excel打开文件进行基本的统计分析并绘制一些图表; Microsoft Excel无法将单元格中的数字识别为数字。例如,当我添加两个单元格时,无论数字是多少,结果都将为零。这是我的Excel环境中的截图:
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.
在黄色单元格(C101)中,我试图获得C列中单元格的总和,但正如我所解释的那样,sum函数(以及所有其他函数,如Count或Max)无法正常工作。我还必须说,所有单元格都有“数字”数据类型。我很困惑,任何建议都会有所帮助。
1 个解决方案
#1
3
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=',')
#1
3
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=',')