How to convert list of nested dictionary to pandas DataFrame?

Question

I have some data containing nested dictionaries like below:

mylist = [{"a": 1, "b": {"c": 2, "d":3}}, {"a": 3, "b": {"c": 4, "d":3}}]

If we convert it to pandas DataFrame,

import pandas as pd 

result_dataframe = pd.DataFrame(mylist)
print(result_dataframe)

It will output:

    a   b
  0 1   {'c': 2, 'd': 3}
  1 3   {'c': 4, 'd': 3}

I want to convert the list of dictionaries and ignore the key of the nested dictionary. My code is below:

new_dataframe = result_dataframe.drop(columns=["b"])
b_dict_list = [document["b"] for document in mylist]
b_df = pd.DataFrame(b_dict_list)
frames = [new_dataframe, b_df]
total_frame = pd.concat(frames, axis=1)

The total_frame is which I want:

    a   c   d
0   1   2   3
1   3   4   3

But I think my code is a little complicated. Is there any simple way to deal with this problem? Thank you.

df_data = [{"a": el["a"], **el["b"]} for el in mylist]

Tom Wojcik
– Tom Wojcik

2019-01-23 06:40:53 +00:00
Commented Jan 23, 2019 at 6:40 — Tom Wojcik
– Tom Wojcik, Commented Jan 23, 2019 at 6:40
@TomWojcik Hi, how about if the dictionary has many keys

lazy_frog
– lazy_frog

2019-01-23 08:22:28 +00:00
Commented Jan 23, 2019 at 8:22 — lazy_frog
– lazy_frog, Commented Jan 23, 2019 at 8:22

blackraven · Accepted Answer · 2022-08-06 04:05:43Z

15

I had a similar problem to this one. I used pd.json_normalize(x) and it worked. The only difference is that the column names of the data frame will look a little different.

mylist = [{"a": 1, "b": {"c": 2, "d":3}}, {"a": 3, "b": {"c": 4, "d":3}}]
df = pd.json_normalize(mylist)
print(df)

Output:

	a	b.c	b.d
0	1	2	3
1	3	4	3

edited Aug 6, 2022 at 4:05

blackraven

5,6797 gold badges27 silver badges51 bronze badges

answered Mar 18, 2021 at 3:11

Stein Monteiro

3113 silver badges5 bronze badges

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

1 Comment

Volodymyr Obrizan Over a year ago

It works for me. It is an elegant solution. Thank you!

jezrael · Accepted Answer · 2019-01-23 06:52:51Z

9

Use dict comprehension with pop for extract value b and merge dictionaries:

a = [{**x, **x.pop('b')} for x in mylist]
print (a)
[{'a': 1, 'c': 2, 'd': 3}, {'a': 3, 'c': 4, 'd': 3}]

result_dataframe = pd.DataFrame(a)
print(result_dataframe)
   a  c  d
0  1  2  3
1  3  4  3

Another solution, thanks @Sandeep Kadapa :

a = [{'a': x['a'], **x['b']} for x in mylist] 
#alternative
a = [{'a': x['a'], **x.get('b')} for x in mylist]

edited Jan 23, 2019 at 6:52

answered Jan 23, 2019 at 6:39

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

3 Comments

economy Over a year ago

This is a really elegant solution, but unfortunately I haven't been able to extend it to take in an arbitrary list of columns, since unpacking is not allowed inside a dict comprehension (e.g. [{**x.pop(k) for k in x.keys()} for x in mylist]. Is there an easy way to use with an arbitrary list of columns?

jezrael Over a year ago

@economy Now I am on phone only, because holiday, so the best create new question, also with sample data and expected output, thanks.

wsdzbm Over a year ago

Not a safe way. x.pop destroyed the original dicts.

anky · Accepted Answer · 2019-01-23 06:45:36Z

2

Or by applying pd.Series() to your method:

mylist = [{"a": 1, "b": {"c": 2, "d":3}}, {"a": 3, "b": {"c": 4, "d":3}}]
result_dataframe = pd.DataFrame(mylist)
result_dataframe.drop('b',1).join(result_dataframe.b.apply(pd.Series))

   a  c  d
0  1  2  3
1  3  4  3

edited Jan 23, 2019 at 6:45

answered Jan 23, 2019 at 6:44

anky

75.3k11 gold badges46 silver badges76 bronze badges

1 Comment

anky Over a year ago

@jezrael ok, noted. :)

ycx · Accepted Answer · 2019-01-23 07:00:18Z

I prefer to write a function that accepts your mylist and converts it 1 nested layer down and returns a dictionary. This has the added advantage of not requiring you to 'manually' know what key like b to convert. So this function works for all nested keys 1 layer down.

mylist = [{"a": 1, "b": {"c": 2, "d":3}}, {"a": 3, "b": {"c": 4, "d":3}}]
import pandas as pd

def dropnested(alist):
    outputdict = {}
    for dic in alist:
        for key, value in dic.items():
            if isinstance(value, dict):
                for k2, v2, in value.items():
                    outputdict[k2] = outputdict.get(k2, []) + [v2]
            else:
                outputdict[key] = outputdict.get(key, []) + [value]
    return outputdict    

df = pd.DataFrame.from_dict(dropnested(mylist))
print (df)
#   a  c  d
#0  1  2  3
#1  3  4  3

If you try:

mylist = [{"a": 1, "b": {"c": 2, "d":3}, "g": {"e": 2, "f":3}}, 
          {"a": 3, "z": {"c": 4, "d":3}, "e": {"e": 2, "f":3}}]
df = pd.DataFrame.from_dict(dropnested(mylist))
print (df)
#   a  c  d  e  f
#0  1  2  3  2  3
#1  3  4  3  2  3

We can see here that it converts keys b,g,z,e without issue, as opposed to having to define each and every nested key name to convert

Collectives™ on Stack Overflow

How to convert list of nested dictionary to pandas DataFrame?

4 Answers 4

1 Comment

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related