CODEWITHSUNDEEP
pythonjsonpandastree
natsuapo
natsuapo

Reputation: 613

How to convert a json tree data into dataframe in Python?

I have a json data which can be represented as the tree structure with each node has four attributes: name,id,child,parentid(pid) (for leaf node it has only three attributes: id,pid,name).

{'child': [{'id': '','child':[{'id': '','child':['name':'','id':'','pid':''], 'name': '', 'pid':''}], 'name': '', 'pid': ''}],'name':'','pid':'','id':''}

I want to convert it to a dataframe with three columns like:

    id, pid, name
1   .., ..., ....
2   .., ..., ....

With the data from all layers in three attributes (id,pid,name)

I have tried pandas.read_json with the default parameters but it seems that it cannot iterate the whole layers and the output is just like:

    id, pid, name, child
1   .., ..., ...., {'id':'','pid': '','name': '', 'child':[{...}]}
2   .., ..., ...., {'id':'','pid': '','name': '', 'child':[{...}]}

I am wondering whether there are some easy methods to solve this problem with or without pandas.

Upvotes: 0

Views: 2129

Answers (1)

natsuapo
natsuapo

Reputation: 613

I use a recursion to fulfill it and I have proved that it works on my data. 

import json
import pandas as pd


def test_iterate(df):
    global total_data
    total_data = total_data.append(df[['id','pid','name']])
    try:
        df['child'].apply(lambda x:test_iterate(pd.DataFrame(x)))
    except Exception as inst:
        print(inst)
        pass

if __name__ == '__main__':
    total_data = pd.DataFrame()
    loaddata = json.load(open('test.json'))
    df = pd.DataFrame(loaddata)
    test_iterate(df)
    total_data.to_csv('test.csv',index=None)

Upvotes: 1

Related Questions