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') # Json format file is saved in D drive

This will create a file my_file.json with json formatted data at root of D drive.

df.to_json() # Output as Json string

to_json(): Datafram data to Json string #B07

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

Python Pandas DataFrame to create JSON file & using MySQL sample table to JSON string by to_json()

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

Questions

What is the purpose of the to_json() function in Pandas?
How do you use the to_json() function to convert a Pandas DataFrame to a JSON string?
Can the to_json() function handle complex data structures, such as nested dictionaries or lists?
What are the available options for the orient parameter in the to_json() function?
How can you specify the output file path when using the to_json() function?
Is it possible to customize the formatting of the JSON string generated by the to_json() function?
Can the to_json() function handle missing or NaN values in the DataFrame?
What is the difference between using to_json() with the orient parameter set to "columns" versus "index"?
How does the date_format parameter work in the to_json() function?
Is it possible to exclude certain columns or rows from the JSON output using the to_json() function?

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() : Json string from DataFrame

Option orient

Using SQLAlchemy

From Excel file to JSON output

Questions

Subscribe