Preserve decimal places in DataFrame

Question

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?

Answer 1

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