python - Function to convert a pandas dataframe into JSON - Stack Overflow

I have a pandas dataframe that I want to convert to JSON in the required format. The JSON is basically a tree structure of the dataframe.

Input:

Total           Resolution  Category    Escalated   Count
Total Tickets   False       IT          False       4
Total Tickets   False       IT          True        3
Total Tickets   True        IT          False       1
Total Tickets   True        IT          True        15
Total Tickets   True        Unknown     True        1

Current Output:

{
    "chart":
    {
        "data":
        {
            "path":
            [],
            "displayColumnLabel": "Total",
            "displayValueLabel": "Total Tickets",
            "values":
            [
                {
                    "type": "ticket",
                    "value": 24,
                    "change": 0,
                    "changeType": "neutral"
                }
            ],
            "isCritial": false,
            "children":
            [
                {
                    "path":
                    [],
                    "displayColumnLabel": "Resolution",
                    "displayValueLabel": "True",
                    "values":
                    [
                        {
                            "type": "ticket",
                            "value": 17,
                            "change": 0,
                            "changeType": "neutral"
                        }
                    ],
                    "isCritial": false,
                    "children":
                    [
                        {
                            "path":
                            [],
                            "displayColumnLabel": "Category",
                            "displayValueLabel": "IT",
                            "values":
                            [
                                {
                                    "type": "ticket",
                                    "value": 16,
                                    "change": 0,
                                    "changeType": "neutral"
                                }
                            ],
                            "isCritial": false,
                            "children":
                            [
                                {
                                    "path":
                                    [],
                                    "displayColumnLabel": "Escalated",
                                    "displayValueLabel": "True",
                                    "values":
                                    [
                                        {
                                            "type": "ticket",
                                            "value": 15,
                                            "change": 0,
                                            "changeType": "neutral"
                                        }
                                    ],
                                    "isCritial": false,
                                    "children":
                                    [
                                        {
                                            "path":
                                            [],
                                            "displayColumnLabel": "tickets",
                                            "displayValueLabel": "15",
                                            "values":
                                            [
                                                {
                                                    "type": "ticket",
                                                    "value": 15,
                                                    "change": 0,
                                                    "changeType": "neutral"
                                                }
                                            ],
                                            "isCritial": false,
                                            "children":
                                            []
                                        }
                                    ]
                                },
                                {
                                    "path":
                                    [],
                                    "displayColumnLabel": "Escalated",
                                    "displayValueLabel": "False",
                                    "values":
                                    [
                                        {
                                            "type": "ticket",
                                            "value": 1,
                                            "change": 0,
                                            "changeType": "neutral"
                                        }
                                    ],
                                    "isCritial": false,
                                    "children":
                                    [
                                        {
                                            "path":
                                            [],
                                            "displayColumnLabel": "tickets",
                                            "displayValueLabel": "1",
                                            "values":
                                            [
                                                {
                                                    "type": "ticket",
                                                    "value": 1,
                                                    "change": 0,
                                                    "changeType": "neutral"
                                                }
                                            ],
                                            "isCritial": false,
                                            "children":
                                            []
                                        }
                                    ]
                                }
                            ]
                        },
                        {
                            "path":
                            [],
                            "displayColumnLabel": "Category",
                            "displayValueLabel": "Unknown",
                            "values":
                            [
                                {
                                    "type": "ticket",
                                    "value": 1,
                                    "change": 0,
                                    "changeType": "neutral"
                                }
                            ],
                            "isCritial": false,
                            "children":
                            [
                                {
                                    "path":
                                    [],
                                    "displayColumnLabel": "Escalated",
                                    "displayValueLabel": "True",
                                    "values":
                                    [
                                        {
                                            "type": "ticket",
                                            "value": 1,
                                            "change": 0,
                                            "changeType": "neutral"
                                        }
                                    ],
                                    "isCritial": false,
                                    "children":
                                    [
                                        {
                                            "path":
                                            [],
                                            "displayColumnLabel": "tickets",
                                            "displayValueLabel": "1",
                                            "values":
                                            [
                                                {
                                                    "type": "ticket",
                                                    "value": 1,
                                                    "change": 0,
                                                    "changeType": "neutral"
                                                }
                                            ],
                                            "isCritial": false,
                                            "children":
                                            []
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                },
                {
                    "path":
                    [],
                    "displayColumnLabel": "Resolution",
                    "displayValueLabel": "False",
                    "values":
                    [
                        {
                            "type": "ticket",
                            "value": 7,
                            "change": 0,
                            "changeType": "neutral"
                        }
                    ],
                    "isCritial": false,
                    "children":
                    [
                        {
                            "path":
                            [],
                            "displayColumnLabel": "Category",
                            "displayValueLabel": "IT",
                            "values":
                            [
                                {
                                    "type": "ticket",
                                    "value": 7,
                                    "change": 0,
                                    "changeType": "neutral"
                                }
                            ],
                            "isCritial": false,
                            "children":
                            [
                                {
                                    "path":
                                    [],
                                    "displayColumnLabel": "Escalated",
                                    "displayValueLabel": "False",
                                    "values":
                                    [
                                        {
                                            "type": "ticket",
                                            "value": 4,
                                            "change": 0,
                                            "changeType": "neutral"
                                        }
                                    ],
                                    "isCritial": false,
                                    "children":
                                    [
                                        {
                                            "path":
                                            [],
                                            "displayColumnLabel": "tickets",
                                            "displayValueLabel": "4",
                                            "values":
                                            [
                                                {
                                                    "type": "ticket",
                                                    "value": 4,
                                                    "change": 0,
                                                    "changeType": "neutral"
                                                }
                                            ],
                                            "isCritial": false,
                                            "children":
                                            []
                                        }
                                    ]
                                },
                                {
                                    "path":
                                    [],
                                    "displayColumnLabel": "Escalated",
                                    "displayValueLabel": "True",
                                    "values":
                                    [
                                        {
                                            "type": "ticket",
                                            "value": 3,
                                            "change": 0,
                                            "changeType": "neutral"
                                        }
                                    ],
                                    "isCritial": false,
                                    "children":
                                    [
                                        {
                                            "path":
                                            [],
                                            "displayColumnLabel": "tickets",
                                            "displayValueLabel": "3",
                                            "values":
                                            [
                                                {
                                                    "type": "ticket",
                                                    "value": 3,
                                                    "change": 0,
                                                    "changeType": "neutral"
                                                }
                                            ],
                                            "isCritial": false,
                                            "children":
                                            []
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                }
            ]
        }
    }
}

Problem:

My current function works, but there are two things I cant figure out.

1. 'path' is always [], the logic for path is that for root node path=[], and for the child nodes it follows the parent node path. Eg: for 2nd Node, path:[Total Tickets]
2. 'isCritial' (typo is intentional) flag, basically this flag is true for all nodes specified by the user. eg: the row with count 15 is deemed target and all nodes(Total:Total Tickets, Resolution:True, Category:IT, Escalated:True, Count:15)

Current function:

def create_hierarchical_json_new(df, value_column='Ticket Id'):
    # Extract column names
    columns = df.columns.tolist()
    

    # Build data and children sections
    def build_children(df, group_cols):
        if not group_cols:
            return []  # Base case: no more grouping columns, return empty list

        current_col = group_cols[0]
        grouped = df.groupby(current_col)
        children = []
        for value, group in grouped:
            is_terminal_node = len(group_cols) == 1
            child = {
                "path": [],
                "displayColumnLabel": current_col,
                "displayValueLabel": str(value),
                "values": [
                    {
                        "type": "ticket",
                        "value": group[value_column].sum(),
                        "change": 0,
                        "changeType": "neutral",
                    }
                ],
                "isCritial": False,
                "children": build_children(group, group_cols[1:]),
            }

            # Add one more node if this is the terminal node
            if is_terminal_node:
                child["children"].append({
                    "path": [],
                    "displayColumnLabel": "tickets",
                    "displayValueLabel": str(group[value_column].sum()),
                    "values": [
                        {
                            "type": "ticket",
                            "value": group[value_column].sum(),
                            "change": 0,
                            "changeType": "neutral",
                        }
                    ],
                    "isCritial": False,
                    "children": []
                })

            children.append(child)
        return children

    data = {
        "path": [],
        "displayColumnLabel": columns[0],
        "displayValueLabel": str(df[columns[0]].iloc[0]),
        "values": [
            {
                "type": "ticket",
                "value": df[value_column].sum(),
                "change": 0,
                "changeType": "neutral",
            }
        ],
        "isCritial": False,
        "children": build_children(df, columns[1:-1]),  # Group by all columns except first and last
    }

    # Combine meta and data sections
    result = {
        "chart": {
            "data": data
        }
    }

    return result