How to convert JSON into a table using python?

Question

HI I'm a beginner with python. I have a csv file which I retrieve from my database. The table has several columns, one of which contains data in json. I have difficulty converting json data into a table to be saved in pdf. First I load the csv. Then I take the column that has the data in json and I convert them.

df = pd.DataFrame(pd.read_csv('database.csv',sep=';'))
print(df.head())

for index, row in df.iterrows():

    str = row["json_data"]
    val = ast.literal_eval(str)
    val1 = json.loads(str)

A sample of my json is that

{
    "title0": {
        "id": "1",
                 "ex": "90",
    },
    "title1": {
        "name": "Alex",
        "surname": "Boris",
        "code": "RBAMRA4",

    },
    "title2": {
        "company": "LA",
        "state": "USA",
        "example": "9090",

    }
}

I'm trying to create a table like this


-------------------------------------------\
title0
--------------------------------------------\
id 1
ex 90
---------------------------------------------\
title 1
---------------------------------------------\
name Alex
surname Boris
code RBAMRA4
----------------------------------------------\
title2
----------------------------------------------\
company LA
state USA
example 9090

Vignesh SP · Accepted Answer · 2019-07-18 23:12:03Z

1

You can use the Python JSON library to achieve this.

import json

my_str = open("outfile").read()
val1 = json.loads(my_str)

for key in val1:
    print("--------------------\n"+key+"\n--------------------")
    for k in val1[key]:
        print(k, val1[key][k])

Load the JSON data into the json.jsonloads function, this will deserialize the string and convert it to a python object (the whole object becomes a dict).

Then you loop through the dict the way you want.

--------------------
title0
--------------------
id 1
ex 90
--------------------
title1
--------------------
name Alex
surname Boris
code RBAMRA4
--------------------
title2
--------------------
company LA
state USA
example 9090

Read about parsing a dict, then you will understand the for loop.

answered Jul 18, 2019 at 23:12

Vignesh SP

4461 gold badge6 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jean Over a year ago

Thank you for your help and the good explanation. I'm going to study the dict.

Collectives™ on Stack Overflow

How to convert JSON into a table using python?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related