Learn how to use Dash Bio for next-gen sequencing & quality control. 🧬 Register for the Oct 27 webinar.

Dash Datatable Large Number Formatting

Hello~

I have read through the datatable number formatting docs (Number Formatting | Dash for Python Documentation | Plotly), and haven’t seen a way to format very large numbers into shorthand, i.e. 123,000,000 as 123M. As my datatable values get into billions it would be great to have a way to maintain functional shorting while displaying the shorthand strings. Currently I have a function to transform the number into this string, but obviously this breaks the native sorting.

Is there any way to achieve this?

Thanks in advance!
-Daniel

Hi @danielhhowell

The DataTable uses the d3-format library to format the numeric data in the table.

This will format like your requested shorthand – 123M (SI-prefix with three significant digits)

table = dash_table.DataTable(
    id="format_table",
    columns=[
        {
            "name": i,
            "id": i,
            "type": "numeric",  # Required!
            "format": { "specifier": ".3s"}
        }
        for i in df.columns
    ],
    data=df.to_dict("records"),
    editable=True,
)

There are lots of other ways to format large numbers - for example making everything with the same SI-prefix, setting the number of significant digits, trimming trailing zeros etc.

You can try different options and see more examples in this app:
https://formattable.pythonanywhere.com/

Hi @AnnMarieW,

Great solution. I was wondering if this is possible for data which is populated from an upstream process (such as an API call), whereby the data is only “displayed” in a datatable upon upstream user interactions?

Yes, it’s possible to do that too!

Thank you @AnnMarieW!

Dash truly can do everything. I had a look through the formatting, and it seems the SI standard coverts billions to a ‘G’ notation, whereas this is meant to be more monetary large quantities, so 123,000,000,000 as 123B instead of 123G.

Fingers crossed this is possible?

Thank you in advance!

Hi @AnnMarieW , I have been playing with this for the past few hours and still struggling to update the formatting when no data is initalised in the Datatable. Is it possible to share a minimum working example for this particular use case?

Hi @danielhhowell

That’s a great question, and it looks like it’s not an option in d3. javascript - D3: Formatting tick value. To show B (Billion) instead of G (Giga) - Stack Overflow

It’s a pretty easy workaround in javascript, but that doesn’t help much with Dash. Maybe this should be a new feature request. In the meantime, one solution is to divide by a billion and add a “B” symbol suffix.


import dash
import dash_table
import pandas as pd
import dash_html_components as html
import dash_bootstrap_components as dbc
import numpy as np

app = dash.Dash(__name__, external_stylesheets=[dbc.themes.BOOTSTRAP])

df = pd.DataFrame(np.random.randint(10 ** 12, size=(4, 4)), columns=list("ABCD"))

df = df / 10 ** 9

table = dash_table.DataTable(
    id="format_table",
    columns=[
        {
            "name": i,
            "id": i,
            "type": "numeric",  # Required!
            "format": {"locale": {"symbol": ["", " B"]}, "specifier": "$,.1f"},
        }
        for i in df.columns
    ],
    data=df.to_dict("records"),
    editable=True,
)
app.layout = html.Div(table)

if __name__ == "__main__":
    app.run_server(debug=True)

Hi @d_dash_user

Could you say more about your use-case? It makes sense that you can’t format the data if there is no data. Is it possible to initialize the table?

Hi @AnnMarieW,

Thanks for your reply. Essentially, the below datatable (and a few others like it) shall be populated from an API call, which is dependent on user inputs. Whilst the table isn’t initalised with any data, the structure which is returned from the API is consistent (same columns)

tab4_content = dbc.Card(children=[
    dbc.CardBody(children=[
        html.Br(),
        dbc.Row(children=[
            dcc.Loading(id="loading-9", type="default",
                        children=[
                            dash_table.DataTable(id='sector-exposures',
                                                 export_format='xlsx',
                                                 export_headers='display',
                                                 style_as_list_view=True,
                                                 sort_action="native",
                                                 sort_mode='multi',
                                                 persistence=True,
                                                 persistence_type='memory',
                                                 style_table={'overflowX': 'scroll', 'overflowY': 'auto',
                                                              'width': '1000px', 'backgroundColor': 'white'},
                                                 style_header={'backgroundColor': 'white', 'fontWeight': 'bold'},
                                                 style_cell={'height': 'auto',
                                                             'minWidth': '150px', 'width': '250px',
                                                             'maxWidth': '600px', 'whiteSpace': 'medium', 'textAlign': 'center'},
                                                 ),
                                ]
                        )
                    ]
            ),

    ])],
    className="mt-3")

Is it possible to use datatable formatting in this instance?

Thanks for your advice :slight_smile:

@d_dash_user Oh, I see. Yes, you can use formatting in this instance. You just need to include it in the columns definition in the callback. And be sure to include the "type": "numeric", otherwise it doesn’t apply the formatting - and there is no error message.

If you are still having a problem, can you share how you are updating the data and columns from the API?

1 Like

thank you for sharing with us it’s really good

1 Like

Hi @sunil808 and welcome to the Dash community. :slight_smile:

I’m glad you found this helpful - thanks for letting me know!

it’s possible to do that too!

1 Like

thank you for sharing..

1 Like

I just switch off sorting for the table whith formatting =( As i can understand problem starts after .map or .format float type becouse it converts float to string and we get unexpected result of sorting. To solve this, i think we can create some component mb dcc.dropdown with column names and sort pandas dataframe and format it and after display at table in callback of that dropdown.

its very confusing actually . .

Hey @meshare and welcome to the Dash community :slightly_smiling_face:

The cool thing about using format in the DataTable definition, is that you don’t need to convert your data to strings. It only changes how the data is displayed in the table.

You can find more info in the docs here: Number Formatting | Dash for Python Documentation | Plotly

Also, if you make a minimal working example, or just say more about the current format and how you would like it displayed, I’m sure I’ll be able to help!