How to edit data in tables during drilldowns (rowGroup=True)?

🧩 Custom Components streamlit-aggrid
1

Hello,

I am working on displaying data in a drilldown table.

The user should be able to filter the table descriptive colums and it should be possible to edit only some of the remaining columns. One of the editable columns is boolean (displayed as a checkbox), the others are numeric.

Now, to display the data in a user-friendly fashion I want to use a drill-down table using the rowGroup argument.

However, I do not understand how to make entries editable and parse them back to the input dataframe. In addition I would like to add the following constraints for editing:

  • Numeric columns should only be editable on the lowest drilldown level
  • The boolean column should be editable on all leveles, and higher level overwrite the state of the lower levels
  • The inital value of the checkbox should be derived from the input data
  • The aggFunc of the boolean should be just a count of the Trues.

If the table is not in drill-down format I can edit the columns and the initial state of my checkboxes is determined by the input data.

I have written the following minimal example. The app includes a switch to toggle between drill-down and non-drilldown of the aggrid table, it displays the aggrid-table and shows the resulting pandas dataframe to check if it is affected by edits in the aggrid table.

The app uses the checkboxes from Checkboxes for boolean columns in aggrid (many thanks).

import streamlit as st
import pandas as pd
from st_aggrid import AgGrid, GridOptionsBuilder, GridUpdateMode, JsCode, DataReturnMode


st.set_page_config(layout="wide")

checkbox_renderer = JsCode(
    """
class CheckboxRenderer{

    init(params) {
        this.params = params;

        this.eGui = document.createElement('input');
        this.eGui.type = 'checkbox';
        this.eGui.checked = params.value;

        this.checkedHandler = this.checkedHandler.bind(this);
        this.eGui.addEventListener('click', this.checkedHandler);
    }

    checkedHandler(e) {
        let checked = e.target.checked;
        let colId = this.params.column.colId;
        this.params.node.setDataValue(colId, checked);
    }

    getGui(params) {
        return this.eGui;
    }

    destroy(params) {
    this.eGui.removeEventListener('click', this.checkedHandler);
    }
}//end class
"""
)


def load_data() -> pd.DataFrame:
    df = pd.DataFrame(
        {
            "Item": [
                "Appel",
                "Appel",
                "Appel",
                "Banana",
                "Banana",
                "Paprika",
                "Paprika",
                "Paprika",
            ],
            "Color": [
                "Red",
                "Green",
                "Yellow",
                "Yellow",
                "Green",
                "Red",
                "Yellow",
                "Green",
            ],
            "Quantity": [4, 5, 12, 34, 5, 2, 7, 9],
            "ItemPrice": [0.1, 0.1, 0.1, 0.15, 0.15, 0.12, 0.12, 0.12],
            "Accepted": [True, False, True, True, False, True, True, False],
        },
        index=list(range(8)),
    )
    return df


if "data" not in st.session_state:
    st.session_state["data"] = load_data()


shouldDisplayPivoted = st.checkbox("Pivot data")


gb = GridOptionsBuilder()

gb.configure_default_column(
    resizable=True,
    filterable=True,
    sortable=True,
    editable=False,
)


gb.configure_column(
    field="Item",
    header_name="Item",
    width=80,
    rowGroup=shouldDisplayPivoted,
)

gb.configure_column(
    field="Color",
    header_name="Color",
    width=80,
    rowGroup=shouldDisplayPivoted,
)

gb.configure_column(
    field="ItemPrice",
    header_name="Item Price",
    width=80,
    aggFunc="avg",
    type=["numericColumn"],
    editable=True,
)

gb.configure_column(
    field="Quantity",
    header_name="Quantity",
    width=80,
    aggFunc="sum",
    type=["numericColumn"],
    editable=True,
)


gb.configure_column(
    field="Accepted",
    header_name="Accepted",
    width=50,
    type=["boolean"],
    aggFunc="max",
    cellRenderer=checkbox_renderer,
)
gb.configure_grid_options(
    pivotMode=shouldDisplayPivoted,
    suppressAggFuncInHeader=True,
    autoGroupColumnDef=dict(
        minWidth=300, pinned="left", cellRendererParams=dict(suppressCount=True)
    ),
)

for col_name in st.session_state["data"].columns:
    gb.configure_column(f"{col_name}", suppressMenu=True)


go = gb.build()

st.session_state["dta"] = AgGrid(
    st.session_state["data"],
    gridOptions=go,
    height=400,
    width="100%",
    update_mode=GridUpdateMode.MODEL_CHANGED,
    fit_columns_on_grid_load=True,
    allow_unsafe_jscode=True,
    enable_enterprise_modules=True,
    reload_data=False,
    theme="streamlit",
    data_return_mode=DataReturnMode.AS_INPUT,
)

st.session_state["data"] = st.session_state["dta"]["data"]

st.dataframe(st.session_state["data"])