Функция для преобразования кадра данных pandas в JSON ⇐ Python

[Anonymous]

У меня есть фрейм данных pandas, который я хочу преобразовать в JSON в требуемом формате. По сути, JSON представляет собой древовидную структуру кадра данных.
Ввод:
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

Текущий результат:
{
"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":
[]
}
]
}
]
}
]
}
]
}
}
}

Проблема:
Моя текущая функция работает, но я не могу понять две вещи.

• 'path' всегда равен [], логика пути такая же, как для корневого узла path=[], а для дочерних узлов он следует пути родительского узла. Например: для 2-го узла, путь:[Всего заявок]
• 'isCritial' (опечатка намеренна), в основном этот флаг верен для всех узлов, указанных пользователем. например: строка с числом 15 считается целевой и все узлы (Всего: Всего заявок, Разрешение: True, Категория: IT, Расширенное: True, Количество: 15)

Текущая функция:
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


Подробнее здесь: https://stackoverflow.com/questions/792 ... -into-json