I'd like to store JSON data in a Python Pandas DataFrame
my JSON data is a dict of dicts of dicts like this
d = {
"col1": {
"row1": {
"data1": "0.87",
"data2": "Title col1",
"data3": "14.4878",
"data4": "Title row1"
},
"row2": {
"data1": "15352.3",
"data2": "Title col1",
"data3": "14.9561",
"data4": "Title row2"
},
"row3": {
"data1": "0",
"data2": "Title col1",
"data3": "16.8293",
"data4": "Title row3"
}
},
"col2": {
"row1": {
"data1": "0.87",
"data2": "Title col2",
"data3": "24.4878",
"data4": "Title row1"
},
"row2": {
"data1": "15352.3",
"data2": "Title col2",
"data3": "24.9561",
"data4": "Title row2"
},
"row3": {
"data1": "0",
"data2": "Title col2",
"data3": "26.8293",
"data4": "Title row3"
}
}
}
I did this to put my data in a DataFrame
import pandas as pd
df=pd.DataFrame(d)
I get this
In [1]: df
Out[1]:
col1 col2
row1 {'data4': 'Title col1', 'data1': '0.87', 'data3': {'data4': 'Title col1', 'data1': '0.87', 'data3':
row2 {'data4': 'Title col2', 'data1': '15352.3', 'data {'data4': 'Title col2', 'data1': '15352.3', 'data
row3 {'data4': 'Title col3', 'data1': '0', 'data3': '1 {'data4': 'Title col3', 'data1': '0', 'data3': '2
My problem is that my DataFrame contains dicts instead of values.
I wonder how I can manage multidimensionnal data (more than 2 dimensions... 3 dimensions here) with a Pandas DataFrame.
Each dict inside DataFrame have the same keys.
df = pd.Panel.from_dict(d).to_frame()
output:
col1 col2
major minor
data1 row1 0.87 0.87
row2 15352.3 15352.3
row3 0 0
data2 row1 Title col1 Title col2
row2 Title col1 Title col2
row3 Title col1 Title col2
data3 row1 14.4878 24.4878
row2 14.9561 24.9561
row3 16.8293 26.8293
data4 row1 Title row1 Title row1
row2 Title row2 Title row2
row3 Title row3 Title row3
If you don't want use Panel:
pd.concat(map(pd.DataFrame, d.itervalues()), keys=d.keys()).stack().unstack(0)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With