to_json()

Pandas Pandas input output Json

Returns JSON formatted string from the DataFrame.
import pandas as pd 
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,40]
	}
df = pd.DataFrame(data=my_dict)
df.to_json('D:\my_file.json')
This will create a file my_file.json with json formatted data at root of D drive.
  • to_json() : DataFrame to JSON output file/string


if the path_or_buf is not given then string output is returned.
import pandas as pd 
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,40]
	}
df = pd.DataFrame(data=my_dict)
df.to_json()
Output
'{"NAME":{"0":"Ravi","1":"Raju","2":"Alex"},"ID":{"0":1,"1":2,"2":3},
"MATH":{"0":30,"1":40,"2":50},"ENGLISH":{"0":20,"1":30,"2":40}}'

Option orient

We can give option for orient as split,records, index ( default ) , columns, values,table
import pandas as pd 
my_dict={
	'NAME':['Ravi','Raju','Alex'],
	'ID':[1,2,3],'MATH':[30,40,50],
	'ENGLISH':[20,30,40]
	}
df = pd.DataFrame(data=my_dict)
df.to_json(orient='split')
Output
'{"columns":["NAME","ID","MATH","ENGLISH"],"index":[0,1,2],"data":[["Ravi",1,30,20],["Raju",2,40,30],["Alex",3,50,40]]}'
orient='index' ( default )
'{"0":{"NAME":"Ravi","ID":1,"MATH":30,"ENGLISH":20},
"1":{"NAME":"Raju","ID":2,"MATH":40,"ENGLISH":30},
"2":{"NAME":"Alex","ID":3,"MATH":50,"ENGLISH":40}}'
orient='records'
'[{"NAME":"Ravi","ID":1,"MATH":30,"ENGLISH":20},
{"NAME":"Raju","ID":2,"MATH":40,"ENGLISH":30},
{"NAME":"Alex","ID":3,"MATH":50,"ENGLISH":40}]'
orient='columns'
'{"NAME":{"0":"Ravi","1":"Raju","2":"Alex"},
"ID":{"0":1,"1":2,"2":3},"MATH":{"0":30,"1":40,"2":50},
"ENGLISH":{"0":20,"1":30,"2":40}}'
orient='values'
'[["Ravi",1,30,20],["Raju",2,40,30],["Alex",3,50,40]]'
orient='table'
'{"schema": {"fields":[{"name":"index","type":"integer"},{"name":"NAME","type":"string"},{"name":"ID","type":"integer"},{"name":"MATH","type":"integer"},{"name":"ENGLISH","type":"integer"}],"primaryKey":["index"],"pandas_version":"0.20.0"}, "data": [{"index":0,"NAME":"Ravi","ID":1,"MATH":30,"ENGLISH":20},{"index":1,"NAME":"Raju","ID":2,"MATH":40,"ENGLISH":30},{"index":2,"NAME":"Alex","ID":3,"MATH":50,"ENGLISH":40}]}'

Using SQLAlchemy

We will collect records from our sample student table in MySQL database and display the JSON formatted string.
Collect SQL dump of sample studtent table below.
Read more on MySQL with SQLAlchemy connection. Below code will create student.json file in the same directory, you can add path if you want the file to be created at different location.
import pandas as pd 
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")
sql="SELECT * FROM student LIMIT 0,10 "
df = pd.read_sql(sql,my_conn)
df.to_json('student1.json',orient='records') 

From Excel file to JSON output

In above code we have created one DataFrame by taking data from a MySQL database table. We can create DataFrame by using any excel data or by using any csv file or from any other sources. ( check here to create a DataFrame from 8 different sources )
Once a DataFrame is created, then using that we can create JSON output by using to_json(). Here is one example to read one Excel file to a DataFrame and generate JSON string, you can explore other sources to create a DataFrame and finally generate JSON string / file.
We used read_excel() to read our sample student.xlsx file.
df=pd.read_excel("D:\\my_data\\student.xlsx") # Path of the file. 
df.to_json()
We can read one csv file by using read_csv()
df=pd.read_csv("D:\\my_data\\student.csv") # change the path
df.to_json()

Data input and output from Pandas DataFrame
Pandas read_csv() read_excel() read_json() to_csv() to_excel()


plus2net.com



Post your comments , suggestion , error , requirements etc here





Python Video Tutorials
Python SQLite Video Tutorials
Python MySQL Video Tutorials
Python Tkinter Video Tutorials
We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer