Covert Nest JSON to dataframe

Question

I have a Json as

"d" : [
    {
      "Key" : "72e4003b024165e7dca74df9a2f17275",
      "occurrenceCount" : 1,
      "localStatus" : null,
      "ordered" : true,
      "events" : [
        {
          "eventNumber" : 1,
          "eventTreePosition" : "1",
          "eventSet" : 0,
          "main" : true,
          "moreInformationId" : null,
          "remediation" : false,
          "events" : null
        },
        {
          "eventNumber" : 2,
          "eventTreePosition" : "2",
          "eventSet" : 0,
          "main" : false,
          "moreInformationId" : null,
          "remediation" : true,
          "events" : null
        },
        {
          "eventNumber" : 3,
          "eventTreePosition" : "3",
          "eventSet" : 0,
          "main" : false,
          "moreInformationId" : null,
          "remediation" : false,
          "events" : null
        }
      ],
      "stateOnServer" : null,
      "checkerProperties" : {
        "category" : "violation",
        "categoryDescription" : "violation",
        "cweCategory" : "710",
        "issueKinds" : [
          "QUALITY"
        ],
        "eventSetCaptions" : [],
        "impact" : "Low",
        "impactDescription" : "Low",
        "subcategoryLongDescription" : "If a pointer to a structure or union is never dereferenced within a Translation Unit",
        "Category" : "Adv"
      }
    },
    {
    "Key" : "72e4003b024165e7dca74df9a2f17275",
    "occurrenceCount" : 1,
    "localStatus" : null,
    "ordered" : true,
    "events" : [
      {
        "eventNumber" : 27,
        "eventTreePosition" : "1",
        "eventSet" : 0,
        "main" : true,
        "moreInformationId" : null,
        "remediation" : false,
        "events" : null
      },
      {
        "eventNumber" : 24,
        "eventTreePosition" : "2",
        "eventSet" : 0,
        "main" : false,
        "moreInformationId" : null,
        "remediation" : true,
        "events" : null
      },
      {
        "eventNumber" : 32,
        "eventTreePosition" : "3",
        "eventSet" : 0,
        "main" : false,
        "moreInformationId" : null,
        "remediation" : false,
        "events" : null
      }
    ],
    "stateOnServer" : null,
    "checkerProperties" : {
      "category" : "violation",
      "categoryDescription" : "violation",
      "cweCategory" : "710",
      "issueKinds" : [
        "QUALITY"
      ],
      "eventSetCaptions" : [],
      "impact" : "Low",
      "impactDescription" : "Low",
      "subcategoryLongDescription" : "If a pointer to a structure or union is never dereferenced within a Translation Unit",
      "Category" : "Adv"
    }
  }
]

When I tried with pandas Json normalize events is not splitting up I need to split up the events part also split up to rows particularly I need the first one only ie., event number 1 in first and 27 in second so on.,

The header are as Key, occurencecount, localstatus, ordered, eventnumber,eventTreePosition, ......, stateOnServer, category, ..., issueKinds, ....

Please help me on this

Answer 1

Convert your string to proper json content like {"d": ...}, and change null, true, false to proper string values and try below, hope it will help.

cols = ['Key','occurrenceCount','localStatus','ordered',\
    'eventnumber','eventTreePosition','stateOnServer','category','issueKinds']

df1 = pd.DataFrame(columns=cols)

for test in test1['d']:
    df1 = df1.append({'Key':test['Key'], 'occurrenceCount':test['occurrenceCount'], \
        'localStatus':test['localStatus'], 'ordered':test['ordered'], \
        'eventnumber':test['events'][0]['eventNumber'], 'eventTreePosition':test['events'][0]['eventTreePosition'],\
        'stateOnServer':test['stateOnServer'], 'category':test['checkerProperties']['category'], \
        'issueKinds':test['checkerProperties']['issueKinds'][0]}, ignore_index=True)