Best way to convert Pandas dataframe to json in python

Question

I'm trying to convert pandas dataframe to json in python. but I couldn't get the result format that want in json i'm new to pandas is there a way to pivot and simply use All I can think of is create loop over and over to get key "freq" and state. But I feel like there must be a better way. Any recommendation would be appreciated.

json : I want :

 [
    {State:'AL',freq:{low:4786, mid:1319, high:249}}
    ,{State:'AZ',freq:{low:1101, mid:412, high:674}}
    ,{State:'CT',freq:{low:932, mid:2149, high:418}}
    ,{State:'DE',freq:{low:832, mid:1152, high:1862}}
    ,{State:'FL',freq:{low:4481, mid:3304, high:948}}
    ,{State:'GA',freq:{low:1619, mid:167, high:1063}}
    ,{State:'IA',freq:{low:1819, mid:247, high:1203}}
    ,{State:'IL',freq:{low:4498, mid:3852, high:942}}
    ,{State:'IN',freq:{low:797, mid:1849, high:1534}}
    ,{State:'KS',freq:{low:162, mid:379, high:471}}
    ];

but my dataframe have 3 columns like this :

Dataframe : I got

State   type    freq
AZ  low 1101
CT  low 932
DE  low 832
FL  low 4481
GA  low 1619
IA  low 1819
IL  low 4498
IN  low 797
KS  low 162
AZ  mid 412
CT  mid 2149
DE  mid 1152
FL  mid 3304
GA  mid 167
IA  mid 247
IL  mid 3852
IN  mid 1849
KS  mid 379
AZ  high    674
CT  high    418
DE  high    1862
FL  high    948
GA  high    1063
IA  high    1203
IL  high    942
IN  high    1534
KS  high    471

Paul H · Accepted Answer · 2014-11-03 21:47:25Z

2

import pandas as pd

data = """\
State   type    freq
AZ  low 1101
CT  low 932
DE  low 832
FL  low 4481
GA  low 1619
IA  low 1819
IL  low 4498
IN  low 797
KS  low 162
AZ  mid 412
CT  mid 2149
DE  mid 1152
FL  mid 3304
GA  mid 167
IA  mid 247
IL  mid 3852
IN  mid 1849
KS  mid 379
AZ  high    674
CT  high    418
DE  high    1862
FL  high    948
GA  high    1063
IA  high    1203
IL  high    942
IN  high    1534
KS  high    471"""

data = pd.DataFrame([line.split() for line in data.splitlines()[1:]],
                    columns=data.splitlines()[0].split())

bystate = data.pivot('State', 'type')

prints

          freq            
   type   high   low   mid
   State                  
   AZ      674  1101   412
   CT      418   932  2149
   DE     1862   832  1152
   FL      948  4481  3304
   GA     1063  1619   167
   IA     1203  1819   247
   IL      942  4498  3852
   IN     1534   797  1849
   KS      471   162   379

Once you have your final dataframe, to you can use bystate.to_json() to get a JSON-formatted string .

edited Nov 3, 2014 at 21:47

Paul H

68.7k23 gold badges165 silver badges139 bronze badges

answered Oct 30, 2014 at 23:39

holdenweb

37.8k7 gold badges62 silver badges80 bronze badges

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

1 Comment

Paul H Over a year ago

OP needs JSON output. Just add bystate.json

Collectives™ on Stack Overflow

Best way to convert Pandas dataframe to json in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related