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

Rayner 发表于 Dev

雷纳

我在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"
      }
    ]
  }
  ]
}

系列休闲

在最终将它们转换为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"
      }
    ]
  }
]

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。