将数据帧中的json列转换为嵌套的json输出

Question

我在dataframe df中有一个json列，我试图将其转换为嵌套的json，并将其保存为客户机的txt文件：

数据帧测向

"json":{"0":{"status":"Active","task_name":"transientAlertf9b8b69eb4b84bd597b3983566ffc538","schedule":"","owner":"","task_type":"FraudIncremental","created_date":"Tue, 08 Jan 2019 00:34:43 GMT","parameters":{"program":"abc","subject":"abc ","days_back":1,"last_run_status":"OK"},"category":"Alert","permission":"private","messaging_channels":[{"channel_type":"email","cc":"","recipients":[""],"bcc":""}],"tags":"Fraud"}}}

转换为嵌套的json并保存为txt

    {
  "created_date": "Tue, 08 Jan 2019 00:34:43 GMT",
  "schedule": "",
  "parameters": {
    "last_run_status": "OK",
    "subject": "abc ",
    "days_back": 1,
    "program": "abc"
  },
  "status": "Active",
  "permission": "private",
  "task_type": "abc",
  "tags": "Fraud",
  "messaging_channels": [
    {
      "bcc": "",
      "cc": "",
      "channel_type": "email",
      "recipients": [
        ""
      ]
    }
  ],
  "task_name": "transientAlertf9b8b69eb4b84bd597b3983566ffc538",
  "category": "Alert",
  "owner": ""
}

有什么想法吗？谢谢。你知道吗

我查了几个帖子，不太一样

Pandas DataFrame to Nested JSON Without Changing Data Structure

DataFrame to nested JSON

Convert a Pandas Dataframe into a nested json

Convert Pandas Dataframe to nested JSON

Answer 1

以您给出的df为例：

df = pd.DataFrame({"json":{"0":{"status":"Active","task_name":"transientAlertf9b8b69eb4b84bd597b3983566ffc538","schedule":"","owner":"","task_type":"FraudIncremental","created_date":"Tue, 08 Jan 2019 00:34:43 GMT","parameters":{"program":"abc","subject":"abc ","days_back":1,"last_run_status":"OK"},"category":"Alert","permission":"private","messaging_channels":[{"channel_type":"email","cc":"","recipients":[""],"bcc":""}],"tags":"Fraud"}}})
df

    json
0   {'status': 'Active', 'task_name': 'transientAl...

运行以下代码将json列中所有行的内容转换为json条目，并将它们保存到一个文本文件中，在该文件中它们的格式看起来很好：

import json

json_data = df['json'].to_json(orient='index')
with open('data.txt', 'w') as outfile:
    json.dump(json.loads(json_data), outfile, sort_keys=True, indent=2)

这是在记事本中打开时data.txt的显示方式：

{
  "0": {
    "category": "Alert",
    "created_date": "Tue, 08 Jan 2019 00:34:43 GMT",
    "messaging_channels": [
      {
        "bcc": "",
        "cc": "",
        "channel_type": "email",
        "recipients": [
          ""
        ]
      }
    ],
    "owner": "",
    "parameters": {
      "days_back": 1,
      "last_run_status": "OK",
      "program": "abc",
      "subject": "abc "
    },
    "permission": "private",
    "schedule": "",
    "status": "Active",
    "tags": "Fraud",
    "task_name": "transientAlertf9b8b69eb4b84bd597b3983566ffc538",
    "task_type": "FraudIncremental"
  }
}