如何用Pandas Groupby&Agg拼接列并生成指定JSON结构？

需求说明 #

按site_address分组，将site_address、site_city、site_state拼接为"地址, 城市 州"格式的location字段，同时对每组price求和得到value字段，最终生成指定结构的JSON。

数据集 #

data = [['2608 W SYLVESTER ST', 'PASCO', 'WA', 4304],
        ['61 W MESQUITE BLVD', 'MESQUITE', 'NV', 115000],
        ['287 NW 3RD AVE', 'ESTACADA', 'OR', 1000],
        ['287 NW 3RD AVE', 'ESTACADA', 'OR', 2000],
        ['287 NW 3RD AVE', 'ESTACADA', 'OR', 7000]]

目标JSON结构 #

{
   "sites": [
      {
        "location": "2608 W SYLVESTER ST, PASCO WA",
        "value": 4304
      },
      {
        "location": "61 W MESQUITE BLVD, MESQUITE NV",
        "value": 115000
      },
      {
        "location": "287 NW 3RD AVE, ESTACADA OR",
        "value": 10000
      }
   ]
}

完整实现代码 #

import pandas as pd
import json

data = [['2608 W SYLVESTER ST', 'PASCO', 'WA', 4304],
        ['61 W MESQUITE BLVD', 'MESQUITE', 'NV', 115000],
        ['287 NW 3RD AVE', 'ESTACADA', 'OR', 1000],
        ['287 NW 3RD AVE', 'ESTACADA', 'OR', 2000],
        ['287 NW 3RD AVE', 'ESTACADA', 'OR', 7000]]

df = pd.DataFrame(data, columns=['site_address', 'site_city', 'site_state', 'price'])

# 分组聚合核心逻辑
df_grp = df.groupby('site_address', as_index=False).agg(
    location=lambda x: f"{x.name}, {x.iloc[0]['site_city']} {x.iloc[0]['site_state']}",
    value=('price', 'sum')
)[['location', 'value']]

# 生成目标JSON结构
result = {"sites": json.loads(df_grp.to_json(orient='records'))}

print(json.dumps(result, indent=2))

关键逻辑解释 #

• location字段实现：利用分组对象的x.name获取当前分组的地址值，x.iloc[0]取分组内第一条数据的城市和州（同一地址对应城市、州唯一），按指定格式拼接。
• value字段直接通过('price', 'sum')对分组内价格求和。
• 最后将聚合结果转为JSON数组，嵌套到sites字段中，匹配目标结构。

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