So for an app I’m building, I’m trying to create a pivot table object. The goal would be to have it operate similar to an Excel pivot, allowing users to select rows to group by, columns to pivot on, values to aggregate, and filters to…well, filter.
The rows, columns, values and aggregation types work just fine. I’m also able to display and hide dropdowns for different columns I want to filter on. However, as soon as I try to populate those dropdowns with the unique column values, just about everything breaks. Uncommenting the commented block causes the row and column dropdowns to display nothing when selected. It also causes the filter dropdowns to no longer show properly.
I changed the “Columns to filter on” dropdown to require submission before the callback executes in hopes that it would fix the problem, but no dice. Keeping it for now.
Thanks for any suggestions anyone might have!
import dash
import dash_core_components as dcc
import dash_table
import dash_html_components as html
from dash.dependencies import Input, Output, State
import socket
import string
import pandas as pd
import numpy as np
hostname = socket.gethostname()
myIP = socket.gethostbyname(hostname)
df = pd.read_csv(
'https://data.montgomerycountymd.gov/api/views/4mse-ku6q/rows.csv'
)
external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']
app = dash.Dash(__name__, external_stylesheets=external_stylesheets)
class dash_pivot_table(object):
aggregation_list = ['Sum','Count','Mean','Max','Min','Median','Mode']
value_display_types = [None]
filter_count = 10
numeric_functions = ['sum','mean','max','min','mode']
def __init__(self,df,pivot_name):
self.df = df
self.pivot_name = pivot_name
self.numeric_columns = None
def translate_to_id(self,name):
return name.translate(str.maketrans('','',string.punctuation)).lower().replace(' ', '_')
def create_pivot_dropdown(self,name,columns,multiple_values=True):
return html.Div([dcc.Markdown(name),
dcc.Dropdown(
id=self.pivot_name + '_' + self.translate_to_id(name),
options=[{'label': i, 'value': i} for i in columns],
multi=multiple_values,
placeholder = name
)]
)
def create_filter_dropdown(self,name,multiple_values=True):
return html.Div([
dcc.Dropdown(
id=self.pivot_name + '_' + self.translate_to_id(name),
options=[],
style={'display' : 'none'},
multi=multiple_values,
placeholder = name
)]
)
def generate_dash_table(self,df, max_rows=10):
return dash_table.DataTable(
id=self.pivot_name +'_datatable-interactivity',
columns=[
{"name": i, "id": i, "deletable": True} for i in df.columns
],
data = [],
editable=True,
filtering=True,
sorting=True,
sorting_type="multi",
row_selectable="multi",
row_deletable=True,
selected_rows=[],
pagination_mode="fe",
pagination_settings={
"displayed_pages": 1,
"current_page": 0,
"page_size": 35,
},
navigation="page",
)
def generate_pivot_table(self,dff,pivot_values,pivot_index,pivot_columns,pivot_aggfunc='count'):
return pd.pivot_table(dff,values=pivot_values,index=pivot_index,columns=pivot_columns,aggfunc=pivot_aggfunc.lower(),fill_value = 'nan').reset_index()
def generate_filter_dropdowns(self):
filter_layout = []
for i in range(self.filter_count):
filter_layout.append(self.create_filter_dropdown('Filter Columns ' + str(i)))
return html.Div(filter_layout)
def setup_pivot_table(self,categorical_cutoff=50):
categorical_columns = [col for col in self.df.columns if self.df[col].nunique() < categorical_cutoff]
self.numeric_columns = [col for col in self.df.columns if np.issubdtype(self.df[col].dtype, np.number)]
return html.Div([
html.Div([
html.Div([
self.create_pivot_dropdown('Filter Columns',categorical_columns),
html.Button(id='submit-button',n_clicks=0,children = 'Submit'),
self.generate_filter_dropdowns(),
self.create_pivot_dropdown('Aggregate by...',self.aggregation_list,multiple_values=False),
# self.create_pivot_dropdown('Show values as...',self.value_display_types,multiple_values=False)
],
style={'width': '48%', 'display': 'inline-block'})
,
html.Div([
self.create_pivot_dropdown('Rows',categorical_columns),
self.create_pivot_dropdown('Columns',categorical_columns),
self.create_pivot_dropdown('Values',self.df.columns)
],
style={'width': '48%', 'display': 'inline-block'})
,
dcc.ConfirmDialog(
id=self.pivot_name + '_confirm',
message='Note: no more than ' + str(self.filter_count) + ' filter columns allowed.',
)
])
,
html.Div(self.generate_dash_table(df))]
)
my_pivot_table = dash_pivot_table(df,'test_pivot')
filter_count = my_pivot_table.filter_count
pivot_name = my_pivot_table.pivot_name
app.layout = html.Div(
my_pivot_table.setup_pivot_table()
)
filter_name_arr = [pivot_name + '_filter_columns_' + str(i) for i in range(filter_count)]
@app.callback([Output(filter_name,'style') for filter_name in filter_name_arr],
[Input('submit-button','n_clicks')],
[State(pivot_name + '_filter_columns','value')])
def update_filters(n_clicks,filter_list):
return_array = []
#If we are wanting to filter...
if filter_list:
iter_range = len(filter_list)
#Cap it at the allotted limit
if iter_range >filter_count:
filter_list = filter_list[:filter_count]
#For each selected filter
#We want to set the style to block (so that the filters are no longer hidden)
for i in range(iter_range):
return_array.append({'display' : 'block'})
#Hide dropdowns that we don't want to display
for i in range(iter_range,filter_count):
return_array.append({'display' : 'none'})
return return_array
else:
#If there is no filter, we can just populate all the filter columns with None
return return_array + [{'display' : 'none'}]*filter_count
@app.callback([Output(filter_name,'placeholder') for filter_name in filter_name_arr],
[Input('submit-button','n_clicks')],
[State(pivot_name + '_filter_columns','value')])
def update_placeholders(n_clicks,filter_list):
return_array = []
#If we are wanting to filter...
if filter_list:
#Get the number of filters we'll need
iter_range = len(filter_list)
#Cap it at the allotted limit
if iter_range >filter_count:
filter_list = filter_list[:filter_count]
#Change placeholder text to display the column being filtered
for i in range(iter_range):
return_array.append(filter_list[i])
#
for i in range(iter_range,filter_count):
return_array.append(None)
return return_array
else:
return return_array + [None]*filter_count
#@app.callback([Output(filter_name,'options') for filter_name in filter_name_arr],
# [Input('submit-button','n_clicks')],
# [State(pivot_name + '_filter_columns','value')])
#
#def populate_filters(n_clicks,filter_list):
# return_array = []
#
# #If we are wanting to filter...
# if filter_list:
# #Populated the filter with options from the filter column
# #Cap it at the allotted limit
# iter_range = len(filter_list)
# if iter_range >filter_count:
# filter_list = filter_list[:filter_count]
# ##CHANGE NAME IN CASE OF DIFFERENT TABLE OBJECT NAME
# for i in range(iter_range):
# col_dicts = [{'label': j, 'value': j} for j in list(my_pivot_table.df[filter_list[i]].unique())]
# return_array.append(col_dicts)
# # We populate all other dropdowns with blanks
# for i in range(iter_range,filter_count):
# return_array.append([None])
# return return_array
# else:
# #If there is no filter, we can just populate all the filter columns with None
# return [None]*filter_count
#
###BE SURE TO CHANGE THE INPUT FOR THE PIVOT TABLE
@app.callback([
Output(pivot_name + '_datatable-interactivity', 'columns'),
Output(pivot_name + '_datatable-interactivity', 'data')],
[
Input(pivot_name + '_aggregate_by', 'value'),
# Input(pivot_name + '_show_values_as', 'value'),
Input(pivot_name + '_rows', 'value'),
Input(pivot_name + '_columns', 'value'),
Input(pivot_name + '_values', 'value'),
Input(pivot_name + '_filter_columns','value')]
+
[Input(filter_name,'value') for filter_name in filter_name_arr] +
[Input(filter_name,'style') for filter_name in filter_name_arr])
def update_dash_table(
table_agg_func,
# table_show_values,
table_rows,
table_columns,
table_values,
table_filter,
*args):
##CHANGE NAME IN CASE OF DIFFERENT TABLE OBJECT NAME
dff = my_pivot_table.df.copy()
if (table_rows or table_columns) and table_agg_func:
##CHANGE NAME IN CASE OF DIFFERENT TABLE OBJECT NAME
if table_values is not None:
if not set(table_values).issubset(my_pivot_table.numeric_columns):
if str(table_agg_func).lower() in my_pivot_table.numeric_functions:
return [],{}
else:
table_values = my_pivot_table.numeric_columns
pivot_update = my_pivot_table.generate_pivot_table(dff,table_values,table_rows,table_columns,table_agg_func)
elif (not table_rows and not table_columns ) and table_agg_func:
if table_agg_func.lower() in my_pivot_table.numeric_functions:
pivot_update = dff[table_values].agg(table_agg_func.lower()).reset_index()
else:
return [],{}
pivot_update.index.name = None
pivot_col=[
{"name": ' '.join(map(str,i)), "id": ' '.join(map(str,i)), "deletable": True} if isinstance(i,tuple) else
`{"name": i, "id": i, "deletable": True} for i in pivot_update.columns
]
pivot_dict = pivot_update.to_dict('rows')
for dict_iter in pivot_dict:
for key in list(dict_iter):
if isinstance(key,tuple):
tempKey = ' '.join(map(str,key))
dict_iter[tempKey] = dict_iter[key]
del dict_iter[key]
return pivot_col, pivot_dict
if __name__ == '__main__':
app.run_server(debug=False,host=myIP)