to_json to output data in Json format from DataFrame taken from MySQL Excel & CSV files with options like orient

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()

Numpy arrays Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

to_json()

to_json() : DataFrame to JSON output file/string

Option orient

Using SQLAlchemy

From Excel file to JSON output

Subscribe