Ag-Grid component with input support

[harryvde]

Hello @PablocFonseca, this is a very useful component! Thanks for sharing it!
This might already have been asked somewhere else (so apologies for duplicates if any): is there a way to get the index of cells that have been modified? Thanks very much

[Josh_Meyer]

Hello @PablocFonseca, is there an example to add streamlit sliders or filters to adjust ag grid data.

I.e. streamlit slider to change calculated column in ag grid from 0-2

Greatly appreciate your help!

Thank you

[San]

@PablocFonseca, any solution for this?

[marduk]

Hi Streamlit community (and streamlit-aggrid users )!

Has anyone found a way to clear a range selection? For example, if a user clicks outside the ag-grid. Is that even possible?

Thanks in advance.

[PablocFonseca]

Not yet…

I Looked into couple solutions, but I’m with very little time available to implement them. I’ll post here when it happens.

[rjh124]

Hi @Josh_Meyer , I’ve made an example incorporating a few useful features. Please let me know if this is what you’re looking for or you have any questions!

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

@st.cache(allow_output_mutation=True)
def load_data():
    df = pd.DataFrame({'fruit':['Apple', 'Banana'], 'unit_price':[50,25], 'num_units':[0,0], 'total_price':[0,0]})
    return df

loaded_data = load_data()

num_units = st.slider('Select Number of Units', 0, 130, 1)
loaded_data['num_units'] = num_units

js_calculate_total = JsCode("""

    function (params) {
        
         var unit_price = params.data.unit_price;
         var num_units = params.data.num_units;
         return (unit_price * num_units).toLocaleString(undefined, {minimumFractionDigits: 0, maximumFractionDigits: 0});

    }

""")

gb = GridOptionsBuilder.from_dataframe(loaded_data) #Infer basic colDefs from dataframe types
# Grid Builder configurations
gb.configure_default_column(groupable=True, value=True, enableRowGroup=True, aggFunc='sum', editable=False)
# Below, the first parameters are all the same as in the load_data dateframe, but the "header_name" is what is displayed and can be anything 
gb.configure_column("fruit", header_name='Fruit', pivot=True, sort='asc')
gb.configure_column("unit_price", header_name='Unit Price', sort='desc', type=["numericColumn","numberColumnFilter"], valueFormatter="data.estimatednotional_usd.toLocaleString(undefined, {minimumFractionDigits: 0, maximumFractionDigits: 0})", aggFunc='sum') # defines numeric column
gb.configure_column("total_price", header_name='Total Price', type=["numericColumn","numberColumnFilter"], editable=False, valueGetter=js_calculate_total) # uses custom value getter for calculated column
gridOptions = gb.build()
                            
grid_response = AgGrid(
        loaded_data,
        gridOptions = gridOptions,
        height=200,
        width='100%', # how much of the Streamlit page width this grid takes up
        update_mode=GridUpdateMode.MODEL_CHANGED,
        fit_columns_on_grid_load=True, # automatically fits all columns to be displayed in the grid in one view (doesn't always seem to work)
        allow_unsafe_jscode=True, # Set to True to allow the Javascript code to be injected
        enable_enterprise_modules=True, # enables right click and fancy features - can add license key as another parameter (license_key='string') if you have one
        key='select_grid', # stops grid from re-initialising every time the script is run
        reload_data=True, # allows modifications to loaded_data to update this same grid entity
        theme='light'
        )

Best
Ryan

[mick76]

Hi,

is it possible to remove the column menus?

Thank you

[Shawn_Pereira]

try the following line when configuring the grid:

gb.configure_column(“Your column name”, suppressMenu=True)

[Shawn_Pereira]

Hi, just thought to share this with others, in case anyone needs it:

I found a simple way to update changes made in an aggrid, to a pandas dataframe;
The code also provides a way to iterate through the aggrid. With this method, you can further selectively write to screen or update a database if you want/need to
To test this out, you will need to first create a simple csv file containing 2 columns: Name & Amt with a few entries.

The code is below:

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

vpth = “your path” # path that contains the csv file
csvfl = “tst.csv” # I used a small csv file containing 2 columns: Name & Amt
tdf = pd.read_csv(vpth + csvfl) # load csv into dataframe

gb = GridOptionsBuilder.from_dataframe(tdf)
gb.configure_column(“Name”, header_name=(“F Name”), editable=True)
gb.configure_column(“Amt”, header_name=(“Amount”), editable=True, type=[“numericColumn”,“numberColumnFilter”,“customNumericFormat”], precision=0)

gridOptions = gb.build()
dta = AgGrid(tdf,
gridOptions=gridOptions,
reload_data=False,
height=200,
editable=True,
theme=“streamlit”,
data_return_mode=DataReturnMode.AS_INPUT,
update_mode=GridUpdateMode.MODEL_CHANGED)

st.write(“Please change an amount to test this”)

if st.button(“Iterate through aggrid dataset”):
for i in range(len(dta[‘data’])): # or you can use for i in range(tdf.shape[0]):
st.caption(f"df line: {tdf.loc[i][0]} | {tdf.loc[i][1]} || AgGrid line: {dta[‘data’][‘Name’][i]} | {dta[‘data’][‘Amt’][i]}")

    # check if any change has been done to any cell in any col by writing a caption out
    if tdf.loc[i]['Name'] != dta['data']['Name'][i]:
        st.caption(f"Name column data changed from {tdf.loc[i]['Name']} to {dta['data']['Name'][i]}...")
        # consequently, you can write changes to a database if/as required

    if tdf.loc[i]['Amt'] != dta['data']['Amt'][i]:
        st.caption(f"Amt column data changed from {tdf.loc[i]['Amt']} to {dta['data']['Amt'][i]}...")

tdf = dta['data']    # overwrite df with revised aggrid data; complete dataset at one go
tdf.to_csv(vpth + 'file1.csv', index=False)  # re/write changed data to CSV if/as required
st.dataframe(tdf)    # confirm changes to df

Cheers

[mick76]

Thank you.

Is it possible to auto fit the table in a streamlit column?

[Petar]

Hi guys, how do I configure aggrid to enable RangeSelection?
I found out how to enable MultiSelection with checkboxes but it would make my life much easier if there would be a way to enable RangeSelection.

Best regards and massive thank you!
Petar

[marduk]

Hi everyone,

I am trying the ag-grid component with a key (st.session_state.aggrid_key) and reload_data=True, to get the data to reload without redrawing (I want to avoid it disappearing for a fraction of a second) See my code below.

The button I am using works normally but after making an edit in ag-grid it stops working. Do you know why? Thanks in advance!

import pandas as pd
import streamlit as st
from st_aggrid import AgGrid, GridOptionsBuilder

st.title('Football goals for each player')

if 'aggrid_key' not in st.session_state:
    st.session_state.aggrid_key = True
    st.session_state.df = pd.DataFrame({'players':['A', 'B', 'C', 'D'], 'goals':[20, 23, 19, 41]})

st.markdown('When you load the app the button works normally')

if st.button('Add one goal to each player'):
    st.session_state.df['goals'] = st.session_state.df['goals'] + 1

gb = GridOptionsBuilder.from_dataframe(st.session_state.df)       
     
gb.configure_column(field='players',editable=False)

gb.configure_column(field='goals',editable=True)

gb.configure_grid_options(stopEditingWhenCellsLoseFocus=True,headerHeight=30,rowHeight=30)

go = gb.build()

st.markdown('But if later you edit the ag-grid, the button stops working')

ag = AgGrid(
    dataframe=st.session_state.df,
    key=st.session_state.aggrid_key,
    reload_data=True,
    height=180,
    gridOptions=go,
    enable_enterprise_modules=True)

if (st.session_state.df['goals'] != ag['data'].iloc[:,1]).any():
    st.session_state.df['goals'] = ag['data'].iloc[:,1].values
    st.experimental_rerun()

[nguyenle0912]

Hello! I was wondering how did you get the conditional formatting for coloring the PRE% column? I’m currently trying to color a column cell red if the column value is <= 50% and green > 50%. My problem is that since the value is an object type not a float, I don’t know how to write the condition. I hope you could help!

[MarceTU]

Hello!,
Anyone has been able to add new rows to the Ag-grid with a small button?
I would like to do something as explained in this link: JavaScript Data Grid: Client-Side Data - Transaction Updates

[dmenini]

Hi, I am having problems with AutoHeight. Instead of resizing the grid to show exactly the number of rows in the data frame, it gets cropped.

The normal output has extra space at the bottom that I want to get rid of:

But if I use: gb.configure_auto_height(autoHeight=True), only the first rows are shown:

Am I missing something?

[marduk]

@dmenini

Hi, I am having problems with AutoHeight. Instead of resizing the grid to show exactly the number of rows in the data frame, it gets cropped.

The normal output has extra space at the bottom that I want to get rid of:

Hi @dmenini,

Just an idea -

1. Calculate beforehand the number of rows you will have in the table.
2. Set the header height and row height arguments in configure_grid_options.
3. Parametrize the total height argument in AgGrid based on the above, i.e., height = (number of rows * row height) + header height.

Hope that helps!

[timL]

Hi, does anyone have a good example of how to clear all filters? I see in the AG Grid documentation there are methods (JavaScript Data Grid: Filter API (ag-grid.com)) but I’m unsure of how to translate this to the st_aggrid package.

[rjh124]

@marduk, this also keeps happening to me. Have you found a solution?

I asked the same question a while ago and no one seems to be able to explain this behaviour, which seems to happen randomly. Reset Data in AG Grid After Edits might help though (I think it uses a setup similar to one of @PablocFonseca’s examples), but I have not yet tried the suggestions and not sure if they help!

[marduk]

Hi @rjh124 ,

Unfortunately I haven’t found a solution yet for the issue I mention a few posts above (i.e., strange behavior when using streamlit-aggrid component with a key and reload_data=True).

For now I am using streamlit-aggrid without a key, and trying to find a way so it doesn’t disappear for a split second every time it refreshes. Keen to hear if anyone has a workaround for that

For reference, here the example code again:

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

st.set_page_config(layout="wide")
st.title('Football goals for each player')
st.write('')

def add_goals():
    st.session_state.df['goals'] = st.session_state.df['goals'] + 1
    st.session_state.counter += 1

if 'aggrid_key' not in st.session_state:
    st.session_state.aggrid_key = 0
    st.session_state.counter = 0
    st.session_state.df = pd.DataFrame({'players':['A', 'B', 'C', 'D'], 'goals':[20, 23, 19, 41]})

col1,col2,col3,col4 = st.columns(4)
reload_data = False

with col1:
    st.markdown('When you load the app the button works normally:')
    bootoon = st.button('Add one goal to each player')
    if bootoon:
        add_goals()
        reload_data = True
    st.markdown('Count of times button was clicked:')
    st.write(st.session_state.counter)

with col2:
    st.markdown('Input dataframe before ag-grid:')
    st.dataframe(st.session_state.df)

with col3:
    st.markdown('But if later you edit the ag-grid, the button stops working')
    gb = GridOptionsBuilder.from_dataframe(st.session_state.df)
    gb.configure_column(field='players',editable=False)
    gb.configure_column(field='goals',editable=True)
    gb.configure_grid_options(stopEditingWhenCellsLoseFocus=True,headerHeight=30,rowHeight=30)
    go = gb.build()
    ag = AgGrid(
        dataframe=st.session_state.df,
        key=st.session_state.aggrid_key,
        reload_data=reload_data,
        height=180,
        gridOptions=go,
        enable_enterprise_modules=True)

with col4:
    st.markdown('Input dataframe after ag-grid:')
    st.dataframe(st.session_state.df)

if (st.session_state.df['goals'] != ag['data'].iloc[:,1]).any():
    st.session_state.df['goals'] = ag['data'].iloc[:,1].values
    st.experimental_rerun()

[fabmeyer]

Thanks for your example. Does anybody know how to set width of columns when using rowGroup=True?

Edit: Nevermind I got it to work with: gb.configure_default_column(editable=False, min_column_width=300)