Formatting numbers with commas as thousand separators in st_aggrid's configure_columns method

🧩 Streamlit Components streamlit-aggrid
1

Hi all. Apologies if this is a stupid question/request, but I’m a bit of a noob. However, I’ve been searching for the last couple of weeks for a way to be able to format figures in tables I’m making with the st_aggrid library/component, to have comma delimiters for thousands, millions, etc.

While I’ve found a number of references to the valueFormatter, in each instance that appears to be formatting a specific data column (eg valueFormatter="parseFloat(data.numeric_column_a)... where I believe numeric_column_a is a specific column name.)

I’ve created a function (which I called table_maker ) to build tables and into that function I pass a list of currency columns, using your customCurrencyFormat method, which is great. However, within this I’d like to add in the comma delimiters for thousands.

Does anyone know if there’s a way I can deploy the valueFormatter within the configure_columns method to achieve this result? Here’s what I’m using in my table_maker function to format the currency columns:

gb.configure_columns( currency_cols_list, type=["numericColumn", "numberColumnFilter", "customCurrencyFormat"], precision=2, custom_currency_symbol="£", )

Again, apologies if this is not the right forum or I’m not being clear in the question. It’s just that I’ve been trying to get this working for a couple of weeks and can’t see any other way to move forward.

Thanks, in advance, for any help.

2

Hi @theabbot

Is this what you are looking for?

Assuming I have a CSV file containing the following fields (and 1 line of data)
EMPNAME,AMT
P. KUMAR,441270

You want to display the number as … say… $441,270.0 in the AGGRID display, right?
Then you can use something like this to configure your number column (in my case - a column named ‘AMT’:

gb.configure_column("AMT", 
                    type=["numericColumn","numberColumnFilter","customNumericFormat"], 
                    valueGetter="data.AMT.toLocaleString('en-US', {style: 'currency', currency: 'USD', maximumFractionDigits:1})")

Please change the parameters as required. Hope it works for you. (There are other ways to achieve this, but this seems the most simple.)

Cheers :slight_smile:

4 Likes
3

Hi @Shawn_Pereira

Thanks very much for your reply.

I was hoping that there was a way to do this using the configure_columns method, as that’s what I’ve used to pass a list of columns in. I guess it may be possible to use a list comprehension through valueGetter or valueFormatter, but I was hoping there was a slick way I was missing within configure_columns that might avoid that.

Thanks again for your help. Cheers.

4

Hi @Shawn_Pereira ,

  • Are you aware of some documentation that describes the different functions and parameters, like the ones you have used here for gb.configure_column in detail? The official documentation I found here, does not have these details.

  • I want to display a number with commas (1,200 instead of 1200), without a decimal. I am not able to figure out how to do that with gb.configure_column

Would you be able to help? Thanks.

5

Hi @mjo, to answer your question - No, I don’t have access to any special documentation on AGgrid. I learn by the trials of others and my own. Good places to know details about AGgrid are:
a. This forum - just search aggrid and you will get a lot of info and code that you can try out and repurpose.
b. Pablo’s - the creator’s - GitHub, for docs and examples
c The AG grid website
d And finally, Google

Below are some configure column (numeric column) examples, that are easily understandable, that you can reference for information:

a. gb.configure_column("Amt", header_name=("Amount"), editable=True, type=["numericColumn","numberColumnFilter","customNumericFormat"], precision=0)

b. gb.configure_column('MySum', type=['numericColumn'], valueGetter='(Number(data.EMPLVL) * 5.23).toFixed(1)', editable='false', resizable=False, sortable=False)

Regarding your comma number formatting, you could try out the valueGetter or through JScode. If I get some time this weekend, I will try to check that out for you.

Cheers

2 Likes
6

Thanks @Shawn_Pereira :slight_smile:

7

Hi @mjo,

Column 2 is the format you want - refer the code below:

import streamlit as st
import pandas as pd
from st_aggrid import GridOptionsBuilder, AgGrid

df = pd.DataFrame({'col1': [1000, 2000, 3000], 'col2': [4000, 5000, 6000]})

gb = GridOptionsBuilder.from_dataframe(df)
gb.configure_column("col2", 
                    type=["numericColumn","numberColumnFilter","customNumericFormat"], 
                    valueFormatter="data.col2.toLocaleString('en-US');") 
vgo = gb.build()
AgGrid(df, gridOptions=vgo)

Cheers

2 Likes
8

Much appreciated @Shawn_Pereira :pray:t4:

9

If you want to use config_columns, you could do something like this:

    
    import st_aggrid as st_ag

    k_sep_formatter = st_ag.JsCode("""
    function(params) {
        return (params.value == null) ? params.value : params.value.toLocaleString(); 
    }
    """)
    builder.configure_columns(['quantity', 'amount', 'price'], valueFormatter=k_sep_formatter)

and remember to add the argument AgGrid(..., allow_unsafe_jscode=True). There might be a way to do this without jscode, but I haven’t found it yet.

5 Likes
10

Thanks @HHest :slight_smile:
This works pretty well for me as I have several columns that need to be formatted.

12

wow this worked perfectly though!!! thank you

13

Nice, thank you, work for my currency IDR

1 Like