Ad

Converting Nested List Containing Multilevel Dictionary In Python

I have a json file with nested lists containing multilevel dictionary. I am trying to create a python DataFrame from this data.

Loading data:

data = []
with open('TREC_blog_2012.json') as f:
for line in f:
    data.append(json.loads(line))

Data output:

IN LIST FORMAT: data[0] 

{'id': '1d3bc37004e71da2816dbfda8df90746',
'article_url': 'https://www.washingtonpost.com/express/wp/2012/01/03/month-of-muscle/',
'title': 'Month of Muscle',
'author': 'Vicky Hallett',
'published_date': 1325608933000,
'contents': [{'content': 'Express', 'mime': 'text/plain', 'type': 'kicker'},
{'content': 'Month of Muscle', 'mime': 'text/plain', 'type': 'title'},
{'content': 'By Vicky Hallett', 'mime': 'text/plain', 'type': 'byline'},
{'content': 1325608933000, 'mime': 'text/plain', 'type': 'date'},
{'content': 'SparkPeople trainer Nicole Nichols asks for only 28 days to get you into shape',
'mime': 'text/plain',
'type': 'deck'},
{'fullcaption': 'Nicole Nichols, front, chose backup exercisers with strong but realistic physiques to make the program less intimidating.',
'imageURL': 'http://www.expressnightout.com/wp-content/uploads/2012/01/SparkPeople28DayBootcamp.jpg',
'mime': 'image/jpeg',
'imageHeight': 201,
'imageWidth': 300,
'type': 'image',
'blurb': 'Nicole Nichols, front, chose backup exercisers with strong but realistic physiques to make the program less intimidating.'},
 {'content': 'If you’ve seen a Nicole Nichols workout before, chances are it was on YouTube. The fitness expert, known as just Coach Nicole to the millions of members of <a target="_blank" rel="nofollow noreferrer" href="http://www.sparkpeople.com" target="_blank">SparkPeople.com</a>, has filmed dozens of routines for the free health website. The popular videos showcasing her girl-next-door style, gentle encouragement and clear cueing have built such a devoted following that the American Council on Exercise and Life Fitness just named her “America’s top personal trainer to watch.”',
'subtype': 'paragraph',
'type': 'sanitized_html',
'mime': 'text/html'},
{'content': '<strong>3. Prioritize.</strong> When people say they can’t fit exercise in their schedule, Nichols always asks, “How much TV do you watch?” Use your shows as a reward for your workout instead of the replacement, she suggests.',
'subtype': 'paragraph',
'type': 'sanitized_html',
'mime': 'text/html'},
{'role': '',
'type': 'author_info',
'name': 'Vicky Hallett',
'bio': 'Vicky Hallett is a freelancer and former MisFits columnist.'}],
'type': 'blog',
'source': 'The Washington Post'}

I want to convert this data into a DataFrame type with keys as columns and it's respective values as row values.

But the problem I am facing is the key "contents" contains a list of multilevel dictionary values which I don't understand how to convert into a proper DataFrame value.

The method I tried:

df = pd.DataFrame(data)
test = pd.DataFrame(df['contents'][0])
test.head()

gives me the output of df['contents'] as

output received for test

The data is not aligned properly and is not correctly assigned if I try the above method. Any suggestion on how to resolve this list of the dictionaries of the contents key into a proper dataframe?

TIA:)

Ad

Answer

I would do something like this :

new_data = []
for row in data: 
    if 'contents' in row:
        for content in row['contents']:
            new_dict = dict(row)
            del new_dict['contents']

            for key, value in content.items():
                new_dict['content_{}'.format(key)] = value

            new_data.append(new_dict)
    else:
        new_data.append(row)

Be aware that I create one line of dataframe per element in 'contents'. So you will have 9 lines corresponding to the elements in data[0].

pd.DataFrame.from_dict(new_data)

enter image description here

Basically you have two ways to convert a nested dict to a 2D dataframe : you can keep one line per element of your list but you will need to add a lot of columns (one for each element of dicts contained in 'contents', numbers of columns may vary a lot and become a pain in the neck) or by adding one line per element in 'contents'. I think the last one suits well for your case.

Ad
source: stackoverflow.com
Ad