How can i create a nested json in pandas from a dataframe using one column value as key?

Question

I have a dataframe df like below:

col1 | col2| col3| col4| col5
_____|_____|_____|_____|______
 1   |  X  |  A  |  a  |  x
 1   |  X  |  B  |  b  |  y

I need to convert it as below:

{'col1':'1', 'col2':'X' , 'A':{ 'col4':'a', 'col5':'x'}, 'B':{'col4':'b', 'col5':'y'}}

I have tried with below code:

json= df.groupby(['col1,'col2'],as_index='False')[['col3','col4','col5']]
                   .apply(lambda x:x.set_index('col3').to_dict(orient='index'))
                   .reset_index()
                   .to_json(orient='records'))

And it gives me output:

[{'col1':'1', 'col2':'X' ,'0':{ 'A':{ 'col4':'a', 'col5':'x'}, 'B':{'col4':'b', 'col5':'y'} }}]

I have tried using to_dict in place of to_json but it does not work as well. Above is the closest I came to meeting my requirement. I am assuming the '0' represents the index. Is there any way to remove it?

What do you want to remove?

Ann Kilzer
– Ann Kilzer

2019-11-24 13:40:25 +00:00
Commented Nov 24, 2019 at 13:40 — Ann Kilzer
– Ann Kilzer, Commented Nov 24, 2019 at 13:40

jezrael · Accepted Answer · 2019-11-24 13:40:10Z

1

Use:

json= (df.groupby(['col1','col2','col3'],as_index='False')[['col4','col5']]
                   .apply(lambda x: dict(x.values))
                   .unstack()
                   .reset_index()
                   .to_json(orient='records')
)
print (json)
[{"col1":1,"col2":"X","A":{"a":"x"},"B":{"b":"y"}}]

answered Nov 24, 2019 at 13:40

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

How can i create a nested json in pandas from a dataframe using one column value as key?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related