CODEWITHSUNDEEP
pythonpandas
tedioustortoise
tedioustortoise

Reputation: 269

Pandas dataframe to html, unable to display to 2 decimal places

No matter how I try and round and display this data to 2 decimal places (d.p), I can’t seem to get it to work. Basically the DR, CR and Balance columns refer to financials, so a 2 d.p. display is required. But the CR column is only showing to 1 d.p for the zero values. There are also other errors such as the balance in the first row also only showing to 1 d.p.

As you can see, I am importing from sqlite3 and exporting to html and working with a dataframe in-between. Thanks

df = pd.read_sql_query("SELECT * FROM "+n+" ORDER BY Date", conn)
df["DR"] = pd.to_numeric(df["DR"])
df["CR"] = pd.to_numeric(df["CR"])
df = df.fillna(0)
df["Balance"] = ''
i = 0
df.iloc[i,6] = df.iloc[i,3]-df.iloc[i,4]
for i in range(1,df.shape[0]):
    try:df.iloc[i,6] = df.iloc[i-1,6]+df.iloc[i,3]-df.iloc[i,4]
    except: pass
for u in range(df.shape[0]):
    try:
        if df.iloc[u,6] <= 0:
            CRt = abs(df.iloc[u,6])
            #df.iloc[u,6] = str(abs(df.iloc[u,6]))+' CR' 
            df.iloc[u,6] = str(CRt.round(decimals=2))+' CR' 
        else:
            DRt = df.iloc[u,6]
            df.iloc[u,6] = str(DRt.round(decimals=2))+' DR' 
    except:
        pass
df = df.fillna('')
html = df.to_html(header=True,classes="table table-sm table-hover",justify="center",index=False)

enter image description here

Upvotes: 1

Views: 2675

Answers (1)

Georgina Skibinski
Georgina Skibinski

Reputation: 13397

Consider the following:

import pandas as pd

df=pd.DataFrame({"x": [45.76, -345.8906, 44.237, 12]})

df["y"]=df["x"].map('{:.2f}'.format)
df["z"]=df["x"].abs()

print(df)

Outputs:

          x        y         z
0   45.7600    45.76   45.7600
1 -345.8906  -345.89  345.8906
2   44.2370    44.24   44.2370
3   12.0000    12.00   12.0000

(1) the immediate solution is - just use map as per the above, it will do the formatting you need

(2) consider rewriting your code to leverage vectorized calculations - instead of using loop - it will speed up your code meaningfully (I put bits, that I see you are doing in a vectorized way in my example).

Upvotes: 3

Related Questions