Pandas JSON Normalize multiple columns in a dataframe

Question

So I have the following dataframe:

The JSON blobs all look something like this:

{"id":"dddd1", "random_number":"77777"}

What I want my dataframe to look like is something like this:

Basically what I need is to get a way to iterate and normalize all the JSON blob columns and put them back in the dataframe in the proper rows (0-99). I have tried the following:

pd.json_normalize(data_frame.iloc[:, JSON_0,JSON_99])

I get the following error:

IndexingError: Too many indexers

I could go through and normalize each JSON_BLOB column individually however that is inefficient, I cant think of a proper way to do this via a Lambda function or for loop because of the JSON blob. The for loop I wrote gives me the same error:

array=[]
for app in data_frame.iloc[:, JSON_0,JSON_99]:
    data = {
        'id': data['id']
        
    }
    array.append(data)


test= pd.DataFrame(array)


IndexingError: Too many indexers

Also some of the JSON_Blobs have NAN values

Any suggestions would be great.

I have a funny feeling this is gonna be super easy when I read the responses btw — jthedudeoflife
– jthedudeoflife, Commented Dec 2, 2022 at 20:02

Bushmaster · Accepted Answer · 2022-12-02 21:30:16Z

2

Can you try this:

normalized = pd.concat([df[i].apply(pd.Series) for i in df.iloc[:,2:]],axis=1) #2 is the position number of JSON_0.
final = pd.concat([df[['Root_id_PK','random_number']],normalized],axis=1)

if you want the column names as in the question:

normalized = pd.concat([df[i].apply(pd.Series).rename(columns={'id':'id_from_{}'.format(i),'random_number':'random_number_from_{}'.format(i)}) for i in df.iloc[:,2:]],axis=1)
final = pd.concat([df[['Root_id_PK','random_number']],normalized],axis=1)

edited Dec 2, 2022 at 21:30

answered Dec 2, 2022 at 20:40

Bushmaster

4,6364 gold badges11 silver badges34 bronze badges

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

5 Comments

jthedudeoflife Over a year ago

it looks like it works but my notebook cant handle this much data, Im gonna try to cut down on the cells and see if it works

Bushmaster Over a year ago

Ok. I made an update to the answer, Check it again.

Bushmaster Over a year ago

wait, I am preparing a much more effective solution.

Bushmaster Over a year ago

Sorry, the method i tried took a little longer than the answer. So the solution in my answer is faster.

jthedudeoflife Over a year ago

TY, I was on this one for a while, much apprecaited

Collectives™ on Stack Overflow

Pandas JSON Normalize multiple columns in a dataframe

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related