Thousands Separator in a Number Column of Data Editor

Summary

How to show thousands separator in a Number Column inside the Data Editor component introduced in streamlit 1.23?

I tried to set the number format to “%,.2f” as it is recognized by the defautl pandas DataFrame Styler, but this format results in erro when used in the Data Editor.

Steps to reproduce

Code snippet:

import pandas as pd
import streamlit as st

data = {
  "name": ["Alice", "Bob"],
  "value": [50000.5, 40000.4]
}

st.title("Example")
#load data into a DataFrame object:
df = pd.DataFrame(data)

st.data_editor(
  df, 
  column_config={
    "value": st.column_config.NumberColumn(format="%,.2f")
  }
)

If applicable, please provide the steps we should take to reproduce the error or specified behavior.

Expected behavior:

I expected the Data Editor to be compatible with the same formats as the default DataFrame Styler.
Also it would be great if one could choose which character to use as thousands and decimal separator.

Debug info

  • Streamlit version: 1.23
  • Python version: 3.10
  • Using PipEnv

Hi @tp-rbrasset ,

I don’t think it’s doable right now with the Number Column format parameter. you can use the step parameter.

The main reason why it’s not super doable with the format parameter is because we use the sprintf-js library and there is an issue here:

Hi @willhuang, thanks for the quick response, I appreciate it.

Sad to know that implementing this feature is not something simple.

But I still have some doubts about this issue.

I find it curious that when the format parameter is not informed, the number column already shows the values with the thousand separator but without decimal places (try it in my code snippet to check). In this situation, isn’t sprintf.js used?

Wouldn’t it be possible to allow using this standard formatting but with 2 decimal places?

Ah yes you’re right. Huh for some reason, I was trying to check the sprintf format but you can just do something like this:

import pandas as pd
import streamlit as st

data = {
  "name": ["Alice", "Bob"],
  "value": [50000.5, 40000.4]
}

st.title("Example")
#load data into a DataFrame object:
df = pd.DataFrame(data)

st.data_editor(
  df, 
  column_config={
    # NOTICE THE STEP PARAMETER BEING USED HERE
    "value": st.column_config.NumberColumn(step=".01")
  }
)

Thanks @willhuang, your suggestion achieves the desired behavior and helped me a lot.

I think this solution is sufficient for the moment, but it is not yet ideal, because it implies an effective loss of data accuracy beyond the decimal points defined in the step and not just a visualization with less accuracy.

In my understanding, in an ideal solution the data could have arbitrary amounts of decimal places but only the visualization would show fewer decimal places, so that the data returned would have all decimal places.

Therefore, I think it would be very beneficial to talk to others in the Streamlit team about this issue and perhaps open an Issue to seek a solution to this problem.

Finally, could I use this reply to also ask if there is any way to change the character of the thousand and decimal separator? I’m from Brazil and here we use dot as thousand separator and comma as decimal separator

Hi @tp-rbrasset ,

thanks for the idea! Is it possible that you can write it all down here so that I don’t misconstrue what you’re attempting to say.

As for changing the thousand separator, let me check and get back to you.

Thank You very much @willhuang !

I will post the issue later in the github page.
Meanwhile, I look forward to your return on changing thousands and decimal separator.

Hi @willhuang!

Any news about this topic?

Hi @tp-rbrasset ,

As far as I know and talking with Lukas Masuch, there is no way to change the character of the thousand and decimal separator as of right now. One solution would be to add a locale and that would be a feature enhancement. I suggest creating a feature enhancement with locale as an option in order to get it prioritized as we do look at github issues and prioritize the ones with high upvotes / interactions.

This issue seems to affect st.dataframe as well – I cannot get st.NumberColumn’s format to display a proper thousands separator.

The workaround for me is the use of a Pandas Styler but it’s super confusing if there is a format parameter capable of some but not all formatting.

I think a lot of users would greatly appreciate an improvement here.

I’m waiting for a solution.

1 Like

EDIT : read too fast thought this was about st.dataframe

Hey guys, I wanted to share the solution I used to properly format numbers when displaying a DataFrame with streamlit.

We can use the style property of a pandas DataFrame, by specifying a formatter (a dict with column names as keys and formatters as values), a thousands character used to separate thousands and a decimal character as decimal separator. This is really useful as, for example, French people use a space as thousands separator and a comma as decimal separator.

Here is an example. I have a df with 4 columns, 3 of which need specific formats :

# df is a pd.DataFrame with columns ["Segment", "Début", "Fin", "Variation"]

# Apply your style to desired columns
df = df.style.format(
    {
        "Début": lambda x : '{:,.1f} €'.format(x),
        "Fin": lambda x : '{:,.1f} €'.format(x),
        "Variation": lambda x : '{:,.1f} %'.format(x),
    },
    thousands=' ',
    decimal=',',
)

# Show the table
st.dataframe(df)

Result :

Hope this helps :slight_smile:

1 Like

Hi Julien, thanks for contributing with the thread, but the discussion here is more about the DataEditor than the DataFrame component, which supports pandas styles as you pointed.

Till this day, there is still no way of showing thousands separator in a DataEditor component :frowning:

Oh yeah seems like I read too fast, my bad

Hi, everyone!

One thing I’ve done and kind of worked is:

  • convert the float to string:
    .apply(lambda x: locale.format_string(‘%.3f’, x, grouping=True))

  • show that string on the data editor

  • convert it back to float
    .apply(lambda formatted_value: float(locale.atof(formatted_value)))

Altough it works, it would be better if the data editor supported pandas styles…

Well, it helped me with a dataframe (year was 2,018 now it’s 2018), so thanks!

I found that using the method of @julienbosse made other columns with numbers looking weird. Don’t know why.

To anyone other looking to remove the thousands seperator in a column of a st.dataframe (e.g. from a column with years), I did it with:

st.dataframe(df, column_config={“year”:st.column_config.NumberColumn(format= “%f”)}, hide_index=True)

E.g. 2,018 will be formatted to 2018.