Ag-Grid component with input support

sis · July 16, 2021, 12:00pm

@rchamila @dxz6228 Don’t know if it’s still relevant to you, but I am able to get rows selection work via the below snippet

def display_table(df: pd.DataFrame) -> AgGrid:
    # Configure AgGrid options
    gb = GridOptionsBuilder.from_dataframe(df)
    gb.configure_selection('single')
    return AgGrid(
        df,
        gridOptions=gb.build(),
        # this override the default VALUE_CHANGED
        update_mode=GridUpdateMode.MODEL_CHANGED
        )

Hope this helps!

rchamila · July 16, 2021, 4:44pm

Thansk @sis , Yeah it was helpful and managed to fix the issue with row selected. But I just want to delete these selected rows and display the updated table. Any thoughts on how to do that?

sis · July 21, 2021, 8:12am

@rchamila a possible solution to your case is given by the below snippet (consider that I am no expert in neither AG Grid nor streamlit-aggrid components, so maybe some implementations can be improved)

It works for both single and multiple rows selection.

Snippet

import string

import numpy as np
import pandas as pd
import streamlit as st
from st_aggrid import AgGrid, GridOptionsBuilder, GridUpdateMode

st.set_page_config(layout='wide')


def display_table(df: pd.DataFrame, selection: str) -> AgGrid:
    # Configure AgGrid options
    gb = GridOptionsBuilder.from_dataframe(df)
    gb.configure_selection(selection)
    st.write(f"Dataframe shape: {df.shape}")
    return AgGrid(
        df,
        gridOptions=gb.build(),
        # this override the default VALUE_CHANGED
        update_mode=GridUpdateMode.MODEL_CHANGED
    )


# Define dummy data
rng = np.random.default_rng(2021)
N_SAMPLES = 100
N_FEATURES = 10
df = pd.DataFrame(rng.integers(0, N_SAMPLES, size=(
    N_SAMPLES, N_FEATURES)), columns=list(string.ascii_uppercase[:N_FEATURES]))

cols = st.beta_columns(2)

with cols[0]:

    st.markdown('# 🠔 Before')

    # Display AgGrid from data and write response
    st.markdown("### 1️⃣ Let's display dummy data through AgGrid")
    selection = st.radio('Selection mode', ['single', 'multiple'])
    response = display_table(df, selection=selection)

    st.markdown(
        "### 2️⃣ AgGrid response contains `data` (original df) and `selected_rows`")
    for k, v in response.items():
        st.write(k, v)

with cols[1]:

    st.markdown('# 🠖 After')

    # Retrieve selected rows indices
    st.markdown(
        "### 3️⃣ From selected rows we can obtain dataframe indices to drop")
    data = response['data'].to_dict(orient='records')
    indices = [data.index(row) for row in response['selected_rows']]
    st.write(f"Selected rows are located at indices: {indices}")

    # Use retrieved indices to remove corresponding rows from dataframe
    st.markdown(
        "### 4️⃣ Display the updated dataframe where rows have been removed")
    _df = df.drop(indices, axis=0)
    st.write(f"Dataframe shape: {_df.shape}")
    AgGrid(_df)

rchamila · July 21, 2021, 8:59am

Thanks @sis, Will that update the same AgGrid ? or will that shows a new one? I want to update the same AgGrid.

sis · July 21, 2021, 11:24am

@rchamila the provided snippet will create and display a new pandas dataframe based on:

the original one
rows selected for removal

If you need to update the original dataframe, it should be sufficient to change last three codelines by overwriting df rather than create _df

rchamila · July 21, 2021, 12:27pm

@sis , Thanks for trying to sort this out. What I need is to update the same grid in the UI. Rather than having a separate upgraded grid.

With your solution, seems it shows a new grid as shown below screenshot

sis · July 21, 2021, 5:36pm

@rchamila following this example in streamlit-aggrid repo and this blog post, I found a way to achieve what you need (interactive row deletion) through custom JavaScript injection

Solution

import string

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

st.set_page_config(layout='wide')


def display_table(df: pd.DataFrame) -> AgGrid:
    # Configure AgGrid options
    gb = GridOptionsBuilder.from_dataframe(df)
    gb.configure_selection('single', use_checkbox=True)
    
    # Custom JS code for interactive rows deletion
    # For credits SEE: 
    # https://github.com/PablocFonseca/streamlit-aggrid/blob/1acb526ba43b5aac9c8eb22cc54eeb05696cd84d/examples/example_highlight_change.py#L21
    # https://ag-grid.zendesk.com/hc/en-us/articles/360020160932-Removing-selected-rows-or-cells-when-Backspace-or-Delete-is-pressed
    js = JsCode("""
    function(e) {
        let api = e.api;        
        let sel = api.getSelectedRows();
        
        api.applyTransaction({remove: sel});
    };
    """)
    gb.configure_grid_options(onRowSelected=js) 
    return AgGrid(
        df,
        gridOptions=gb.build(),
        # this override the default VALUE_CHANGED
        update_mode=GridUpdateMode.MODEL_CHANGED,
        # needed for js injection
        allow_unsafe_jscode=True
    )


# Define dummy data
rng = np.random.default_rng(2021)
N_SAMPLES = 100
N_FEATURES = 10
df = pd.DataFrame(rng.integers(0, N_SAMPLES, size=(
    N_SAMPLES, N_FEATURES)), columns=list(string.ascii_uppercase[:N_FEATURES]))

st.info("Select a row to remove it")
response = display_table(df)
st.write(f"Dataframe shape: {response['data'].shape}")

rchamila · July 25, 2021, 12:42pm

Genius, That sounds promising. Any idea what would be the event of Update button click? . Then I can set it to the grid options instead of onRowSelected event.

Thanks
Chamila

sis · July 25, 2021, 1:02pm

Nope, unfortunately as I said I’m just discovering AgGrid. Maybe component creator @PablocFonseca can help you.

PablocFonseca · July 30, 2021, 6:34pm

Finally I had some spare time, and updated to version 0.2.0, which supports theming. Checkout the examples folder

phelipefrancesco · July 31, 2021, 9:21pm

Hi, I’m wondering if it is possible to color whole rows based on values in a single column. Does anyone know?

Pierre · August 10, 2021, 3:24pm

Hi @PablocFonseca, thanks for all your work, it’s super useful!
I’m struggling with columns formatting. I’m not able to format columns as percentages, and not able to have a thousand separators for numbers. Do you have a solution for this?
Thanks a lot!

rjh124 · August 13, 2021, 4:40pm

Hi @Pierre!

To get comma separators (or full stop separators depending on locale (i.e. your browser’s language settings)), using the gridbuilder package as in the examples, for the column you’re talking about try something like:

gb.configure_column("numeric_column_a", header_name='Numeric Column A', type=["numericColumn","numberColumnFilter"], valueFormatter="data.numeric_column_a.toLocaleString(undefined, {minimumFractionDigits: 0, maximumFractionDigits: 0})", aggFunc='sum')

I believe it should work in most browsers. As for percentages, I often use percentages in column headings for ease, but it can help readability so it’s understandable to want them within the table! If your number is between 0 and 1, to show as a percentage use something like:

gb.configure_column("percentage_column_b", header_name='Percentage Column B', type=["numericColumn","numberColumnFilter"], valueFormatter="(data.percentage_column_b*100).toFixed(1)+'%', aggFunc='sum')

Hope this helps!

PeterT · September 2, 2021, 1:33pm

Hi @PablocFonseca

Again, awesome work on this component.

I have a question: Is it currently possible to change the text in the “Update” button that it shown when update_mode is set to “MANUAL”?

Best,
Peter

PablocFonseca · September 2, 2021, 1:55pm

Hello!

No, it’s not…

but this feature became obsolete with the introduction of streamlit forms. You can wrap the grid in a form and then place a submit button instead.

PeterT · September 2, 2021, 2:12pm

Awesome @PablocFonseca ! That was just what I needed And I see upon upgrading to 0.2.0 that themes are now also supported, sweet!

mojakus · September 4, 2021, 12:50am

For comma separators, this worked better for me:

valueFormatter="parseFloat(data.numeric_column_a).toLocaleString('en',{minimumFractionDigits: 0,  maximumFractionDigits: 2})", aggFunc='sum')

joe · September 13, 2021, 1:17pm

@PablocFonseca this is amazing… By far the best streamlit table option out there. Thanks for your hard work making this available to all of us!

Is there a way to perform conditional formatting based on cell contents? e.g. if a cell contains “gold” I want the background color to be gold.

There’s a nice tutorial from AG Grid here:
https://blog.ag-grid.com/conditional-formatting-for-cells-in-ag-grid/

But I couldn’t find a way to inject CSS into the table in order to do something like this.

PablocFonseca · September 13, 2021, 1:31pm

You’d need to inject some JSCode for this

I do that on streamlit-aggrid/main_example.py at main · PablocFonseca/streamlit-aggrid · GitHub. line 97

cellsytle_jscode = JsCode("""
function(params) {
    if (params.value == 'A') {
        return {
            'color': 'white',
            'backgroundColor': 'darkred'
        }
    } else {
        return {
            'color': 'black',
            'backgroundColor': 'white'
        }
    }
};
""")
gb.configure_column("group", cellStyle=cellsytle_jscode)

... (later on)...

grid_response = AgGrid(
    ...
    allow_unsafe_jscode=True, #Set it to True to allow jsfunction to be injected
    )

joe · September 13, 2021, 1:45pm

That helps a lot! I think I got distracted by the CSS route. This looks easier–I’ll give it a try. Cheers mate!!

Topic		Replies	Views
Aggrid return dataframe with removed columns streamlit-aggrid aggrid	0	318	January 2, 2023
Streamlit aggrid retain selected rows streamlit-aggrid	0	521	October 31, 2022
Streamlit-aggrid refresh streamlit-aggrid	1	433	April 13, 2023
Editable Table / User configurable table Custom Components	4	4316	August 15, 2022
Table or dataframe with Search field Show the Community!	3	1318	June 22, 2023

Ag-Grid component with input support

Related topics