Sai

Different JSON Formats Supported by Pandas

[

Different JSON Formats supported by Pandas¶

Let us go through the details about different standard JSON formats supported by Pandas.

Here are the arguments we need to pass to read_json or to_json for respective JSON formats.
- orient=split – columns, index, and data are separated.
- orient=records – array of json documents (eg: courses.json)
- lines=True – Multiple jsons with one json per line (eg: customers.json)
We can use to_json to display the output from buffer. We can also write to files using to_json.
We can specify orient as part of to_json along with _ as path or buffer to see the output with out writing to the files.
Here are examples with different supported JSON formats.

In [1]:

!ls -ltr customers.json

-rw-rw-r-- 1 itversity itversity 1338 Mar  8 02:04 customers.json

In [2]:

import pandas as pd

In [3]:

df = pd.read_json('customers.json', lines=True)

In [4]:

df

Out[4]:

	id	first_name	last_name	email	gender	ip_address
0	1	Frasco	Necolds	fnecolds0@vk.com	Male	243.67.63.34
1	2	Dulce	Santos	dsantos1@mashable.com	Female	60.30.246.227
2	3	Prissie	Tebbett	ptebbett2@infoseek.co.jp	Genderfluid	22.21.162.56
3	4	Schuyler	Coppledike	scoppledike3@gnu.org	Agender	120.35.186.161
4	5	Leopold	Jarred	ljarred4@wp.com	Agender	30.119.34.4
5	6	Joanna	Teager	jteager5@apache.org	Bigender	245.221.176.34
6	7	Lion	Beere	lbeere6@bloomberg.com	Polygender	105.54.139.46
7	8	Marabel	Wornum	mwornum7@posterous.com	Polygender	247.229.14.25
8	9	Helenka	Mullender	hmullender8@cloudflare.com	Non-binary	133.216.118.88
9	10	Christine	Swane	cswane9@shop-pro.jp	Polygender	86.16.210.164

In [5]:

df.to_json(orient='split') # columns, index and data are separated

Out[5]:

'{"columns":["id","first_name","last_name","email","gender","ip_address"],"index":[0,1,2,3,4,5,6,7,8,9],"data":[[1,"Frasco","Necolds","fnecolds0@vk.com","Male","243.67.63.34"],[2,"Dulce","Santos","dsantos1@mashable.com","Female","60.30.246.227"],[3,"Prissie","Tebbett","ptebbett2@infoseek.co.jp","Genderfluid","22.21.162.56"],[4,"Schuyler","Coppledike","scoppledike3@gnu.org","Agender","120.35.186.161"],[5,"Leopold","Jarred","ljarred4@wp.com","Agender","30.119.34.4"],[6,"Joanna","Teager","jteager5@apache.org","Bigender","245.221.176.34"],[7,"Lion","Beere","lbeere6@bloomberg.com","Polygender","105.54.139.46"],[8,"Marabel","Wornum","mwornum7@posterous.com","Polygender","247.229.14.25"],[9,"Helenka","Mullender","hmullender8@cloudflare.com","Non-binary","133.216.118.88"],[10,"Christine","Swane","cswane9@shop-pro.jp","Polygender","86.16.210.164"]]}'

In [6]:

pd.read_json(_, orient='split') # Creating data frame by using data from buffer

Out[6]:

	id	first_name	last_name	email	gender	ip_address
0	1	Frasco	Necolds	fnecolds0@vk.com	Male	243.67.63.34
1	2	Dulce	Santos	dsantos1@mashable.com	Female	60.30.246.227
2	3	Prissie	Tebbett	ptebbett2@infoseek.co.jp	Genderfluid	22.21.162.56
3	4	Schuyler	Coppledike	scoppledike3@gnu.org	Agender	120.35.186.161
4	5	Leopold	Jarred	ljarred4@wp.com	Agender	30.119.34.4
5	6	Joanna	Teager	jteager5@apache.org	Bigender	245.221.176.34
6	7	Lion	Beere	lbeere6@bloomberg.com	Polygender	105.54.139.46
7	8	Marabel	Wornum	mwornum7@posterous.com	Polygender	247.229.14.25
8	9	Helenka	Mullender	hmullender8@cloudflare.com	Non-binary	133.216.118.88
9	10	Christine	Swane	cswane9@shop-pro.jp	Polygender	86.16.210.164

In [7]:

df.to_json(orient='records') # array of json documents

Out[7]:

'[{"id":1,"first_name":"Frasco","last_name":"Necolds","email":"fnecolds0@vk.com","gender":"Male","ip_address":"243.67.63.34"},{"id":2,"first_name":"Dulce","last_name":"Santos","email":"dsantos1@mashable.com","gender":"Female","ip_address":"60.30.246.227"},{"id":3,"first_name":"Prissie","last_name":"Tebbett","email":"ptebbett2@infoseek.co.jp","gender":"Genderfluid","ip_address":"22.21.162.56"},{"id":4,"first_name":"Schuyler","last_name":"Coppledike","email":"scoppledike3@gnu.org","gender":"Agender","ip_address":"120.35.186.161"},{"id":5,"first_name":"Leopold","last_name":"Jarred","email":"ljarred4@wp.com","gender":"Agender","ip_address":"30.119.34.4"},{"id":6,"first_name":"Joanna","last_name":"Teager","email":"jteager5@apache.org","gender":"Bigender","ip_address":"245.221.176.34"},{"id":7,"first_name":"Lion","last_name":"Beere","email":"lbeere6@bloomberg.com","gender":"Polygender","ip_address":"105.54.139.46"},{"id":8,"first_name":"Marabel","last_name":"Wornum","email":"mwornum7@posterous.com","gender":"Polygender","ip_address":"247.229.14.25"},{"id":9,"first_name":"Helenka","last_name":"Mullender","email":"hmullender8@cloudflare.com","gender":"Non-binary","ip_address":"133.216.118.88"},{"id":10,"first_name":"Christine","last_name":"Swane","email":"cswane9@shop-pro.jp","gender":"Polygender","ip_address":"86.16.210.164"}]'

In [8]:

pd.read_json(_) # the default for orient is None which is similar to records

Out[8]:

	id	first_name	last_name	email	gender	ip_address
0	1	Frasco	Necolds	fnecolds0@vk.com	Male	243.67.63.34
1	2	Dulce	Santos	dsantos1@mashable.com	Female	60.30.246.227
2	3	Prissie	Tebbett	ptebbett2@infoseek.co.jp	Genderfluid	22.21.162.56
3	4	Schuyler	Coppledike	scoppledike3@gnu.org	Agender	120.35.186.161
4	5	Leopold	Jarred	ljarred4@wp.com	Agender	30.119.34.4
5	6	Joanna	Teager	jteager5@apache.org	Bigender	245.221.176.34
6	7	Lion	Beere	lbeere6@bloomberg.com	Polygender	105.54.139.46
7	8	Marabel	Wornum	mwornum7@posterous.com	Polygender	247.229.14.25
8	9	Helenka	Mullender	hmullender8@cloudflare.com	Non-binary	133.216.118.88
9	10	Christine	Swane	cswane9@shop-pro.jp	Polygender	86.16.210.164

In [9]:

df.to_json(orient='records', lines=True) # Multiple jsons with one json per line

Out[9]:

'{"id":1,"first_name":"Frasco","last_name":"Necolds","email":"fnecolds0@vk.com","gender":"Male","ip_address":"243.67.63.34"}\n{"id":2,"first_name":"Dulce","last_name":"Santos","email":"dsantos1@mashable.com","gender":"Female","ip_address":"60.30.246.227"}\n{"id":3,"first_name":"Prissie","last_name":"Tebbett","email":"ptebbett2@infoseek.co.jp","gender":"Genderfluid","ip_address":"22.21.162.56"}\n{"id":4,"first_name":"Schuyler","last_name":"Coppledike","email":"scoppledike3@gnu.org","gender":"Agender","ip_address":"120.35.186.161"}\n{"id":5,"first_name":"Leopold","last_name":"Jarred","email":"ljarred4@wp.com","gender":"Agender","ip_address":"30.119.34.4"}\n{"id":6,"first_name":"Joanna","last_name":"Teager","email":"jteager5@apache.org","gender":"Bigender","ip_address":"245.221.176.34"}\n{"id":7,"first_name":"Lion","last_name":"Beere","email":"lbeere6@bloomberg.com","gender":"Polygender","ip_address":"105.54.139.46"}\n{"id":8,"first_name":"Marabel","last_name":"Wornum","email":"mwornum7@posterous.com","gender":"Polygender","ip_address":"247.229.14.25"}\n{"id":9,"first_name":"Helenka","last_name":"Mullender","email":"hmullender8@cloudflare.com","gender":"Non-binary","ip_address":"133.216.118.88"}\n{"id":10,"first_name":"Christine","last_name":"Swane","email":"cswane9@shop-pro.jp","gender":"Polygender","ip_address":"86.16.210.164"}\n'

In [10]:

pd.read_json(_, lines=True) # the default for orient is records

Out[10]:

	id	first_name	last_name	email	gender	ip_address
0	1	Frasco	Necolds	fnecolds0@vk.com	Male	243.67.63.34
1	2	Dulce	Santos	dsantos1@mashable.com	Female	60.30.246.227
2	3	Prissie	Tebbett	ptebbett2@infoseek.co.jp	Genderfluid	22.21.162.56
3	4	Schuyler	Coppledike	scoppledike3@gnu.org	Agender	120.35.186.161
4	5	Leopold	Jarred	ljarred4@wp.com	Agender	30.119.34.4
5	6	Joanna	Teager	jteager5@apache.org	Bigender	245.221.176.34
6	7	Lion	Beere	lbeere6@bloomberg.com	Polygender	105.54.139.46
7	8	Marabel	Wornum	mwornum7@posterous.com	Polygender	247.229.14.25
8	9	Helenka	Mullender	hmullender8@cloudflare.com	Non-binary	133.216.118.88
9	10	Christine	Swane	cswane9@shop-pro.jp	Polygender	86.16.210.164

]

Sai

Different JSON Formats Supported by Pandas

Different JSON Formats supported by Pandas¶

Share this post

Join Our Community

Follow Us

Links

Contact Info

Address

Phone

Email