英文:
Preserve decimal places in DataFrame
问题
我正在尝试使用pd.read_excel()函数将Excel工作表读取为DataFrame。我想要检查FX列中的每个元素是否包含2位小数并以2位小数显示。实际上，FX汇率列中的所有元素都有2位小数，但其中一些没有显示小数。
我尝试使用以下命令将FX列的数据类型转换为str。
fx_forward = pd.read_excel(io=file_path, sheet_name="FX Rate", dtype={"FX Rate": str})
我期望获得一个如下所示的DataFrame：
然而，我得到的DataFrame中的FX Rate都是"1"。
我应该怎么做才能获得预期的DataFrame？
英文:
I'm trying to read the excel worksheet to DataFrame using pd.read_excel() function. I want to check if each element in the FX column contains 2 decimals and are displayed with 2 decimals. Actually all elements in the FX Rate column have 2 decimals, but some of them are displayed with no decimal.
I tried to use the following command to transfer the data type of FX column to str.
fx_forward = pd.read_excel(io = file_path, sheet_name = "FX Rate", dtype = {"FX Rate" :str})
I expect to get a DataFrame looks like this:
However, I get a DataFrame whose FX Rate are all "1".
What should I do to get the expected DataFrame?
答案1
得分: 1
我猜原因是您将dtype声明为str，而实际上数值并没有包含小数点，Excel只是显示它们。
我已经创建了一个类似您提到的Excel文件，并且结果显示为2位小数点，如下所示：
import pandas as pd
# 将默认的pandas浮点数打印格式设置为显示2位小数点
pd.set_option("display.float_format", '{:,.2f}'.format)
df = pd.read_excel(io=file_path, sheet_name='FX Rate', dtype={'FX Rate': float})
df.head()
结果如下：
ID FX Rate
0 TRADE1 1.00
1 TRADE2 1.00
2 TRADE3 1.00
3 TRADE4 1.00
4 TRADE5 1.00
英文:
I guess the reason for that is that you are declaring the dtype as str and the values do not actualy holds the decimals, and excel just shows them.
I've created an excel similar to the one you mentioned and have the results with 2 decimal points as follows
import pandas as pd
# Set the default pandas float print format to show 2 decimal points
pd.set_option("display.float_format", '{:,.2f}'.format)
df = pd.read_excel(io = file_path, sheet_name = 'FX Rate', dtype = {'FX Rate': float})
df.head()
The results:
ID FX Rate
0 TRADE1 1.00
1 TRADE2 1.00
2 TRADE3 1.00
3 TRADE4 1.00
4 TRADE5 1.00
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论