# Calculate sum totals data-table

I have monthly income in a dash data-table and was hoping to add rolling aggregate row with totals at the end of each year.

In excel, this would basically be something like `sum(A1:A10)`. Is there a way to do this in data-table?

Maybe, I can write a callback that calculates this and then output it as a row to data-table? Any examples out here?

Hey Keval,

Hereâ€™s a brute solution. It basically does what you say in your last line. The pandas dataframe calculates the yearly total and appends it onto the dataframe via a for loop. See below in this example I use monthly data from the energy information administration on electricity generation and usage from 1973-2020. you can find the data here save it in the same directory as where you saved/copied this script.

I didnâ€™t bother to format at all except for the styling of the total row so itâ€™s easily discernible.

Personally, depending on how big your data is, the for-loop approach isnâ€™t completely recommended. You could also maybe just do a groupby year and find a way to extract the indeces of the last date in the year before a new year and using that list of indeces insert each yearly row in- but yeah depends on how big it is.

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

filter_choices = [col for col in df.columns if col!= 'Date']

app = dash.Dash(__name__)

app.layout = html.Div([
dcc.Dropdown(
id='page-1-dropdown',
options=[{'label': i, 'value': i} for i in filter_choices],
value=filter_choices[0]),
dash_table.DataTable(
id='table',
columns = [],
style_table = {'maxHeight': '400px',
'overflowY':'scroll'},
fixed_rows={ 'headers': True, 'data': 0 },
data = [],
editable=True,
'color':'white',
'font_size':'14px'},
style_data_conditional=[
{'if': {
'filter_query': '{Status} eq 1'
},
'backgroundColor': "#3D9970",
'color':'white'},
],
style_cell={'fontSize':14, 'font-family':'Arial',
'minWidth': '15%', 'maxWidth': '20%'},
),

])

@app.callback(
[Output('table', 'data'),
Output('table','columns')],
[Input('page-1-dropdown', 'value')])

def update_data(series):
'''Changes the data shown based of filter/dropdown

'''

data = df.copy()
data['Year']=pd.to_datetime(data['Date']).dt.year

data = data[['Year','Date',series]]

data['Status'] = 0

final = pd.DataFrame()

for year in data['Year'].unique():
if final.empty:
final = data.loc[data['Year']==year]
annual_total = final.groupby(['Year'],as_index=False).sum()
annual_total['Date']='{}'.format(year)
annual_total = annual_total[[col for col in final.columns]]
annual_total['Status']=1
final = pd.concat([final,annual_total],sort=False)
else:
new_data = data.loc[data['Year']==year]
annual_total = new_data.groupby(['Year'],as_index=False).sum()
annual_total['Date']='{}'.format(year)
annual_total = annual_total[[col for col in final.columns]]
annual_total['Status']=1
yearly = pd.concat([new_data,annual_total],sort=False)
final = pd.concat([final,yearly],sort=False)

final[series]=final[series].round(2)

final.drop(columns=['Year'],inplace=True,axis=1)
Columns = [{"name": i, "id": i} for i in final.columns if i != 'Status']

final['Date'] = final['Date'].astype(str)

return final.to_dict('records'),Columns

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

really nice, was just going to ask the same question! thx