converted json column in pandas data frame into nested json output

Question

I have a json column in a dataframe df, where i am trying to convert it into a nested json and save it as a txt file for clients:

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

into a nested json and save into 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 thoughts? Thanks.

I have checked a few posts and they are not quite the same

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

score 1 · Accepted Answer · answered Feb 12 '19 at 04:48

Starting with the df you gave as an example:

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...

Running the below code converts the contents of all rows in the json column to json entries and saves them all to a text file where they appear nicely formatted:

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)

This is how data.txt appears when opened in notepad:

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

converted json column in pandas data frame into nested json output

1 Answers1

Linked