'Convert nested JSON to CSV or table

I know this question has been asked many times but none of the answers satisfy my requirement. I want to dynamically convert any nested JSON to a CSV file or Dataframe. Some sample examples are:

input : {"menu": {
    "header": "SVG Viewer",
    "items": [
        {"id": "Open"},
        {"id": "OpenNew", "label": "Open New"},
        null,
        {"id": "ZoomIn", "label": "Zoom In"},
        {"id": "ZoomOut", "label": "Zoom Out"},
        {"id": "OriginalView", "label": "Original View"},
        null,
        {"id": "Quality"},
        {"id": "Pause"},
        {"id": "Mute"},
        null,
        {"id": "Find", "label": "Find..."},
        {"id": "FindAgain", "label": "Find Again"},
        {"id": "Copy"},
        {"id": "CopyAgain", "label": "Copy Again"},
        {"id": "CopySVG", "label": "Copy SVG"},
        {"id": "ViewSVG", "label": "View SVG"},
        {"id": "ViewSource", "label": "View Source"},
        {"id": "SaveAs", "label": "Save As"},
        null,
        {"id": "Help"},
        {"id": "About", "label": "About Adobe CVG Viewer..."}
    ]
}}

Output:

input 2 : {"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}

Output 2:

So far I have tried below code, which works fine but it explodes the list type data into columns, but I want it be exploded in rows.

from pandas.io.json import json_normalize
import pandas as pd


def flatten_json(y):
    out = {}

    def flatten(x, name=''):
        if type(x) is dict:
            
            for a in x:
                
                flatten(x[a], name + a + '.')
        elif type(x) is list:
            i = 0
            for a in x:
                flatten(a, name + str(i) + '.')
                i += 1
        else:
            
            out[str(name[:-1])] = str(x)

    flatten(y)
    return out
  
def start_explode(data):
    
  if type(data) is dict: 
    df = pd.DataFrame([flatten_json(data)])
  else:
    df = pd.DataFrame([flatten_json(x) for x in data])
  
  df = df.astype(str)
  return df

complex_json = {"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}
df = start_explode(complex_json['menu'])
display(df)

It gives output like below for one the above inputs:

Solution 1:^[1]

In python you can use pandas to do this but It will repeat the header values for each line like below

code

output

Solution 2:^[2]

You can try json_normalize

import pandas as pd
import json

data = json.loads("""{"menu": {
    "header": "SVG Viewer",
    "items": [
        {"id": "Open"},
        {"id": "OpenNew", "label": "Open New"},
        null,
        {"id": "ZoomIn", "label": "Zoom In"},
        {"id": "ZoomOut", "label": "Zoom Out"},
        {"id": "OriginalView", "label": "Original View"},
        null,
        {"id": "Quality"},
        {"id": "Pause"},
        {"id": "Mute"},
        null,
        {"id": "Find", "label": "Find..."},
        {"id": "FindAgain", "label": "Find Again"},
        {"id": "Copy"},
        {"id": "CopyAgain", "label": "Copy Again"},
        {"id": "CopySVG", "label": "Copy SVG"},
        {"id": "ViewSVG", "label": "View SVG"},
        {"id": "ViewSource", "label": "View Source"},
        {"id": "SaveAs", "label": "Save As"},
        null,
        {"id": "Help"},
        {"id": "About", "label": "About Adobe CVG Viewer..."}
    ]
}}""")

# remove null
data['menu']['items'] = [i for i in data['menu']['items'] if i is not None]

pd.json_normalize(data['menu'], record_path=['items'], meta=['header'], record_prefix='items_')

#   items_id    items_label
# header        
# SVG Viewer    Open    NaN
# SVG Viewer    OpenNew Open New
# SVG Viewer    ZoomIn  Zoom In
# SVG Viewer    ZoomOut Zoom Out
# SVG Viewer    OriginalView    Original View
# SVG Viewer    Quality NaN
# SVG Viewer    Pause   NaN
# SVG Viewer    Mute    NaN
# SVG Viewer    Find    Find...
# SVG Viewer    FindAgain   Find Again
# SVG Viewer    Copy    NaN
# SVG Viewer    CopyAgain   Copy Again
# SVG Viewer    CopySVG Copy SVG
# SVG Viewer    ViewSVG View SVG
# SVG Viewer    ViewSource  View Source
# SVG Viewer    SaveAs  Save As
# SVG Viewer    Help    NaN
# SVG Viewer    About   About Adobe CVG Viewer...

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	rao yasir
Solution 2	Epsi95

'Convert nested JSON to CSV or table

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]