How to create a sum/ subtotal row which related to dash_table.DataTable?
The sum/subtotal column may not a must to be include in the dash_table.DataTable, it can be an external column or row unless the figure is correct and can based on the filter in the dash_table.DataTable.
Create something like excel’s subtotal.
Anyone have any idea how to do this?
Hello @beginof
You can take the input from the tables data, convert it to a pandas data frame and then df[‘sales’].sum() as your output value to the target element.
Trying to create something based on the idea here DataTable Interactivity
However, getting error message
ValueError: (‘Lengths must match to compare’, (5854,), (0,))
Below are the code I tried:
dbc.Col([
html.P("Table:",
style={"textDecoration":"underline"}),
dbc.Col([
html.Table([
html.Td('Sub', id = '',style = header_column_cell_style), #title of the column
html.Td('', id = 'subtotal', style = body_column_cell_style) #data of the column
]),
dash_table.DataTable(id='table',
columns=[
{'name': 'Today', "id": 'Date'},
{'name': 'Product', "id": 'Product'},
{'name': 'Sale', "id": 'Sale'},
],
sort_action= 'native', #"custom",
sort_mode="multi",
filter_action = "native",
row_selectable = 'multi',
# selected_rows = []
# data=df.to_dict('records')
),
])
]),
])
# table
@app.callback(
Output('table', 'data'),
Input('date_dd', 'value')
)
def update_table(selection):
if len (selection) == 0 : #and len (product) == 0
return dash.no_updates
else:
selection = datetime.strptime(selection, '%Y-%m-%d').date()
dff = df[df['Date'] == selection]
columns = dff[['Date', 'Product', 'Sale]]
data=columns.to_dict('records')
return data
@app.callback(
Output('subtotal', 'children'),
Input('table', 'derived_virtual_data'),
Input('table', 'derived_virtual_selected_rows'),
Input('date_dd', 'value')
)
def update_table(selection, rows, derived_virtual_selected_rows):
if derived_virtual_selected_rows is None and len (selection) == 0:
derived_virtual_selected_rows = []
dff = df[df['Date'] == selection]
dff1 = dff if rows is None else pd.DataFrame(rows)
subt = dff1['TPV'].sum()
return subt
Anyone can assist?
This is what I came up with:
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
@app.callback(
Output('subtotal','children'),
Input('table', "filter_query"),
State('table','data')
)
def update_table(filter, df):
dff = pd.DataFrame.from_dict(df)
if filter:
filtering_expressions = filter.split(' && ')
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)]
return dff['Sale'].sum()
This is based upon the documentation here (Back-End Filtering):
HI @jinnyzor ,
Thanks. It is a great solution.
KeyError: ‘Sale’
Besides, if I sum the amount which is already using .map('{:,.2f}'.format), the amount will not be sum and will display all the amount based on the filtered (it is seem like a list of the filtered amount). how to correct it?
If the column amount is I manual calculate in my script (which mean no include in original dataframe). it also failed to calculate the subtotal.
How about if no filter, can it show the total as well ?
Sounds like the ‘Sale’ isnt in the data.
Can you show me an example?
You’ll need to add a callback to feed the new data back to the server. I’m assuming that you are putting it into the table after it loads?
Yes, it should be showing the total even if there is no filter.
The Sale is in the original column.
Here is the column that i .map('{:,.2f}'.format) and its output.
df2[‘Sale1’]=df[‘Sale’].map(‘{:,.2f}’.format)
Output:
1,000.00,400.00,800.00
The dataframe is not the original df. Due to some of the data require to add-in, so i will create a new column for those data.
How to add a new callback to feed the new data back to server? Will it affect my database in SQL?
Okay, it should showing when all the issue solved. 
Is it possible to see the code of what you are doing? It’s kinda hard to tell how to help without seeing what you are doing with the data.
@jinnyzor
Here you go
df['year'] = pd.to_datetime(df['Date'], format = '%d/%m/%Y', errors='coerce').dt.year
df['date'] = pd.to_datetime(df['Date'], format = '%Y-%m-%d', errors='coerce')
df['date1'] = pd.to_datetime(df['Date'], format = '%Y-%m-%d', errors='coerce').dt.date
df['day'] = pd.to_datetime(df['Date'], format = '%Y-%m-%d', errors='coerce').dt.day
month_labels = {1: 'Jan', 2: 'Feb', 3: 'Mar', 4: 'Apr', 5: 'May', 6: 'Jun', 7: 'Jul', 8: 'Aug',9: 'Sep', 10: 'Oct', 11: 'Nov', 12: 'Dec'}
df['month'] = pd.to_datetime(df['Date'], format = '%Y-%m-%d', errors='coerce').dt.month
df['monthname']= df['month'].apply(lambda x: month_labels[x])
df.sort_values(by= ['year', 'month', Date', 'Product'], inplace = True)
month_cat = list(df['monthname'].unique())
sort_mm = sorted(month_cat, key=lambda m: datetime.strptime(m, "%b"))
df["pdate"] = df.date.apply(lambda x: (x - pd.DateOffset(months=1)))
df2 = df.copy()
final_df = pd.merge(left = df,right = df2, how="left", left_on=['pdate','Product'], right_on=['date','Product'])
df[['Date', 'Product', 'Sale'
, 'year' ,'month'
,'monthname', 'date'
,'date1', 'day'
, 'mtd' , 'pdate', 'lsale'
, 'lmtdsale']] = final_df[['Date_x', 'Product', 'Sale_x', 'year_x' ,'month_x' ,'monthname_x', 'date_x','date1_x'
, 'day_x' , 'mtd_x', 'pdate_x', 'Sale_y' ,'mtd_y']].fillna(0)
df['Sale1']=df['Sale'].map('{:,.2f}'.format)
df['mtd1']=df['mtd'].map('{:,.2f}'.format)
df['lmtdsale1']=df['lmtdsale'].map('{:,.2f}'.format)
dbc.Col([
html.Table([
html.Td('Sub', id = '',style = header_column_cell_style),
html.Td('', id = 'subtotal', style = body_column_cell_style)
]),
dash_table.DataTable(id='table',
columns=[
{'name': 'Today', "id": 'Date'},
{'name': 'Product', "id": 'Product'},
{'name': 'Sale', "id": 'Sale1'},
{'name': 'Current MTD Sale', "id": 'mtd1'},
{'name': 'Last MTD Sale', 'id': 'lmtdtpv2'},
],
sort_action= 'native',
sort_mode="multi",
filter_action = "native",
row_selectable = 'multi',
),
])
@app.callback(
Output('table', 'data'),
Input('date_dd', 'value')
)
def update_table(selection):
if len (selection) == 0 :
return dash.no_updates
else:
selection = datetime.strptime(selection, '%Y-%m-%d').date()
dff = df[df['date1'] == selection]
current_mtd_start = selection - pd.tseries.offsets.MonthBegin(1)
current_mtd_end = selection
last_mtd_start = current_mtd_start - pd.DateOffset(months=1)
last_mtd_end = current_mtd_end - pd.DateOffset(months=1)
last_mtd_end = pd.to_datetime(last_mtd_end, format='%Y-%m-%d')
new_df1 = df[(df['date'].dt.month==last_mtd_end.month)
& (df['date'].dt.year==last_mtd_end.year)
& (df['date']<=last_mtd_end)
]
rename = {'Sale':'sale'}
dk = new_df1.groupby(['Product','Acquirer'])[['Sale']].agg('sum').reset_index().rename(columns=rename)
df2 = pd.merge(left = dff,right = dk, how="left", left_on=['Product'], right_on=[ 'Product'])
df2['lmtdsale2']=df2['sale'].map('{:,.2f}'.format)
columns = df2[['Date', 'Product', Sale1', 'mtd1', 'lmtdtpv2' ]]
data= columns.to_dict('records')
return data
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
@app.callback(
Output('subtotal','children'),
Input('table', "filter_query"),
State('table','data')
)
def update_table(filter, df):
dff = pd.DataFrame.from_dict(df2)
if filter:
filtering_expressions = filter.split(' && ')
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)]
return df2['Sale1'].sum()
Try:
return df2['Sale1'].astype(float).sum()
There are a couple of ways to send info back to a SQL server, but not in scope of this topic. 
fail, same error message.
Try this, it looks like you were trying to use the old dataframe instead of the table.
def update_table(filter, df):
dff = pd.DataFrame.from_dict(df)
if filter:
filtering_expressions = filter.split(' && ')
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)]
return df2['Sale1'].astype(float).sum()
Still failed.