+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Convert Json to table format (/thread-38313.html)
Convert Json to table format - python_student - Sep-27-2022
I have a json as shown below and I need to convert this to table format. (all post about this did not help me and I think is because the json structure)
I tried using pandas but it is not working as I expected.
I need something like this
id, t_id, value, date_timestamp, type
179328741654819, 963852456741, 499.66, 2020-09-22T15:18:17, in
This is my code: (I am using Jupyter notebook)
import json
import pandas as pd
from IPython.display import display
# load json file
pd_object = pd.read_json('file.json',typ='series')
df = pd.DataFrame(pd_object)
display(df)But it show only one columnresult
Output:0 {'id': '179328741654819', 't_values'...
200 rows × 1 columnsOutput:[
{
"id": "179328741654819",
"t_values": [
{
"t_id": "963852456741",
"value": "499.66",
"date_timestamp": "2020-09-22T15:18:17",
"type": "in"
},
{
"t_id": "852951753456",
"value": "1386.78",
"date_timestamp": "2020-10-31T14:46:44",
"type": "in"
}
]
},
{
"id": "823971648264792",
"t_values": [
{
"t_id": "753958561456",
"value": "672.06",
"date_timestamp": "2020-03-16T22:41:16",
"type": "in"
},
{
"t_id": "321147951753",
"value": "773.88",
"date_timestamp": "2020-05-08T18:29:31",
"type": "out"
},
{
"t_id": "258951753852",
"value": "733.13",
"date_timestamp": null,
"type": "in"
}
]
}
] RE: Convert Json to table format - snippsat - Sep-28-2022
Use json_normalize for this.
Example.
import pandas as pd
lst = [
{
"id": "179328741654819",
"t_values": [
{
"t_id": "963852456741",
"value": "499.66",
"date_timestamp": "2020-09-22T15:18:17",
"type": "in"
},
{
"t_id": "852951753456",
"value": "1386.78",
"date_timestamp": "2020-10-31T14:46:44",
"type": "in"
}
]
},
{
"id": "823971648264792",
"t_values": [
{
"t_id": "753958561456",
"value": "672.06",
"date_timestamp": "2020-03-16T22:41:16",
"type": "in"
},
{
"t_id": "321147951753",
"value": "773.88",
"date_timestamp": "2020-05-08T18:29:31",
"type": "out"
},
{
"t_id": "258951753852",
"value": "733.13",
"date_timestamp": 'null',
"type": "in"
}
]
}
]>>> df = pd.json_normalize(lst, meta=['id'], record_path='t_values')
>>> df
t_id value date_timestamp type id
0 963852456741 499.66 2020-09-22T15:18:17 in 179328741654819
1 852951753456 1386.78 2020-10-31T14:46:44 in 179328741654819
2 753958561456 672.06 2020-03-16T22:41:16 in 823971648264792
3 321147951753 773.88 2020-05-08T18:29:31 out 823971648264792
4 258951753852 733.13 null in 823971648264792
>>> df = df.reindex(['id', 't_id', 'value', 'date_timestamp', 'type'], axis=1)
>>> df
id t_id value date_timestamp type
0 179328741654819 963852456741 499.66 2020-09-22T15:18:17 in
1 179328741654819 852951753456 1386.78 2020-10-31T14:46:44 in
2 823971648264792 753958561456 672.06 2020-03-16T22:41:16 in
3 823971648264792 321147951753 773.88 2020-05-08T18:29:31 out
4 823971648264792 258951753852 733.13 null in
RE: Convert Json to table format - python_student - Sep-28-2022
(Sep-28-2022, 08:19 AM)snippsat Wrote: Use json_normalize for this.
Example.
import pandas as pd lst = [ { "id": "179328741654819", "t_values": [ { "t_id": "963852456741", "value": "499.66", "date_timestamp": "2020-09-22T15:18:17", "type": "in" }, { "t_id": "852951753456", "value": "1386.78", "date_timestamp": "2020-10-31T14:46:44", "type": "in" } ] }, { "id": "823971648264792", "t_values": [ { "t_id": "753958561456", "value": "672.06", "date_timestamp": "2020-03-16T22:41:16", "type": "in" }, { "t_id": "321147951753", "value": "773.88", "date_timestamp": "2020-05-08T18:29:31", "type": "out" }, { "t_id": "258951753852", "value": "733.13", "date_timestamp": 'null', "type": "in" } ] } ]>>> df = pd.json_normalize(lst, meta=['id'], record_path='t_values') >>> df t_id value date_timestamp type id 0 963852456741 499.66 2020-09-22T15:18:17 in 179328741654819 1 852951753456 1386.78 2020-10-31T14:46:44 in 179328741654819 2 753958561456 672.06 2020-03-16T22:41:16 in 823971648264792 3 321147951753 773.88 2020-05-08T18:29:31 out 823971648264792 4 258951753852 733.13 null in 823971648264792 >>> df = df.reindex(['id', 't_id', 'value', 'date_timestamp', 'type'], axis=1) >>> df id t_id value date_timestamp type 0 179328741654819 963852456741 499.66 2020-09-22T15:18:17 in 1 179328741654819 852951753456 1386.78 2020-10-31T14:46:44 in 2 823971648264792 753958561456 672.06 2020-03-16T22:41:16 in 3 823971648264792 321147951753 773.88 2020-05-08T18:29:31 out 4 823971648264792 258951753852 733.13 null in
Thank you so much. This was I needed