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

Question

我在dataframe df中有一个json列，我正尝试将其转换为嵌套的json，并将其保存为txt文件以供客户端使用：

数据帧df

"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": ""
}

ANy的想法？谢谢。

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

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"
  }
}