AG Grid: Custom sorting based on a list

Dash Python
1

I am trying to custom sort a grouped AG-Grid on the sport column based on a list SPORT_ORDER = [“Swimming”, “Gymnastics”, “Speed Skating”] but am unable to setup a comparator for it.

If anyone can please help find out the issue with this, would be great.

import dash
from dash import html
import dash_ag_grid as dag
import pandas as pd, json

SPORT_ORDER = ["Swimming", "Gymnastics", "Speed Skating"]

_value_comparator = {
    "function": f"""
      function(a, b) {{
        const order = {json.dumps(SPORT_ORDER)};
        const ia = order.indexOf(a), ib = order.indexOf(b);
        if (ia < 0 && ib < 0) return 0;
        if (ia < 0) return 1;
        if (ib < 0) return -1;
        return ia - ib;
      }}
    """
}

df = pd.DataFrame({
    "athlete": ["Michael Phelps","Aleksey Nemov","Cindy Klassen",
                "Nastia Liukin","test","test2"],
    "sport":   ["Swimming","Speed Skating","Gymnastics",
                "Gymnastics","Swimming","Swimming"],
    "gold":    [8,2,1,1,2,3],
})

column_defs = [
    {
        "field": "sport",
        "rowGroup": True,
        "hide": True,
        "sortable": True,
        "sort": "asc",                 # triggers the comparator
        "comparator": _value_comparator,
    },
    {"field": "athlete", "minWidth": 150},
    {"field": "gold", "aggFunc": "sum", "width": 90},
]

auto_group_def = {
    "headerName": "Sport (custom order)",
    "cellRenderer": "agGroupCellRenderer",
    "cellRendererParams": {"suppressCount": True},
}

app = dash.Dash(__name__)
app.layout = html.Div(
    dag.AgGrid(
        rowData=df.to_dict("records"),
        columnDefs=column_defs,
        defaultColDef={"resizable": True, "filter": True},
        dashGridOptions={
            "groupDisplayType": "singleColumn",
            "rowGroupPanelShow": "always",
            "autoGroupColumnDef": auto_group_def,
        },
        enableEnterpriseModules=True,
        dangerously_allow_code=True,
        style={"height": 400, "width": 600},
    )
)

if __name__ == "__main__":
    app.run_server(debug=True)
2

Hello @ajuneja,

Welcome to the community!

I’d recommend mapping the category to a numerical value and passing that column to the grid and sorting. :blush:

3

I see, so is it not possible directly using a comparator?
I ask because I was testing this in a dummy example but in my actual app I have 3 columns where I want to do a custom sort.
I noticed you had previously suggested a solution for a custom sort here but I couldn’t build on it

Additionally, if my ranks are my only choice, how do I implement it?
I don’t want to show the rank columns in my actualy view o I went witgh a solution like this but it still shows the rows in alphabetical sport order and not my custom order

"""
app.py – Dash-AG-Grid example with a hidden numeric rank column
that forces a custom order of category groups.

Run:  python app.py
"""

import json
import dash
from dash import html
import dash_ag_grid as dag
import pandas as pd

SPORT_ORDER = ["Swimming", "Gymnastics", "Speed Skating"]
RANK = {sport: rank for rank, sport in enumerate(SPORT_ORDER)}

df = pd.DataFrame(
    {
        "athlete": [
            "Michael Phelps", "Aleksey Nemov", "Cindy Klassen",
            "Nastia Liukin", "test", "test2"
        ],
        "sport": [
            "Swimming", "Speed Skating", "Gymnastics",
            "Gymnastics", "Swimming", "Swimming"
        ],
        "gold": [8, 2, 1, 1, 2, 3],
    }
)

df["sport_rank"] = df["sport"].map(RANK).fillna(99).astype(int)

column_defs = [
    {
        "field":    "sport_rank",
        "hide":     True,
        "sortable": True,
        "sort":     "asc",     
    },
    {
        "field":    "sport",
        "rowGroup": True,
        "hide":     True,
    },
    {"field": "athlete", "minWidth": 150},
    {"field": "gold",    "aggFunc": "sum", "width": 90},
]

# Definition for the visible auto-group column
auto_group_def = {
    "headerName": "Sport",
    "cellRenderer": "agGroupCellRenderer",
    "cellRendererParams": {"suppressCount": True},
}

app = dash.Dash(__name__)

app.layout = html.Div(
    dag.AgGrid(
        rowData=df.to_dict("records"),
        columnDefs=column_defs,
        defaultColDef={"resizable": True, "filter": True},
        dashGridOptions={
            "groupDisplayType": "singleColumn",
            "rowGroupPanelShow": "always",
            "autoGroupColumnDef": auto_group_def,
        },
        enableEnterpriseModules=True,
        dangerously_allow_code=True,
        style={"height": 400, "width": 600},
    )
)


if __name__ == "__main__":
    app.run_server(debug=True)
4

You can create a custom comparator, but for dynamic options or other things you would have to continuously adjust the comparator.

If you develop a key for the order, you can pass that to the grid and hide the column while also sorting by the column. You could also not pass the column def in general and adjust your comparator for the column to check against the provided option. Though, from a user perspective, if they are allowed to adjust the sorting, it doesnt seem intuitive.

Check out this version, it uses some fancy footwork to get around (mainly due to enterprise), but it makes everything look like its really not changed at all.

"""
app.py – Dash-AG-Grid example with a hidden numeric rank column
that forces a custom order of category groups.

Run:  python app.py
"""

import json
import dash
from dash import html
import dash_ag_grid as dag
import pandas as pd

SPORT_ORDER = ["Swimming", "Gymnastics", "Speed Skating"]
RANK = {sport: rank for rank, sport in enumerate(SPORT_ORDER)}

df = pd.DataFrame(
    {
        "athlete": [
            "Michael Phelps", "Aleksey Nemov", "Cindy Klassen",
            "Nastia Liukin", "test", "test2"
        ],
        "sport": [
            "Swimming", "Speed Skating", "Gymnastics",
            "Gymnastics", "Swimming", "Swimming"
        ],
        "gold": [8, 2, 1, 1, 2, 3],
    }
)

df["sport_rank"] = df["sport"].map(RANK).fillna(99).astype(int)

column_defs = [
    {
        "field":    "sport_rank",
        "rowGroup": True,
        "hide":     True,
        "sortable": True,
        "sort":     "asc",
        "headerName": "Sport",
        "valueFormatter": {"function": "params.data ? params.data.sport : params.node.allLeafChildren[0].data.sport"}
    },
    {"field": "athlete", "minWidth": 150},
    {"field": "gold",    "aggFunc": "sum", "width": 90},
]

# Definition for the visible auto-group column
auto_group_def = {
    "headerName": "Sport",
    "cellRenderer": "agGroupCellRenderer",
    "cellRendererParams": {"suppressCount": True},
}

app = dash.Dash(__name__)

app.layout = html.Div(
    dag.AgGrid(
        rowData=df.to_dict("records"),
        columnDefs=column_defs,
        defaultColDef={"resizable": True, "filter": True},
        dashGridOptions={
            "groupDisplayType": "singleColumn",
            "rowGroupPanelShow": "always",
            "autoGroupColumnDef": auto_group_def,
        },
        enableEnterpriseModules=True,
        dangerously_allow_code=True,
        style={"height": 400, "width": 600},
    )
)


if __name__ == "__main__":
    app.run(debug=True)
5

Many thanks for this, this does work!
However, I do wonder why isn’t a direct grouping based on a list not an option. now coming to the actual problem I am trying to solve for.

I have say a df of 1000 bonds that I showing on my dash app using groupby’s based on multiple columns:

  • Ratings - where I expect the order to be RATINGS = [“AAA”,“AA”,“A”,“BBB”,“BB”,“B”,“CCC”,“CC”,“C”,“D”,“NR”]

  • Price bin - where I expect the order to be [0-10, 10-20, 20-30, 30-40, . . .]

What’s good in my case is that these lists are fixed. There would never be a rating or a price bucket not in a list (not sure if this is what you intended to say by dynamicity)
Secondly, I do multi-level groupings so I defined my stuff as

rating_comparator = {
    "function": f"""
      function(a, b) {{
        const order = {json.dumps(RATINGS)};
        const ia = order.indexOf(a), ib = order.indexOf(b);
        // put any “not found” labels at the end:
        if (ia < 0 && ib < 0) return 0;
        if (ia < 0) return 1;
        if (ib < 0) return -1;
        return ia - ib;
      }}
    """
}

            colDef = {
                "headerName": FIELD_LABEL[f],
                "field": col,
                "rowGroupIndex": 0,
                "hide": True,
                "enableRowGroup": True,
                "rowGroup": (f==group_field)
            }
            if f == "ratings":
                colDef["comparator"] = rating_comparator
                colDef["sortable"] = True
                colDef["sort"] = "asc"
            columnDefs.append(colDef)

        auto_group_def = {
            "headerName":"Group",
            "colId":"autoGroup",
            "minWidth":250,
            "cellRenderer":"agGroupCellRenderer",
            "cellRendererParams":{"suppressCount":True}
            }
        if group_field == "ratings":
            auto_group_def["comparator"] = rating_comparator
            auto_group_def["sort"] = "asc"
            auto_group_def["sortable"] = True

which obviously wasn’t working which inspired me to test with the example above.

Now, considering all this, would your suggestion still be to use the enumeration rankings for each of them as you did in this example or is a direct grouping comparator a better solution?

6

I dont know why a list isnt available as passing to AG Grid to perform the comparisons, this would be something with upstream, not DAG.

I think performing the ranking on Python side vs the Client is easier in this case, and a little easier to track vs trying to create a different comparator for it. As long as the data doesnt change clientside, I think it will be fine.