Normalize JSON Object from a multiple column dataframe

Question

I am trying to extract some information of a JSON I got from a request, but have some troubles at one point.

As my data is a nested JSON, I first used

df = json_normalize(response)

to get the infos into a df. But as a result I get a df, which still has a column with JSON-style information in it, which I can't extract with json_normalize anymore. The df looks like:

id    duration     columnA                            
1     12         [{"A": "600", "B": "30", "C": "50"},{"A": "200", "B": "35", "C": "50"}]  
2     5          [{"A": "300", "B": "70", "C": "80"},{"A": "400", "B": "76", "C": "90"}]

But when I am trying

df2 = json_normalize(df['columnA'])

I only get an error "AttributeError: 'list' object has no attribute 'values'"

As result I would like to get a df like:

post your response contents

RomanPerekhrest
– RomanPerekhrest

2019-07-28 16:29:06 +00:00
Commented Jul 28, 2019 at 16:29 — RomanPerekhrest
– RomanPerekhrest, Commented Jul 28, 2019 at 16:29

Alex Hall · Accepted Answer · 2019-07-28 16:33:03Z

2

You can normalise each of the inner objects and concat the resulting dataframes together:

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

df = pd.DataFrame(
    [
        [
            1,
            2,
            [{"A": "600", "B": "30", "C": "50"}, {"A": "200", "B": "35", "C": "50"}],
        ],
        [
            2,
            5,
            [{"A": "300", "B": "70", "C": "80"}, {"A": "400", "B": "76", "C": "90"}],
        ],
    ],
    columns='id duration columnA'.split(),
)

print(pd.concat(list(df.columnA.map(json_normalize))))

Output:

     A   B   C
0  600  30  50
1  200  35  50
0  300  70  80
1  400  76  90

answered Jul 28, 2019 at 16:33

Alex Hall

36.2k5 gold badges63 silver badges98 bronze badges

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

Comments

Nidhin Bose J. · Accepted Answer · 2019-07-28 16:24:33Z

0

Could you try this?

df = json_normalize(response, 'columnA', ['A','B','C'])

Assuming A, B and C are flat

answered Jul 28, 2019 at 16:24

Nidhin Bose J.

1,09215 silver badges28 bronze badges

Collectives™ on Stack Overflow

Normalize JSON Object from a multiple column dataframe

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related