Floats rendering incorrectly in st.dataframe

Using Streamlit
1

I’ve been struggling to figure out how to resolve this issue and have tried everything I can think of and can’t figure out how to resolve it.
Basically, whenever a float value has a leading 0.0 all leading 0s are being removed before the dataframe is rendered in streamlit.

Here is a screenshot of what shows in streamlit:
SNAG-2023-09-19_11-40-58_AM

Here is a screenshot of the df in my IDE before it is added to streamlit:
SNAG-2023-09-19_11-42-26_AM

You can see the “Executive & Admin” value is 0.091400 but in the streamlit UI it shows it as 91400.

If there are more leading 0s like 0.0000100 it would just show as 100. No idea why and not sure what to do to fix it?

I am using streamlit 1.26.0 on python 3.9.13

2

Works as expected here. You must be doing something different.

3

It works totally fine if I manually create a data frame and type in 0.00123, but if the data comes from a query from st.experimental_connection, it doesn’t seem to work

4

Is df a pandas DataFrame or something else?

5

What connection type is being used?

6

Using the snowpark connection. I’ve tried both the direct result from the snowpark connection.query(sql) as well as taking that result and attempting to convert it to a pandas dataframe (even though I’m pretty sure that query returns a pandas dataframe).
I have a feeling if you connect to snowflake via snowpark and do something as simple as select 0.000123 as f and put those results into the table, it wouldn’t render correctly, but haven’t tried it yet…

7

If you can reproduce the issue with a pandas DataFrame, there must be a way to share it.

8

This could be a Pandas styling issue, not displaying the float appropriately.

Try this:

st.dataframe(df.style.format({"f": "{:.6f}"}), use_container_width=True)

9

@CarlosSerrano is there any way to do this generically? Ideally a solution that formats float columns as float, but non-float columns as ints and strings? I guess I could look at the data to do this, but was hoping for a “simpler” solution.

10

You could write some data cleaning/parsing functions to make sure you account for all your data types appropriately. Then visualize it.

11

@CarlosSerrano I ran into this issue again, not exactly the same, but very similar…
Code and screenshot below

sql = """
    WITH NumberSequence AS (
        SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) r,
        ROUND(UNIFORM(3200000, 3300000, RANDOM())) AS num
        FROM
        TABLE(GENERATOR(ROWCOUNT => 12))
    ), t1 as (
        select * from NumberSequence
    ), t2 as (
        select * from NumberSequence
    )
    select
    t1.*,
    t2.num + 200000 as num2,
    ROUND(((t1.num - num2) / t2.num) * 100, 1) AS percentage_change

    from t1
    left join t2 on t2.r = t1.r
"""

df = conn.query(sql)
st.dataframe(df, hide_index=True)

SNAG-2023-11-02_03-50-29_PM
SNAG-2023-11-02_03-50-29_PM671×562 115 KB

And here is the result from snowflake

SNAG-2023-11-02_03-53-52_PM
SNAG-2023-11-02_03-53-52_PM1784×596 48.1 KB

12

Try being specific about your casting on the SQL side.

ROUND(((t1.num - num2) / t2.num) * 100, 1)::float AS percentage_change

13

Unfortunately the sql is being generated by gpt… Any other ideas for streamlit to interpret the data format better? Python prints the dataframe fine… And ironically the df export to csv from the streamlit download button is also correct, so it’s just the displayed table that is incorrect

14

I would add something like this to convert all the objects to floats if possible.

for k,c in dict(df.dtypes).items():
    if c == 'object':
        try:
            df = df.astype({k:'float'})
        except Exception as e:
            st.error(e)