AG-grid has avg as in average built-in aggregation function:
"aggFunc": "avg"
I am trying to write a custom function that woud return “avg. value” + (“RSD value” %).
I cannot find how ag-grid has structured their bult-in funcsion such as average mentioned above. Has anybody found where built-in funcions are located at? This would be a good starting point to write my wanted custom agg funcion I believe.
Hello @davzup89,
You can check here to see how one custom function is built:
This is the test that it pertains to:
And in the docs, check here:
i’ve managed to make it work. If it is of any use to someone:
dagfuncs.customAvgAggFunc = function(params) {
let sum = 0;
let count = 0;
let squaredDifferences = 0;
params.values.forEach((value) => {
const groupNode =
value !== null && value !== undefined && typeof value === 'object';
if (groupNode) {
// We are aggregating groups, so we take the
// aggregated values to calculate a weighted average.
sum += value.avg * value.count;
count += value.count;
} else {
// Skip values that are not numbers (i.e., skip empty values).
if (typeof value === 'number') {
sum += value;
count++;
}
}
});
let avg = null;
if (count !== 0) {
avg = sum / count;
}
// Calculate the squared differences from the average
params.values.forEach((value) => {
if (typeof value === 'number') {
squaredDifferences += Math.pow(value - avg, 2);
}
});
// Compute the standard deviation (using sample standard deviation formula)
let variance = squaredDifferences / (count - 1);
let standardDeviation = Math.sqrt(variance);
// Compute the RSD
let rsd = (avg === 0) ? 0 : (standardDeviation / avg) * 100;
const result = {
count: count,
avg: avg,
rsd: rsd,
toString: function() {
return (this.avg === 0) ? `${Math.round(this.avg)}` : `${Math.round(this.avg)} (${this.rsd.toFixed(1)}% RSD)`;
}
};
return result;
};
dagfuncs.roundedCustomAvgAggFunc = function(params) {
const result = dagfuncs.customAvgAggFunc(params);
if (result.avg !== null) {
result.avg = Math.round(result.avg);
result.rsd = parseFloat(result.rsd.toFixed(1));
}
return result;
};
However, I am also using valueFormatter on my columns:
"valueFormatter": {"function":"Number(params.value).toFixed(0)"}
} for i in display_columns]
This means that valueFormatter now renders new avg+rsd values to nan, since it can’t handle strings. Any ideas how to apply valueFormatter only to rows that aren’t aggregated or how to valueFormat only number values but not string ones?
Also, my custom agg function renders all headervalues as func(value), I would like to replace func part with smth else but haven’t figured it out how to do this yet.
You can always make the valueFormatter a JS function that is setup to return the proper value, something like this might work:
dagfuncs.avgRSD = (value) => {
if (parseInt(value)) {
return Number(value).toFixed(0)
} else if (value == 0) {
return Number(0).toFixed(0)
}
return value
}
Then in your valueFormatter:
"valueFormatter": {"function": "avgRSD(params.value)"}
As far as the headername, just pass it a headerName for the colDef
My solution is slightly different but works nontheless:
dagfuncs.Formatter2 = function(v) {
if (typeof v !== 'number') {
return v;
}
return Math.round(v).toString();
};
Any way to format headers of agg columns? By default they show func(header_value). I would like to substitute func part
dashGridOptions={
"suppressAggFuncInHeader": True,
}
According to this:
One more question regarding custom agg functions that comes to mind, is it possible to define a function behavior based on the condition if the checkbox in a row is selected or not?
I would think so.
But not sure how to do it.
Trying to incorporate the following angular example into my customAvgAggFunc:
this.columnDefs = [
{
headerName: 'Name',
field: 'testName',
checkboxSelection: true, //HERE !!!!
width: 150
}
someMethod() {
let selectedRows;
selectedRows = this.gridApi.getSelectedRows();
console.log(selectedRows);
///than you can map your selectedRows
selectedRows.map((row) => {
console.log(row);
console.log(row.data);
});
}
Probably making some syntax errors, since I can’t get the result. Do you maybe know what is the equivalent of “this.” in dag? Or do I just omit it?
Is it even possible to call gridAPI from DAG?
Yes.
Using this is possible, however, if you have a big selection, it can be quite expensive to run through all the rows.
Also, the grids api is normally available in the params of whatever.
And yes, there is something that will allow you to get the grid’s api, you can use the clientside function in JS, window.dash_ag_grid.getApi and pass it the stringified id of the grid.
For the minimal example here:
import dash
from dash import html, dcc
import dash_ag_grid as dag
import pandas as pd
app = dash.Dash(__name__)
df = pd.read_csv(
"testcsv.csv"
)
columnDefs = [
# Row group by country and by year is enabled.
{
"field": "country",
"rowGroup": True,
"hide": True,
"suppressColumnsToolPanel": True,
},
{
"field": "sport",
"rowGroup": False,
"hide": True,
"suppressColumnsToolPanel": True,
},
{"field": "gold", "sortable": True, "filter": True, "aggFunc": {"function": "customAvgAggFunc(params)"}},
]
app.layout = html.Div(
[
dcc.Markdown(
"This is an example of how to calculate a ratio using values from multiple columns."
),
dag.AgGrid(
enableEnterpriseModules=True,
columnDefs=columnDefs,
rowData=df.to_dict("records"),
defaultColDef={"resizable":True},
dashGridOptions={
"groupDisplayType": "singleColumn",
"autoGroupColumnDef": {
"headerName": "Country",
"flex": 10,
"checkboxSelection": True,
},
"rowSelection": "multiple",
"suppressAggFuncInHeader": True,
"groupSelectsChildren": True,
"suppressRowClickSelection": True,
}
),
]
)
if __name__ == "__main__":
app.run_server(debug=False)
with customagg function:
dagfuncs.customAvgAggFunc = function(params) {
let sum = 0;
let count = 0;
params.values.forEach((value) => {
const groupNode =
value !== null && value !== undefined && typeof value === 'object';
if (groupNode) {
sum += value.avg * value.count;
count += value.count;
} else if (typeof value === 'number') {
sum += value;
count++;
}
});
return (count !== 0) ? sum / count : null;
};
I get the following console reading:
rowNode: e
|---> childrenAfterGroup: Array(x)
|---> x: e
|---> childrenAfterGroup: Array(x)
|---> x: e
|---> selected: true
Thus, I tried to access childrenAfterGroup[1] like:
dagfuncs.customAvgAggFunc = function(params) {
let sum = 0;
let count = 0;
params.values.forEach((value) => {
const groupNode =
value !== null && value !== undefined && typeof value === 'object';
if (groupNode) {
const secondChild = value.childrenAfterGroup[1];
// Check if the secondChild exists and is selected
if (secondChild && secondChild.selected) {
sum += value.avg * value.count;
count += value.count;
}
} else if (typeof value === 'number') {
sum += value;
count++;
}
});
return (count !== 0) ? sum / count : null;
};
but with this I cannot access the selected parameter and return average only for selected rows. Could it even be done this way supposed my code would actually get the status of selected?
You only want to perform the calculation on selected rows if there are selected rows in the group?
It doesnt look like any calculation is rendered after the grid initially shows the data. So not sure you can do anything with it.
The issue you are running into above is that the values are just going to be the values that are rolled up into the calculation, this wont have anything about the children of the row grouping.
Using simple function renders averages in grid
dagfuncs.customAvgAggFunc = function(params) {
let sum = 0;
let count = 0;
params.values.forEach((value) => {
const groupNode =
value !== null && value !== undefined && typeof value === 'object';
if (groupNode) {
sum += value.avg * value.count;
count += value.count;
} else if (typeof value === 'number') {
sum += value;
count++;
}
});
return (count !== 0) ? sum / count : null;
};
I want to achive dynamic calculation of average where only selected rows (checkbox is selected) are included in calculation of average
You cant make it so the selection adjustments force it to recalculate the aggregation.
The only way would be to calculate the selections into a new grid or component, averaging the way that you want to.
Would be much quicker in pandas imo.