Dashboards in Python for Beginners using Dash — Exporting Data from a Dashboard

Learn Techniques to Build Professional Dashboards using Python

Eric Kleppen
Apr 23 · 11 min read
Image for post
Image for post
https://www.pexels.com/photo/working-woman-person-technology-7375/

Everyone Wants Data!

Dash Refresher

Layout

Callbacks

Necessary Installations

pip install pandas
pip install dash
pip install dash-bootstrap-components
pip install plotly

The File Structure

Image for post
Image for post
file structure

Creating the Dash App

Image for post
Image for post
The Filterable Data Table with Export button

Import Dependencies

import dash
import dash_html_components as html
import dash_core_components as dcc
import dash_table
import pandas as pd
import urllib
from dash.dependencies import Input, Output, State

Import the Data

#establish the connection to the database
conn = sqlite3.connect("Data\wine_data.sqlite")
c = conn.cursor()
#load the data into a pandas dataframe
df = pd.read_sql("select * from wine_data", conn)
#include the desired columns only
df = df[['country', 'description', 'rating', 'price','province','title','variety','winery','color']]
#display the first row of the dataframe
df.head(1)

Instantiate the Dash App

#instantiate the dash app
app = dash.Dash(__name__)
<Layout and callbacks go here>if __name__ == '__main__':
app.run_server(debug=True)

Construct the Layout for a Data Table

Data Table

#set the page size
PAGE_SIZE = 200
#build the app layout
app.layout = html.Div([
dash_table.DataTable(
id='table-sorting-filtering',
style_data={
'whiteSpace': 'normal',
'height': 'auto'
},
style_table={
'maxHeight': '800px'
,'overflowY': 'scroll'
},
columns=[
{'name': i, 'id': i} for i in df.columns
],
page_current= 0,
page_size= PAGE_SIZE,
page_action='custom',
filter_action='custom',
filter_query='',
sort_action='custom',
sort_mode='multi',
sort_by=[]
)
])# end div

Filtering Data Table Data

Filtering Operators

Image for post
Image for post
https://dash.plot.ly/datatable/filtering
operators = [['ge ', '>='],
['le ', '<='],
['lt ', '<'],
['gt ', '>'],
['ne ', '!='],
['eq ', '='],
['contains '],
['datestartswith ']]

Filtering Function

def split_filter_part(filter_part):
for operator_type in operators:
for operator in operator_type:
if operator in filter_part:
name_part, value_part = filter_part.split(operator, 1)
name = name_part[name_part.find('{') + 1: name_part.rfind('}')]
value_part = value_part.strip()
v0 = value_part[0]
if (v0 == value_part[-1] and v0 in ("'", '"', '`')):
value = value_part[1: -1].replace('\\' + v0, v0)
else:
try:
value = float(value_part)
except ValueError:
value = value_part
# word operators need spaces after them in the filter string,
# but we don't want these later
return name, operator_type[0].strip(), value
return [None] * 3

Filtering Callback

@app.callback(
Output('table-sorting-filtering', 'data'),
[Input('table-sorting-filtering', "page_current"),
Input('table-sorting-filtering', "page_size"),
Input('table-sorting-filtering', 'sort_by'),
Input('table-sorting-filtering', 'filter_query')])
def update_table(page_current, page_size, sort_by, filter):
filtering_expressions = filter.split(' && ')
dff = df
for filter_part in filtering_expressions:
col_name, operator, filter_value = split_filter_part(filter_part)
if operator in ('eq', 'ne', 'lt', 'le', 'gt', 'ge'):
# these operators match pandas series operator method names
dff = dff.loc[getattr(dff[col_name], operator)(filter_value)]
elif operator == 'contains':
dff = dff.loc[dff[col_name].str.contains(filter_value)]
elif operator == 'datestartswith':
# this is a simplification of the front-end filtering logic,
# only works with complete fields in standard format
dff = dff.loc[dff[col_name].str.startswith(filter_value)]
if len(sort_by):
dff = dff.sort_values(
[col['column_id'] for col in sort_by],
ascending=[
col['direction'] == 'asc'
for col in sort_by
],
inplace=False
)
page = page_current
size = page_size
return dff.iloc[page * size: (page + 1) * size].to_dict('records')

Extracting Data as CSV

Create a Download Link

html.A('Download CSV', id='my-link', download="data.csv",
href="",
target="_blank")

Create an Export Callback

@app.callback(Output('my-link', 'href')
, [Input('table-sorting-filtering', "page_current"),
Input('table-sorting-filtering', "page_size"),
Input('table-sorting-filtering', 'sort_by'),
Input('table-sorting-filtering', 'filter_query')])
def update_table2(page_current, page_size, sort_by, filter):
filtering_expressions = filter.split(' && ')
dff = df
for filter_part in filtering_expressions:
col_name, operator, filter_value = functions.split_filter_part(filter_part)
if operator in ('eq', 'ne', 'lt', 'le', 'gt', 'ge'):
# these operators match pandas series operator method names
dff = dff.loc[getattr(dff[col_name], operator)(filter_value)]
elif operator == 'contains':
dff = dff.loc[dff[col_name].str.contains(filter_value)]
elif operator == 'datestartswith':
# this is a simplification of the front-end filtering logic,
# only works with complete fields in standard format
dff = dff.loc[dff[col_name].str.startswith(filter_value)]
if len(sort_by):
dff = dff.sort_values(
[col['column_id'] for col in sort_by],
ascending=[
col['direction'] == 'asc'
for col in sort_by
],
inplace=False
)
csv_string = dff.to_csv(index=False, encoding='utf-8')
csv_string = "data:text/csv;charset=utf-8,%EF%BB%BF" + urllib.parse.quote(csv_string)
return csv_string

The Complete Code

Image for post
Image for post
Example of Downloading filtered data table

The Code

import dash
import dash_html_components as html
import dash_core_components as dcc
import dash_table
import pandas as pd
import sqlite3
from dash.dependencies import Input, Output, State
import urllib
#establish the connection to the database
conn = sqlite3.connect("Data\wine_data.sqlite")
c = conn.cursor()
#load the data into a pandas dataframe
df = pd.read_sql("select * from wine_data", conn)
#include the desired columns only
df = df[['country', 'description', 'rating', 'price','province','title','variety','winery','color']]
#display the first row of the dataframe
df.head(1)
#instantiate the dash app
app = dash.Dash(__name__)
PAGE_SIZE = 200#build the app layout
app.layout = html.Div([
html.H1("Wine Data"),
html.P(''),
html.P('Filter and sort the data, then export as needed.'),
html.P(''),
dash_table.DataTable(
id='table-sorting-filtering',
style_data={
'whiteSpace': 'normal',
'height': 'auto'
},
style_table={
'maxHeight': '800px'
,'overflowY': 'scroll'
},
columns=[
{'name': i, 'id': i} for i in df.columns
],
page_current= 0,
page_size= PAGE_SIZE,
page_action='custom',
filter_action='custom',
filter_query='',
sort_action='custom',
sort_mode='multi',
sort_by=[]
)
, html.Div([ html.P(' ')
,html.A('Download CSV', id='my-link', download="data.csv",
href="",
target="_blank")
])
])# end div
#list of operators for data table query
operators = [['ge ', '>='],
['le ', '<='],
['lt ', '<'],
['gt ', '>'],
['ne ', '!='],
['eq ', '='],
['contains '],
['datestartswith ']]
def split_filter_part(filter_part):
for operator_type in operators:
for operator in operator_type:
if operator in filter_part:
name_part, value_part = filter_part.split(operator, 1)
name = name_part[name_part.find('{') + 1: name_part.rfind('}')]
value_part = value_part.strip()
v0 = value_part[0]
if (v0 == value_part[-1] and v0 in ("'", '"', '`')):
value = value_part[1: -1].replace('\\' + v0, v0)
else:
try:
value = float(value_part)
except ValueError:
value = value_part
# word operators need spaces after them in the filter string,
# but we don't want these later
return name, operator_type[0].strip(), value
return [None] * 3#call back to sort and filter data table
@app.callback(
Output('table-sorting-filtering', 'data'),
[Input('table-sorting-filtering', "page_current"),
Input('table-sorting-filtering', "page_size"),
Input('table-sorting-filtering', 'sort_by'),
Input('table-sorting-filtering', 'filter_query')])
def update_table(page_current, page_size, sort_by, filter):
filtering_expressions = filter.split(' && ')
dff = df
for filter_part in filtering_expressions:
col_name, operator, filter_value = split_filter_part(filter_part)
if operator in ('eq', 'ne', 'lt', 'le', 'gt', 'ge'):
# these operators match pandas series operator method names
dff = dff.loc[getattr(dff[col_name], operator)(filter_value)]
elif operator == 'contains':
dff = dff.loc[dff[col_name].str.contains(filter_value)]
elif operator == 'datestartswith':
# this is a simplification of the front-end filtering logic,
# only works with complete fields in standard format
dff = dff.loc[dff[col_name].str.startswith(filter_value)]
if len(sort_by):
dff = dff.sort_values(
[col['column_id'] for col in sort_by],
ascending=[
col['direction'] == 'asc'
for col in sort_by
],
inplace=False
)
page = page_current
size = page_size
return dff.iloc[page * size: (page + 1) * size].to_dict('records')
#callback to filter and sort data for export link
@app.callback(Output('my-link', 'href')
, [Input('table-sorting-filtering', "page_current"),
Input('table-sorting-filtering', "page_size"),
Input('table-sorting-filtering', 'sort_by'),
Input('table-sorting-filtering', 'filter_query')])
def update_table2(page_current, page_size, sort_by, filter):
filtering_expressions = filter.split(' && ')
dff = df
for filter_part in filtering_expressions:
col_name, operator, filter_value = split_filter_part(filter_part)
if operator in ('eq', 'ne', 'lt', 'le', 'gt', 'ge'):
# these operators match pandas series operator method names
dff = dff.loc[getattr(dff[col_name], operator)(filter_value)]
elif operator == 'contains':
dff = dff.loc[dff[col_name].str.contains(filter_value)]
elif operator == 'datestartswith':
# this is a simplification of the front-end filtering logic,
# only works with complete fields in standard format
dff = dff.loc[dff[col_name].str.startswith(filter_value)]
if len(sort_by):
dff = dff.sort_values(
[col['column_id'] for col in sort_by],
ascending=[
col['direction'] == 'asc'
for col in sort_by
],
inplace=False
)
csv_string = dff.to_csv(index=False, encoding='utf-8')
csv_string = "data:text/csv;charset=utf-8,%EF%BB%BF" + urllib.parse.quote(csv_string)
return csv_string
#used for instantiating the app
if __name__ == '__main__':
app.run_server(debug=True)

The Startup

Medium's largest active publication, followed by +720K people. Follow to join our community.

Eric Kleppen

Written by

Software Product Analyst in Data Science. pythondashboards.com Top writer in Business www.linkedin.com/in/erickleppen01/

The Startup

Medium's largest active publication, followed by +720K people. Follow to join our community.

Eric Kleppen

Written by

Software Product Analyst in Data Science. pythondashboards.com Top writer in Business www.linkedin.com/in/erickleppen01/

The Startup

Medium's largest active publication, followed by +720K people. Follow to join our community.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store