I have a pandas dataframe representing some basic weather info which looks like this
location | dDate | min-temp | max-temp | rain |
---|---|---|---|---|
Sydney | 2013-01-01 | 15 | 35 | 10 |
Sydney | 2013-01-02 | 16 | 36 | 5 |
Sydney | 2013-01-03 | 16 | 31 | 0 |
Sydney | 2013-01-04 | 16 | 31 | 2 |
To graph it using a JavaScript library such as C3, I need each column as an array in the JSON
{
"x": ["2013-01-01", "2013-01-02", "2013-01-03", "2013-01-04"],
"rain": [10, 5, 0, 2],
"max-temp": [35, 36, 31, 31]
}
formatting as column doesn't quite work
df = client.query(query).to_dataframe()
jsonstr = df.to_json(orient='columns')
It results in this
{
“location”:{
“0”:Sydney,
“1”:Sydney,
“2”:Sydney,
“3”:Sydney
},
"dDate":{
"0":2013-01-01,
"1":2013-01-02,
"2":2013-01-03,
"3":2013-01-04
},
“min-temp”:{
“0”:15,
“1”:16,
“2”:16,
“3”:16
},
…….
}
How can I reproduce the desired JSON where each colum is just an array of values?
to_json
only supports records and nested dict formats. To get a dict of lists, convert to dictionary first using to_dict
, then convert to json:
pd.io.json.dumps(df.to_dict(orient='list'))
# '{"x":["2013-01-01","2013-01-02","2013-01-03","2013-01-04"],"rain":[10,5,0,2],"max-temp":[35,36,31,31]}'