Ag-Grid delete selected rows then update the Ag-Grid table and save the selected rows to new DataFrame

Custom Components streamlit-aggrid
1

Dear all,

I’m trying to delete selected rows in the Ag-Grid table and after deletion update/refresh the Ag-Grid table. Also I would like to save the selected rows to the new DataFrame.

It that possible to do with just one button click (on click on the button the Ag-Grid table updates, deletes the selected rows and saves them to DataFrame)?

Thank you in advance!

Best regards,
Jaša

Code:

df = pd.read_excel('test.xlsx', sheet_name='base_table')

js = JsCode("""
    function(e) {
        let api = e.api;     
        let sel = api.getSelectedRows();
        api.applyTransaction({remove: sel});
    };
    """)

gb_base = GridOptionsBuilder.from_dataframe(df, enableRowGroup=True, editable=True, groupable = True)
gb_base.configure_pagination(enabled=True, paginationAutoPageSize = False, paginationPageSize=20)
gb_base.configure_selection('multiple', use_checkbox=True)
gb_base.configure_grid_options(onRowSelected = js, pre_selected_rows = []) 
grid_options_base = gb_base.build()
table_base = AgGrid(df, gridOptions=grid_options_base, allow_unsafe_jscode=True, fit_columns_on_grid_load=True)
1 Like
2

Hi,

I managed to get a general case to work. I might need some changing to your case, however it should be fairly easy.

import streamlit as st
from st_aggrid import AgGrid, GridOptionsBuilder, DataReturnMode, AgGridTheme
import pandas as pd
from pandas.api.types import is_bool_dtype, is_numeric_dtype
import numpy as np


def get_blank_from_dtype(dtype):
    """Return correct values for the new line: 0 if column is numeric, "" if column is object, ..."""
    if is_numeric_dtype(dtype):
        return 0
    elif is_bool_dtype(dtype):
        return False
    else:
        return ""


def add_row(df):
    df = df.append(
        pd.Series(
            [
                get_blank_from_dtype(dtype)
                for dtype in df.dtypes
            ],
            index=df.columns,
        ),
        ignore_index=True,
    )
    return df


def delete_row(df, grid):
    selected_rows = grid['selected_rows']
    if selected_rows:
        selected_indices = [i['_selectedRowNodeInfo']
                            ['nodeRowIndex'] for i in selected_rows]
        df_indices = st.session_state.df_for_grid.index[selected_indices]
        df = df.drop(df_indices)
    return df


def create_grid(df):

    gb = GridOptionsBuilder.from_dataframe(df)
    gb.configure_default_column(editable=True, filter=True, resizable=True, sortable=True, value=True, enableRowGroup=True,
                                enablePivot=True, enableValue=True, floatingFilter=True, aggFunc='sum', flex=1, minWidth=150, width=150, maxWidth=200)
    gb.configure_selection(selection_mode='multiple', use_checkbox=True)
    gridOptions = gb.build()
    grid = AgGrid(
        df,
        gridOptions=gridOptions,
        data_return_mode=DataReturnMode.AS_INPUT,
        update_on='MANUAL',
        fit_columns_on_grid_load=True,
        theme=AgGridTheme.STREAMLIT,  # Add theme color to the table
        enable_enterprise_modules=True,
        height=600,
        width='100%',
    )

    return grid


def main():
    # Initialize your dataframe
    if "df_for_grid" not in st.session_state:
        st.session_state.df_for_grid = pd.DataFrame({"A": [1, 2], "B": [3, 4]})
    add_row_button = st.button("➕ Row")
    delete_row_button = st.button("➖ Row")
    if add_row_button:
        st.session_state.df_for_grid = add_row(st.session_state.df_for_grid)
    grid = create_grid(st.session_state.df_for_grid)
    if delete_row_button:
        st.session_state.df_for_grid = delete_row(
            st.session_state.df_for_grid, grid)


if __name__ == "__main__":
    main()
2 Likes
3

@asl Thank you very much for your answer!

I changed your code appropriately for my case and it works!

However I still have two minor problems:

  1. In order to delete the selected rows I must click on the -Row button twice
  2. I want to change/edit the rows and when I remove them I want to save them into a DataFrame for further manipulation. This saving proccess is still causing me some problems.

Help would be very much appreciated :slight_smile:
Jaša

1 Like
4

@Jasa_Dimic

Regarding the first point: It actually deletes the row after one click, however due to how I setup the logic, the grid isn’t reloaded immediately. Haven’t had the time to figure out a clever way to do this and instantly reload the grid.
And with regards to saving the changes, you can simply create a method that writes the grid['data'] to a new dataframe (or even the same dataframe).

However, it looks like Streamlit will be releasing some rather useful features for editable dataframes within this month, as per their roadmap.

It might be worth waiting for that :slight_smile:

1 Like
5

@asl thank you again for your help and response!

The new features look very promising :slight_smile:

6

I tried above code it was working fine but now I am seeing below error recently:
ValueError: The truth value of a DataFrame is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

This is occuring at line at if selected_rows: code in delete_row function. Has this functionality changed recently?