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.
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.
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?
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.
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?
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)
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)
@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?
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.
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!
Is it still not possible to show B (billion) instead of G (giga) ?
Your suggested solution of dividing by a billion then adding a suffix does not work when the formatting should be dynamic. For example, when we want to format a y axis and we have a filter to change the data shown. So in that case, sometimes we show numbers in millions, sometimes in billions and so on.
So I think it’s important to have a way to do that, maybe by using a function to do the formatting and return the formatted string? or by extending D3 format language?
Please let us know if this on the short-term roadmap and if there are any workarounds for the use case mentioned above.
