Convert 4 level nested JSON file to 1 level nested with Python

Question

I have the 4 level nested JSON file below, that I would like to normalize to a one level nesting:

Input file is like this:

{
    "@index": "40",
    "row": [
      {
        "column": [
          {
            "text": {
              "@fontName": "Times New Roman",
              "@fontSize": "12.0",
              "@x": "85.10",
              "@y": "663.12",
              "@width": "250.01",
              "@height": "12.00",
              "#text": "text 1"
            }
          }
        ]
      },
      {
        "column": [
          {
            "text": {
              "@fontName": "Times New Roman",
              "@fontSize": "8.0",
              "@x": "121.10",
              "@y": "675.36",
              "@width": "348.98",
              "@height": "8.04",
              "#text": "text 2"
            }
          },
          {
            "text": {
              "@fontName": "Times New Roman",
              "@fontSize": "12.0",
              "@x": "473.30",
              "@y": "676.92",
              "@width": "42.47",
              "@height": "12.00",
              "#text": "text 3"
            }
          }
        ]
      },
      {
        "column": [
          {
            "text": {
              "@fontName": "Times New Roman",
              "@fontSize": "12.0",
              "@x": "85.10",
              "@y": "690.72",
              "@width": "433.61",
              "@height": "12.00",
              "#text": "text 4"
            }
          }
        ]
      }
    ]
  }

Desired output is like this:

{
    "@index": "40",
    "row": [
          {
              "@fontName": "Times New Roman",
              "@fontSize": "12.0",
              "@x": "85.10",
              "@y": "663.12",
              "@width": "250.01",
              "@height": "12.00",
              "#text": "Text 1"
          },
          {
              "@fontName": "Times New Roman",
              "@fontSize": "8.0",
              "@x": "121.10",
              "@y": "675.36",
              "@width": "348.98",
              "@height": "8.04",
              "#text": "Text 2"
          },
          {
              "@fontName": "Times New Roman",
              "@fontSize": "12.0",
              "@x": "473.30",
              "@y": "676.92",
              "@width": "42.47",
              "@height": "12.00",
              "#text": "Text 3"
          },
          {
              "@fontName": "Times New Roman",
              "@fontSize": "12.0",
              "@x": "85.10",
              "@y": "690.72",
              "@width": "433.61",
              "@height": "12.00",
              "#text": "Text 4"
          }
    ]
  }

The code I have so far is this using pandas is below, but I don´t know how to continue to normalize to one level.

import json 
import pandas as pd 
from pandas.io.json import json_normalize #package for flattening json in pandas df

#load json object
with open('D:\Files\JSON\4Level.json') as f:
    d = json.load(f)

nycphil = json_normalize(d['row'])
print (nycphil.head(4))

This is the current output tabulated, where shows that column is a nested element:

                                            column
0  [{'text': {'@fontName': 'Times New Roman', '@f...
1  [{'text': {'@fontName': 'Times New Roman', '@f...
2  [{'text': {'@fontName': 'Times New Roman', '@f...

The print with one level nesting would be:

text.#text   text.@fontName text.@fontSize   ...   text.@width text.@x text.@y
0     Text 1  Times New Roman           12.0   ...        250.01   85.10  663.12
1     Text 2  Times New Roman            8.0   ...        348.98  121.10  675.36
2     Text 3  Times New Roman           12.0   ...         42.47  473.30  676.92
3     Text 4  Times New Roman           12.0   ...        433.61   85.10  690.72

The Input/Output comparison is like this:

Maybe someone could help me with this. Thanks for any help.

UPDATE

In order to make a small sample in first sample input I showed, I removed some elements that seems to be are needed in your scripts to work. So now I show exactly the same structure as real file but with this input your scripts don't work. I think they need a little tweak but I've been trying and I don't know how to change them to get the same output with this new input. Maybe you can help me and sorry for not show the correct input from beginning.

{
   "document":{
      "page":[
         {
            "@index":"0",
            "image":{
               "@data":"ABC",
               "@format":"png",
               "@height":"620.00",
               "@type":"base64encoded",
               "@width":"450.00",
               "@x":"85.00",
               "@y":"85.00"
            }
         },
         {
            "@index":"1",
            "row":[
               {
                  "column":[
                     {
                        "text":""
                     },
                     {
                        "text":{
                           "#text":"Text1",
                           "@fontName":"Arial",
                           "@fontSize":"12.0",
                           "@height":"12.00",
                           "@width":"71.04",
                           "@x":"121.10",
                           "@y":"83.42"
                        }
                     }
                  ]
               },
               {
                  "column":[
                     {
                        "text":""
                     },
                     {
                        "text":{
                           "#text":"Text2",
                           "@fontName":"Arial",
                           "@fontSize":"12.0",
                           "@height":"12.00",
                           "@width":"101.07",
                           "@x":"121.10",
                           "@y":"124.82"
                        }
                     }
                  ]
               }
            ]
         },
         {
            "@index":"2",
            "row":[
               {
                  "column":{
                     "text":{
                        "#text":"Text3",
                        "@fontName":"Arial",
                        "@fontSize":"12.0",
                        "@height":"12.00",
                        "@width":"363.44",
                        "@x":"85.10",
                        "@y":"69.62"
                     }
                  }
               },
               {
                  "column":{
                     "text":{
                        "#text":"Text4",
                        "@fontName":"Arial",
                        "@fontSize":"12.0",
                        "@height":"12.00",
                        "@width":"382.36",
                        "@x":"85.10",
                        "@y":"83.42"
                     }
                  }
               },
               {
                  "column":{
                     "text":{
                        "#text":"Text5",
                        "@fontName":"Arial",
                        "@fontSize":"12.0",
                        "@height":"12.00",
                        "@width":"435.05",
                        "@x":"85.10",
                        "@y":"97.22"
                     }
                  }
               }
            ]
         },
         {
            "@index":"3"
         }
      ]
   }
}

Check flatten_json() from [here](https://stackoverflow.com/a/51379007/8353711). I have checked already. It's working. — shaik moeed, May 28 '19 at 06:40
Possible duplicate of [Python flatten multilevel JSON](https://stackoverflow.com/questions/51359783/python-flatten-multilevel-json) — shaik moeed, May 28 '19 at 06:40

JohnO · Answer 1 · 2019-05-28T11:02:19.677

2

As an alternative to json_normalize() you can also use a comprehension.:

my_dict["row"] = [{k: v for k, v in col_entry["text"].items()} for entry in my_dict["row"] for col_entry in entry["column"]]

Edit: fixed code to cover multiple entries in each column list. This does admittedly approach the pain threshold in terms of nesting of comprehensions...

edited May 28 '19 at 11:02

answered May 28 '19 at 06:46

JohnO

777
6
10

Hi John. I'm newbie to Python. In this case I need to load the Json file into a dictionary? If yes, How would be to load the file in a dict? Thanks – Ger Cas May 28 '19 at 07:02
json.loads(). You can find more info here https://docs.python.org/3/library/json.html – JohnO May 28 '19 at 07:04
This will extract only the first element of a "column" list. You will get 3 font sections instead of 4 in the output. – addmoss May 28 '19 at 09:58
Ah yes sorry I missed that there were two on the same level. Will fix the code once I'm back home. – JohnO May 28 '19 at 10:10
Hi JohnO, may you see please the new input below my UPDATE. I wasn't able to modify your script in order to make it work with this input that is the same structure as real file. Thanks – Ger Cas May 28 '19 at 23:39
Honestly, I think this has reached a complexity where trying to solve it with a list comprehension would be a gross violation of "readability counts". So at this point I'd go with one of the other solutions posted. – JohnO May 29 '19 at 08:29

score 1 · Answer 2 · answered May 28 '19 at 07:35

Below is a working code:

(56336255.json is the sample data you have posted)

import json
import pprint

flat_data = dict()
with open('56336255.json') as f:
    data = json.load(f)
    for k, v in data.items():
        if k == '@index':
            flat_data[k] = data[k]
        else:
            flat_data[k] = []
            for row in v:
                for cell in row['column']:
                    flat_data[k].append(cell['text'])

    pprint.pprint(flat_data)

output

{'@index': '40',
 'row': [{'#text': 'text 1',
          '@fontName': 'Times New Roman',
          '@fontSize': '12.0',
          '@height': '12.00',
          '@width': '250.01',
          '@x': '85.10',
          '@y': '663.12'},
         {'#text': 'text 2',
          '@fontName': 'Times New Roman',
          '@fontSize': '8.0',
          '@height': '8.04',
          '@width': '348.98',
          '@x': '121.10',
          '@y': '675.36'},
         {'#text': 'text 3',
          '@fontName': 'Times New Roman',
          '@fontSize': '12.0',
          '@height': '12.00',
          '@width': '42.47',
          '@x': '473.30',
          '@y': '676.92'},
         {'#text': 'text 4',
          '@fontName': 'Times New Roman',
          '@fontSize': '12.0',
          '@height': '12.00',
          '@width': '433.61',
          '@x': '85.10',
          '@y': '690.72'}]}

addmoss · Answer 3 · 2019-05-28T10:05:33.273

This does the job:

data = json.load(json_file)
flat = [ column['text'] for entry in data['row'] for column in entry['column'] ]

Complete working example:

import json
import sys
import os.path

def main(argv):

    #Load JSON
    current_folder = os.path.dirname(os.path.realpath(__file__))
    with open(current_folder + '\\input.json') as json_file:  
        data = json.load(json_file)

    #Flatten (using for loops)
    flat=[]
    for entry in data['row']:
        for column in entry['column']:
            flat.append(column['text'])

    # OR, Flatten the pythonic way (using list comprehension)
    # looks strange at first but notice
    #   1. we start with the item we want to keep in the list
    #   2. the loops order is the same, we just write them inline 
    flat2 = [ column['text'] for entry in data['row'] for column in entry['column'] ]


    #Format data for saving to JSON
    output = {}
    output['@index']=data['@index']
    output['row'] = flat #or flat2 

    #Save to JSON
    with open('flat.txt', 'w') as outfile:
        json.dump(output, outfile, indent=4)

if __name__ == "__main__":
   main(sys.argv[1:])

Ajax1234 · Accepted Answer · 2019-05-29T00:36:20.320

You can use a list comprehension:

d = {'@index': '40', 'row': [{'column': [{'text': {'@fontName': 'Times New Roman', '@fontSize': '12.0', '@x': '85.10', '@y': '663.12', '@width': '250.01', '@height': '12.00', '#text': 'text 1'}}]}, {'column': [{'text': {'@fontName': 'Times New Roman', '@fontSize': '8.0', '@x': '121.10', '@y': '675.36', '@width': '348.98', '@height': '8.04', '#text': 'text 2'}}, {'text': {'@fontName': 'Times New Roman', '@fontSize': '12.0', '@x': '473.30', '@y': '676.92', '@width': '42.47', '@height': '12.00', '#text': 'text 3'}}]}, {'column': [{'text': {'@fontName': 'Times New Roman', '@fontSize': '12.0', '@x': '85.10', '@y': '690.72', '@width': '433.61', '@height': '12.00', '#text': 'text 4'}}]}]}
new_d = {**d, 'row':[c['text'] for b in d['row'] for c in b['column']]}

import json
print(json.dumps(new_d, indent=4))

Output:

{
 "@index": "40",
 "row": [
     {
        "@fontName": "Times New Roman",
        "@fontSize": "12.0",
        "@x": "85.10",
        "@y": "663.12",
        "@width": "250.01",
        "@height": "12.00",
        "#text": "text 1"
     },
     {
        "@fontName": "Times New Roman",
        "@fontSize": "8.0",
        "@x": "121.10",
        "@y": "675.36",
        "@width": "348.98",
        "@height": "8.04",
        "#text": "text 2"
     },
     {
        "@fontName": "Times New Roman",
        "@fontSize": "12.0",
        "@x": "473.30",
        "@y": "676.92",
        "@width": "42.47",
        "@height": "12.00",
        "#text": "text 3"
     },
     {
        "@fontName": "Times New Roman",
        "@fontSize": "12.0",
        "@x": "85.10",
        "@y": "690.72",
        "@width": "433.61",
        "@height": "12.00",
        "#text": "text 4"
    }
  ]
}

Edit: to flatten a nested structure, you can use recursion with a generator:

def flatten(d, t = ["image", "text"]):
   for a, b in d.items():
      if a in t:
         yield b
      elif isinstance(b, dict):
         yield from flatten(b)
      elif isinstance(b, list):
         for i in b:
            yield from flatten(i)


d = {'document': {'page': [{'@index': '0', 'image': {'@data': 'ABC', '@format': 'png', '@height': '620.00', '@type': 'base64encoded', '@width': '450.00', '@x': '85.00', '@y': '85.00'}}, {'@index': '1', 'row': [{'column': [{'text': ''}, {'text': {'#text': 'Text1', '@fontName': 'Arial', '@fontSize': '12.0', '@height': '12.00', '@width': '71.04', '@x': '121.10', '@y': '83.42'}}]}, {'column': [{'text': ''}, {'text': {'#text': 'Text2', '@fontName': 'Arial', '@fontSize': '12.0', '@height': '12.00', '@width': '101.07', '@x': '121.10', '@y': '124.82'}}]}]}, {'@index': '2', 'row': [{'column': {'text': {'#text': 'Text3', '@fontName': 'Arial', '@fontSize': '12.0', '@height': '12.00', '@width': '363.44', '@x': '85.10', '@y': '69.62'}}}, {'column': {'text': {'#text': 'Text4', '@fontName': 'Arial', '@fontSize': '12.0', '@height': '12.00', '@width': '382.36', '@x': '85.10', '@y': '83.42'}}}, {'column': {'text': {'#text': 'Text5', '@fontName': 'Arial', '@fontSize': '12.0', '@height': '12.00', '@width': '435.05', '@x': '85.10', '@y': '97.22'}}}]}, {'@index': '3'}]}}
print(json.dumps(list(filter(None, flatten(d))), indent=4))

Output:

[
  {
    "@data": "ABC",
    "@format": "png",
    "@height": "620.00",
    "@type": "base64encoded",
    "@width": "450.00",
    "@x": "85.00",
    "@y": "85.00"
  },
  {
    "#text": "Text1",
    "@fontName": "Arial",
    "@fontSize": "12.0",
    "@height": "12.00",
    "@width": "71.04",
    "@x": "121.10",
    "@y": "83.42"
  },
  {
    "#text": "Text2",
    "@fontName": "Arial",
    "@fontSize": "12.0",
    "@height": "12.00",
    "@width": "101.07",
    "@x": "121.10",
    "@y": "124.82"
  },
  {
    "#text": "Text3",
    "@fontName": "Arial",
    "@fontSize": "12.0",
    "@height": "12.00",
    "@width": "363.44",
    "@x": "85.10",
    "@y": "69.62"
  },
  {
    "#text": "Text4",
    "@fontName": "Arial",
    "@fontSize": "12.0",
    "@height": "12.00",
    "@width": "382.36",
    "@x": "85.10",
    "@y": "83.42"
  },
  {
    "#text": "Text5",
    "@fontName": "Arial",
    "@fontSize": "12.0",
    "@height": "12.00",
    "@width": "435.05",
    "@x": "85.10",
    "@y": "97.22"
  }
]

Hi Ajax1234, may you please see the new input below my UPDATE. I wasn't able to modify your script in order to make it work with this input that is the same structure as real file. Thanks — Ger Cas, May 28 '19 at 23:40
@GerCas No problem. What is your desired output from your new sample? Is it still the data associated with `row`? — Ajax1234, May 28 '19 at 23:49
Hi Ajax, thanks. I only want one level with the same blocks, those between `"text":{....}` `image":{...}` if possible. Thanks in advance — Ger Cas, May 29 '19 at 00:24
@GerCas Thank you for clarifying. Please see my recent edit. — Ajax1234, May 29 '19 at 00:37
Ajax, thanks a lot. It works just perfect. Now only need to print in tabulated form. I think for this can I use panda with the output of your solution? — Ger Cas, May 29 '19 at 02:19
@GerCas Yes, definitely, you can use [`pd.DataFrame(result)`](https://stackoverflow.com/questions/20638006/convert-list-of-dictionaries-to-a-pandas-dataframe). — Ajax1234, May 29 '19 at 02:23

shaik moeed · Answer 5 · 2019-05-28T17:00:08.577

0

Try this,

#!/usr/bin/python
# -*- coding: utf-8 -*-


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[name[:-1]] = x

    flatten(y)
    return out


expected_output = flatten_json(input_data)  # This will convert

edited May 28 '19 at 17:00

answered May 28 '19 at 07:26

shaik moeed

5,300
1
18
54

@GerCas Please check now and confirm. – shaik moeed May 28 '19 at 17:00
The same issue. I've tried with python console directly and as script `python script.py`, in this case I only get as output `{'': 'input.json'} ` – Ger Cas May 28 '19 at 23:21
@GerCas I have tested again on my machine with your input_data, It's working. – shaik moeed May 28 '19 at 23:23
I'm not sure what happens, I replace input_data with 'input.json' and I get that print. Additionally maybe you can see my UPDATE. – Ger Cas May 28 '19 at 23:41

Convert 4 level nested JSON file to 1 level nested with Python

5 Answers5