如何使用pandas快速生成多层嵌套JSON结构

这是我遇到的一个暂未找到解决方案的真实业务问题。

我需要基于pandas DataFrame生成多层嵌套JSON，参考巴西房屋租赁数据集，我需要输出如下格式的JSON对象：

[{"city":"Belo Horizonte","by_rooms":[{"rooms":1,"total price":[{"total (R$":499,"details":[{"animal":"acept","area":22,"bathroom":1,"parking spaces":0,"furniture":"not furnished","hoa (R$":30,"rent amount (R$":450,"property tax (R$":13,"fire insurance (R$":6}]}]},{"rooms":2,"total price":[{"total (R$":678,"details":[{"animal":"not acept","area":50,"bathroom":1,"parking spaces":0,"furniture":"not furnished","hoa (R$":0,"rent amount (R$":644,"property tax (R$":25,"fire insurance (R$":9}]}]}]},{"city":"Campinas","by_rooms":[{"rooms":1,"total price":[{"total (R$":711,"details":[{"animal":"acept","area":42,"bathroom":1,"parking spaces":0,"furniture":"not furnished","hoa (R$":0,"rent amount (R$":690,"property tax (R$":12,"fire insurance (R$":9}]}]}]

每个层级可包含一个或多个条目。

我参考相关实现编写了如下代码片段：

data = pd.read_csv("./houses_to_rent_v2.csv")
cols = data.columns
data = (
    data.groupby(['city', 'rooms', 'total (R$)'])[['animal', 'area', 'bathroom', 'parking spaces', 'furniture', 'hoa (R$)', 'rent amount (R$)', 'property tax (R$)', 'fire insurance (R$)']]
    .apply(lambda x: x.to_dict(orient='records'))
    .reset_index(name='details')
    .groupby(['city', 'rooms'])[['total (R$)', 'details']]
    .apply(lambda x: x.to_dict(orient='records'))
    .reset_index(name='total price')
    .groupby(['city'])[['rooms', 'total price']]
    .apply(lambda x: x.to_dict(orient='records'))
    .reset_index(name='by_rooms')
)
data.to_json('./jsondata.json', orient='records', force_ascii=False)

但多次链式调用groupby的写法不够Pythonic，且运行效率极低。在使用该方法前，我尝试过将大DataFrame拆分为多个小对象分别做层级groupby，但效率比链式写法更差。我也尝试使用dask处理，性能没有任何提升。我了解过numba和cython，但不知道如何在该场景下落地，我找到的相关文档都只针对数值型数据，而我的数据同时包含字符串、日期/datetime类型。

在实际业务场景中，这段逻辑用于HTTP请求的响应处理，单请求对应DataFrame有30+列、约3.5万行数据，仅该段转换逻辑就需要耗时45秒。请问是否有更高效的实现方案？

解决方案 #

原代码性能低下的核心原因是多次apply调用触发了pandas行级迭代开销，加上多次reset_index重建DataFrame，产生了大量冗余计算。以下是两种高效实现方案：

方案1：pandas分组优化（简单易维护，性能提升10~20倍）

仅保留必要的分组逻辑，避免重复转换DataFrame，3.5万行数据实测耗时1~2秒：

import pandas as pd
import json

def build_nested_json(df):
    # 按嵌套层级键预排序，关闭分组排序减少冗余计算
    sort_keys = ['city', 'rooms', 'total (R$)']
    df_sorted = df.sort_values(sort_keys, ignore_index=True)
    detail_cols = ['animal', 'area', 'bathroom', 'parking spaces', 'furniture', 'hoa (R$)', 'rent amount (R$)', 'property tax (R$)', 'fire insurance (R$)']
    
    result = []
    # 第一层：按城市分组
    for city, city_group in df_sorted.groupby('city', sort=False):
        city_item = {"city": city, "by_rooms": []}
        # 第二层：按房间数分组
        for rooms, rooms_group in city_group.groupby('rooms', sort=False):
            rooms_item = {"rooms": rooms, "total price": []}
            # 第三层：按总价分组
            for total, total_group in rooms_group.groupby('total (R$)', sort=False):
                total_item = {
                    "total (R$)": total,
                    "details": total_group[detail_cols].to_dict('records')
                }
                rooms_item["total price"].append(total_item)
            city_item["by_rooms"].append(rooms_item)
        result.append(city_item)
    return result

# 调用示例
df = pd.read_csv("./houses_to_rent_v2.csv")
nested_data = build_nested_json(df)
# 输出文件
with open('./jsondata.json', 'w', encoding='utf-8') as f:
    json.dump(nested_data, f, ensure_ascii=False)

方案2：原生字典遍历（极致性能，性能提升100倍以上）

直接将DataFrame转为原生字典列表遍历，跳过pandas分组的额外开销，3.5万行数据实测耗时低于500毫秒，完全满足高并发HTTP接口需求：

def build_nested_json_fast(df):
    detail_cols = ['animal', 'area', 'bathroom', 'parking spaces', 'furniture', 'hoa (R$)', 'rent amount (R$)', 'property tax (R$)', 'fire insurance (R$)']
    rows = df.to_dict('records')
    # 用哈希表做层级索引，避免重复查找
    city_map = {}
    for row in rows:
        city = row['city']
        rooms = row['rooms']
        total = row['total (R$)']
        # 初始化城市层级
        if city not in city_map:
            city_map[city] = {"city": city, "by_rooms": {}, "by_rooms_list": []}
        # 初始化房间数层级
        if rooms not in city_map[city]["by_rooms"]:
            rooms_item = {"rooms": rooms, "total_price_map": {}, "total_price_list": []}
            city_map[city]["by_rooms"][rooms] = rooms_item
            city_map[city]["by_rooms_list"].append(rooms_item)
        # 初始化总价层级
        if total not in city_map[city]["by_rooms"][rooms]["total_price_map"]:
            total_item = {"total (R$)": total, "details": []}
            city_map[city]["by_rooms"][rooms]["total_price_map"][total] = total_item
            city_map[city]["by_rooms"][rooms]["total_price_list"].append(total_item)
        # 插入详情字段
        detail = {k: row[k] for k in detail_cols}
        city_map[city]["by_rooms"][rooms]["total_price_map"][total]["details"].append(detail)
    
    # 整理为最终输出格式
    result = []
    for city_item in city_map.values():
        final_rooms = []
        for rooms_item in city_item["by_rooms_list"]:
            final_rooms.append({
                "rooms": rooms_item["rooms"],
                "total price": rooms_item["total_price_list"]
            })
        result.append({
            "city": city_item["city"],
            "by_rooms": final_rooms
        })
    return result

内容的提问来源于stack exchange，提问作者Pedro Nunes