Sorting data table after formatting data

Dash Python
1

When I use the code below and sort the table it works, however, when I format the data it does not sort properly.

import pandas as pd
import dash
import dash_table
import dash_html_components as html

sales = pd.read_excel('sales.xls')

sales = sales.iloc[:][['Location','DOB','SDLY']]
var = ((sales['DOB']/sales['SDLY'])-1)
sales = pd.concat([sales,var], axis=1)
sales.columns=['Location','DOB','SDLY','Variance']
### if I format the data the sorting does not work ###
#sales['DOB'] = sales.iloc[0:]['DOB'].astype(int).apply("{:,}".format)
#sales['SDLY'] = sales.iloc[0:]['SDLY'].astype(int).apply("{:,}".format)
#sales['Variance'] = sales.iloc[0:]['Variance'].apply("{:.1%}".format)

app = dash.Dash(__name__)

app.layout = html.Div([
	dash_table.DataTable(
		id='table',
		data=sales.to_dict('records'),
		columns=[{'name': i, 'id': i} for i in sales.columns],
		sort_action='native'
		)
	]
)

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

Is there any workaround for this?

2

You can try leaving the data unformatted on the Python side, but use the formatting options built into DataTable - see https://dash.plot.ly/datatable/typing

3

Thanks, Alex! This should work for me.

4

Hi Alex. I’m trying to add the type and format to the table but it’s not working. Am I inserting it on the wrong line?

app.layout = html.Div([
	dash_table.DataTable(
		id='table',
		data=sales.to_dict('records'),
		columns=[[{'name': i, 'id': i} for i in sales.columns],
                'type': 'numeric',
                'format': FormatTemplate.money(0)],
		sort_action='native'
		)
	]
)
5

type and format should be specified per-column. So if they’re ALL monetary columns, something like this should work:

app.layout = html.Div([
    dash_table.DataTable(
        id='table',
        data=sales.to_dict('records'),
            columns=[{
                'name': i,
                'id': i,
                'type': 'numeric',
                'format': FormatTemplate.money(0)
            } for i in sales.columns],
            sort_action='native'
        )
    ]
)

But if there are any text or regular number fields, they would want some separate logic to change their type or format.

6

Thanks a lot for your help!