Ag-Grid component with input support

Hi All!!!.. Is there a way to do a callback using AGGRID?
– I’m having an issue updating the AGGRID table.
I have three columns (col1, col2, and col3).
– col1 (has data in it, but is also editable)
– col2 (is static, and has data in it)
– col3 (has data in it, but should equal c3 = col1 * col2),
I should be able to have a user edit COL1, then COL3 should update with the calculated value.
I can’t figure out how to update Col3 with new data in the AGGRID Table, and have it show up when I Change the COL1 value (and hit [enter]).
I think I need a callback on Cell Value Change, but I can’t find any documentation on Callbacks?
– Does anyone have any ideas, on How I can do this? any help would be very much appreciated.

and thank you so much @PablocFonseca for creating this awesome tool !!! It really fills a gap in editable, great looking tables. (please see above message, thanks again)

Hi, I am really struggled with the AgGrid parameter, what are the use cases of data_return_mode and update_mode? What are their differences?
As for GridUpdateMode: there are 7 options, NO_UPDATE, MANUAL, VALUE_CHANGED, SELECTION_CHANGED, FILTERING_CHANGED, MODEL_CHANGED. When and how a value may change in the grid, any specific for MODEL_CHANGED? Thanks!

check https://share.streamlit.io/pablocfonseca/streamlit-aggrid/main/examples/example.py?example=Virtual%20Columns.

take a look at: Ag-Grid component with input support - #261 by PablocFonseca and AgGrid — streamlit-aggrid 0.2.3 documentation

Thank you so much, I’ll give that a try…
I’ve been banging my head on a wall for three days.
– again, thanks

Again, thank you so much for that Virtual Columns Tip.
but now, of course, I have a new issue. Please see the attached image.
The First Image is the original data set.
Second Image: Is where I “edited a value” in the green column. The FPoints got calculated correctly, but the Sum accum at the top of the column, subtracted the 39, but didn’t add the 100?
Third Image: shows that the Changed values were not saved in the Dataframe? which is probably OK. but…
Fourth Image: the Results returned from AGGRID, only saved the change I made to the “Minutes” column.
– The questions are (1) “Can the Sum Accum show the new sum of the column.?” (and/or)
(2) “Can I somehow save the Calculated “FPoints” in the returned Results, so I can sum the column outside of AGGRID?” (or will I need to manipulate the Results, and add the calculated FPoints, myself)
Again, I have a feeling that this is something simple, but I just can’t figure it out.
Please see the attached image.
Thanks again, so much!!! AGGRID is such a great tool!!!

Has anybody managed to style the scrollbar of streamlit ag-grid?

I am trying with

*::-webkit-scrollbar-thumb {
    background: white !important;
}

::-webkit-scrollbar-thumb {
    background: white !important;
}

.ag-body-horizontal-scroll-viewport::-webkit-scrollbar-thumb {
    background: white !important;
}

.ag-body-horizontal-scroll-viewport {
    background: white !important;
}

… but without success

Hi, does anyone know how to change the color when you hover over rows? I don’t know how to do this via javascript injection.

Thanks.

Thank you!

Hi! Has anyone figured out how to implement rowSpan like found here? I haven’t been able to get it working.

Any update on this? I am trying to add different dynamic content in each row of the table for a dataframe

Hello Pablo
I’m using ag-grid which is amazing!
but I do have a question,
I wanted to know if it is possible to edit the ag-grid table on my streamlit web app and it will update it on the database as well
if so, can you please instruct me on how to do so?

p.s I’m using python, SQLite, and pandas.

Thanks!

Hi everyone !

Thanks for this amazing component ! I have a little issue : I would want to display a cell information when the mouse is over it as with a dataframe (see the image below). I have seen here that it’s possible to do things with the cellMouseOver event. However I don’t know how to code in Java so it’s hard for me to implement uncommon stuff for aggrid. Is it possible to do it as with a simple dataframe ?
Thanks in advance for your help !
Alexis

Hi @PablocFonseca
Is there a way to add Standard deviation as an aggFunc together with avg, sum, count, first, last, max, min under 'Values" in Pivot mode?
Your response would be greatly appreciated

Hi @usmansiddiqui98! Good question, yes! Hopefully the code attached here should be a full working example. Let me know if it doesn’t work.

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

js_sumFunction = JsCode("""

    function sumFunction(params) {
        let result = 0;
        params.values.forEach((value) => {
            if (typeof value === 'number') {
                result += value;
            }
        });
      return result;
    }

""")

js_stdev_sample = JsCode("""

    function stdevSample(params) {
    
        /* Double slash comments appear not to work here 
        
        Quick example adapted from https://www.geeksforgeeks.org/how-to-get-the-standard-deviation-of-an-array-of-numbers-using-javascript/
        
        */
    
        /* Define array and add each entry from AG Grid into the array - there is probably a better way of doing this...?*/
        let arr = [];
        params.values.forEach((value) => {
            if (typeof value === 'number') {
                arr.push(value);
            }
        });
        
        /* Calculate mean */
        let mean = arr.reduce((acc, curr)=>{
            return acc + curr
        }, 0) / arr.length;
        
        /* Calculating sum of (x-mean_x)^2 */
        arr = arr.map((k)=>{
            return (k - mean) ** 2
        });
        
        /* Apply (x-mean_x)^2 calculation for all x in the array */
        let sum = arr.reduce((acc, curr)=> acc + curr, 0);
        
        /* Variance definition of a sample (including -1 normalisation) */
        let variance = sum / (arr.length - 1);
        
        /* Sample standard deviation is square root of sample variance */
        return Math.sqrt(variance);

    }

""")

columnDefs = [
                { "field": 'Name'},
                { "field": 'Fruit'},
                { "field": 'Count', "type":["numericColumn", "numberColumnFilter"]}
            ]
gridOptions = {
    "defaultColDef":{
        "minWidth":5,
        "editable":False,
        # allow every column to be aggregated
        "enableValue": True,
        # allow every column to be grouped
        "enableRowGroup": True,
        # allow every column to be pivoted
        "enablePivot": True,
        "filter":True,
        "resizable":True,
        # "rowGroup":False,
        # "hide":False,
        # if it's not clear already, any other AG Grid gridOptions can go here - look on the website!
        },
    "columnDefs": columnDefs,
    "aggFuncs": {"sumFunction": js_sumFunction, # example sum function from AG Grid docs (https://www.ag-grid.com/react-data-grid/aggregation-custom-functions/) to show how this works - has same result as inbuilt sum but appears separately in Column Tools Panel 
                "stdevSample": js_stdev_sample
                },
    "sideBar": True,
    "animateRows": True,

}
# A fun fruit-based sample dataset
df = pd.DataFrame({'Name':['James', 'James', 'Frank', 'Catherine', 'Catherine', 'Catherine', 'Mira'], 
                   'Fruit':['Apple', 'Mango', 'Banana', 'Banana', 'Grape', 'Tomato', 'Banana'],
                   'Count':[1,3, 2,3,7, 1, 4]})
grid_response = AgGrid(
                    df,
                    gridOptions=gridOptions,
                    height=500,
                    allow_unsafe_jscode=True,
                    enable_enterprise_modules=True,
                    reload_data=False,
                    update_mode=GridUpdateMode.MODEL_CHANGED,
                    theme="light"
                )


(Only issue is the divide by zero error when number of data points=1 and the denominator of the variance is 1-1=0, but apart from that this works for me.)

1 Like

@PablocFonseca would it be possible to add the Alpine theme (like they use on the AG Grid website), please? Alternatively, how would I update the relevant parts and install locally rather than from pypi? Thanks as always!

Thanks a lot for the help. Also, I am not quite sure how to get all the columns in my dataFrame and make ‘columnDefs’ as my df has 400 columns and it would be extremely difficult to do it manually.
Once again, thank you so much for the help!

Quicker initial answer to this one from me - don’t show 400 columns in AG Grid by default! I’d think about whether they can be grouped and collapsed (look up AG Grid column grouping), and maybe start with a filtered dataset. 400 columns might also be a bit slow.

That being said, look at how I defined the columnDefs above. You can do some sort of list comprehension or just a for loop to create the field definitions dictionary and whatever dynamic parameters you like based on the particular column. e.g.

columnDefs = [{"field":column, "displayName":column.upper()} for column in df.columns]
  • this defines for each column, and formats the column name displayed (displayName) to make it upper case, as an example.

i am using the st_aggrid component with connection with database what i want is to allow the user user to edit a column value using st_aggrid than after edit is done its commit on the database how can i do this ?? because until now when user edit a value the database value doesn’t change.

code:

df = pd.read_sql_query("select * from app_db where user ='jasmin'",con)

gd=GridOptionsBuilder.from_dataframe(df)
gd.configure_column("status",editable = True)
gridoptions = gd.build()
grid_table = AgGrid(df,
                    gridOptions = gridoptions, 
                    update_mode = GridUpdateMode.SELECTION_CHANGED,
                    height = 500,
                    them = "fresh"
)

from here how to commit the update on the database??