Calculate sum totals data-table

Dash Python
1

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?

2

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

df = pd.read_csv('electricitydata.csv')

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,
                        style_header={'backgroundColor':'rgb(229,90,0)',
                                      '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
3

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