Convert Json to table formathttps://python-forum.io/thread-38313.html

[python_student]

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 column
result

Output:
0	{'id': '179328741654819', 't_values'... 200 rows × 1 columns

The json structure is:

Output:
[	{	"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"	}	]	} ]

[snippsat]

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

[python_student]

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

[Larz60+]

python comes with a built-in tool, json.tool
to use: python -m json.tool jsonfilename > textfilename