Python Forum
Convert Json to table format
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Convert Json to table format
#1
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" } ] } ]
Reply
#2
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 likes this post
Reply
#3
(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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  format json outputs ! evilcode1 3 1,692 Oct-29-2023, 01:30 PM
Last Post: omemoe277
Thumbs Up Convert word into pdf and copy table to outlook body in a prescribed format email2kmahe 1 705 Sep-22-2023, 02:33 PM
Last Post: carecavoador
  Python Script to convert Json to CSV file chvsnarayana 8 2,344 Apr-26-2023, 10:31 PM
Last Post: DeaD_EyE
  write json into a table herobpv 4 1,424 Jan-22-2023, 04:36 AM
Last Post: herobpv
  Convert .xlsx to Format as Table bnadir55 0 860 Aug-11-2022, 06:39 AM
Last Post: bnadir55
  how to convert and format a varable darktitan 4 1,623 May-29-2022, 12:59 PM
Last Post: darktitan
  Converting cells in excel to JSON format desmondtay 4 1,685 May-23-2022, 10:31 AM
Last Post: Larz60+
  Convert nested sample json api data into csv in python shantanu97 3 2,725 May-21-2022, 01:30 PM
Last Post: deanhystad
  json format Bubu93200 6 1,865 Apr-23-2022, 08:59 AM
Last Post: Bubu93200
  Convert python dataframe to nested json kat417 1 6,244 Mar-18-2022, 09:14 PM
Last Post: kat417

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020