CODEWITHSUNDEEP
pythonjsonpandasdataframe
Georg Heiler
Georg Heiler

Reputation: 17676

Parse column of nested JSON as pandas DataFrame

How can I simply separate a JSON column inside pandas:

pd.DataFrame({
    'col1':[1,2], 
    'col2':["{'foo':1, 'bar':2, 'baz':{'foo':2, 'x':1}}",
            "{'foo':3, 'bar':5, 'baz':{'foo':2, 'x':1}}"]})

   col1                                        col2
0     1  {'foo':1, 'bar':2, 'baz':{'foo':2, 'x':1}}
1     2  {'foo':3, 'bar':5, 'baz':{'foo':2, 'x':1}}

into real columns in a simple and python way?

edit

Desired output:

pd.DataFrame({'col1':[1,2], 'foo':[1,3], 'bar':[2,5], 
              'baz_foo':[2,2], 'baz_x':[1,1]})

   col1  foo  bar  baz_foo  baz_x
0     1    1    2        2      1
1     2    3    5        2      1

Upvotes: 3

Views: 1030

Answers (2)

Joseph Cottam
Joseph Cottam

Reputation: 848

json_normalize changes nested json-like dictionaries into a table. The nesting path is used to create the column names.

import pandas as pd
from pandas.io.json import json_normalize

data = {'col1':[1,2,3], 
        'col2':[{'foo': 1, 'bar': 2, 'baz': {'foo': 2, 'x': 1}},
                {'foo': 3, 'bar': 5, 'baz': {'foo': None, 'x': 1}}]}

pd.DataFrame(data={"col1": data["col1"]})\
  .join(json_normalize(data["col2"]))

Upvotes: 0

cs95
cs95

Reputation: 402333

json_normalize is the right way to tackle nested JSON data.

import ast
from pandas.io.json import json_normalize

v = json_normalize([ast.literal_eval(j) for j in df.pop('col2')], sep='_')
pd.concat([df, v], 1)

   col1  bar  baz_foo  baz_x  foo
0     1    2        2      1    1
1     2    5        2      1    3

Note, you will still have to convert the JSON to a dictionary first.

If you want to handle NaNs in "col2", try using join at the end:

df = pd.DataFrame({
    'col1':[1,2,3], 
    'col2':["{'foo':1, 'bar':2, 'baz':{'foo':2, 'x':1}}",
            "{'foo':3, 'bar':5, 'baz':{'foo':2, 'x':1}}", 
            np.nan]})


v = json_normalize([
    ast.literal_eval(j) for j in df['col2'].dropna()], sep='_'
)
v.index = df.index[df.pop('col2').notna()]

df.join(v, how='left')
   col1  bar  baz_foo  baz_x  foo
0     1  2.0      2.0    1.0  1.0
1     2  5.0      2.0    1.0  3.0
2     3  NaN      NaN    NaN  NaN

Upvotes: 5

Related Questions