时间戳

首页 > 解决方案 > 通过唯一的 column1 column2 组合从 pandas 数据框中转储 JSON

python - 通过唯一的 column1 column2 组合从 pandas 数据框中转储 JSON

问题描述

我在 SQL 数据库中有一些数据，我希望将其转换为 JSON。对于 grp1 和 grp2 的每个独特组合，我希望它列出 grp1、grp2 和名称。我猜正确的术语是嵌套的 JSON？

到目前为止，这是我的代码：

import pandas as pd
import json

json_string = '[{"grp1":"aaa","grp2":"streets","name":"Carter"},{"grp1":"aaa","grp2":"streets","name":"Chestnut"},{"grp1":"aaa","grp2":"streets","name":"Main"},{"grp1":"aaa","grp2":"aaa","name":"aaa"},{"grp1":"aaa","grp2":"ramps","name":"Ramp_A"},{"grp1":"aaa","grp2":"ramps","name":"Ramp_B"},{"grp1":"aaa","grp2":"ramps","name":"Ramp_C"},{"grp1":"test1","grp2":"test2","name":"testname"}]'

a_json = json.loads(json_string)

df = pd.DataFrame.from_dict(a_json)
print(df)

g = df.groupby(['grp1', 'grp2']).size().reset_index().rename(columns={0:'count'})
g['combo'] = g['grp1'] + '-' + g['grp2']
print(g)

如何利用 json.dumps 吐出以下嵌套的 JSON？

{
  "groups": [
  {
    "title": "aaa-aaa",
    "items": [
      {
         "title": "aaa",
         "combo3": "aaa-aaa-aaa"
      }
    ]
  },
  {
    "title": "aaa-streets",
    "items": [
      {
         "title": "Carter",
         "combo3": "aaa-streets-Carter"
      },
      {
         "title": "Chestnut",
         "combo3": "aaa-streets-Chestnut"
      },
      {
         "title": "Main",
         "combo3": "aaa-streets-Main"
      }
    ]
  },
  {
    "title": "aaa-ramps",
    "items": [
      {
         "title": "Ramp_A",
         "combo3": "aaa-ramps-Ramp_A"
      },
      {
         "title": "Ramp_B",
         "combo3": "aaa-ramps-Ramp_B"
      },
      {
         "title": "Ramp_C",
         "combo3": "aaa-ramps-Ramp_C"
      }
    ]
  },
  {
    "title": "test1-test2",
    "items": [
      {
         "title": "testname",
         "combo3": "test1-test2-testname"
      }
    ]
  }
  ]
}

标签： pythonjsonpandasdataframedictionary

解决方案

在最终将它们转换为 json 格式之前，您需要收集这些项目并将它们映射到一个临时字典。

这是运行代码：

json_string = '[{"grp1":"aaa","grp2":"streets","name":"Carter"},{"grp1":"aaa","grp2":"streets","name":"Chestnut"},{"grp1":"aaa","grp2":"streets","name":"Main"},{"grp1":"aaa","grp2":"aaa","name":"aaa"},{"grp1":"aaa","grp2":"ramps","name":"Ramp_A"},{"grp1":"aaa","grp2":"ramps","name":"Ramp_B"},{"grp1":"aaa","grp2":"ramps","name":"Ramp_C"},{"grp1":"test1","grp2":"test2","name":"testname"}]'

a_json = json.loads(json_string)
df = pd.DataFrame(a_json)

df['title'] = df['grp1'] + '-' + df['grp2']
df['combo3'] = df['grp1'] + '-' + df['grp2'] + '-' + df['name']

result_json = df[['title', 'name', 'combo3']].groupby(['title'])\
    .apply(lambda x: x[['name', 'combo3']].rename(columns={'name': 'title'}).to_dict('r'))\
    .reset_index()\
    .rename(columns={0:'items'})\
    .to_json(orient='records')

print(result_json)

输出：

[
  {
    "title": "aaa-aaa",
    "items": [
      {
        "name": "aaa",
        "combo3": "aaa-aaa-aaa"
      }
    ]
  },
  {
    "title": "aaa-ramps",
    "items": [
      {
        "name": "Ramp_A",
        "combo3": "aaa-ramps-Ramp_A"
      },
      {
        "name": "Ramp_B",
        "combo3": "aaa-ramps-Ramp_B"
      },
      {
        "name": "Ramp_C",
        "combo3": "aaa-ramps-Ramp_C"
      }
    ]
  },
  {
    "title": "aaa-streets",
    "items": [
      {
        "name": "Carter",
        "combo3": "aaa-streets-Carter"
      },
      {
        "name": "Chestnut",
        "combo3": "aaa-streets-Chestnut"
      },
      {
        "name": "Main",
        "combo3": "aaa-streets-Main"
      }
    ]
  },
  {
    "title": "test1-test2",
    "items": [
      {
        "name": "testname",
        "combo3": "test1-test2-testname"
      }
    ]
  }
]

推荐阅读