Nested JSON to Pandas DataFrames

Question

I'm trying to expand nested json to pandas dataframe, but the offered solutions haven't worked for me.

My case is:

I have json data in MongoDB.

When I connect with this database, it works well, but the problem is when I need to expand nested json to pandas dataframes, because there are columns where I have json object.

index   id    name         location
0       0001  George       [{'country':'Colombia', 'department': 'Antioquia', 'city': 'Medellin'}]
1       0002  Gilberth     [{'country':'Colombia', 'department': 'Antioquia', 'city': 'Medellin'}]
2       0003  Christopher  [{'country':'Colombia', 'department': 'Antioquia', 'city': 'Medellin'}]

I need to convert to dataframe something like this:

index   id    name         country   department  city
0       0001  George       Colombia  Antioquia   Medellin
1       0002  Gilberth     Colombia  Antioquia   Medellin
2       0003  Christopher  Colombia  Antioquia   Medellin

Here I show how to read json data:

json_documents = list(properties.find({}))
df = pd.DataFrame(json_documents)
df.head(2)

Thanks in advance.

ansev · Answer 1 · 2019-10-23T23:10:03.807

1

Use:

df2=df['location'].apply(lambda x: pd.DataFrame(x)) 
pd.concat([df[df.columns[:-1]],df2],axis=1)

Also you can try json_normalize

from pandas.io.json import json_normalize
df=json_normalize(json_documents)

edited Oct 23 '19 at 23:10

answered Oct 23 '19 at 22:59

ansev

30,322
5
17
31

The first solution doesn't work for me. – Stiven Ramírez Arango Oct 24 '19 at 13:25
0 address city country d... 1 address city ... Name: location, dtype: object – Stiven Ramírez Arango Oct 24 '19 at 13:25
I obtained this. – Stiven Ramírez Arango Oct 24 '19 at 13:25
The second solution works if exists the column in all json documents, but sometimes a specific column doesn't exist. How can I do in that situation? – Stiven Ramírez Arango Oct 24 '19 at 13:28

score 0 · Answer 2 · answered Oct 23 '19 at 22:49

0

Did you try.

df["location"].apply(pd.Series)

answered Oct 23 '19 at 22:49

Florian Bernard

2,561
1
9
22

It doesn't work. – Stiven Ramírez Arango Oct 24 '19 at 13:29
I obtained this: 0 {'country': 'Colombia', 'department': 'Antioqu... 1 {'country': 'Colombia', 'department': 'Antioqu... 2 {'country': 'Colombia', 'department': 'Antioqu... 3 {'country': 'Colombia', 'department': 'Antioqu... 4 {'country': 'Colombia', 'department': 'Antioqu... – Stiven Ramírez Arango Oct 24 '19 at 13:29

Nested JSON to Pandas DataFrames

2 Answers2