How to create custom json from pandas dataframe

Question

I have a data frame like this,

df
col1    col2    col3
 A       1       2
 B       2       3
 C       4       5
 B       6       7
 B       9       10
 C       11      12
 A       13      14

I need to convert it to json format. When I converting using to_jon the outpput is like this,

{"col1":{"0":"A","1":"B","2":"C","3":"B","4":"B","5":"C","6":"A"},"col2": 
{"0":1,"1":2,"2":4,"3":6,"4":9,"5":11,"6":13},"col3": 
{"0":2,"1":3,"2":5,"3":7,"4":10,"5":12,"6":14}}

But the json I am looking for will look like,

{"A":{"col2":[1,13],"col3":[2,14]}, "B":{"col2":[2,6,9],"col3":[3,7,10]}, "C":{"col2": 
[4,11],"col3":[5,12]}}

I am looking for pandas shortcuts/ pythonic way to do this task efficiently.

anky · Accepted Answer · 2020-07-16 12:33:35Z

1

Try with groupby+transpose:

js = df.groupby('col1').agg(list).T.to_json()
#or df.pivot_table(columns='col1',aggfunc=list).to_json()

print(js)

{"A":{"col2":[1,13],"col3":[2,14]},"B":{"col2":[2,6,9],"col3":[3,7,10]},
 "C":{"col2":[4,11],"col3":[5,12]}}

edited Jul 16, 2020 at 12:33

answered Jul 16, 2020 at 12:27

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

How to create custom json from pandas dataframe

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