Black Lives Matter. Please consider donating to Black Girls Code today.

Expand/collapse rows of datatable

Hi,

I am trying to make a datatable that the user can “drill into” somehow, for instance by collapsing or expanding rows, and I’m hoping someone can point me in the right direction.

In the example below I would like for the user to be able to click on ‘car’ or ‘bmw’ to collapse or expand the rows where those columns have the same values. How the aggregation of the strings should work does not matter so much to me. It could show an empty cell, or the first value, or anything else. I would like to be able to control how the numbers are aggregated (sum, average, weighted average, …).

import pandas as pd
import dash
import dash_table

data = [['car', 'ford', 'focus', 10],
        ['car', 'bmw', 'm3', 2],
        ['car', 'bmw', 'i3',  2],
        ['motorcycle', 'yamaha', 'td2', 3]
        ]

df = pd.DataFrame(data=data, columns=['vehicle', 'manufacturer', 'model', 'sold'])

app = dash.Dash(__name__)

app.layout = dash_table.DataTable(
    id='table',
    columns=[{"name": i, "id": i} for i in df.columns],
    data=df.to_dict('records'),
)

if __name__ == '__main__':
    app.run_server(debug=False, port=myport, host=myip)

If that is not possible I had thought about having new datatables generated when a cell was clicked, but I’m not sure where to start with that. I’m also open to other similar suggestions.

I hope this was not too unclear :slight_smile:

Hello @dhove, what you could do is to retrieve the active_cell (CTRL-F active_cell in https://dash.plot.ly/datatable/interactivity), and then do the collapse using tools provided by the Pandas library, such as query and groupby, then update the Dash Datatable accordingly. What do you think?

1 Like

Alternatively, create several DataTables and hide/show them with html.Details & html.Summary

I ended up using @Emmanuelle suggestion. Although my execution is not the most elegant in the world, it is fairly dynamic, which I needed. So thanks for the input both of you :slight_smile:

In case anyone else has similar needs, I’ll leave my solution here as a starting point for them.


import dash
from dash.dependencies import Input, Output, State
import dash_table
import dash_html_components as html
import pandas as pd
import numpy as np

def defineDF():
    data = [['car', 'ford', 'focus', 'blue', 10],
            ['car', 'ford', 'focus', 'red', 12],
            ['car', 'bmw', 'm3', 'blue', 2],
            ['car', 'bmw', 'm3', 'black', 7],
            ['car', 'bmw', 'i3', 'red', 2],
            ['car', 'bmw', 'i3', 'blue', 2],
            ['motorcycle', 'yamaha', 'td2', 'black', 3],
            ['motorcycle', 'yamaha', 'td2', 'green', 4],
            ['motorcycle', 'honda', 'crf', 'white', 9]
            ]
    
    df = pd.DataFrame(data=data, columns=['vehicle', 'manufacturer', 'model', 'color', 'sold'])
    
    return df

startDF = defineDF()

app = dash.Dash(__name__)

app.layout = html.Div([
    dash_table.DataTable(
    id='testtable',
    columns=[{"name": i, "id": i} for i in startDF.columns],
    data=startDF.to_dict('records'),
    editable=True,
    selected_rows=[],
    ),
    html.Div(id='datatable-row-ids-container')
])


@app.callback(Output('testtable', 'data'),
              [Input('testtable', 'active_cell')],
              [State('testtable', 'data'),
               State('testtable', 'columns')],
              )
def updateGrouping(active_cell, power_position, power_position_cols):
    if active_cell is None:
        returndf = defineDF()
    else:
        currentColNames = [iii['name'] for iii in power_position_cols]
        tableData = pd.DataFrame(data=power_position, columns=currentColNames)
        columnNames = tableData.columns.values.tolist()
        
        lastColNumberList = [active_cell['column'] + 1]
        groupingColList = [columnNames[:lastColNumberList[0]]]
        presentingColList = [columnNames[:lastColNumberList[0]] + [columnNames[-1]]]

        rowNumber = active_cell['row']
        rowValueList = [pd.DataFrame(data=[tableData.iloc[rowNumber, :lastColNumberList[0]].values], 
                                     columns=groupingColList[0])]
        
        cellValue = tableData.iloc[rowNumber, lastColNumberList[0] - 1]

        if cellValue is None:
            nullElements = np.where(pd.isnull(tableData))
            nullRowColumn = np.column_stack((nullElements[0].tolist(), nullElements[1].tolist()))
            nullRowColumnDF = pd.DataFrame(nullRowColumn, columns=['row', 'column'])
            nullRowColumnDF = nullRowColumnDF.drop_duplicates(subset=['row'], keep='first')
            for index, rowColPair in nullRowColumnDF.iterrows():
                currentRow = rowColPair['row']
                currentColumn = rowColPair['column']
                if currentRow == rowNumber:
                    continue
                lastColNumberList += [currentColumn]
                currentGrouping = columnNames[:currentColumn]
                groupingColList += [currentGrouping]
                presentingColList += [currentGrouping  + [columnNames[-1]]]
                rowValueList += [pd.DataFrame(data=[tableData.iloc[currentRow, :currentColumn].values], 
                                              columns=currentGrouping)]

            tableData = defineDF()

        returndf = tableData.fillna('-') 
        for iii in range(len(rowValueList)):
            rowValueDF = rowValueList[iii]
            rowValueDF = rowValueDF.fillna('-')
            groupingCols = groupingColList[iii]
            presentingCols = presentingColList[iii]
            lastColNumber = lastColNumberList[iii]
            
            numberEmptyCells = len(np.where(rowValueDF.iloc[0,:] == '-')[0])
            lastColNumber -= numberEmptyCells
            rowValueDF = rowValueDF[:(len(rowValueDF.columns)-numberEmptyCells)]
            relevantRows = pd.merge(returndf.iloc[:, :lastColNumber].reset_index(), rowValueDF, how='inner').set_index('index')
            rowsToGroup = returndf.loc[relevantRows.index, :]
            rowsNotToGroup = returndf.drop(relevantRows.index)
            
            groupedDF = rowsToGroup.groupby(groupingCols, sort=False).sum().reset_index()[presentingCols]
            lenGrouopedDF = len(groupedDF.index)
            groupedDF = groupedDF.set_index(rowsToGroup.index[:lenGrouopedDF])
            returndf = groupedDF.append(rowsNotToGroup, ignore_index=False, sort=False)[columnNames]
            returndf = returndf.sort_index(inplace=False).reset_index(drop=True)
        returndf = returndf.replace({'-': None})
        
    return returndf.to_dict('rows')


if __name__ == '__main__':
    app.run_server(debug=False)
1 Like

thank you for sharing your code! I’m glad it worked out for you.

Is there any way by which I can make accordion tables>