Expand json data in a column inside dataframe

Question

I know there is a way to expand the columns without extracting and joining/concatenating/appending data.

I have this json data which I've already normalized but I have a column that has a nested json: Image of issue

So what I want to do is to expand this json data in a way that adds columns automatically in the dataframe without indexing since the data inside that column doesn´t have any key to pair, so it should be by position/row only.

What could I do to unnest it? Thanks!

Update: In a previous step I've already normalized it:

response1 = requests.get(url1, params=params1, headers=headertoken)
textresponse1 = response1.text
   if "El contrato enviado no tiene envios registrados." in textresponse1:
                continue
textresponse1 = json.loads(response1.text)
response1_df = pd.json_normalize(textresponse1['envios'])
enviosdf = pd.concat([enviosdf,response1_df])

Problem is that, after this, the column 'bultos' is another json. Which when you try to normalize it, this happens:

0   {'kilos': 0.0025, 'IdDeProducto': 'ysyHhBHttHd...
1   {'kilos': 0.0025, 'IdDeProducto': 'QNEOqaNXtsi...
2   {'kilos': 0.0025, 'IdDeProducto': 'V7b3D7xaSur...

The normalization doesn't normalize it nor expands it.

Does this answer your question? How to normalize a json file to pandas dataframe? — esqew
– esqew, Commented Nov 23, 2022 at 17:50

Bushmaster · Accepted Answer · 2022-11-23 19:21:53Z

-1

you should use explode before json_normalize because they are lists:

enviosdf=enviosdf.explode('bultos').reset_index(drop=True)
enviosdf=enviosdf.join(pd.json_normalize(enviosdf.pop('bultos')))

answered Nov 23, 2022 at 19:21

Bushmaster

4,6364 gold badges11 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

Expand json data in a column inside dataframe

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related